【開發篇plsql】plsql遊標
4,遊標
經典的資料庫程式設計中，用來處理返回一個查詢結果時使用的內部資料結構叫做遊標。
Plsql的遊標可以分為隱式遊標和顯式遊標。
顯式遊標由使用者顯式宣告，遊標將指向活動集中的當前行，可以顯式的開啟，提取，關閉。例如：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp;
4 v_emp scott.emp%rowtype;
5 begin
6 open cur_emp;
7 fetch cur_emp
8 into v_emp;
9 DBMS_output.put_line('empno: ' || v_emp.empno || ' ename: ' ||
10 v_emp.ename);
11 close cur_emp;
12 end;
13 /
PL/SQL procedure successfully completed
或者也可以使用for loop 的形式來開啟和使用遊標：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp where rownum = 1;
4 v_emp scott.emp%rowtype;
5 begin
6 for cs in cur_emp loop
7 dbms_output.put_line('cs.empno:'||cs.empno);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed
也可以巢狀定義遊標：
SQL> declare
2 cursor cur_emp is
3 select * from scott.emp a where a.empno = 7369;
4
5 cursor cur_emp_1 is
6 select cursor (select * from scott.emp a where a.empno = 7369)
7 from scott.emp a where rownum <4;
8 cur_type sys_refcursor;
9 v_emp scott.emp%rowtype;
10
11 begin
12 open cur_emp_1;
13
14 loop
15 fetch cur_emp_1
16 into cur_type;
17 exit when cur_emp_1%notfound;
18 dbms_output.put_line('begin outer loop:');
19 loop
20 fetch cur_type
21 into v_emp;
22 exit when cur_type%notfound;
23 dbms_output.put_line('begin inner loop:');
24 dbms_output.put_line(v_emp.empno);
25 end loop;
26 end loop;
27 close cur_emp_1;
28 end;
29 /
begin outer loop:
begin inner loop:
7369
begin outer loop:
begin inner loop:
7369
begin outer loop:
begin inner loop:
7369
PL/SQL procedure successfully completed
遊標屬性
注意到上述的
exit when cur_emp_1%notfound;
這裡是迴圈退出的條件， notfound是遊標的屬性之一，plsql提供了幾個遊標屬性：
|
名稱 |
描述 |
|
%FOUND |
記錄成功提取，返回true，否則返回false |
|
%NOTFOUND |
記錄沒有成功提取，返回true，否則返回false |
|
%ROWCOUNT |
當前從遊標中提取的記錄數量 |
|
%ISOPEN |
遊標是否已經開啟 |
|
%BULK_ROWCOUNT |
返回由forall修改了的記錄數量 |
|
%BULK_EXCEPTIONS |
返回forall語句修改後的行的例外資訊 |
%BULK_ROWCOUNT和%BULK_EXCEPTIONS這兩個屬性在批量處理中示意。
隱式遊標
Plsql在每次執行一個DML語句（insert,update,delete）或者使用select into從資料庫中直接返回單行到plsql資料庫結構中是，都會定義和操縱一個隱式遊標。
之所以稱做隱式，是因為oracle自動的管理遊標相關的操作，比如分配一個遊標，開啟一個遊標，
提取記錄，乃至關閉遊標。例如：
SQL> declare
2 v_emp scott.emp%rowtype;
3 i number := 0;
4 begin
5 for cur_emp in (select * from scott.emp where rownum = 1) loop
6 DBMS_output.put_line(i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed
遊標變數
可以在資料庫中宣告一個遊標的引用變數，可以在執行時賦值或者改變。
同時可以將遊標變數作為函式或者過程的引數，當需要傳遞結果集到其他環境(比較java)的時候，這種用法很有用。
宣告的遊標變數可以定義返回型別（強型別）或者不定義返回型別（弱型別），示例：
SQL> declare
2 v_emp scott.emp%rowtype;
3 type t_cur_emp_str is ref cursor return scott.emp%rowtype;
4 v_cur_emp_str t_cur_emp_str;
5 type t_cur_emp_weak is ref cursor;
6 v_cur_emp_weak t_cur_emp_weak;
7
8 begin
9 open v_cur_emp_str for
10 select * from scott.emp where rownum = 1;
11
12 fetch v_cur_emp_str
13 into v_emp;
14
15 close v_cur_emp_str;
16
17 open v_cur_emp_weak for
18 select * from scott.emp where rownum = 1;
19
20 fetch v_cur_emp_weak
21 into v_emp;
22 close v_cur_emp_weak;
23 end;
24 /
PL/SQL procedure successfully completed
或者用來作為函式或者儲存過程的引數：
SQL> declare
2 type tp_cur_emp_str is ref cursor return scott.emp%rowtype;
3 cur_emp_str tp_cur_emp_str;
4 v_emp_row scott.emp%rowtype;
5
6 function func_use_cur(cur in tp_cur_emp_str) return number is
7 v_emprow scott.emp%rowtype;
8 num number := 0;
9 begin
10 loop
11 fetch cur
12 into v_emprow;
13 exit when cur%notfound;
14 num := cur%rowcount;
15 end loop;
16 return num;
17 end;
18
19 procedure proc_use_cur(cur in tp_cur_emp_str) is
20 v_emprow scott.emp%rowtype;
21 begin
22 loop
23 fetch cur
24 into v_emprow;
25 exit when cur%notfound;
26 dbms_output.put_line('cur%rowcount :' || cur%rowcount);
27 dbms_output.put_line('v_emprow.empno:' || v_emprow.empno);
28 end loop;
29 end;
30
31 begin
32 open cur_emp_str for
33 select * from scott.emp a where rownum < 5;
34 dbms_output.put_line('func_use_cur(cur_emp_str): ' ||
35 func_use_cur(cur_emp_str)||chr(10));
36 close cur_emp_str;
37
38 open cur_emp_str for
39 select * from scott.emp a where rownum < 5;
40 proc_use_cur(cur_emp_str);
41 close cur_emp_str;
42
43 end;
44 /
func_use_cur(cur_emp_str): 4
cur%rowcount :1
v_emprow.empno:1
cur%rowcount :2
v_emprow.empno:7369
cur%rowcount :3
v_emprow.empno:7499
cur%rowcount :4
v_emprow.empno:7521
PL/SQL procedure successfully completed
也可以用作函式的返回值，或者函式和儲存過程的out 引數，或者結合管道函式方面的使用，在過程和函式小結時再具體舉例。
可以使用 where current of更新遊標中當前行如：
SQL> declare
2 cursor cs is
3 select * from scott.emp a where a.empno = 1 for update;
4 begin
5 for css in cs loop
6 update scott.emp a set a.ename = 'ename' where current of cs;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed
再來看看使用顯式遊標與select into的隱式遊標在讀取一行時的比較，可以看出單行讀取的情況下好像隱式遊標稍稍好一點：
SQL> set timi on;
SQL> declare
2 cursor cs is
3 select dummy from dual;
4 v_dummy dual.dummy%type;
5 begin
6 for i in 1 .. 100000 loop
7 open cs;
8 fetch cs
9 into v_dummy;
10 close cs;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed
Executed in 4.141 seconds
SQL>
SQL> declare
2 v_dummy dual.dummy%type;
3 begin
4 for i in 1 .. 100000 loop
5 select dummy into v_dummy from dual;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed
Executed in 3.172 seconds
很多時候把操作儘可能的讓oracle來做效果要好一點。
如果上面一個不明顯的話，再看一個含有order by的cursor的例子：
create table t_test_cusor as select * from all_objects a ;
/
SQL> select count(*) from all_objects a ;
COUNT(*)
----------
59069
Executed in 3.265 seconds
SQL> declare
2 cursor cs is
3 select object_name from t_test_cusor a order by object_id desc;
4 v_object_name t_test_cusor.object_name%type;
5 begin
6 for i in 1 .. 500 loop
7 open cs;
8 fetch cs
9 into v_object_name;
10 close cs;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed
Executed in 19.954 seconds
SQL> declare
2 v_object_name t_test_cusor.object_name%type;
3 begin
4 for i in 1 .. 500 loop
5 select object_name
6 into v_object_name
7 from (select object_name from t_test_cusor order by object_id desc)
8 where rownum = 1;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed
Executed in 11.532 seconds
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-664455/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle plsqlOracleSQL
- PLSQL Developer 14SQLDeveloper
- plsql use skillsSQL
- plsql配置使用SQL
- PLSQL程式語言SQL
- PLSQL Developer 行號SQLDeveloper
- plsql中文亂碼SQL
- windows安裝plsqlWindowsSQL
- PLSQL Developer配置使用SQLDeveloper
- 0710_plsql 函式SQL函式
- [20180511]PLSQL與fetchsize.txtSQL
- PLSQL Developer 12 註冊碼SQLDeveloper
- plsql 除錯 pipelined 函式SQL除錯函式
- plsql developer 視窗的使用SQLDeveloper
- 6. PLSQL 編寫規範SQL
- 配置PLSQL Developer詳細步驟SQLDeveloper
- PLSQL只匯出表結構SQL
- plsql developer 15的安裝配置SQLDeveloper
- plsql developer工具生成sql monitor reportSQLDeveloper
- Oracle vs PostgreSQL Develop（29） - COMMIT in PLSQLOracleSQLdevMIT
- [20220708]PLSQL – choosing the BEST data type.txtSQL
- [20210621]plsql_code_type=native.txtSQL
- PLSQL一些常用的知識點SQL
- PLSQL安裝與使用問題彙總SQL
- plsql以及instantclient下載安裝配置使用SQLclient
- 使用plsql 匯出欄位為json 格式SQLJSON
- [20211116]plsql_code_type=native補充.txtSQL
- 關於plsql，crt登入比較慢SQL
- 解決plsql中中文亂碼問題SQL
- PLSQL Developer 複製查詢結果 卡頓SQLDeveloper
- plsql中資料生成工具data generator的使用SQL
- [20211027]引數plscope_settings分析PLSQL.txtSQL
- [20221130]PLSQL的變數作用範圍(linux).txtSQL變數Linux
- plsql developmer 匯出資料和生成資料SQLdev
- [20210218]Select vs Assign – How To Assign PLSQL Variables.txtSQL
- 用PLSQL建立新使用者及匯入dmpSQL
- Oracle儲存過程乾貨（二）：PLSQL控制語句Oracle儲存過程SQL
- 7_Oracle truncate異常恢復之plsql修復OracleSQL
- Oracle vs PostgreSQL Develop（27） - PLSQL vs PLPGSQL（輸出引數）OracleSQLdev