游标的使用
1、显式游标(含有声明)
declarecursor mycur is
select * from g3e_attribute;
myrecord g3e_attribute%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.g3e_username);
fetch mycur into myrecord;
end loop;
close mycur;
end;
在显式游标中,使用FETCH...BULK CELLECT INTO语句提取所有数据
从Oracle9i开始,通过使用FETCH...BULK CELLECT INTO语句,一次就可以提取结果集的所有数据。见如下示例:
DECLARE
CURSOR my_cursor IS
SELECT g3e_cno FROM g3e_featurecomponent WHERE g3e_fno=25257;--27行
TYPE my_type_cno IS TABLE OF NUMBER(5);
type_cno my_type_cno;
BEGIN
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO type_cno;
FOR i IN 1..type_cno.COUNT LOOP
DBMS_OUTPUT.put_line(type_cno(i));
END LOOP;
END;
--结果输出了27行的cno数据
在显式游标中使用FETCH..BULK COLLECT INTO..LIMIT语句提取部分数据
当使用FETCH..BULK COLLECT INTO语句提取数据时,默认情况下会提取结果集的所有数据。如果结果集含有大量数据,并且使用VARRAY集合变量接受数据,那么可能需要限制每次提取的行数。示例如下,每次提取5行数据。
DECLARE
CURSOR my_cursor IS
SELECT g3e_cno FROM g3e_featurecomponent WHERE g3e_fno=25257;--27行
TYPE my_type_cno IS TABLE OF NUMBER(5);
type_cno my_type_cno;
rows INT:=5;
v_count INT:=0;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor BULK COLLECT INTO type_cno
LIMIT rows;
DBMS_OUTPUT.PUT('设施组件:');
FOR i IN 1..(my_cursor%ROWCOUNT-v_count) LOOP
DBMS_OUTPUT.put_line(type_cno(i)||' ');
END LOOP;
dbms_output.new_line;
v_count:=my_cursor%ROWCOUNT;
EXIT WHEN my_cursor%NOTFOUND;
END LOOP;
CLOSE my_cursor;
END;
--结果每五行一次输出
设施组件:25467 25816 25446 54 25260
设施组件:25261 25450 24789 25298 52
设施组件:25481 25463 25437 65 25459
设施组件:24787 64 63 25465 25461
设施组件:24788 24790 25812 51 25439
设施组件:25443 25441
--带参数的游标
declare
cursor cur_para(id number) is
select g3e_username from g3e_attribute where g3e_ano=id;
t_name g3e_attribute.g3e_username%type;
begin
open cur_para(1);
loop
fetch cur_para into t_name;
exit when cur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close cur_para;
end;
注:定义参数游标时,游标参数只能指定数据类型,而不能指定长度。另外,定义参数游标时,一定要在游标子查询的where子句中引用该参数,否则失去了定义参数游标的意义。
--游标以for循环的方式出现
declare
cursor cur_para(id number) is
select g3e_username from g3e_attribute where g3e_ano=id;
begin
dbms_output.put_line('*******结果集为: ******');
for cur in cur_para(1) loop
dbms_output.put_line(cur.g3e_username);
end loop;
end;
--%isopen的使用
declare
t_name g3e_attribute.g3e_username%type;
cursor cur(id number) is
select g3e_username from g3e_attribute where g3e_ano=id;
begin
if cur%isopen then --判断游标是否已经打开
dbms_output.put_line('游标已经被打开!');
else
open cur(2477471);
end if;
fetch cur into t_name;
close cur;
dbms_output.put_line(t_name);
end;
--%rowcount的使用
declare
t_name varchar2(255);
cursor mycur is
select g3e_username from g3e_feature;
begin
open mycur;
loop
fetch mycur into t_name;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line('游标mycur的rowcount是'||mycur%rowcount);
end loop;
close mycur;
end;
--利用游标修改属性
declare
cursor cur is
select g3e_username from g3e_feature where g3e_fno=25257 for update;
text varchar2(255);
begin
open cur;
fetch cur into text;
while cur%found loop
update g3e_feature set g3e_username=g3e_username||'test' where current of cur; --修改游标的当前行
fetch cur into text;
end loop;
close cur;
end;
使用NOWAIT子句修改属性
使用FOR UPDATE语句对被作用行加锁,如果其他会话已经在被作用行上加锁,那么在默认情况下当前会话会一直等待对方释放锁。通过使用FOR UPDATE子句中指定NOWAIT语句,可以避免等待锁。当指定了NOWAIT子句之后,如果其他会话已经在被作用行加锁,那么当前会话会显示错误提示信息,并推出PL\SQL块。示例如下:
DECLARE
CURSOR my_cursor IS
SELECT g3e_fno,g3e_cno FROM g3e_featurecomponent FOR UPDATE NOWAIT;
v_fno g3e_featurecomponent.g3e_fno%TYPE;
v_cno g3e_featurecomponent.g3e_cno%TYPE;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO v_fno,v_cno;
EXIT WHEN my_cursor%NOTFOUND;
IF v_cno<51 THEN
UPDATE g3e_featurecomponent SET g3e_cno=g3e_cno+1 WHERE CURRENT OF my_cursor;
END IF;
END LOOP;
CLOSE my_cursor;
END;
游标for循环的使用
1)使用游标for循环
cursor emp_cursor is
select ename, sal from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('第' || emp_cursor%rowcount || '雇员:' ||
emp_record.ename);
end loop;
end;
2)在游标for循环中直接使用子查询
for emp_record in (select ename, sal from emp) loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
游标变量的使用
定义游标变量不能在包内定义。
1)在定义REF CURSOR时不指定return子句
type emp_cursor_type is ref cursor; --没有指定return子句
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
open emp_cursor for
select * from emp;
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('第' || emp_cursor%rowcount || '雇员:' ||
emp_record.ename);
end loop;
close emp_cursor;
end;
2)在定义REF CURSOR时指定return子句
当指定return子句时,其数据类型必须是record类型。
type emp_record_type is record(
ename varchar2(10),
salary number);
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; --游标子查询的返回结果与emp_record_type要匹配
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('第' || emp_cursor%rowcount || '个雇员:' ||
emp_record.ename);
end loop;
close emp_cursor;
end;
使用cursor表达式
可以返回嵌套游标
type refcursor is ref cursor;
cursor dept_cur(no number) is
select dname,
cursor (select ename, sal from emp where a.deptno = deptno)
from dept a
where a.deptno = &no;
empcur refcursor;
v_dname dept.dname%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open dept_cur(&no);
loop
fetch dept_cur
into v_dname, empcur;
exit when dept_cur%notfound;
dbms_output.put_line('部门名称:' || v_dname);
loop
fetch empcur
into v_ename, v_sal;
exit when empcur%notfound;
dbms_output.put_line('雇员名:' || v_ename|| ',雇员工资:' || v_sal);
end loop;
end loop;
close dept_cur;
end;
部门名称:ACCOUNTING
雇员名:CLARK,雇员工资:2450
雇员名:KING,雇员工资:5000
雇员名:MILLER,雇员工资:1300
2、隐式游标
begin
for cur in (select g3e_username from g3e_feature) loop
dbms_output.put_line(cur.g3e_username);
end loop;
end;