存储过程总结

BernardHan

于 2019-10-20 02:50:17 发布

阅读量336

点赞数

分类专栏： sql 文章标签：存储过程

本文链接：https://blog.csdn.net/hanguihb/article/details/102645533

版权

sql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

存储过程的一般结构如下：

CREATE PROCEDURE testDynamicCursor(IN flag CHAR(1)) --首句定义名称及参数
BEGIN --开始
DECLARE v_sql VARCHAR(1000); --定义变量
DECLARE v_st STATEMENT;
DECLARE v_cursor CURSOR WITH RETURN FOR v_st; -- 定义游标（不同的数据库写法可能不一样）
--下面是处理过程（处理过程里面也可以继续定义变量或游标，继续处理）
IF (flag = '1') THEN
SET v_sql = 'SELECT * FROM TEST1';
ELSE
SET v_sql = 'SELECT * FROM TEST2';
END IF;

PREPARE v_st FROM v_sql;
OPEN v_cursor;
END --结束

使用存储过程的难点在于游标的使用，本文着重介绍游标的使用。
使用游标对记录集循环进行处理的时候一般操作如以下几个步骤：
1、把记录集传给游标（定义游标）
2、打开游标
3、开始循环
4、从游标中取值
5、检查那一行被返回
6、处理
7、关闭循环
8、关闭游标

一、ORCAL的游标
1.其游标的遍历有两种：
⑴For 循环游标
循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
For 变量名 In 游标名
Loop
数据处理语句；
End Loop；
⑵Loop循环游标
。。。
Loop
Fatch 游标名InTo 临时记录或属性类型变量；
Exit When 游标名%NotFound；
End Loop；
实例：
loop遍历：
Declare
Cursor myCur is select ename,job,sal,empno from emp;
varE myCur%rowType;
Begin
if myCur%isopen = false then
open myCur;
dbms_output.put_line('Opening...');
end if;
loop
fetch myCur into varE;
exit when myCur%notfound;
dbms_output.put_line(myCur%rowCount||' '||vare.empno||' '||vare.ename||' '||vare.sal);
end loop;
if myCur%isopen then
Close myCur;
dbms_output.put_line('Closing...');
end if;
End;

for遍历：
Declare
Cursor myCur is select * from emp;
Begin
for varA in myCur
loop
dbms_output.put_line(myCur%rowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
end loop;
End;

二、DB2游标
有两种定义方式：
注意 commit和rollback
使用游标时要特别注意如果没有加with hold 选项,在Commit和Rollback时,该游标将被关闭。Commit 和Rollback有很多东西要注意。特别小心。
1.第一种
declare continue handler for not found
begin
set v_notfound = 1;
end;
declare cursor1 cursor with hold for select market_code from tb_market_code for update;
open cursor1;
set v_notfound=0;
fetch cursor1 into v_market_code;
while v_notfound=0 Do
--work
set v_notfound=0;
fetch cursor1 into v_market_code;
end while;
close cursor1;
这种方式使用起来比较复杂，但也比较灵活。特别是可以使用 with hold 选项。如果循环内有commit或rollback 而要保持该cursor不被关闭，只能使用这种方式。

注：实际上这种还有些复杂，可以简化：

begin

declare ....... --定义游标;

open .... ---开启游标;

while .... do

fetch cursor1 into v_market_code;

----work;

p1:begin

declare ......

.......

end p1;

end while;

colse .... --关闭游标

end;
2.第二种
pcursor1: for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
end for;
这种方式的优点是比较简单，不用（也不允许）使用 open,fetch,close。
但不能使用with hold 选项。如果在游标循环内要使用commit,rollback则不能使用这种方式。如果没有commit或rollback的要求，推荐使用这种方式(看来For这种方式有问题)。

修改游标的当前记录的方法
update tb_market_code set market_code='0' where current of cursor1;
不过要注意将cursor1定义为可修改的游标
declare cursor1 cursor for select market_code from tb_market_code
for update;

for update 不能和 GROUP BY、 DISTINCT、 ORDER BY、 FOR READ ONLY及UNION, EXCEPT, or INTERSECT 但 UNION ALL除外）一起使用。

实例：
CREATE OR REPLACE PROCEDURE "WGHYXXM_2"."PROC_SQL_ADDR"
()
LANGUAGE SQL
SPECIFIC SQL191014013422827
BEGIN
--特别注意：过程中循环游标时，while语句就是循环，所以要去掉loop
declare np_addrs VARCHAR(30);
declare rel_org VARCHAR(30);
declare p_addrs cursor with hold FOR SELECT cust_id from GD_P_CUST_ADDR_HIS where cust_location_bd is NULL;
open p_addrs;
-- LOOP
fetch p_addrs into np_addrs; --所有地址为空的客户
while np_addrs is not null DO
begin
DECLARE rel_loc_db CURSOR for SELECT BRANCH_ID from GD_P_CUST_MANGER_BRANCH_REL where CUST_ID = np_addrs;
open rel_loc_db;
-- LOOP
fetch rel_loc_db into rel_org;
while rel_org is not null DO
begin
declare cust_dbx NUMERIC ;
declare cust_dby NUMERIC;
declare loc_dbs cursor with hold for SELECT db2gse.st_x(LOCATION_BD) , db2gse.st_y(LOCATION_BD) from FND_BANK_INFO where ORG_ID = rel_org;
open loc_dbs;
-- LOOP
fetch loc_dbs into cust_dbx , cust_dby;
while cust_dbx is not null do
BEGIN
UPDATE GD_P_CUST_ADDR_HIS set CUST_LOCATION_BD = db2gse.st_POINT(cust_dbx , cust_dby,1) where CUST_ID = np_addrs;
end;
end while;
-- end loop;
close loc_dbs;
end;
end while;
-- end loop;
close rel_loc_db;
end;
end while;
-- end LOOP;
close p_addrs;
END