MySQL Stored Procedure ERROR Handler Exception Handling

MySQL Stored Procedure ERROR Handler Exception Handling

MySQL Stored Procedure ERROR Handler Exception Handling
Example: when insert fails, I want to record it in the log file,

"" Here we need to create a primary key table and a foreign key table. We use Innodb, so the foreign key Association check is open. When I insert a foreign key table
When the value in a non-primary key table fails, the created data table is as follows:
Create table t2 (s1 int primary key) engine = innodb ;//
Create table t3 (s1 int,
Key (s1 ),
Foreign key (s1) references t2 (s1) engine = innodb ;//
Create table error_log (error_message char (80 ));//

1. Create a process. The first statement declare exit handler is used to handle exceptions, meaning that if error 1216 occurs, this program inserts a row into the error log table,
EXIT means that this compound statement is released after the action is successfully submitted.
Create procedure p22 (parameter int)
Begin
Declare exit Handler for 1452
Insert into error_log values (concat ('time: ', current_date,'. Foreign key reference failure for value = ', parameter ));
Insert into t3 values (parameter );
End ;//

2. declare handler syntax:
DECLARE {EXIT | CONTINUE} handler for {error_number | {SQLSTATE error-string} | condition} SQL Statement
The above is the usage of error handling, that is, a piece of code that is automatically triggered when a program fails. MYSQL allows two processors, one is exit processing, and the other is continue processing.
The difference is that after the execution, the original main program continues to run, then the compound statement will not be exported.

---- Example of continue processing:
Create table t4 (s1 int primary key );//
Create procedure p23 ()
Begin
Declare continue handler for SQLSTATE '000000' set @ x2 = 1;
Set @ x = 1;
Insert into t4 values (1 );
Set @ x = 2;
Insert into t4 values (1 );
Set @ x = 3;
Select @ x, @ x2;
End ;//
Call p23 ();//

---- Rollback (rollback transaction), define your own error processing name declare 'error processing NAME 'condition for sqlstate' 23000 ';
Create procedure p24 ()
Begin
Declare ViolationSelf condition for SQLSTATE '201312 ';
Declare exit handler for ViolationSelf rollback;
Start transaction;
Insert into t2 values (1 );
Insert into t2 values (1 );
Commit;
End ;//

/*************************************** ********************************* *************************/

Summary of the cursor implementation function: Declares the cursor, opens the cursor, reads from the cursor, and closes the cursor.
DECLARE cursor-name cursor for select ······
OPEN cursor-name;
FETCH cursor-name INTO variable;
CLOSE cursor-name;

1. create procedure p25 (out return_val int)
Begin
DECLARE a, B, c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set B = 1;
Open cur_1;
Set c = 0;
Repeat
Fetch cur_1 into;
Until B = 1
End repeat;
Close cur_1;
Set return_val =;
End ;//

2. create procedure p25_1 (out return_val int)
Begin
DECLARE a, B, c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set B = 1;
Open cur_1;
Set c = 0;
Lable_1: loop
Fetch cur_1 into;
If B = 1 then
Leave lable_1;
End if;
Set c = c + 1;
End loop;
Close cur_1;
Set return_val = c;
End ;//

Create procedure p34 (in va int)
Begin
Delete from t where s1 = va;
End ;//