mysql tips: 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=' Violation of primary key constraints ';
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END

Calling a stored procedure

/*  Calling a stored procedure */
CALL handlerdemo();

/*  View the results of the called stored procedure */
SELECT @X,@info;

Experience:

1. Statement terminator

Perhaps mysql treats stored procedures and custom functions as one statement, so multiple statements in a stored procedure use ";" To avoid conflicts, use delimiter to redefine the terminator.

It is usually possible to define a new terminator before the stored procedure begins, such as

delimiter //

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

You can also write delimiter $$before the stored procedure starts, but you can't write any more statements after the stored procedure. Examples above

2, variables,

The variable of mysql, like SQL SERVER1, looks like @X, but does not need to be declared and is used directly.

In a stored procedure, the variable is declared without using @. And declare that you want to put it in the head of the stored procedure. , such as this example, otherwise report an error. It's weird that, in the first place, you can use variables without declaring them, and in the second place, you have to restrict the position of the declarations, which is confusing and kind of arbitrary.

A variable within a stored procedure whose scope is limited to the stored procedure. But those variables with @ appear to be global variables that span sessions and connections? The example above.

3. Conditions and treatment

Conditions are defined to give processing calls. Examples above:

DECLARE oh_no condition for sqlstate '23000';
#DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
declare exit handler for oh_no set @info=' Violation of primary key constraints ';