6.5PLSQL中的游标

1、游标

A.游标概论

游标是一个私有的SQL工作区域，Oracle数据库中有两种游标，分别是隐式游标和显式游标， 隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行提交的SQL 语句； 而显式游标是程序员在程序中显式声明的；通常游标均指显式游标。

B.隐式游标

SQL%ROWCOUNT 受最近的SQL语句影响的行数
SQL%FOUND 最近的SQL语句是否影响了一行以上的 数据
SQL%NOTFOUND 最近的SQL语句是否未影响任何数据
SQL%ISOPEN 对于隐式游标而言永远为FALSE

VARIABLE rows_deleted VARCHAR2(30)
DECLARE
 v_employee_id employees.employee_id%TYPE := 176;
BEGIN
 DELETE FROM employees
 WHERE employee_id = v_employee_id;
 :rows_deleted := (SQL%ROWCOUNT ||
 ' row deleted.');
END;
/
PRINT rows_deleted

C.显式游标

对于返回多行结果的SQL语句的返回结果，可使用显式游标独立的处理器中每一行的数据。
显式游标的相关函数可以做到：
1、一行一行的处理返回的数据。
2、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动；

D.在程序中对显式游标控制的一般过程

DECLARE
 v_empno employees.employee_id%TYPE;
 v_ename employees.last_name%TYPE;
 CURSOR emp_cursor IS
 SELECT employee_id, last_name
 FROM employees;
 BEGIN
 OPEN emp_cursor;
 LOOP
 FETCH emp_cursor INTO v_empno, v_ename;
 EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
 emp_cursor%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)
 ||' '|| v_ename);
 END LOOP;
 CLOSE emp_cursor;
END ;

For循环进行游标的遍历，For循环省去了游标的 声明、打开、提取、测试、关闭等语句

FOR record_name IN cursor_name LOOP
 statement1;
 statement2;
 . . .
END LOOP;

BEGIN
 FOR emp_record IN (SELECT last_name, department_id
 FROM employees) LOOP
 -- implicit open and implicit fetch occur
 IF emp_record.department_id = 80 THEN
 ...
 END LOOP; -- implicit close occurs
END;

DECLARE
 CURSOR emp_cursor IS
 SELECT last_name, department_id
 FROM employees;
BEGIN
 FOR emp_record IN emp_cursor LOOP
 -- implicit open and implicit fetch occur
 IF emp_record.department_id = 80 THEN
 ...
 END LOOP; -- implicit close occurs
END;
/

E.游标带参数

CURSOR cursor_name
 [(parameter_name datatype, ...)]
IS
 select_statement;

DECLARE
 CURSOR emp_cursor
 (p_deptno NUMBER, p_job VARCHAR2) IS
 SELECT employee_id, last_name
 FROM employees
 WHERE department_id = p_deptno
 AND job_id = p_job;
BEGIN
 OPEN emp_cursor (80, 'SA_REP');
 . . .
 CLOSE emp_cursor;
 OPEN emp_cursor (60, 'IT_PROG');
 . . .
END;

F.FOR UPDATE NOWAIT语句

有的时候打开一个游标是为了更新或者删除一些记录，这种情况下希望在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败就停止不再继续，以免出现长时间等待资源的死锁情况。

SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT];

DECLARE
 CURSOR emp_cursor IS
 SELECT employee_id, last_name, department_name
 FROM employees,departments
 WHERE employees.department_id =
 departments.department_id
 AND employees.department_id = 80
 FOR UPDATE OF salary NOWAIT;

G.WHERE CURRENT OF cursor

经常要逐条处理游标中的每一条记录，在循环体内做Update 或者 Delete 时需要有Where指向游标的当前记录可以使用WHERE CURRENT OF cursor

DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
 and d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor
LOOP
 IF emp_record.salary < 5000 THEN
 UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
 END IF;
END LOOP;
END;
/