ORACLE PL/SQL 显式游标

显式游标

显式游标使用流程:

1.声明 declare

2.打开 open

3.获取 fetch

4.关闭 close

练习 1: 基本loop循环+显示游标的使用

DECLARE

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

CURSOR emp_cursor IS SELECT empno, ename FROM emp; --声明

BEGIN

OPEN emp_cursor; --打开

LOOP

FETCH emp_cursor INTO v_empno, v_ename; --获取

exit when emp_cursor%rowcount>20 or emp_cursor%notfound;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||' '|| v_ename);

END LOOP;

CLOSE emp_cursor; --关闭

END ;

/

练习 2: for循环+显示游标的使用

CURSOR emp_cursor IS SELECT empno, ename FROM emp;

OPEN emp_cursor;

FOR i IN 1..10 LOOP --数字for循环

FETCH emp_cursor INTO v_empno, v_ename;

CLOSE emp_cursor;

游标for循环:

declare

cursor emp_cursor is select rownum,empno,ename from emp; --声明

begin

for emp_record in emp_cursor loop --隐式打开隐式获取

exit when emp_cursor%rowcount>5;

dbms_output.put_line(emp_record.rownum||' '||

emp_record.empno||' '||

emp_record.ename);

end loop; --隐式关闭

end;

省略游标定义:

for r in (select ename from emp) loop

dbms_output.put_line(r.ename);

end loop;

高级显式游标(带参数的游标):

练习 1:通过传入不同的参数使打开游标时取到不同的结果集

cursor c1 (p_deptno number,p_job varchar2)

is

select empno,ename

from emp

where deptno=p_deptno

and job=p_job;

Dbms_output.put_line('first fetch cursor!');

for r_c1 in c1(10,'MANAGER') loop --open cursor时传入不同的实际参数得到不同的游标上下文!

Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);

Dbms_output.put_line('second fetch cursor!');

for r_c1 in c1(20,'MANAGER') loop

Dbms_output.put_line('third fetch cursor!');

for r_c1 in c1(30,'MANAGER') loop

练习:获取每个部门前两个雇员的信息

获取10部门前两个人的信息

cursor c1 is select * from scott.emp

where deptno=10;

for r1 in c1 loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

使用替代变量取指定部门的前两个人的信息

where deptno=&p_deptno;

使用高级游标代替替代变量

cursor c1(p_deptno number) is select * from scott.emp

where deptno=p_deptno;

for r1 in c1(10) loop

for r1 in c1(20) loop

for r1 in c1(30) loop

使用循环嵌套简化上面的代码

cursor c2 is select distinct deptno from scott.emp;

cursor c1(p_deptno number) is

select * from scott.emp

for r2 in c2 loop

for r1 in c1(r2.deptno) loop

练习 2:将每个部门工资小于1000的职员工资涨10%

cursor c1 is select deptno from scott.dept;

cursor c2 (p_deptno number,p_job varchar2)

and job=p_job

for update of sal;

for r_c1 in c1 loop

dbms_output.put_line('第'||c1%rowcount||'次获取游标c1' || '修改'||r_c1.deptno||'部门职员的工资');

for r_c2 in c2(r_c1.deptno,'CLERK') loop

if r_c2.sal<1000 then

update scott.emp set sal=sal*1.1

where current of c2;

end if;

丢失更新:

conn scott/tiger

create table t (id int,flag char);

for i in 1..10 loop

insert into t values (i,'Y');

commit;

session 1:

select x from t

where x =

(select x from

(select x from t

where y='Y' order by x)

where rownum<2)

for update;

select x,y from t

update t set y='N' where x=

where rownum<2));

update t set y='X' where x=