Oracle游标概念及使用

游标的概念:
游标位于内存中的 "临时表"。 具体如下：游标是从数据表中提取出来的数据，以 临时表 的形式存放到 内存中，在游标中有一个 数据指针， 在初始状态下指向的是首记录，利用 fetch 语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回到数据库中。游标用来查询数据库，获取记录集合（结果集）的指针，可以让开发者 一次访问一行结果集， 在每条结果集上作操作。

一、语法及属性

1.隐式游标

在 PL/SQL 中使用 DML 和 select into时，会自动创建隐式游标，隐式游标自动声明、打开和关闭（无法手动查看），其名为 SQL，通过检查隐式游标的属性可以获得 最近执行的 DML 和 select into 语句的信息

数据准备
INTO stu(s_id, s_xm) VALUES (1, '小游子')
INTO stu(s_id, s_xm) VALUES (2, '小优子') 


总共 4 个步骤，缺一不可：（参数可选）
DECLARE
   CURSOR cur_stu(参数值 参数类型) IS SELECT * FROM stu t [WHERE t.id = 参数值]; -- 步骤1: 声明游标
   v_stu  cur_stu%ROWTYPE;
BEGIN
   OPEN cur_stu(参数值); -- 步骤2: 打开游标
   
   LOOP 
     FETCH cur_stu INTO v_stu; -- 步骤3: 提取数据
     EXIT WHEN cur_stu%NOTFOUND;
       dbms_output.put_line(v_stu.s_id ||' : '||v_stu.s_xm);
   END LOOP;
   
   CLOSE cur_stu; -- 步骤4: 关闭游标
   
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

2.动态游标

DECLARE
   v_sql VARCHAR(2000);
   v_b1 NUMBER(3) := 3;
   v_id system.stu.s_id%TYPE;
   v_xm system.stu.s_xm%TYPE;
   -- TYPE cur_stu_type IS REF CURSOR;
   -- cur_stu cur_stu_type;
   cur_stu SYS_REFCURSOR;
BEGIN
   v_sql := 'SELECT t.s_id, t.s_xm FROM stu t WHERE t.s_id <= :b1';

   OPEN cur_stu FOR v_sql
      USING v_b1; -- 绑定变量 : 大数据处理常用优化手段

   LOOP
      FETCH cur_stu
         INTO v_id, v_xm;
      EXIT WHEN cur_stu%NOTFOUND;
      dbms_output.put_line('序号：' || v_id || chr(10) || '姓名：' || v_xm);
   END LOOP;
   
   CLOSE cur_stu;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

3.带参数的游标

declare  
isok integer;  
v_event_id number(10);  
v_isagain number(2);  
v_rate number(2);  
  
v_sender char(11) := '13800138000';  
  
cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 声明游标  
  
begin  
    open cursorVar(v_sender);    -- 打开游标，在括号里传参。  
    loop  
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
         exit when cursorVar%notfound;                             --当没有记录时退出循环  
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
    end loop;  
     
    close cursorVar;   -- 关闭游标  
end;

4.属性

属性	返回值类型	说明
SQL%ISOPEN	布尔型	游标是否开启， true：开启，false：关闭
SQL%FOUND	布尔型	前一个 fetch 语句是否有值，true：有，false：没有
SQL%NOTFOUND	布尔型	与上述相反，常被用于 退出循环，true：有，false：没有, null : 空。注意哦，只有 为 true 时，才退出（当 第一次 fetch 为 null 时，不会退出！）
SQL%ROWCOUNT	整型	当前成功执行的数据行数（非总记录数

DECLARE
   CURSOR cur_stu IS SELECT * FROM stu;
   v_stu cur_stu%ROWTYPE;
BEGIN

   OPEN cur_stu;

   LOOP
      FETCH cur_stu INTO v_stu;
      EXIT WHEN cur_stu%NOTFOUND; -- sql%notfound
      
      IF cur_stu%FOUND THEN -- sql%found
         dbms_output.put_line(v_stu.s_id || ' : ' || v_stu.s_xm);
         dbms_output.put_line('当前记录条数：' || cur_stu%ROWCOUNT); -- sql%rowcount
      ELSE 
         dbms_output.put_line('无记录...');
      END IF;
   END LOOP;

   CLOSE cur_stu; 

EXCEPTION
   WHEN OTHERS THEN
      IF cur_stu%ISOPEN THEN -- sql%isopen
         CLOSE cur_stu;
      END IF;
      
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

二、特别说明 sql%notfound

1、fetch … into 语句有数据时，会覆盖 into 变量后的值
2、fetch … into 语句无数据时，into 变量的值不改变（为最后一次有数据的值），就像 select … into 如果没有数据会报异常，但是不会把 into 后面的变量置为空一样。