第六章 PL/SQL与Oracle间交互

最新推荐文章于 2022-07-28 17:48:45 发布

iteye_2060

最新推荐文章于 2022-07-28 17:48:45 发布

阅读量118

点赞数

文章标签：数据库数据结构与算法开发工具

<div id="chapter">第六章 PL/SQL与Oracle间交互</div>

<div id="text">
一、PL/SQL支持的SQL一览
PL/SQL扩展了SQL，变得更加强大和易用。我们可以用PL/SQL灵活安全地操作Oracle数据，因为它支持所有的SQL数据操作语句(除
了EXPLAIN
PLAN)，事务控制语句，函数，伪列和操作符。PL/SQL还支持动态SQL，能让我们动态地执行SQL数据定义，数据控制和会话控制语句。除此之
外，PL/SQL还遵循ANSI/ISO的SQL标准。 
1、数据操作
我们可以使用INSERT、UPDATE、DELETE、SELECT和LOCK
TABLE来操作Oracle数据。INSERT用于向数据表中插入新行；UPDATE能修改行；DELETE可以删除不需要的行；SELECT可以按照
我们给定的查询标准检索出满足条件的行；LOCK TABLE可以临时限制对一个数据表的访问。 
2、事务控制
Oracle是面向事务的，它使用事务来保证数据的完整性。数据库事务是指作为单个逻辑工作单元执行的一系列SQL操作。
同时，Oracle还可以通过事务将我们的操作持久化或取消。如果我们的程序在事务中有一步执行失败，Oracle就会发现错误并回滚整个事务。这样，数据库就会自动恢复到先前的状态。 
我们可以使用COMMIT、ROLLBACK、SAVEPOINT和SET
TRANSACTION命令来控制事务。COMMIT会提交事务，将数据库的变化永久性地提交；ROLLBACK会终止当前事务，并放弃自事务开启后所有
发生变化的内容；SAVEPOINT能够标记当前事务的处理进度；一并使用ROLLBACK和SAVEPOINT就可以回滚部分事务。SET
TRANSACTION可以设置事务的属性，如读写访问和隔离级别。 
3、SQL函数
PL/SQL能让我们使用所有的SQL聚合函数：AVG、COUNT、GROUPING、MAX、MIN、STDDEV、SUM和VARIANCE。除了COUNT(*)之外，所有的聚合函数都会忽略空值。
我们可以在SQL语句中使用聚合函数，但不能在过程化语句中使用。聚合函数会作用于整个字段，除非我们使用SELECT GROUP
BY语句对返回的结果行进行分组排序。如果我们忽略GROUP
BY子句，聚合函数就会把所有的结果行当作一个整体来处理。我们可以使用下面的语法来调用聚合函数：
<blockquote>
<table border="0"><tbody><tr>
<td>function_name([ALL
|DISTINCT
]expression) </td>
</tr></tbody></table>
</blockquote>
expression会引用一个或多个数据库字段。如果我们指定ALL(默认的)，聚合函数就会考虑所有的行，也包括重复的行内容。如果我们指定的是DISTINCT，聚合函数只会考虑有区别的值。例如，下面的语句只返回数据表emp中不同的职别的个数：
<blockquote>
<table border="0"><tbody><tr>
<td>
SELECT
COUNT(DISTINCT
job)INTO
job_countFROM
emp; </td>
</tr></tbody></table>
</blockquote>
函数COUNT可以让我们使用星号(*)操作符返回一个数据表中行的个数。例如，下面的语句返回表emp中行的个数：
<blockquote>
<table border="0"><tbody><tr>
<td>
SELECT
COUNT(*)INTO
emp_countFROM
emp; </td>
</tr></tbody></table>
</blockquote>
如果使用默认的ALL，聚合函数会考虑所有的列值，包括重复项；如果改换DISTINCT的话，聚合函数就只会考虑重复项中的一个值。 
4、SQL伪列
PL/SQL可以辨识CURRVAL、LEVEL、NEXTVAL、ROWID和ROWNUM这样能返回特殊的数据项的SQL伪列。伪列并不是真实
存在于数据表中的字段，但它们的行为看起来跟真实字段一样。例如，我们在查询时就可以选取伪列。但是，我们不允许在SQL语句中向伪列中插入值。伪列只能
在SQL语句中使用，不能用在过程化语句中。 
<ul>
<li>CURRVAL and NEXTVAL </li>
</ul>
序列(sequence)是一个能产生顺序编号的模式对象。创建序列时，我们可以指定它的初始值和增量值。CURRVAL能返回指定序列的当前值。
在使用序列前，我们必须先调用NEXTVAL，NEXTVAL的作用是生成并返回序列的下一个顺序值。想要得到序列中的当前值或是下一个值，我们必须使用点标志，方法如下：
<blockquote>
<table border="0"><tbody><tr>
<td>sequence_name.CURRVAL
 
sequence_name.NEXTVAL
</td>
</tr></tbody></table>
</blockquote>
创建序列后，我们就可以在事务处理中用它产生唯一的顺序编号了。但是，我们只能在用SELECT列表、VALUES子句和SET子句中使用CURRVAL和NEXTVAL。在下面的例子中，我们使用序列向两个数据表中插入相同的雇员编号：
<blockquote>
<table border="0"><tbody><tr>
<td>
INSERT
INTO
empVALUES
(empno_seq.NEXTVAL
,my_ename,...); INSERT
INTO
salsVALUES
(empno_seq.CURRVAL
,my_sal,...); </td>
</tr></tbody></table>
</blockquote>
在事务中调用NEXTVAL时，序列的值会立即发生改变，不管我们是否使用提交或回滚语句。 
<ul>
<li>LEVEL </li>
</ul>
我们可以用LEVEL配合SELECT CONNECT BY语句把数据表中的行组织成一个树形结构。LEVEL能返回树形结构中节点的级数。根节点的级数是1，根节点的子节点级数是2，孙子节点级数是3，依次类推。
在START WITH子句中，我们指定辨识根节点的条件。我们可以使用PRIOR操作符指定树的遍历方向(从根向下或从枝干向上)。 
<ul>
<li>ROWID </li>
</ul>
ROWID能够返回数据表中行的ROWID(二进制地址)。我们可以使用UROWID类型变量来存放具有可读性的ROWID，下例中，我们声明了一个名为row_id的变量：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
row_idUROWID; </td>
</tr></tbody></table>
</blockquote>
当我们把物理rowid放到UROWID变量中时，就要使用函数ROWIDTOCHAR，它能把二进制值转成一个长度为18字节的字符串。然后我们
就可以在UPDATE或DELETE语句的WHERE子句中，把UROWID变量的值同ROWID伪列值进行比较并找出从游标中取得的最新行。 
<ul>
<li>ROWNUM </li>
</ul>
ROWNUM能够返回从数据表中选出的行的标识。被选取的第一行的ROWNUM是1，第二行是2，依次类推。如果SELECT语句包含一个ORDER BY子句，ROWNUM会在排序操作之前被指定。
我们可以在UPDATE语句中用ROWNUM为每一行数据赋上一个唯一的值。同样，也可以在SELECT语句的WHERE子句中用ROWNUM限定选取的行的个数，如下例：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
empno,sal FROM
emp WHERE
sal>2000AND
ROWNUM
<=10;--returns10rows
</td>
</tr></tbody></table>
</blockquote>
ROWNUM的值只是在行被检索出来的时候才会增长，所以，只有在WHERE子句中的ROWNUM才有意义：
<blockquote>
<table border="0"><tbody><tr>
<td>...WHERE
ROWNUM
<constant; 
...WHERE
ROWNUM
<=constant; </td>
</tr></tbody></table>
</blockquote>
5、SQL操作符
PL/SQL能让我们在SQL语句中使用所有的SQL比较操作符、集合操作符和行操作符。这里简要描述一下其中几个操作符的功能。 
<ul>
<li>比较操作符 </li>
</ul>
我们常常需要在一个数据操作语句的WHERE子句中使用比较操作符来判断一个表达式的值是TRUE、FALSE还是NULL。下面的表格描述了各个操作符的用途： 
<table id="table-list" border="0"><tbody>
<tr>
<th id="table-list-head">操作符</th>
<th id="table-list-head">描述</th>

</tr>
<tr>
<td valign="top">ALL</td>
<td>把同一个列表中的一个值与其他值或是子查询返回的值进行比较，如果所有的比较结果都为TRUE，那么结果值就为TRUE。</td>
</tr>
<tr>
<td valign="top">ANY, SOME</td>
<td>把同一个列表中的一个值与其它值或是子查询返回的值进行比较，只要其中有一个结果值为TRUE，那么结果值就为TRUE。</td>
</tr>
<tr>
<td>BETWEEN</td>
<td>测试一个值是否在指定的区间范围内。</td>
</tr>
<tr>
<td>EXISTS</td>
<td>如果子查询至少返回一行数据，那么EXISTS的结果就为TRUE。</td>
</tr>
<tr>
<td>IN</td>
<td>测试指定的值是否是集合成员。</td>
</tr>
<tr>
<td>IS</td>
<td>测试指定的值是否是NULL。</td>
</tr>
<tr>
<td>LIKE</td>
<td>测试一个字符串是否满足一个指定的样式，其中包含通配符。</td>
</tr>
</tbody></table>
<ul>
<li>集合操作符 </li>
</ul>
集合操作符的作用是把两个查询的结果合并成一个结果。INTERSECT返回两个结果的交集；MINUS把目标结果集与另外一个结果集比较，并把存
在于另一个结果集中的记录从目标结果集中去除，然后返回目标结果集；UNION把两个结果集的内容合并，在有重复项的情况下，只保留其中一项；UNION
ALL同UNION的作用类似，但它会保留所有记录，不管是否有相同的记录。 
<ul>
<li>行操作符 </li>
</ul>
行操作符能返回或引用特定的行。ALL会保留查询或聚合表达式中的重复行；DISTINCT的作用与ALL相反，用于消除重复行；PRIOR能引用树形查询中返回的当前行的父级行。 
二、管理游标
PL/SQL有两种类型的游标：隐式游标和显式游标。PL/SQL为所有的SQL数据操作语句都声明了一个隐式的游标，其中也包括只返回一条记录的
查询。但是，对于返回多于一条记录的查询来说，我们必须声明显式的游标，然后使用游标FOR循环或使用BULK COLLECT子句。 
1、显式游标一览
查询返回的行集合可能是由零行、一行或多行组成，这个结果取决于我们的检索条件。当一个查询返回多行记录时，我们可以显式地声明一个游标来处理每一行数据。游标的声明可以放到PL/SQL块、子程序或包的声明部分。
我们可以使用三种命令来控制游标：OPEN、FETCH和CLOSE。首先用OPEN语句初始化一个游标，然后重复执行FETCH语句取出已检索到
的数据，或是使用BULK
COLLECT批量选取数据。当处理完结果集中最后一行数据时，就可以用CLOSE语句关闭游标。我们还可以同时打开多个游标并发处理多个查询操作。 
<ul>
<li>声明游标 </li>
</ul>
在PL/SQL中，向前引用是不允许的。所以我们必须在其它语句引用游标之前声明它。声明游标时，我们需要为它命名，并用下面的语法把它和一个查询相关联： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CURSOR
cursor_name[(parameter[,parameter]...)] 
[RETURN
return_type]IS
select_statement; </td>
</tr></tbody></table>
</blockquote>
return_type必须是记录或是数据表的行类型，parameter的含义如下：
<blockquote>
<table border="0"><tbody><tr>
<td>cursor_parameter_name[IN
]datatype[{:=|DEFAULT
}expression] </td>
</tr></tbody></table>
</blockquote>
例如，我们可以像下面这样声明游标c1和c2：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
empno,ename,job,sal FROM
emp WHERE
sal>2000; CURSOR
c2RETURN
dept%ROWTYPE
IS
 SELECT
* FROM
dept WHERE
deptno=10; </td>
</tr></tbody></table>
</blockquote>
游标名是一个未声明的标识符，而不是PL/SQL变量名。我们不能把值赋给游标名或是在一个表达式中使用它。但是，游标和变量有着同样的作用域规则。虽然在数据表为游标命名是允许的，但并不推荐这样做。 
游标是可以接收参数的，这些参数可以在关联查询中允许出现常量的地方使用。游标的形式参数都必须是IN模式的。因此，它们不能把值赋给实际参数。并且，我们不能给游标的参数添加NOT NULL约束。
如下例所示，我们为游标参数初始化一个默认值。这样，我们就能给游标传递不同个数的实参。并且，我们还可以在不改变游标引用的情况下添加新的参数。
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1(lowINTEGER
DEFAULT
0,highINTEGER
DEFAULT
99)IS
 SELECT
* FROM
... </td>
</tr></tbody></table>
</blockquote>
游标参数作用域对于游标来说是本地的，这也就意味着它们只能在游标声明时所指定的查询语句中使用。游标参数的值只在游标被打开的时候被使用。 
<ul>
<li>打开游标 </li>
</ul>
游标只能在打开之后才能执行查询操作。对于使用了FOR UPDATE子句的游标来说，OPEN语句会把满足查询条件的行琐住，如下例所示： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename,job FROM
emp WHERE
sal<3000; 
... BEGIN
 OPEN
c1; 
... END
; </td>
</tr></tbody></table>
</blockquote>
OPEN语句执行时结果集中的行并不被选取，只有在FETCH语句执行的时候数据才被取得。 
<ul>
<li>传递游标参数 </li>
</ul>
我们可以用OPEN语句来为游标传递参数。如果在没有使用参数默认值的情况下，我们就必须在OPEN语句中为游标声明中的每一个形式参数指定一个对应的实际参数。比如下面的游标声明：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
emp_nameemp.ename%TYPE
; 
salaryemp.sal%TYPE
; CURSOR
c1(NAMEVARCHAR2
,salaryNUMBER
)IS
 SELECT
* FROM
... </td>
</tr></tbody></table>
</blockquote>
下面三个语句都能打开游标：
<blockquote>
<table border="0"><tbody><tr>
<td>
OPEN
c1(emp_name,3000); OPEN
c1('ATTLEY'
,1500); OPEN
c1(emp_name,salary); </td>
</tr></tbody></table>
</blockquote>
在上面的例子中，标识符salary用在游标声明的时候，它引用的是形式参数。但是，当用在OPEN语句中时，它引用的就是PL/SQL变量，为了避免混乱，最好使用唯一的标识。
有默认值的形参不需要有对应的实参，在OPEN语句执行时程序会自动使用它们的默认值。
我们可以用位置标识法或名称标识法把OPEN语句中的实参和游标声明中的形参关联起来。每个实参的数据类型和它对应的形参数据类型必须兼容。
<ul>
<li>从游标中取值 </li>
</ul>
除非在FETCH时使用BULK COLLECT子句，否则FETCH语句每次只会从结果集中取出一条记录，并把游标向下移动，指向当前记录的下一条记录。下面演示了FETCH语句的用法： 
<blockquote>
<table border="0"><tbody><tr>
<td>
FETCH
c1INTO
my_empno,my_ename,my_deptno; </td>
</tr></tbody></table>
</blockquote>
对于游标查询返回的每一个字段值，在INTO列表中都必须有一个与之对应且类型兼容的变量。通常，我们可以像下面这样使用FETCH语句：
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
c1INTO
my_record; EXIT
WHEN
c1%NOTFOUND; --processdatarecord
 END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
查询可以在它的作用域范围内引用PL/SQL变量，但是，在查询中的任何变量只有在游标被打开时才计算它的值。在下面的例子中，每个被检索出来的salary只和2相乘，即使factor值在后续的操作中已经发生了改变：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
my_salemp.sal%TYPE
; 
my_jobemp.job%TYPE
; 
factorINTEGER
:=2; CURSOR
c1IS
 SELECT
factor*sal FROM
emp WHERE
job=my_job; BEGIN
 
... OPEN
c1;--herefactorequals2
 LOOP
 FETCH
c1 INTO
my_sal; EXIT
WHEN
c1%NOTFOUND; 
factor:=factor+1;--doesnotaffectFETCH
 END
LOOP
; END
; </td>
</tr></tbody></table>
</blockquote>
要想改变查询结果集或查询中的变量值，我们就必须关闭并重新打开含有输入变量的游标，这样才能使用新的输入变量值。 
但是，我们可以为同一游标每次取得的数据指定一个不同的INTO列表。这样每次取得的数据就会被放到目标变量中去，如下例所示： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename FROM
emp; 
name1emp.ename%TYPE
; 
name2emp.ename%TYPE
; 
name3emp.ename%TYPE
; BEGIN
 OPEN
c1; FETCH
c1 INTO
name1;--thisfetchesfirstrow
 FETCH
c1 INTO
name2;--thisfetchessecondrow
 FETCH
c1 INTO
name3;--thisfetchesthirdrow
 
... CLOSE
c1; END
; </td>
</tr></tbody></table>
</blockquote>
如果游标的指向超过了结果集中的最后一行，那么目标变量的值就无法确定。 
注意：在结果集的最后一次执行的FETCH语句一定取不到数据，而且没有异常发生。想要发现这种情况，我们就必须得使用游标的%FOUND或%NOTFOUND属性。 
<ul>
<li>批量取得游标中的数据 </li>
</ul>
BULK COLLECT子句能让我们批量的绑定数据。这样我们就能一次性从结果集中取得所有的行。在下面的例子中，我们从游标中批量取得数据分别放到两个集合中去：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
numtabIS
TABLE
OF
emp.empno%TYPE
; TYPE
nametabIS
TABLE
OF
emp.ename%TYPE
; 
numsnumtab; 
namesnametab; CURSOR
c1IS
 SELECT
empno,ename FROM
emp WHERE
job='CLERK'
; BEGIN
 OPEN
c1; FETCH
c1 BULK
COLLECT
INTO
nums,names; 
... CLOSE
c1; END
; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>关闭游标 </li>
</ul>
CLOSE语句可以关闭游标，游标被关闭后，我们还可以重新打开它。操作一个已经关闭了的游标会抛出预定义异常INVALID_CURSOR。 
<ul>
<li>在游标中使用子查询 </li>
</ul>
子查询就是出现在另外一个SQL数据操作语句中的查询(通常用圆括号封闭)。计算时，子查询能为语句提供一个值或是一个值集合。通常，子查询用在WHERE子句中。例如，下面的查询就用于返回不在芝加哥居住的雇员：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
empno,ename FROM
emp WHERE
deptnoIN
(SELECT
deptno FROM
dept WHERE
loc<>'CHICAGO'
); </td>
</tr></tbody></table>
</blockquote>
再举一个在FROM子句中使用子查询的例子，下面语句能够返回含有五个或五个以上雇员的部门编号和部门名称： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
t1.deptno,dname,"STAFF" FROM
deptt1,(SELECT
deptno,COUNT(*)"STAFF" FROM
emp GROUP
BY
deptno)t2 WHERE
t1.deptno=t2.deptnoAND
"STAFF">=5; </td>
</tr></tbody></table>
</blockquote>
针对一个数据表的子查询只执行一次，而具有关联关系的子查询会为每一行结果执行一次。如下面的查询，它的作用是找出工资超过所在部门的平均工资的雇员姓名和工资。对于emp表中的每一行，关联子查询都会为它计算一次部门的平均工资。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
deptno,ename,sal FROM
empt WHERE
sal>(SELECT
AVG
(sal) FROM
emp WHERE
t.deptno=deptno) ORDER
BY
deptno; </td>
</tr></tbody></table>
</blockquote>
2、隐式游标一览
Oracle会隐式地打开一个游标处理所有那些不与显式游标相关联的SQL语句。我们可以引用这个隐式的SQL游标，虽然不能使用OPEN、FETCH和CLOSE语句来控制SQL游标，但可以利用它的属性来获取与最近执行的SQL语句的相关信息。 
三、用包将游标的声明和游标体分离
我们可以将游标说明从游标体中分离出来并放到包中。这样做的话就可以在不改变游标说明的条件下修改游标体。我们可以在包说明中用下面语法编写游标说明： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CURSOR
cursor_name[(parameter[,parameter]...)]RETURN
return_type; </td>
</tr></tbody></table>
</blockquote>
在下面的例子中，我们可以使用%ROWTYPE属性来代表数据表emp中的行类型： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
emp_stuffAS
 CURSOR
c1RETURN
emp%ROWTYPE
;--declarecursorspec
 
... END
emp_stuff; CREATE
PACKAGE
BODY
emp_stuffAS
 CURSOR
c1RETURN
emp%ROWTYPE
IS
 SELECT
* FROM
emp WHERE
sal>2500;--definecursorbody
 
... END
emp_stuff; </td>
</tr></tbody></table>
</blockquote>
游标说明部分并不含有SELECT语句，因为后面RETURN子句中指明了返回值的数据类型。并且，游标体中含有的SELECT语句列表中的每一项，必须和说明部分的RETURN子句相匹配。
打包游标有着更好的灵活性。例如，我们可以任意修改上例中声明的游标的游标体而不用修改游标说明： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
BODY
emp_stuffAS
 CURSOR
c1RETURN
emp%ROWTYPE
IS
 SELECT
* FROM
emp WHERE
deptno=20;--newWHEREclause
 
... END
emp_stuff; </td>
</tr></tbody></table>
</blockquote>
我们可以使用点标志从一个PL/SQL块或子程序中引用一个打包游标，如下例所示：

<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
emp_recemp%ROWTYPE
; 
... BEGIN
 
... OPEN
emp_stuff.c1; LOOP
 FETCH
emp_stuff.c1 INTO
emp_rec; EXIT
WHEN
emp_suff.c1%NOTFOUND; 
... END
LOOP
; CLOSE
emp_stuff.c1; END
; </td>
</tr></tbody></table>
</blockquote>
打包游标的作用域并不局限于某个特定的PL/SQL块。所以，打开一个打包游标后，它会一直保持打开状态直到我们关闭它或是退出Oracle会话。 
四、使用游标FOR循环
在大多数需要使用显式游标的情况下，我们都可以用一个游标FOR循环来代替OPEN、FETCH和CLOSE语句。游标FOR循环隐式地声明了一
个%ROWTYPE类型的记录作为它的循环索引，打开游标，然后反复执行把结果集中的行放到索引中去，最后在所有行都被处理完成后关闭游标。
思考下面PL/SQL块例子，它能从一个实验中计算出结果，然后把结果保存在一张临时表中。FOR循环的索引c1_rec是被隐式声明的记录。它的每一个域都保存来自游标c1中取出的值。对独立的域的引用可以使用点标志。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
RESULTtemp.col1%TYPE
; CURSOR
c1IS
 SELECT
n1,n2,n3 FROM
data_table WHERE
exper_num=1; BEGIN
 FOR
c1_recIN
c1LOOP
 /*calculateandstoretheresults*/
 
RESULT:=c1_rec.n2/(c1_rec.n1+c1_rec.n3); INSERT
INTO
temp VALUES
(RESULT,NULL
,NULL
); END
LOOP
; COMMIT
; END
; </td>
</tr></tbody></table>
</blockquote>
当进入游标FOR循环时后，游标的名称就不属于用OPEN语句打开的游标，也不属于封闭游标FOR循环。在每个循环之前，PL/SQL会把数据放到隐式声明的记录中去。记录的有效作用范围只在循环内，所以我们不能在循环的外部引用它。
循环内的语句序列会为每一个满足条件的结果行执行一次，当游标离开循环时，游标会被自动地关闭，这包括正常地使用EXIT或GOTO语句来结束循环，或是因异常抛出而退出循环的情况。 
1、使用子查询代替显式游标
有时候我们并不需要声明游标，因为PL/SQL允许我们使用子查询来进行替代。下面的游标FOR循环先计算奖金值，然后把结果插入数据表中： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
bonusREAL
; BEGIN
 FOR
emp_recIN
(SELECT
empno,sal,comm FROM
emp)LOOP
 
bonus:=(emp_rec.sal*0.05)+(emp_rec.comm*0.25); INSERT
INTO
bonuses VALUES
(emp_rec.empno,bonus); END
LOOP
; COMMIT
; END
; </td>
</tr></tbody></table>
</blockquote>
2、使用游标子查询
我们可以使用游标子查询(又称游标表达式)把一个查询结果集作为参数传递给函数。如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
SELECT
*FROM
TABLE
(StockPivot(CURSOR
(SELECT
*FROM
StockTable))); </td>
</tr></tbody></table>
</blockquote>
游标子查询通常可以用在表函数中，这将在第八章详细讨论。
3、在游标FOR循环中为表达式定义别名
隐式声明的记录中每个域存放着最近取得的数据。记录的域名称和SELECT列表中的字段相对应。但是，如果SELECT中含有表达式时会发生什么呢？看一下下面的例子： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CURSOR
c1IS
 SELECT
empno,sal+NVL(comm,0)wages,job FROM
... </td>
</tr></tbody></table>
</blockquote>
这样的情况下，我们就必须为表达式起一个别名。如下例，wages就是表达式sal+NVL(comm,0)的一个别名：
<blockquote>
<table border="0"><tbody><tr>
<td>
CURSOR
c1IS
 SELECT
empno,sal+NVL(comm,0)wages,job FROM
... </td>
</tr></tbody></table>
</blockquote>
如果要引用对应的域，就得使用别名进行代替，如下例所示：
<blockquote>
<table border="0"><tbody><tr>
<td>
IF
emp_rec.wages<1000THEN
... </td>
</tr></tbody></table>
</blockquote>
4、为游标FOR循环传递参数
我们可以在游标FOR循环中把参数传递给游标。下例中，我们传递一个部门编号。然后计算出该部门应付给它的雇员的工资数额。并且，我们可以判断出有多少雇员的工资超过2000和/或他们的佣金大于他们的工资。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
emp_cursor(dnumNUMBER
)IS
 SELECT
sal,comm FROM
emp WHERE
deptno=dnum; 
total_wagesNUMBER
(11,2):=0; 
high_paidNUMBER
(4):=0; 
higher_commNUMBER
(4):=0; BEGIN
 /*Thenumberofiterationswillequalthenumberofrows 
returnedbyemp_cursor.*/
 FOR
emp_recordIN
emp_cursor(20)LOOP
 
emp_record.comm:=NVL(emp_record.comm,0); 
total_wages:=total_wages+emp_record.sal+emp_record.comm; IF
emp_record.sal>2000.00THEN
 
high_paid:=high_paid+1; END
IF
; IF
emp_record.comm>emp_record.salTHEN
 
higher_comm:=higher_comm+1; END
IF
; END
LOOP
; INSERT
INTO
temp VALUES
(high_paid,higher_comm, 'TotalWages:'
||TO_CHAR(total_wages)); COMMIT
; END
; </td>
</tr></tbody></table>
</blockquote>
五、使用游标变量
跟游标一样，游标变量也是指向多行查询的结果集中的当前行。但是，游标与游标变量不同之处就和常量与变量不同之处类似。游标是静态的，而游标变量是动态的，因为游标变量并不与某个特定的查询相绑定。所以，游标变量可以打开任何类型兼容的查询，灵活性很大。 
并且，我们还可以为游标变量赋新值，把它作为参数传递给本地和存储子程序。这就很容易地让我们把数据检索集中化处理。 
游标变量可以在每个PL/SQL客户端使用。例如，我们可以在OCI或Pro*C这样的主环境中声明游标变量，然后把它作为输入主变量(绑定变量)
传给PL/SQL。并且，像Oracle Forms和Oracle
Reports这样的含有PL/SQL引擎的开发工具，完全可以在客户端使用游标变量。Oracle服务器也有一个PL/SQL引擎。所以，我们可以在应
用程序和服务器之间通过远程调用(RPC)来回传递游标变量。 
1、什么是游标变量
游标变量同C或Pascal语言中的指针类似，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体
的内容。在PL/SQL中，指针是一个REF X类型，REF是REFERENCE的缩写，而X代表对象的类型。因此，游标变量的数据类型是REF
CURSOR。 
为了执行多行查询，Oracle会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声明指向工作区
的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。所以，游标和游标变量不能交互使用；也就是
说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。 
2、为什么使用游标变量
我们主要是在PL/SQL存储子程序和各种客户端之间用游标变量来传递查询结果。PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指
向存放结果集工作区的指针而已。例如，一个OCI客户端，一个Oracle Forms应用程序和Oracle服务器可以引用同一个工作区。 
只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。例如，我们把主游标变量传递到嵌套在Pro*C程序中的PL/SQL块，游标变量指向的工作区就可以被访问。 
如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。 
3、定义REF CURSOR类型
创建游标变量分为两个步骤。首先，我们得定义REF CURSOR类型，然后声明该类型的游标变量。我们可以在任何PL/SQL块、子程序或包内使用下面语法来定义REF CURSOR类型：
<blockquote>
<table border="0"><tbody><tr>
<td>
TYPE
ref_type_nameIS
REF
CURSOR
[RETURN
return_type]; </td>
</tr></tbody></table>
</blockquote>
ref_type_name是类型区分符，return_type必须是记录类型或是代表数据表中的行类型。在下面的例子中，我们把返回的数据类型指定为数据表dept的行类型：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
DeptCurTypIS
REF
CURSOR
RETURN
dept%ROWTYPE
; </td>
</tr></tbody></table>
</blockquote>
REF CURSOR类型可以是强类型也可以是弱类型。如下例所示，强类型REF CURSOR需要指定返回类型，而弱类型没有这个要求： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
EmpCurTypIS
REF
CURSOR
RETURN
emp%ROWTYPE
;--strong
 TYPE
GenericCurTypIS
REF
CURSOR
;--weak
</td>
</tr></tbody></table>
</blockquote>
强类型REF CURSOR在编译时会检查类型兼容，这样能更好的避免发生错误。但是，弱类型REF CURSOR具有更大的灵活性，因为它允许我们把游标变量与任何相兼容的查询关联起来。 
4、声明游标变量
定义了REF CURSOR后，就可以在PL/SQL块或子程序中声明该类型的游标变量了。在下面的程序中，我们声明了游标变量dept_cv： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
DeptCurTypIS
REF
CURSOR
RETURN
dept%ROWTYPE
; 
dept_cvDeptCurTyp;--declarecursorvariable
</td>
</tr></tbody></table>
</blockquote>
注意：不能在包里声明游标变量。与打包变量不同，游标变量没有一个持久的状态。记住，声明游标变量就是创建了一个指针，它只是指向一个内容，本身并
不存在，所以，游标变量是不能存放到数据库中的。变量的作用域规则也同样适用于游标变量。本地的PL/SQL游标变量在我们进入块或子程序时被初始化，在
退出时被销毁。
在REF CURSOR定义的RETURN子句中，我们可以使用%ROWTYPE指定返回类型，如下例：

<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
tmpcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; 
tmp_cvtmpcurtyp;--declarecursorvariable
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
tmp_cv%ROWTYPE
; 
emp_cvempcurtyp;--declarecursorvariable
</td>
</tr></tbody></table>
</blockquote>
同样，也可以使用%TYPE指定返回值类型，如下例：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
dept_recdept%ROWTYPE
;--declarerecordvariable
 TYPE
deptcurtypIS
REF
CURSOR
 RETURN
dept_rec%TYPE
; 
dept_cvdeptcurtyp;--declarecursorvariable
</td>
</tr></tbody></table>
</blockquote>
最后一个例子，我们把RETURN子句的返回类型指定为用户自定义的RECORD类型：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
emprectypIS
RECORD
( 
empnoNUMBER
(4), 
enameVARCHAR2
(1O), 
salNUMBER
(7,2) 
); TYPE
empcurtypIS
REF
CURSOR
 RETURN
emprectyp; 
emp_cvempcurtyp;--declarecursorvariable
</td>
</tr></tbody></table>
</blockquote>
<ul>
<li>作为参数的游标变量 </li>
</ul>
我们可以把游标变量声明为函数和过程的形式参数。在下面例子中，我们定义REF CURSOR类型的EmpCurTyp，然后把该类型的游标变量作为过程的形式参数： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp)IS
... </td>
</tr></tbody></table>
</blockquote>
注意：跟所有的指针一样，游标变量也能增加参数别名出现的可能性。
5、控制游标变量
我们可以使用三个语句来控制游标变量：OPEN-FOR、FETCH和CLOSE。首先，使用OPEN打开游标变量，然后从结果集中FETCH数据，当完成所有的处理后，就可以使用CLOSE语句关闭游标变量。 
<ul>
<li>打开游标变量 </li>
</ul>
OPEN-FOR语句把一个游标变量和一个多行查询关联起来并执行查询。语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
OPEN
{cursor_variable|:host_cursor_variable}FOR
 
{select_statement 
|dynamic_string[USINGbind_argument[,bind_argument]...]}; </td>
</tr></tbody></table>
</blockquote>
host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string代表多行查询的字符串表达式。
注意，这里只讨论使用静态SQL的情况。
与游标不同，游标变量是没有参数的。但这个并不影响灵活性，因为我们可以把整个查询传递给游标变量。被传递的查询语句可以使用主变量、PL/SQL变量、参数和函数。
下例中，我们首先打开游标变量emp_cv。与游标相似，我们也可以在游标变量上使用%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT： 
<blockquote>
<table border="0"><tbody><tr>
<td>
IF
NOT
emp_cv%ISOPENTHEN
 /*Opencursorvariable.*/
 OPEN
emp_cvFOR
SELECT
*FROM
emp; END
IF
; </td>
</tr></tbody></table>
</blockquote>
其它的OPEN-FOR语句可以使用不同的查询打开同样的游标变量。在重新打开游标变量之前是不需要关闭它的(但对一个静态游标使用多次OPEN操
作时，系统会抛出预定义异常CURSOR_ALREADY_OPEN)。为执行一个不同的查询而重新打开游标变量时，前面的查询结果就会丢失。
一般地，我们可以把游标变量传递给过程，然后由过程负责打开它，如下例，打包过程打开游标变量emp_cv： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
emp_dataAS
 
... TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp); END
emp_data; CREATE
PACKAGE
BODY
emp_dataAS
 
... PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp)IS
 BEGIN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp; END
open_emp_cv; END
emp_data; </td>
</tr></tbody></table>
</blockquote>
当我们把游标变量声明为一个打开游标变量的子程序的形式参数时，就必须要指定参数模式为IN OUT模式。那样的话，子程序就可以把一个打开的游标变量返回给调用者。
另外，我们还可以使用独立的过程来打开游标变量。只要简单的在包里定义REF CURSOR类型，然后在一个独立的过程中引用它就行了。例如，如果我们创建了下面的无体包，我们就能在独立的过程中引用包中所定义的REF CURSOR了： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
cv_typesAS
 TYPE
genericcurtypIS
REF
CURSOR
; TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; TYPE
deptcurtypIS
REF
CURSOR
 RETURN
dept%ROWTYPE
; 
... END
cv_types; </td>
</tr></tbody></table>
</blockquote>
下例中，我们创建一个引用REF CURSOR类型EmpCurTyp的过程，这个类型是在包cv_types中定义的。 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
open_emp_cv(emp_cvIN
OUT
cv_types.empcurtyp)AS
 BEGIN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp; END
open_emp_cv; </td>
</tr></tbody></table>
</blockquote>
为把数据检索集中化处理，我们可以把一个存储过程中类型兼容的查询进行分组。在下面的例子中，打包过程声明了一个选择器作为它的形势参数。调用时，过程会为选定的查询打开游标变量： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
emp_dataAS
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp,choiceINT); END
emp_data; CREATE
PACKAGE
BODY
emp_dataAS
 PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp,choiceINT)IS
 BEGIN
 IF
choice=1THEN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp WHERE
commIS
NOT
NULL
; ELSIF
choice=2THEN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp WHERE
sal>2500; ELSIF
choice=3THEN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp WHERE
deptno=20; END
IF
; END
; END
emp_data; </td>
</tr></tbody></table>
</blockquote>
为了获取更大的灵活性，我们可以把游标变量和选择器传递给过程，让它执行查询然后返回不同的查询结果。如下例所示： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
admin_dataAS
 TYPE
gencurtypIS
REF
CURSOR
; PROCEDURE
open_cv(generic_cvIN
OUT
gencurtyp,choiceINT); END
admin_data; CREATE
PACKAGE
BODY
admin_dataAS
 PROCEDURE
open_cv(generic_cvIN
OUT
gencurtyp,choiceINT)IS
 BEGIN
 IF
choice=1THEN
 OPEN
generic_cvFOR
 SELECT
* FROM
emp; ELSIF
choice=2THEN
 OPEN
generic_cvFOR
 SELECT
* FROM
dept; ELSIF
choice=3THEN
 OPEN
generic_cvFOR
 SELECT
* FROM
salgrade; END
IF
; END
; END
admin_data; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>使用游标变量作为主变量 </li>
</ul>
我们可以在OCI或Pro*C程序这样的PL/SQL主环境中声明游标变量。在使用游标变量之前，我们需要把它作为主变量传递给PL/SQL。在下面的Pro*C例子中，我们把主游标变量和选择器一并传递给PL/SQL块，然后为被选择的查询打开游标变量： 
<blockquote>
<table border="0"><tbody><tr>
<td>EXECSQL
BEGIN
DECLARE
SECTION; 
... /*Declarehostcursorvariable.*/
 
SQL_CURSORgeneric_cv; 
intchoice; 
EXECSQL
END
DECLARE
SECTION; 
... /*Initializehostcursorvariable.*/
 
EXECSQL
ALLOCATE:generic_cv; 
... /*PasshostcursorvariableandselectortoPL/SQLblock.*/
 
EXECSQL
EXECUTE
 BEGIN
 IF
:choice=1THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
emp; ELSIF
:choice=2THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
dept; ELSIF
:choice=3THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
salgrade; END
IF
; END
; END
-EXEC; </td>
</tr></tbody></table>
</blockquote>
主游标变量与任何查询的返回类型都兼容，它们就像PL/SQL中的弱类型游标变量一样。 
<ul>
<li>从游标变量中取得数据 </li>
</ul>
FETCH语句能从多行查询的结果集中取得数据，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
FETCH
{cursor_variable_name|:host_cursor_variable_name} 
[BULK
COLLECT
] INTO
{variable_name[,variable_name]...|record_name}; </td>
</tr></tbody></table>
</blockquote>
下面的例子中，我们每次都从游标变量emp_cv中取出一条数据放到用户定义的记录emp_rec中： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 /*Fetchfromcursorvariable.*/
 FETCH
emp_cv INTO
emp_rec; EXIT
WHEN
emp_cv%NOTFOUND;--exitwhenlastrowisfetched
 --processdatarecord
 END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
我们可以使用BULK COLLECT子句批量地从游标变量中取得数据放到一个或多个集合中。如下例所示：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; TYPE
namelistIS
TABLE
OF
emp.ename%TYPE
; TYPE
sallistIS
TABLE
OF
emp.sal%TYPE
; 
emp_cvempcurtyp; 
namesnamelist; 
salssallist; BEGIN
 OPEN
emp_cvFOR
 SELECT
ename,sal FROM
emp; FETCH
emp_cv BULK
COLLECT
INTO
names,sals; 
... END
; </td>
</tr></tbody></table>
</blockquote>
当游标变量被打开时，关联查询中的所有变量都会被计算。如果要改变查询中的结果集或要使用变量的最新值，我们就必须重新打开游标变量。不过我们可以为每一个从游标变量中取得数据使用不同的INTO子句。
PL/SQL能保证游标变量的返回类型与FETCH语句中的INTO子句后面的类型相兼容。对于游标变量的关联查询返回的每一个字段，INTO子句
后面都必须有一个与之相对应的、类型兼容的域或变量。同样，字段的个数和域的个数也应该相同。否则的话，就会产生错误。如果游标变量是强类型的话，这个错
误在编译期就会发生；如果是弱类型，错误会在运行时发生。在运行时，PL/SQL会在第一次取得数据之前抛出预定义异常
ROWTYPE_MISMATCH。所以，如果我们捕获到错误，并使用一个不同的INTO子句再次执行FETCH语句，就不会丢失数据。
如果我们把游标变量声明为从游标变量中取得数据的子程序的形式参数，那么我们必须指定参数模式为IN或IN OUT模式。但是，如果在子程序中还需要打开游标变量的话，就必须使用IN OUT模式。 
如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。 
<ul>
<li>关闭游标变量 </li>
</ul>
CLOSE语句会关闭游标变量。如果执行了关闭操作，相关的结果集就不确定了。关闭操作的语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CLOSE
{cursor_variable_name|:host_cursor_variable_name); </td>
</tr></tbody></table>
</blockquote>
在下面的例子中，当最后一行数据也被处理完毕时，我们就可以关闭游标变量emp_cv： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
emp_cv INTO
emp_rec; EXIT
WHEN
emp_cv%NOTFOUND; --processdatarecord
 END
LOOP
; /*Closecursorvariable.*/
 CLOSE
emp_cv; </td>
</tr></tbody></table>
</blockquote>
当把游标变量作为用于关闭游标变量的子程序的形式参数时，我们必须指定它的参数模式为IN或IN OUT模式。
如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。 
6、游标变量示例：主从表
思考下面的存储过程，它的作用是搜索图书馆数据库中的图书、期刊和磁带。主表存放标题和类别编号(其中1=书，2=期刊，3=磁带)。三个详细表分别保存特定类别的信息。在调用时，存储过程会按照标题来搜索主表，然后利用主表提供的类别编号到从详细表中检索详细内容。 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
cv_typesAS
 TYPE
libcurtypIS
REF
CURSOR
; 
... END
cv_types; CREATE
PROCEDURE
FIND_ITEM(titleVARCHAR2
, 
lib_cvIN
OUT
cv_types.libcurtyp)AS
 
codeBINARY_INTEGER
; BEGIN
 SELECT
item_code INTO
code FROM
titles WHERE
item_title=title; IF
code=1THEN
 OPEN
lib_cvFOR
 SELECT
* FROM
books WHERE
book_title=title; ELSIF
code=2THEN
 OPEN
lib_cvFOR
 SELECT
* FROM
periodicals WHERE
periodical_title=title; ELSIF
code=3THEN
 OPEN
lib_cvFOR
 SELECT
* FROM
tapes WHERE
tape_title=title; END
IF
; END
FIND_ITEM; </td>
</tr></tbody></table>
</blockquote>
7、游标变量示例：客户端PL/SQL块
一个客户端应用程序可能会使用下面的PL/SQL块来显示检索出来的信息： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
lib_cvcv_types.libcurtyp; 
book_recbooks%ROWTYPE
; 
periodical_recperiodicals%ROWTYPE
; 
tape_rectapes%ROWTYPE
; BEGIN
 
get_title(:title);--titleisahostvariable
 
FIND_ITEM(:title,lib_cv); FETCH
lib_cv INTO
book_rec; 
display_book(book_rec); EXCEPTION
 WHEN
ROWTYPE_MISMATCHTHEN
 BEGIN
 FETCH
lib_cv INTO
periodical_rec; 
display_periodical(periodical_rec); EXCEPTION
 WHEN
ROWTYPE_MISMATCHTHEN
 FETCH
lib_cv INTO
tape_rec; 
display_tape(tape_rec); END
; END
; </td>
</tr></tbody></table>
</blockquote>
8、游标变量示例：Pro*C程序
下面的Pro*C程序让用户选择一张数据表，然后使用游标变量进行查询，并返回查询结果：
<blockquote>
<table border="0"><tbody><tr>
<td>#include<stdio.h> 
#include<sqlca.h> 
voidsql_error(); 
main() 
{ 
chartemp[32]; 
EXECSQL
BEGIN
DECLARE
SECTION; 
char*uid="scott/tiger"
; 
SQL_CURSORgeneric_cv;/*cursorvariable*/
 
inttable_num;/*selector*/
 
struct/*EMPrecord*/
 
{ 
intemp_num; 
charemp_name[11]; 
charjob_title[10]; 
intmanager; 
charhire_date[10]; 
floatsalary; 
floatcommission; 
intdept_num; 
}emp_rec; 
struct/*DEPTrecord*/
 
{ 
intdept_num; 
chardept_name[15]; 
charlocation[14]; 
}dept_rec; 
struct/*BONUSrecord*/
 
{ 
charemp_name[11]; 
charjob_title[10]; 
floatsalary; 
}bonus_rec; 
EXECSQL
END
DECLARE
SECTION; /*HandleOracleerrors.*/
 
EXECSQL
WHENEVER
SQLERRORDO
sql_error(); /*ConnecttoOracle.*/
 
EXECSQL
CONNECT
:uid; /*Initializecursorvariable.*/
 
EXECSQL
ALLOCATE:generic_cv; /*Exitloopwhendonefetching.*/
 
EXECSQL
WHENEVER
NOT
FOUNDDO
break; 
for(;;) 
{ 
printf("\n1=EMP,2=DEPT,3=BONUS"
); 
printf("\nEntertablenumber(0toquit):"
); 
gets(temp); 
table_num=atoi(temp); 
if(table_num<=0)break; /*Opencursorvariable.*/
 
EXECSQL
EXECUTE
 BEGIN
 IF
:table_num=1THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
emp; ELSIF
:table_num=2THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
dept; ELSIF
:table_num=3THEN
 OPEN
:generic_cvFOR
 SELECT
* FROM
bonus; END
IF
; END
; END
-EXEC; 
for(;;) 
{ 
switch(table_num) 
{ 
case1:/*FetchrowintoEMPrecord.*/
 
EXECSQL
FETCH
:generic_cvINTO
:emp_rec; 
break; 
case2:/*FetchrowintoDEPTrecord.*/
 
EXECSQL
FETCH
:generic_cvINTO
:dept_rec; 
break; 
case3:/*FetchrowintoBONUSrecord.*/
 
EXECSQL
FETCH
:generic_cvINTO
:bonus_rec; 
break; 
} /*Processdatarecordhere.*/
 
} /*Closecursorvariable.*/
 
EXECSQL
CLOSE
:generic_cv; 
} 
exit(0); 
} 
voidsql_error() 
{ /*HandleSQLerrorhere.*/
 
} </td>
</tr></tbody></table>
</blockquote>
9、游标变量示例：SQL*Plus中操作主变量
主变量就是一个声明在主环境中的变量，它会被传递到一个或多个PL/SQL程序中，在程序中可以跟其他的变量一样使用。在SQL*Plus环境里，可以使用命令VARIABLE来声明主变量。例如，我们可以像下面这样声明一个NUMBER类型的主变量： 
<blockquote>
<table border="0"><tbody><tr>
<td>VARIABLEreturn_codeNUMBER
</td>
</tr></tbody></table>
</blockquote>
SQL*Plus和PL/SQL都能引用主变量，SQL*Plus还可以显示主变量的值。但是，在PL/SQL中引用主变量的时候，我们必须加上冒号(:)前缀，如下例所示： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
... BEGIN
 
:return_code:=0; IF
credit_check_ok(acct_no)THEN
 
:return_code:=1; END
IF
; 
... END
; </td>
</tr></tbody></table>
</blockquote>
在SQL*Plus环境里，我们可以使用PRINT命令来显示主变量的值，例如： 
<blockquote>
<table border="0"><tbody><tr>
<td>
SQL
>PRINTreturn_code 
RETURN_CODE -----------
 
1 </td>
</tr></tbody></table>
</blockquote>
SQL*Plus中的REF
CURSOR数据类型可以让我们声明游标变量，这样就可以使用存储子程序返回的查询结果。在下面的脚本中，我们声明了REFCURSOR类型的主变量。我
们还可以在SQL*Plus中使用SET AUTOPRINT ON命令来自动地显示查询结果： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
emp_dataAS
 TYPE
emprectypIS
RECORD
( 
emp_idNUMBER
(4), 
emp_nameVARCHAR2
(10), 
job_titleVARCHAR2
(9), 
dept_nameVARCHAR2
(14), 
dept_locVARCHAR2
(13) 
); TYPE
empcurtypIS
REF
CURSOR
 RETURN
emprectyp; PROCEDURE
get_staff(dept_noIN
NUMBER
,emp_cvIN
OUT
empcurtyp); END
; 
/ CREATE
PACKAGE
BODY
emp_dataAS
 PROCEDURE
get_staff(dept_noIN
NUMBER
,emp_cvIN
OUT
empcurtyp)IS
 BEGIN
 OPEN
emp_cvFOR
 SELECT
empno,ename,job,dname,loc FROM
emp,dept WHERE
emp.deptno=dept_noAND
emp.deptno=dept.deptno ORDER
BY
empno; END
; END
; 
/ 
COLUMNEMPNOHEADINGNumber 
COLUMNENAMEHEADINGName 
COLUMNJOBHEADINGJobTitle 
COLUMNDNAMEHEADINGDepartment 
COLUMNLOCHEADINGLocation SET
AUTOPRINTON
 
VARIABLEcvREFCURSOR EXECUTE
emp_data.get_staff(20,:cv) </td>
</tr></tbody></table>
</blockquote>
10、在向PL/SQL块传递主游标变量时减少网络负荷
在向PL/SQL传递主游标变量时，我们可以把多个OPEN-FOR语句组合在一起使用，以便减少网络流量。例如，下面的PL/SQL块： 
<blockquote>
<table border="0"><tbody><tr>
<td>
/*anonymousPL/SQLblockinhostenvironment*/
 BEGIN
 OPEN
:emp_cvFOR
 SELECT
* FROM
emp; OPEN
:dept_cvFOR
 SELECT
* FROM
dept; OPEN
:grade_cvFOR
 SELECT
* FROM
salgrade; OPEN
:pay_cvFOR
 SELECT
* FROM
payroll; OPEN
:ins_cvFOR
 SELECT
* FROM
insurance; END
; </td>
</tr></tbody></table>
</blockquote>
在Oracle Forms中，这种方法可能很有用，比如我们在填充一个多模块窗体的时候。 
当我们向PL/SQL块传递一个主游标变量时(游标变量由该块负责打开)，游标变量指向的查询工作区在块结束后还是能够被访问的。这就能让我们的OCI或Pro*C程序在普通的游标操作中继续使用这些工作区。下面的例子中，我们在一个块中打开了多个这样的工作区： 
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 OPEN
:c1FOR
 SELECT
1 FROM
DUAL; OPEN
:c2FOR
 SELECT
1 FROM
DUAL; OPEN
:c3FOR
 SELECT
1 FROM
DUAL; OPEN
:c4FOR
 SELECT
1 FROM
DUAL; OPEN
:c5FOR
 SELECT
1 FROM
DUAL; 
... END
; </td>
</tr></tbody></table>
</blockquote>
赋给c1、c1、c1、c1、c1的游标都可以正常使用。当使用完毕时，只要像下面这样简单的关闭就可以了：
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 CLOSE
:c1; CLOSE
:c2; CLOSE
:c3; CLOSE
:c4; CLOSE
:c5; 
... END
; </td>
</tr></tbody></table>
</blockquote>
11、避免游标变量的错误
如果在赋值操作中的两个游标变量都是强类型，那么它们必须有着完全相同的数据类型。下面的例子中，虽然游标变量的返回类型相同，但是在赋值操作时也会引起异常，这是因为它们的数据类型不相同： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; TYPE
tmpcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; PROCEDURE
open_emp_cv(emp_cvIN
OUT
empcurtyp,tmp_cvIN
OUT
tmpcurtyp)IS
 BEGIN
 
... 
emp_cv:=tmp_cv;--causes'wrongtype'error
 END
; </td>
</tr></tbody></table>
</blockquote>
如果其中一个或两个游标变量是弱类型，那它们就没必要类型相同了。 
如果我们要对一个没有指向工作区的游标变量进行数据取得、关闭或调用游标属性的操作，PL/SQL就会跑出一个INVALID_CURSOR异常。我们有两种方法可以让游标变量(或参数)指向工作区：
<ol>
<li>对游标变量使用OPEN-FOR语句。 </li>
</ol>
<ol>
<li>把一个已经OPEN过的主游标变量赋给PL/SQL游标变量。 </li>
</ol>
下面的例子演示了如何使用这两种方法： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; 
emp_cv1empcurtyp; 
emp_cv2empcurtyp; 
emp_recemp%ROWTYPE
; BEGIN
 /*Thefollowingassignmentisuselessbecauseemp_cv1 
doesnotpointtoaqueryworkareayet.*/
 
emp_cv2:=emp_cv1;--useless
 /*Makeemp_cv1pointtoaqueryworkarea.*/
 OPEN
emp_cv1FOR
 SELECT
* FROM
emp; /*Useemp_cv1tofetchfirstrowfromemptable.*/
 FETCH
emp_cv1 INTO
emp_rec; /*Thefollowingfetchraisesanexceptionbecauseemp_cv2 
doesnotpointtoaqueryworkareayet.*/
 FETCH
emp_cv2 INTO
emp_rec;--raisesINVALID_CURSOR
 EXCEPTION
 WHEN
INVALID_CURSORTHEN
 /*Makeemp_cv1andemp_cv2pointtosameworkarea.*/
 
emp_cv2:=emp_cv1; /*Useemp_cv2tofetchsecondrowfromemptable.*/
 FETCH
emp_cv2 INTO
emp_rec; /*Reuseworkareaforanotherquery.*/
 OPEN
emp_cv2FOR
 SELECT
* FROM
old_emp; /*Useemp_cv1tofetchfirstrowfromold_emptable. 
Thefollowingfetchsucceedsbecauseemp_cv1and 
emp_cv2pointtothesamequeryworkarea.*/
 FETCH
emp_cv1 INTO
emp_rec;--succeeds
 END
; </td>
</tr></tbody></table>
</blockquote>
把游标变量当作参数传递时一定要小心。在运行时，如果实参和形参的返回类型不兼容，PL/SQL就会抛出ROWTYPE_MISMATCH异常。
在下面的Pro*C程序中，我们定义了打包REF
CURSOR类型，并指明返回类型为emp%ROWTYPE。下一步，创建一个引用这个新类型的过程。然后在PL/SQL块内为表dept的查询打开主游
标变量。但是，在把打开的游标变量传递给存储过程的时候，由于形参和实参的返回类型不兼容，PL/SQL就抛出了ROWTYPE_MISMATCH异常。

<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
cv_typesAS
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; 
... END
cv_types; 
/ CREATE
PROCEDURE
open_emp_cv(emp_cvIN
OUT
cv_types.empcurtyp)AS
 BEGIN
 OPEN
emp_cvFOR
 SELECT
* FROM
emp; END
open_emp_cv; 
/ --anonymousPL/SQLblockinPro*Cprogram
 
EXECSQL
EXECUTE
 BEGIN
 OPEN
:cvFOR
 SELECT
* FROM
dept; 
... 
open_emp_cv(:cv);--raisesROWTYPE_MISMATCH
 END
; END
-EXEC; </td>
</tr></tbody></table>
</blockquote>
12、游标变量的限制
目前，游标变量有以下限制： 
<ol>
<li>不能在包中声明游标变量。例如，下面的声明就是不允许的：
<blockquote>

<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
emp_stuffAS
 TYPE
empcurtypIS
REF
CURSOR
 RETURN
emp%ROWTYPE
; 
emp_cvempcurtyp;--notallowed
 END
emp_stuff; </td>
</tr></tbody></table>
</blockquote>
</li>
<li>处于另外一个服务器上的远程子程序不能接受游标变量的值。因此，我们不能使用RPC将游标变量从一个服务器传递到另一个服务器。
</li>
<li>如果我们把主游标变量传递到PL/SQL中，就不能在服务器端从这个游标变量中取得数据了，除非打开这个游标变量的操作也是在同一个服务器调用中进行的。
</li>
<li>不能简单的用比较操作符来判断两个游标变量是否是相等，不相等或空。
</li>
<li>不能为游标变量赋空值。
</li>
<li>不能在CREATE TABLE或CREATE VIEW语句中把字段类型指定为REF CURSOR类型。因为数据库字段是不能存放游标变量值的。
</li>
<li>不能把REF CURSOR类型作为集合的元素类型，也就是说，索引表，嵌套表和变长数组不能存放游标变量的值。
</li>
<li>游标和游标变量不能互相替换。如下例中，不能把适用于游标的FOR循环应用在游标变量上：
<blockquote>

<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
EmpCurTypIS
REF
CURSOR
RETURN
emp%ROWTYPE
; 
emp_cvEmpCurTyp; 
... BEGIN
 
... FOR
emp_recIN
emp_cvLOOP
...--notallowed
 END
; </td>
</tr></tbody></table>
</blockquote>
</li>
</ol>
六、使用游标属性
每个显示的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。这些属性都能返回关于数据操作语句执行的有用信息。我们可以在过程化语句中使用游标属性，但不能在SQL语句中使用。
1、显式游标属性一览
每个显式的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOFOUND和%ROWCOUNT。我们可以在过程语句中使用这些属性，但不能再SQL语句中使用。 
显式游标属性能返回多行查询的信息。当一个显式游标或游标变量被打开时，满足查询条件的行就会被做上标记，最终形成结果集。然后我们就可以就从结果集中取出行数据。 
<ul>
<li>%FOUND属性：行被取出了吗？ </li>
</ul>
在一个游标或游标变量被打开后且在首次取得数据之前，%FOUND会产生NULL值。而此后每取得一行数据，它的值就为TRUE，直到最后一次取得数据失败，它的值变成FALSE。下例中，我们利用%FOUND的值来进行条件判断： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
c1 INTO
my_ename,my_sal,my_hiredate; IF
c1%FOUNDTHEN
--fetchsucceeded
 
... ELSE
--fetchfailed,soexitloop
 EXIT
; END
IF
; END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
在没有打开游标或游标变量的时候使用%FOUND会引起预定义异常INVALID_CURSOR。 
<ul>
<li>%ISOPEN属性：游标打开了吗？ </li>
</ul>
%ISOPEN在对应的游标或游标变量打开的时候返回TRUE;否则返回FALSE。下例中，我们用%ISOPEN来进行条件判断： 
<blockquote>
<table border="0"><tbody><tr>
<td>
IF
c1%ISOPENTHEN
--cursorisopen
 
... ELSE
--cursorisclosed,soopenit
 OPEN
c1; END
IF
; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>%NOTFOUND属性：FETCH失败了吗？ </li>
</ul>
%NOTFOUND属性的作用和%FOUND属性正好相反。它在数据取得失败时返回TRUE，成功时返回FALSE。在下面的例子中，当FETCH语句没有取得数据的时候，我们使用%NOTFOUND来退出循环： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
c1 INTO
my_ename,my_sal,my_hiredate; EXIT
WHEN
c1%NOTFOUND; 
... END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
在取数据之前，%NOTFOUND会返回NULL。所以，如果FETCH从来都没有成功执行的话，循环就不会退出。那是因为只有EXIT WHEN语句中的WHEN子句值为TRUE时，这条语句才能被执行。为了安全起见，我们可以使用下面的EXIT语句进行替换： 
<blockquote>
<table border="0"><tbody><tr>
<td>
EXIT
WHEN
c1%NOTFOUNDOR
c1%NOTFOUNDIS
NULL
; </td>
</tr></tbody></table>
</blockquote>
在没有打开游标或游标变量的时候使用%NOTFOUND会引起预定义异常INVALID_CURSOR。 
<ul>
<li>%ROWCOUNT属性：已经取得了多少条数据？ </li>
</ul>
当游标或游标变量被打开时，%ROWCOUNT值为零。每成功取得一条数据，%ROWCOUNT的值就加一。下例中，我们用%ROWCOUNT来判断取得的数据是否超过十条，然后采取相关的对策： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
c1 INTO
my_ename,my_deptno; IF
c1%ROWCOUNT>10THEN
 
... END
IF
; 
... END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
在没有打开游标或游标变量的时候使用%ROWCOUNT会引起预定义异常INVALID_CURSOR。 
下表是执行OPEN、FETCH或CLOSE语句前后对应的游标属性值： 
<table id="table-list" border="0"><tbody>
<tr>
<th id="table-list-head" colspan="2"></th>
<th id="table-list-head">%FOUND</th>
<th id="table-list-head">%ISOPEN</th>
<th id="table-list-head">%NOTFOUND</th>
<th id="table-list-head">%ROWCOUNT</th>

</tr>
<tr>
<td>OPEN</td>
<td>之前</td>
<td>异常</td>
<td>FALSE</td>
<td>异常</td>
<td>异常</td>
</tr>
<tr>
<td>OPEN</td>
<td>之后</td>
<td>NULL</td>
<td>TRUE</td>
<td>NULL</td>
<td>0</td>
</tr>
<tr>
<td>First FETCH</td>
<td>之前</td>
<td>NULL</td>
<td>TRUE</td>
<td>NULL</td>
<td>0</td>
</tr>
<tr>
<td>First FETCH</td>
<td>之后</td>
<td>TRUE</td>
<td>TRUE</td>
<td>FALSE</td>
<td>1</td>
</tr>
<tr>
<td>Next FETCH(es)</td>
<td>之前</td>
<td>TRUE</td>
<td>TRUE</td>
<td>FALSE</td>
<td>1</td>
</tr>
<tr>
<td>Next</td>
<td>之后</td>
<td>TRUE</td>
<td>TRUE</td>
<td>FALSE</td>
<td>与数据条数相关</td>
</tr>
<tr>
<td>Last FETCH</td>
<td>之前</td>
<td>TRUE</td>
<td>TRUE</td>
<td>FALSE</td>
<td>与数据条数相关</td>
</tr>
<tr>
<td>Last FETCH</td>
<td>之后</td>
<td>FALSE</td>
<td>TRUE</td>
<td>TRUE</td>
<td>与数据条数相关</td>
</tr>
<tr>
<td>CLOSE</td>
<td>之前</td>
<td>FALSE</td>
<td>TRUE</td>
<td>TRUE</td>
<td>与数据条数相关</td>
</tr>
<tr>
<td>CLOSE</td>
<td>之后</td>
<td>异常</td>
<td>FALSE</td>
<td>异常</td>
<td>异常</td>
</tr>
<tr>
<td colspan="6">注意： <ol>
<li>如果在游标打开之前或关闭之后引用属性%FOUND、%NOTFOUND或%ROWCOUNT，都会引起INVALID_CURSOR异常。
</li>
<li>第一个FETCH之后，如果结果集是空的，%FOUND会产生FALSE，%NOTFOUND会产生TRUE，%ROWCOUNT会产生0。 </li>
</ol>
</td>
</tr>
</tbody></table>
<ul>
<li>游标属性的一些实例 </li>
</ul>
假设我们有一个名为data_table的数据表，用它来收集实验室的实验数据，并且我们需要分析实验1的数据。在下面的例子中，我们可以计算出实验结果并把它们放到一个名为temp的数据表中。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
num1data_table.n1%TYPE
;--Declarevariables
 
num2data_table.n2%TYPE
;--havingsametypesas
 
num3data_table.n3%TYPE
;--databasecolumns
 
RESULTtemp.col1%TYPE
; CURSOR
c1IS
 SELECT
n1,n2,n3 FROM
data_table WHERE
exper_num=1; BEGIN
 OPEN
c1; LOOP
 FETCH
c1 INTO
num1,num2,num3; EXIT
WHEN
c1%NOTFOUND;--TRUE
whenFETCH
findsnomorerows
 
RESULT:=num2/(num1+num3); INSERT
INTO
temp VALUES
(RESULT,NULL
,NULL
); END
LOOP
; CLOSE
c1; COMMIT
; END
; </td>
</tr></tbody></table>
</blockquote>
在接下来的例子中，我们会检查所有那些包含零件号码为5469的贮藏器，把它们的内容提取出来直到累计到1000个单位： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
bin_cur(part_numberNUMBER
)IS
 SELECT
amt_in_bin FROM
bins WHERE
part_num=part_numberAND
amt_in_bin>0 ORDER
BY
bin_num FOR
UPDATE
OF
amt_in_bin; 
bin_amtbins.amt_in_bin%TYPE
; 
total_so_farNUMBER
(5):=0; 
amount_neededCONSTANT
NUMBER
(5):=1000; 
bins_looked_atNUMBER
(3):=0; BEGIN
 OPEN
bin_cur(5469); WHILE
total_so_far<amount_neededLOOP
 FETCH
bin_cur INTO
bin_amt; EXIT
WHEN
bin_cur%NOTFOUND; --ifweexit,there'snotenoughtofilltheorder
 
bins_looked_at:=bins_looked_at+1; IF
total_so_far+bin_amt<amount_neededTHEN
 UPDATE
bins SET
amt_in_bin=0 WHERE
CURRENT
OF
bin_cur; --takeeverythinginthebin
 
total_so_far:=total_so_far+bin_amt; ELSE
--wefinallyhaveenough
 UPDATE
bins SET
amt_in_bin=amt_in_bin-(amount_needed-total_so_far) WHERE
CURRENT
OF
bin_cur; 
total_so_far:=amount_needed; END
IF
; END
LOOP
; CLOSE
bin_cur; INSERT
INTO
temp VALUES
(NULL
,bins_looked_at,'<-binslookedat'
); COMMIT
; END
; </td>
</tr></tbody></table>
</blockquote>
2、隐式游标属性一览
隐式游标属性会返回一些关于INSERT、UPDATE、DELETE和SELECT INTO语句的执行信息。这些属性值总是与最近一次执行的语句相关。在Oracle打开SQL游标之前，隐式游标的所有属性都是NULL。 
要注意SQL游标还有另外一个专门为FORALL语句设计的%BULK_ROWCOUNT属性。
隐式游标属性和显式游标相同，也有%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，并且这些属性的用法也和显式游标的类
似，这里就不再详细说明。由于Oracle在执行完语句后会自动关闭SQL游标，所以SQL游标的%ISOPEN属性值总是FALSE。 
<ul>
<li>隐式游标属性的使用准则 </li>
</ul>
隐式游标的属性值总是与最后一次执行的语句相关，无论这个语句处于哪个作用域。所以，如果我们想把一个属性值保存起来以便以后使用，就要立即把它赋
给一个布尔变量。下面的例子中依赖于IF语句的条件是很危险的，因为过程check_status可能会改变属性%NOTFOUND的值： 
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 
... UPDATE
partsSET
quantity=quantity-1WHERE
partno=part_id; 
check_status(part_id);--procedurecall
 IF
SQL
%NOTFOUNDTHEN
--dangerous!
 
... END
; END
; </td>
</tr></tbody></table>
</blockquote>
我们可以像下面这样改善代码：
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 
... UPDATE
partsSET
quantity=quantity-1WHERE
partno=part_id; 
sql_notfound:=SQL
%NOTFOUND;--assignvaluetoBooleanvariable
 
check_status(part_id); IF
sql_notfoundTHEN
... END
; </td>
</tr></tbody></table>
</blockquote>
如果SELECT INTO没有返回结果，PL/SQL就会抛出预定义异常NO_DATA_FOUND。如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 
... SELECT
salINTO
my_salFROM
empWHERE
empno=my_empno; --mightraiseNO_DATA_FOUND
 IF
SQL
%NOTFOUNDTHEN
--conditiontestedonlywhenfalse
 
...--thisactionisnevertaken
 END
IF
; </td>
</tr></tbody></table>
</blockquote>
上面的检查是没有作用的，因为IF语句只在%NOTFOUND值是假的情况下才能进行检查。当PL/SQL抛出NO_DATA_FOUND异常，正常的执行就会终止，控制权被交给异常处理部分。
但一个调用聚合函数的SELECT INTO语句从来不会抛出异常NO_DATA_FOUND，因为聚合函数总会返回一个值或空。在这种情况下，%NOTFOUND就会产生FALSE，如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 
... SELECT
MAX
(sal)INTO
my_salFROM
empWHERE
deptno=my_deptno; --neverraisesNO_DATA_FOUND
 IF
SQL
%NOTFOUNDTHEN
--alwaystestedbutnevertrue
 
...--thisactionisnevertaken
 END
IF
; EXCEPTION
 WHEN
NO_DATA_FOUNDTHEN
...--neverinvoked
</td>
</tr></tbody></table>
</blockquote>
七、使用游标表达式
一个游标表达式能返回一个嵌套游标。结果集中的每一行跟平常一样，每个字段都包含一些值，其中的一些包含的是嵌套游标。因此，一个独立的查询就能返回从多个数据表间检索出来的相关值。我们可以用嵌套循环来处理结果集,然后再处理每一行中的嵌套游标。
PL/SQL支持把游标表达式作为游标声明、REF CURSOR声明和游标变量的一部分的查询。我们还可以在动态游标查询中使用游标表达式。语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CURSOR
(subquery) </td>
</tr></tbody></table>
</blockquote>
在从父级游标取得数据时，嵌套游标就会被隐式地打开。嵌套游标只有在下面的情况下才会被关闭：
<ol>
<li>嵌套游标被用户显式地关闭
</li>
<li>父级游标被重新执行
</li>
<li>父级游标被关闭
</li>
<li>父级游标被取消
</li>
<li>在从嵌套游标的一个父级游标中取数据时发生错误。嵌套游标会被作为清理内容的一部分而被关闭 </li>
</ol>
1、游标表达式的约束
<ol>
<li>不能在隐式游标中使用游标表达式
</li>
<li>游标表达式只能出现在：
<ol>
<li>非子查询的SELECT语句中，并且这条语句不能是游标表达式本身的子查询
</li>
<li>作为table函数的参数，出现在SELECT语句的FROM子句中 </li>
</ol>
</li>
<li>游标表达式只能出现在查询说明的SELECT列表中
</li>
<li>游标表达式不能出现在视图声明中
</li>
<li>不能对游标表达式进行BIND和EXECUTE操作 </li>
</ol>
2、游标表达式的示例
下例中，我们要用一个游标取出某个指定ID的地区中所有的部门。在我们取得每一个部门名称的过程中，我们也会从另一张表取出该部门雇员的详细信息。 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
OR
REPLACEPROCEDURE
emp_report(p_locidNUMBER
)IS
 TYPE
refcursorIS
REF
CURSOR
; --Thequeryreturnsonly2columns,butthesecondcolumnis
 --acursorthatletsustraverseasetofrelatedinformation.
 CURSOR
c1IS
 SELECT
l.city, CURSOR
(SELECT
d.department_name, CURSOR
(SELECT
e.last_name FROM
employeese WHERE
e.department_id=d.department_id 
)AS
ename FROM
departmentsd WHERE
l.location_id=d.location_id 
)dname FROM
locationsl WHERE
l.location_id=p_locid; 
loccurrefcursor; 
deptcurrefcursor; 
empcurrefcursor; 
v_citylocations.city%TYPE
; 
v_dnamedepartments.department_name%TYPE
; 
v_enameemployees.last_name%TYPE
; BEGIN
 OPEN
c1; LOOP
 FETCH
c1 INTO
v_city,loccur; EXIT
WHEN
c1%NOTFOUND; --WecanaccessthecolumnC1.city,thenprocesstheresultsof
 --thenestedcursor.
 LOOP
 FETCH
loccur INTO
v_dname,deptcur;--Noneedtoopen
 EXIT
WHEN
loccur%NOTFOUND; LOOP
 FETCH
deptcur INTO
v_ename;--Noneedtoopen
 EXIT
WHEN
deptcur%NOTFOUND; 
DBMS_OUTPUT.put_line(v_city||''
||v_dname||''
||v_ename); END
LOOP
; END
LOOP
; END
LOOP
; CLOSE
c1; END
; 
/ </td>
</tr></tbody></table>
</blockquote>
八、PL/SQL中的事务处理一览
在这里，我们学习一下，如何使用事务处理的基本技术来保证数据库的一致性，这其中包括如何提交或取消对数据库的改动。Oracle管理下的工作或任
务被称为会话。当我们运行应用程序或Oracle工具并连接到Oracle时，一个用户会话就会被开启。为了让用户会话可以"同步"工作并能共享计算机资
源，Oracle就必须控制并发，所谓并发指的是多个用户同时访问同样的数据资源。要是没有合适的并发控制的话，就可能无法保证数据的完整性。也就是说，
对数据的改变可能是在错误的秩序下完成的。 
Oracle使用锁来控制并发访问数据。锁可以让我们临时占有某个数据库资源，如一个数据表或是表中的一条数据。这样，数据就不能被其他用户改变，
直到我们结束对被锁定数据的处理。我们不需要显式地锁定一个资源，因为默认的锁机制会帮助我们保护数据和它的结构。但是，当我们想覆盖掉默认的锁时，我们
就可以从多个锁模型中(如行共享和行排他)选出一个，发出请求为表或行加上我们选定的锁来替代默认的锁。 
当两个或多个用户同时访问同一个模式对象时，就有可能发生死锁。比如说，两个用户要同时更新数据表，如果他们互相占有另外一个用户所要更新的资源，他们就会因得不到所需的资源而互相等待，直到Oracle向最后一个事务发出错误信号破除死锁为止。 
当一个数据表在同一时刻被一个用户查询另一个用户更新时，Oracle就会为数据查询生成一个读一致的视图。一旦查询开始并继续执行的时候，被读取
的数据是不会改变的。当更新活动执行时，Oracle会把数据表的数据和记录的变化内容放到回滚段中。Oracle利用回滚段建立读一致查询结果集并能在
必要的时候取消所变化的内容。 
1、如何用事务保护数据库
数据库事务是指作为单个逻辑工作单元执行的一系列操作。Oracle把一系列操作当作一个单元以便由语句引起的所有变动能够被一次性提交或回滚。如果在一个事务中某个环节执行失败，Oracle会自动地将数据内容恢复到执行前的状态。
程序中的第一条SQL语句会开启事务，当事务结束时，下一条SQL语句会自动地开启另一个事务。因此，每条SQL语句都是事务的一部分。一个分布式事务应该至少包含一条能够更新分布式数据库节点上的数据的SQL语句。 
COMMIT和ROLLBACK语句能确保所有的数据库变化一次性提交，或一次性回滚。自上次提交或回滚之后的所有SQL语句又成为当前事务的一部分。SAVEPOINT语句能为当前事务处理中的当前点进行命名与标记。 
2、使用COMMIT提交事务
COMMIT语句能终止当前事务，并把事务中的数据库变化提交到数据库中。在我们提交变化的内容之前，其他用户是无法访问到被修改了的数据；他们所看到的数据跟未修改之前的内容完全一样。
看一下事务的例子，假设把资金从一个银行的账户转入另一个银行的账户。这个事务需要做两次更新操作，借记第一个银行账户，然后借贷第二个银行账户。
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 
... UPDATE
accts SET
bal=my_bal-debit WHERE
acctno=7715; 
... UPDATE
accts SET
bal=my_bal+credit WHERE
acctno=7720; COMMIT
WORK
; END
; </td>
</tr></tbody></table>
</blockquote>
COMMIT命令会释放作用于表和行的锁，也能清除自上一次提交或回滚之后的所有保存点。可选关键字WORK只是用于改善可读性而已。而关键字END代表了PL/SQL块的结束，而不是事务的结束。就像块可以跨越多个事务一样，事务也能跨越多个块。
可选关键字COMMENT能让我们为某个分布式事务添加注释。在提交的过程中如果出现了网络或机器故障，分布式事务的状态就未知或是有疑问(in-
doubt)的了。那样的话，Oracle会在数据词典中保存COMMENT提供的文本内容和相关的事务ID。文本内容必须用引号夹起来的长度不超过50
字符的文字。如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
COMMIT
COMMENT
'In-doubtordertransaction;notifyOrderEntry'
; </td>
</tr></tbody></table>
</blockquote>
PL/SQL不支持FORCE子句，这个子句在SQL中可以手工提交一个有疑问的(in-doubt)分布式事务。例如，下面的语句是不允许的：
<blockquote>
<table border="0"><tbody><tr>
<td>
COMMIT
FORCE'23.51.54'
;--notallowedP257
</td>
</tr></tbody></table>
</blockquote>
3、使用ROLLBACK回滚事务
ROLLBACK语句能终止当前事务并放弃所有的数据变更。使用回滚有两个原因。第一，如果我们不小心误删了数据，回滚能帮助我们恢复原始数据。第
二，如果我们开启了一个因异常或SQL语句执行失败而不能完成的事务，回滚就能让我们的数据回到最初状态，然后重新再执行一次。如下面的例子，我们把一个
雇员的信息插入到三个不同的数据表中。如果插入过程中出现主键冲突，就会抛出DUP_VAL_ON_INDEX异常，这时，我们就可以在异常控制部分中使
用事务回滚了。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
emp_idINTEGER
; 
... BEGIN
 SELECT
empno,... INTO
emp_id,... FROM
new_emp WHERE
... 
... INSERT
INTO
empVALUES
(emp_id,...); INSERT
INTO
taxVALUES
(emp_id,...); INSERT
INTO
payVALUES
(emp_id,...); 
... EXCEPTION
 WHEN
DUP_VAL_ON_INDEXTHEN
 ROLLBACK
; 
... END
; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>语句级(Statement-Level)回滚 </li>
</ul>
执行SQL之前，Oracle会标记一个隐式的保存点。然后，在语句执行失败的时候，Oracle就会自动执行回滚操作。例如，如果一条
INSERT语句因主键冲突而执行失败，语句就会被回滚。这时只有未执行成功的SQL所作的工作被丢弃。而那条语句之前执行成功的语句所作工作都会被保存
下来。
Oracle还能回滚单条SQL语句并解除死锁，它会把错误发给参与执行的一个事务并回滚那个事务中的当前语句。
执行SQL语句之前，Oracle必须分析语法，确保语句满足语法规则并且语句内涉及到的模式对象都是有效的。语句执行时发现的错误能引起回滚操作，而分析时发现的错误不能引起回滚操作。 
4、使用SAVEPOINT回滚部分事务
SAVEPOINT能为事务处理中的当前点进行命名和标记。使用ROLLBACK
TO语句时，保存点能让我们恢复作了标记的事务的部分内容，而不是恢复整个事务。下例中，我们可以在插入操作之前标记一个保存点。如果INSERT语句要
把一个重复的值插入字段empno，预定义异常DUP_VAL_ON_INDEX就会被抛出。那样，我们可以回滚到保存点，只有恢复插入操作。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
emp_idemp.empno%TYPE
; BEGIN
 UPDATE
emp SET
... WHERE
empno=emp_id; DELETE
FROM
empWHERE
... 
... SAVEPOINT
do_insert; INSERT
INTO
empVALUES
(emp_id,...); EXCEPTION
 WHEN
DUP_VAL_ON_INDEXTHEN
 ROLLBACK
TO
do_insert; END
; </td>
</tr></tbody></table>
</blockquote>
当我们回滚到一个保存点时，任何在那个保存点之后标记的保存点都会被擦除。但是，我们所回滚到的保存点不会被擦除。例如，如果我们标记了五个保存点，然后回滚到第三个，那么只有第四个和第五个保存点会被擦除。一个简单的回滚或提交都会擦除所有的保存点。
如果我们在一个递归子程序里标记了一个保存点，递归中每级都会有一个SAVEPOINT语句实例被执行。但是，我们只能回滚到最近标记的那个保存点。
保存点的名称是未声明的标识符并能在事务中反复使用。每次使用都会把保存点从它的旧位置移动到事务当前点。因此，回滚到保存点的操作只会影响到事务的当前部分。如下例所示：
<blockquote>
<table border="0"><tbody><tr>
<td>
BEGIN
 SAVEPOINT
my_point; UPDATE
emp SET
... WHERE
empno=emp_id; 
... SAVEPOINT
my_point;--movemy_pointtocurrentpoint
 INSERT
INTO
empVALUES
(emp_id,...); EXCEPTION
 WHEN
OTHERS
THEN
 ROLLBACK
TO
my_point; END
; </td>
</tr></tbody></table>
</blockquote>
每一个会话中可用的保存点是没有限制的。一个有效的保存点就是一个自上一次提交或回滚之后的一个标记。
5、Oracle如何隐式回滚
在INSERT、UPDATE或DELETE语句执行之前，Oracle会标记一个隐式的保存点(对用户是不可用的)。如果语句执行失
败，Oracle就会回滚到保存点。正常情况下，只有失败的SQL语句被回滚，而不是整个事务。但是，如果语句抛出了一个未捕获异常，主环境会决定回滚哪
些内容。 
如果我们的存储子程序因未捕获异常而退出，PL/SQL就不会为OUT模式参数进行赋值。并且，PL/SQL也不会对子程序所做的操作进行回滚。 
6、终止事务
好的编程习惯是显式地执行提交或回滚每一个事务。是否在PL/SQL块或主环境中执行提交或回滚操作取决于程序的逻辑流程。如果我们没有显式地提交
或回滚，主环境会决定它的最终状态。例如在SQL*PLUS中，如果PL/SQL块没有包含COMMIT或ROLLBACK语句，事务的最终状态就由块后
的操作内容决定。如果我们执行一个数据定义，数据控制或COMMIT语句，或是调用EXIT，DISCONNECT或QUIT命令，Oracle都会提交
事务。如果我们执行了ROLLBACK或退出SQL*PLUS会话，Oracle就会回滚事务。 
在Oracle的预编译器环境中，如果程序非法结束，Oracle就会回滚事务。当程序显式地提交或回滚工作并使用RELEASE参数断开Oracle连接，它就能正常地退出： 
<blockquote>
<table border="0"><tbody><tr>
<td>EXECSQL
COMMIT
WORK
RELEASE
; </td>
</tr></tbody></table>
</blockquote>
7、使用SET TRANSACTION设置事务属性
我们可以使用SET
TRANSACTION语句开启一个只读或只写的事务，建立隔离级别或把当前事务赋给一个指定的回滚段。只读事务对于运行那些涉及到一个或多个数据表的多
查询来说，是很有用的；并且，在其他用户对表进行更新操作的时候，我们也可以对同样的表进行查询操作。
在只读事务中，所有的查询都会引用同一个提供多表，多查询，读一致视图的数据库快照。其他用户可以像平时一样继续查询或更新数据。在下面的例子中，
作为一个商店经理，我们可以使用一个只读事务来收集过去一天、一周和一个月的销售量。在事务中，这些数字不会受到其他更新数据的用户的影响： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
daily_salesREAL
; 
weekly_salesREAL
; 
monthly_salesREAL
; BEGIN
 
... COMMIT
;--endsprevioustransaction
 SET
TRANSACTIONREADONLYNAME'Calculatesalesfigures'
; SELECT
SUM
(amt) INTO
daily_sales FROM
sales WHERE
dte=SYSDATE
; SELECT
SUM
(amt) INTO
weekly_sales FROM
sales WHERE
dte>SYSDATE
-7; SELECT
SUM
(amt) INTO
monthly_sales FROM
sales WHERE
dte>SYSDATE
-30; COMMIT
;--endsread-onlytransaction
 
... END
; </td>
</tr></tbody></table>
</blockquote>
SET TRANSACTION语句必须是只读事务中的第一条SQL语句，且只能出现一次。如果把事务设置成READ ONLY，后续查询就能看到事务开始之前提交的内容。使用READ ONLY并不会影响其他用户或事务。 
<ul>
<li>SET TRANSACTION的约束 </li>
</ul>
只有SELECT INTO、OPEN、FETCH、CLOSE、LOCK TABLE、COMMIT和ROLLBACK语句才允许出现在只读事务中，并且查询过程不能使用FOR UPDATE。 
8、覆盖默认锁
默认情况下，Oracle会自动地帮助我们锁定数据结构。但是，当覆盖掉默认的锁会对我们更加有利时，我们就可以发出请求为行或表添加特殊的数据锁。显式锁定能让我们在事务中共享数据表或拒绝对数据表的访问。
使用LOCK TABLE语句可以显式地锁住整张数据表；而SELECT FOR
UPDATE可以锁定表中的特殊行，保证它们在更新或删除之前不会发生改变。但是，Oracle在执行更新或删除操作时会自动地获取行级锁(row-
level locks)。所以，只在我们希望更新或删除操作执行之前锁住行才使用FOR UPDATE子句。 
<ul>
<li>使用FOR UPDATE </li>
</ul>
当我们声明了一个被UPDATE或DELETE语句的子句CURRENT OF所引用的游标时，就必须使用FOR UPDATE子句来获取排它锁。如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
empno,sal FROM
emp WHERE
job='SALESMAN'
AND
comm>sal FOR
UPDATE
NOWAIT
; </td>
</tr></tbody></table>
</blockquote>
SELECT ... FOR UPDATE语句能够标记出那些将被更新或被删除的行，然后把它们一一锁定在结果集中。这在我们想对于行中已存在值进行修改时是很有用的。那样，我们就必须确定在更新行之前没有其他用户对它进行更改。
可选关键字NOWAIT能告诉Oracle，如果被请求行已经被其他用户锁定，那么就不需要等待了。控制权可以马上还给我们程序以便能够在重新获取锁之前做一些其他工作。如果不使用NOWAIT，Oracle会一直等待，直到能够访问到被锁定的行释放为止。 
打开游标时，所有的行都会被锁住，而不仅仅是被取出的行。提交或回滚事务能够让行解除锁定。所以，我们不能在事务提交之后从FOR UPDATE的游标中取得数据。 
查询多个数据表时，我们可以使用FOR UPDATE子句把行锁定限制在特定的表中。仅当FOR UPDATE OF子句引用到表中的一个字段的时候，该表中的行才会被锁定。例如，下面的查询就把行锁定在表emp，而不是dept：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename,dname FROM
emp,dept WHERE
emp.deptno=dept.deptnoAND
job='MANAGER'
 FOR
UPDATE
OF
sal; </td>
</tr></tbody></table>
</blockquote>
如下例所示，我们可以使用UPDATE或DELETE语句的CURRENT OF子句来引用从游标中取出的最新的行数据： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
empno,job,sal FROM
emp FOR
UPDATE
; 
... BEGIN
 OPEN
c1; LOOP
 FETCH
c1 INTO
... 
... UPDATE
emp SET
sal=new_sal WHERE
CURRENT
OF
c1; END
LOOP
; END
; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>使用LOCK TABLE </li>
</ul>
我们可以使用LOCK
TABLE语句把整张数据表用指定的锁模式进行锁定，这样就能共享或拒绝对这些表的访问。例如，下面的语句就把表emp用行共享的模式进行锁定。行共享锁
允许并行访问数据表；它能阻止其他用户为了独占数据表而将整张表锁定。当事务提交或回滚后，锁就会被释放。 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOCK
TABLE
empIN
ROW
SHARE
MODE
NOWAIT
; </td>
</tr></tbody></table>
</blockquote>
锁的模式决定了什么样的其它锁可以作用于数据表上。例如，许多用户都可以同时获取一个表上的行共享锁，但只可能有一个用户获取排他锁。当其中一个用户获取的排他锁时，其他的用户就不能插入、删除或更新表中的数据了。
一个表锁从不会阻止用户对表进行查询，而且查询也不会获取表锁。只有两个不同的事务尝试修改同样的数据时，才可能出现其中一个事务等待另一个事务完成的现象。 
<ul>
<li>提交后的数据取得 </li>
</ul>
FOR UPDATE子句能获取排他锁。打开游标时所有的行都会被锁住，在事务提交后锁会被释放。所以，我们不能在事务提交后从使用了FOR
UPDATE子句的游标中取得数据。如果这样做的话，PL/SQL就会抛出异常。下例中，游标FOR循环在第十次插入操作后会执行失败： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename FROM
emp FOR
UPDATE
OF
sal; 
ctrNUMBER
:=0; BEGIN
 FOR
emp_recIN
c1LOOP
--FETCHesimplicitly
 
... 
ctr:=ctr+1; INSERT
INTO
temp VALUES
(ctr,'stillgoing'
); IF
ctr>=10THEN
 COMMIT
;--releaseslocks
 END
IF
; END
LOOP
; END
; </td>
</tr></tbody></table>
</blockquote>
如果想在数据提交后也能取得数据，就不要使用FOR UPDATE和CURRENT OF子句。我们可以使用伪列ROWID模拟CURRENT
OF子句。只要把每行的ROWID放到UROWID类型的变量中就可以了。然后在后续的更新和删除操作中用ROWID来辨识当前行。示例如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename,job,ROWID
 FROM
emp; 
my_enameemp.ename%TYPE
; 
my_jobemp.job%TYPE
; 
my_rowidUROWID; BEGIN
 OPEN
c1; LOOP
 FETCH
c1 INTO
my_ename,my_job,my_rowid; EXIT
WHEN
c1%NOTFOUND; UPDATE
emp SET
sal=sal*1.05 WHERE
ROWID
=my_rowid; --thismimicsWHERE
CURRENT
OF
c1
 COMMIT
; END
LOOP
; CLOSE
c1; END
; </td>
</tr></tbody></table>
</blockquote>
一定要注意，上面的例子中，被取得的记录并没有被锁住，因为我们没有使用FOR
UPDATE子句。所以，其他用户可能无意地覆盖了我们所更新的内容。这样的话，游标就必须提供一个读一致的数据视图，而在更新中所使用的回滚段在游标关
闭之前是不能被释放的。这就会降低行更新的处理速度。下面的例子演示了我们如何使用一个游标的%ROWTYPE属性，其中，游标引用了ROWID伪列：

<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 CURSOR
c1IS
 SELECT
ename,sal,ROWID
 FROM
emp; 
emp_recc1%ROWTYPE
; BEGIN
 OPEN
c1; LOOP
 FETCH
c1 INTO
emp_rec; EXIT
WHEN
c1%NOTFOUND; 
... IF
...THEN
 DELETE
FROM
emp WHERE
ROWID
=emp_rec.ROWID
; END
IF
; END
LOOP
; CLOSE
c1; END
; </td>
</tr></tbody></table>
</blockquote>
九、使用自治事务完成单个逻辑工作单元
数据库事务是指作为单个逻辑工作单元执行的一系列SQL操作。通常，一个事务是由另外一个事务开启。在某些应用程序中，一个事务必须在开启它的事务的作用域之外进行操作。
自治事务是一个由其他事务(主事务)开启的独立的事务。自治事务可以把主事务挂起，然后执行SQL操作，在提交或回滚这些操作后，重新恢复主事务。下图是从主事务(MT)到自治事务(AT)然后返回的过程演示： 
<img src="https://p-blog.csdn.net/images/p_blog_csdn_net/rcom10002/244670/o_6-1.gif" alt="">1、自治事务的优点
自治事务一旦被开启，就完全独立。它不与主事务共享任何锁、资源或提交依赖(commit-dependency)。所以，我们不能把事件记入日志，增加重试计数器等等，即使是主事务执行了回滚操作。
更重要的是，自治事务可以帮助我们建立模块化和可重用的软件组件。例如，存储过程可以在它们自己的自治事务中独立执行。应用程序不必知道过程的匿名操作，存储过程也无需知道应用程序的事务上下文。这就使自治事务比常规事务更不容易出错，使用更方便。
另外，自治事务具有常规事务的所有功能。他们可以并发查询，分布处理，并能使用所有的事务控制语句，其中也包括SET TRANSACTION。 
2、定义自治事务
我们可以使用编译指示(编译器指令)AUTONOMOUS_TRANSACTION来定义自治事务。这个编译指示会让PL/SQL编译器把"程序"标记为自治的(独立的)。这里的术语"程序"包含： 
<ol>
<li>顶级(Top-level，非嵌套)自治PL/SQL块
</li>
<li>本地的、独立的或打包的函数和过程
</li>
<li>SQL对象类型的方法
</li>
<li>数据库触发器 </li>
</ol>
我们可以把这个指令放到程序声明部分的任何地方。但为了良好的可读性，一般把它放到声明的最顶部，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
PRAGMA
AUTONOMOUS_TRANSACTION; </td>
</tr></tbody></table>
</blockquote>
在下面的例子中，我们把一个函数标记为自治：
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
bankingAS
 
... FUNCTION
balance(acct_idINTEGER
) RETURN
REAL
; END
banking; CREATE
PACKAGE
BODY
bankingAS
 
... FUNCTION
balance(acct_idINTEGER
) RETURN
REAL
IS
 PRAGMA
AUTONOMOUS_TRANSACTION; 
my_balREAL
; BEGIN
 
... END
; END
banking; </td>
</tr></tbody></table>
</blockquote>
约束：我们不能在一个包中使用这个编译指示来达到把所有的子程序(或对象类型中的所有方法)标记为自治的目的。只有独立的程序才能被标记为自治的。例如，下面这样的编译指示是不能使用的：
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
bankingAS
 PRAGMA
AUTONOMOUS_TRANSACTION;--notallowed
 
... FUNCTION
balance(acct_idINTEGER
)RETURN
REAL
; END
banking; </td>
</tr></tbody></table>
</blockquote>
在下面的例子中，我们再把一个独立的过程标记为自治：
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
close_account(acct_idINTEGER
,OUT
balance)AS
 PRAGMA
AUTONOMOUS_TRANSACTION; 
my_balREAL
; BEGIN
...END
; </td>
</tr></tbody></table>
</blockquote>
下面，我们把一个PL/SQL块标记为自治：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 PRAGMA
AUTONOMOUS_TRANSACTION; 
my_empnoNUMBER
(4); BEGIN
 
... END
; </td>
</tr></tbody></table>
</blockquote>
约束：我们不可以把嵌套PL/SQL块标记为自治。
在下面的例子中，我们把一个数据库触发器标记为自治。与常规的触发器不同的是，自治触发器能够包含事务控制语句，如COMMIT和ROLLBACK。
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
TRIGGER
parts_trigger 
BEFOREINSERT
 ON
parts FOR
EACHROW
 DECLARE
 PRAGMA
AUTONOMOUS_TRANSACTION; BEGIN
 INSERT
INTO
parts_log VALUES
(:NEW
.pnum,:NEW
.pname); COMMIT
;--allowedonlyinautonomoustriggers
 END
; </td>
</tr></tbody></table>
</blockquote>
<ul>
<li>自治事务VS嵌套事务 </li>
</ul>
虽然匿名事务是由另一个事务开启的，但它并不是一个嵌套事务：
<ol>
<li>它不与主事务共享事务资源。
</li>
<li>它不依赖于主事务。例如，如果主事务回滚了，嵌套事务就会回滚，但自治事务不会。
</li>
<li>它提交变化的内容对其他事务来说是立即可见的。(一个嵌套事务所提交的变化内容在主事务提交之前对其它事务是不可见的。)
</li>
<li>自治事务中抛出的异常会产生事务级回滚，而不是语句级回滚。 </li>
</ol>
<ul>
<li>事务关联文 </li>
</ul>
如下图所示，主事务与嵌套程序共享它的关联文，但不与自治事务共享。同样，当一个自治程序调用另一个自治程序(或自我递归调用)，程序也不会共享事务关联文。但是，当自治程序调用一个非自治程序的时候，程序会共享同一个事务关联文。
<img src="https://p-blog.csdn.net/images/p_blog_csdn_net/rcom10002/244670/o_6-2.gif" alt=""><ul>
<li>事务可见性 </li>
</ul>
如图所示，自治事务在提交时它所做的内容变化对其它事务是可见的。当恢复到主事务的时候变化内容对主事务也是可见的，但这需要把它的隔离级别被设置为READ COMMITTED(默认)。
<img src="https://p-blog.csdn.net/images/p_blog_csdn_net/rcom10002/244670/o_6-3.gif" alt="">如果我们像下面一样把主事务的隔离级别设置为SERIALIZABLE，恢复主事务时，由它的自治事务所做的变化对主事务就不可见了。
<blockquote>
<table border="0"><tbody><tr>
<td>
SET
TRANSACTIONISOLATION
LEVEL
SERIALIZABLE; </td>
</tr></tbody></table>
</blockquote>
3、控制自治事务
一个自治程序中的第一个SQL语句会开启一个事务。当事务结束时下一个SQL语句又会开启另一个事务。自上一次提交或回滚后执行的SQL语句组成了当前事务。要控制自治事务，需使用下面的语句，它们只能应用到当前事务： 
<ol>
<li>COMMIT
</li>
<li>ROLLBACK [TO savepoint_name]
</li>
<li>SAVEPOINT savepoint_name
</li>
<li>SET TRANSACTION </li>
</ol>
COMMIT可以结束当前事务并把数据的变化保存到数据库中。ROLLBACK可以结束当前事务并放弃所有的数据变化，把数据恢复到未变化之前的状
态。ROLLBACK还可以部分恢复事务。SAVEPOINT可以在一个事务中标记当前点；SET
TRANSACTION能设置事务的属性，如读写访问和隔离级别。
要注意的是，设置在主事务中的属性并不会影响到它的自治事务。 
<ul>
<li>进入与退出 </li>
</ul>
当我们进入一个自治事务的执行部分时，主事务就被会挂起。当我们退出程序时，主事务就会恢复。要想正常地退出事务，我们就要显示地提交或回滚所有的自治事务。如果程序(或任何由它调用的程序)中含有状态无法确定的事务，就会有异常抛出，无法确定的事务就会被回滚。
<ul>
<li>提交与回滚 </li>
</ul>
COMMIT和ROLLBACK会结束活动自治事务但不退出自治程序。如下图所示，当一个事务结束，紧接着的下一条SQL语句就会开启另一个事务。
<img src="https://p-blog.csdn.net/images/p_blog_csdn_net/rcom10002/244670/o_6-4.gif" alt=""><ul>
<li>使用保存点 </li>
</ul>
保存点的作用域就是定义它的事务。定义在主事务中的保存点与定义在自治事务中的保存点没有任何关系。实际上，主事务和一个自治事务是可以使用相同的保存点名称的。
我们只能回滚到当前事务中标记的保存点。所以，当在一个自治事务中，我们不能回滚到主事务中标记的保存点。要是想这样做的话，我们就必须用已存在的自治程序恢复主事务。
在主事务中，如果回滚到一个在我们开启一个自治事务之前标记的保存点，那么回滚操作并不会影响到自治事务。记住，自治事务完全独立于主事务。
<ul>
<li>避免错误 </li>
</ul>
为了避免一些公共错误，在设计自治事务时一定要记住以下几点：
<ol>
<li>如果一个自治事务要访问主事务(自治程序退出之前是不能恢复的)拥有的资源，就可能发生死锁。那样的话，Oracle就会在自治事务中抛出异常，如果异常未被捕获，主事务就会被回滚。
</li>
<li>Oracle初始化参数TRANSACTIONS指定了并行事务的最大数量。如果自治事务(与主事务并行执行)没有被考虑的话这个数字就有可能被超出。
</li>
<li>如果我们没有使用提交或回滚操作退出一个活动自治事务，Oracle就会抛出一个异常。如果异常未被捕获，事务就会回滚。 </li>
</ol>
4、使用自治触发器
很多时候，我们可以使用数据库触发器记下事件。假定我们要跟踪一个数据表所有的插入操作，即使是那些后来被回滚掉的。在下例中，我们用触发器把重复
的行插入到一个影像表(shadow
table)。由于是自治触发器，所以，触发器能把插入的内容提交到影像表中，无论我们是否把插入内容提交到主表中。
<blockquote>
<table border="0"><tbody><tr>
<td>
--createamaintableanditsshadowtable
 CREATE
TABLE
parts(pnumNUMBER
(4),pnameVARCHAR2
(15)); CREATE
TABLE
parts_log(pnumNUMBER
(4),pnameVARCHAR2
(15)); --createanautonomoustriggerthatinsertsintothe
 --shadowtablebeforeeachinsertintothemaintable
 CREATE
TRIGGER
parts_trig 
BEFOREINSERT
 ON
parts FOR
EACHROW
 DECLARE
 PRAGMA
AUTONOMOUS_TRANSACTION; BEGIN
 INSERT
INTO
parts_log VALUES
(:NEW
.pnum,:NEW
.pname); COMMIT
; END
;--insertarowintothemaintable,andthencommittheinsert
 INSERT
INTO
parts VALUES
(1040,'HeadGasket'
); COMMIT
; --insertanotherrow,butthenrollbacktheinsert
 INSERT
INTO
parts VALUES
(2075,'OilPan'
); ROLLBACK
; --showthatonlycommittedinsertsaddrowstothemaintable
 SELECT
*FROM
partsORDER
BY
pnum; 
PNUMPNAME ----------------------
 
1040HeadGasket --showthatbothcommittedandrolled-backinsertsaddrows
 --totheshadowtable
 SELECT
*FROM
parts_logORDER
BY
pnum; 
PNUMPNAME ----------------------
 
1040HeadGasket 
2075OilPan </td>
</tr></tbody></table>
</blockquote>
不同于常规触发器的是，自治触发器还能使用本地动态SQL执行DDL语句。下例中，触发器bonus_trig在表bonus更新后，删除临时表temp_bonus： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
TRIGGER
bonus_trig 
AFTERUPDATE
 ON
bonus DECLARE
 PRAGMA
AUTONOMOUS_TRANSACTION;--enablestriggertoperformDDL
 BEGIN
 EXECUTE
IMMEDIATE
'DROPTABLEtemp_bonus'
; END
; </td>
</tr></tbody></table>
</blockquote>
5、从SQL中调用自治函数
从SQL语句中调用的函数，必须遵守控制副作用的规则。为了检查是否与规则相冲突，可以使用编译指示RESTRICT_REFERENCES。它的作用是判断函数是否读写数据表或打包变量。
但是，在定义的时候，自治程序总不会与规则"不读数据库"(RNDS)和"不写数据库"(WNDS)相冲突，即使事实上是冲突的。这样的特性是很有
用，在下面的例子中，当我们从查询中调用打包函数log_msg时，它能向数据表debug_output插入数据，而且还不与规则"不写数据库"冲突：

<blockquote>
<table border="0"><tbody><tr>
<td>
--createthedebugtable
 CREATE
TABLE
debug_output(msgVARCHAR2
(200)); --createthepackagespec
 CREATE
PACKAGE
debuggingAS
 FUNCTION
log_msg(msgVARCHAR2
) RETURN
VARCHAR2
; PRAGMA
RESTRICT_REFERENCES(log_msg,WNDS,RNDS); END
debugging; --createthepackagebody
 CREATE
PACKAGE
BODY
debuggingAS
 FUNCTION
log_msg(msgVARCHAR2
) RETURN
VARCHAR2
IS
 PRAGMA
AUTONOMOUS_TRANSACTION; BEGIN
 --thefollowinginsertdoesnotviolatetheconstraint
 --WNDSbecausethisisanautonomousroutine
 INSERT
INTO
debug_output VALUES
(msg); COMMIT
; RETURN
msg; END
; END
debugging; --callthepackagedfunctionfromaquery
 DECLARE
 
my_empnoNUMBER
(4); 
my_enameVARCHAR2
(15); BEGIN
 
... SELECT
debugging.log_msg(ename) INTO
my_ename FROM
emp WHERE
empno=my_empno; --evenifyourollbackinthisscope,theinsert
 --into'debug_output'remainscommittedbecause
 --itispartofanautonomoustransaction
 IF
...THEN
 ROLLBACK
; END
IF
; END
; </td>
</tr></tbody></table>
</blockquote>
十、确保PL/SQL程序的向后兼容
PL/SQL第二版允许使用一些不再使用的非正常功能： 
<ol>
<li>声明变量时,可以对记录和表类型向前引用
</li>
<li>在函数说明的RETURN子句中指定变量(非类型)名称
</li>
<li>使用IN模式的参数为index-by表的元素赋值
</li>
<li>把一个IN模式的记录中的一个字段作为另一个子程序的OUT模式参数
</li>
<li>把一个OUT模式的记录中的一个字段放置于赋值符号的右边
</li>
<li>在SELECT语句的FROM列表中使用OUT模式参数 </li>
</ol>
为了向后兼容，我们可以设置PLSQL_V2_COMPATIBILITY标识来保留第二版的这些功能，在服务器端有两种方法设置这个标识： 
<ol>
<li>把下面行添加到Oracle初始化文件中：
<blockquote>

<table border="0"><tbody><tr>
<td>PLSQL_V2_COMPATIBILITY=TRUE
</td>
</tr></tbody></table>
</blockquote>
</li>
<li>执行下面的语句：
<blockquote>

<table border="0"><tbody><tr>
<td>
ALTER
SESSIONSET
plsql_v2_compatibility=TRUE
; ALTER
SYSTEMSET
plsql_v2_compatibility=TRUE
; </td>
</tr></tbody></table>
</blockquote>
</li>
</ol>
如果我们把标识指定为FALSE(默认的)，就不能使用非正常功能。在客户端，命令行选项可以设置标识。例如，使用Oracle预编译程序，我们就可以在命令行中指定运行时选项DBMS。 
</div>

iteye_2060

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第六章 PL/SQL与Oracle间交互

第六章 PL/SQL与Oracle间交互&lt;!-- InstanceEndEditable --&gt;&lt;!-- InstanceBeginEditable name="EditRegion2" --&gt;一、PL/SQL支持的SQL一览PL/SQL扩展了SQL，变得更加强大和易用。我们可以用PL/SQL灵活安全地操作Oracle数据，因为它支持所有的SQL数据操...
复制链接

扫一扫