【Oracle】PL/SQL 显式游标、隐式游标、动态游标

2022-10-19 19:51:23

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，Oracle会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

对于不同的SQL语句，游标的使用情况不同：

SQL语句

游标

非查询语句

隐式的

结果是单行的查询语句

隐式的或显示的

结果是多行的查询语句

显示的

处理显式游标

例：

  1. DECLARE
  2. CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --1、声明游标,有参数没有返回值
  3. IS
  4. SELECT first_name f_name, hire_date FROM employees
  5. WHERE department_id = dept_id AND job_id = j_id;
  6. --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
  7. v_emp_record c4%ROWTYPE;
  8. BEGIN
  9. OPEN c4(90, 'AD_VP');             --2、打开游标,传递参数值
  10. LOOP
  11. FETCH c4 INTO v_emp_record;    --3、提取游标fetch into
  12. IF c4%FOUND THEN
  13. DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
  14. ||v_emp_record.hire_date);
  15. ELSE
  16. DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
  17. EXIT;
  18. END IF;
  19. END LOOP;
  20. CLOSE c4;                         --4、关闭游标
  21. END;

退出LOOP或者用：

EXIT WHEN c4%NOTFOUND;

游标属性：

Cursor_name%FOUND     布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

Cursor_name%NOTFOUND   布尔型属性，与%FOUND相反；——注意区别于DO_DATA_FOUND(select
into抛出异常)

Cursor_name%ISOPEN     布尔型属性，当游标已打开时返回 TRUE；

Cursor_name%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

游标的for循环

PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；

  1. 当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
  2. 当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
  3. 当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

格式：

  FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标数据处理代码
  END LOOP;

其中：

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。

例：

  1. DECLARE
  2. CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
  3. IS
  4. SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
  5. BEGIN
  6. --当dept_no参数值为30
  7. FOR c1_rec IN c_cursor(30) LOOP
  8. DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
  9. END LOOP;
  10. --使用默认的dept_no参数值10
  11. FOR c1_rec IN c_cursor LOOP
  12. DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
  13. END LOOP;
  14. END;

或者可以在游标FOR循环语句中使用子查询

  1. BEGIN
  2. FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP
  3. DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
  4. END LOOP;
  5. END;

处理隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；

而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，隐式游标的名字为SQL，这是由ORACLE 系统定义的。

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

格式调用为： SQL%

  1. DECLARE
  2. v_rows NUMBER;
  3. BEGIN
  4. --更新数据
  5. UPDATE employees SET salary = 30000
  6. WHERE department_id = 90 AND job_id = 'AD_VP';
  7. --获取默认游标的属性值
  8. v_rows := SQL%ROWCOUNT;
  9. DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
  10. --删除指定雇员；如果部门中没有雇员，则删除部门
  11. DELETE FROM employees WHERE department_id=v_deptno;
  12. IF SQL%NOTFOUND THEN
  13. DELETE FROM departments WHERE department_id=v_deptno;
  14. END IF;
  15. END;

更新或删除当前游标数据

游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。

如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作；对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-0054 ：resource busy  and  acquire with nowait specified.

  1. DECLARE
  2. V_deptno employees.department_id%TYPE :=&p_deptno;
  3. CURSOR emp_cursor
  4. IS
  5. SELECT employees.employee_id, employees.salary
  6. FROM employees WHERE employees.department_id=v_deptno
  7. FOR UPDATE NOWAIT;                    --1、for update
  8. BEGIN
  9. FOR emp_record IN emp_cursor LOOP
  10. IF emp_record.salary < 1500 THEN
  11. UPDATE employees SET salary=1500
  12. WHERE CURRENT OF emp_cursor; --2、WHERE CURRENT OF cursor_name子句
  13. END IF;
  14. END LOOP;
  15. END;

动态游标

与游标一样，动态游标（游标变量）也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。

游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

    1. DECLARE
    2. --定义一个游标数据类型
    3. TYPE emp_cursor_type IS REF CURSOR;
    4. --声明一个游标变量
    5. c1 EMP_CURSOR_TYPE;
    6. --声明两个记录变量
    7. v_emp_record employees%ROWTYPE;
    8. v_reg_record regions%ROWTYPE;
    9. BEGIN
    10. OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
    11. LOOP
    12. FETCH c1 INTO v_emp_record;
    13. EXIT WHEN c1%NOTFOUND;
    14. DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
    15. ||v_emp_record.hire_date);
    16. END LOOP;
    17. --将同一个游标变量对应到另一个SELECT语句
    18. OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
    19. LOOP
    20. FETCH c1 INTO v_reg_record;
    21. EXIT WHEN c1%NOTFOUND;
    22. DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
    23. ||v_reg_record.region_name);
    24. END LOOP;
    25. CLOSE c1;
    26. END;
上一篇：ASP.NET MVC 学习笔记-4.ASP.NET MVC中Ajax的应用


下一篇：一文了解 Apache Hive 联邦查询（Query Federation）