Mysql experience sharing: Stored Procedure _ MySQL-mysql tutorial

Mysql experiences: Stored Procedure

Use test; drop table if exists t8; create table t8 (s1 INT, primary key (s1); drop procedure if exists handlerdemo; DELIMITER $ create procedure handlerdemo () BEGINdeclare xx int default 4; DECLARE oh_no condition for sqlstate '2013'; # declare continue handler for sqlstate '000000' SET @ X2 = 1; declare exit handler for oh_no set @ info = 'violation of primary key constraint '; SET @ X = 1; insert into t8 VALUES (1); SET @ X = 2; insert into t8 VALUES (1); SET @ X = 3; END

Call stored procedure

/* CALL the stored procedure */CALL handlerdemo ();/* view the CALL stored procedure result */SELECT @ X, @ info;

Experience:

1. statement terminator

Mysql may regard stored procedures and user-defined functions as one statement. Therefore, multiple statements in stored procedures are separated by commas (;). to avoid conflicts, you must use delimiter to redefine the Terminator.

You can define a new Terminator before the stored procedure starts, as shown in figure

Delimiter //

After writing the stored procedure, restore the definition: delimiter;

But you can also write delimiter $ Before the stored procedure starts, but you cannot write other statements after the stored procedure. Example above

2. variables

Mysql variables, like SQL SERVER, are in the form of @ X, but are directly used without declaration.

In the stored procedure, the variable does not need to be @, but must be declared. And the statement should be placed in the header of the stored procedure (?), In this example, an error is reported. It's really strange. On the one hand, sometimes variables can be used without declaration. on the other hand, sometimes the declaration position must be limited, which seems at a loss.

The variables in a stored procedure only apply to the stored procedure. But those variables with @ seem to be able to span sessions and connections, and seem to be global variables? The preceding example is as follows.

3. conditions and handling

Define conditions to call the processing. For example:

DECLARE oh_no condition for sqlstate '201312'; # declare continue handler for sqlstate '000000' SET @ X2 = 1; declare exit handler for oh_no set @ info = 'violation of primary key constraint ';