Oracle数据库游标的类型及使用实例全解

本文我们主要介绍了Oracle数据库的五种类型的游标的使用，这五种游标分别是：隐式游标、显式游标、REF CURSOR、BULK SQL和动态性能表V$OPEN_CURSOR，希望能够对您有所帮助。

游标是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由系统自动打开和关闭.

例如：

1. setserveroutputon
3. declare
5. begin
7. updateemployeessetemployee_name='Mike'whereemployee_id=1001;
9. ifSQL%FOUNDthen
11. dbms_output.put_line('Nameisupdated');
13. else
15. dbms_output.put_line('Nameisnotupdated');
17. endif;
19. end;
21. /
23. setserveroutputon
25. declare
27. begin
29. fortableInfoin(select*fromuser_tables)loop
31. dbms_output.put_line(tableInfo.table_name);
33. endloop;
35. exception
37. whenothersthen
39. dbms_output.put_line(sqlerrm);
41. end;
43. /

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用法。

1. <p>setserveroutputon
3. declare
5. cursorcurisselect*fromuser_tables;
7. tableInfouser_tables%rowtype;
9. begin
11. opencur;
13. loop
15. fetchcurintotableInfo;
17. exitwhencur%notfound;
19. dbms_output.put_line(tableInfo.table_name);
21. endloop;</p><p>exception
23. whenothersthen
25. dbms_output.put_line(sqlerrm);</p><p>closecur;
27. end;
29. /</p>
31. setserveroutputon
33. declare
35. cursorcurisselect*fromuser_tables;
37. begin
39. fortableInfoincurloop
41. dbms_output.put_line(tableInfo.table_name);
43. endloop;
45. exception
47. whenothersthen
49. dbms_output.put_line(sqlerrm);
51. end;
53. /

还可以使用带参数open的cursor。

1. <p>setserveroutputon
3. declare
5. cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;
7. tableInfouser_constraints%rowtype;
9. begin
11. opencur('EMPLOYEES');
13. loop
15. fetchcurintotableInfo;
17. exitwhencur%notfound;
19. dbms_output.put_line(tableInfo.constraint_name);
21. endloop;</p><p>exception
23. whenothersthen
25. dbms_output.put_line(sqlerrm);</p><p>closecur;
27. end;
29. /</p><p></p>
31. setserveroutputon
33. declare
35. cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;
37. begin
39. fortableInfoincur('EMPLOYEES')loop
41. dbms_output.put_line(tableInfo.constraint_name);
43. endloop;
45. exception
47. whenothersthen
49. dbms_output.put_line(sqlerrm);
51. end
53. /

可以使用WHERE CURRENT OF子句执行UPDATE或DELETE操作。

1. setserveroutputon
3. declare
5. cursorcurisselect*fromemployeesforupdate;
7. begin
9. fortableInfoincurloop
11. updateemployeessetsalarysalary=salary*1.1wherecurrentofcur;
13. endloop;
15. commit;
17. exception
19. whenothersthen
21. dbms_output.put_line(sqlerrm);
23. end;
25. /

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:

1. createorreplacepackageTESTas
3. typeemployees_refcursor_typeisrefcursorreturnemployees%rowtype;
5. procedureemployees_loop(employees_curINemployees_refcursor_type);
7. endTEST;
9. /

Step2:

1. createorreplacepackagebodyTESTas
3. procedureemployees_loop(employees_curINemployees_refcursor_type)is
5. empemployees%rowtype;
7. begin
9. loop
11. fetchemployees_curintoemp;
13. exitwhenemployees_cur%NOTFOUND;
15. dbms_output.put_line(emp.employee_id);
17. endloop;
19. endemployees_loop;
21. endTEST;
23. /

Step3:

1. setserveroutputon
3. declare
5. empRefCurTEST.employees_refcursor_type;
7. begin
9. foriin10..20loop
11. dbms_output.put_line('DepartmentID='||i);
13. openempRefCurforselect*fromemployeeswheredepartment_id=i;
15. TEST.employees_loop(empRefCur);
17. endloop;
19. exception
21. whenothersthen
23. dbms_output.put_line(sqlerrm);
26. closeempRefCur;
28. end;
30. /

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。

1. SQL>createtableemployees_tmpasselectfirst_name,last_name,salaryfromemployeeswhere0=1;
3. setserveroutputon
5. declare
7. cursoremployees_cur(depIdemployees.department_id%type)isselectfirst_name,last_name,salaryfromemployeeswheredepartment_id=depId;
9. typeemployee_table_typeistableofemployees_cur%rowtypeindexbypls_integer;
11. employee_tableemployee_table_type;
13. begin
15. openemployees_cur(100);
17. fetchemployees_curbulkcollectintoemployee_table;
19. closeemployees_cur;
21. foriin1..employee_table.countloop
23. dbms_output.put_line(employee_table(i).first_name||''||employee_table(i).last_name||','||employee_table(i).salary);
25. endloop;
27. foralliinemployee_table.first..employee_table.last
29. insertintoemployees_tmpvalues(employee_table(i).first_name,employee_table(i).last_name,employee_table(i).salary);
31. commit;
33. end;
35. /