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