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);
                            set o_err_no=1;
                            return 0;
                    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;
                         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
                         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;    
                                 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);
        
                     --记录
                     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;
                                     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整理表空间';
                             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)||'''';
                             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