ORACLE 游标基本结构

隆多9
772 阅读5分钟

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类信息

    1. 查询返回的数据行
    2. 查询所处理的数据的行号
    3. 指向共享池的已分析的SQL语句
    -- 使用游标
-- 显示使用游标： 定义、打开游标提取数据、关闭
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;
​

  2. 游标分类

    1. 显式游标

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

    2. 隐式游标

      • 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;

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

      -- 显式游标定义
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;

    4. 提取游标数据

      -- 提取游标数据   -- 行类型的嵌套表类型
-- 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;

    5. 操作数据循环

      1. 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;

      2. 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;

      3. FOR 循环

        • 只是单向的遍历结果集，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;

    6. 修改游标数据

      1. FOR UPDATE 字句

      • for update 子句会对用select语句提取出来的结果进行锁定，相当于结果集的行加了一把互斥锁，实行行级锁定。
      [FOR UPDATE [OF (COLUMN_LIST)] [NOWAIT]];
-- COLUMN_LIST 需要锁定的列
-- NOWAIT 如果所检索的行已被锁定，将不会等待，而是立即返回
CURSOR TEST_CUR IS SELECT * FROM TEST6 FOR UPDATE ID,NAME;

      1. WHERE CURRENT OF

      • WHERE CURRENT OF子句检索的游标一定要有FOR UPDATE子句，并且游标要被打开且至少返回一行，不然触发错误。
      -- WHERE CURRENT OF 子句
WHERE CURRENT OF CURSORNAME;
-- CURSORNEM 为当前使用FOR UPDATE子句的游标名称，用来更新游标数据；
      1. 使用游标更改数据
      -- 使用游标更新数据
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;

    7. 游标变量

    -- 游标变量类型
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;

\

avatar
文章
阅读
粉丝