第八章 PL/SQL子程序

<h1 class="postTitle">
<a id="ctl04_TitleUrl" class="postTitle2" href="http://www.cnblogs.com/cxd4321/archive/2008/03/19/1113188.html">第八章 PL/SQL子程序</a>
</h1>
<p class="title1">一、什么是子程序</p>
<p>子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。PL/SQL子程序有两种类型，过程和函数。一般地，过程用于执行一个操作，而函数用于计算一个结果值。</p>
<p>与未命名或匿名PL/SQL块一样，子程序也有声明部分，执行部分和一个可选的异常处理部分。声明部分包含类型、游标、常量、变量、异常和嵌套子程
序的声明。这些内容都是本地的，在程序退出时会自动销毁。执行部分包含赋值语句、流程控制语句和Oracle的数据操作语句。异常处理部分包含异常处理程
序。思考下面用于记入借方银行账户的debit_account过程： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
debit_account(acct_id<strong>INTEGER</strong>
,amount<strong>REAL</strong>
)<strong>IS</strong>
<br>
old_balance<strong>REAL</strong>
;<br>
new_balance<strong>REAL</strong>
;<br>
overdrawn<strong>EXCEPTION</strong>
;<br><strong>BEGIN</strong>
<br><strong>SELECT</strong>
bal<br><strong>INTO</strong>
old_balance<br><strong>FROM</strong>
accts<br><strong>WHERE</strong>
acct_no=acct_id;<br><br>
new_balance:=old_balance-amount;<br><br><strong>IF</strong>
new_balance<0<strong>THEN</strong>
<br><strong>RAISE</strong>
overdrawn;<br><strong>ELSE</strong>
<br><strong>UPDATE</strong>
accts<br><strong>SET</strong>
bal=new_balance<br><strong>WHERE</strong>
acct_no=acct_id;<br><strong>END</strong>
<strong>IF</strong>
;<br><strong>EXCEPTION</strong>
<br><strong>WHEN</strong>
overdrawn<strong>THEN</strong>
<br>
...<br><strong>END</strong>
debit_account; </td>
</tr></tbody></table>
</blockquote>
<p>在被调用时，这个过程接受一个银行账号和借贷金额。它使用账号从accts表中查询账目结算信息。然后用借款金额计算新的账目结算。如果计算后的余额比零小，异常就会被抛出；否则，该账号相关信息就会被更新。 </p>
<p class="title1">二、子程序的优点</p>
<p>子程序能提供扩展性，它能够让我们根据自己的需求来编写特定的PL/SQL。比如，我们需要一个能够创建新部门的过程，就可以像下面这样编写代码： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
create_dept(new_dname<strong>VARCHAR2</strong>
,new_loc<strong>VARCHAR2</strong>
)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br><strong>INSERT</strong>
<strong>INTO</strong>
dept<br><strong>VALUES</strong>
(deptno_seq.<strong>NEXTVAL</strong>
,new_dname,new_loc);<br><strong>END</strong>
create_dept; </td>
</tr></tbody></table>
</blockquote>
<p>子程序还能提供模块化，就是说它可以把一个程序定义成多个模块，更易管理。这样，我们就可以用自顶而下的设计(top-down design)和逐步求精(stepwise refinement)的方法来解决问题。</p>
<p>此外，子程序在提高程序的重用性和可维护方面也是很有用的。只要编译成功，子程序就可以放心地用在很多应用程序中。如果它的定义内容发生了改变，受
到影响的只有子程序本身而已，这就简化了维护过程。最后，子程序还有助于逻辑的抽象。使用子程序时，我们需要知道的是它们的功能，而不是它们实现功能的细
节问题。 </p>
<p class="title1">三、理解PL/SQL过程</p>
<p>过程是一个能执行某个特定操作的子程序。我们可以用下面的语法来编写过程： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>[<strong>CREATE</strong>
[<strong>OR</strong>
REPLACE]]<br><strong>PROCEDURE</strong>
procedure_name[(parameter[,parameter]...)]<br>
[<strong>AUTHID</strong>
{DEFINER|CURRENT_USER}]{<strong>IS</strong>
|<strong>AS</strong>
}<br>
[<strong>PRAGMA</strong>
AUTONOMOUS_TRANSACTION;]<br>
[localdeclarations]<br><strong>BEGIN</strong>
<br>
executablestatements<br>
[<strong>EXCEPTION</strong>
<br>
exceptionhandlers]<br><strong>END</strong>
[name]; </td>
</tr></tbody></table>
</blockquote>
<p>parameter的含义如下： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>parameter_name[<strong>IN</strong>
|<strong>OUT</strong>
[<strong>NOCOPY</strong>
]|<strong>IN</strong>
<strong>OUT</strong>
[<strong>NOCOPY</strong>
]]datatype<br>
[{:=|<strong>DEFAULT</strong>
}expression] </td>
</tr></tbody></table>
</blockquote>
<p>CREATE子句能让我们创建保存在数据库中的独立过程。我们可以从SQL*Plus中或是在使用动态SQL的程序中执行CREATE PROCEDURE语句。</p>
<p>AUTHID子句决定了存储过程是按所有者权限(默认)调用还是按当前用户权限执行，也能决定在没有限定修饰词的情况下，对所引用的对象是按所有者模式进行解析还是按当前用户模式进行解析。我们可以指定CURRENT_USER来覆盖掉程序的默认行为。</p>
<p>编译指示AUTONOMOUS_TRANSACTION会告诉PL/SQL编译器把过程标记为自治(独立)。自治事务能让我们把主事务挂起，执行SQL操作，提交或回滚自治事务，然后再恢复主事务。</p>
<p>我们不能对参数的数据类型进行约束，如下例中对acct_id的声明就是不合法的，因为它对CHAR类型进行了长度限制：</p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
reconcile(acct_id<strong>CHAR</strong>
(5))<strong>IS</strong>
...<em>--illegal</em>
</td>
</tr></tbody></table>
</blockquote>
<p>但是，我们可以使用下面的方法间接的对字符的长度进行限制： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>DECLARE</strong>
<br><strong>SUBTYPE</strong>
Char5<strong>IS</strong>
<strong>CHAR</strong>
(5);<br><strong>PROCEDURE</strong>
reconcile(acct_idChar5)<strong>IS</strong>
... </td>
</tr></tbody></table>
</blockquote>
<p>过程有两个部分，过程说明和过程体。说明部分由关键字PROCEDURE开头，以过程名或参数列表结尾。参数声明是可选的。没有参数的过程是不用使用圆括号的。</p>
<p>过程体由关键字IS(或AS)开头，并以END结尾，END后面可以跟上一个可选的过程名。过程体有三个部分：声明、执行和可选的异常处理。</p>
<p>声明部分包括本地声明，它处于IS和BEGIN之间。在匿名PL/SQL块使用的关键字DECLARE在这里不再需要。执行部分包括许多语句，它们
被放到BEGIN和EXCEPTION(或END)之间，并且至少要有一条语句出现在过程的执行部分。NULL语句可以满足这个需求。异常处理部分包含异
常处理程序，它被放在关键字EXCEPTION和END之间。</p>
<p>在下面的过程raise_salary中，我们会根据给定的金额来为雇员加薪： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
raise_salary(emp_id<strong>INTEGER</strong>
,amount<strong>REAL</strong>
)<strong>IS</strong>
<br>
current_salary<strong>REAL</strong>
;<br>
salary_missing<strong>EXCEPTION</strong>
;<br><strong>BEGIN</strong>
<br><strong>SELECT</strong>
sal<br><strong>INTO</strong>
current_salary<br><strong>FROM</strong>
emp<br><strong>WHERE</strong>
empno=emp_id;<br><br><strong>IF</strong>
current_salary<strong>IS</strong>
<strong>NULL</strong>
<strong>THEN</strong>
<br><strong>RAISE</strong>
salary_missing;<br><strong>ELSE</strong>
<br><strong>UPDATE</strong>
emp<br><strong>SET</strong>
sal=sal+amount<br><strong>WHERE</strong>
empno=emp_id;<br><strong>END</strong>
<strong>IF</strong>
;<br><strong>EXCEPTION</strong>
<br><strong>WHEN</strong>
NO_DATA_FOUND<strong>THEN</strong>
<br><strong>INSERT</strong>
<strong>INTO</strong>
emp_audit<br><strong>VALUES</strong>
(emp_id,<em>'Nosuchnumber'</em>
);<br><strong>WHEN</strong>
salary_missing<strong>THEN</strong>
<br><strong>INSERT</strong>
<strong>INTO</strong>
emp_audit<br><strong>VALUES</strong>
(emp_id,<em>'Salaryisnull'</em>
);<br><strong>END</strong>
raise_salary; </td>
</tr></tbody></table>
</blockquote>
<p>在调用时，过程接受雇员编号和薪资调整金额，然后用雇员编号从emp表找出指定雇员的当前工资。如果雇员编号无法找到或是当前工资为空，异常就会被抛出，否则工资就会被更新。</p>
<p>过程可以作为一个PL/SQL语句来调用。例如，我们可以像下面这样调用raise_salary：</p>
<blockquote>
<table border="0"><tbody><tr>
<td>raise_salary(emp_id,amount); </td>
</tr></tbody></table>
</blockquote>
<p class="title1">四、理解PL/SQL函数</p>
<p>函数是一个能够计算结果值的子程序，函数除了有一个RETURN子句之外，其它结构跟过程类似。我们可以用下面的语法来编写(本地)函数： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>[<strong>CREATE</strong>
[<strong>OR</strong>
REPLACE]]<br><strong>FUNCTION</strong>
function_name[(parameter[,parameter]...)]<strong>RETURN</strong>
datatype<br>
[<strong>AUTHID</strong>
{DEFINER|CURRENT_USER}]<br>
[PARALLEL_ENABLE<br>
[{[<strong>CLUSTER</strong>
parameter<strong>BY</strong>
(column_name[,column_name]...)]|<br>
[<strong>ORDER</strong>
parameter<strong>BY</strong>
(column_name[,column_name]...)]}]<br>
[(<strong>PARTITION</strong>
parameter<strong>BY</strong>
<br>
{[{<strong>RANGE</strong>
|HASH}(column_name[,column_name]...)]|<strong>ANY</strong>
}<br>
)]<br>
]<br>
[DETERMINISTIC][PIPELINED[USINGimplementation_type]]<br>
[AGGREGATE[<strong>UPDATE</strong>
VALUE][<strong>WITH</strong>
EXTERNALCONTEXT]<br>
USINGimplementation_type]{<strong>IS</strong>
|<strong>AS</strong>
}<br>
[<strong>PRAGMA</strong>
AUTONOMOUS_TRANSACTION;]<br>
[localdeclarations]<br><strong>BEGIN</strong>
<br>
executablestatements<br>
[<strong>EXCEPTION</strong>
<br>
exceptionhandlers]<br><strong>END</strong>
[name]; </td>
</tr></tbody></table>
</blockquote>
<p>函数的语法结构与过程类似，这里就不再重复。但有几个不同点还是需要注意的。</p>
<p>PARALLEL_ENABLE选项能声明一个在并发DML操作的从属会话(slave
session)中被安全调用的存储函数。主(logon)会话的状态不会被从属会话所共享。每个从属会话都有它自己的状态，这是在会话开始时初始化的。
函数的结果不应依赖于会话(静态)变量的状态。否则结果就可能随着会话而发生变化。 </p>
<p>提示DETERMINISTIC能帮助优化程序避免冗余的函数调用。如果存储函数的调用跟前一次调用时所使用的参数相同，优化程序就直接选出前一次
的计算结果值。函数结果不应该依赖于会话变量或模式对象的状态。否则结果会随着调用而发生变化。只有DETERMINISTIC函数才允许被函数索引或是
参数query_rewrite_enabled为TRUE的实体化视图调用。</p>
<p>我们不能对参数或是函数返回值的类型添加约束，但可以像前面的过程那样使用间接的约束方法。 </p>
<p>思考下面的函数sal_ok，它的作用是检查工资是否超出限定范围： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>FUNCTION</strong>
sal_ok(salary<strong>REAL</strong>
,title<strong>VARCHAR2</strong>
)<br><strong>RETURN</strong>
<strong>BOOLEAN</strong>
<strong>IS</strong>
<br>
min_sal<strong>REAL</strong>
;<br>
max_sal<strong>REAL</strong>
;<br><strong>BEGIN</strong>
<br><strong>SELECT</strong>
losal,hisal<br><strong>INTO</strong>
min_sal,max_sal<br><strong>FROM</strong>
sals<br><strong>WHERE</strong>
job=title;<br><br><strong>RETURN</strong>
(salary>=min_sal)<strong>AND</strong>
(salary<=max_sal);<br><strong>END</strong>
sal_ok; </td>
</tr></tbody></table>
</blockquote>
<p>调用时，函数接受雇员的工资金额和职别，然后使用职别从数据表sals选择工资范围。函数标识符sal_ok由RETURN语句返回的布尔值赋值。如果salary超过限定范围，sal_ok值就是FALSE，否则就是TRUE。 </p>
<p>函数可以作为表达式的一部分而被调用，如下例所示。函数标识符sal_ok就像一个变量一样，它的值是由传递进去的参数所决定的。 </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>IF</strong>
sal_ok(new_sal,new_title)<strong>THEN</strong>
... </td>
</tr></tbody></table>
</blockquote>
<p class="title2">1、使用RETURN语句</p>
<p>RETURN语句能够立即结束当前执行的子程序并把控制权交还给调用者。然后程序继续执行子程序调用之后的语句。(不要把RETURN语句和函数声明中的RETURN子句搞混淆了，声明中的RETURN只是用于指明函数返回值的数据类型。)</p>
<p>子程序能包含几个RETURN语句。最后一个语句不一定非得是RETURN语句。只要执行RETURN语句就能立即结束当前子程序。但是，在一个子程序包含有多个出口点不是一个好习惯。 </p>
<p>在过程中，RETURN语句不能返回值，也不能返回任何表达式。它的作用只是在过程到达正常的过程结尾之前将控制权交给调用者。</p>
<p>但是，在函数中，RETURN语句必须包含一个表达式，该表达式的值会在RETURN语句执行时被计算。计算的结果赋给函数标识符，标识符的作用相当于RETURN说明中的类型的变量。观察下面返回指定银行账户的余额的函数balance： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>FUNCTION</strong>
balance(acct_id<strong>INTEGER</strong>
)<br><strong>RETURN</strong>
<strong>REAL</strong>
<strong>IS</strong>
<br>
acct_bal<strong>REAL</strong>
;<br><strong>BEGIN</strong>
<br><strong>SELECT</strong>
bal<br><strong>INTO</strong>
acct_bal<br><strong>FROM</strong>
accts<br><strong>WHERE</strong>
acct_no=acct_id;<br><br><strong>RETURN</strong>
acct_bal;<br><strong>END</strong>
balance; </td>
</tr></tbody></table>
</blockquote>
<p>下例演示了如何在RETURN语句中使用复杂的表达式：</p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>FUNCTION</strong>
compound(years<strong>NUMBER</strong>
,amount<strong>NUMBER</strong>
,rate<strong>NUMBER</strong>
)<br><strong>RETURN</strong>
<strong>NUMBER</strong>
<strong>IS</strong>
<br><strong>BEGIN</strong>
<br><strong>RETURN</strong>
amount*POWER((rate/100)+1,years);<br><strong>END</strong>
compound; </td>
</tr></tbody></table>
</blockquote>
<p>函数中，至少要有一条执行路径能够到达RETURN语句。否则，在运行时就会得到函数没有返回值的错误。 </p>
<p class="title2">2、控制PL/SQL子程序的副影响</p>
<p>为了能在SQL中被调用，存储函数必须遵守以下"纯度"规则，这些规则能控制函数副作用： </p>
<ol>
<li>当从SELECT语句或是并发的INSERT、UPDATE、DELETE语句中调用函数时，它不能修改任何数据表。
</li>
<li>当从INSERT、UPDATE或DELETE语句中调用函数时，它不能查询或修改这些语句所能影响到的数据表。
</li>
<li>当
从SELECT、INSERT、UPDATE或DELETE语句中调用函数时，它不能执行SQL事务控制语句(如COMMIT)，会话控制语句(如SET
ROLE)或系统控制语句(如ALTER SYSTEM)。同样，它也不能执行数据定义语句(如CREATE)，因为这些语句都是自动提交事务的。 </li>
<li> </li>
</ol>
<p>如果函数内部任何一条SQL语句与上述规则相冲突，我们就会在运行时得到错误(语句被分析的时候)。 </p>
<p>为了检查这些冲突项，我们可以使用编译指示RESTRICT_REFERENCES(编译器指令)。编译指示能判断函数是否读写数据表或包中的变量。例如在下面的函数中，编译指示就能判断出函数credit_ok不写数据库(WNDS)也不读取包(RNPS)： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>CREATE</strong>
<strong>PACKAGE</strong>
loans<strong>AS</strong>
<br>
...<br><strong>FUNCTION</strong>
credit_ok<strong>RETURN</strong>
<strong>BOOLEAN</strong>
;<br><strong>PRAGMA</strong>
RESTRICT_REFERENCES(credit_ok,WNDS,RNPS);<br><strong>END</strong>
loans; </td>
</tr></tbody></table>
</blockquote>
<p>注意：一个静态的INSERT、UPDATE或DELETE语句总是与WNDS相冲突的；如果读取了数据库字段，它也会与RNDS冲突。一个动态INSERT、UPDATE或DELETE语句总与WNDS和RNDS冲突。</p>
<p class="title1">五、声明PL/SQL子程序</p>
<p>我们可以在PL/SQL块、子程序或包中声明子程序。但是，子程序只能在其他内容声明之后再声明。</p>
<p>PL/SQL需要我们先声明标识然后才能引用它们。所以，在使用子程序之前必须要先声明。例如，下面对过程award_bonus的声明就是非法的，因为它在过程calc_rating未声明之前就开始调用它： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>DECLARE</strong>
<br>
...<br><strong>PROCEDURE</strong>
award_bonus<strong>IS</strong>
<br><strong>BEGIN</strong>
<br>
calc_rating(...);<em>--undeclaredidentifier</em>
<br>
...<br><strong>END</strong>
;<br><strong>PROCEDURE</strong>
calc_rating(...)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br>
...<br><strong>END</strong>
; </td>
</tr></tbody></table>
</blockquote>
<p>这种情况下，我们只要简单地把过程calc_rating放到award_bonus之前就可以了。但是简单的做法不一定总是有效的，如：两个过程相互引用或是我们就想按逻辑或字母顺序来定义他们。</p>
<p>我们可以使用"向前声明"来解决这个问题，向前声明由子程序说明和一个分号组成。在下面的例子中，向前声明通知PL/SQL，过程calc_rating的体部分可以在块的后面找到。 </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>DECLARE</strong>
<br><strong>PROCEDURE</strong>
calc_rating(...);<em>--forwarddeclaration</em>
<br>
... </td>
</tr></tbody></table>
</blockquote>
<p>虽然形式参数列表在向前声明中已经出现过了，但它也必须出现在子程序体中。子程序体可以放在向前声明之后的任何地方，但它们必须出现在同一个程序单元中。 </p>
<p class="title1">六、子程序打包</p>
<p>我们可以把逻辑相关的子程序打包后放到数据库中。那样，子程序就能被许多应用程序共享。子程序说明部分放在包说明部分；子程序体放在包体，子程序体对应用程序是不可见的。因此，包能帮助我们隐藏程序的实现细节。如下例： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>CREATE</strong>
<strong>PACKAGE</strong>
emp_actions<strong>AS</strong>
<em>--packagespec</em>
<br><strong>PROCEDURE</strong>
hire_employee(emp_id<strong>INTEGER</strong>
,NAME<strong>VARCHAR2</strong>
,...);<br><br><strong>PROCEDURE</strong>
fire_employee(emp_id<strong>INTEGER</strong>
);<br><br><strong>PROCEDURE</strong>
raise_salary(emp_id<strong>INTEGER</strong>
,amount<strong>REAL</strong>
);<br>
...<br><strong>END</strong>
emp_actions;<br><br><strong>CREATE</strong>
<strong>PACKAGE</strong>
<strong>BODY</strong>
emp_actions<strong>AS</strong>
<em>--packagebody</em>
<br><strong>PROCEDURE</strong>
hire_employee(emp_id<strong>INTEGER</strong>
,NAME<strong>VARCHAR2</strong>
,...)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br>
...<br><strong>INSERT</strong>
<strong>INTO</strong>
emp<br><strong>VALUES</strong>
(emp_id,NAME,...);<br><strong>END</strong>
hire_employee;<br><br><strong>PROCEDURE</strong>
fire_employee(emp_id<strong>INTEGER</strong>
)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br><strong>DELETE</strong>
<strong>FROM</strong>
emp<br><strong>WHERE</strong>
empno=emp_id;<br><strong>END</strong>
fire_employee;<br><br><strong>PROCEDURE</strong>
raise_salary(emp_id<strong>INTEGER</strong>
,amount<strong>REAL</strong>
)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br><strong>UPDATE</strong>
emp<br><strong>SET</strong>
sal=sal+amount<br><strong>WHERE</strong>
empno=emp_id;<br><strong>END</strong>
raise_salary;<br>
...<br><strong>END</strong>
emp_actions; </td>
</tr></tbody></table>
</blockquote>
<p>我们还能够直接在包体内定义子程序而不用在包说明部分编写它们的说明。但是，这样的子程序只能在包内的使用。 </p>
<p class="title1">七、形参VS实参</p>
<p>子程序使用参数来传递信息。调用时子程序参数列表中引用的变量或表达式是实际参数(actual parameter，以下简称实参)。例如，下面的过程列出了两个实参emp_num和amout： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>raise_salary(emp_num,amount); </td>
</tr></tbody></table>
</blockquote>
<p>下面的过程调用演示了用表达式作为实参： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>raise_salary(emp_num,merit+cola); </td>
</tr></tbody></table>
</blockquote>
<p>子程序声明和子程序体中引用的变量是形式参数(formal parameter，以下简称形参)。例如，下面的过程声明了两个形参emp_id和amount： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
raise_salary(emp_id<strong>INTEGER</strong>
,amount<strong>REAL</strong>
)<strong>IS</strong>
<br><strong>BEGIN</strong>
<br><strong>UPDATE</strong>
emp<br><strong>SET</strong>
sal=sal+amount<br><strong>WHERE</strong>
empno=emp_id;<br><strong>END</strong>
raise_salary; </td>
</tr></tbody></table>
</blockquote>
<p>好的编程习惯就是使用不同命名的形参和实参。</p>
<p>调用过程raise_salary时，实参的内容会被赋值到对应的形参上，如果有必要的话，在赋值之前PL/SQL会帮助我们进行类型转换。例如，下面对raise_salary的调用就是有效的： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>raise_salary(emp_num,<em>'2500'</em>
); </td>
</tr></tbody></table>
</blockquote>
<p>实参和它对应的形参必须类型兼容，例如，PL/SQL是不能把数据从DATE类型转到REAL类型的。下面的过程调用就会引起预定义异常VALUE_ERROR，因为PL/SQL不能把第二个实参转成一个数字： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>raise_salary(emp_num,<em>'$2500'</em>
);<em>--notethedollarsign</em>
</td>
</tr></tbody></table>
</blockquote>
<p class="title1">八、位置标示法VS名字标示法</p>
<p>在调用子程序时，我们既可以使用位置标示法又可以使用名字标示法来编写实参。也就是说，我们可以按位置或名称来把实参和形参关联起来。如下例所示： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>DECLARE</strong>
<br>
acct<strong>INTEGER</strong>
;<br>
amt<strong>REAL</strong>
;<br><br><strong>PROCEDURE</strong>
credit_acct(acct_no<strong>INTEGER</strong>
,amount<strong>REAL</strong>
)<strong>IS</strong>
... </td>
</tr></tbody></table>
</blockquote>
<p>我们可以使用四种等价的方法来调用过程credit_acct：</p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>BEGIN</strong>
<br>
credit_acct(acct,amt);<em>--positionalnotation</em>
<br>
credit_acct(amount=>amt,acct_no=>acct);<em>--namednotation</em>
<br>
credit_acct(acct_no=>acct,amount=>amt);<em>--namednotation</em>
<br>
credit_acct(acct,amount=>amt);<em>--mixednotation</em>
</td>
</tr></tbody></table>
</blockquote>
<p class="title2">1、使用位置标示法</p>
<p>第一个过程调用使用了位置标示法。PL/SQL编译器将第一个实参acct和第一个形参acct_no关联，并把第二个实参amt和第二个形参amount关联。 </p>
<p class="title2">2、使用名字标示法</p>
<p>第二个过程调用使用了名字标示法。箭头(=>)作为关联操作符，把左边的实参和右边的形参关联起来。 </p>
<p>第三个过程调用也使用了名字标示法，而且我们可以随意安排参数的位置。所以，我们不需要知道形参的在参数列表中的顺序。 </p>
<p class="title2">3、使用混合标示法</p>
<p>第四个过程调用使用了名字标示法和位置标示法。在这种情况下，位置标示法必须在名字标示法之前，不能反过来使用，像下面这样的调用方法就是不合法的： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>credit_acct(acct_no=>acct,amt);<em>--illegal</em>
</td>
</tr></tbody></table>
</blockquote>
<p class="title1">九、指定子程序参数模式</p>
<p>我们可以使用参数的模式来定义形式参数的行为。一共有三种模式：IN、OUT和IN OUT。但是，最好避免在函数中使用OUT和IN
OUT模式。函数的作用是用来接受零个或多个参数然后返回一个值。用函数返回多个值不是个好习惯。同样，函数应该避免产生负影响，那样会改变那些对子程序
来说是非本地的变量值。 </p>
<p class="title2">1、使用IN模式</p>
<p>IN模式能让我们把值传递给被调用子程序，在子程序中，IN模式参数的作用就像常量一样。因此，它不能被赋值。例如，下面的赋值语句就会引起编译错误： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<strong>PROCEDURE</strong>
debit_account(acct_id<strong>IN</strong>
<strong>INTEGER</strong>
,amount<strong>IN</strong>
<strong>REAL</strong>
)<strong>IS</strong>
<br>
minimum_purchase<strong>CONSTANT</strong>
<strong>REAL</strong>
<strong>DEFAULT</strong>
10.0;<br>
service_charge<strong>CONSTANT</strong>
<strong>REAL</strong>
<strong>DEFAULT</strong>
0.50;<br><strong>BEGIN</strong>
<br><strong>IF</strong>
amount<minimum_purchase<strong>THEN</strong>
<br>
amount:=amount+service_charge;<em>--causescompilationerror</em>
<br><strong>END</strong>
<strong>IF</strong>
;<br>
...<br><strong>END</strong>
debit_account; </td>
</tr></tbody></table>
</blockquote>
<p>IN模式形参对应的实参可以是常量、文字、被初始化的变量或是表达式。与OUT和IN OUT模式的参数不同，我们可以为IN模式的参数初始化一个默认值。 </p>
<p class="title2">2、使用OUT模式</p>
<p>OUT模式的参数能让我们把值返回给子程序的调用者。在子程序中，OUT模式参数的作用就像变量。这也就意味着我们可以把它当作本地变量来使用，例如： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<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 border="0"><tbody><tr>
<td>calc_bonus(7499,salary+commission);<em>--causescompilationerror</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 border="0"><tbody><tr>
<td>
<strong>DECLARE</strong>
<br><strong>SUBTYPE</strong>
counter<strong>IS</strong>
<strong>INTEGER</strong>
<strong>NOT</strong>
<strong>NULL</strong>
;<br><br>
ROWScounter:=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>--raisesVALUE_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" border="0"><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 border="0"><tbody><tr>
<td>
<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 border="0"><tbody><tr>
<td>
<strong>SQL</strong>
><strong>SET</strong>
SERVEROUTPUT<strong>ON</strong>
<br><strong>SQL</strong>
>GETtest.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>
3emp_tabEmpTabTyp:=EmpTabTyp(<strong>NULL</strong>
);<em>--initialize</em>
<br>
4t1<strong>NUMBER</strong>
(5);<br>
5t2<strong>NUMBER</strong>
(5);<br>
6t3<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>
15emp_tab.EXTEND(24999,1);<em>--copyelement1into2..25000</em>
<br>
16get_time(t1);<br>
17do_nothing1(emp_tab);<em>--passINOUTparameter</em>
<br>
18get_time(t2);<br>
19do_nothing2(emp_tab);<em>--passINOUTNOCOPYparameter</em>
<br>
20get_time(t3);<br>
21dbms_output.put_line(<em>'CallDuration(secs)'</em>
);<br>
22dbms_output.put_line(<em>'--------------------'</em>
);<br>
23dbms_output.put_line(<em>'JustINOUT:'</em>
||TO_CHAR(t2-t1));<br>
24dbms_output.put_line(<em>'WithNOCOPY:'</em>
||TO_CHAR(t3-t2));<br>
25*<strong>END</strong>
;<br><strong>SQL</strong>
>/<br>
CallDuration(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>
<li>默
认地，如果子程序异常退出，赋给OUT和IN
OUT参数的值就不会拷贝到对应的实参上，这看起来有点像回滚操作。但是，对于按引用传递的NOCOPY参数来说，我们对形参所作的更改会立即在对应的实
参上体现出来。所以，即使子程序是因异常发生而结束，它所做的变更内容也不会"回滚"。 </li>
<li>目前，RPC协议允许我们只按值传递参数。例如，如果我们把一个含有NOCOPY参数的本地过程传到远程站点，这些参数就不再按引用传递了。 </li>
</ol>
<p>还有，使用NOCOPY会增加参数别名出现的可能性。 </p>
<p class="title2">2、NOCOPY的限制</p>
<p>在以下几种情况中，PL/SQL编译器会忽略NOCOPY提示而直接使用按值传递参数的方法(不发生错误的情况下)： </p>
<ol>
<li>实参是索引表中的一个元素。这个限制并不适用于整个索引表。
</li>
<li>实参是受约束的(如精度或NOT NULL等)。这个约束不会扩展到元素或属性。同样，对长度受限的字符串也不适用。
</li>
<li>实参和形参都是记录，其中一个或两个使用了%ROWTYPE或%TYPE声明，且在记录中对应域的约束不同。
</li>
<li>实参和形参都是记录，实参是作为游标FOR循环的索引而被声明的(隐式声明)，记录之间对应域的约束不同。
</li>
<li>实参传递需要进行隐式地数据类型转换。
</li>
<li>子程序被外部或远程过程调用。 </li>
</ol>
<p class="title1">十一、使用子程序参数的默认值</p>
<p>如下例所示，我们可以为IN模式的参数初始化默认值。这样，我们就可以把不同个数的参数传给子程序，其中既可以使用参数默认值又可以使用我们传入的参数值覆盖掉默认值。并且，我们还可以在不修改每个子程序调用的情况下添加新的参数。 </p>
<blockquote>
<table border="0"><tbody><tr>
<td>
<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</strong>
<br><strong>INSERT</strong>
<strong>INTO</strong>
dept<br><strong>VALUES</strong>
(deptno_seq.<strong>NEXTVAL</strong>
,new_dname,new_loc);<br>
...<br><strong>END</strong>
; </td>
</tr></tbody></table>
</blockquote>
<p>如果实参没有被传入，它所对应的形参就会使用定义时的默认值。下面是对过程create_dept的调用： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>create_dept;<br>
create_dept(<em>'MARKETING'</em>
);<br>
create_dept(<em>'MARKETING'</em>
,<em>'NEWYORK'</em>
); </td>
</tr></tbody></table>
</blockquote>
<p>第一个调用没有传入任何实参，所以子程序会使用两个默认的参数值；而第二个调用只为第一个参数指定了实参，这样，子程序会使用第一个参数传入的值和
第二个参数的默认值；最后一个调用接受两个实参，它们将对应的形参默认值覆盖，子程序就使用两个传入的值。通常我们使用位置标示法覆盖形参的默认值，但
是，我们不能靠省略实参来跳过它们对应的形参。例如，像下面这样把实参值"NEW YORK"和形参new_dname关联的做法是不对的： </p>
<blockquote>
<table border="0"><tbody><tr>
<td>create_dept(<em>'NEWYORK'</em>
);<em>--incorrect</em>
<br>
create_dept(,<em>'NEWYORK'</em>
);<em>--notallowed</em>
</td>
</tr></tbody></table>
</blockquote>
<p>我们也不可以靠放置一个占位符来解决这个问题。例如，下面的调用就是不允许的：</p>
<blockquote>
<table border="0"><tbody><tr>
<td>create_dept(,<em>'NEWYORK'</em>
);<em>--notallowed</em>
</td>
</tr></tbody></table>
</blockquote>
<p>当出现这种情况，我们就需要使用名字标示法： </p>
<p>



create_dept(new_loc=><em>'NEWY
</em>
</p>