Oracle显式游标和隐式游标

游标的概念: 

隐式游标 

sql%found （布尔类型，默认值为null）

sql%notfound（布尔类型,默认值为null）

sql%rowcount(数值类型默认值为0)

sql%isopen(布尔类型)

当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

SQL%FOUND和SQL%NOTFOUND

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：

     . TRUE :INSERT

　　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.

　　. TRUE :SELECT INTO至少返回一行

　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

 SQL%ROWCOUNT

　　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、delete为0条），SQL%ROWCOUNT的值为0，而对于update和delete来说表示游标所检索数据库行的个数即更新或者删除的行数。

SQL%ISOPEN

　　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

 最后我们来说一下隐式游标中SELECT..INTO 语句，当执行的时候会有三种可能：

（1).结果集只含有一行，且select是成功的

 (2).没有查询到任何结果集，引发NO_DATA_FOUND异常

 (3).结果集中含有两行或者更多行，引发TOO_MANY_ROWS异常。

例子:

BEGIN UPDATE exchangerate SET rate=7 where quarter='2011Q1';
 DBMS_output.put_line('游标所影响的行数：'||SQL%rowcount); if SQL%NotFound then 
 DBMS_output.put_line('NotFound为真'); DBMS_output.put_line('NofFound为假'); end if; if SQL%Found then DBMS_output.put_line('Found为真'); else DBMS_output.put_line('Found为假'); end if; if SQL%isopen then DBMS_output.put_line('isOpen为真'); else DBMS_output.put_line('isOpen为假'); end if; 

  

END;

显式游标:

游标的定义和操作 
游标的使用分成以下4个步骤。 

1．声明游标 

现在通过一个例子来学习一下显示游标的使用方法：

有一个表原来结构是如下的

create table EXCHANGERATE ( QUARTER VARCHAR2(20), RATE NUMBER(10,4), DESCRIPTION VARCHAR2(900), ID VARCHAR2(10) not null, CURRENCY VARCHAR2(100) )

这是一个汇率表里面维护着的是季度 币种和汇率的关系，现在有一个新的需求是在原来表的基础上增加一列名字为currentmonth，变为季度、季度中月份、 币种和汇率的关系，

并且使原来每个季度对应的币种和汇率变成每个季度 对应该季度月份 币种和汇率，每个月的默认值为原来季度对应的值。

例如 原来 2013Q2 CNY 6.2

现在我们要变为2013Q2 2013-04 CNY 6.2  2013Q2 2013-05 CNY 6.2

2013Q2 2013-06 CNY 6.2  三条记录。

通过分析以上需求，我们首先要增加一列：

alter table exchangerate add currentmonth varchar2(20);

然后我们通过在匿名块中通过显示游标来实现以上需求:

declare 

insert into exchangerate(id,quarter,currentmonth,rate,currency,Description)
 values(SEQUENCE_EXCHANGERATE.nextval,p_rate.quarter,
 to_char(to_date(v_year||(v_month+i),'yyyyMM'),'yyyy-MM'),p_rate.rate,p_rate.currency,p_rate.description); end loop; 
 

exit when c_rate%notfound;
 

 我们把上面的例子有游标的for循环来改写一下。

显式游标的for循环

declare 

cursor c_rate is select * from exchangerate t where t.currentmonth is null;

begin

for p_rate in c_rate loop

v_year:=substr(p_rate.quarter, 0, 4);

v_month:=(to_number(substr(p_rate.quarter,6,1)) – 1) * 3;

for i in 1 .. 3 loop insert into exchangerate(id,quarter,currentmonth,rate,currency,Description)
 values(SEQUENCE_EXCHANGERATE.nextval,p_rate.quarter,
 to_char(to_date(v_year||(v_month+i),'yyyyMM'),'yyyy-MM'),p_rate.rate,p_rate.currency,p_rate.description); end loop; 

end loop;

end;

/

我们可以看到游标FOR循环确实很好的简化了游标的开发，我们不在需要open、fetch和close语句，不在需要用%FOUND属性检测是否到最后一条记录，这一切Oracle隐式的帮我们完成了。

隐式游标的for循环

declare 

begin

for p_rate in (select * from exchangerate t where t.currentmonth is null) loop

 insert into exchangerate(id,quarter,currentmonth,rate,currency,Description) 
 values(SEQUENCE_EXCHANGERATE.nextval,p_rate.quarter,
 to_char(to_date(v_year||(v_month+i),'yyyyMM'),'yyyy-MM'),p_rate.rate,p_rate.currency,p_rate.description); end loop; 

end loop;

显示游标中游标参数的传递

例子：就以上面的表来说 加入我们在定义游标时不确定查询条件中的值，这时我们可以通过游标参数来解决

declare 

fetch c_rate into p_rate; update exchangerate set rate=p_rate.rate+1 where id=p_rate.id; exit when c_rate%notfound; 

end loop;

close c_rate;

end;

游标变量

如何定义游标类型

TYPE ref_type_name IS REF CURSOR

[RETURN return_type];

声明游标变量

cursor_name ref_type_name;

ref_type_name 是后面声明游标变量时要用到的我们的游标类型(自定义游标类型,即CURSOR是系统默认的,ref_type_name是我们定义的 );

return_type代表数据库表中的一行,或一个记录类型

TYPE ref_type_name IS REF CURSOR RETURN EMP%TYPE

RETURN 是可选的,如果有是强类型,可以减少错误,如果没有return是弱引用,有较好的灵活性.

游标变量的操作

 例子：

declare 

begin

fetch c_rate into p_rate;--提取游标变量 update exchangerate set rate=p_rate.rate+1 where id=p_rate.id; exit when c_rate%notfound; 

end loop;

–将同一个游标变量对应到另一个SELECT语句

fetch c_rate into p_rate;--提取游标变量 update exchangerate set rate=p_rate.rate-1 where id=p_rate.id; exit when c_rate%notfound; 

end;

游标表达式

Oracle在SQL语言中提供了一个强有力的工具：游标表达式。一个游标表达式从一个查询中返回一个内嵌的游标。在这个内嵌游标的结果集中，每一行数据包含了在SQL查询中的可允许的数值范围；它也能包含被其他子查询所产生的游标。

因此，你能够使用游标表达式来返回一个大的和复杂的，从一张或多张表获取的数据集合。游标表达式的复杂程度，取决于查询和结果集。然而，了解所有从Oracle RDBMS提取数据的可能途径，还有大有好处的。

你能够在以下任何一种情况使用游标表达式：

   （1）、 显式游标声明

   （2）、动态SQL查询。

   （3）、REF CURSOR 声明和变量。

你不能在一个隐式查询中使用游标表达式。

游标表达式的语法是相当简单的：

    CURSOR (查询语句)

当Oracle从父游标或外围游标那里检取包含游标表达式的数据行时，Oracle就会隐式地打开一个内嵌的游标，这个游标就是被上述的游标表达式所定义。在以下情况发生时，这个内迁游标将会被关闭：

    （1）、你显式地关闭这个游标。

    （2）、外围或父游标被重新执行，关闭或撤销。

    （3）、当从父游标检取数据时，发生异常。内嵌游标就会与父游标一起被关闭。

 使用游标表达式

你可以通过两种不同的，但是非常有用的方法来使用游标表达式：

   1.  在一个外围查询中把字查询作为一列来检取数据。

   2.  把一个查询转换成一个结果集，而这个结果集就可以被当成一个参数传递给一个流型或变换函数。

例子：

CREATE OR REPLACE PROCEDURE emp_report(p_locid NUMBER) IS TYPE refcursor IS REF CURSOR; CURSOR all_in_one IS SELECT l.city, CURSOR( SELECT d.department_name, CURSOR ( SELECT e.last_name FROM employees e WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID ) as ename FROM departments d WHERE d.LOCATION_ID = l.LOCATION_ID ) as dname FROM locations l WHERE l.location_id = p_locid; departments_cur refcursor; employees_cur refcursor; v_city locations.city%type; v_dname departments.department_name%type; v_ename employees.last_name%type; i integer :=1; j integer :=1; k integer :=1; BEGIN OPEN all_in_one; LOOP FETCH all_in_one INTO v_city, departments_cur; EXIT WHEN all_in_one%NOTFOUND; LOOP FETCH departments_cur INTO v_dname, employees_cur; EXIT WHEN departments_cur%NOTFOUND; LOOP FETCH employees_cur INTO v_ename; EXIT WHEN employees_cur%NOTFOUND; dbms_output.put_line(i || ' , ' || j || ' , ' || k || '----' || v_city || ' ,' || v_dname || ' ,' || v_ename ); k := k + 1; END LOOP; j := j + 1; END LOOP; i := i + 1; END LOOP; END; /