存储过程使用游标(显示游标)

此文为初学存储过程总结的，如有问题请以标准教材或资料为准。仅供参考。

游标(存储过程)
显示游标：
1).声明列变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select TESTNO from mytest;
--V_TESTNO VARCHAR2(30);定义方式1
--V_TESTNO mytest.TESTNO%TYPE; 定义方式2
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST
INTO V_TESTNO;
EXIT WHEN CUR_TEST%NOTFOUND;

DBMS_OUTPUT.put_line(V_TESTNO);
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;

2).声明行变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from mytest;
V_TESTNO mytest%ROWTYPE; --方式1 (用表名定义行变量)
V_TESTNO2 CUR_TEST%ROWTYPE; --方式2（用游标定义行变量）
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST
INTO V_TESTNO;
EXIT WHEN CUR_TEST%NOTFOUND;
DBMS_OUTPUT.put_line(V_TESTNO.TESTNO);
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;

3）.使用BULK COLLECT 的游标
--行变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from mytest;
TYPE T_REN_STS IS TABLE OF mytest%ROWTYPE;--只能定义成这样，不能按照以上定义的方式定义
V_TESTNO T_REN_STS;
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_TESTNO LIMIT 1000;
FOR I IN 1 .. V_TESTNO.COUNT LOOP
DBMS_OUTPUT.put_line(V_TESTNO(I).TESTNO);
END LOOP; -- for in loop
EXIT WHEN CUR_TEST%NOTFOUND;
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;
--列变量
CREATE OR REPLACE PROCEDURE MY_TEST IS

CURSOR CUR_TEST IS
select TESTNO from mytest;
TYPE T_REN_STS IS TABLE OF mytest.TESTNO%TYPE;
V_TESTNO T_REN_STS;
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_TESTNO LIMIT 1000;
FOR I IN 1 .. V_TESTNO.COUNT LOOP
DBMS_OUTPUT.put_line(V_TESTNO(I));
END LOOP; -- for in loop
EXIT WHEN CUR_TEST%NOTFOUND;
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;
4）for loop 游标（不需要打开关闭游标）
--列定义变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select TESTNO from mytest;
V_TESTNO mytest.TESTNO%type;
BEGIN
for c in CUR_TEST loop
DBMS_OUTPUT.put_line(c.TESTNO);
end loop;
END MY_TEST;
--行定义变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from mytest;
V_TESTNO mytest%rowtype;
BEGIN
for c in CUR_TEST loop
DBMS_OUTPUT.put_line(c.TESTNO);
end loop;
END MY_TEST;
5）含有参数的游标
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST(mypolicy varchar2) IS
select * from mytest g where g.TESTNO=mypolicy;
TYPE T_REN_STS IS TABLE OF mytest%rowTYPE;
V_TESTNO T_REN_STS;
BEGIN
OPEN CUR_TEST('PDDH201621021600000001');
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_TESTNO LIMIT 1000;
FOR I IN 1 .. V_TESTNO.COUNT LOOP
DBMS_OUT

PUT.put_line(V_TESTNO(I).TESTNO);
END LOOP; -- for in loop
EXIT WHEN CUR_TEST%NOTFOUND;
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;