Mysql experience Sharing: Stored procedures _mysql

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 = ' violates PRIMARY key constraint ';
SET @x=1;
INSERT into T8 VALUES (1);
SET @x=2;
INSERT into T8 VALUES (1);
SET @x=3;
End

Calling stored procedures

/* Call the stored procedure * *
Handlerdemo ();

/* View call Stored procedure results
/SELECT @x, @info;

Experience:

1, Statement Terminator

Perhaps MySQL is a stored procedure, a custom function as a statement, therefore, stored procedures in a number of statements with ";" separated, in order to avoid conflicts, you need to redefine the Terminator with delimiter.

You can typically define a new terminator before the stored procedure starts, such as

Delimiter//

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

But you can also write delimiter $$ before the stored procedure starts, but you can't write another statement after the stored procedure. As the above example

2, variable

MySQL variables, like SQL Server, are shaped like @x, but need not be declared to be used directly.

In a stored procedure, a variable is not required to use @, but to declare. and declare the header to be placed in the stored procedure (? ), as in this case, or an error. It's strange, on the one hand, sometimes variables need not be declared to use, on the other hand, sometimes to limit the position of the declaration, it is confusing, seems to be a bit casual.

A variable within a stored procedure that is scoped to a stored procedure. But those with @ variables seem to span sessions and connections, and look like global variables? As the above example.

3. Conditions and treatment

Defines the condition to be invoked for processing. 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 = ' violates PRIMARY key constraint ';