oracle 游标

转自：http://blog.csdn.net/fw0124/article/details/6626370

When Oracle Database executes a SQL statement, it stores the result set and processing information in an unnamedprivate SQL area. A pointer to this unnamed area, called acursor, lets youretrieve the rows of the result set one at a time.Cursor attributes return information about the state of the cursor.游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

Cursor类型包含三种: 静态游标：分为显式（explicit）游标和隐式（implicit）游标；REF游标：是一种引用类型，类似于指针。
1． 隐式游标
1)Select …INTO…语句，DML语句，使用隐式Cursor。此外，还有一种使用FOR LOOP的Implicit Cursor用法。
Every time you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQL opens animplicit cursor. You can get information about this cursor from its attributes, but you cannot control it. After the statement runs, the database closes the cursor; however, its attribute values remain available until another DML orSELECTINTO statement runs. 
2)可以通过隐式Cusor的属性来了解操作的状态和结果。Cursor的属性包含：
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND  布尔型  值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
3) 隐式Cursor由系统自动打开和关闭.
例如：

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3. begin    
4.   update employees set employee_name='Mike' where employee_id=1001;  
5.   if SQL%FOUND then    
6.     dbms_output.put_line('Name is updated');  
7.   else  
8.     dbms_output.put_line('Name is not updated');  
9.   end if;  
10. end;  
11. /  

 

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3. begin    
4.   for tableInfo in (select * from user_tables) loop  
5.     dbms_output.put_line(tableInfo.table_name);  
6.   end loop;  
7. exception  
8.   when others then  
9.     dbms_output.put_line(sqlerrm);  
10. end;  
11. /  

2． 显式游标
PL/SQL also lets you declare explicit cursors. An explicit cursor has a name and is associated with a query (SQLSELECT statement)—usually one that returns multiple rows. After declaring an explicit cursor, you must open it (with theOPEN statement), fetch rows one at a time from the result set (with theFETCH statement), and close the cursor (with theCLOSE statement). After closing the cursor, you can neither fetch records from the result set nor see the cursor attribute values. 
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用法。

[sql] view plaincopy

1. <p>set serveroutput on  
2. declare  
3.   cursor cur is select * from user_tables;  
4.   tableInfo user_tables%rowtype;  
5. begin  
6.   open cur;      
7.   loop  
8.     fetch cur into tableInfo;  
9.     exit when cur%notfound;  
10.     dbms_output.put_line(tableInfo.table_name);  
11.   end loop;</p><p>exception  
12.   when others then  
13.     dbms_output.put_line(sqlerrm);</p><p>  close cur;  
14. end;  
15. /</p>  

 

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3.   cursor cur is select * from user_tables;  
4. begin    
5.   for tableInfo in cur loop  
6.     dbms_output.put_line(tableInfo.table_name);  
7.   end loop;  
8. exception  
9.   when others then  
10.     dbms_output.put_line(sqlerrm);  
11. end;  
12. /  

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

[sql] view plaincopy

1. <p>set serveroutput on  
2. declare  
3.   cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;  
4.   tableInfo user_constraints%rowtype;  
5. begin  
6.   open cur('EMPLOYEES');      
7.   loop  
8.     fetch cur into tableInfo;  
9.     exit when cur%notfound;  
10.     dbms_output.put_line(tableInfo.constraint_name);  
11.   end loop;</p><p>exception  
12.   when others then  
13.     dbms_output.put_line(sqlerrm);</p><p>  close cur;  
14. end;  
15. /</p><p></p>  

 

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3.   cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;  
4. begin  
5.   for tableInfo in cur('EMPLOYEES') loop  
6.     dbms_output.put_line(tableInfo.constraint_name);  
7.   end loop;  
8. exception  
9.   when others then  
10.     dbms_output.put_line(sqlerrm);  
11. end  
12. /  

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

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3.   cursor cur is select * from employees for update;  
4. begin    
5.   for tableInfo in cur loop  
6.     update employees set salary=salary*1.1 where current of cur;  
7.   end loop;  
8.   commit;  
9. exception  
10.   when others then  
11.     dbms_output.put_line(sqlerrm);  
12. end;  
13. /  

3． REF CURSOR（Cursor Variables）
REF Cursor在运行的时候才能确定游标使用的查询。利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。
也可以利用REF CURSOR实现BULK SQL，提高SQL性能。

REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。

A strong REF CURSOR type specifies a return type, which is the RECORD type of its cursor variables. The PL/SQL compiler does not allow you to use these strongly typed cursor variables for queries that return rows that are not of the return type. Strong REF CURSOR types are less error-prone than weak ones, but weak ones are more flexible.Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。

A weak REF CURSOR type does not specify a return type. The PL/SQL compiler accepts weakly typed cursor variables in any queries. Weak REF CURSOR types are interchangeable; therefore, instead of creating weak REF CURSOR types, you can use the predefined type weak cursor type SYS_REFCURSOR.Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配。

After declaring a cursor variable, you must open it for a specific query (with the OPEN FOR statement), fetch rows one at a time from the result set (with the FETCH statement), and then either close the cursor (with the CLOSE statement) or open it for another specific query (with the OPEN FOR statement). Opening the cursor variable for another query closes it for the previous query. After closing a cursor variable for a specific query, you can neither fetch records from the result set of that query nor see the cursor attribute values for that query.

Ref cursor的使用:
1) Type [Cursor type name] is ref cursor 
2) Open cursor for...
3) Fetch  [Cursor name] 
4) Close Cursor
例如：

Step1:

[sql] view plaincopy

1. create or replace package TEST as  
2.   type employees_refcursor_type is ref cursor return employees%rowtype;  
3.   procedure employees_loop(employees_cur IN employees_refcursor_type);  
4. end TEST;  
5. /  

Step2:

[sql] view plaincopy

1. create or replace package body TEST as  
2.   procedure employees_loop(employees_cur IN employees_refcursor_type) is  
3.     emp employees%rowtype;  
4.   begin  
5.     loop  
6.       fetch employees_cur into emp;  
7.       exit when employees_cur%NOTFOUND;  
8.       dbms_output.put_line(emp.employee_id);  
9.     end loop;  
10.   end employees_loop;  
11. end TEST;  
12. /  

Step3:

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3.   empRefCur TEST.employees_refcursor_type;  
4. begin  
5.   for i in 10..20 loop  
6.     dbms_output.put_line('Department ID=' || i);  
7.     open empRefCur for select * from employees where department_id=i;  
8.     TEST.employees_loop(empRefCur);  
9.   end loop;  
10. exception  
11.   when others then  
12.     dbms_output.put_line(sqlerrm);  
13.       
14.   close empRefCur;  
15. end;  
16. /  

 
再例如，ref cursor作为输出参数。

[sql] view plaincopy

1. create or replace procedure get_emps(deptno in int, cur out sys_refcursor) as  
2. begin  
3.   open cur for select * from emp where emp.deptno = deptno;  
4. end;  
5.   
6. declare  
7.   cur sys_refcursor;  
8.   rec emp%rowtype;  
9. begin  
10.   get_emps(10, cur);  
11.   loop  
12.     fetch cur into rec;  
13.     dbms_output.put_line(rec.ename);  
14.     exit when cur%notfound;  
15.   end loop;  
16.   close cur;  
17. end;  

 

4.  BULK SQL
使用FORALL和BULK COLLECT子句。利用BULK SQL可以减少PLSQL Engine和SQL Engine之间的通信开销，提高性能。
The PL/SQL features that comprise bulk SQL are the FORALL statement and theBULKCOLLECT clause. TheFORALL statement sends DML statements from PL/SQL to SQL in batches rather than one at a time. TheBULKCOLLECT clause returns results from SQL to PL/SQL in batches rather than one at a time. If a query or DML statement affects four or more database rows, then bulk SQL can significantly improve performance.
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;

[sql] view plaincopy

1. set serveroutput on  
2. declare  
3.   cursor employees_cur(depId employees.department_id%type) is select first_name, last_name, salary from employees where department_id=depId;  
4.   type employee_table_type is table of employees_cur%rowtype index by pls_integer;  
5.   employee_table employee_table_type;  
6. begin  
7.   open employees_cur(100);  
8.   fetch employees_cur bulk collect into employee_table;  
9.   close employees_cur;  
10.     
11.   for i in 1..employee_table.count loop  
12.     dbms_output.put_line(employee_table(i).first_name || ' ' || employee_table(i).last_name || ',' || employee_table(i).salary);  
13.   end loop;  
14.     
15.   forall i in employee_table.first..employee_table.last  
16.     insert into employees_tmp values(employee_table(i).first_name, employee_table(i).last_name, employee_table(i).salary);  
17.   commit;  
18. end;  
19. /  

5.游标的属性
每个显式的游标都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。
它们都能返回与静态或动态SQL语句执行结果相关的有用信息。

为处理SQL数据操作语句，Oracle会打开一个名为SQL的隐式游标。
它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。

6.  动态性能表V$OPEN_CURSOR
v$open_cursor是oracle提供的一张查看每个session打开过的cursor的视图。
在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor。
11g release 2之后，V$OPEN_CURSOR视图中增加了CURSOR_TYPE列。包括以下值：
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED
可以通过以下语句了解实际打开的Cursor数量。
select sum(a.value), b.namefrom v$sesstat a, v$statname bwhere a.statistic# = b.statistic#and b.name = 'opened cursors current'group by b.name;

Oracle提供2个参数。
1)open_cursors, 定义每个Session最大能够打开的游标数量。可以通过select * from v$parameter where name = 'open_cursors'或者show parameter open_cursors查询。
2)session_cached_cursors 这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru(Least Recently Used)链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.