4,游标
经典的数据库编程中，用来处理返回一个查询结果时使用的内部数据结构叫做游标。
Plsql的游标可以分为隐式游标和显式游标。
显式游标由用户显式声明，游标将指向活动集中的当前行，可以显式的打开，提取，关闭。例如：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp;
4 v_emp scott.emp%rowtype;
5 begin
6 open cur_emp;
7 fetch cur_emp
8 into v_emp;
9 DBMS_output.put_line('empno: ' || v_emp.empno || ' ename: ' ||
10 v_emp.ename);
11 close cur_emp;
12 end;
13 /
PL/SQL procedure successfully completed
或者也可以使用for loop 的形式来打开和使用游标：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp where rownum = 1;
4 v_emp scott.emp%rowtype;
5 begin
6 for cs in cur_emp loop
7 dbms_output.put_line('cs.empno:'||cs.empno);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed
也可以嵌套定义游标：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp a where a.empno = 7369;
4
5 cursor cur_emp_1 is
6 select cursor (select * from scott.emp a where a.empno = 7369)
7 from scott.emp a where rownum <4;
8 cur_type sys_refcursor;
9 v_emp scott.emp%rowtype;
10
11 begin
12 open cur_emp_1;
13
14 loop
15 fetch cur_emp_1
16 into cur_type;
17 exit when cur_emp_1%notfound;
18 dbms_output.put_line('begin outer loop:');
19 loop
20 fetch cur_type
21 into v_emp;
22 exit when cur_type%notfound;
23 dbms_output.put_line('begin inner loop:');
24 dbms_output.put_line(v_emp.empno);
25 end loop;
26 end loop;
27 close cur_emp_1;
28 end;
29 /
begin outer loop:
begin inner loop:
7369
begin outer loop:
begin inner loop:
7369
begin outer loop:
begin inner loop:
7369
PL/SQL procedure successfully completed
游标属性
注意到上述的
exit when cur_emp_1%notfound;
这里是循环退出的条件， notfound是游标的属性之一，plsql提供了几个游标属性：
| 名称 | 描述 |
| %FOUND | 记录成功提取，返回true，否则返回false |
| %NOTFOUND | 记录没有成功提取，返回true，否则返回false |
| %ROWCOUNT | 当前从游标中提取的记录数量 |
| %ISOPEN | 游标是否已经打开 |
| %BULK_ROWCOUNT | 返回由forall修改了的记录数量 |
| %BULK_EXCEPTIONS | 返回forall语句修改后的行的例外信息 |
%BULK_ROWCOUNT和%BULK_EXCEPTIONS这两个属性在批量处理中示意。
隐式游标
Plsql在每次执行一个DML语句（insert,update,delete）或者使用select into从数据库中直接返回单行到plsql数据库结构中是，都会定义和操纵一个隐式游标。
之所以称做隐式，是因为oracle自动的管理游标相关的操作，比如分配一个游标，打开一个游标，
提取记录，乃至关闭游标。例如：
SQL> declare
2 v_emp scott.emp%rowtype;
3 i number := 0;
4 begin
5 for cur_emp in (select * from scott.emp where rownum = 1) loop
6 DBMS_output.put_line(i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed
游标变量
可以在数据库中声明一个游标的引用变量，可以在运行时赋值或者改变。
同时可以将游标变量作为函数或者过程的参数，当需要传递结果集到其他环境(比较java)的时候，这种用法很有用。
声明的游标变量可以定义返回类型（强类型）或者不定义返回类型（弱类型），示例：
SQL> declare
2 v_emp scott.emp%rowtype;
3 type t_cur_emp_str is ref cursor return scott.emp%rowtype;
4 v_cur_emp_str t_cur_emp_str;
5 type t_cur_emp_weak is ref cursor;
6 v_cur_emp_weak t_cur_emp_weak;
7
8 begin
9 open v_cur_emp_str for
10 select * from scott.emp where rownum = 1;
11
12 fetch v_cur_emp_str
13 into v_emp;
14
15 close v_cur_emp_str;
16
17 open v_cur_emp_weak for
18 select * from scott.emp where rownum = 1;
19
20 fetch v_cur_emp_weak
21 into v_emp;
22 close v_cur_emp_weak;
23 end;
24 /
PL/SQL procedure successfully completed
或者用来作为函数或者存储过程的参数：
SQL> declare
2 type tp_cur_emp_str is ref cursor return scott.emp%rowtype;
3 cur_emp_str tp_cur_emp_str;
4 v_emp_row scott.emp%rowtype;
5
6 function func_use_cur(cur in tp_cur_emp_str) return number is
7 v_emprow scott.emp%rowtype;
8 num number := 0;
9 begin
10 loop
11 fetch cur
12 into v_emprow;
13 exit when cur%notfound;
14 num := cur%rowcount;
15 end loop;
16 return num;
17 end;
18
19 procedure proc_use_cur(cur in tp_cur_emp_str) is
20 v_emprow scott.emp%rowtype;
21 begin
22 loop
23 fetch cur
24 into v_emprow;
25 exit when cur%notfound;
26 dbms_output.put_line('cur%rowcount :' || cur%rowcount);
27 dbms_output.put_line('v_emprow.empno:' || v_emprow.empno);
28 end loop;
29 end;
30
31 begin
32 open cur_emp_str for
33 select * from scott.emp a where rownum < 5;
34 dbms_output.put_line('func_use_cur(cur_emp_str): ' ||
35 func_use_cur(cur_emp_str)||chr(10));
36 close cur_emp_str;
37
38 open cur_emp_str for
39 select * from scott.emp a where rownum < 5;
40 proc_use_cur(cur_emp_str);
41 close cur_emp_str;
42
43 end;
44 /
func_use_cur(cur_emp_str): 4
cur%rowcount :1
v_emprow.empno:1
cur%rowcount :2
v_emprow.empno:7369
cur%rowcount :3
v_emprow.empno:7499
cur%rowcount :4
v_emprow.empno:7521
PL/SQL procedure successfully completed
也可以用作函数的返回值，或者函数和存储过程的out 参数，或者结合管道函数方面的使用，在过程和函数小结时再具体举例。
可以使用 where current of更新游标中当前行如：
SQL> declare
2 cursor cs is
3 select * from scott.emp a where a.empno = 1 for update;
4 begin
5 for css in cs loop
6 update scott.emp a set a.ename = 'ename' where current of cs;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed
再来看看使用显式游标与select into的隐式游标在读取一行时的比较，可以看出单行读取的情况下好像隐式游标稍稍好一点：
SQL> set timi on;
SQL> declare
2 cursor cs is
3 select dummy from dual;
4 v_dummy dual.dummy%type;
5 begin
6 for i in 1 .. 100000 loop
7 open cs;
8 fetch cs
9 into v_dummy;
10 close cs;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed
Executed in 4.141 seconds
SQL>
SQL> declare
2 v_dummy dual.dummy%type;
3 begin
4 for i in 1 .. 100000 loop
5 select dummy into v_dummy from dual;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed
Executed in 3.172 seconds
很多时候把操作尽可能的让oracle来做效果要好一点。
如果上面一个不明显的话，再看一个含有order by的cursor的例子：
create table t_test_cusor as select * from all_objects a ;
/
SQL> select count(*) from all_objects a ;
COUNT(*)
----------
59069
Executed in 3.265 seconds
SQL> declare
2 cursor cs is
3 select object_name from t_test_cusor a order by object_id desc;
4 v_object_name t_test_cusor.object_name%type;
5 begin
6 for i in 1 .. 500 loop
7 open cs;
8 fetch cs
9 into v_object_name;
10 close cs;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed
Executed in 19.954 seconds
SQL> declare
2 v_object_name t_test_cusor.object_name%type;
3 begin
4 for i in 1 .. 500 loop
5 select object_name
6 into v_object_name
7 from (select object_name from t_test_cusor order by object_id desc)
8 where rownum = 1;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed
Executed in 11.532 seconds
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-664455/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-664455/
2465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
