--==================
-- PL/SQL --> 游标
--==================
一、游标的相关概念及特性
1. 定义
映射在结果集中某一行数据的具体位置，类似于 C 语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求
对该行进行相应特定的操作。
2. 游标的分类
显示游标 : 即用户自定义游标，专门用于处理 select 语句返回的多行数据
隐式游标 : 系统自动定义的游标，记录集只有单行数据，用于处理 select into 和 DML 语句
3. 游标使用的一般过程：
显示游标：声明 , 打开 , 读取 , 关闭
隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的
4. 显示游标的过程描述
a . 声明游标
CURSOR cursor_name IS select_statement
如： CURSOR emp_cur IS SELECT empno , ename , job , sal FROM scott . emp ;
b . 打开游标
OPEN cursor_name -- 打开游标则执行对应的 select 语句，将对应的结果集存放到游标当中
如： OPEN emp_cur
c . 读取数据
FETCH cursor_name INTO var_name1 ,... var_name2 ; -- 提取单行数据，需要配合循环语句来使用
FETCH cursor_name BULK COLLECT INTO collect1 , collect2 ,... [LIMIT rows] ; -- 提取多行数据， collect 为集合变量
d . 关闭游标
CLOSE cursor_name
5. 显示游标的个属性
cursor_name%ISOPEN 游标是否打开
cursor_name%FOUND 最近的 FETCH 是否提取到数据
cursor_name%NOTFOUND 最近的 FETCH 是否没有提取到数据
cursor_name%ROWCOUNT 返回到目前为止，已经从游标缓冲区中提取到数据的行数
二、显示游标应用示例
-- 例：浏览数据，输入职位，查看每个人工资 ( 使用 fetch cursor_name into 来提取单行记录 )
scott@ORCL > get / u01 / bk / scripts / emp_cur1 . sql
1 DECLARE
2 v_name emp . ename % TYPE ; -- 定义用于存放游标提取的数据的变量
3 v_job emp . job % TYPE ;
4 v_sal emp . sal % TYPE ;
5 CURSOR emp_cur IS select ename , sal FROM emp WHERE job = v_job ;
6 BEGIN
7 v_job := '&inputjob' ;
8 OPEN emp_cur ;
9 DBMS_OUTPUT . PUT_LINE ( 'Name Sal' );
10 LOOP
11 FETCH emp_cur INTO v_name , v_sal ;
12 EXIT WHEN emp_cur % NOTFOUND ;
13 DBMS_OUTPUT . PUT_LINE ( v_name|| ' ' || v_sal );
14 END LOOP ;
15 CLOSE emp_cur ;
16 * END ;
scott@ORCL > start / u01 / bk / scripts / emp_cur1 . sql
Enter value for inputjob : CLERK
old 7 : v_job := '&inputjob' ;
new 7 : v_job := 'CLERK' ;
Name Sal
SMITH 800
ADAMS 1100
JAMES 950
MILLER 1300
PL / SQL procedure successfully completed .
-- 例：定义一个游标，输入部门号时，则显示该部门所有成员的名字 ( 使用 fetch cursor_name bulk collect into 提取所有数据 )
scott@ORCL > get / u01 / bk / scripts / emp_cur2 . sql
1 DECLARE
2 v_deptno emp . deptno % type ;
3 type ename_table_type is table of varchar2 ( 10 ); -- 定义 PL/SQL 表类型
4 ename_table ename_table_type ; -- 定义 PL/SQL 表变量存放游标数据
5 cursor emp_cur is
6 select ename from emp where deptno = v_deptno ;
7 BEGIN
8 v_deptno :=& inputno ;
9 open emp_cur ;
10 fetch emp_cur bulk collect into ename_table ; -- 使用 bulk collect into 提取所有数据
11 for i in 1. . ename_table . count
12 loop
13 dbms_output . put_line ( ename_table ( i ));
14 end loop ;
15 close emp_cur ;
16 * END ;
scott@ORCL > start / u01 / bk / scripts / emp_cur2 . sql
Enter value for inputno : 10
old 8 : v_deptno :=& inputno ;
new 8 : v_deptno := 10 ;
CLARK
KING
MILLER
-- 例：游标属性使用示例 ( 使用 %isopen 和 %rowcount 属性 )
scott@ORCL > get / u01 / bk / scripts / emp_cur3 . sql
1 DECLARE
2 v_deptno emp . deptno % type ;
3 type ename_table_type is table of varchar2 ( 10 );
4 ename_table ename_table_type ;
5 cursor emp_cur is
6 select ename from emp where deptno = v_deptno ;
7 BEGIN
8 v_deptno :=& inputno ;
9 if not emp_cur % isopen then -- 判断游标是否打开，如未打开，则打开游标
10 open emp_cur ;
11 end if ;
12 fetch emp_cur bulk collect into ename_table ;
13 dbms_output . put_line
14 ( 'All record counts from cursor is : ' || emp_cur % rowcount ); -- 使用 cursor_name%rowcount 统计游标的记录数
15 close emp_cur ;
16 * END ;
scott@ORCL > start / u01 / bk / scripts / emp_cur3 . sql
Enter value for inputno : 20
old 8 : v_deptno :=& inputno ;
new 8 : v_deptno := 20 ;
All record counts from cursor is : 5
-- 例 : 基于游标定义记录变量 ( 该方式大大简化了所需要定义的变量个数 )
scott@ORCL > get / u01 / bk / scripts / emp_cur4 . sql
1 DECLARE
2 cursor emp_cur is select ename , sal from emp ;
3 emp_record emp_cur % rowtype ; -- 定义游标类型记录变量
4 BEGIN
5 open emp_cur ;
6 loop
7 fetch emp_cur into emp_record ;
8 exit when emp_cur % notfound ;
9 dbms_output . put_line
10 ( 'Employee Name : ' || emp_record . ename || ' ,Sal: ' || emp_record . sal );
11 end loop ;
12 close emp_cur ;
13 * END ;
scott@ORCL > start / u01 / bk / scripts / emp_cur4 . sql
Employee Name : SMITH , Sal : 800
Employee Name : ALLEN , Sal : 1600
Employee Name : WARD , Sal : 1250
.......
三、使用游标更新记录
通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据
如果要通过游标更新和删除数据，在定义游标时必须要带有 FOR UPDATE 子句
格式：
CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]
FOR UPDATE ：子句用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行 DML 操作
OF ：子句用于游标子查询到多张表时来确定哪些表要加锁，如未指定，则 select 语句所引用的全部表将被加锁
NOWAIT ：子句指定不等待锁
使用 DML 语句操作游标中的当前行时，需要在 update 或 delete 语句中引用 where current of 子句
UPDATE tbname set col1 =.. WHERE CURRENT OF cursor_name ;
DELETE tbname WHERE CURRENT OF cursor_name ;
-- 例：使用游标修改所有记录的工资，根据 JOB 来作不同的修改。
scott@ORCL > create table tb_emp as select * from emp ;
scott@ORCL > get / u01 / bk / scripts / emp_cur6 . sql
1 DECLARE
2 v_job tb_emp . job % TYPE ;
3 CURSOR emp_cur IS SELECT job FROM tb_emp FOR UPDATE ; -- 定义时，使用 FOR UPDATE
4 BEGIN
5 OPEN emp_cur ;
6 LOOP
7 FETCH emp_cur INTO v_job ;
8 EXIT WHEN emp_cur % NOTFOUND ;
9 CASE
10 WHEN v_job = 'CLERK' THEN
11 UPDATE tb_emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cur ; -- 注意，需要使用 WHERE CURRENT OF
12 WHEN v_job = 'SALESMAN' THEN
13 UPDATE tb_emp SET sal = sal * 1.08 WHERE CURRENT OF emp_cur ;
14 ELSE
15 UPDATE tb_emp SET sal = sal * 1.05 WHERE CURRENT OF emp_cur ;
16 END CASE ;
17 END LOOP ;
18 CLOSE emp_cur ;
19 * END ;
-- 例：利用游标删除数据
scott@ORCL > get / u01 / bk / scripts / emp_cur7 . sql
1 DECLARE
2 v_job tb_emp . job % type ;
3 v_sal tb_emp . sal % type ;
4 cursor emp_cur is select job , sal from tb_emp for update ;
5 BEGIN
6 open emp_cur ;
7 fetch emp_cur into v_job , v_sal ;
8 while emp_cur % found
9 loop
10 if v_sal > 3000 then
11 delete from tb_emp where current of emp_cur ;
12 end if ;
13 fetch emp_cur into v_job , v_sal ;
14 end loop ;
15 close emp_cur ;
16 * END ;
17 /
-- 例：使用 OF 子句对特定的表加共享锁
scott@ORCL > get / u01 / bk / scripts / emp_cur8 . sql
1 DECLARE
2 cursor emp_cur is
3 select ename , sal , dname , e . deptno
4 from tb_emp e join dept d
5 on e . deptno = d . deptno for update of e . deptno ;
6 emp_record emp_cur % rowtype ;
7 BEGIN
8 open emp_cur ;
9 loop
10 fetch emp_cur into emp_record ;
11 exit when emp_cur % notfound ;
12 if emp_record . deptno = 20 then
13 update tb_emp set sal = sal + 100 where current of emp_cur ;
14 end if ;
15 dbms_output . put_line ( 'Ename: ' || emp_record . ename||
16 ',Sal: ' || emp_record . sal||
17 ',Deptname:' || emp_record . dname );
18 end loop ;
19 close emp_cur ;
20 * END ;
21 /
Ename : SMITH , Sal : 880 , Deptname:RESEARCH
Ename : ALLEN , Sal : 1728 , Deptname : SALES
........
-- 例 :NOWAIT 子句的使用
scott@ORCL > get / u01 / bk / scripts / emp_cur9 . sql
1 DECLARE
2 v_ename tb_emp . ename % type ;
3 v_oldsal tb_emp . sal % type ;
4 cursor emp_cur is
5 select ename , sal from tb_emp for update nowait ; -- 使用 nowait 子句指定不等待锁，会给出错误提示
6 BEGIN
7 open emp_cur ;
8 loop
9 fetch emp_cur into v_ename , v_oldsal ;
10 exit when emp_cur % notfound ;
11 if v_oldsal < 2000 then
12 update tb_emp set sal = sal + 200 where current of emp_cur ;
13 end if ;
14 end loop ;
15 close emp_cur ;
16 * END ;
scott@ORCL > start / u01 / bk / scripts / emp_cur9 . sql
DECLARE
*
ERROR at line 1 :
ORA - 00054 : resource busy and acquire with NOWAIT specified
ORA - 06512 : at line 5
ORA - 06512 : at line 7
四、游标 FOR 循环
游标 FOR 循环是为了简化游标使用过程而设计的。使用游标 FOR 循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标
的关闭都是 ORACLE 系统自动进行的。
游标 FOR 循环两种语句格式：
格式一：
先在定义部分定义游标，然后在游标 FOR 循环中引用该游标
FOR record_name IN cursor_name LOOP
statement1 ;
statement2 ;
END LOOP ;
格式二：
在 FOR 循环中直接引用子查询，隐式定义游标
FOR record_name IN subquery LOOP
statement ;
END LOOP ;
-- 例：定义游标并使用 for 循环逐个显示记录
DECLARE
v_job emp . job % TYPE ;
CURSOR emp_cur IS SELECT ename , sal FROM emp WHERE job = v_job ;
BEGIN
v_job := '&inputjob' ;
DBMS_OUTPUT . PUT_LINE ( 'NO. Name Sal' );
FOR emp_record IN emp_cur LOOP
DBMS_OUTPUT . PUT_LINE ( emp_cur % ROWCOUNT|| ' ' ||
emp_record . ename|| ' ' || emp_record . sal );
END LOOP ;
END ;
/
scott@ORCL > start / u01 / bk / scripts / emp_cur10 . sql
Enter value for inputjob : SALESMAN
old 5 : v_job := '&inputjob' ;
new 5 : v_job := 'SALESMAN' ;
NO . Name Sal
1 ALLEN 1600
2 WARD 1250
3 MARTIN 1250
4 TURNER 1500
-- 例：直接在游标 for 循环中使用子查询来逐个显示记录
DECLARE
v_job emp . job % TYPE ;
BEGIN
v_job := '&inputjob' ;
DBMS_OUTPUT . PUT_LINE ( 'Name Sal' );
FOR emp_record IN ( SELECT ename , sal FROM emp WHERE job = v_job ) LOOP
DBMS_OUTPUT . PUT_LINE ( emp_record . ename|| ' '
|| emp_record . sal );
END LOOP ;
END ;
/
五、参数游标
参数游标是指带有参数的游标。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。
定义参数游标：
CURSOR cursor_name ( para_name1 datatype [,para_name2 datatype ...] )
IS select_statement ;
注： datatype 只指定数据类型即可，不能指定参数的长度、精度、刻度
打开参数游标：
OPEN cursor_name [(vlaues)]
参数个数、类型必须与定义时的形参相匹配。
对于定义的参数游标，一定要在游标子查询的 where 子句中指定定义的参数，否则将使得参数游标失去意义
例：用部门编号 deptno 作形参，显示每个人的姓名和工资
scott@ORCL > get / u01 / bk / scripts / emp_cur5 . sql
1 DECLARE
2 v_deptno emp . deptno % type ;
3 cursor emp_cur ( v_deptno emp . deptno % type ) is -- 定义游标时指定了参数 v_deptno 及类型
4 select ename , sal from emp where deptno = v_deptno ; -- 必须在 where 子句中指定定义的参数
5 emp_record emp_cur % rowtype ;
6 BEGIN
7 v_deptno :=& inputno ;
8 open emp_cur ( v_deptno );
9 loop
10 fetch emp_cur into emp_record ;
11 exit when emp_cur % notfound ;
12 dbms_output . put_line
13 ( 'Employe Name is :' || emp_record . ename|| ' ,Sal:' || emp_record . sal );
14 end loop ;
15 close emp_cur ;
16 * END ;
17 /
Enter value for inputno : 10
old 7 : v_deptno :=& inputno ;
new 7 : v_deptno := 10 ;
Employe Name is : CLARK , Sal : 2450
Employe Name is : KING , Sal : 5000
Employe Name is : MILLER , Sal : 1300
六、游标变量
简言之，其一是一个游标，其次则是一个变量，因此称之为游标变量，可以用来存储不同的游标
对于游标变量的使用，在打开游标变量时指定其对应的 select 语句
1. 游标变量的使用步骤
a . 定义 REF CURSOR 类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type] ; -- 必须先定义 REF CURSOR 类型
cursor_variable ref_type_name ; -- 接下来再定义游标变量
ref_type_name : 指定自定义的类型名
RETURN : 指定 REF CURSOR 返回结果的数据类型
cursor_variable : 定义游标变量的名字
注：若指定 RETURN 子句，其数据类型必须是记录类型，此外，不能在包内定义游标变量
b . 打开游标
在打开游标时必须指定其对应的 select 语句，一旦打开游标变量则对应的 select 结果集将存放到游标变量中
OPEN cursor_variable FOR select_statement ;
c . 提取数据
提取数据与普通的显示游标提取数据的方法类似
FETCH cursor_variable INTO variable1 ,... variable2 ; -- 提取单行数据，需要配合循环语句来使用
FETCH cursor_variable BULK COLLECT INTO collect1 , collect2 ,... [LIMIT rows] ; -- 提取多行数据， collect 为集合变量
d . 关闭游标变量
CLOSE cursor_vairable ;
2. 游标变量使用的例子
-- 例 . 根据部门名称显示该部门的所有雇员 ( 定义 REF CURSOR 时不指定 RETURN 子句 )
scott@ORCL > get / u01 / bk / scripts / emp_cur12 . sql
1 DECLARE
2 type emp_cur_type is ref cursor ; -- 定义游标类型为 ref cursor
3 emp_cur emp_cur_type ; -- 定义游标变量为 emp_cur
4 emp_record emp % rowtype ; -- 定义游标变量记录类型为 emp_record
5 v_deptno emp . deptno % type ;
6 BEGIN
7 v_deptno :=& inputno ;
8 open emp_cur for select * from emp where deptno = v_deptno ;
9 dbms_output . put_line ( 'No Name' );
10 loop
11 fetch emp_cur into emp_record ;
12 exit when emp_cur % notfound ;
13 dbms_output . put_line ( emp_cur % rowcount|| ' ' || emp_record . ename );
14 end loop ;
15 close emp_cur ;
16 * END ;
17 /
Enter value for inputno : 10
old 7 : v_deptno :=& inputno ;
new 7 : v_deptno := 10 ;
No Name
1 CLARK
2 KING
3 MILLER
-- 例 : 根据部门名称显示该部门的所有雇员名字及薪水 ( 定义 REF CURSOR 时指定 RETURN 子句 )
scott@ORCL > get / u01 / bk / scripts / emp_cur13 . sql
1 DECLARE
2 type emp_record_type is record ( name varchar2 ( 10 ), salary number ( 6 , 2 )); -- 定义 PL/SQL 记录变量类型
3 type emp_cur_type is ref cursor return emp_record_type ; -- 定义游标类型为 ref cursor ，且具有返回类型
4 emp_cur emp_cur_type ; -- 定义游标变量为 emp_cur
5 emp_record emp_record_type ; -- 定义类型为 emp_record_type 记录变量 emp_record
6 v_deptno emp . deptno % type ;
7 BEGIN
8 v_deptno :=& inputno ;
9 open emp_cur for select ename , sal from emp where deptno = v_deptno ;
10 dbms_output . put_line ( 'Name Salary' );
11 loop
12 fetch emp_cur into emp_record ;
13 exit when emp_cur % notfound ;
14 dbms_output . put_line ( emp_record . name|| ' ' || emp_record . salary );
15 end loop ;
16 close emp_cur ;
17 * END ;
18 /
Enter value for inputno : 10
old 8 : v_deptno :=& inputno ;
new 8 : v_deptno := 10 ;
Name Salary
CLARK 2450
KING 5000
MILLER 1300
-- 如果 REF CURSOR 指定 RETURN 子句的数据列于 select 子句的数据列不一致将收到如下的错误提示信息
scott@ORCL > start / u01 / bk / scripts / emp_cur13 . sql
Enter value for inputno : 10
old 8 : v_deptno :=& inputno ;
new 8 : v_deptno := 10 ;
open emp_cur for select ename , sal , job from emp where deptno = v_deptno ; -- 多出了一列
*
ERROR at line 9 :
ORA - 06550 : line 9 , column 22 :
PLS - 00382 : expression is of wrong type
ORA - 06550 : line 9 , column 5 :
PL / SQL : SQL Statement ignored
-- 例：游标变量的多次使用
scott@ORCL > get / u01 / bk / scripts / emp_cur14 . sql
1 DECLARE
2 type cur_type is ref cursor ;
3 scott_cur cur_type ;
4 v_emp emp % rowtype ;
5 v_dept dept % rowtype ;
6 BEGIN
7 open scott_cur for select * from emp where deptno = 10 ; -- 使用 for select 首次打开游标
8 dbms_output . put_line ( 'No, Name' );
9 loop
10 fetch scott_cur into v_emp ;
11 exit when scott_cur % notfound ;
12 dbms_output . put_line ( scott_cur % rowcount|| ',' || v_emp . ename );
13 end loop ;
14 open scott_cur for select * from dept where deptno = 10 ; -- 使用 for select 再此打开游标，此次加载了不同数据
15 dbms_output . put_line ( 'Deptno, Name' );
16 loop
17 fetch scott_cur into v_dept ;
18 exit when scott_cur % notfound ;
19 dbms_output . put_line ( v_dept . deptno|| ',' || v_dept . dname );
20 end loop ;
21 * END ;
22 /
No , Name
1 , CLARK
2 , KING
3 , MILLER
Deptno , Name
10 , ACCOUNTING
七、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
