Oracle学习笔记之游标详解
游标
游标存在意义：解决“select *”返回空、多行记录问题，但凡select，就可能多行结果集，也就需要用游标。
游标分4步走：cursor、open、fetch、close
可能省略open、close，用for ... in ... loop ... end loop;
1、静态游标：
（1）隐式游标：
在PL/SQL中DML（select into、insert、update、delete）时，Oracle隐式定义一个叫SQL的游标，自动声明、打开、关闭。
①sql%found
如果DML语句影响一行或多行，则sql%found为true
例如：
declare
empno emp.empno%type;
begin
select empno into empno -- 对于DML之select into
from emp
where empno =7369;
ifsql%foundthen
dbms_output.put_line('有一行记录');-- 用sql%found、sql%notfound判断是否返回一行记录
endif;
Exception
when no_data_found then
dbms_output.put_line('查询返回空行');-- 用no_data_found判断是否返回空行记录
when too_many_rows then
dbms_output.put_line('查询返回多行');-- 用too_many_rows判断是否返回多行记录
When others then
dbms_output.put_line(‘系统错误');
end;
例如：
declare
total integer;
e_null exception;--自定义的异常
e_toomanyrow exception;
begin
select count(*) into total from emp where emp.deptno=20;
if total>1 then
raise e_toomanyrow;--抛出异常
elsif total<=0 then
raise e_null;
end if;
--捕获并处理异常
Exception when e_toomanyrow then
dbms_output.put_line('Exception：返回多行记录');
when e_null then
dbms_output.put_line('Exception：无返回记录');
when others then
dbms_output.put_line('Exception：其他异常');
end;
②sql%notfound
如果DML语句没有影响任何行，则sql%notfound为true
第一种情况：DML是insert、delete、update
例如：
declare
begin
deletefrom emp
where empno =47;
ifsql%notfoundthen
dbms_output.put_line('未找到值');
else
dbms_output.put_line('已经删除');
endif;
end;
第二种情况：DML是select into
PL/SQL中的“select into”的结果必须“有且只有一行”，该行结果可以赋给列变量、或记录变量
例如：
declare
type type_emp isrecord
(
empno emp.empno%type,
ename emp.ename%type,
dname dept.dname%type,
sal emp.sal%type
);
v_emp type_emp;
begin
select empno, ename, dname, sal into v_emp
from emp innerjoin dept
on emp.deptno = dept.deptno
where ename ='SMITH';
--if sql%notfound then
--dbms_output.put_line('查无此人'); -- 永远执行不到，因为查询返回空行时触发no_data_found异常
--else
dbms_output.put_line(v_emp.empno||'*'||v_emp.ename||'*'||v_emp.dname||'*'||v_emp.sal);
--end if;
exception
whenno_data_foundthen
dbms_output.put_line('查无此人');
whenothersthen
dbms_output.put_line(sqlerrm);
end;
注意：
如果查询返回空行，PL/SQL抛出no_data_found异常；
如果查询返回多行，PL/SQL抛出too_many_rows异常；
所以在DML之“select into”引发异常时，不能使用sql%notfound属性查看DML是否影响了行。
③sql%rowcount
declare
empno emp.empno%type;
begin
select empno into empno
from emp
where empno =7788;
ifsql%rowcount>0then
dbms_output.put_line('从表中选择了'||sql%rowcount||'行');
else
dbms_output.put_line('从表中未选择行');
endif;
end;
④sql%isopen
SQL%ISOPEN返回一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。
（２）显式游标
重点在“步骤”:定义游标，打开游标， 抓取数据，关闭游标；
①游标%found
如果最后一条fetch语句成功提取行，则“游标%found”为true
②游标%notfound
如果最后一条fetch语句未能提取行，则“游标%notfound”为true
例如：
declare
cursor cur is
select ename,job from emp; --这里enamel，job 两列
item emp.ename%type; --定义item是emp表ename列的类型
item2 emp.job%type; --定义item2 是emp表job列类型。 这里声明的变量必须对性查询--出的列对应。
begin
open cur;
loop
fetch cur into item,item2; --捕捉了游标cur 的元素放入，item，item2中。
exitwhencur%notfound; -- 当cur游标都便利完就exit退出。
endloop;
dbms_output.put_line(item||item2);
close cur; --注意关闭游标
end;
③游标%isopen，判断该游标是否被打开；
例如：
Declare
--声明一个游标
cursor cur is
select* from emp where deptno =10;
v_emp cur%rowtype;
begin
ifnot cur%isopenthen
open cur;--如果游标没有被打开，那么就代开游标
endif;
--循环，从游标中抓取数据
loop
fetch cur into v_emp;
exitwhen cur%notfound;--当最后一条数据被获取后，exit退出循环
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
endloop;
close cur;--关闭游标
end;
④游标%rowcount：返回游标查询记录数；
注意：SQL%ROWCOUNT:在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.
例如：（上面例子多了几行代码） declare
cursor cur is
select*
from emp
where deptno =10;
v_emp cur%rowtype;
a number;
begin
ifnot cur%isopenthen
open cur;
endif;
loop
fetch cur into v_emp;
exitwhen cur%notfound;
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
endloop;
-----------------------------------------------------------
--下面是sql%rowcount
select empno into a from emp where empno='7369';
dbms_output.put_line(sql%rowcount);--如果查询sql 有记录数，此时返回1否则为null
dbms_output.put_line(cur%rowcount);--返回游标查询的最终记录数
-----------------------------------------------------------
close cur;
end;
例如：
declare
cursor cur(i varchar2)is
select*from emp where deptno=i ;
item cur%rowtype;
begin
for r in cur('10') -- 注意这里的in。。。 后面跟的是游标 cur 而不是 tiem
--这里可以写成：for admin in cur（&部门编号）；用通配符，使键盘键入内容。
loop
dbms_output.put_line(r.ename|| cur%rowcount);--其中cur%rowcount 是有标明%rowcount 显示游标序列
endloop;
end;
⑤使用显式游标更新或删除行：
declare
-- select语句必须只包括一个表
-- 某表不能是“含有distinct、order by的子查询”查询来的结果集（视图）
cursor 游标名 is select 某列 from 某表 for update[ of 某列];
变量 某表.某列%type;
变量 某表%rowtype;
变量 游标%type;
变量 游标%rowtype;
begin
open 游标;
loop
fetch 游标 into 变量;
exit when 游标%notfound;
if 某条件 then
-- update、delete语句只有在打开游标、提取特定行之后才能使用
-- update语句中使用的列必须出现在“select-for update of”语句中
update 某表 set 某列 = 某值 where current of 游标;
end if;
end loop;
close 游标;
commit;
end;
例如：
-- 如果sal < 2000，长工资1000
declare
cursor cur is
select sal from emp
where sal <2000forupdate;
cursor bbb is
select*from emp;
begin
for v_sal in cur
loop
update emp
set sal = sal +1000
wherecurrentof cur;
endloop;
Commit;
for v_emp in bbb
loop
dbms_output.put_line(v_emp.ename||'*'||v_emp.sal);
endloop;
end;
动态游标：
（1）弱类型游标
例如：
declare -- //弱类型游标
type cursor_type isrefcursor;
cur cursor_type;
temp emp%rowtype;
begin
open cur for
select*from emp;
loop
fetch cur into temp;
exitwhencur%notfound;
dbms_output.put_line(temp.empno||temp.ename);
endloop;
end;
例如：
declare
type cur_type isrefcursor;-- 弱类型游标 通过is ref 定义
cur cur_type;
v_emp emp%rowtype;
v_dept dept%rowtype;
selection varchar2(1):= upper('D');
begin
if selection ='E'then
open cur for
select*from emp;
loop
fetch cur into v_emp;
exitwhen cur%notfound;
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
endloop;
close cur;
elsif selection ='D'then
open cur for
select*from dept;
loop
fetch cur intov_dept;
exitwhen cur%notfound;
dbms_output.put_line(v_dept.deptno||':'||v_dept.dname);
endloop;
close cur;
else
null;
endif;
end;
（2）强类型游标
例如：
declare --强类型游标 强制返回内容
type record_type isrecord(empno number,ename varchar2(20));
type cursor_type isrefcursorreturn record_type;
rec record_type;
cur cursor_type;
begin
open cur for
select empno,ename from emp;
loop
fetch cur into rec;
exitwhen cur%notfound;
dbms_output.put_line(rec.empno||' '||rec.ename);
endloop;
end;
例如：
-- 用强类型游标显示员工姓名和工资
declare
type record_type isrecord(namevarchar2(10), sal number);-- 记录类型
type cur_type isrefcursorreturn record_type;-- 强类型游标，将来必须返回record_type类型的结果集
emp_record record_type;-- 记录类型变量
emp_cur cur_type;-- 强类型游标变量
begin
open emp_cur for
select ename, sal from emp;-- 动态SQL两边的单引号可以省略，必须返回record_type类型的结果集
loop
fetch emp_cur into emp_record;-- 把record_type类型的结果集向record_type类型的变量中填充
exitwhen emp_cur%notfound;
dbms_output.put_line(emp_record.name||':'||emp_record.sal);
endloop;
close emp_cur;
end;
