游标—cursor

游标
使用SQL编写查询语句时，所有的查询结果会直接显示给用户，但是在很多情况下，用户需要对返回结果中的每一条数据分别进行操作，则这个时候普通的查询语句就无法使用了，那么就可以通过结果集（由查询语句返回完整的行集合叫做结果集）来接收，之后就可以利用游标来进行操作可以指定某一个结果集中的特定行位置，或者直接修改当前位置中的数据等，可以以编程的方式来访问数据库。

游标分为以下两种类型：
静态游标：结果集已经确实（静态定义）的游标。分为隐式和显示游标。
隐式游标和显式游标区别在于declare中有没有定义
隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息；
显示游标：用户显式声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标；
REF游标：动态关联结果集的临时对象。

单行隐式游标
DECLARE
v_empRow emp%ROWTYPE ; – 保存emp每行记录
BEGIN
SELECT * INTO v_empRow FROM emp WHERE empno=7369 ;
IF SQL%FOUND THEN – 发现数据
DBMS_OUTPUT.put_line(‘雇员姓名：’ || v_empRow.ename || ‘，职位：’ || v_empRow.job) ;
END IF ;
END ;
/

创建显式游标
CURSOR 游标名称([参数列表]) [RETURN 返回值类型] IS 子查询 [FOR UPDATE [OF 数据列, 数据列,)] [NOWAIT]];

两种创建游标操作方法，一种使用循环，一种使用取数据方式
DECLARE
CURSOR cur_emp IS SELECT * FROM emp ;
v_empRow emp%ROWTYPE ;
BEGIN
IF cur_emp%ISOPEN THEN – 游标已经打开
NULL ;
ELSE – 游标未打开
OPEN cur_emp ; – 打开游标
END IF ;
FETCH cur_emp INTO v_empRow ; – 取出游标当前行数据
WHILE cur_emp%FOUND LOOP – 判断是否有数据
DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || ‘、雇员姓名：’ ||
v_empRow.ename || ‘，职位：’ || v_empRow.job || ‘，工资：’ || v_empRow.sal) ;
FETCH cur_emp INTO v_empRow ;-- 把游标指向下一行
END LOOP ;
CLOSE cur_emp ; – 关闭游标
END ;
/

DECLARE
CURSOR cur_emp IS SELECT * FROM emp ;
BEGIN
FOR v_empRow IN cur_emp LOOP
DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || ‘、雇员姓名：’ ||v_empRow.ename || ‘，职位：’ || v_empRow.job || ‘，工资：’ || v_empRow.sal) ;
END LOOP ;
END ;
/

取整个表的数据
使用嵌套表接收游标数据
DECLARE
TYPE dept_nested IS TABLE OF dept%ROWTYPE ; – 定义dept的嵌套表类型
v_dept dept_nested ;
CURSOR cur_dept IS SELECT * FROM dept ; – 定义游标
BEGIN
IF cur_dept%ISOPEN THEN – 游标已经打开
NULL ;
ELSE – 游标未打开
OPEN cur_dept ; – 打开游标
END IF ;
FETCH cur_dept BULK COLLECT INTO v_dept ; – 保存整个游标
CLOSE cur_dept ; – 关闭游标
FOR x IN v_dept.FIRST … v_dept.LAST LOOP
DBMS_OUTPUT.put_line(‘部门编号：’ || v_dept(x).deptno || ‘，部门名称：’ ||
v_dept(x).dname
|| ‘，部门位置：’ || v_dept(x).loc) ;
END LOOP ;
END ;
/

取得部分数据保存在数组之中
DECLARE
TYPE dept_varray IS VARRAY(2) OF dept%ROWTYPE ;
v_dept dept_varray ;
v_rows NUMBER := 2 ; – 每次提取的行数
v_count NUMBER := 1 ; – 每次少显示1条记录
CURSOR cur_dept IS SELECT * FROM dept ; – 定义游标
BEGIN
IF cur_dept%ISOPEN THEN – 游标已经打开
NULL ;
ELSE – 游标未打开
OPEN cur_dept ; – 打开游标
END IF ;
FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows ; – 保存指定行数
CLOSE cur_dept ; – 关闭游标
FOR x IN v_dept.FIRST … (v_dept.LAST - v_count) LOOP
DBMS_OUTPUT.put_line(‘部门编号：’ || v_dept(x).deptno || ‘，部门名称：’ ||
v_dept(x).dname
|| ‘，部门位置：’ || v_dept(x).loc) ;
END LOOP ;
END ;
/

FOR UPDATE子句会将游标提取出来的数据进行行级锁定
FOR UPDATE [OF 列,列…]此语句将为游标中的数据列增加行级锁定

下例中的更新是更新全部数据
DECLARE
CURSOR cur_emp IS SELECT * FROM emp
WHERE deptno=10 ;
BEGIN
FOR v_empRow IN cur_emp LOOP
UPDATE emp SET sal=9999 ;
END LOOP ;
END ;
/
加入以下声明即可按要求进行修改
DECLARE
CURSOR cur_emp IS SELECT * FROM emp
WHERE deptno=10 FOR UPDATE OF sal,comm ;
BEGIN
FOR v_empRow IN cur_emp LOOP
UPDATE emp SET sal=9999 WHERE CURRENT
OF cur_emp ;
END LOOP ;
END ;
/

FOR UPDATE NOWAIT不等待游标
DECLARE
CURSOR cur_emp IS SELECT * FROM emp
WHERE deptno=10 FOR UPDATE NOWAIT;
BEGIN
FOR v_empRow IN cur_emp LOOP
UPDATE emp SET sal=9999 WHERE
empno=v_empRow.empno ;
END LOOP ;
END ;
/

动态游标
定义游标变量类型
CURSOR 游标变量类型名称 IS REF CURSOR [RETURN 数据类型] ;
DECLARE
TYPE dept_ref IS REF CURSOR RETURN dept%ROWTYPE ; – 定义游标类型
cur_dept dept_ref ; – 定义游标变量
v_deptRow dept%ROWTYPE ; – 定义行类型
BEGIN
OPEN cur_dept FOR SELECT * FROM dept ; – 打开游标
LOOP
FETCH cur_dept INTO v_deptRow ; – 取得游标数据
EXIT WHEN cur_dept%NOTFOUND ; – 如果没有数据则退出
DBMS_OUTPUT.put_line(‘部门名称：’ || v_deptRow.dname || ‘，部门位置：’ ||
v_deptRow.loc) ;
END LOOP ;
CLOSE cur_dept ; – 关闭游标
END ;
/