Oracle遊標

waynelo發表於2018-07-10
Oracle

遊標

　　遊標是sql的一個記憶體工作區，由系統或使用者以變數的形式定義，用於臨時儲存從資料庫中提取的資料塊。

　　遊標分為顯式遊標和隱式遊標，可自定義顯式遊標用來儲存多行多列的資料。

　　顯式遊標一旦開啟，就相當於執行了select語句，執行的結果集就儲存在遊標中。

隱式遊標

　　在進行DML操作和單行SELECT語句(select…into…)資料庫會自動使用隱式遊標，可通過呼叫隱式遊標變數直接取值，隱式遊標只儲存單個資料。

　　四個隱式遊標：

　　　　sql%rowcount：整型，用於記錄DML語句成功修改記錄的條數。

　　　　sql%found：布林型，true表示crud的操作成功。

　　　　sql%notfount：布林型，與sql%found屬性的值相反。

　　　　sql%isopen：布林型，DML執行過程中為true，執行結束後為false。

顯式遊標

　　使用步驟：

　　　　1.宣告遊標

　　　　　　在declear宣告，

　　　　　　語法：

　　　　　　　　corsor 遊標名[引數1 資料型別[,引數2 資料型別…]] is select語句；

　　　　2.開啟遊標

　　　　　　遊標在使用前必須開啟；

　　　　　　open 遊標名[(引數1[,引數2…..])]；–相當於執行is後面的select語句，並把查詢結果存入遊標中。

　　　　3.提取資料

　　　　　　fetch 遊標名 into 變數1[,變數2….]；–取一條(row)資料到變數中，變數的個數和型別要和select語句中欄位變數的個數和型別一致。

　　　　　　遊標開啟後有一個指標指向資料區，fetch語句每一次返回一行的資料，每次執行完指標指向下一行。可通過迴圈實現返回多行資料，控制迴圈可通過遊標的屬性%found和%notfount控制。

　　　　4.關閉遊標

　　　　　　close 遊標名；

　　　　　　遊標關閉後佔用的資源就被釋放，遊標變為無效。

　　　 顯式遊標必須手動關閉，顯式遊標開啟的數量有限制，預設為300，超過限制會報錯:ORA-01000: maximum open cursors exceeded

 

　　使用遊標的屬性，在遊標名後面帶上屬性以取得屬性的值。如：遊標名%FOUND；

　　　　%FOUND：指標當前指向資料區有資料時為true，無資料為false；

　　　　%NOTFOUND：與%found相反，無資料為true；

　　　　%ISOPEN：當前遊標已開啟為true，未開啟為false；

　　　　%ROWCOUNT：已從遊標中取出的資料的行數；

 

遊標變數(動態遊標)ref cursor：

　　遊標變數和普通遊標基本相同，不同之處在於遊標變數更加靈活，在宣告時不需要繫結查詢，並且遊標變數沒有引數。

　　遊標變數分為強型別和弱型別

　　　　強型別：列的數量和列的資料型別在定義遊標變數時即定義好。

　　　　弱型別：定義遊標變數時不定義列的資料的型別，在遊標被開啟時再確定每列的資料型別和列的數量，遊標的列數量和列的資料型別一旦被確定不能再更改。

　　用法：

　　　　1.宣告動態遊標型別

　　　　　　弱型別，不指定return type：type cur_type is ref cursor;   

　　　　　　強型別，指定return type：type cur_type is ref cursor return 表名%rowtype;

　　　　2.宣告自定義的動態遊標型別的變數：遊標名 cur_type;  

　　　　3.開啟動態遊標：open 遊標名 for select語句;

　　　　4.獲取遊標中的資料：fatch 遊標名 into 變數1[，變數2….];

　　　　5.關閉遊標：close 遊標名；

 

使用動態遊標的例項：

set serveroutput on
declare
　　type re_type is record(
　　name varchar2(200)
　　);
　　type ref_cursor is ref cursor return re_type;
　　c1 ref_cursor;
　　message varchar2(200);
begin
　　open c1 for select name from t_test;
　　loop
　　　　fetch c1 into message;
　　　　exit when c1%notfound;
　　　　dbms_output.put_line(`第`||c1%rowcount||`個：`||message);
　　end loop;
　　close c1;
end;

 

資料庫中關於遊標的設定

　　1.檢視Oracle遊標最大開啟數

　　　　show parameter cursor; –檢視所有和curcor有關的引數，自帶like關鍵字

　　2.檢視當前開啟的遊標

　　　　select * from v$open_cursor;

　　3.修改遊標的最大開啟數

　　　　alter system set open_cursors = 500 scope=both;–修改遊標的最大開啟數為500，

　　　　其中scope的取值為：

　　　　　　memory：僅修改記憶體，只會影響當前的使用，重啟資料庫就會失效。

　　　　　　spfile：僅修改配置檔案，不會影響本次使用，重啟資料庫才生效。

　　　　　　both：兩個都修改。

 

相關文章