Oracle Cursor的使用

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 you retrieve 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 an implicit 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由系统自己主动打开和关闭.
比如：

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．显式游标
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使用方法。

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;
exception
  when others then
    dbms_output.put_line(sqlerrm);
  close cur;
end;
/

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。

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;
exception
  when others then
    dbms_output.put_line(sqlerrm);
  close cur;
end;
/

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 salary=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。

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:

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;
/

再比如，ref cursor作为输出參数。

create or replace procedure get_emps(deptno in int, cur out sys_refcursor) as
begin
  open cur for select * from emp where emp.deptno = deptno;
end;

declare
  cur sys_refcursor;
  rec emp%rowtype;
begin
  get_emps(10, cur);
  loop
    fetch cur into rec;
    dbms_output.put_line(rec.ename);
    exit when cur%notfound;
  end loop;
  close cur;
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;

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.游标的属性
每一个显式的游标都有四个属性：%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更高的性能.