DB2"纯"sql 存储过程例子

核心提示：我看到很多人要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