Oracle显式游标与隐式游标

ref cursor 定义方式参见http://blog.csdn.net/deepwishly/article/details/23959141

显式游标和隐式游标都可实现对记录的循环操作。显式游标需要用户遵循声明、打开、捕获记录、操作记录和关闭等步骤。隐式游标无须如此繁的步骤，而更接近于其他编程语言中的for循环。

虽然显式游标操作复杂，但使用上却可以更加灵活，例如，打开游标时传递参数，可以创建更加灵活多样的游标。相对而言，隐式游标步骤简练，但用户不能进行干预，因此适合于简单的游标操作。

一、显式游标：

可以方便地控制游标的生命周期。

1.声明游标：

语法：delcare cursor 游标名称 is 查询语句；

2.声明列变量：

语法：变量名 原列名%type; 

3.声明行变量：

语法：变量名 表名%rowtype; 

如：

declare cursor cu_employee is

select * from employees;

employee employees%rowtype;

注：选择的必须为所有列*。

4.使用显式游标：

打开游标：

open 游标名称

捕获数据：

fetch 游标名称 into 变量

5.使用普通变量获取游标信息：

SQL> set serverout on;

SQL> declare cursor cu_employee is

select employee_id,employee_name,employee_age

from employees;

employee_id number;

employee_name varchar2(20);

employee_age number;

begin

open cu_employee;

fetch cu_employee into employee_id,employee_name,employee_age;

while cu_employee%found loop

dbms_output.put_line(employee_id || ':' ||employee_name || ':' || employee_age);

fetch cu_employee into employee_id,employee_name,employee_age;

end loop;

end;

/

1:王晓:37

2:钟小平:32

3:刘俊:27

4:王龙:26

5:钟文:26

6:张三:25

7:李四:24

8:王五:25

19:Jamaly:

20:吖ling:

PL/SQL procedure successfully completed

6.使用行类型变量来获取游标信息：

SQL> declare cursor cu_employee is

select *   --必须为*

from employees;

employee employees%rowtype;

begin

open cu_employee;

fetch cu_employee into employee;

while cu_employee%found loop

dbms_output.put_line(employee.employee_id || ':' || employee.employee_name || ':' || employee.employee_age);

fetch cu_employee into employee;

end loop;

close cu_employee;

end;

/

1:王晓:37

2:钟小平:32

3:刘俊:27

4:王龙:26

5:钟文:26

6:张三:25

7:李四:24

8:王五:25

19:Jamaly:

20:吖ling:

PL/SQL procedure successfully completed

7.为游标传递参数：

SQL> declare cursor cu_employee(minAge in number,maxAge in number) is

select *

from employees

where employee_age>=minAge and employee_age<=maxAge;

employee employees%rowtype;

begin

open cu_employee(21,30);

fetch cu_employee into employee;

while cu_employee%found loop

dbms_output.put_line(employee.employee_id || ':' || employee.employee_name || ':' || employee.employee_age);

fetch cu_employee into employee;

end loop;

close cu_employee;

end;

/

3:刘俊:27

4:王龙:26

5:钟文:26

6:张三:25

7:李四:24

8:王五:25

PL/SQL procedure successfully completed

8.游标属性：

8.1 found： fetch捕获记录成功。

8.2 not found: fetch捕获不成功。

8.3 rowcount： 返回当前时刻已捕获的记录数。

8.4 isopen： 游标是否打开。

二、隐式游标

分为sql隐式游标和 cursor for 游标。

1.查看sql 隐式游标的默认值：

SQL> begin

if sql%isopen then

dbms_output.put_line('sql is open');

else

dbms_output.put_line('sql is not open');

end if;

dbms_output.put_line('rowcount is' || sql%rowcount);

end;

/

sql is not open

rowcount is

2.查询语句对sql隐式游标的影响：

SQL> declare employee_age number;

begin

select employee_age into employee_age from employees where employee_id=1;

if sql%isopen then

dbms_output.put_line('sql 游标已打开');

else

dbms_output.put_line('sql 游标未打开');

end if;

dbms_output.put_line('游标捕获记录数： ' || sql%rowcount);

end;

/

sql 游标未打开

游标捕获记录数： 1

3.更新语句对sql隐式游标的影响：

3.1

 SQL> begin

update employees set employee_age=employee_age+1;

if sql%isopen then

dbms_output.put_line('sql 游标已打开');

else

dbms_output.put_line('sql 游标未打开');

end if;

dbms_output.put_line('游标捕获记录数： ' || sql%rowcount);

end;

/

sql 游标未打开

游标捕获记录数： 10

3.2 sql 游标属性信息保留为最后一条语句的更新信息。

SQL> begin

update employees set employee_age=employee_age+1;

insert into employees(employee_id,employee_name,employee_age)

values(21,'Asha',23);

if sql%isopen then

dbms_output.put_line('sql 游标已打开');

else

dbms_output.put_line('sql 游标未打开');

end if;

dbms_output.put_line('游标捕获记录数： ' || sql%rowcount);

end;

/

sql 游标未打开

游标捕获记录数： 1

4.sql 隐式游标的使用：

插入一条记录，如果存在对记录各列更新，否则，插入该记录：

SQL> begin

update employees set employee_name='MLing' ,employee_age=22

where employee_id=9;

if sql%rowcount=0 then

insert into employees(employee_id,employee_name,employee_age)

values(9,'MLing',22);

end if;

end;

/

5.cursor for 游标的使用：

语法：

for 游标变量 in (查询) loop

DML操作

end loop;

SQL> begin

for employee in (select * from employees where employee_id<=6) loop

dbms_output.put_line(employee.employee_id || ':' || employee.employee_name

|| ':' || employee.employee_age);

end loop;

end;

/

1:王晓:39

2:钟小平:34

3:刘俊:29

4:王龙:28

5:钟文:28

6:张三:27