set serveroutput on;
游标使用步骤:
SQL> declare
2 cursor empcursor is select * from s_emp; --1.定义游标--
3 var_emp s_emp%rowtype;
4 begin
5 open empcursor; --2.打开游标--
6 fetch empcursor into var_emp; --3.获取游标--
7 dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
8 fetch empcursor into var_emp;
9 dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
10 close empcursor; --4.关闭游标--
11 end;
12 /
执行结果:
1:Carmen
2:LaDoris
两个注意事项:1.注释要前后都有“--”
SQL> declare
2 cursor mycursor is select first_name,salary,name
3 from s_emp,s_dept
4 where dept_id=s_dept.id;
5 var_e mycursor%rowtype;
6 begin
7 open mycursor;
8 fetch mycursor into var_e;
9 dbms_output.put_line(var_e.first_name||':'||var_e.salary||':'
10 ||var_e.name);
11 close mycursor;
12 end;
13 /
Carmen:2500:Administration
PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------
****游标的属性
1.游标名%found
当提取游标数据时,如果提取到新数据,这个属性就返回TRUE;反之返回FALSE。
游标必须处于打开状态,否则返回非法游标,游标必须fetch,否则返回NULL值。
2.游标名%notfound
当提取游标数据时,如果提取到新数据,这个属性就返回FALSE;反之返回TRUE。
游标必须处于打开状态,否则返回非法游标,游标必须fetch,否则返回NULL值。
declare
cursor empcursor is select
e.id eid,first_name,name
from s_emp e,s_dept d
where e.dept_id=d.id and
e.id<11;
var_emp empcursor%rowtype;
begin
open empcursor;
loop
fetch empcursor into var_emp;
/* 如何结束循环? */
exit when empcursor%notfound;
dbms_output.put_line(var_emp.eid
||':'||var_emp.first_name||':'
||var_emp.name);
end loop;
close empcursor;
end;
/
1:Carmen:Administration
2:LaDoris:Operations
3:Midori:Sales
4:Mark:Finance
5:Audry:Administration
6:Molly:Operations
7:Roberta:Operations
8:Ben:Operations
9:Antoinette:Operations
10:Marta:Operations
PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------