显示游标:
1).声明列变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select POLICYNO from t_cmcs_test;
--V_POLICYNO VARCHAR2(30);定义方式1
--V_POLICYNO t_cmcs_test.policyno%TYPE; 定义方式2
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST
INTO V_POLICYNO;
EXIT WHEN CUR_TEST%NOTFOUND;
DBMS_OUTPUT.put_line(V_POLICYNO);
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;
2).声明行变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from t_cmcs_test;
V_POLICYNO t_cmcs_test%ROWTYPE; --方式1 (用表名定义行变量)
V_POLICYNO2 CUR_TEST%ROWTYPE; --方式2(用游标定义行变量)
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST
INTO V_POLICYNO;
EXIT WHEN CUR_TEST%NOTFOUND;
DBMS_OUTPUT.put_line(V_POLICYNO.POLICYNO);
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;
3).使用BULK COLLECT 的游标
--行变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from t_cmcs_test;
TYPE T_REN_STS IS TABLE OF t_cmcs_test%ROWTYPE;--只能定义成这样,不能按照以上定义的方式定义
V_POLICYNO T_REN_STS;
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_POLICYNO LIMIT 1000;
FOR I IN 1 .. V_POLICYNO.COUNT LOOP
DBMS_OUTPUT.put_line(V_POLICYNO(I).POLICYNO);
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 policyno from t_cmcs_test;
TYPE T_REN_STS IS TABLE OF t_cmcs_test.policyno%TYPE;
V_POLICYNO T_REN_STS;
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_POLICYNO LIMIT 1000;
FOR I IN 1 .. V_POLICYNO.COUNT LOOP
DBMS_OUTPUT.put_line(V_POLICYNO(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 policyno from t_cmcs_test;
V_POLICYNO t_cmcs_test.policyno%type;
BEGIN
for c in CUR_TEST loop
DBMS_OUTPUT.put_line(c.policyno);
end loop;
END MY_TEST;
--行定义变量
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST IS
select * from t_cmcs_test;
V_POLICYNO t_cmcs_test%rowtype;
BEGIN
for c in CUR_TEST loop
DBMS_OUTPUT.put_line(c.policyno);
end loop;
END MY_TEST;
5)含有参数的游标
CREATE OR REPLACE PROCEDURE MY_TEST IS
CURSOR CUR_TEST(mypolicy varchar2) IS
select * from t_cmcs_test g where g.policyno=mypolicy;
TYPE T_REN_STS IS TABLE OF t_cmcs_test%rowTYPE;
V_POLICYNO T_REN_STS;
BEGIN
OPEN CUR_TEST('PDDH201621021600000001');
LOOP
FETCH CUR_TEST BULK COLLECT
INTO V_POLICYNO LIMIT 1000;
FOR I IN 1 .. V_POLICYNO.COUNT LOOP
DBMS_OUTPUT.put_line(V_POLICYNO(I).policyno);
END LOOP; -- for in loop
EXIT WHEN CUR_TEST%NOTFOUND;
END LOOP;
CLOSE CUR_TEST;
END MY_TEST;
19.动态游标
-- --T_REN_STS 为列变量
CREATE OR REPLACE PROCEDURE P_DALIAN IS
--定义动态游标
TYPE T_STS4201 IS REF CURSOR;
CURSOR_STS4201 T_STS4201;
--定义列变量
TYPE T_REN_STS IS TABLE OF t_cmcs_test.policyno%TYPE;
V_REN_STS T_REN_STS;
--接受游标列变量循环的参数
V_policyno t_cmcs_test.policyno%TYPE;
BEGIN
OPEN CURSOR_STS4201 FOR 'SELECT g.policyno FROM t_cmcs_test g ';
LOOP
FETCH CURSOR_STS4201 BULK COLLECT
INTO V_REN_STS LIMIT 2;
FOR I IN 1 .. V_REN_STS.COUNT LOOP
V_policyno := V_REN_STS(I);
DBMS_OUTPUT.put_line(V_policyno);
END LOOP; -- for in loop
COMMIT;
EXIT WHEN CURSOR_STS4201%NOTFOUND;
END LOOP;
CLOSE CURSOR_STS4201;
END P_DALIAN;
--T_REN_STS 为行变量
CREATE OR REPLACE PROCEDURE P_DALIAN IS
--定义动态游标
TYPE T_STS4201 IS REF CURSOR;
CURSOR_STS4201 T_STS4201;
--定义变量
TYPE T_REN_STS IS TABLE OF t_cmcs_test%ROWTYPE;
V_REN_STS T_REN_STS;
--接受游标列变量循环的参数
V_policyno t_cmcs_test.Policyno%TYPE;--定义成列变量
V_policyno2 t_cmcs_test%ROWTYPE;--定义成行变量
BEGIN
OPEN CURSOR_STS4201 FOR 'SELECT * FROM t_cmcs_test g ';
LOOP
FETCH CURSOR_STS4201 BULK COLLECT
INTO V_REN_STS LIMIT 2;
--V_REN_STS和游标查询的内容有关,若查询行,则要定义为行变量;若查询列,定义成列变量
FOR I IN 1 .. V_REN_STS.COUNT LOOP
V_policyno := V_REN_STS(I).Policyno;--定义成列变量,则V_REN_STS后需要取出字段名
V_policyno2 := V_REN_STS(I);--定义成行变量,则V_REN_STS中的每一行赋值给V_policyno2
DBMS_OUTPUT.put_line(V_policyno);
DBMS_OUTPUT.put_line(V_policyno2.policyno);
DBMS_OUTPUT.put_line(V_REN_STS(I).policyno);
END LOOP; -- for in loop
COMMIT;
EXIT WHEN CURSOR_STS4201%NOTFOUND;
END LOOP;
CLOSE CURSOR_STS4201;
END P_DALIAN;
总之,定义的变量和游标查询要一致。行对行,列对列。