显示游标
显示游标包括四种属性：
%ISOPEN ：检测游标是否已经打开
%FOUND ：检测游标结果集是否存在数据，存在则返回TRUE
%NOTFOUND ：检测游标结果集是否不存在数据，不存在则返回TRUE
%ROWCOUNT ：返回已提取的实际行数
使用显示游标
定义游标：CURSOR cursor_name IS select_statement；
打开游标：OPEN cursor_name；
提取数据：FETCH cursor_name INTO variable1[,variable2,…];
FETCH INTO每次只能提取一行数据，批量数据需使用循环
使用游标变量接受数据：
SQL> declare
2 cursor emp_cursor is
3 select ename,job,sal from emp where deptno=&dno;
4 vname emp.ename%type;
5 vsal emp.sal%type;
6 vjob emp.job%type;
7 begin Oracle 学习笔记 第 74 页 共 90 页
8 open emp_cursor;
9 loop
10 fetch emp_cursor into vname,vjob,vsal;
11 exit when emp_cursor%notfound;
12 dbms_output.put_line('姓名：'||vname||'，岗位：'||vjob||'，工资：'||vsal);
13 end loop;
14 close emp_cursor;
15 end;
16 /
输入 dno 的值: 30
姓名：ALLEN，岗位：SALESMAN，工资：1600
姓名：WARD，岗位：SALESMAN，工资：1250
姓名：JONES，岗位：MANAGER，工资：3272.5
PL/SQL 过程已成功完成。
使用PL/SQL记录变量接受游标数据：简化单行数据处理
SQL> declare
2 cursor ecur is select ename,sal from emp order by sal desc;
3 erec ecur%rowtype;
4 begin
5 open ecur;
6 loop
7 fetch ecur into erec;
8 exit when ecur%notfound or ecur%rowcount>&n;
9 dbms_output.put_line('姓名：'||erec.ename||'，工资：'||erec.sal);
10 end loop;
11 close ecur;
12 end;
13 /
输入 n 的值: 5
姓名：KING，工资：5000
姓名：FORD，工资：3300
姓名：JONES，工资：3272.5
姓名：BLAKE，工资：2850
姓名：MARY，工资：2000
PL/SQL 过程已成功完成。
使用PL/SQL集合变量接受游标数据：简化多行多列数据处理
SQL> declare
2 cursor ec is select ename,sal from emp where lower(job)=lower('&job');
3 type etype is table of ec%rowtype index by binary_integer;
4 et etype;
5 i int;
6 begin
7 open ec;
8 loop Oracle 学习笔记 第 75 页 共 90 页
9 i:=ec%rowcount+1;
10 fetch ec into et(i);
11 exit when ec%notfound;
12 dbms_output.put_line('姓名：'||et(i).ename||'，工资：'||et(i).sal);
13 end loop;
14 close ec;
15 end;
16 /
输入 job 的值: manager
姓名：JONES，工资：3272.5
姓名：BLAKE，工资：2850
姓名：CLARK，工资：1500
PL/SQL 过程已成功完成。
游标FOR循环
使用游标for循环时，oracle会隐含的打开游标，提取数据并关闭游标
在游标for循环中引用已定义游标：
SQL> declare
2 cursor ec is select ename,hiredate from emp order by hiredate desc;
3 begin
4 for erec in ec loop
5 dbms_output.put_line('姓名：'||erec.ename||'，工作日期：'||erec.hiredate);
6 exit when ec%rowcount=&n;
7 end loop;
8 end;
9 /
输入 n 的值: 3
姓名：MARY，工作日期：
姓名：ADAMS，工作日期：23-5月 -87
姓名：SCOTT，工作日期：01-1月 -84
PL/SQL 过程已成功完成。
在游标for循环中直接引用子查询：
SQL> begin
2 for erec in (select ename,hiredate,rownum from emp order by hiredate) loop
3 dbms_output.put_line('姓名：'||erec.ename||'，工作日期：'||erec.hiredate);
4 exit when erec.rownum=&n;end loop;
5 end;
6 /
输入 n 的值: 2
姓名：ALLEN，工作日期：20-2月 -81
姓名：WARD，工作日期：22-2月 -81
PL/SQL 过程已成功完成。
参数游标：参数只能指定数据类型，不能指定长度，而且必须在where子句中引用参数
SQL> declare Oracle 学习笔记 第 76 页 共 90 页
2 cursor ec(dno number) is select ename,job from emp where deptno=dno;
3 begin
4 for erec in ec(&dno) loop
5 dbms_output.put_line('姓名：'||erec.ename||'，岗位：'||erec.job);
6 end loop;
7 end;
8 /
输入 dno 的值: 30
姓名：ALLEN，岗位：SALESMAN
姓名：WARD，岗位：SALESMAN
姓名：JONES，岗位：MANAGER
PL/SQL 过程已成功完成。
更新游标行
declare
cursor emp_cursor is select ename,sal,deptno from emp for update;
dno int:=&no;
begin
for emp_record in emp_cursor loop
if emp_record.deptno=dno then
dbms_output.put_line(‘姓名：’||emp_record.ename||’，原工资：’||emp_record.sal);
update emp set sal=sal*1.1 where current of emp_cursor;
end if;
end loop;
end;
/
删除游标行
declare
cursor emp_cursor is select ename from emp for update;
name varchar2(10):=lower(‘&name’);
begin
for emp_record in emp_cursor loop
if lower(emp_record.ename)=name then
delete from emp where current of emp_cursor;
else
dbms_output.put_line(‘姓名：’||emp_record.ename);
end if;
end loop;
end;
/
使用for子句在特定表上加共享锁(涉及多张表时的同步问题)
SQL> declare
2 cursor emp_cursor is Oracle 学习笔记第 77 页 共 90 页
select a.dname,b.ename from dept a JOIN emp b ON a.deprno=b.deptno;
3 name varchar2(10):=lower('&name');
4 begin
5 for emp_record in emp_cursor loop
6 if lower(emp_record.dname)=name then
7 dbms_output.put_line(‘姓名：’||emp_record.ename);
8 delete from emp where current of emp_cursor;
9 end if;
10 end loop;
11 end;
12 /
输入 name 的值: sales
PL/SQL过程已完成。
游标变量
游标变量是基于REF CURSOR类型所定义的变量，它实际上是指向内存地址的指针。使用显式游标只能定义静态游标，而通过使用游标变量可以在打开游标时指定其对应的select语句，从而实现动态游标。
使用无返回类型的游标变量
SQL> set serveroutput on
SQL> set verify off
SQL> declare
2 type ref_cursor_type is ref cursor;
3 rc ref_cursor_type;
4 v1 number(6);
5 v2 varchar2(10);
6 begin
7 open rc for
8 select &col1 col1,&col2 col2 from &table where &cond;
9 loop
10 fetch rc into v1,v2;
11 exit when rc%notfound;
12 dbms_output.put_line('col1= '||v1||',col2= '||v2);
13 end loop;
14 close rc;
15 end;
16 /
输入 col1 的值: empno
输入 col2 的值: ename
输入 table 的值: emp
输入 cond 的值: deptno=10
col1= 7782,col2= CLARK
col1= 7839,col2= KING
col1= 7934,col2= MILLER
PL/SQL 过程已成功完成。 Oracle 学习笔记 第 78 页 共 90 页
使用有返回类型的游标变量
SQL> declare
2 type emp_cursor_type is ref cursor return emp%rowtype;
3 ec emp_cursor_type;
4 er emp%rowtype;
5 begin
6 open ec for select * from emp where deptno=&dno;
7 loop
8 fetch ec into er;
9 exit when ec%notfound;
10 dbms_output.put_line('姓名：'||er.ename||'，工资：'||er.sal);
11 end loop;
12 close ec;
13 end;
14 /
输入 dno 的值: 20
姓名：SMITH，工资：800
姓名：JONES，工资：2975
姓名：SCOTT，工资：3000
姓名：ADAMS，工资：1100
姓名：FORD，工资：3000
PL/SQL 过程已成功完成。
使用批量提取
使用fetch…bulk collect提取所有数据;
SQL> declare
2 cursor ec is
3 select * from emp where lower(job)=lower('&job');
4 type etype is table of emp%rowtype;
5 et etype;
6 begin
7 open ec;
8 fetch ec bulk collect into et;
9 close ec;
10 for i in 1..et.count loop
11 dbms_output.put_line('姓名：'||et(i).ename||'，工资：'||et(i).sal);
12 end loop;
13 end;
14 /
输入 job 的值: clerk
姓名：SMITH，工资：800
姓名：ADAMS，工资：1100
姓名：JAMES，工资：950
姓名：MILLER，工资：1300
PL/SQL 过程已成功完成。 Oracle 学习笔记 第 79 页 共 90 页
使用LIMIT子句限制提取行数
SQL> declare
2 cursor ec is select * from emp;
3 type emp_array_type is varray(5) of emp%rowtype;
4 ea emp_array_type;
5 begin
6 open ec;
7 loop
8 fetch ec bulk collect into ea limit &rows;
9 for i in 1..ea.count loop
10 dbms_output.put_line('姓名：'||ea(i).ename||'，工资：'||ea(i).sal);
11 end loop;
12 exit when ec%notfound;
13 end loop;
14 close ec;
15 end;
16 /
输入 rows 的值: 4
姓名：SMITH，工资：800
姓名：ALLEN，工资：1600
姓名：WARD，工资：1250
姓名：JONES，工资：2975
姓名：MARTIN，工资：1250
姓名：BLAKE，工资：2850
姓名：CLARK，工资：2450
姓名：SCOTT，工资：3000
姓名：KING，工资：5000
姓名：TURNER，工资：1500
姓名：ADAMS，工资：1100
姓名：JAMES，工资：950
姓名：FORD，工资：3000
姓名：MILLER，工资：1300
PL/SQL 过程已成功完成。
使用cursor表达式
SQL> declare
2 cursor dept_cursor(no number) is
3 select a.dname,cursor(select * from emp where deptno=a.deptno)
4 from dept a where a.deptno=no;
5 type ref_cursor_type is ref cursor;
6 ec ref_cursor_type;
7 er emp%rowtype;
8 vdname dept.dname%type;
9 begin
10 open dept_cursor(&dno); Oracle 学习笔记第 80 页 共 90 页
11 loop
12 fetch dept_cursor into vdname,ec;
13 exit when dept_cursor%notfound;
14 dbms_output.put_line('部门名：'||vdname);
15 loop
16 fetch ec into er;
17 exit when ec%notfound;
18 dbms_output.put_line('----雇员名：'||er.ename||'，岗位：'||er.job);
19 end loop;
20 end loop;
21 close dept_cursor;
22 end;
23 /
输入 dno 的值: 10
部门名：ACCOUNTING
----雇员名：CLARK，岗位：MANAGER
----雇员名：KING，岗位：PRESIDENT
----雇员名：MILLER，岗位：CLERK
PL/SQL 过程已成功完成。
