游标(存储过程)

显示游标：

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;

总之，定义的变量和游标查询要一致。行对行，列对列。