游标是一个私有的SQL工作区域,Oracle数据库中有两种游标,分别是隐式游标和显式游标, 隐式游标不易被用户和程序员察觉和意识到,实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL 语句; 而显式游标是程序员在程序中显式声明的;通常我们说的游标均指显式游标。
隐式游标的几个有用属性:
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;
显式游标:
1、一行一行的处理返回的数据。
2、保持当前处理行的一个跟踪,像一个指针一样指示当前的处理的记录。
3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动;
此图为显式游标标准的语句
如果你觉得像前面那个例子那样对一个游标的遍历很麻烦的话,可以考虑使用For循环,For循环省去了游标的 声明、打开、提取、测试、关闭等语句,对程序员来说很方便,语法如下:——————两种方法
DECLARE
CURSOR emp_cursor IS
SELECT employee_id
,last_name
,departments.department_name
FROM employees
,departments
WHERE employees.department_id = departments.department_id
AND employees.department_id = 90
FOR UPDATE OF salary NOWAIT ;
BEGIN
FOR emp_data IN emp_cursor LOOP
UPDATE employees e
SET e.salary = 29999
--where e.employee_id = emp_data.employee_id
WHERE CURRENT OF emp_cursor;
END LOOP;
END;
或者:
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 (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;
FOR UPDATE NOWAIT语句:有的时候我们打开一个游标是为了更新或者删除一些记录,这种情况下我们希望 在打开游标的时候即锁定相关记录,应该使用for update 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;
WHERE CURRENT OF cursor :
我们经常要逐条处理游标中的每一条记录,在循环体内做Update 或者 Delete 时需要有Where指向游标的当前记录, 有没有简单一点的的Where条件写法呢?
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;