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=