oracle之游标

先建两个表emp(员工表，员工号，所在部门号，名字，职位，工资)和dept（部门表，部门号，部门名称）

create table emp(empno char(4) constraint pk_emp_empno primary key,

deptno number(1) constraint fk_emp_dept_deptno references dept(deptno),
ename varchar(12),
job varchar(12),
sal number
);


create table dept(deptno number(1) constraint pk_dept_deptno primary key,
dname varchar2(20));

insert into dept values(1,'leader');
insert into dept values(2,'sale');
insert into dept values(3,'research');

        
insert into emp values('1111',2,'赵良万','saleman',3300    );
insert into emp values('1112',1,'邱晨  ','manager',3301    );
insert into emp values('1113',1,'王诗豪','manager',3302    );
insert into emp values('1114',1,'林雅娴','manager',3303    );
insert into emp values('1115',1,'黄松威','president',3304    );
insert into emp values('1116',3,'童宇翔','researchman',3305    );
insert into emp values('1117',3,'王智瑞','researchman',3306    );
insert into emp values('1118',3,'李沫寒','researchman',3307    );
insert into emp values('1119',3,'李维浩','researchman',3308    );
insert into emp values('1120',1,'罗子迪','manager',3309    );
insert into emp values('1121',1,'赵俊杰','manager',3310    );
insert into emp values('1122',1,'房亚鑫','manager',3311    );
insert into emp values('1123',2,'吴睿卿','saleman',3312    );
insert into emp values('1124',2,'王树声','saleman',3313    );
insert into emp values('1125',2,'郭佩强','saleman',3314    );
insert into emp values('1126',2,'周瑞阳','saleman',3315    );
insert into emp values('1127',2,'周倩  ','saleman',3316    );
insert into emp values('1128',2,'王真颖','saleman',3317    );
insert into emp values('1129',2,'王子旭','saleman',3318    );
    

 set serveroutput on;//可以在屏幕输出信息

1:显示职位为manager的员工的信息
declare

cursor c_job
is select empno,ename,job,sal
from emp where job='manager';
c_row c_job%rowtype;//可以省去，因为for循环里的in的作用
begin

for c_row in c_job loop
dbms_output.put_line(c_row.empno||' '||c_row.ename||' '||c_row.job||' '||c_row.sal);
end loop;
end;

2:

declare
cursor c_job
is select * from emp
where job='manager';
c_row c_job%rowtype;
begin
open c_job;
loop
fetch c_job into c_row;
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||' '||c_row.ename||' '||c_row.job||' '||c_row.sal);
end loop;
close c_job;
end;


3：随便用一个update语句，看看隐式游标sql%notfound,sql%found,sql%isopen,sql%rowcount
begin
update emp set job='manger' where empno ='1111';
if sql%isopen then
  dbms_output.put_line('opening');
else
dbms_output.put_line('closing');
end if;

if sql%found then
  dbms_output.put_line('游标指向了有效行');
else
  dbms_output.put_line('novalues');  
end if;

dbms_output.put_line(sql%rowcount);

exception
when no_data_found then
  dbms_output.put_line('no data found');
when too_many_rows then
 dbms_output.put_line('too many rows');

end;




 
 declare
 empnumber emp.empno%type;
 empname emp.ename%type;
 begin
 if sql%isopen then
   dbms_output.put_line('cursor is openning');
 else
   dbms_output.put_line('cursor is closing');
 end if;
 if sql%notfound then
   dbms_output.put_line('not values');
 else
   dbms_output.put_line(empnumber);
 end if;
 dbms_output.put_line(sql%rowcount);
 dbms_output.put_line('-----------');
 select EMPNO,ENAME into  empNumber,empName from emp where EMPNO='1111';
 dbms_output.put_line(sql%rowcount);
 if sql%isopen then
   dbms_output.put_line('cursor is closing');
 else
   dbms_output.put_line('cursor is opening');
 end if;
 if sql%found then
   dbms_output.put_line(empnumber);
 else
   dbms_output.put_line('no values');
 end if;
 exception
 when no_data_found then
    dbms_output.put_line('No Value');
 when too_many_rows then
    dbms_output.put_line('too many rows');
 end;

   
4:使用游标和for 循环显示所有员工编号和姓名
declare
cursor c_emp is
select empno,ename from emp;
begin
for c_row in c_emp loop
  dbms_output.put_line('Empno:'||c_row.empno||' Ename:'||c_row.ename);
end loop;
end;



5:使用游标和while循环显示所有员工编号和姓名
（1）把游标所蕴含的变量分别into到相同类型的变量，把变量输出

declare
c_empno emp.empno%type;
c_name emp.ename%type;
cursor c_emp is
select empno,ename from emp;
begin
open c_emp;
fetch c_emp into c_empno,c_name;
while   c_emp%found  loop
  dbms_output.put_line('Empno:'||c_empno||' Ename:'||c_name);
  fetch c_emp into c_empno,c_name;
end loop;
close c_emp;
end;

6:使用游标和loop循环显示所有员工编号和姓名
(2)直接定义一个指向游标变量指向游标的一行数据
declare
cursor c_emp is
select empno,ename from emp;
c_row c_emp%rowtype;//定义一个游标变量指向c_emp游标的一行数据
begin
open c_emp;
loop
fetch c_emp into c_row;
exit when c_emp%notfound;
dbms_output.put_line('Empno:'||c_row.empno||' Ename:'||c_row.ename);
end loop;
close c_emp;
end;


7:使用有参数的游标显示指定部门名称的所有员工编号姓名
declare
cursor c_emp(deptname varchar) is
select empno,ename from emp
where deptno in(select deptno from dept where dname=deptname);
c_row c_emp%rowtype;

begin
for c_row in c_emp('sale') loop
dbms_output.put_line('Empno:'||c_row.empno||' Ename:'||c_row.ename);
end loop;

end;



8：使用游标来对 游标结果集合中当前行所对应的数据库进行delete或update操作
该需求迫使ORACLE锁定游标结果集合的行，
可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
语法：
/*nowait表示马上执行该程序，如果游标本该指向的结果集合已被其他事务锁定，则报错*/

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
where current of 游标变量 /*表示在该游标所在当前行*/


create table emp1 as select * from emp;
(1)
declare
cursor c_emp(deptname varchar) is
select * from emp1
where deptno in(select deptno from dept where dname=deptname) for update of sal nowait ;

newsal number;
c_row c_emp%rowtype;
begin
open c_emp('sale');
loop
fetch c_emp into c_row;
exit when c_emp%notfound;
if c_row.sal<1000 then
  newsal:=c_row.sal*5;
elsif c_row.sal<3000 then
  newsal:=c_row.sal*3;
else
  newsal:=c_row.sal*2;
end if;

update emp1 set sal=newsal where current of c_emp;
end loop;
close c_emp;
end;

(2)当然，也可以通过游标做一般的修改，只是在确定更新或删除的行时where 的判断条件要注意选择恰当

declare
cursor c_emp(deptname varchar) is
select * from emp1
where deptno in(select deptno from dept where dname=deptname);

newsal number;
c_row c_emp%rowtype;
begin
open c_emp('leader');
loop
fetch c_emp into c_row;
exit when c_emp%notfound;
if c_row.sal<1000 then
  newsal:=c_row.sal*5;
elsif c_row.sal<3000 then
  newsal:=c_row.sal*3;
else
  newsal:=c_row.sal*2;
end if;

update emp1 set sal=newsal where empno=c_row.empno;
end loop;
close c_emp;
end;



9：动态游标
declare
type emp_cursor is ref cursor;
my_cursor emp_cursor;
tab varchar2(100);
str varchar2(120);
c_row emp_cursor%rowtype;
begin
str = 'select * from '||tab;
open my_cursor for str;
loop
fetch my_cursor into c_row;
exit when my_cursor%notfound;
end loop;
end;