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

1. 游标概念
   使用游标可以让用户像操作数组一样操作查询出来数据结果集，它提供了一种从集合性质的结果中提取单条记录的手段。

游标(Cursor)可以看作一个变动的光标。它实际上是一个指针，它在一段Oracle存放数据查询结果集的内存中，它可以指向结果集中的任意记录，初始是指向首记录。像数组的结构。

2、游标基本原理
在PL/SQL块中执行select，insert，update和delete语句时，Oracle会在内存中为其分配上下文区（Context Area），用数据库语言来描述游标就是：映射在上下文区结果集中一行数据上的位置实体。即一个缓冲区。
游标是指向该区的一个指针，或是命名一个工作区，或是一种结构化数据类型。它为应用程序提供了一种具有多行数据查询结果集中的每一行数据分别进行单独处理的方法。
用户可以使用游标访问结果集中的任意一行数据，将游标指向某行后，即可对该行数据进行操作。游标为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

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

游标可分为显式游标和隐式游标两类。
显示游标：由用户声明和操作的一种游标；是指在使用之前有明确的游标声明和定义，这样游标定义会关联数据查询语句，通常会返回一行或多行。打开游标后，用户可以利用游标的位置对结果集进行任何操作。
隐式游标：Oracle为所有数据操作语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游标，其数量由数据库初始参数文件的open cursors参数定义。它被PL/SQL自动关联，也叫SQL游标，由Oracle管理，用户无法控制但可以得到它的属性信息。

3、显式游标使用步骤
1）声明/定义游标，语法： CURSOR cursor_name IS select_statement;
2）打开游标，执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集。语法：OPEN cursor_name
3）读取数据，检索结果集合中的数据行，放入指定的输出变量中。每一次提取数据后，游标都指向结果集的下一行。语法：FETCH cursor_name INTO variable[,variable,...]
4）关闭游标，当处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源。关闭游标后不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。语法：CLOSE cursor_name;

完整示例（不带参数的游标）：

DECLARE
  -- 定义游标
  CURSOR c_cursor IS 
  SELECT cp_code, cp_name ,cp_level FROM company;   

  -- 声明变量
  v_cp_code     company.cp_code%TYPE;
  v_cp_name     company.cp_name%TYPE;   
  v_cp_level    company.cp_level%TYPE;   

BEGIN
 -- 打开游标
 OPEN c_cursor;
 -- 获取数据
 FETCH c_cursor INTO v_cp_code, v_cp_name,v_cp_level;
 -- 处理数据
 WHILE c_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_cp_code||'---'||v_cp_name||'---'||v_cp_level);
    FETCH c_cursor INTO v_cp_code, v_cp_name,v_cp_level;
 END LOOP;
 -- 关闭游标
 CLOSE c_cursor;
END;

输出结果：
17000000---广西分公司---2
18000000---湖南分公司---2
19000000---浙江分公司---2
21000000---四川分公司---2
22000000---苏州分公司---2
25000000---陕西分公司---2

带参数的游标
与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。语法如下：

CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

定义参数的语法如下：

Parameter_name [IN] data_type[{:=|DEFAULT} value]

在打开游标时给参数赋值，语法如下：

OPEN cursor_name[value[,value]....];

完整示例（带参数的游标）：

declare 
  cursor company_cursor(v_company_id number) is 
     select cp_code,cp_name from company where company_id = v_company_id order by cp_code;
  v_dno number;
begin
  v_dno := &no;
  for company_record in company_cursor(v_dno) loop
        dbms_output.put_line('公司CODE：'||company_record.cp_code||'，公司名称：'||company_record.cp_name);
  end loop;
end;

DECLARE
   -- 定义RECORD记录类型
   TYPE user_record_type IS RECORD(
        v_account   user.account%TYPE,
        v_username   user.username%TYPE);
   -- 声明记录变量
   v_user_record user_record_type;
   -- 定义游标，有参数与返回值
   CURSOR user_cursor(u_id NUMBER,c_id NUMBER) 
      RETURN user_record_type
   IS
      SELECT account, username FROM user 
      WHERE user_id >= u_id and company_id >= c_id;
     
   v_userid number:=10011702;
   v_companyid number:=10001414;

BEGIN
   -- 打开游标，传递参数值
   OPEN user_cursor(v_userid,v_companyid); 
   LOOP
      FETCH user_cursor INTO v_user_record;    -- 获取数据
      IF user_cursor%FOUND THEN
         DBMS_OUTPUT.PUT_LINE('账号：'||v_user_record.v_account||'的姓名：'||v_user_record.v_username);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT; -- 处理完则退出循环
      END IF;
   END LOOP;
   CLOSE user_cursor;   --关闭游标
END;

输出结果：
账号：18000113的姓名：18000113
账号：GUILIN363678900000000的姓名：GUILIN363678900000000
账号：tester的姓名：tester
账号：system的姓名：system
已经处理完结果集了
上述查询结果，同以下语句：

select * from user where user_id >= 10702 and company_id >= 100014;

4. 显式游标属性

游标的状态（如是否打开，获取了多少行数据等）可以使用游标属性来获取。
游标属性以“%属性名”的形式加在游标名之后。显式游标属性有：

image.png

DECLARE 
   v_claimno           emb.claim.claimno%type;  
   v_estimateamt       emb.claim.estimate_amt%type;  
   -- 定义游标
   CURSOR c_cursor IS SELECT claimno, estimate_amt FROM claim WHERE claimid>760; 
BEGIN
    -- 打开游标
   OPEN c_cursor;
   LOOP
      -- 获取数据
      FETCH c_cursor INTO v_claimno, v_estimateamt;
      EXIT WHEN c_cursor%NOTFOUND;  -- 未读取到记录，则退出循环
       DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor%ROWCOUNT||'----------定损单号：'||v_claimno);
   END LOOP;
    
   -- 关闭游标
   CLOSE c_cursor;
END;

输出结果：
记录数:1----------定损单号：54107827
记录数:2----------定损单号：54107846
记录数:3----------定损单号：54107845
记录数:4----------定损单号：d20200311008
记录数:5----------定损单号：l20200401002
记录数:6----------定损单号：d20200311009

5. 基于游标定义记录变量

使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名和列别名。

为了简化显式游标的数据处理，建议使用基于游标的记录变量存放游标数据。基于游标定义记录变量，比声明记录类型变量要方便，不容易出错。

示例：

DECLARE 
   CURSOR c_cursor IS SELECT * FROM claim WHERE claimid>760; 
   claim_row claim%ROWTYPE;
BEGIN
   OPEN c_cursor;
    LOOP
      FETCH c_cursor INTO claim_row;
      EXIT WHEN c_cursor%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE('定损单:'||claim_row.claimno||',定损金额:'||claim_row.estimate_amt);       
    END LOOP;
   CLOSE c_cursor;
END;

输出结果：
定损单:54107827,定损金额:7106.94
定损单:54107846,定损金额:1772.94
定损单:54107845,定损金额:1768.67
定损单:dre20200311008,定损金额:1481.09
定损单:lxl20200401002,定损金额:3089.94

6. 游标FOR循环
   游标FOR循环是显示游标的一种快捷使用方式，它依次读取结果集中的行数据，当FOR循环开始时，游标自动打开（不需要OPEN）,每循环一次系统自动读取游标当前行的数据（不需要FETCH)，当退出FOR循环时，游标被自动关闭（不需要使用CLOSE）使用游标FOR循环的时候不能使用OPEN语句，FETCH语句和CLOSE语句，否则会产生错误。
   语法：

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标处理语句
END LOOP;

示例（上述示例用FOR循环）：

DECLARE
    CURSOR claim_cur(claimid number) IS
        SELECT * FROM claim WHERE claimid>claimid;
BEGIN
    FOR claim IN claim_cur(760) LOOP
        DBMS_OUTPUT.PUT_LINE('定损单:'||claim.claimno||',定损金额:'||claim.estimate_amt);
    END LOOP;
END;

输出结果：
定损单:54107827,定损金额:7106.94
定损单:54107846,定损金额:1772.94
定损单:54107845,定损金额:1768.67
定损单:d20200311008,定损金额:1481.09
定损单:l20200401002,定损金额:3089.94