Oracle数据库---游标

--查询所有员工的员工号、姓名和职位的信息。
declare
--定义游标
cursor emp_cursor is select empno,ename,job from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
begin
--打开游标，执行查询
open emp_cursor;
--提取数据
loop
fetch emp_cursor into v_empno,v_ename,v_job;
dbms_output.put_line('员工号：'||v_empno||'，姓名：'||v_ename||',职位：'||v_job);
--什么时候能够退出循环？
--%found,%notfound
exit when emp_cursor%notfound;
end loop;
--关闭游标
close emp_cursor;
end;

--查询所有员工的员工号、姓名和职位的信息。
declare
--定义游标
cursor emp_cursor is select empno,ename,job from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
begin
--打开游标，执行查询
--open emp_cursor;
--检测游标是否打开
if emp_cursor%isopen then
dbms_output.put_line('游标已经打开');
else
dbms_output.put_line('游标没有打开');
end if;

end;

--游标for循环
declare
cursor emp_cursor is select empno,ename,job from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);
end loop;
end;

--游标for循环中引用子查询
begin
for emp_record in (select empno,ename,job from emp) loop
dbms_output.put_line('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);
end loop;
end;

--参数游标
declare
cursor emp_cursor(dno number) is select empno,ename,job from emp where deptno = dno;
begin
for emp_record in emp_cursor(&no) loop
dbms_output.put_line('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);
end loop;
end;

--根据用户输入的员工号，更新指定员工的工资，比如工资涨100
--隐式游标
begin
update empnew set sal = sal + 100 where empno = &no;
if sql%found then
dbms_output.put_line('成功修改员工的工资');
commit;
else
dbms_output.put_line('修改员工工资失败!');
rollback;
end if;
end;

select * from empnew;

--按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。
--1：用显示游标的常规方式实现业务需求
declare
--定义游标
cursor empnew_cursor is select empno,job from empnew;
v_empno empnew.empno%type;
v_job empnew.job%type;
begin
--打开游标
open empnew_cursor;
--提取数据
loop
fetch empnew_cursor into v_empno,v_job;
if v_job='president' then
update empnew set sal = sal + 1000 where empno = v_empno;
elsif v_job='manager' 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 empnew_cursor%notfound;
end loop;
commit;
--关闭游标
close empnew_cursor;
end;

--2：用游标for循环的方式实现业务需求
declare
--定义游标
cursor empnew_cursor is select empno,job from empnew;
begin
for empnew_record in empnew_cursor loop
dbms_output.put_line(empnew_record.empno||'----'||empnew_record.job);
if empnew_record.job = 'president' then
update empnew set sal = sal + 1000 where empno = empnew_record.empno;
elsif empnew_record.job = 'manager' then
update empnew set sal = sal + 500 where empno = empnew_record.empno;
else
update empnew set sal = sal + 300 where empno = empnew_record.empno;
end if;
end loop;
--commit;
end;

select * from empnew for update;

--3：使用游标添加或删除数据时，定义游标时利用for update 子句可以将游标提取出来的数据进行行级锁定
declare
--定义游标
cursor empnew_cursor is select empno,job from empnew for update;
begin
for empnew_record in empnew_cursor loop
dbms_output.put_line(empnew_record.empno||'----'||empnew_record.job);
if empnew_record.job = 'president' then
update empnew set sal = sal + 1000 where current of empnew_cursor;
elsif empnew_record.job = 'manager' then
update empnew set sal = sal + 500 where current of empnew_cursor;
else
update empnew set sal = sal + 300 where current of empnew_cursor;
end if;
end loop;
commit;
end;

select * from empnew;

--for update nowait 不等待锁，如发现所操作的数据行已经锁定，将不会等待，立即返回
declare
--定义游标
cursor empnew_cursor is select empno,job from empnew for update nowait;
begin
for empnew_record in empnew_cursor loop
dbms_output.put_line(empnew_record.empno||'----'||empnew_record.job);
if empnew_record.job = 'president' then
update empnew set sal = sal + 1000 where current of empnew_cursor;
elsif empnew_record.job = 'manager' then
update empnew set sal = sal + 500 where current of empnew_cursor;
else
update empnew set sal = sal + 300 where current of empnew_cursor;
end if;
end loop;
commit;
end;

select * from empnew;

--使用of子句在特定表上加行共享锁
declare
cursor empnew_cursor is
select d.dname dname,e.ename ename
from empnew e join dept d on e.deptno = d.deptno
where e.deptno = &deptno
for update of e.deptno;
begin
for empnew_record in empnew_cursor loop
dbms_output.put_line('部门名称：'||empnew_record.dname||'员工名：'||empnew_record.ename);
delete from empnew where current of empnew_cursor;
end loop;
commit;
end;

select * from empnew where deptno = 20;