declare
cursor csr_dept
is
--select语句
select dname
from depth;
--指定行指针,这句话应该是指定和csr_dept行类型相同的变量
row_dept csr_dept%rowtype;
begin
--for循环
for row_dept in csr_dept loop
dbms_output.put_line('部门名称:'||row_dept.dname);
end loop;
end;
declare
--游标声明
cursor csr_testWhile
is
select loc
from depth;
--指定行指针
row_testWhile csr_testWhile%rowtype;
begin
--使用fetch语句时,需要先开启游标
open csr_testWhile;
--给第一行喂数据
fetch csr_testWhile into row_testWhile;
while csr_testWhile%found loop
dbms_output.put_line('部门地点:'||row_testWhile.loc);
--给下一行喂数据
fetch csr_testWhile into row_testWhile;
end loop;
close csr_testWhile;
end;
--向游标传递一个工种,显示此工种的所有雇员的所有信息(使用参数游标)
declare
--游标声明
cursor c_job(p_job nvarchar2)
is
select * from emp where job=p_job;
--指定行指针
r_job emp%rowtype;
begin
for r_job in c_job('click') loop
dbms_output.put_line('员工号:'||r_job.empno||' 员工姓名:'||r_job.ename);
end loop;
end;
--使用更新游标来为雇员加佣金(用if语句实现)
create table emp1 as select * from emp;
declare
--游标声明
cursor csr_update
is
select * from emp1 for update of sal;--for update of 锁定table中满足条件的行
empInfo csr_update%rowtype;
saleInfo emp1.sal%type;
begin
for empInfo in csr_update loop
if empInfo.sal < 1500 then
saleInfo := empInfo.sal*1.2;
elsif empInfo.sal < 2000 then
saleInfo := empInfo.sal*1.5;
elsif empInfo.sal < 3000 then
saleInfo := empInfo.sal*2;
end if;
update emp1 set sal=saleInfo where current of csr_update; --想要删除和更新被for update of 锁定的行时,需要使用where current of.
end loop;
end;
--对名字以'A'或'B'开头的所有员工的sal的10%加薪
declare
--游标声明
cursor cur_update
is
select * from emp1 where ename like 'A%' or ename like 'B%' for update of sal;
empInfo cur_update%rowtype;
saleInfo emp1.sal%type;
begin
for empInfo in cur_update loop
dbms_output.put_line('原来的sal是'|| empInfo.sal);
saleInfo := empInfo.sal*1.1;
update emp1 set sal=saleInfo where current of cur_update;
end loop;
end;
--对所有的salesman增加佣金(comm)500
declare
--游标声明
cursor csr_comm(p_job nvarchar2)
is
select * from emp1 where job=p_job for update of comm;
c_addcomm emp1%rowtype;
commInfo emp1.comm%type;
begin
for c_addcomm in csr_comm('salesman') loop
commInfo := c_addcomm.comm + 500;
update emp1 set comm=commInfo where current of csr_comm;
end loop;
end;
好久之前网上找的小例子,原地址记不清了,见谅。