游标是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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 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用法。
<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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
还可以使用带参数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
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
可以使用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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 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:
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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 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。
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;
/
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
5. 动态性能表V$OPEN_CURSOR
本视图列出session打开的所有cursors。
关于Oracle数据库游标的类型和使用的知识就介绍到这里了，如果您想了解更多的Oracle数据库的知识，可以到这里看一下：http://database.51cto.com/oracle/，相信一定能够带给您收获的！
【编辑推荐】
