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由系统自动打开和关闭.
例如：
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更高的性能.