--游标(Cursor)管理
--1.隐式游标
--不可定义游标名称,固定叫SQL,当PL/SQL中执行DML语句时,会自动打开游标
--%Found/%NotFound/%RowCount/%IsOpen,功能有限
declare
mysal emp.sal%type;
myname emp.ename%type;
mydeptno emp.deptno%type;
begin
mydeptno := &no;
update emp set sal = sal + 100 where deptno=mydeptno;
dbms_output.put_line('给'||sql%rowcount||'个人涨了薪水');
end;
--2。显示游标
--自定义游标和名称,操作方式:Open/Fetch/Close,推荐使用Loop循环取数据
--显示指定部门下所有人员的薪水
declare
Cursor mycur is select ename,sal from emp where deptno=10;--定义游标
mysal emp.sal%type;
myname emp.ename%type;
mydept emp.deptno%type;
begin
open mycur;--打开游标
loop
fetch mycur into myname,mysal;--提取记录
exit when mycur%notfound;
dbms_output.put_line(myname||'的薪水是:'||mysal);
end loop;
close mycur;--关闭游标
end;
--带参数的游标
--指定参数的类型时,不能带精度
declare
Cursor mycur(deptcode number) is select ename,sal from emp where deptno=deptcode;--定义游标
mysal emp.sal%type;
myname emp.ename%type;
mydept emp.deptno%type;
begin
mydept := &id;
open mycur(mydept);--打开游标(指定游标的参数)
loop
fetch mycur into myname,mysal;--提取记录
exit when mycur%notfound;
dbms_output.put_line(myname||'的薪水是:'||mysal);
end loop;
close mycur;--关闭游标
end;
--使用游标做更新
--给30部门所有的员工涨三成薪水
declare
cursor mycur is select * from emp where deptno=30 for update;
myrec emp%rowtype;
newsal emp.sal%type;
begin
open mycur;
loop
fetch mycur into myrec;
exit when mycur%notfound;
newsal := myrec.sal * 1.3;
update emp set sal = newsal where current of mycur;
dbms_output.put_line(myrec.ename||'的薪水从'||myrec.sal||'涨到了'||newsal);
end loop;
close mycur;
commit;
end;
--循环游标(显示游标的简化版)
--使用(特殊的)for循环自动打开/关闭游标,自动提取记录
declare
Cursor mycur is select * from emp where deptno=10;--定义游标
--myrec emp%rowtype;
mydept emp.deptno%type;
begin
for myrec in mycur
loop
dbms_output.put_line(myrec.ename||'的薪水是:'||myrec.sal);
end loop;
end;
--循环游标带参数
declare
Cursor mycur(deptcode number) is select * from emp where deptno=deptcode;--定义游标
mydept emp.deptno%type;
begin
mydept := &id;
for myrec in mycur(mydept)
loop
dbms_output.put_line(myrec.ename||'的薪水是:'||myrec.sal);
end loop;
end;
select * from dept;
--3。REF(动态)游标
--在程序体中来确定游标对应查询语句
--根据输入的值确定输出的内容:
--输入1: 显示所有人员的姓名和薪水
--输入2: 显示所有人员的姓名和所属部门名称
--使用方式:首先定义游标类型,然后定义游标变量
declare
type t56_cursor is ref cursor;--定义游标类型;
mycur t56_cursor;--定义游标变量
myno number(1);
myname emp.ename%type;
mysal emp.sal%type;
mydeptname dept.dname%type;
begin
myno := &id;
if myno = 1 then
open mycur for select ename,sal from emp;
loop
fetch mycur into myname,mysal;
exit when mycur%notfound;
dbms_output.put_line(myname||'的薪水是:'||mysal);
end loop;
close mycur;
else
open mycur for select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
loop
fetch mycur into myname,mydeptname;
exit when mycur%notfound;
dbms_output.put_line(myname||'所属的部门是:'||mydeptname);
end loop;
close mycur;
end if;
end;