你的位置:首页 > 数据库

[数据库]存储过程用法


存储过程的使用方法的具体实例

关键字:输出参数,游标,删除,查询,插入,修改,循环,动态临时表,异常处理

(所有表名及字段名随意写的,执行环境: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;