---遊標：類似於C語言中的指標

遊標：分為隱式遊標和顯式遊標

隱式遊標：系統自動定義的遊標，記錄集只有單行資料，用於處理select into 和DML語句

顯示遊標:即使用者自定義遊標，專門用於處理select語句返回的多行資料

遊標使用的一般過程：

顯示遊標：宣告, 開啟, 讀取, 關閉

隱式遊標：直接使用讀取，宣告、開啟、關閉都是系統自動進行的

----顯式遊標：包括宣告, 開啟, 讀取, 關閉

declare

cursor cur is

select * from emp;

my_cur cur%rowtype;

begin

open cur;

loop

fetch cur

into my_cur;

exit when cur%notfound;

dbms_output.put_line(my_cur.job);

end loop;

close cur;

end;

---------------

DECLARE

v_deptno emp.deptno%type;

type ename_table_type is table of varchar2(10); --定義PL/SQL表型別

ename_table ename_table_type; --定義PL/SQL表變數存放遊標資料

cursor emp_cur is

select ename from emp where deptno = v_deptno;

BEGIN

v_deptno := &inputno;

open emp_cur;

fetch emp_cur bulk collect

into ename_table; --使用bulk collect into提取所有資料

for i in 1 .. ename_table.count loop

dbms_output.put_line(ename_table(i));

end loop;

close emp_cur;

END;

---遊標的屬性：

cursor_name%ISOPEN 遊標是否開啟

cursor_name%FOUND 最近的FETCH是否提取到資料

cursor_name%NOTFOUND 最近的FETCH是否沒有提取到資料

cursor_name%ROWCOUNT 返回到目前為止，已經從遊標緩衝區中提取到資料的行數

DECLARE

v_deptno emp.deptno%type type ename_table_type is table of varchar2(10);

ename_table ename_table_type;

cursor emp_cur is

select ename from emp where deptno = v_deptno;

BEGIN

v_deptno := &inputno;

if not emp_cur%isopen then

--判斷遊標是否開啟，如未開啟，則開啟遊標

open emp_cur;

end if;

fetch emp_cur bulk collect

into ename_table;

dbms_output.put_line('All record counts from cursor is : ' ||

emp_cur%rowcount); --使用cursor_name%rowcount 統計遊標的記錄數

close emp_cur;

END;

----使用遊標更新記錄

透過遊標既可以逐行檢索結果集中的記錄，又可以更新或刪除當前遊標行的資料

如果要透過遊標更新和刪除資料，在定義遊標時必須要帶有FOR UPDATE子句格式：

CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]

FOR UPDATE ：子句用於在遊標結果集資料上加行共享鎖，以防止其它使用者在相應行上執行DML操作

OF ：子句用於遊標子查詢到多張表時來確定哪些表要加鎖，如未指定，則select語句所引用的全部表將被加鎖

NOWAIT ：子句指定不等待鎖

使用DML語句操作遊標中的當前行時，需要在update或delete語句中引用where current of子句

UPDATE tbname set col1=.. WHERE CURRENT OF cursor_name;

DELETE tbname WHERE CURRENT OF cursor_name;

--例：使用遊標修改所有記錄的工資，根據JOB來作不同的修改。

create table tb_emp as select * from emp;

DECLARE

v_job tb_emp.job%TYPE;

CURSOR emp_cur IS

SELECT job FROM tb_emp FOR UPDATE; --定義時，使用FOR UPDATE

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur

INTO v_job;

EXIT WHEN emp_cur%NOTFOUND;

CASE

WHEN v_job = 'CLERK' THEN

UPDATE tb_emp

SET sal = sal * 1.1

WHERE CURRENT OF emp_cur; --注意，需要使用WHERE CURRENT OF

WHEN v_job = 'SALESMAN' THEN

UPDATE tb_emp

SET sal = sal * 1.08

WHERE CURRENT OF emp_cur;

ELSE

UPDATE tb_emp

SET sal = sal * 1.05

WHERE CURRENT OF emp_cur;

END CASE;

END LOOP;

CLOSE emp_cur;

END;

--例：利用遊標刪除資料

DECLARE

v_job tb_emp.job%type;

v_sal tb_emp.sal%type;

cursor emp_cur is

select job, sal from tb_emp for update;

BEGIN

open emp_cur;

fetch emp_cur

into v_job, v_sal;

while emp_cur%found loop

if v_sal > 3000 then

delete from tb_emp where current of emp_cur;--注意，需要使用WHERE CURRENT OF

end if;

fetch emp_cur

into v_job, v_sal;

end loop;

close emp_cur;

END;

/

--例：使用OF子句對特定的表加共享鎖

DECLARE

cursor emp_cur is

select ename, sal, dname, e.deptno

from emp e

join dept d

on e.deptno = d.deptno

for update of e.deptno;---使用OF子句對特定的表加共享鎖

emp_record emp_cur%rowtype;

BEGIN

open emp_cur;

loop

fetch emp_cur

into emp_record;

exit when emp_cur%notfound;

if emp_record.deptno = 20 then

update emp

set sal = sal + 100

where current of emp_cur;

end if;

dbms_output.put_line('Ename: ' || emp_record.ename ||

',Sal: ' || emp_record.sal ||

',Deptname:' || emp_record.dname);

end loop;

close emp_cur;

END;

--例:NOWAIT子句的使用

DECLARE

v_ename emp.ename%type;

v_oldsal emp.sal%type;

cursor emp_cur is

select ename, sal from emp for update nowait; --使用nowait子句指定不等待鎖，會給出錯誤提示

BEGIN

open emp_cur;

loop

fetch emp_cur

into v_ename, v_oldsal;

exit when emp_cur%notfound;

if v_oldsal < 2000 then

update emp

set sal = sal + 200

where current of emp_cur;-----注意，需要使用WHERE CURRENT OF

end if;

end loop;

close emp_cur;

END;

---遊標FOR迴圈

遊標FOR迴圈是為了簡化遊標使用過程而設計的。

使用遊標FOR迴圈檢索遊標時，遊標的開啟、資料提取、資料是否檢索到的判斷與遊標

的關閉都是ORACLE系統自動進行的。

遊標FOR迴圈兩種語句格式：

格式一：

先在定義部分定義遊標，然後在遊標FOR迴圈中引用該遊標

FOR record_name IN cursor_name LOOP

statement1;

statement2;

END LOOP;

格式二：

在FOR迴圈中直接引用子查詢，隱式定義遊標

FOR record_name IN subquery LOOP

statement;

END LOOP;

--例：定義遊標並使用for迴圈逐個顯示記錄（顯式遊標）

DECLARE

v_job emp.job%TYPE;

CURSOR emp_cur IS

SELECT ename, sal FROM emp WHERE job = v_job;

BEGIN

v_job := '&inputjob';

DBMS_OUTPUT.PUT_LINE('NO. Name Sal');

FOR emp_record IN emp_cur LOOP ----記錄名稱

DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT || ' ' ||

emp_record.ename || ' ' ||

emp_record.sal);

END LOOP;

END;

/

--例：直接在遊標for迴圈中使用子查詢來逐個顯示記錄（隱式遊標）

DECLARE

v_job emp.job%TYPE;

BEGIN

v_job := '&inputjob';

DBMS_OUTPUT.PUT_LINE('Name Sal');

FOR emp_record IN (SELECT ename, sal FROM emp WHERE job = v_job) LOOP

DBMS_OUTPUT.PUT_LINE(emp_record.ename || ' ' ||

emp_record.sal);

END LOOP;

END;

datatype 只指定資料型別即可，不能指定引數的長度、精度、刻度

開啟引數遊標：

OPEN cursor_name [(vlaues)]

引數個數、型別必須與定義時的形參相匹配。

對於定義的引數遊標，一定要在遊標子查詢的where子句中指定定義的引數，否則將使得引數遊標失去意義

例：用部門編號deptno作形參，顯示每個人的姓名和工資

遊標變數

簡言之，其一是一個遊標，其次則是一個變數，因此稱之為遊標變數，可以用來儲存不同的遊標

對於遊標變數的使用，在開啟遊標變數時指定其對應的select語句

1.遊標變數的使用步驟

a.定義REF CURSOR 型別和遊標變數

TYPE ref_type_name IS REF CURSOR [RETURN return_type]; --必須先定義REF CURSOR型別

cursor_variable ref_type_name; --接下來再定義遊標變數

ref_type_name: 指定自定義的型別名

RETURN: 指定REF CURSOR返回結果的資料型別

cursor_variable: 定義遊標變數的名字

注：若指定RETURN子句，其資料型別必須是記錄型別，此外，不能在包內定義遊標變數

b.開啟遊標

在開啟遊標時必須指定其對應的select語句，一旦開啟遊標變數則對應的select結果集將存放到遊標變數中

OPEN cursor_variable FOR select_statement;

c.提取資料

提取資料與普通的顯示遊標提取資料的方法類似

FETCH cursor_variable INTO variable1,...variable2 ; --提取單行資料，需要配合迴圈語句來使用

FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows]; --提取多行資料，collect為集合變數

d.關閉遊標變數

CLOSE cursor_vairable;

2.遊標變數使用的例子

--例.根據部門名稱顯示該部門的所有僱員(定義REF CURSOR時不指定RETURN子句)

DECLARE

type emp_cur_type is ref cursor; --定義遊標型別為ref cursor

emp_cur emp_cur_type; --定義遊標變數為emp_cur

emp_record emp%rowtype; --定義遊標變數記錄型別為emp_record

v_deptno emp.deptno%type;

BEGIN

v_deptno := &inputno;

open emp_cur for

select * from emp where deptno = v_deptno;

dbms_output.put_line('No Name');

loop

fetch emp_cur

into emp_record;

exit when emp_cur%notfound;

dbms_output.put_line(emp_cur%rowcount || ' ' || emp_record.ename);

end loop;

close emp_cur;

END;

--例:根據部門名稱顯示該部門的所有僱員名字及薪水(定義REF CURSOR時指定RETURN子句)

DECLARE

type emp_record_type is record(

name varchar2(10),

salary number(6, 2)); --定義PL/SQL記錄變數型別

type emp_cur_type is ref cursor return emp_record_type; --定義遊標型別為ref cursor，且具有返回型別

emp_cur emp_cur_type; --定義遊標變數為emp_cur

emp_record emp_record_type; --定義型別為emp_record_type記錄變數emp_record

v_deptno emp.deptno%type;

BEGIN

v_deptno := &inputno;

open emp_cur for

select ename, sal from emp where deptno = v_deptno;

dbms_output.put_line('Name Salary');

loop

fetch emp_cur

into emp_record;

exit when emp_cur%notfound;

dbms_output.put_line(emp_record.name || ' ' || emp_record.salary);

end loop;

close emp_cur;

END;

--例：遊標變數的多次使用

DECLARE

type cur_type is ref cursor;

scott_cur cur_type;

v_emp emp%rowtype;

v_dept dept%rowtype;

BEGIN

open scott_cur for

select * from emp where deptno = 10; --使用for select首次開啟遊標

dbms_output.put_line('No, Name');

loop

fetch scott_cur

into v_emp;

exit when scott_cur%notfound;

dbms_output.put_line(scott_cur%rowcount || ',' || v_emp.ename);

end loop;

open scott_cur for

select * from dept where deptno = 10; --使用for select 再此開啟遊標，此次載入了不同資料

dbms_output.put_line('Deptno, Name');

loop

fetch scott_cur

into v_dept;

exit when scott_cur%notfound;

dbms_output.put_line(v_dept.deptno || ',' || v_dept.dname);

end loop;

END;

資料庫開發---常用物件-遊標

相關文章