[轉]MySQL預存程序 ERROR Handler 異常處理

FROM : http://www.111cn.net/database/110/44f4c8e26e35c649a9b42e2754a69e5d.htm

MySQL預存程序 ERROR Handler 異常處理
 問題範例：當insert失敗時，我希望將其記錄在記錄檔中，

       》》在這裡需要建立一個主鍵表，以及一個外鍵表，我們使用的是Innodb ，因此外部索引鍵關聯檢查是開啟的，當我向外鍵表中插入
           非主鍵表中的值時，動作將會失敗，建立的資料表如下：
    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. 建立一個過程，第一個語句 DECLARE EXIT HANDLER是用來處理異常的，意思是如果錯誤 1216發生，這個程式將會在錯誤記錄表中插入一行，
   EXIT的意思是 當動作成功提交後推出這個複合陳述式。
     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
   上面就是錯誤處理的用法，也就是一段當程式出錯後自動觸發的代碼，MYSQL允許兩種處理器，一種是exit處理，另外一種是 continue處理，與exit
   不同的是在於他執行後，原主程式仍然繼續運行，那麼該複合陳述式就沒有出口了。

----continue處理的例子：
      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（復原事務）,定義自己的錯誤處理名字 declare '錯誤處理名' 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遊標  **********************************************************/

遊標實現功能的摘要： 聲明遊標， 開啟遊標，從遊標裡讀取，關閉遊標
      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
       delete from t where s1=va;
    end;//