DB2 sql存储过程基础 （转）

[font=Verdana]DB2 sql存储过程基础[/font] [font=verdana, arial, helvetica][size=2][size=12px]原帖发于：[url=http://eyejava.javaeye.com/blog/32263][color=#0000ff]http://eyejava.javaeye.com/blog/32263[/color][/url]

基本概念：
存储过程即stored procedure,一般会被简称procedure。要学这个先得弄明白另外一个概念：routine，这个一般翻译成“例程”
>>routine:存在server端，按应用程序逻辑编写的，可以通过client或者其他routine调用的数据库对象.
>3种类型：stored procedures,UDFs(自定义function),methods.
stored procedures:作为客户端的扩展但是运行在服务端；UDFs:扩展并且自定义SQL；methods:提供结构化类型的行为
>2种形式：
1）sql routines:完全用sql编写，通过create statement来注册routine.
2）external routines:用C,C++,Java,OLE编写，stored procedure还可用cobol编写。任何语言编写的都可以包含sql。
不同形式的routines可以互相调用，不管是什么语言编写的。

再来看看stored procedure.
>>stored procedures:可以通过call statement被client或者其他routine调用；stored procedures 和它的调用程序通过create procedure statement中的参数交换数据；stored procedures还能给它的调用者返回result sets.
stored procedures的优点：
1) 多个sql statement被调用者一次调用就能全部执行，这能减少client和server间的数据传输。
2)将数据库逻辑与应用程序逻辑隔离开
3)能返回多个result sets
4)如果被应用程序调用，运行起来stored procedure就像应用程序的一部分
缺点：
1)不能被sql statement调用，除了用call
2)返回的结果集不能直接被sql statement使用
3)多次调用之间不能保存调用的状态，即调用之间是独立的，无法传递信息。
一般的应用之处：
1)提供一个interface给一组sql statements。比如同时对多个表的insert操作
2)标准化应用程序逻辑（不理解，就是把db logic与app logic隔离吗？）

开发特性：
明白了这些基本概念后再来看看开发的特性。根据以上得知开发routine的语言有很多，这篇只讲sql procedure（即sql/sql pl写的procedure）。
>>各种语言的特性
sql:
1)效率高于java routine,基本上与c/c++ routine相当
2)完全用sql编写，能很快就能执行(making them quick to implement)
3)DB2认为sql routine是'safe'的因为全是sql，正因如此sql routine能直接在db engine上运行，并且有很好的运行效率和应用范围(good performance and scalability)
>>stored procedure feathures:
parameter modes:
3种类型的参数：1)IN :传入数据到stored procedure 2)OUT: stored procedure 返回数据 3)INOUT: 传入的那部分数据，在执行过程中被返回数据覆盖

result sets:
stored procedure通过cursor来传递结果集给调用者。存储过程必须为每一个需要返回的结果集保留一个游标。
>使用with return to caller/client来指定结果集返回的对象。指定为client将使得中间调用的routine不能获得结果集，只有client才能获得。
>使用dynamic result sets 语句来指定返回结果集的数目，这个数目保存在syscat.routines视图的result_sets字段。如果实际返回的结果集数目大于声明的这个数目，将发出一个warning(sqlcode +464,sqlstate 0100E)
sql stored procedure返回结果集的操作步骤：
1)declare cursor:
如：declare clientcur cursor with return to caller for select * from staff;
2)open the cursor:如 open clientcur;
3)不关闭游标退出stored procedure

开发：
最后终于来到了真正的开发了，刚才讲到sql procedure是由sql,sql pl写的，sql就没什么好说的了。关键说说sql pl (procedural language)
>>功能:控制逻辑流向，声明和设置变量，处理警告和异常。可用于例程(routine),触发器，动态复合语句（单个调用中的sql语句块）
>>控制语句：declare,set,for,get diagnostics,if,iterate,leave,return,signal,while
>>sql pl不能执行的sql：table,index,view的create和drop
>>begin atomic 开头，end 结尾
>>declare :定义变量 和 定义出错处理
declare sql-var-name data-type default default-values
declare condition-name condition for sqlstate value... //这里的condition一般做“异常”解释
>>set:声明变量 和 给触发器定义中的表中的列赋值
set pay = select salary from employee where empno = 5;//仅返回一个值
set pay = null;//空值
set pay = default;//变量定义的默认值
//专用寄存器的内容
set userid = userid;
set today = current date;
//同时给多个变量赋值
set pay =10000,bonus = 1500;
set (pay,bonus) = (10000,1500);
set (pay,bonus) = select (pay,bonus) from employee where empno = 5;
>>if/then/else
三种形式：
1) if then/end if 语句块
2) if then/else/end if
3) if then/elseif /else/end if
可以在if/then/else 语句中使用sql运算符，如：
if (salary between 10000 and 90000) then...
if (deptno in ('a00','b01')) then..
if (exist (select * from employee)) then...
if (select count(*) from employee)>0) then..
>>while
label:
while condition do
...sql pl ..
end while lable; //label可选
>>for:用于循环select返回结果集的行
格式：
label:
for row_label as select satement do
..sql pl..
end for label;//label可选
例子：
for emp as select * from employee where bonus >1000 do
set total_bonus = total_bonus +emp.bonus;
end for;
>>iterate:用来回到for或者while循环的开始重新执行
check_bonus:
for emp as select * from employee do
if(emp.bonus>10000) then
set total_bonus = total_bonus +emp.bonus;
else
iterate check_bonus;
end if;
end for check_bonus;
>>leave:相当于java中的break，需要一个label

>>signal:对出现异常的应用程序报警
signal sqlstate value set message_text = '...';//自定义一个sqlstate,7、8、9和I~Z开头的sqlstate
signal condition set message_text = '...';//自定义异常condition

>>get diagnostics:用在sql pl触发器或语句块(不是函数)内，返回update,insert,delete语句影响的记录数。
get diagnostics variable = row_count;[/size][/size][/font]

这是以前收集的文章。这两天开始学存储过程，因此又看了一遍，感觉文章写得言简意赅，很利于存储过程初学者上手。

再转一个例子

我看到很多人要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