📄 第八章 pl-sql子程序 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
PROCEDURE语句。</P>
<P>AUTHID子句决定了存储过程是按所有者权限(默认)调用还是按当前用户权限执行，也能决定在没有限定修饰词的情况下，对所引用的对象是按所有者模式进行解析还是按当前用户模式进行解析。我们可以指定CURRENT_USER来覆盖掉程序的默认行为。</P>
<P>编译指示AUTONOMOUS_TRANSACTION会告诉PL/SQL编译器把过程标记为自治(独立)。自治事务能让我们把主事务挂起，执行SQL操作，提交或回滚自治事务，然后再恢复主事务。</P>
<P>我们不能对参数的数据类型进行约束，如下例中对acct_id的声明就是不合法的，因为它对CHAR类型进行了长度限制：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><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>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>SUBTYPE</STRONG> Char5 <STRONG>IS</STRONG> <STRONG>CHAR</STRONG>(5);<BR> <STRONG>PROCEDURE</STRONG> reconcile (acct_id Char5) <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>
<TBODY>
<TR>
<TD
noWrap><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>'No such number'</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>'Salary is null'</EM>);<BR><STRONG>END</STRONG> raise_salary;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在调用时，过程接受雇员编号和薪资调整金额，然后用雇员编号从emp表找出指定雇员的当前工资。如果雇员编号无法找到或是当前工资为空，异常就会被抛出，否则工资就会被更新。</P>
<P>过程可以作为一个PL/SQL语句来调用。例如，我们可以像下面这样调用raise_salary：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD noWrap>raise_salary(emp_id, amount);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>四、理解PL/SQL函数</P>
<P>函数是一个能够计算结果值的子程序，函数除了有一个RETURN子句之外，其它结构跟过程类似。我们可以用下面的语法来编写(本地)函数：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>[<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 [ USING implementation_type ] ]<BR> [ AGGREGATE [<STRONG>UPDATE</STRONG> VALUE] [<STRONG>WITH</STRONG> EXTERNAL CONTEXT]<BR> USING implementation_type ] {<STRONG>IS</STRONG> | <STRONG>AS</STRONG>}<BR> [ <STRONG>PRAGMA</STRONG> AUTONOMOUS_TRANSACTION; ]<BR> [ local declarations ]<BR><STRONG>BEGIN</STRONG><BR> executable statements<BR>[ <STRONG>EXCEPTION</STRONG><BR> exception handlers ]<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>
<TBODY>
<TR>
<TD
noWrap><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>
<TBODY>
<TR>
<TD
noWrap><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>
<TBODY>
<TR>
<TD
noWrap><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>
<TBODY>
<TR>
<TD
noWrap><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>当从INSERT、UPDATE或DELETE语句中调用函数时，它不能查询或修改这些语句所能影响到的数据表。
<LI>当从SELECT、INSERT、UPDATE或DELETE语句中调用函数时，它不能执行SQL事务控制语句(如COMMIT)，会话控制语句(如SET
ROLE)或系统控制语句(如ALTER
SYSTEM)。同样，它也不能执行数据定义语句(如CREATE)，因为这些语句都是自动提交事务的。
<LI> </LI></OL>
<P>如果函数内部任何一条SQL语句与上述规则相冲突，我们就会在运行时得到错误(语句被分析的时候)。 </P>
<P>为了检查这些冲突项，我们可以使用编译指示RESTRICT_REFERENCES(编译器指令)。编译指示能判断函数是否读写数据表或包中的变量。例如在下面的函数中，编译指示就能判断出函数credit_ok不写数据库(WNDS)也不读取包(RNPS)：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><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>
<TBODY>
<TR>
<TD
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -