PL/SQL-遊標和遊標變數的使用

遊標是什麼：
    遊標字面理解就是遊動的游標。
    用資料庫語言來描述：遊標是對映在結果集中一行資料上的位置實體，有了遊標使用者就可以訪問結果集中的任意一行資料了，將遊標放置到某行後，即可對該行資料進行操作，例如提取當前行的資料等等。
遊標的分類： 顯式遊標和隱式遊標
(1)、顯示遊標的使用：
    1.宣告遊標
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
    2.開啟遊標
open mycur(000627) 注：000627:引數
    3.讀取資料
fetch mycur into varno,varprice;
    4.關閉遊標
close mycur;

遊標的屬性
    oracle 遊標有4個屬性： %ISOPEN ， %FOUND ， %NOTFOUND， %ROWCOUNT
    %ISOPEN 判斷遊標是否被開啟，如果開啟%ISOPEN 等於true,否則等於false
    %FOUND %NOTFOUND 判斷遊標所在的行是否有效，如果有效，則%FOUNDD等於true，否則等於false
    %ROWCOUNT 返回當前位置為止遊標讀取的記錄行數。
    示例：
set serveroutput on;
declare
varno varchar2(20);
varprice varchar2(20);

CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
begin

if mycur%isopen = false then

open mycur(000627);
end if;
fetch mycur into varno,varprice;
while mycur%found
loop
dbms_output.put_line(varno||','||varprice);
if mycur%rowcount=2 then
exit;
end if;
fetch mycur into varno,varprice;
end loop;
close mycur;
end;
(2)、隱式遊標的使用：
如果在pl/sql程式中使用了select語句進行操作，pl/sql會隱含處理遊標定義，即稱做隱式遊標。這種遊標不需要宣告、開啟和關閉。
例：
Create or replace procedure cx_xm
(in_xh in char,out_num out char)
As
Begin
       Select xm into out_xm from xs where xh=in_xh;   /*隱式遊標必須使用into*/
    Dbms_output.put_line(out_xm);
End
使用隱式遊標時要注意以下幾點：
A、每一個隱式遊標必須有一個into;
B、和顯示遊標一樣，帶有關鍵字into接收資料的變數時資料型別要與列表一致。
C、隱式遊標一次只能返回移行資料。

典型遊標for 迴圈

遊標for迴圈和顯示遊標的一種快捷使用方式，它使用for迴圈依次讀取結果集中的行資料，當for迴圈開始時，遊標自動開啟（不需要 open）,每迴圈一次系統自動讀取遊標當前行的資料（不需要fetch)，當退出for迴圈時，遊標被自動關閉（不需要使用close）使用遊標for 迴圈的時候不能使用open語句，fetch語句和close語句，否則會產生錯誤。

set serveroutput on;
declare
cursor mycur(vartype number)is
select emp_no,emp_zc from cus_emp_basic
where com_no=vartype;
begin
for person in mycur(000627) loop
dbms_output.put_line('僱員編號:'||person.emp_no||',地址:'||person.emp_zc);
end loop;
end;

在遊標FOR迴圈中使用查詢

在遊標FOR迴圈中可以定義查詢，由於沒有顯式宣告所以遊標沒有名字，記錄名通過遊標查詢來定義。
DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN (SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;

遊標中的子查詢
語法如下：
CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!='ACCOUNTING');
可以看出與SQL中的子查詢沒有什麼區別。

遊標中的更新和刪除
在PL/SQL中依然可以使用UPDATE和DELETE語句更新或刪除資料行。顯式遊標只有在需要獲得多行資料的情況下使用。PL/SQL提供了僅僅使用遊標就可以執行刪除或更新記錄的方法。
UPDATE或DELETE語句中的WHERE CURRENT OF子串專門處理要執行UPDATE或DELETE操作的表中取出的最近的資料。要使用這個方法，在宣告遊標時必須使用FOR UPDATE子串，當對話使用FOR UPDATE子串開啟一個遊標時，所有返回集中的資料行都將處於行級（ROW-LEVEL)獨佔式鎖定，其他物件只能查詢這些資料行，不能進行 UPDATE、DELETE或SELECT...FOR UPDATE操作。
語法：
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]
在多表查詢中，使用OF子句來鎖定特定的表,如果忽略了OF子句，那麼所有表中選擇的資料行都將被鎖定。如果這些資料行已經被其他會話鎖定，那麼正常情況下ORACLE將等待，直到資料行解鎖。
在UPDATE和DELETE中使用WHERE CURRENT OF子串的語法如下：
WHERE{CURRENT OF cursor_name|search_condition}

例：
DELCARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
END

使用遊標變數：
與遊標類似，遊標變數指向多行查詢的結果集的當前行。但是，遊標與遊標變數是不同的，就像常量和變數的關係一樣。遊標是靜態的，遊標變數是動態的，因為它不與特定的查詢繫結在一起。
(1)、用法１：
declare
type cur_tab is ref cursor;
sqlcur cur_tab;
v_T3100101 char(2);
v_T3100102 char(10);
begin
open sqlcur for select T3100101,T3100102 from T31001;
loop
fetch sqlcur into v_T3100101,v_T3100102;
exit when sqlcur%notfound;
dbms_output.put_line(v_T3100101||v_T3100102);
end loop;
close sqlcur;
end;

(２)、用法２：用於儲存過程返回結果集
create or replace package selecttable
is
type cur_T31001 is ref cursor return T31001%rowtype;　　--注意，這裡沒有begin
end selecttable;

create or replace procedure T31001_select
(
cur out selecttable.cur_T31001       --引數型別定義為先前定義好的T31001
)
is
begin
open cur for
select * from T31001;
end T31001_select;
----------------------------------------------------------------------------------------------------------------------
通過從遊標工作區中抽取出來的資料，可以對資料庫中的資料進行操縱，包括修改與刪除操作。
要想通過遊標操縱資料庫，在定義遊標的時候，必須加上FOR UPDATE OF子句；

而且在UPDATE或DELETE時，必須加上WHERE CURRENT OF子句，則遊標所在行被更新或者刪除。