存储过程写法
存储过程写法的具体实例
关键字：输出参数，游标，删除，查询，插入，修改，循环，动态临时表，异常处理
（所有表名及字段名随意写的，执行环境：DB2数据库）
CREATE OR REPLACE PROCEDURE 数据库实例名.存储过程名 (
OUT OV_RETVAL INTEGER,--整型输出变量
OUT OV_RETMSG VARCHAR(4000),---字符串型输出变量
IN kh_RQ TIMESTAMP,--变量_日期
IN kh_BM DECIMAL(18,0) )--变量_部门
BEGIN not ATOMIC
--声明变量
DECLARE SQLCODE INT default 0; --异常判断标志
DECLARE temp_table_sql VARCHAR (4000);--创建临时表的sql
DECLARE temp_index_sql VARCHAR (4000);--创建临时表拼接指标字段的sql
DECLARE temp_index_sql_all VARCHAR (4000);--创建临时表拼接指标字段的相加之后的sql
DECLARE KHflag integer default 0; --循环变量
DECLARE KHryflag integer default 0; --循环变量(往临时表取（临时表的字段数据）值时循环取字段名)
DECLARE temp_insertID VARCHAR (20);--取字段名
DECLARE gs_gs VARCHAR (80);--业务表取出的相关业务数据
DECLARE gs_khcolid DECIMAL(18,0);--业务表取出的相关业务数据
DECLARE gs_duty_id DECIMAL(18,0);--业务表取出的相关业务数据
DECLARE KHgsflag integer default 0; --取业务数据的循环变量
--查询业务数据放入游标gs_name中
DECLARE
gs_name CURSOR FOR
select A,B,C from TABLE where C=kh_RQ and D=kh_BM ;
--查询出业务数据放入游标index_name中
DECLARE
index_name CURSOR FOR
SELECT a
FROM table
WHERE b = kh_RQ AND c = kh_BM;
--异常处理
DECLARE exit HANDLER FOR SQLEXCEPTION
BEGIN
if sqlcode<0 then
rollback;--
set OV_RETVAL='-1';--
set OV_RETMSG='A:失败 '||temp_table_sql;--
return;--
end if;--
END;
set OV_RETVAL='0';--
set OV_RETMSG='成功';--
--构建创建临时表业务字段的循环语句
select count(*) into KHflag FROM table
WHERE a = kh_RQ AND b = kh_BM;--获取循环变量的最大值
--删除业务表已有数据（数据准备）
delete from table where to_char(RQ,'yyyy-mm-dd')=kh_RQ and DEPARTMENTID=kh_BM;
open index_name;--打开游标
set temp_index_sql_all='';--拼接创建临时表字段的sql之和
while KHflag>0 do
fetch index_name into temp_index_sql;--循环取创建临时表需要的字段数据放入变量temp_index_sql
set temp_index_sql=temp_index_sql || ' DECIMAL(18, 4) ,';
set temp_index_sql_all=temp_index_sql_all||temp_index_sql;--拼接创建临时表字段sql
SET KHflag = KHflag - 1;--自减1
end while;--
set temp_index_sql_all=SUBSTR(temp_index_sql_all,1,LENGTH(temp_index_sql_all)-1);--去除最后面的逗号（ ,）
close index_name;--
set temp_table_sql='declare global temporary table SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||' (rq TIMESTAMP,khcolid INTEGER,RS_RYJCXX_ID INTEGER NOT NULL,SON_NAME VARCHAR(100),departmentid DECIMAL(5, 0),duty_id DECIMAL(5, 0),'|| temp_index_sql_all || ' ) ';--创建动态临时表sql
execute immediate temp_table_sql;--执行创建动态临时表
--执行向临时表插入日期，姓名等业务信息的sql
set temp_table_sql='insert into SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||'(a,b,c,d,e) select A.a,B.b,A.c,A.d,A.e
from tablea A ,tableb B where A.a=to_date('''||TO_CHAR(kh_RQ,'YYYY-MM-DD')||''',''yyyy-mm-dd'') and A.b='||TO_CHAR(kh_BM)||' and A.f=2
and A.a=B.a and A.b=B.b
and exists(select 1 from tablec D where A.a=D.a and A.a=D.a
and A.c=D.c
and D.x=B.x)';
execute immediate temp_table_sql; --执行插入数据
----根据业务id将对应的字段数据值从业务表中取到临时表里
select count(*) into KHryflag FROM table
WHERE a = kh_RQ AND b= kh_BM;--获取循环变量的最大值
open index_name;--打开游标
while KHryflag>0 do
fetch index_name into temp_insertID;--
set temp_table_sql='update SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||' set '||temp_insertID||' = (select COALESCE(a.a,0) from tablea a ,tableb b where a.c= b.cand a.a=to_date('''||TO_CHAR(kh_RQ,'YYYY-MM-DD')||''',''yyyy-mm-dd'')
and a.c= SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||'.c and a.d=SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||'.d and b.a=a.a
and b.e='''||temp_insertID||''' and a.b='||TO_CHAR(kh_BM)||' )';
execute immediate temp_table_sql;--执行修改临时表单个字段数据的sql
SET KHryflag = KHryflag - 1;--自减1
end while;--
close index_name;--
---往业务表里插入数据(字段数据值都赋值为0)
set temp_table_sql =' insert into table(a,b,c,d,e,f,g,h)
select r for 序列索引,b,2,d,e,f,g,0
from SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||'';
execute immediate temp_table_sql;
--更新业务表目前最大id值，加一
update table set a=(select max(b)+1 from c) where d='sdfsdfsd';
select count(*) into KHgsflag FROM table
WHERE a=kh_RQ and b=kh_BM and c=2;--获取循环变量的最大值
open gs_name;--打开游标
while KHgsflag>0 do
fetch gs_name into a,b,c;--循环取出游标内容（有三列）依次放入三个变量中
set temp_table_sql='update ca set a.c=(select '|| to_char(gs_gs) ||'
from SESSION.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||' b where a.c=b.c and b.c='||to_char(gs_khcolid)||' and b.c='||
to_char(s)||' and a.s=b.s and a.s=b.s)
where a.s=to_date('''||TO_CHAR(kh_RQ,'YYYY-MM-DD')||''',''yyyy-mm-dd'') and a.s='||TO_CHAR(kh_BM)||' and a.s='||to_char(s)||' and a.s='||to_char(s)||' ';
execute immediate temp_table_sql;
SET KHgsflag = KHgsflag - 1;--自减1
end while;--
close gs_name;--
set temp_table_sql='DROP TABLE session.GZ_EJKH_FYHZ_'|| to_char(kh_BM) ||'';--释放临时表
execute immediate temp_table_sql;
commit;--
END;
