oracle学习笔记(4)-游标

CURSOR是oracle系统在内存中开辟的一块工作区,其中存放select语句返回的查询结果;
使用游标时,select记过可以是任意条记录;
游标工作去中存在着一个指针(POINTER),默认指向第一条记录;

游标的类型:
显示游标:用户定义,用于处理select语句返回的多条记录;
隐式游标:系统自动进行操作,用于处理DML和返回一条记录的select语句;

定义游标:
CURSOR cursor_name IS select_statement;
说明
游标必须在PL/SQL块的声明部分进行定义；
游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；
定义游标时并没有生成数据，只是将定义信息保存到数据字典中；
游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量.
打开游标:
OPEN cursor_name;
说明:
检查变量的值
执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。
游标指针指向第一个元组
一旦游标打开，就无法再次打开，除非先关闭
如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。
检索游标:
FETCH cursor_name INTO variable_list|record_variable,
说明
在使用FETCH语句之前必须先打开游标
对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。
游标指针只能向下移动，不能回退
INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应
关闭游标:
CLOSE cursor_name;
例子:根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定:

DECLARE
	v_deptno emp.deptno%TYPE;
	CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;
	v_emp c_emp%ROWTYPE;
BEGIN
        v_deptno:=&x;
    OPEN c_emp;
    LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '|| v_emp.ename||' '||  v_emp.sal ||'  '||  v_deptno);                                   
    END LOOP; 
    CLOSE c_emp;
END; 	

显示游标的属性:
%ISOPEN
布尔型。如果游标已经打开，返回TRUE,否则为FALSE。
%FOUND
布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;
%NOTFOUND
布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;
%ROWCOUNT
数值型，返回到目前为止从游标缓冲区检索的元组数。
%BULK_ROWCOUNT（i）
数值型，用于取得FORALL语句执行批绑定操作时第i个元素所影响的行数。
循环检索游标:
利用简单循环检索游标
DECLARE
CURSOR cursor_name IS SELECT…;
BEGIN
   OPEN cursor_name;
   LOOP
  FETCH…INTO…;
EXIT WHEN cursor_name%NOTFOUND;
……
   END LOOP;
   CLOSE cursor_name;
END; 
注意:EXIT WHEN子句应该是FETCH…INTO语句的下一条语句.
例子:输出各部门的平均工资;

DECLARE
    CURSOR cursor_dept IS select AVG(sal) deptno,avg_sal from emp group by deptno;
     v_dept cursor_dept%ROWTYPE;
BEGIN
    OPEN cursor_dept;
    LOOP
    FETCH cursor_dept INTO v_dept;
        EXIT WHEN cursor_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_dept.avg_sal || '' || v_dept.deptno);
    END LOOP;
    CLOSE cursor_dept;
END;

利用WHILE循环检索游标
DECLARE
CURSOR cursor_name IS SELECT…;
BEGIN
OPEN cursor_name;
FETCH…INTO…;
WHILE cursor_name%FOUND LOOP
FETCH…INTO…;
……
END LOOP;
CLOSE cursor;
END;
注意:在循环体外进行一次FETCH操作，作为第一次循环的条件。
例子:输出部门的平均工资;

DECLARE
    CURSOR cursor_dept IS select AVG(sal) deptno,avg_sal from emp group by deptno;
    v_dept cursor_dept%ROWTYPE;
BEGIN
    OPEN cursor_dept;
    FETCH cursor_dept INTO v_dept;
    WHILE cursor_dept%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_dept.avg_sal || ' ' || v_dept.deptno);
        FETCH cursor_dept INTO v_dept;
    END LOOP;
    CLOSE cursor_dept;
END;

利用FOR循环检索游标
DECLARE
CURSOR cursor_name IS SELECT…;
BEGIN
FOR loop_variable IN cursor_name LOOP
  ……
END LOOP;
END; 
由于用FOR循环检索游标时，游标的打开、数据的检索、是否检索到数据的判断以及游标的关闭都是自动进行的，因此，可以不在声明部分定义游标，而在FOR语句中直接使用子查询。
例子:输出部门号位10的员工姓名;

BEGIN
    FOR v_emp IN (select * from emp where deptno=10) 
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||  v_emp.ename);
    END LOOP;
END;

利用游标更新或删除数据:
CURSOR cursor_name IS
SELECT select_list_item FROM table  FOR UPDATE
[OF column_reference] [NOWAIT]; 
打开游标时对相应的表加锁（通常SELECT操作不在数据上设置任何锁），其他用户不能对该表进行DML操作；
若数据对象已经被其他会话加锁，则当前会话挂起等待（默认状态），若指定了NOWAIT子句，则不等待，返回ORACLE错误。
对于多表查询时，可以通过OF子句指定某个要加锁的表的列的形式，对特定的表加锁，而其他表不加锁；否则所有表都加锁。 
当用户执行COMMIT或ROLLBACK操作时，数据上的锁会自动被释放。

例子:修改员工的工资，如果员工的部门号为10，则工资提高100；如果部门号为20，则工资提高150；如果部门号为30，则工资提高200；否则工资提高250。 

DECLARE
    CURSOR c_emp IS SELECT * FROM emp FOR UPDATE;-----------
    v_increment NUMBER;
BEGIN
    FOR v_emp IN c_emp LOOP
        CASE v_emp.deptno 
        WHEN 10 THEN v_increment:=100;
        WHEN 20 THEN v_increment:=150;
        WHEN 30 THEN v_increment:=200;
        ELSE         v_increment:=250;
        END CASE;
        UPDATE emp SET sal=sal+v_increment WHERE CURRENT OF c_emp;-----------
    END LOOP;
    COMMIT;
END;