DB2預存程序模版

1、日誌表 create table joblog(jobfun varchar(50),      --預存程序的功能procname varchar(100),   --預存程序runtime varchar(50),     --已耗用時間currentuser varchar(50), --目前使用者state varchar(4),        --狀態ChangeRecordsNum integer --受影響行數)state代表:--Joblog stats =0  正在執行--Joblog stats =1  預存程序正常結束--Joblog stats =8  有其他過程正在執行，中斷--Joblog stats =9  預存程序異常終止2、預存程序模板create PROCEDURE YY_Proc01 (IN FromDate integer)--Joblog stats =0  running--Joblog stats =1  Finish right--Joblog stats =8  Finish cause by another running--Joblog stats =9  Stop with exceptionLANGUAGE sqlYY_Proc:begin    declare  JobFun varChar(50) default 'Delete W02 CINACC';--    declare ChangeRecordsNum integer default 0 ;    declare flag integer default 0;--考慮作為返回參數    declare SQLSTATE char(5);    declare at_end integer default 0;    declare not_found condition for SQLSTATE '02000';       declare MyCur cursor for < ..select..>;    /**********異常處理************/    declare exit HANDLER FOR SQLEXCEPTION    begin        rollback;        insert into joblog values(JobFun,'YY_Proc01',(select current timestamp from sysibm.sysdummy1),                         (select user from sysibm.sysdummy1),'9',0);--執行遇到異常        set flag=1;        return flag;    end;    declare CONTINUE HANDLER FOR  not_found    begin          set at_end=1;    end;    /***************************/            set flag = 0;    --開始工作 insert into logs    insert into joblog values(JobFun,'YY_Proc01',(select current timestamp from sysibm.sysdummy1),                         (select user from sysibm.sysdummy1),'0',0);     OPEN  MyCur;    set at_end=0;    FETCH MyCur INTO ans;    WHILE at_end=0 DO        --your work        SET at_end = 0;        --迴圈受影響行        Set ChangeRecordsNum = ChangeRecordsNum+1;        FETCH MyCur INTO ans;    END WHILE;    CLOSE MyCur;    --順利執行完畢    insert into joblog values(JobFun,'YY_Proc01',(select current timestamp from sysibm.sysdummy1),                         (select user from sysibm.sysdummy1),'1',ChangeRecordsNum);        commit;    set flag=0;    return flag;end YY_Proc