相当于java中的ResultSet
1、使用
例如:cursor c1 is select ename from emp;
打开光标:open c1;
取一行游标的值:fetch c1 into pjob;(pjob必须和emp表中的job列类型一致)
关闭游标:close c1;
游标的结束方式:exit when c1%notfound(根据游标的属性结束循环)
例子:打印所有员工的姓名和薪水
declare
--定义光标
cursor c1 is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open c1;
loop
--游标结束退出循环
exit when c1%notfound;
--取一行值
fetch c1 into pename,psal;
dbms_output.put_line(pename||'的工资是'||psal);
end loop;
--关闭光标
close c1;
end;
2、光标属性
%isopen
%rowcount(影响的行数)
%found
%notfound
3、程序中写了DML语句,需要commit
例子:给员工涨工资,总裁1000 经理800 其他400
declare
--定义光标
cursor c1 is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
--打开光标
open c1;
loop
--游标结束退出循环
exit when c1%notfound;
--取一行值
fetch c1 into pempno,pjob;
if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
--关闭光标
close c1;
commit;
end;
4、带参数的光标
例子:查询某部门的员工姓名
declare
cursor c1(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
open c1(20);
loop
exit when c1%notfound;
fetch c1 into pename;
dbms_output.put_line(pename);
end loop;
close c1;
end;