MySQL stored procedure ERROR Handler exception handling

Problem Sample: When an insert fails, I want to record it in a log file.

"Here we need to create a primary key table, and a foreign key table, we are using InnoDB, so the foreign Key Association check is open when I insert into the foreign key table
When a value is not in the primary key table, the action fails and the data table is created 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. Establish a process, the first statement DECLARE EXIT handler is used to handle the exception, meaning that if error 1216 occurs, the program will insert a row in the Error record table,
Exit means that the compound statement is rolled out when 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. Affirms the syntax of exception handling DECLARE HANDLER syntax:
DECLARE {exit| CONTINUE} HANDLER for {error_number|{ SQLSTATE Error-string}|condition} SQL Statement
The above is the use of error handling, that is, when the program automatically triggered after the error code, MySQL allows two kinds of processors, one is exit processing, the other is continue processing, and exit
The difference is that after he executes, the primary program still runs, and then the compound statement is not exported.

Example----Continue processing:
      CREATE table t4 (S1 int primary key);//
       CREATE PROCEDURE p23 ()
       begin
          declare continue handler for SQLSTATE ' 23000 ' 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 its own error handling name declare ' Error handling name ' condition for SQLSTATE ' 23000 ';
CREATE PROCEDURE P24 ()
Begin
DECLARE violationself condition for SQLSTATE ' 23000 ';
DECLARE EXIT HANDLER for violationself rollback;
Start transaction;
INSERT into T2 values (1);
INSERT into T2 values (1);
Commit
end;//

/******************************************** cursor Cursor ********************************************************** /

Summary of the cursor implementation feature: Declaring the cursor, opening the cursor, reading from the cursor, closing 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 A;
  until b=1
       end repeat;
       Close cur_1;
       set return_val=a;
    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 A;
  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
     & nbsp Delete from t where S1=va;
    end;//