PL/SQL学习笔记(游标)

游标的概念

PL/SQL用游标（CURSOR）来管理SQL的select语
游标是为处理这些语句而分配的一大块内存
游标的一个常见的用法是保存查询结果以备以后使用
一个游标结果集是通过执行SELECT 查询来建立的
数据库游标允许你选择一组数据，通过翻阅这组数据记录（通常被称为数据集），检查每一个游标所在的特定的行。

游标的分类:
 静态游标
 显式游标 (explicit cursor)
 隐式游标 (implicit cursor)
 REF游标（即游标变量）

显式游标:
 显式游标是由用户显式声明的游标
 显式游标的操作有声明（declare），使用前的打开（open），使用，使用后的关闭（close）四部分
SET SERVEROUT ON
DECLARE
     v_ename  EMP.ENAME%TYPE;
     v_salary  EMP.SAL%TYPE;
     CURSOR c_emp IS SELECT ename,sal FROM emp;
BEGIN
     OPEN c_emp;
     FETCH c_emp INTO v_ename,v_salary;
     DBMS_OUTPUT.PUT_LINE('Salary of Employee  '|| v_ename ||'  is  '|| v_salary);
     FETCH c_emp INTO v_ename,v_salary;
     DBMS_OUTPUT.PUT_LINE('Salary of Employee  '|| v_ename ||'  is  '|| v_salary);
     FETCH c_emp INTO v_ename,v_salary;
     DBMS_OUTPUT.PUT_LINE('Salary of Employee  '|| v_ename ||'  is  '|| v_salary);
     CLOSE c_emp;
 END;
 /
显式游标的属性:
%FOUND 如果最近最后一次从当前游标中抽取出数据，则返回TRUE；否则返回FALSE
%NOTFOUND 与%FOUND相反
%ROWCOUNT 当前总共已抽取的记录数
%ISOPEN  如果游标已经打开，则返回TRUE；否则返回FALSE

DECLARE
       v_ename   EMP.ENAME%TYPE;
       v_salary   EMP.SAL%TYPE;
       CURSOR  c_emp  IS  SELECT ename,sal FROM emp;
BEGIN
       OPEN c_emp;
       LOOP
           FETCH c_emp INTO v_ename,v_salary;
           EXIT WHEN c_emp%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE('Salary of Employee　'|| v_ename ||'is' ||v_salary);
      END LOOP;
      CLOSE c_emp;
END;
/

隐式游标:
PL/SQL为所有SQL数据操纵语句（包括返回一行的查询）隐式声明游标
不能对一个SQL游标(隐式游标)显式的执OPEN， CLOSE和FETCH语句
Oracle会自动的为隐式游标 --打开SQL游标、处理SQL游标、然后再关闭该游标

隐式游标的属性:

隐式游标的属性名虽然与显式游标的属性名一致，可含义不尽相同，请大家仔细区分：
%FOUND  如果DML语句影响一行或多行，则与该条语句配套的隐式游标的%FOUND属性返回TRUE，否则返回FALSE
%NOTFOUND 与%FOUND相反
%ROWCOUNT 统计DML语句返回的行数
%ISOPEN  隐式游标关联的SQL语句执行完后，ORACLE自动关闭该游标。因此，%ISOPEN属性的值始终为FALSE

DECLARE
BEGIN
    DELETE FROM EMP WHERE DEPTNO=40;
    IF sql%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('未找到值');
    ELSE
            DBMS_OUTPUT.PUT_LINE('共删除' || sql%ROWCOUNT || '行');
    END IF;
END;
 /

REF游标 :
REF游标可在运行时与不同的查询相关联
REF游标也可称为游标变量
声明一个引用游标类型，语法如下： 
 TYPE  <type  name>  IS  REF CURSOR  [RETURN <record type>]
使用REF游标
DECLARE
   TYPE RefCur IS REF CURSOR RETURN dept%rowtype;
   Cur1  RefCur;
BEGIN
   open Cur1  for select * from dept;
   close  Cur1;
－－error,Cur1中的结果集结构必须是dept%rowtype
－－open Cur1 for select * from emp;
    close  Cur1;
END;

/
带参数的游标:可以将参数传递给游标并在查询中使用
CURSOR cursor_name[(parameter[,parameter],...)]
IS select_statement;

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

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

游标中的更新和删除

DECLARE
      CURSOR c1 IS
              SELECT empno,sal  FROM emp   WHERE comm IS NULL
              FOR UPDATE OF comm;   -- 所更新的列名
v_comm NUMBER(10,2);
BEGIN
      FOR r1 IN c1 LOOP
              IF r1.sal<500 THEN
                      v_comm:=r1.sal*0.25;
              ELSE
                      v_comm:=r1.sal*0.20;
              END IF;
              
              UPDATE emp
              SET comm=v_comm
              WHERE CURRENT OF c1;
      END LOOP;
END;
/