Oracle PL/SQL (8) - 游标Cursor的基本用

作者: 乘风破浪的姐姐 | 来源:发表于2020-04-23 10:15 被阅读0次

Oracle PL/SQL (8) - 游标Cursor的基本用
Oracle PL/SQL (7) - 游标Cursor的基本用
PL/SQL——游标(CURSOR)
PL/SQL
JAVA学习笔记（PL/SQL）
PL_SQL_Oracle基础教程.pdf
PL/SQL基础
19年第46周：数据库中的游标Cursor
pl/sql游标
oracle工具PL/SQL快速入门1简介

1、使用显示游标修改数据
在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERE CURRENT OF子句专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子句，当使用FOR UPDATE子句打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT…FOR UPDATE操作。

语法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[NOWAIT]

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

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

在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：

WHERE{CURRENT OF cursor_name|search_condition}

示例：

DELCARE
   CURSOR curs IS 
       SELECT empno,salary FROM emp
       WHERE comm IS NULL
       FOR UPDATE OF comm;
   v_comm NUMBER(10,2);
BEGIN
   FOR r1 IN curs LOOP
       IF r1.salary<500 THEN
           v_comm:=r1.salary*0.25;
　　      ELSEIF r1.salary<1000 THEN
           v_comm:=r1.salary*0.20;
　　      ELSEIF r1.salary<3000 THEN
           v_comm:=r1.salary*0.15;
       ELSE
           v_comm:=r1.salary*0.12;
       END IF;
       UPDATE emp SET comm=v_comm WHERE CURRENT OF curs ;
   END LOOP;
END

where current of 子句只能在定义游标的时候使用了 for update语句才可以使用。
上述例子中，以下 where current of 子句已限制了查询条件，更新只是当前记录集。

       CURSOR curs IS 
       SELECT empno,salary FROM emp WHERE comm IS NULL  FOR UPDATE OF comm;

r1是curs中的一行数据，更新的时候用CURRENT OF curs，因为r1只是一个隐式游标，它本身只代表了循环中curs的当前记录。所以update语句或者delete语句的作用范围就只在你循环的当前行范围中了。

WHERE CURRENT OF与ROWID定位的性能哪个高？可以参考文章：https://blog.csdn.net/yoursly/article/details/79331750
2、游标变量
游标与游标变量是不同的，就像常量和变量的关系一样。游标是静态的，游标变量是动态的，因为它不与特定的查询绑定在一起。
语法：

TYPE ref_type_name IS REF CURSOR
   [ RETURN return_type];

说明：
游标变量类型有强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。
return_type为游标变量的返回值类型，它必须为记录变量。
示例1：

declare
  type ref_type_cursor is ref cursor;
  v_cursor   ref_type_cursor;
  claim_record t_claim%ROWTYPE;
  v_no t_claim.accident_no%type:='claim0317001';
begin
 open v_cursor for select * from t_claim where accident_no=v_no;
 loop
     fetch v_cursor into claim_record;
     exit when v_cursor%notfound;
     dbms_output.put_line('定损单：'||claim_record.claim_id||'   定损金额：'||claim_record.total_estimate_amount);
 end loop;
 close v_cursor;
end;

输出结果：
定损单：532 定损金额：9252
定损单：533 定损金额：4667

示例2：
方法一：

DECLARE
  claim_record          t_claim%rowtype;
  TYPE ref_type_cursor IS REF CURSOR RETURN t_claim%rowtype;
  v_cursor            ref_type_cursor;
  v_no t_claim.accident_no%type:='claim0317001';
BEGIN
   OPEN v_cursor FOR select * from t_claim where accident_no=v_no;
   LOOP
       FETCH v_cursor INTO claim_record;
       EXIT WHEN v_cursor%NOTFOUND;
       dbms_output.put_line('定损单：'||claim_record.claim_id||'   定损金额：'||claim_record.total_estimate_amount);
   END LOOP;
   CLOSE v_cursor;
END;

方法二：
DECLARE
Type claim_record_type IS RECORD(
     claim_id t_claim.claim_id%TYPE,
     total_estimate_amount t_claim.total_estimate_amount%TYPE
   );
claim_record claim_record_type;
v_no t_claim.accident_no%type:='claim0317001';

TYPE ref_type_cursor IS REF CURSOR RETURN claim_record_type;
v_cursor            ref_type_cursor;
BEGIN
   OPEN v_cursor FOR select claim_id,total_estimate_amount from t_claim where accident_no=v_no;
   LOOP
       FETCH v_cursor INTO claim_record;
       EXIT WHEN v_cursor%NOTFOUND;
       dbms_output.put_line('定损单：'||claim_record.claim_id||'   定损金额：'||claim_record.total_estimate_amount);
   END LOOP;
   CLOSE v_cursor;
END;

输出结果：
定损单：532 定损金额：9252
定损单：533 定损金额：4667

3、使用游标批量获取
使用BULK COLLECT，我们可以用对数据库的一个来回，返回多行数据。
BULK COLLECT减少了PL/SQL和SQL引擎之间的上下文开关数目，因而加速了数据获取的速度。
语法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

示例：

DECLARE
    CURSOR claim_cursor(claimid number) IS SELECT * FROM t_claim WHERE claim_id = claimid;
    TYPE type_claim_table IS TABLE OF t_claim%ROWTYPE INDEX BY BINARY_INTEGER;
    claim_table type_claim_table;
    v_claimid t_claim.claim_id%TYPE;
BEGIN
    v_claimid := 521;
    OPEN claim_cursor(v_claimid);
    FETCH claim_cursor BULK COLLECT INTO claim_table;
    CLOSE claim_cursor;
    FOR i IN 1..claim_table.COUNT LOOP
        dbms_output.put_line('定损单：'||claim_table(i).claim_no||' 定损金额：'||claim_table(i).total_estimate_amount);
    END LOOP;
END;

输出结果：
定损单：claim313001 定损金额：3506

4、游标表达式
游标表达式作用是用于返回嵌套游标。语法：

CURSOR(sub_query)

示例：

DECLARE
      CURSOR claim_cursor(claimid number) IS 
  SELECT claim_no,cursor(SELECT * FROM t_claim_wf e WHERE e.claim_id = d.claim_id) 
  FROM t_claim d WHERE claim_id = claimid;
      TYPE claim_wf_cursor_type IS REF CURSOR;
      claim_wf_cursor claim_wf_cursor_type;
      claim_wf_record t_claim_wf%ROWTYPE;
      v_claimid t_claim.claim_id%TYPE;
      v_claimno t_claim.claim_no%TYPE;

BEGIN
     v_claimid := 521;
    OPEN claim_cursor(v_claimid);
    loop 
         FETCH claim_cursor INTO v_claimno,claim_wf_cursor;
         EXIT WHEN claim_cursor%NOTFOUND;
         dbms_output.put_line('定损单：'||v_claimno);
         LOOP
             FETCH claim_wf_cursor INTO claim_wf_record;            
             EXIT WHEN claim_wf_cursor%NOTFOUND;
             dbms_output.put_line('定损单状态：'||claim_wf_record.claim_status||'，用户id：'||claim_wf_record.user_id);
         END LOOP;
    end loop;
    CLOSE claim_cursor;
END;

输出结果：
定损单：claim313001
定损单状态：06，用户id：10011800