Oracle学习笔记之显式游标和隐式游标
游标的概念:
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。我们常用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
隐式游标
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT ... INTO ...。
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示:
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．声明游标
在DECLEAR部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]
IS SELECT语句;
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名[(实际参数1[，实际参数2...])];
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[，变量名2...];
或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明：
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
定义记录变量的方法如下：
变量名 表名|游标名%ROWTYPE；
其中的表必须存在，游标名也必须先定义。
4．关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。
现在通过一个例子来学习一下显示游标的使用方法：
有一个表原来结构是如下的
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 v_year varchar2(20); v_month number; p_rate exchangerate%rowtype;
cursor c_rate is select * from exchangerate t where t.currentmonth is null;
begin open c_rate; loop
fetch c_rate into p_rate; 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;
exit when c_rate%notfound;
end loop;
close c_rate;
end; /
我们把上面的例子有游标的for循环来改写一下。
显式游标的for循环
declare v_year varchar2(20); v_month number; 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 v_year varchar2(20); v_month number; begin for p_rate in (select * from exchangerate t where t.currentmonth is null) 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; /
显示游标中游标参数的传递
例子：就以上面的表来说 加入我们在定义游标时不确定查询条件中的值，这时我们可以通过游标参数来解决
declare v_year varchar2(20); v_month number; p_rate exchangerate%rowtype; cursor c_rate(p_quarter varchar2) --声明游标带参数
is
select * from exchangerate t where t.quarter<=p_quarter; begin open c_rate(p_quarter=>'2011Q3');--打开游标,传递参数值 loop 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;
游标变量
游标是数据库中一个命名的工作区,当游标被声明后,他就与一个固定的SQL想关联,在编译时刻是已知的,是静态的.它永远指向一个相同的查询工作区.
游标变量是动态的可以在运行时刻与不同的SQL语句关联,在运行时可以取不同的SQL语句.它可以引用不同的工作区.
如何定义游标类型
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 v_year varchar2(20); v_month number; p_rate exchangerate%rowtype; type rate is ref cursor;--定义游标变量 c_rate rate; --声明游标变量 begin open c_rate for select * from exchangerate t where t.quarter='2011Q3';--打开游标变量 loop 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语句 open c_rate for select * from exchangerate t where t.quarter='2011Q2';--打开游标变量 loop 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;
游标表达式
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; /
