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='Violate primary key constraint';
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END
Copy the code

\

Calling a stored procedure

/* Call the stored procedure */
CALL handlerdemo();

/* View the result of calling the stored procedure */
SELECT @X.@info;
Copy the code

Experience:

1. Statement terminator

Mysql may treat a stored procedure or custom function as a single statement. Therefore, multiple statements in a stored procedure use “; “. To avoid collisions, redefine the delimiter terminator.

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

delimiter //

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

\

2, variables,

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

In stored procedures, variables are declared without using @. And declare it to be placed at the head of the stored procedure. , as in this example, otherwise an error is reported. It is strange that, on the one hand, variables can be used without declaration, and on the other hand, variables can be declared in a position that is confusing and seems arbitrary.

A variable within a stored procedure whose scope is limited to the stored procedure. But variables with @ signs, which seem to span sessions and connections, look global, right? In the example above.

\

3. Conditions and treatment

Conditions are defined for processing calls. Example 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='Violate primary key constraint';
Copy the code


\

\