mysql study notes: stored procedures

use test;

drop table if exists t8;
CREATE TABLE t8(s1 INT,PRIMARY KEY(s1));

drop procedure if exists handlerdemo;

DELIMITER $$
CREATE PROCEDURE handlerdemo()
BEGIN
declare xx int default 4;
DECLARE oh_no condition for sqlstate '23000';
#DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
declare exit handler for oh_no set @info='违反主键约束';
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END

Call stored procedure

/* 调用存储过程*/
CALL handlerdemo();

/* 查看调用存储过程结果*/
SELECT @X,@info;

Experience:

1. Statement terminator

Perhaps mysql regards stored procedures and custom functions as one statement. Therefore, multiple statements in the stored procedure are separated by ";". In order to avoid conflicts, just Use delimiter to redefine the terminator.

Generally, you can define a new terminator before the stored procedure starts, such as

delimiter //

After the stored procedure is written, restore the definition: delimiter;

2. Variables

Mysql variables are the same as SQL SERVER, in the shape of @X, but no need to declare, they can be used directly.

In the stored procedure, variables do not need to be @, but they must be declared. And the statement should be placed at the head of the stored procedure (?), as in this example, otherwise an error will be reported. It's really strange. On the one hand, sometimes variables can be used without declaring them. On the other hand, sometimes the declaration position must be limited, which is confusing and seems a bit casual.

The variables inside the stored procedure have scope limited to the stored procedure. But those variables with @ seem to span sessions and connections, and appear to be global variables? Like the example above.

3. Conditions and processing

Define conditions to call processing. Such as the above example:

DECLARE oh_no condition for sqlstate '23000';
#DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
declare exit handler for oh_no set @info='违反主键约束';

The above is the above content. For more related content, please pay attention to the PHP Chinese website (www.php.cn)! For more related content, please pay attention to the PHP Chinese website (www.php.cn)!