Cursor語法及理解

遊標的兩種概念

共享遊標 : 是使用者提交 SQL 或 PL/SQL 程式塊到 Oracle 的 share pool 之後，在 library cache 中生成的一個可執行物件，這個物件我們稱之為遊標（ cursor ）。是 SQL 語句在進行硬解析時生成的，其後設資料被在檢視 V$sqlarea 與 v$sql 中具體化。

PL/SQL 遊標 : 則是用於存放 SQL 語句的執行結果 , 使用者可以通過這個中間緩衝區逐條取出遊標中的記錄並對其處理，直到所有的遊標記錄被逐一處理完畢。需要宣告、開啟、提取、關閉。

共享遊標包括父遊標和子游標。

父遊標： 是在進行硬解析時產生的。將 SQL 語句的文字進行雜湊得到雜湊值並在 library cache 尋找相同的雜湊值（ SQL 語句必須完全一致包括大小寫、空格回車等才能共享），如不存在則生存父遊標且儲存在 library cache 中，按順序完成後續步驟。如果此時存在父遊標，則進一步判斷是否存在子游標。若存在相同的子游標，則直接呼叫其子游標的執行計劃執行該 SQL 語句，否則轉到下一步進行邏輯優化。

子游標： 在發生硬解析時，在產生父遊標的同時，則跟隨父遊標會產生相應的子游標，此時 V$SQL.CHILD_NUMBER 的值為。如果存在父遊標，由於不同的執行環境，此時同樣會產生新的子游標，新子游標的 CHILD_NUMBER 在已有子游標基礎上以 1 為單位累計。 v$sql 中的每一行表示了一個 child cursor 子游標，根據 sql_id 與父 cursor 關聯。 child cursor 有自己的 address ，即 v$sql.child_address 。如果你想確定是由那種原因造成的子游標，需要檢視 v$sql_shared_cursor 。

1. 父遊標的關鍵資訊是 sql 文字，子游標的關鍵資訊是執行計劃和執行環境。

2. 硬解析通常是由於不可共享的父遊標造成的，如經常變動的 SQL 語句，或動態 SQL 或未使用繫結變數等。解決硬解析的辦法則通常是使用繫結變數來解決。

3. 與父遊標 SQL 文字完全一致的情形下，多個相同的 SQL 語句可以共享一個父遊標。

4.SQL 文字、執行環境完全一致的情形下，子游標能夠被共享，否則如果執行環境不一致則生成新的子游標。如果 SQL 文字相同，但是可能提交 SQL 語句的使用者不同，或者使用者提交的 SQL 語句所涉及到的物件為同名詞等，都有可能生成不同的子游標。因為這些 SQL 語句的文字雖然完全一樣，但是上下文環境卻不一樣，因此這樣的 SQL 語句不是一個可執行的物件，必須細化為多個子遊標後才能夠執行。

5. 遊標是可以被所有程式共享的，也就是說如果 100 個程式都執行相同的 SQL 語句，那麼這 100 個程式都可以同時使用該 SQL 語句所產生的遊標，從而節省了記憶體。

這裡討論的遊標是指 pl/sql 語句中定義的遊標（ session cursor ） 。

Oracle 在 PL / SQL 中當查詢返回結果超過一行時，從中每次指向一條記錄進行互動的話，就需要用到遊標。

例如： SELECT INTO 查詢語句，一次只能從資料庫中 select 一行資料來進行 insert ，但是如果要處理多行資料，就要由程式設計師定義一個顯式遊標，並通過與遊標有關的語句進行處理。

說明： select * into new_table from table; 在 sqlserver 中正常，但 oracle 會報錯，在 oracle 裡面 select...into... 是 pl/sql 的變數賦值語句

例如： select count(*) into v_count from table_name where id=1; 意思就是把 id=1 的數量放到一變數 v_count 中

當然 insert into new_table select * from table 都是在 sqlserver 或 oracle 中都是正常的，都可以一條 insert 語句插入多行資料

個人一般使用 for m in (select * from emp)loop 來替代顯式遊標的功能

顯式遊標（需要明確定義 cursor ，即有定義 cursor 的就是顯示遊標）

fetch 遊標則必須用到顯式遊標，需要 open 和 close

顯式遊標語法如下

declare

cursor mycursor1 is select * from emp; -- 宣告遊標

table_row mycursor1%rowtype; --mycursor1%rowtype 表示資料型別是一行資料

xx number;

begin

open mycursor1; -- 開啟遊標

loop

fetch mycursor1 into table_row; -- 取出遊標結果放入變數 table_row

exit when mycursor1%notfound;

xx:=table_row.empno;

insert into emp_2 (empno,ename,job ) values (xx,table_row.ename,table_row.job);

end loop;

commit;

close mycursor1; -- 關閉遊標

end;

如下顯式遊標 fetch 遊標名 into 變數 , 變數 , 變數，且變數不需要再定義為遊標名 %rowtype, 但是 sql 出現遊標名 . 欄位則會報錯 PLS-00225: subprogram or cursor 'MYCURSOR111' reference is out of scope

declare

cursor mycursor111 is select empno,ename,job from emp; -- 宣告遊標

xx number;

yy varchar2(10);

zz varchar2(10);

begin

open mycursor111; -- 開啟遊標

loop

fetch mycursor111 into xx,yy,zz; -- 取出遊標結果放入變數 xx,yy,zz

exit when mycursor111%notfound;

insert into emp_2 (empno,ename,job ) values (xx,yy,zz);

end loop;

commit;

close mycursor111; -- 關閉遊標

end;

顯示遊標 fetch 的注意事項

1. 遊標對應的不管是一個還是多個欄位， fetch 遊標名 . 欄位 into 變數，會報錯 PLS-00225: subprogram or cursor 'MYCURSOR111' reference is out of scope ，但是可以出現 fetch 遊標名 into 欄位 1 對應的變數 1, 欄位 2 對應的變數 2, 欄位 3 對應的變數 3 ，這裡這些變數可以不需要定義為 %rowtype ，如果定義為 %rowtype ，則只能 into 一個 %rowtype 的變數， fetch 遊標名 into 遊標名 %rowtype

2. 遊標對應多個欄位時， Fetch 遊標名 into 變數時，其實是按順序把欄位一個個 into 到變數的，如果遊標對應的欄位有 3 個，但是 into 的變數只有 2 個，則報錯 PLS-00394: wrong number of values in the INTO list of a FETCH statement

3. 遊標必須 open ，否則會報錯 ORA-01001: 無效的遊標，當然如果沒有 close 的話執行過程中不會報錯，但是遊標會一直儲存在 PAG 中，直到 session 關閉才會釋放，所以如果很多顯式都不關閉會導致 PGA 很大。

遊標 FOR 迴圈確實很好的簡化了遊標的開發，我們不在需要 open 、 fetch 和 close 語句，不在需要用 %FOUND 屬性檢測是否到最後一條記錄，這一切 Oracle 隱式的幫我們完成了。 （雖然是顯式遊標，不過個人覺得更像隱式遊標）

declare cursor mycursor1 is select * from emp; -- 宣告 cursor

vv varchar2(200);

begin

--open mycursor1; -- 這裡 open 遊標反倒會報錯

for m in mycursor1 loop --m 不需要宣告

vv:=m.empno;

insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);

end loop;

commit;

--close mycursor1; -- 這裡 close 遊標反倒會報錯

end;

下面語句只是比上面語句多了一行 xx mycursor11%rowtype ，發現不需要這一行也一樣。

declare cursor mycursor11 is select * from emp; -- 宣告 cursor

xx mycursor11%rowtype;

yy varchar2(100);

begin

--open mycursor11; -- 這裡 open 遊標反倒會報錯

for xx in mycursor11 loop --m 不需要宣告

yy:=xx.empno;

insert into emp_2 (empno,ename,job ) values (yy,xx.ename,xx.job);

end loop;

commit;

--close mycursor11; -- 這裡 close 遊標反倒會報錯

end;

簡單的 for 迴圈連 cursor 都不用宣告瞭，此時 m 不需要宣告型別，這是隱式遊標

create table emp_2 as select * from emp where 1=2

declare vv varchar2(200);

begin

for m in (select * from emp)loop

vv:=m.empno;

insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);

end loop;

commit;

end;

相比如上，以下加了 sql%found 、 sql%rowcount 可以說明是隱式遊標

declare vv varchar2(200);

begin

for m in (select * from emp) loop

if sql%found then

dbms_output.put_line( sql%rowcount );

end if;

vv:=m.empno;

insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);

end loop;

commit;

end;

隱式遊標

DECLARE

hid VARCHAR2 ( 10 );

hname varchar ( 20 );

BEGIN

SELECT empno , ename INTO hid , hname FROM emp_2 ;

IF SQL % FOUND then

dbms_output.put_line ( hid || ' 的名字是 ' || hname );

end if;

END;

則如下報錯， select into 只能有一行結果，加上 where 條件限定在一行後就不會報錯了

ORA-01422: 實際返回的行數超出請求的行數

ORA-06512: 在 line 5

顯式遊標的個屬性

cursor_name%ISOPEN 遊標是否開啟

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

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

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

隱式遊標的屬性

SQL%ROWCOUNT 代表 DML 語句成功執行的資料行數

SQL%FOUND 值為 TRUE 代表插入、刪除、更新或單行查詢操作成功

SQL%NOTFOUND 與 SQL%FOUND 屬性返回值相反

SQL%ISOPEN DML 執行過程中為真，結束後為假

begin

for m in (select sign_id,SIGN_INFO_ID from MDS_SIGN_LIST) loop

insert into MDS_SIGN_LIST_v2 (SIGN_ID,SIGN_INFO_ID ) values (m.sign_id,m.SIGN_INFO_ID);

end loop;

end;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2117550/，如需轉載，請註明出處，否則將追究法律責任。

Cursor語法及理解

相關文章