-- 查找员工号,姓名,职位
declare
-- 定义游标
cursor mycur is select empno,ename,job from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
begin
-- 打开游标
open mycur;
-- 提取数据
loop
fetch mycur into v_empno,v_ename,v_job;
dbms_output.put_line('员工编号:'||v_empno||'员工名称:'||v_ename||'员工职位:'||v_job);
-- 什么时候能够退出循环
-- exit when mycur%notfound;
-- exit when not mycur%found;
exit when mycur%rowcount=5;
end loop;
-- 关闭游标
close mycur;
exception
when others then
dbms_output.put_line('出错'||sqlerrm);
end;
-- 检测游标是否打开
declare
cursor mycur is
select empno, ename, job from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
begin
open mycur;
-- 检测游标是否打开
if mycur%isopen then
dbms_output.put_line('游标已经打开');
else
dbms_output.put_line('游标没有打开');
end if;
close mycur;
exception
when others then
dbms_output.put_line('出错' || sqlerrm);
end;
-- 按员工的职称涨工资,总裁涨1000元,经理涨500元,其他员工涨300元
declare
cursor mycur is select empno,job from empnew;
v_empno empnew.empno%type;
v_job empnew.job%type;
begin
open mycur;
loop
fetch mycur into v_empno,v_job;
if v_job='董事长' then
update empnew set sal = sal + 1000 where empno = v_empno;
elsif v_job='经理' then
update empnew set sal = sal + 500 where empno = v_empno;
else
update empnew set sal = sal + 300 where empno = v_empno;
end if;
exit when mycur%notfound;
end loop;
commit;
close mycur;
exception
when others then
rollback;
dbms_output.put_line('出错:'||sqlerrm);
end;
select * from empnew;
Oracle 显式游标
最新推荐文章于 2024-04-13 10:06:48 发布