程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2存儲過程

DB2存儲過程

編輯:DB2教程

下面轉載的一個復雜的存儲過程,大家一起分享,其實存儲過程並沒有大家想像中的那麼困難,

只要你掌握了基本的語法,就一定能寫出復雜的過程。復雜的東西都是由簡單堆積而成的!!

CREATE PROCEDURE IPD.st_inter_PROF ( IN in_Transfer_id dec(6,0),
                                     IN in_TRANS_TYPE_id dec(2,0),
                                     IN in_begin_date timestamp,
                                     IN in_TRANSFER_name varchar(1024),
                                     OUT o_err_no int,
                                     OUT o_err_msg varchar(1024) )
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存儲過程 
------------------------------------------------------------------
--                                         --
--                             

                                  --
--              抽取acct_item_billingday,acct_item表             --
--              author :zsk   2002/06/27                         --
--              update   by zsk    at  2002/11/25  as  SZ        --
--              move from Oracle to db2 by dengl 2002-12-8 as sz --
--              返回值結果:0:執行通過                          --
--                         1:執行不通過                         --
--                        -1:調用本過程時異常出錯               --
--              聯合體用戶是 ADMINISTRATOR  BILL.BILL.* /BILL.CAL.* --
-------------------------------------------------------------------
-----------------------------------------------------------------

-------
P1: BEGIN
      --臨時變量出錯變量
       declare rec              integer default 0;
       declare SQLCODE          integer default 0;
       declare stmt             varchar(1024);
       declare at_end           integer default 0;
       declare r_code           integer default 0;  
       declare state            varchar(1024) default ''AAA'';--記錄程序當前所作工作
       declare temp_int         integer default 0;
      --聲明變量
       declare v_cycle_str   varchar(1000);
       declare v_sql_str     varchar(2000);
       declare n_num         bigint;
       declare n_rows        bigint;
       declare n_rows_all    bigint;

      --聲明放游標的值
       
     --聲明動態游標存儲變量
        declare c_bill_task_id  integer;
         declare bill_task cursor for s1; 
        

;

      --聲明出錯處理
        DECLARE EXIT HANDLER FOR SQLEXCEPTION 
          begin
             set r_code=SQLCODE;
             set o_err_no=1;
             set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(r_code);
          end;    
        DECLARE continue HANDLER for not found  
           begin
              SET at_end = 1;
              set o_err_no=100;
           end;

       --開始拉
       select  deal_cycle
       into  v_cycle_str
       from  ipd.transfer_task
       where transfer_id=in_transfer_Id;
       --v_cycle_str:=''%''||v_cycle_str;

        if  in_trans_type_id=7
          then   
             set n_num=1;

             ---將匯總數據寫入任務表

             update ipd.transfer_task
              set    rows_cnt=0
             where  transfer_id=in_transfer_id;
            
              --聲明動態游標
             set stmt='' select  distinct bill_task_id  from  ADMINISTRATOR.bill_task_cycle a , ADMINISTRATOR.billing_cycle b where substr(char(b.CYCLE_BEGIN_DATE),1,4)||substr(char(b.CYCLE_BEGIN_DATE),6,2)=''||char((integer(v_cycle_str)-1))||'' and   a.billing_cycle_id=b.billing_cycle_id''; 
             prepare s1 from stmt;
            -- execute s1;
            
             open bill_task; --using v_cycle_str;
          --聲明完畢
             fetch_loop1:
             loop
                 fetch bill_task into c_bill_task_id   ;
 
         --由於db2和oracle的不同,db2必須先創建一個oracle相連的別名ADMINISTRATOR.*,而不像Oracle直接用@to_jif 下面是oracl的源碼
         --  v_sql_str:='' update transfer_task
         --                  set  rows_cnt=rows_cnt+(select count(*)
         --                                          from cal.acct_item_billingday_''||rec.bill_task_id||''@to_jf)
         --                  where transfer_id=''||in_transfer_id;
         --update by dengl 2002-12-08
        
                set stmt=''create nickname ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id)||'' for bill.cal.acct_item_billingday_''||char(c_bill_task_id);
       
          --記錄
               set state=''創建別名''||''ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id);
               call ipd.sp_exec_dsql(stmt,o_err_no);
       
             --o_err_no 是返回的SQLCODE
               if o_err_no<>0
                 then 
                       update ipd.transfer_task
                       set    deal_flag=-1
                       where  transfer_id=in_transfer_id;
                       set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(o_err_no); 
                       set o_err_no=1;
                       return 0;
               end if;
               set v_sql_str='' update ipd.transfer_task set  rows_cnt=rows_cnt+(select count(*)  from ''||''ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id)||''    where transfer_id=''||char(in_transfer_id);
         
               call ipd.sp_exec_dsql(v_sql_str,o_err_no); 
               if  o_err_no <> 0 
                  then
                     update ipd.transfer_task
                     set    deal_flag=-1
                     where  transfer_id=in_transfer_id;
                     set o_err_msg=char(in_TRANS_TYPE_id)||''傳送出錯!SQLCODE:''||char(o_err_no);
     &nbsp;               set o_err_no=1;
                     return 0;
              &nbsp;end if ;
               commit;
             end loop fetch_loop1;
             close bill_task;
--匯總數據寫入完畢

--建立接口表並插入數據

       ---整理表空間。
             call ipd.bi_settle_tablespace(in_Transfer_id,
                             o_err_no,
                             o_err_msg);--調用此過程,檢測表空間
              --返回值不為0,則不執行返回
             set state=''整理表空間'';
             if o_err_no<>0 
                 then
                    update ipd.TRANSFER_TASK
                    set    DEAL_FLAG=-1
                    where  Transfer_id=in_Transfer_id;
   &nbsp;                commit; 
                    set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(o_err_no); 
                    set o_err_no=1;   
                    return 0;
             end if;

              --創建任務需要的接口表 並把多個表的數據整合到一個表中去,如果是Oracle就要使用零時表而db2用別名就代替了
             set stmt=''create table ipd.''||in_TRANSFER_name;
             call ipd.sp_exec_dsql(stmt,o_err_no);
             set state=''創建接口表ipd.''||in_TRANSFER_name;
             if o_err_no<>0
                 then 
                    update ipd.TRANSFER_TASK
                    set    DEAL_FLAG=-1
                    where  Transfer_id=in_Transfer_id;
                    commit;
                    set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(o_err_no); 
                    set o_err_no=1;   
                    return 0;    
             end if;
         
            --建表完畢開始組合sql語句

             open bill_task using v_cycle_str;
             fetch_loop2:
             loop
                fetch bill_task into c_bill_task_id; 
                if n_num=1 
        &nbsp;           then
                      set  v_sql_str=''inter into ipd.''||in_TRANSFER_name||'' select * from ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id);
                    else
                      set v_sql_str=v_sql_str||''   union select * from ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id);
                end if; 
                set n_num=n_num+1;
             end loop fetch_loop2;
        
             --組合完畢
         
             --  set v_sql_str:=v_sql_str||''   )'';
             set state=''向接口表ipd.''||in_TRANSFER_name||''插入數據'';
             call ipd.sp_exec_dsql(v_sql_str,o_err_no);
             if o_err_no<>0
                     then 
                         update ipd.TRANSFER_TASK
                         set    DEAL_FLAG=-1
                         where  Transfer_id=in_Transfer_id;
                         commit;
                         set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(o_err_no); 
                         set o_err_no=1;   
         &nbsp;               return 0; 
                     else 
                         update  transfer_task
                         set     deal_flag=2
                         where   transfer_id=in_transfer_id;
                         set o_err_no=0;
                         set o_err_msg=o_err_msg||''任務號為''||char(in_TRANSFER_id)||''抽取成功!'';
             end if;
             commit;
              --數據插入完畢

              --刪除聯合體的別名
             open bill_task using v_cycle_str;
             fetch_loop3:
             loop
                 set stmt=''drop  nickname ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id)||'' for bill.cal.acct_item_billingday_''||char(c_bill_task_id);
 &nbsp;   &nbsp; 
                 --記錄
                 set state=''刪除別名''||''ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_''||char(c_bill_task_id);
                 call ipd.sp_exec_dsql(stmt,o_err_no);
                  --o_err_no 是返回的SQLCODE
                 if o_err_no<>0
                    then 
                        update ipd.transfer_task
                        set    deal_flag=-1
                        where  transfer_id=in_transfer_id;
                        set o_err_msg=''處理''||state||''出錯 ''||''錯誤代碼SQLCODE:''||CHAR(o_err_no); 
                        set o_err_no=1;
                        return 0;
                 end if;   
             end loop fetch_loop3; 


 

;          -----下賬數據接口       
         
           
           else if in_trans_type_id =8 
                   then
                     --帳務表的聯合體別名已經建好了

                      set v_sql_str=''update ipd.transfer_task set rows_cnt=(select count(*) from   ADMINISTRATOR.acct_item  a , ADMINISTRATOR.billing_cycle b  where a.billing_cycle_id=b.billing_cycle_id  and     substr(char(b.CYCLE_BEGIN_DATE),1,4)||substr(char(b.CYCLE_BEGIN_DATE),6,2)= ''''''||upper(char(v_cycle_str))||'''''' ) where Transfer_id=''||char(in_Transfer_id);
                      set state=''匯總acct_item數據 '';
                      call ipd.sp_exec_dsql(v_sql_str,o_err_no);
                      if  o_err_no <> 0 
                         then
                             update ipd.transfer_task 
              

;              set   deal_flag=-1
                             where transfer_id=in_transfer_id;
                             set o_err_no=1;
                            &nbsp;set o_err_msg=state||char(in_TRANS_TYPE_id)||''傳送出錯!'';
                         return 0;
                      end if; 
                      --整理表空間。
                      call ipd.bi_settle_tablespace(in_Transfer_id,
                                 o_err_no,
                                 o_err_msg);--調用此過程,檢測表空間
                        --返回值不為0,則不執行返回
                      set state=''為acct_item整理表空間'';
           &nbsp;           if o_err_no<>0 
                         then
                             update ipd.TRANSFER_TASK
                             set    DEAL_FLAG=-1
                             where  Transfer_id=in_Transfer_id;
                             set o_err_msg=state||''任務號''||char(in_TRANS_TYPE_id)||''傳送出錯!SQLCODE:''||char(o_err_no);
                             set   o_err_no=1;       
                             commit;
                             return 0;
                         end if;
                      --在任務表中將狀態改為1,准備傳送數據.
                      update ipd.TRANSFER_TASK
                      set    DEAL_FLAG=1
                      where  Transfer_id=in_Transfer_id;
                      commit;
                      set v_sql_str=''create table ipd.''||in_TRANSFER_name||'' like ADMINISTRATOR.ACCT_item)'';
                      call ipd.sp_exec_dsql(v_sql_str,o_err_no);
                      set stmt=''inset into ipd.''||in_TRANSFER_name||''  select  ACCT_ITEM_ID,SERV_ID,SERV_SEQ_NBR,EXT_SERV_ID, ACCT_ID,ACCT_SEQ_NBR,ACCT_ITEM_TYPE_ID,CHARGE,BILLING_CYCLE_ID,CREATED_DATE,PARTNER_ID,BILL_SERIAL_NBR,STATE,STATE_DATE, Exchange_ID, PAYMENT_METHOD from ADMINISTRATOR.acct_item  where billing_cycle_id like  ''''''||upper(v_cycle_str)||'''''''';
                  &nbsp;   call ipd.sp_exec_dsql(stmt,o_err_no);
                      set state=''插入數據到ipd.''||in_TRANSFER_name;
                      if  o_err_no = 0 
                        then 
     

                      update  transfer_task
                            set     deal_flag=2
                            where   transfer_id=in_transfer_id;
                            set o_err_no=0;
                        else
                            update transfer_task
                            set    deal_flag=-1
                            where  transfer_id=in_transfer_id;
                            set   o_err_msg=state||''任務號''||char(in_TRANS_TYPE_id)||''傳送出錯!SQLCODE:''||char(o_err_no);
                            set   o_err_no=1; 
                     end if ; 
          

;          commit;
             end if;--下帳數據完畢
         end  if;
         set temp_int=0;
         call ipd.bi_check(in_transfer_id,
              in_transfer_name,
              temp_int,
              o_err_no,
              o_err_msg);
END P1

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved