📄 第六章 pl-sql与oracle间交互 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<P class=title1>三、用包将游标的声明和游标体分离</P>
<P>我们可以将游标说明从游标体中分离出来并放到包中。这样做的话就可以在不改变游标说明的条件下修改游标体。我们可以在包说明中用下面语法编写游标说明：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CURSOR</STRONG> cursor_name [(parameter[, parameter]...)] <STRONG>RETURN</STRONG> return_type;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在下面的例子中，我们可以使用%ROWTYPE属性来代表数据表emp中的行类型： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> emp_stuff <STRONG>AS</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>RETURN</STRONG> emp%<STRONG>ROWTYPE</STRONG>; <EM>-- declare cursor spec</EM><BR> ...<BR><STRONG>END</STRONG> emp_stuff;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> emp_stuff <STRONG>AS</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>RETURN</STRONG> emp%<STRONG>ROWTYPE</STRONG> <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> sal > 2500; <EM>-- define cursor body</EM><BR> ...<BR><STRONG>END</STRONG> emp_stuff;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>游标说明部分并不含有SELECT语句，因为后面RETURN子句中指明了返回值的数据类型。并且，游标体中含有的SELECT语句列表中的每一项，必须和说明部分的RETURN子句相匹配。</P>
<P>打包游标有着更好的灵活性。例如，我们可以任意修改上例中声明的游标的游标体而不用修改游标说明： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> emp_stuff <STRONG>AS</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>RETURN</STRONG> emp%<STRONG>ROWTYPE</STRONG> <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno = 20; <EM>-- new WHERE clause</EM><BR> ...<BR><STRONG>END</STRONG> emp_stuff;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以使用点标志从一个PL/SQL块或子程序中引用一个打包游标，如下例所示：</P>
<P> </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> emp_rec emp%<STRONG>ROWTYPE</STRONG>;<BR> ...<BR><STRONG>BEGIN</STRONG><BR> ...<BR> <STRONG>OPEN</STRONG> emp_stuff.c1;<BR><BR> <STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> emp_stuff.c1<BR> <STRONG>INTO</STRONG> emp_rec;<BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> emp_suff.c1%NOTFOUND;<BR> ...<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>CLOSE</STRONG> emp_stuff.c1;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>打包游标的作用域并不局限于某个特定的PL/SQL块。所以，打开一个打包游标后，它会一直保持打开状态直到我们关闭它或是退出Oracle会话。
</P>
<P class=title1>四、使用游标FOR循环</P>
<P>在大多数需要使用显式游标的情况下，我们都可以用一个游标FOR循环来代替OPEN、FETCH和CLOSE语句。游标FOR循环隐式地声明了一个
%ROWTYPE类型的记录作为它的循环索引，打开游标，然后反复执行把结果集中的行放到索引中去，最后在所有行都被处理完成后关闭游标。</P>
<P>思考下面PL/SQL块例子，它能从一个实验中计算出结果，然后把结果保存在一张临时表中。FOR循环的索引c1_rec是被隐式声明的记录。它的每一个域都保存来自游标c1中取出的值。对独立的域的引用可以使用点标志。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> RESULT temp.col1%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> n1, n2, n3<BR> <STRONG>FROM</STRONG> data_table<BR> <STRONG>WHERE</STRONG> exper_num = 1;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FOR</STRONG> c1_rec <STRONG>IN</STRONG> c1 <STRONG>LOOP</STRONG><BR> <EM>/* calculate and store the results */</EM><BR> RESULT := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> temp<BR> <STRONG>VALUES</STRONG> (RESULT, <STRONG>NULL</STRONG>, <STRONG>NULL</STRONG>);<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>当进入游标FOR循环时后，游标的名称就不属于用OPEN语句打开的游标，也不属于封闭游标FOR循环。在每个循环之前，PL/SQL会把数据放到隐式声明的记录中去。记录的有效作用范围只在循环内，所以我们不能在循环的外部引用它。</P>
<P>循环内的语句序列会为每一个满足条件的结果行执行一次，当游标离开循环时，游标会被自动地关闭，这包括正常地使用EXIT或GOTO语句来结束循环，或是因异常抛出而退出循环的情况。
</P>
<P class=title2>1、使用子查询代替显式游标</P>
<P>有时候我们并不需要声明游标，因为PL/SQL允许我们使用子查询来进行替代。下面的游标FOR循环先计算奖金值，然后把结果插入数据表中：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> bonus <STRONG>REAL</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FOR</STRONG> emp_rec <STRONG>IN</STRONG> (<STRONG>SELECT</STRONG> empno, sal, comm<BR> <STRONG>FROM</STRONG> emp) <STRONG>LOOP</STRONG><BR> bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> bonuses<BR> <STRONG>VALUES</STRONG> (emp_rec.empno, bonus);<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、使用游标子查询</P>
<P>我们可以使用游标子查询(又称游标表达式)把一个查询结果集作为参数传递给函数。如下例： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SELECT</STRONG> * <STRONG>FROM</STRONG> <STRONG>TABLE</STRONG>(StockPivot(<STRONG>CURSOR</STRONG>(<STRONG>SELECT</STRONG> * <STRONG>FROM</STRONG> StockTable)));
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>游标子查询通常可以用在表函数中，这将在第八章详细讨论。</P>
<P class=title2>3、在游标FOR循环中为表达式定义别名</P>
<P>隐式声明的记录中每个域存放着最近取得的数据。记录的域名称和SELECT列表中的字段相对应。但是，如果SELECT中含有表达式时会发生什么呢？看一下下面的例子：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, sal + NVL (comm, 0) wages, job<BR> <STRONG>FROM</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>这样的情况下，我们就必须为表达式起一个别名。如下例，wages就是表达式sal+NVL(comm,0)的一个别名：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, sal + NVL (comm, 0) wages, job<BR> <STRONG>FROM</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>如果要引用对应的域，就得使用别名进行代替，如下例所示：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>IF</STRONG> emp_rec.wages < 1000 <STRONG>THEN</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>4、为游标FOR循环传递参数</P>
<P>我们可以在游标FOR循环中把参数传递给游标。下例中，我们传递一个部门编号。然后计算出该部门应付给它的雇员的工资数额。并且，我们可以判断出有多少雇员的工资超过2000和/或他们的佣金大于他们的工资。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> emp_cursor (dnum <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> sal, comm<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno = dnum;<BR><BR> total_wages <STRONG>NUMBER</STRONG> (11, 2) := 0;<BR> high_paid <STRONG>NUMBER</STRONG> (4) := 0;<BR> higher_comm <STRONG>NUMBER</STRONG> (4) := 0;<BR><STRONG>BEGIN</STRONG><BR> <EM>/* The number of iterations will equal the number of rows<BR> returned by emp_cursor. */</EM><BR> <STRONG>FOR</STRONG> emp_record <STRONG>IN</STRONG> emp_cursor (20) <STRONG>LOOP</STRONG><BR> emp_record.comm := NVL (emp_record.comm, 0);<BR> total_wages := total_wages + emp_record.sal + emp_record.comm;<BR> <STRONG>IF</STRONG> emp_record.sal > 2000.00 <STRONG>THEN</STRONG><BR> high_paid := high_paid + 1;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>IF</STRONG> emp_record.comm > emp_record.sal <STRONG>THEN</STRONG><BR> higher_comm := higher_comm + 1;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> temp<BR> <STRONG>VALUES</STRONG> (high_paid, higher_comm,<BR> <EM>'Total Wages: '</EM> || TO_CHAR (total_wages));<BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>五、使用游标变量</P>
<P>跟游标一样，游标变量也是指向多行查询的结果集中的当前行。但是，游标与游标变量不同之处就和常量与变量不同之处类似。游标是静态的，而游标变量是动态的，因为游标变量并不与某个特定的查询相绑定。所以，游标变量可以打开任何类型兼容的查询，灵活性很大。
</P>
<P>并且，我们还可以为游标变量赋新值，把它作为参数传递给本地和存储子程序。这就很容易地让我们把数据检索集中化处理。
</P>
<P>游标变量可以在每个PL/SQL客户端使用。例如，我们可以在OCI或Pro*C这样的主环境中声明游标变量，然后把它作为输入主变量(绑定变量)
传给PL/SQL。并且，像Oracle Forms和Oracle
Reports这样的含有PL/SQL引擎的开发工具，完全可以在客户端使用游标变量。Oracle服务器也有一个PL/SQL引擎。所以，我们可以在应用程序和服务器之间通过远程调用(RPC)来回传递游标变量。
</P>
<P class=title2>1、什么是游标变量</P>
<P>游标变量同C或Pascal语言中的指针类似，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体的内容。在PL/SQL中，指针是一个REF
X类型，REF是REFERENCE的缩写，而X代表对象的类型。因此，游标变量的数据类型是REF CURSOR。
</P>
<P>为了执行多行查询，Oracle会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声明指向工作区的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。所以，游标和游标变量不能交互使用；也就是说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。
</P>
<P class=title2>2、为什么使用游标变量</P>
<P>我们主要是在PL/SQL存储子程序和各种客户端之间用游标变量来传递查询结果。PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指向存放结果集工作区的指针而已。例如，一个OCI客户端，一个Oracle
Forms应用程序和Oracle服务器可以引用同一个工作区。 </P>
<P>只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。例如，我们把主游标变量传递到嵌套在Pro*C程序中的PL/SQL块，游标变量指向的工作区就可以被访问。
</P>
<P>如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。
</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -