declare
-- 定义游标
cursor mycur is select empno,job from empnew for update;
begin
for record in mycur loop
dbms_output.put_line(''||record.empno||'------'||record.job);
if record.job='董事长' then
update empnew set sal = sal + 1000 where current of mycur;
elsif record.job='经理' then
update empnew set sal = sal + 500 where current of mycur;
else
update empnew set sal = sal + 300 where current of mycur;
end if;
exit when mycur%notfound;
end loop;
commit;
exception
when others then
dbms_output.put_line('出错'||sqlerrm);
rollback;
end;
select * from empnew;
-- 输入部门编号,并删除部门下的所有员工
declare
cursor mycur is
select d.deptname dname,e.ename ename
from empnew e join dept1 d on e.deptno = d.deptno
where e.deptno = &deptno
for update of e.deptno;
begin
for record in mycur loop
dbms_output.put_line('部门名称:'||record.dname||'员工名称:'||record.ename);
delete from empnew where current of mycur;
end loop;
commit;
exception
when others then
dbms_output.put_line('出错'||sqlerrm);
rollback;
end;
select * from empnew where deptno=10;