一、使用游标
对于DML语句和单行select into ,oracle自动分配隐形游标。处理select返回多行语句，可以使用显式游标。
使用显示游标处理多行数据,也可使用SELECT..BULK COLLECT INTO 语句处理多行数据.
1.定义游标
cursor cursor_name is select_statement;
2.打开游标
执行对应的SELECT语句并将SELECT语句的结果暂时存放到结果集中.
open cursor_name;
3.提取数据
打开游标后,SELECT语句的结果被临时存放到游标结果集中,使用FETCH语句只能提取一行数据
通过使用FETCH..BULK COLLECT INTO语句每次可以提取多行数据
|
1
2
3
|
fetch cursor_name into variable1,varibale2,...;fetch cursor_name bulk collect into collect1,collect2,...[limit rows]; |
(1)游标中使用fetch..into语句:只能处理一行数据,除非用循环语句
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(10);--打开游标 Loop Fetch c_book into v_bookname; --提取游标 exit when c_book%notfound; update book set price = '33' where bookname = v_bookname; End Loop; Close c_book;--关闭游标 end; |
或
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id;begin Open c_book(10); Fetch c_book into v_bookname;--预先Fetch一次 While c_book%found Loop update book set price = '33' where bookname = v_bookname; Fetch c_book into v_bookname; End Loop; Close c_book;end; |
(3)基于游标定义记录变量
|
1
2
3
4
5
6
7
8
9
10
11
|
declare cursor emp_cursor is select ename,sal from emp; emp_record emp_cursor%rowtype; begin open emp_cursor; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; dbms_output.put_line('雇员名:'||emp_record.ename||',雇员工资:'||emp_record.sal); end loop; end; |
4.关闭游标
|
1
|
close cursor_name; |
5.游标属性
用于返回显示游标的执行信息,包括%isopen,%found,%notfound,%rowcount
- %isopen:确定游标是否打开
- %found:检查是否从结果集中提取到了数据
- %notfound:与%found行为相反。
- %rowcount:返回当前行为止已经提取到的实际行数
no_data_found和%notfound的用法是有区别的，小结如下1)SELECT. . . INTO 语句触发 no_data_found；
2)当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；
3)当UPDATE或DELETE语句的where 子句未找到时触发 sql%notfound；
4)在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。
6.参数游标
注意：定义参数游标时,游标参数只能指定数据类型,而不能指定长度。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare cursor emp_cursor(no number) is select ename from emp where deptno=no; v_ename emp.ename%type; begin open emp_cursor(10); loop fetch emp_cursor into v_ename; exit when emp_cursor%notfound; dbms_output.put_line(v_ename); end loop; close emp_cursor; end; |
二、for循环遍历，实现遍历游标最高效方式。
使用FOR循环时,ORACLE会隐含的打开游标,提取游标数据并关闭游标。
每循环一次提取一次数据，在提取了所有数据后,自动退出循环并隐含的关闭游标。
1.使用游标FOR循环
|
1
2
3
4
5
6
7
8
|
--不需要声明v_bookname,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)declare cursor c_book(i_id number) is select bookname from book where id = i_id;begin for cur in c_book(10) loop --循环变量cur不需要声明 update book set price = '53' where bookname = cur.bookname; end loop;end; |
2.在游标FOR循环中直接使用子查询
|
1
2
3
4
5
|
begin for emp_record in (select ename,sal from emp) loop dbms_output.put_line(emp_record.ename); end loop;end; |
三、使用游标更新或删除数据
要通过游标更新或删除数据,在定义游标时必须要带有FOR UPDATE子句
|
1
|
cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait]; |
- for update子句：用于在游标结果集数据上家行共享锁,防止其他用户在相应行执行DML操作
- of子句：确定哪些表要加锁,没有OF子句,则在所引用的全部表上加锁
- nowait子句：用于指定不等待锁
-
必须在UPDATE后DELETE语句中引用WHERE CURRENT OF子句
update table_name set column=.. where current of cursor_name;
delete table_name where current of cursor_name;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
declare cursor emp_cursor is select ename,sal from emp for update; v_ename emp.ename%type; v_sal emp.sal%tyep; begin open emp_cursor; loop fetch emp_cursor into v_ename,v_oldsal; exit when emp_cursor%notfound; if v_oldsal<2000 then update emp set sal=sal+100 where current of emp_cursor;--delete from emp where current of emp_cursor; end if; end loop; close emp_cursor; end; |
四、通过bulk collect减少loop处理的开销
将查询结果一次性加载到集合中，而不是一条一条的加载。
（1）在显示游标中,使用FETCH..BALK COLLECT INTO语句提取所有数据
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare cursor emp_cursor is select ename from emp where deptno=10; type ename_table_type is table of varchar2(10); ename_table ename_table_type; begin open emp_cursor; fetch emp_cursor bulk collect into ename_table; for i in 1..ename_table.count loop dbms_output.put_line(ename_table(i)); end loop; close emp_cursor; end; |
（2）游标中使用FETCH..BULK COLLECT INTO ..LIMIT语句提取部分数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
declare type name_array_type is varray(5) of varchar2(10); name_array name_array_type; cursor emp_cursor is select ename from emp; rows int:=5; v_count int:=0; begin open emp_cursor; loop fetch emp_cursor bulk collect into name_array limit rows; dbms_output.pur('雇员名'); for i in 1..(emp_currsor%rowcount-v_count) loop dbms_output.put(name_array(i)||' '); end loop; dbms_output.new_line; v_count:=emp_cursor%rowcount; exit when emp_cursor%notfound; end loop; close emp_cursor; end; |
五、使用游标变量
PL/SQL的游标变量中存放着指向内存地址的指针.
1.游标变量使用步骤
包括定义游标变量,打开游标,提取游标数据,关闭游标等四个阶段
1.1定义ref cursor类型和游标变量
|
1
2
3
|
type ref_type_name is ref cursor [return return_type];cursor_varibale ref_type_name; |
当指定RETURN子句时,其数据类型必须是记录类型,不能在包内定义游标变量
1.2打开游标
|
1
|
open cursor_variable for select_statement; |
1.3提取游标数据
|
1
2
3
|
fetch cursor_varibale into variable1,variable2,...;fetch cursor_varibale bulk collect into collect1,collect2,...[limit rows] |
1.4关闭游标变量
|
1
|
close cursor_varibale; |
2.游标变量使用示例
1、在定义FEF CURSOR类型时不指定RETURN子句
在打开游标时可以指定任何的SELECT语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
declare type emp_cursor_type is ref cursor; emp_cursor emp_cursor_type; emp_record emp%rowtype; begin open emp_cursor for select * from emp where deptno=10; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename); end loop; close emp_cursor; end; |
2、在定义REF CURSOR类型时指定RETURN子句
在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型相匹配.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
declare type emp_record_type is record(name varchar2(10),salary number(6,2)); type emp_cursor_type is ref cursor return emp_record_type; emp_cursor emp_cursor_type; emp_record emp_record_type; begin open emp_cursor for select ename,sal from emp where deptno=20; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename); end loop; close emp_cursor; end; |
到此这篇关于Oracle中游标Cursor用法的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/springsnow/p/9394890.html
