11-02 07:36 阅读 152

关注

ORACLE 游标基本结构

10. 游标基本结构

游标只是一个指向查询语句返回的结果的指针，因此在游标定义时，将包含一个查询定义；

游标实际上指向一块内存区域，位于进程全局区内部，称为上下文区域（context area）;

游标定义时并不会获取游标数据，只有在游标打开后，游标相关的SQL查询语句被执行，然后将检索到的结果保存到内存中；

• 游标的四大属性

1. SQL%FOUND：找到数据

2. SQL%NOTFOUND：未找到数据

3. SQL%ISOPEN：是否打开游标

4. SQL%ROWCOUNT：执行行数

• 显示游标注意事项

1. 定义、

2. 打开（OPEN NAME_CUR）、

3. 循环（FETCH NAME_CUR INTO ....）、

4. 判断（EXIT WHEN NAME_CUR%NOTFOUND）

5. 循环结束关闭游标（CLOSE NAME_CUR）

1. 上下文区域的3类信息

   -- 使用游标
   -- 显示使用游标： 定义、打开游标提取数据、关闭
   SELECT * FROM TEST6;
   
   
   DECLARE
     V_TEST6 TEST6%ROWTYPE;  -- 行记录类型变量
     CURSOR t_cur IS         -- 定义游标 游标只能使用IS  视图只能使用AS
       SELECT * FROM TEST6;
   BEGIN
     open t_cur;  -- 打开游标
     LOOP
       FETCH t_cur
         INTO V_TEST6;
       DBMS_OUTPUT.put_line('编号' || V_TEST6.id||
                            ',名字' || v_test6.name||
                            ',年龄' || v_test6.age||
                            ',性别' || v_test6.sex||
                            ',拼音' || v_test6.ename);
       EXIT WHEN T_CUR%NOTFOUND;   -- 特别注意该处，否则无线循环报错 ORU-20000
     END LOOP;
     close t_cur; -- 循环结束必须关闭游标
   END;复制代码

1. 查询返回的数据行

2. 查询所处理的数据的行号

3. 指向共享池的已分析的SQL语句

2. 游标分类

   -- 游标变量类型
   TYPE CURSOR_TYPE_NAME IS REF CURSOR [RETURN RETURN_TYPE];
   -- 无约束
   TYPE TEST_CUR IS REF CURSOR;
   -- 有约束
   TYPE TEST_CUR IS REF CURSOR RETURN TEST6%ROWTYPE;
   
   -- 打开游标变量
   OPEN TEST_CUR FOR SELECT * FROM TEST6;
   
   DECLARE
   TYPE TEST_CURS IS REF CURSOR;  -- 定义游标类型
   TEST_CUR TEST_CURS;            -- 定义游标变量
   TEST_ROW TEST6%ROWTYPE;
   BEGIN
     OPEN TEST_CUR FOR SELECT * FROM TEST6; -- 打开游标变量
     LOOP
       FETCH TEST_CUR INTO TEST_ROW;        -- 提取游标变量
       EXIT WHEN TEST_CUR%NOTFOUND;
       DBMS_OUTPUT.put_line('编号' || TEST_ROW.id || ',名字' || TEST_ROW.name ||
                            ',年龄' || TEST_ROW.age || ',性别' || TEST_ROW.sex ||
                            ',拼音' || TEST_ROW.ename);
     END LOOP;
   END;

• for update 子句会对用select语句提取出来的结果进行锁定，相当于结果集的行加了一把互斥锁，实行行级锁定。

• WHERE CURRENT OF子句检索的游标一定要有FOR UPDATE子句，并且游标要被打开且至少返回一行，不然触发错误。

• 只是单向的遍历结果集，FOR循环更为方便

• PL/SQL DML语句都创建一个隐式的游标，隐式的打开、处理、关闭游标

  -- 隐式游标
  BEGIN
    UPDATE TEST6 SET SEX = 3555 WHERE ID = 8; 
    DBMS_OUTPUT.put_line(SQL%ROWCOUNT || '行被更新'); -- 隐式游标属性SQL%ROWCOUNT
    IF SQL%NOTFOUND THEN              -- 没有找到将需要更新的行            
      DBMS_OUTPUT.PUT_LINE('没有需要更新的行');
    END IF;
    COMMIT;
  EXCEPTION    
    WHEN OTHERS THEN                  -- 更新报错
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;复制代码

• 显示的定义游标，需要打开游标执行SQL并提取数据，关闭游标

1. FOR UPDATE 字句

2. WHERE CURRENT OF

3. 使用游标更改数据

4. LOOP ... END LOOP

   -- loop ...end loop;
   -- 必须在循环中使用 EXIT WHEN NAME_CUR%NOTFOUN;
   DECLARE
     TEST_ROW TEST6%ROWTYPE;
     CURSOR TEST_CUR IS
       SELECT * FROM TEST6;
   BEGIN
     OPEN TEST_CUR;
     LOOP
       FETCH TEST_CUR
         INTO TEST_ROW;
       EXIT WHEN TEST_CUR%NOTFOUND;
       DBMS_OUTPUT.put_line('编号' || TEST_ROW.id || ',名字' || TEST_ROW.name ||
                            ',年龄' || TEST_ROW.age || ',性别' || TEST_ROW.sex ||
                            ',拼音' || TEST_ROW.ename);
     END LOOP;
     CLOSE TEST_CUR;
   END;复制代码

5. WHILE

   -- WHILE
   -- 需循环前、循环中提取游标数据，
   DECLARE
     TEST_ROW TEST6%ROWTYPE;
     CURSOR TEST_CUR IS
       SELECT * FROM TEST6;
   BEGIN
     OPEN TEST_CUR;
      FETCH TEST_CUR INTO TEST_ROW;    -- 提取游标数据
      WHILE TEST_CUR%FOUND LOOP
       DBMS_OUTPUT.put_line('编号' || TEST_ROW.id || ',名字' || TEST_ROW.name ||
                            ',年龄' || TEST_ROW.age || ',性别' || TEST_ROW.sex ||
                            ',拼音' || TEST_ROW.ename);
       FETCH TEST_CUR INTO TEST_ROW;   -- 提取游标数据
     END LOOP;
     CLOSE TEST_CUR;
   END;复制代码

6. FOR 循环

   -- for 循环游标为显式游标，不同于LOOP/WHILE ，不需要open、fetch、close语句
   -- 会隐式的赋值给test_row变量，不需要fetch
   -- 每一次循环完成，会隐式的使用test_row%notfound 进行判断，为false 则退出for循环
   -- for循环结束后，会隐式的调用close语句关闭游标
   DECLARE
   CURSOR TEST_CUR IS SELECT * FROM TEST6;
   BEGIN
     FOR TEST_ROW IN TEST_CUR LOOP
       DBMS_OUTPUT.put_line('编号' || TEST_ROW.id || ',名字' || TEST_ROW.name ||
                            ',年龄' || TEST_ROW.age || ',性别' || TEST_ROW.sex ||
                            ',拼音' || TEST_ROW.ename);
     END LOOP;
   END;
   
   -- 简洁写法
   DECLARE
   BEGIN
     FOR TEST_ROW IN (SELECT * FROM test6) LOOP
       DBMS_OUTPUT.put_line('编号' || TEST_ROW.id || ',名字' || TEST_ROW.name ||
                            ',年龄' || TEST_ROW.age || ',性别' || TEST_ROW.sex ||
                            ',拼音' || TEST_ROW.ename);
     END LOOP;
   END;复制代码

7. 显式游标

8. 隐式游标

9. 游标定义（显式游标）

   -- 显式游标定义
   CURSOR CURSOR_NAME [PARAMETER_LIST]
   [RETURN RETURN_TYPE]
   IS QUERY
   [FOR UPDATE [OF (COLUMN_LIST)][NOWAIT]];
   -- 1. CURSOR_NAME : 有效的游标名称
   -- 2. PARAMETER_LIST：可选的游标参数，用于查询执行
   -- 3. RETURN RETURN_TYPE：可选的返回数据类型，必须是记录或数据表的行类型
   -- 4. QUERY：可以是任何的SELECT 语句
   -- 5. FOR UPDATE：可选锁定游标记录，其他用户为只读模式
   
   
   SELECT * FROM CK_KPD_HZ T WHERE T.RIQI_DATE>SYSDATE-9;
   
   DECLARE
   V_CKD CK_KPD_HZ%ROWTYPE;
   CURSOR KPD_CUR IS
   SELECT * FROM CK_KPD_HZ T WHERE T.RIQI_DATE>SYSDATE-9;
   
   BEGIN
     OPEN KPD_CUR;
     LOOP
       FETCH KPD_CUR INTO V_CKD;
       DBMS_OUTPUT.PUT_LINE('单据：'||V_CKD.DANJ_NO||',货主：'||V_CKD.YEZ_ID);
     EXIT WHEN KPD_CUR%NOTFOUND ;
     END LOOP;
     CLOSE KPD_CUR;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
       END;复制代码

10. 提取游标数据

    -- 提取游标数据   -- 行类型的嵌套表类型
    -- FETCH BULK COLLECT
    DECLARE
    TYPE TEST_TYPE IS TABLE OF TEST6%ROWTYPE;   -- 定义嵌套表类型
    TEST6_TYPE TEST_TYPE;
    CURSOR TEST_CUR IS SELECT * FROM TEST6;
    BEGIN
      OPEN TEST_CUR;
      FETCH TEST_CUR BULK COLLECT INTO TEST6_TYPE;
      --CLOSE TEST_CUR;
      FOR I IN 1.. TEST6_TYPE.COUNT
        LOOP
          DBMS_OUTPUT.put_line('编号' || TEST6_TYPE(I).id||
                             ',名字' || TEST6_TYPE(I).name||
                             ',年龄' || TEST6_TYPE(I).age||
                             ',性别' || TEST6_TYPE(I).sex||
                             ',拼音' || TEST6_TYPE(I).ename);
        END LOOP;
        CLOSE TEST_CUR;
    END;
    
    
    -- 变长数组类型
    DECLARE
    TYPE TEST_TYPE IS VARRAY(10) OF TEST6%ROWTYPE;
    TEST6_TYPE TEST_TYPE;
    CURSOR TEST_CUR IS SELECT * FROM TEST6;
    V_ROWS INT :=2;
    V_COUNT INT :=0;
    BEGIN
      OPEN TEST_CUR;
      LOOP
        FETCH TEST_CUR BULK COLLECT INTO TEST6_TYPE LIMIT V_ROWS;
        EXIT WHEN TEST_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('姓名：');
        FOR I IN 1 .. (TEST_CUR%ROWCOUNT - V_COUNT)
          LOOP
            DBMS_OUTPUT.PUT_LINE(TEST6_TYPE(I).NAME);
          END LOOP;
          DBMS_OUTPUT.new_line;
          V_COUNT := TEST_CUR%ROWCOUNT;
      END LOOP;
      CLOSE TEST_CUR;
      END;复制代码

11. 操作数据循环

12. 修改游标数据

    [FOR UPDATE [OF (COLUMN_LIST)] [NOWAIT]];
    -- COLUMN_LIST 需要锁定的列
    -- NOWAIT 如果所检索的行已被锁定，将不会等待，而是立即返回
    CURSOR TEST_CUR IS SELECT * FROM TEST6 FOR UPDATE ID,NAME;复制代码

    -- WHERE CURRENT OF 子句
    WHERE CURRENT OF CURSORNAME;
    -- CURSORNEM 为当前使用FOR UPDATE子句的游标名称，用来更新游标数据；复制代码

    -- 使用游标更新数据
    DECLARE
    CURSOR TEST_CUR(T_ID IN NUMBER)
    IS
    SELECT * FROM TEST6 WHERE ID = T_ID FOR UPDATE;
    BEGIN
      FOR TEST_ROW IN TEST_CUR(8)
        LOOP
          UPDATE TEST6 SET SEX = 77 WHERE CURRENT OF TEST_CUR;
        END LOOP;
        COMMIT;
      END;
      
    -- 使用游标删除数据
    DECLARE
    CURSOR TEST_CUR(T_ID IN NUMBER)
    IS
    SELECT * FROM TEST6 WHERE ID = T_ID FOR UPDATE;
    BEGIN
      FOR TEST_ROW IN TEST_CUR(8)
        LOOP
          DELETE FROM TEST6 WHERE CURRENT OF TEST_CUR; 
        END LOOP;
        COMMIT;
      END;复制代码

13. 游标变量

作者：隆多9
链接：https://juejin.cn/post/7025907293648584718