游标简介
原理：逐行处理查询结果，以编程的方式访问数据。用游标提取一行，数据保存到游标中，一次处理一行。
应用场景：在代码中需要接收多行查询出来的记录，借助游标遍历结果集。常与过程一起使用。
游标的类型：隐式、显示、REF游标
一、隐式游标
在PL/SQL中使用DML语句时自动创建隐式游标,即：所有的SQL 语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQL cursor)，与显式游标不同，SQL游标不被程序打开和关闭，通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：SQL+属性
1. %FOUND – SQL 语句影响了一行或多行时为 TRUE
2. %NOTFOUND – SQL 语句没有影响任何行时为TRUE
3. %ROWCOUNT – SQL 语句影响的行数
4. %ISOPEN - 游标是否打开，始终为FALSE
SQL> SETSERVEROUTPUT ON
SQL> BEGIN
UPDATE toys SET toyprice=270
WHERE toyid= 'P005';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘表已更新');
END IF;
END; --只有在DML 语句影响一行或多行时，才返回True
隐式游标用的很少，而且Oracle数据库自动创建。
二、显式游标
显式游标在PL/SQL 块的声明部分定义查询，该查询可以返回多行
显式游标的使用：
如果要使用创建好的游标，需要先打开游标，语法结构如下：
open 游标名;
打开游标的过程有以下两个步骤：
（1）将符合条件的记录送入内存。
（2）将指针指向第一条记录。
游标打开了要关闭：CLOSE 游标名；
游标提取数据的操作（每次只能提取一行）
如果要提取游标中的数据，需要使用fetch命令，语法形式如下。
fetch 游标名 into 变量名1, 变量名2,……;
或fetch 游标名into 记录型变量名;
示例代码
set serveroutput on
declare tempsalscott.emp.sal%type;
cursormycursor is
select* from scott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin tempsal:=800;
openmycursor;
fetchmycursor into cursorrecord; --抓取每行数据into到变量cursorrecord
dbms_output.put_line(cursorrecord.deptno);
end;
带参数的显式游标
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
desig VARCHAR2(20);
emp_codeVARCHAR2(5);
empnm VARCHAR2(20);
CURSORemp_cur(desigparam VARCHAR2) IS --参数desigparam
SELECT empno, ename FROM employee
WHERE designation=desigparam;
BEGIN
desig:='&desig';
OPENemp_cur(desig); --open时，带参数
LOOP
FETCHemp_cur INTO emp_code,empnm;
EXITWHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm);
END LOOP;
CLOSEemp_cur;
END;
循环游标
循环游标用于简化游标处理代码
当用户需要从游标中提取所有记录时使用
不需要显示打开和关闭游标
循环游标的语法如下：
FOR<record_index> IN <cursor_name>
LOOP
<executable statements>
END LOOP;
注意：<record_index> 名字可以不需要定义，直接使用
因为是Oracle隐含定义的变量名
SQL> SET SERVER OUTPUT ON
SQL> DECLARE
CURSOR mytoy_cur IS
SELECT toyid, toyname, toyprice
FROM toys;
BEGIN
FOR toy_rec IN mytoy_cur
LOOP
DBMS_OUTPUT.PUT_LINE(
‘玩具编号：'||' '||toy_rec.toyid||' '
||‘玩具名称：'||' '||toy_rec.toyname||' '
||‘玩具单价：'||' '||toy_rec.toyprice);
END LOOP;
END;
三、REF 游标
REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
创建游标变量需要两个步骤：
1. 声明 REF 游标类型
2. 声明 REF 游标类型的变量
用于声明 REF 游标类型的语法为：
TYPE<ref_cursor_name> IS REF CURSOR
[RETURN<return_type>];
DECLARE
TYPE toys_curtype IS REF CURSOR --声明ref游标
RETURN toys%ROWTYPE;
toys_curvar toys_curtype;
toys_rec toys%ROWTYPE;
BEGIN
OPEN toys_curvar FOR
SELECT * FROM toys;
FETCH toys_curvar INTO toys_rec;
...
CLOSE toys_curvar;
END;
例：
...................................................................................................................................
CREATE OR REPLACE PROCEDUREUPDATE_SAL99 --创建 存储过程。
(
P_MIN IN NUMBER --参数
, P_MAX IN NUMBER
, P_dno IN NUMBER
)
AS
V_average number; --声明变量
V_sal emp.sal%type;
cursor C_emp(n number) is --声明游标
select sal from emp where deptno=n for update;
BEGIN
select avg(sal) into V_average from emp whereDEPTNO=P_dno; --查出员工平均工资，赋给变量V_average
open c_emp(P_dno); --打开游标
loop --利用游标，循环读取每行数据
fetch c_emp into V_sal;
exit when c_emp%notfound; --当游标为空，则退出循环
ifv_sal > V_average then --如果>平均工资，给员工工资增长P_max
update emp set sal=sal+p_max where current of c_emp;
else
update emp set sal=sal+p_min where current of c_emp;
endif;
endloop;
close c_emp;
commit; --提交
exception
whenothers then
rollback; --异常处理：任何异常都 回滚
NULL;
END UPDATE_SAL99;
...........................................................................................
--调用
execute UPDATE_SAL99(20,10000,20000); --调用过程，传递实参
select ename, sal from emp where deptno = 20;
游标变量的优点和限制
游标变量的功能强大，可以简化数据处理。
游标变量的优点有：
1. 可从不同的 SELECT 语句中提取结果集
2. 可以作为过程的参数进行传递
3. 可以引用游标的所有属性
4. 可以进行赋值运算
使用游标变量的限制：
1. 不能在程序包中声明游标变量
2. FOR UPDATE子句不能与游标变量一起使用
3. 不能使用比较运算符
总结：
游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF游标
隐式游标由 PL/SQL自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF游标时，不需要将 SELECT语句与其关联