Oracle數據庫游標的類型及使用實例全解
游標是SQL的一個內存工作區，由系統或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數據庫中提取的數據塊。Oracle數據庫的Cursor類型包含三種: 靜態游標：分為顯式（explicit）游標和隱式（implicit）游標；REF游標：是一種引用類型，類似于指針。下面我們一一介紹它們的使用。
1.隱式游標
1)Select …INTO…語句，DML語句，使用隱式Cursor。此外，還有一種使用FOR LOOP的Implicit Cursor用法。
2)可以通過隱式Cusor的屬性來了解操作的狀態和結果。Cursor的屬性包含：
SQL%ROWCOUNT 整型代表DML語句成功執行的數據行數。
SQL%FOUND 布爾型值為TRUE代表插入、刪除、更新或單行查詢操作成功。
SQL%NOTFOUND 布爾型與SQL%FOUND屬性返回值相反。
SQL%ISOPEN 布爾型DML執行過程中為真，結束后為假。
3) 隱式Cursor由系統自動打開和關閉.
例如：
- set serveroutput on
- declare
- begin
- update employees set employee_name='Mike' where employee_id=1001;
- if SQL%FOUND then
- dbms_output.put_line('Name is updated');
- else
- dbms_output.put_line('Name is not updated');
- end if;
- end;
- /
- set serveroutput on
- declare
- begin
- for tableInfo in (select * from user_tables) loop
- dbms_output.put_line(tableInfo.table_name);
- end loop;
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- end;
- /
2.顯式游標
1) 顯式Cursor的屬性包含：
游標的屬性 返回值類型 意義
%ROWCOUNT 整型 獲得FETCH語句返回的數據行數
%FOUND 布爾型 最近的FETCH語句返回一行數據則為真，否則為假
%NOTFOUND 布爾型 與%FOUND屬性返回值相反
%ISOPEN 布爾型 游標已經打開時值為真，否則為假
2) 對于顯式游標的運用分為四個步驟：
a 定義游標---Cursor [Cursor Name] IS;
b 打開游標---Open [Cursor Name];
c 操作數據---Fetch [Cursor name]
d 關閉游標---Close [Cursor Name]
以下是幾種常見顯式Cursor用法。
- <p>set serveroutput on
- declare
- cursor cur is select * from user_tables;
- tableInfo user_tables%rowtype;
- begin
- open cur;
- loop
- fetch cur into tableInfo;
- exit when cur%notfound;
- dbms_output.put_line(tableInfo.table_name);
- end loop;</p><p>exception
- when others then
- dbms_output.put_line(sqlerrm);</p><p> close cur;
- end;
- /</p>
- set serveroutput on
- declare
- cursor cur is select * from user_tables;
- begin
- for tableInfo in cur loop
- dbms_output.put_line(tableInfo.table_name);
- end loop;
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- end;
- /
還可以使用帶參數open的cursor。
- <p>set serveroutput on
- declare
- cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;
- tableInfo user_constraints%rowtype;
- begin
- open cur('EMPLOYEES');
- loop
- fetch cur into tableInfo;
- exit when cur%notfound;
- dbms_output.put_line(tableInfo.constraint_name);
- end loop;</p><p>exception
- when others then
- dbms_output.put_line(sqlerrm);</p><p> close cur;
- end;
- /</p><p></p>
- set serveroutput on
- declare
- cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;
- begin
- for tableInfo in cur('EMPLOYEES') loop
- dbms_output.put_line(tableInfo.constraint_name);
- end loop;
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- end
- /
可以使用WHERE CURRENT OF子句執行UPDATE或DELETE操作。
- set serveroutput on
- declare
- cursor cur is select * from employees for update;
- begin
- for tableInfo in cur loop
- update employees set salarysalary=salary*1.1 where current of cur;
- end loop;
- commit;
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- end;
- /
3.REF CURSOR（Cursor Variables）
REF Cursor在運行的時候才能確定游標使用的查詢。利用REF CURSOR，可以在程序間傳遞結果集（一個程序里打開游標變量，在另外的程序里處理數據）。
也可以利用REF CURSOR實現BULK SQL，提高SQL性能。
REF CURSOR分兩種，Strong REF CURSOR 和 Weak REF CURSOR。
Strong REF CURSOR:指定retrun type，CURSOR變量的類型必須和return type一致。
Weak REF CURSOR:不指定return type，能和任何類型的CURSOR變量匹配。
Ref cursor的使用:
1) Type [Cursor type name] is ref cursor
2) Open cursor for...
3) Fetch [Cursor name]
4) Close Cursor
例如：
Step1:
- create or replace package TEST as
- type employees_refcursor_type is ref cursor return employees%rowtype;
- procedure employees_loop(employees_cur IN employees_refcursor_type);
- end TEST;
- /
Step2:
- create or replace package body TEST as
- procedure employees_loop(employees_cur IN employees_refcursor_type) is
- emp employees%rowtype;
- begin
- loop
- fetch employees_cur into emp;
- exit when employees_cur%NOTFOUND;
- dbms_output.put_line(emp.employee_id);
- end loop;
- end employees_loop;
- end TEST;
- /
Step3:
- set serveroutput on
- declare
- empRefCur TEST.employees_refcursor_type;
- begin
- for i in 10..20 loop
- dbms_output.put_line('Department ID=' || i);
- open empRefCur for select * from employees where department_id=i;
- TEST.employees_loop(empRefCur);
- end loop;
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- close empRefCur;
- end;
- /
4.BULK SQL
使用FORALL和BULK COLLECT子句。利用BULK SQL可以減少PLSQL Engine和SQL Engine之間的通信開銷，提高性能。
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct. 加速INSERT, UPDATE, DELETE語句的執行，也就是用FORALL語句來替代循環語句。
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO. 加速SELECT，用BULK COLLECT INTO 來替代INTO。
- SQL> create table employees_tmp as select first_name, last_name, salary from employees where 0=1;
- set serveroutput on
- declare
- cursor employees_cur(depId employees.department_id%type) is select first_name, last_name, salary from employees where department_id=depId;
- type employee_table_type is table of employees_cur%rowtype index by pls_integer;
- employee_table employee_table_type;
- begin
- open employees_cur(100);
- fetch employees_cur bulk collect into employee_table;
- close employees_cur;
- for i in 1..employee_table.count loop
- dbms_output.put_line(employee_table(i).first_name || ' ' || employee_table(i).last_name || ',' || employee_table(i).salary);
- end loop;
- forall i in employee_table.first..employee_table.last
- insert into employees_tmp values(employee_table(i).first_name, employee_table(i).last_name, employee_table(i).salary);
- commit;
- end;
- /
5. 動態性能表V$OPEN_CURSOR
本視圖列出session打開的所有cursors。
關于Oracle數據庫游標的類型和使用的知識就介紹到這里了，如果您想了解更多的Oracle數據庫的知識，可以到這里看一下：http://database.51cto.com/oracle/，相信一定能夠帶給您收獲的！
【編輯推薦】
