📄 第八章 pl-sql子程序 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> calc_bonus(emp_id <STRONG>IN</STRONG> <STRONG>INTEGER</STRONG>, bonus <STRONG>OUT</STRONG> <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> hire_date <STRONG>DATE</STRONG>;<BR> bonus_missing <STRONG>EXCEPTION</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> sal * 0.10, hiredate<BR> <STRONG>INTO</STRONG> bonus, hire_date<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR><BR> <STRONG>IF</STRONG> bonus <STRONG>IS</STRONG> <STRONG>NULL</STRONG> <STRONG>THEN</STRONG><BR> <STRONG>RAISE</STRONG> bonus_missing;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>IF</STRONG> MONTHS_BETWEEN(<STRONG>SYSDATE</STRONG>, hire_date) > 60 <STRONG>THEN</STRONG><BR> bonus := bonus + 500;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> ...<BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> bonus_missing <STRONG>THEN</STRONG><BR> ...<BR><STRONG>END</STRONG> calc_bonus;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>与OUT模式的形参对应的实参必须是变量；它不能是常量或表达式。例如，下面的调用就不合法： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>calc_bonus(7499, salary + commission); <EM>-- causes compilation error</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>一个OUT实参在子程序调用之前是可以有值的。但是，在子程序调用时，这个值就会丢失，除非我们使用了NOCOPY编译器提示或是子程序因未捕获异常而终止。
</P>
<P>与变量一样，OUT模式的形参会被初始化为NULL.所以，一个OUT模式的形参的数据类型是不能有NOT
NULL约束的(包括内置类型NATURALN和POSITIVEN)。否则的话，PL/SQL就会抛出VALUE_ERROR异常，见下例：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>SUBTYPE</STRONG> counter <STRONG>IS</STRONG> <STRONG>INTEGER</STRONG> <STRONG>NOT</STRONG> <STRONG>NULL</STRONG>;<BR><BR> ROWS counter := 0;<BR><BR> <STRONG>PROCEDURE</STRONG> count_emps(n <STRONG>OUT</STRONG> counter) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> COUNT(*)<BR> <STRONG>INTO</STRONG> n<BR> <STRONG>FROM</STRONG> emp;<BR> <STRONG>END</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> count_emps(ROWS); <EM>-- raises VALUE_ERROR</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在子程序退出之前，它必须要显式地为所有的OUT模式形参赋值。否则对应的实参值就为空。如果成功地退出子程序，PL/SQL就会把值赋给实参。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。
</P>
<P class=title2>3、使用IN OUT模式</P>
<P>一个IN
OUT模式的参数能让我们把它的初始值传递给被调用的子程序，然后再把子程序更新后的值传递给调用者。在子程序中，一个IN
OUT模式参数的作用就像一个初始化了的变量。因此，它能够被赋值，而且它的值还可以赋给其他的变量。 </P>
<P>与IN
OUT模式形参对应的实参必须是变量；它不可以是常量或表达式。如果成功地退出子程序，PL/SQL就会为实参赋值。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。
</P>
<P class=title2>4、子程序参数模式总结</P>
<P>下表总结了我们应该知道关于参数模式的所有内容：</P>
<TABLE id=table-list>
<TBODY>
<TR>
<TH id=table-list-head>IN</TH>
<TH id=table-list-head>OUT</TH>
<TH id=table-list-head>IN OUT</TH></TR>
<TR>
<TD vAlign=top>默认</TD>
<TD vAlign=top>必须被指定</TD>
<TD vAlign=top>必须被指定</TD></TR>
<TR>
<TD vAlign=top>向子程序传值</TD>
<TD vAlign=top>向调用者返回值</TD>
<TD vAlign=top>向子程序传递初始值并向调用者返回<BR>更新后的结果值</TD></TR>
<TR>
<TD vAlign=top>形参的作用同常量相同</TD>
<TD vAlign=top>形参的作用同变量相同</TD>
<TD vAlign=top>形参的作用同被初始化过的变量相同</TD></TR>
<TR>
<TD vAlign=top>形参不能被赋值</TD>
<TD vAlign=top>形参必须被赋值</TD>
<TD vAlign=top>形参应该被赋值</TD></TR>
<TR>
<TD vAlign=top>实参可以是常量、被初始化的变量、<BR>文字或表达式</TD>
<TD vAlign=top>形参必须是变量</TD>
<TD vAlign=top>形参必须是变量</TD></TR>
<TR>
<TD vAlign=top>形参按引用传递</TD>
<TD vAlign=top>形参按值传递，除非使用了NOCOPY</TD>
<TD
vAlign=top>形参按值传递，除非使用了NOCOPY</TD></TR></TBODY></TABLE>
<P class=title1>十、使用NOCOPY编译提示传递大型数据结构</P>
<P>假定子程序声明了一个IN模式参数、一个OUT模式参数和一个IN
OUT模式参数。在调用子程序时，IN模式的是按引用传递的，即把指向IN模式的实参指针赋给形参。所以，两个参数引用都指向同一块内存地址,这块内存存放了实参的值。
</P>
<P>默认情况下，OUT和IN
OUT模式的参数都是按值传递的。就是把实参的值拷贝到对应的形参上。然后，如果子程序正常结束，被赋到OUT和IN
OUT形参上的值就会拷贝到对应的实参上。 </P>
<P>当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用NOCOPY提示来让编译器按引用传递OUT和IN
OUT模式的参数。在下面的例子中，我们请求编译器按引用的方式来传递IN OUT参数my_staff： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> Staff <STRONG>IS</STRONG> VARRAY(200) <STRONG>OF</STRONG> Employee;<BR> <STRONG>PROCEDURE</STRONG> reorganize (my_staff <STRONG>IN</STRONG> <STRONG>OUT</STRONG> <STRONG>NOCOPY</STRONG> Staff) <STRONG>IS</STRONG> ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>记住，NOCOPY只是一个提示，而不是指令。所以，编译器也许仍旧会把my_staff按值传递，即使我们已经发出请求了。但是，通常情况下
NOCOPY是可以成功的。下例中，我们把一个含有25000条记录的本地嵌套表中分别传递给两个没有任何功能的过程。没有使用NOCOPY的记录花费
21秒，而使用的花费不到1秒： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SQL</STRONG>> <STRONG>SET</STRONG> SERVEROUTPUT <STRONG>ON</STRONG><BR><STRONG>SQL</STRONG>> GET test.sql<BR>1 <STRONG>DECLARE</STRONG><BR>2 <STRONG>TYPE</STRONG> EmpTabTyp <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp%<STRONG>ROWTYPE</STRONG>;<BR>3 emp_tab EmpTabTyp := EmpTabTyp(<STRONG>NULL</STRONG>); <EM>-- initialize</EM><BR>4 t1 <STRONG>NUMBER</STRONG>(5);<BR>5 t2 <STRONG>NUMBER</STRONG>(5);<BR>6 t3 <STRONG>NUMBER</STRONG>(5);<BR>7 <STRONG>PROCEDURE</STRONG> get_time (t <STRONG>OUT</STRONG> <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR>8 <STRONG>BEGIN</STRONG> <STRONG>SELECT</STRONG> TO_CHAR(<STRONG>SYSDATE</STRONG>,<EM>'SSSSS'</EM>) <STRONG>INTO</STRONG> t <STRONG>FROM</STRONG> dual; <STRONG>END</STRONG>;<BR>9 <STRONG>PROCEDURE</STRONG> do_nothing1 (tab <STRONG>IN</STRONG> <STRONG>OUT</STRONG> EmpTabTyp) <STRONG>IS</STRONG><BR>10 <STRONG>BEGIN</STRONG> <STRONG>NULL</STRONG>; <STRONG>END</STRONG>;<BR>11 <STRONG>PROCEDURE</STRONG> do_nothing2 (tab <STRONG>IN</STRONG> <STRONG>OUT</STRONG> <STRONG>NOCOPY</STRONG> EmpTabTyp) <STRONG>IS</STRONG><BR>12 <STRONG>BEGIN</STRONG> <STRONG>NULL</STRONG>; <STRONG>END</STRONG>;<BR>13 <STRONG>BEGIN</STRONG><BR>14 <STRONG>SELECT</STRONG> * <STRONG>INTO</STRONG> emp_tab(1) <STRONG>FROM</STRONG> emp <STRONG>WHERE</STRONG> empno = 7788;<BR>15 emp_tab.EXTEND(24999, 1); <EM>-- copy element 1 into 2..25000</EM><BR>16 get_time(t1);<BR>17 do_nothing1(emp_tab); <EM>-- pass IN OUT parameter</EM><BR>18 get_time(t2);<BR>19 do_nothing2(emp_tab); <EM>-- pass IN OUT NOCOPY parameter</EM><BR>20 get_time(t3);<BR>21 dbms_output.put_line(<EM>'Call Duration (secs)'</EM>);<BR>22 dbms_output.put_line(<EM>'--------------------'</EM>);<BR>23 dbms_output.put_line(<EM>'Just IN OUT: '</EM> || TO_CHAR(t2 - t1));<BR>24 dbms_output.put_line(<EM>'With NOCOPY: '</EM> || TO_CHAR(t3 - t2));<BR>25* <STRONG>END</STRONG>;<BR><STRONG>SQL</STRONG>> /<BR>Call Duration (secs)<BR><EM>--------------------</EM><BR>Just <STRONG>IN</STRONG> <STRONG>OUT</STRONG>: 21<BR>With <STRONG>NOCOPY</STRONG>: 0
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、权衡NOCOPY所带来的良好性能</P>
<P>NOCOPY能为我们带来良好的性能，但它也能带来以下几个方面的影响： </P>
<OL>
<LI>因为NOCOPY只是一个提示，不是指令，所以编译器可以把NOCOPY参数按值或按引用的方式传递给子程序。所以，如果子程序因发生未捕获异常而退出时，我们就不能再信赖实参中的值了。
<LI>默认地，如果子程序异常退出，赋给OUT和IN
OUT参数的值就不会拷贝到对应的实参上，这看起来有点像回滚操作。但是，对于按引用传递的NOCOPY参数来说，我们对形参所作的更改会立即在对应的实参上体现出来。所以，即使子程序是因异常发生而结束，它所做的变更内容也不会"回滚"。
<LI>目前，RPC协议允许我们只按值传递参数。例如，如果我们把一个含有NOCOPY参数的本地过程传到远程站点，这些参数就不再按引用传递了。
</LI></OL>
<P>还有，使用NOCOPY会增加参数别名出现的可能性。 </P>
<P class=title2>2、NOCOPY的限制</P>
<P>在以下几种情况中，PL/SQL编译器会忽略NOCOPY提示而直接使用按值传递参数的方法(不发生错误的情况下)：
</P>
<OL>
<LI>实参是索引表中的一个元素。这个限制并不适用于整个索引表。
<LI>实参是受约束的(如精度或NOT
NULL等)。这个约束不会扩展到元素或属性。同样，对长度受限的字符串也不适用。
<LI>实参和形参都是记录，其中一个或两个使用了%ROWTYPE或%TYPE声明，且在记录中对应域的约束不同。
<LI>实参和形参都是记录，实参是作为游标FOR循环的索引而被声明的(隐式声明)，记录之间对应域的约束不同。
<LI>实参传递需要进行隐式地数据类型转换。
<LI>子程序被外部或远程过程调用。 </LI></OL>
<P class=title1>十一、使用子程序参数的默认值</P>
<P>如下例所示，我们可以为IN模式的参数初始化默认值。这样，我们就可以把不同个数的参数传给子程序，其中既可以使用参数默认值又可以使用我们传入的参数值覆盖掉默认值。并且，我们还可以在不修改每个子程序调用的情况下添加新的参数。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> create_dept(<BR> new_dname <STRONG>VARCHAR2</STRONG> <STRONG>DEFAULT</STRONG> ’temp’,<BR> new_loc <STRONG>VARCHAR2</STRONG> <STRONG>DEFAULT</STRONG> ’temp’<BR>) <STRONG>IS</STRONG><BR><STRONG>BEGIN</STRON⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -