📄 第六章 pl-sql与oracle间交互 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<P>游标是可以接收参数的，这些参数可以在关联查询中允许出现常量的地方使用。游标的形式参数都必须是IN模式的。因此，它们不能把值赋给实际参数。并且，我们不能给游标的参数添加NOT
NULL约束。</P>
<P>如下例所示，我们为游标参数初始化一个默认值。这样，我们就能给游标传递不同个数的实参。并且，我们还可以在不改变游标引用的情况下添加新的参数。</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 (low <STRONG>INTEGER</STRONG> <STRONG>DEFAULT</STRONG> 0, high <STRONG>INTEGER</STRONG> <STRONG>DEFAULT</STRONG> 99) <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>游标参数作用域对于游标来说是本地的，这也就意味着它们只能在游标声明时所指定的查询语句中使用。游标参数的值只在游标被打开的时候被使用。
</P>
<UL>
<LI>打开游标 </LI></UL>
<P>游标只能在打开之后才能执行查询操作。对于使用了FOR
UPDATE子句的游标来说，OPEN语句会把满足查询条件的行琐住，如下例所示： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG> <BR> <STRONG>SELECT</STRONG> ename, job<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> sal &lt; 3000;<BR> ...<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>OPEN语句执行时结果集中的行并不被选取，只有在FETCH语句执行的时候数据才被取得。 </P>
<UL>
<LI>传递游标参数 </LI></UL>
<P>我们可以用OPEN语句来为游标传递参数。如果在没有使用参数默认值的情况下，我们就必须在OPEN语句中为游标声明中的每一个形式参数指定一个对应的实际参数。比如下面的游标声明：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> emp_name emp.ename%<STRONG>TYPE</STRONG>;<BR> salary emp.sal%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>CURSOR</STRONG> c1 (NAME <STRONG>VARCHAR2</STRONG>, salary <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下面三个语句都能打开游标：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>OPEN</STRONG> c1(emp_name, 3000);<BR><STRONG>OPEN</STRONG> c1(<EM>'ATTLEY'</EM>, 1500);<BR><STRONG>OPEN</STRONG> c1(emp_name, salary);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在上面的例子中，标识符salary用在游标声明的时候，它引用的是形式参数。但是，当用在OPEN语句中时，它引用的就是PL/SQL变量，为了避免混乱，最好使用唯一的标识。</P>
<P>有默认值的形参不需要有对应的实参，在OPEN语句执行时程序会自动使用它们的默认值。</P>
<P>我们可以用位置标识法或名称标识法把OPEN语句中的实参和游标声明中的形参关联起来。每个实参的数据类型和它对应的形参数据类型必须兼容。</P>
<UL>
<LI>从游标中取值 </LI></UL>
<P>除非在FETCH时使用BULK
COLLECT子句，否则FETCH语句每次只会从结果集中取出一条记录，并把游标向下移动，指向当前记录的下一条记录。下面演示了FETCH语句的用法：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> c1 <STRONG>INTO</STRONG> my_empno, my_ename, my_deptno;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>对于游标查询返回的每一个字段值，在INTO列表中都必须有一个与之对应且类型兼容的变量。通常，我们可以像下面这样使用FETCH语句：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> c1 <STRONG>INTO</STRONG> my_record;<BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> c1%NOTFOUND;<BR> <EM>-- process data record</EM><BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>查询可以在它的作用域范围内引用PL/SQL变量，但是，在查询中的任何变量只有在游标被打开时才计算它的值。在下面的例子中，每个被检索出来的salary只和2相乘，即使factor值在后续的操作中已经发生了改变：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> my_sal emp.sal%<STRONG>TYPE</STRONG>;<BR> my_job emp.job%<STRONG>TYPE</STRONG>;<BR> factor <STRONG>INTEGER</STRONG> := 2;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> factor * sal<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> job = my_job;<BR><STRONG>BEGIN</STRONG><BR> ...<BR> <STRONG>OPEN</STRONG> c1; <EM>-- here factor equals 2</EM><BR><BR> <STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>INTO</STRONG> my_sal;<BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> c1%NOTFOUND;<BR> factor := factor + 1; <EM>-- does not affect FETCH</EM><BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要想改变查询结果集或查询中的变量值，我们就必须关闭并重新打开含有输入变量的游标，这样才能使用新的输入变量值。
</P>
<P>但是，我们可以为同一游标每次取得的数据指定一个不同的INTO列表。这样每次取得的数据就会被放到目标变量中去，如下例所示：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> ename<BR> <STRONG>FROM</STRONG> emp;<BR><BR> name1 emp.ename%<STRONG>TYPE</STRONG>;<BR> name2 emp.ename%<STRONG>TYPE</STRONG>;<BR> name3 emp.ename%<STRONG>TYPE</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>INTO</STRONG> name1; <EM>-- this fetches first row</EM><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>INTO</STRONG> name2; <EM>-- this fetches second row</EM><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>INTO</STRONG> name3; <EM>-- this fetches third row</EM><BR> ...<BR> <STRONG>CLOSE</STRONG> c1;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>如果游标的指向超过了结果集中的最后一行，那么目标变量的值就无法确定。 </P>
<P>注意：在结果集的最后一次执行的FETCH语句一定取不到数据，而且没有异常发生。想要发现这种情况，我们就必须得使用游标的%FOUND或%NOTFOUND属性。
</P>
<UL>
<LI>批量取得游标中的数据 </LI></UL>
<P>BULK
COLLECT子句能让我们批量的绑定数据。这样我们就能一次性从结果集中取得所有的行。在下面的例子中，我们从游标中批量取得数据分别放到两个集合中去：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numtab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.empno%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>TYPE</STRONG> nametab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.ename%<STRONG>TYPE</STRONG>;<BR><BR> nums numtab;<BR> names nametab;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, ename<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> job = <EM>'CLERK'</EM>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> nums, names;<BR> ...<BR> <STRONG>CLOSE</STRONG> c1;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>关闭游标 </LI></UL>
<P>CLOSE语句可以关闭游标，游标被关闭后，我们还可以重新打开它。操作一个已经关闭了的游标会抛出预定义异常INVALID_CURSOR。
</P>
<UL>
<LI>在游标中使用子查询 </LI></UL>
<P>子查询就是出现在另外一个SQL数据操作语句中的查询(通常用圆括号封闭)。计算时，子查询能为语句提供一个值或是一个值集合。通常，子查询用在WHERE子句中。例如，下面的查询就用于返回不在芝加哥居住的雇员：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, ename<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno <STRONG>IN</STRONG> (<STRONG>SELECT</STRONG> deptno<BR> <STRONG>FROM</STRONG> dept<BR> <STRONG>WHERE</STRONG> loc <> <EM>'CHICAGO'</EM>);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>再举一个在FROM子句中使用子查询的例子，下面语句能够返回含有五个或五个以上雇员的部门编号和部门名称：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> t1.deptno, dname, "STAFF"<BR> <STRONG>FROM</STRONG> dept t1, (<STRONG>SELECT</STRONG> deptno, COUNT (*) "STAFF"<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>GROUP</STRONG> <STRONG>BY</STRONG> deptno) t2<BR> <STRONG>WHERE</STRONG> t1.deptno = t2.deptno <STRONG>AND</STRONG> "STAFF" >= 5;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>针对一个数据表的子查询只执行一次，而具有关联关系的子查询会为每一行结果执行一次。如下面的查询，它的作用是找出工资超过所在部门的平均工资的雇员姓名和工资。对于emp表中的每一行，关联子查询都会为它计算一次部门的平均工资。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> deptno, ename, sal<BR> <STRONG>FROM</STRONG> emp t<BR> <STRONG>WHERE</STRONG> sal > (<STRONG>SELECT</STRONG> <STRONG>AVG</STRONG> (sal)<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> t.deptno = deptno)<BR> <STRONG>ORDER</STRONG> <STRONG>BY</STRONG> deptno;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、隐式游标一览</P>
<P>Oracle会隐式地打开一个游标处理所有那些不与显式游标相关联的SQL语句。我们可以引用这个隐式的SQL游标，虽然不能使用OPEN、FETCH和CLOSE语句来控制SQL游标，但可以利用它的属性来获取与最近执行的SQL语句的相关信息。
</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -