WEB开发网
开发学院数据库DB2 DB2"纯"sql 存储过程例子 阅读

DB2"纯"sql 存储过程例子

 2006-03-20 22:03:59 来源:WEB开发网   
核心提示:我看到很多人要sql的存储过程的例子,所以我就把我以前写的发出来,DB2"纯"sql 存储过程例子,和大家一起探讨! 下面是我在苏州的时候写的代码,, call ipd.bi_settle_tablespace(in_Transfer_id, o_err_no, o_err_msg);--调用此

我看到很多人要sql的存储过程的例子,所以我就把我以前写的发出来,和大家一起探讨!

下面是我在苏州的时候写的代码,,是把Oracle(大型网站数据库平台)上的移植过来的,如果大家要Oracle(大型网站数据库平台)的代码,可以告诉我一声,我发

这段代码很全,有出错处理,游标动态定义,联合体用户的使用,分支和循环语句都有,,

到 /sqllib/下面去找,很多例子的代码的

我献丑了!!!

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

Tags:DB quot quot

编辑录入:coldstar [复制链接] [打 印]
赞助商链接