📄 第九章 pl-sql包 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> bank_transactions <STRONG>AS</STRONG><BR> <EM>/* Declare externally visible constant. */</EM><BR> minimum_balance <STRONG>CONSTANT</STRONG> <STRONG>REAL</STRONG> := 100.00;<BR><BR> <EM>/* Declare externally callable procedures. */</EM><BR> <STRONG>PROCEDURE</STRONG> apply_transactions;<BR><BR> <STRONG>PROCEDURE</STRONG> enter_transaction(acct INT, kind <STRONG>CHAR</STRONG>, amount <STRONG>REAL</STRONG>);<BR><STRONG>END</STRONG> bank_transactions;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> bank_transactions <STRONG>AS</STRONG><BR> <EM>/* Declare global variable to hold transaction status. */</EM><BR> new_status <STRONG>VARCHAR2</STRONG>(70) := <EM>'Unknown'</EM>;<BR><BR> <EM>/* Use forward declarations because apply_transactions<BR> calls credit_account and debit_account, which are not<BR> yet declared when the calls are made. */</EM><BR> <STRONG>PROCEDURE</STRONG> credit_account(acct INT, credit <STRONG>REAL</STRONG>);<BR><BR> <STRONG>PROCEDURE</STRONG> debit_account(acct INT, debit <STRONG>REAL</STRONG>);<BR><BR> <EM>/* Fully define procedures specified in package. */</EM><BR> <STRONG>PROCEDURE</STRONG> apply_transactions <STRONG>IS</STRONG><BR> <EM>/* Apply pending transactions in transactions table<BR> to accounts table. Use cursor to fetch rows. */</EM><BR> <STRONG>CURSOR</STRONG> trans_cursor <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> acct_id, kind, amount<BR> <STRONG>FROM</STRONG> transactions<BR> <STRONG>WHERE</STRONG> status = <EM>'Pending'</EM><BR> <STRONG>ORDER</STRONG> <STRONG>BY</STRONG> time_tag<BR> <STRONG>FOR</STRONG> <STRONG>UPDATE</STRONG> <STRONG>OF</STRONG> status; <EM>-- to lock rows</EM><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>FOR</STRONG> trans <STRONG>IN</STRONG> trans_cursor <STRONG>LOOP</STRONG><BR> <STRONG>IF</STRONG> trans.kind = <EM>'D'</EM> <STRONG>THEN</STRONG><BR> debit_account(trans.acct_id, trans.amount);<BR> <STRONG>ELSIF</STRONG> trans.kind = <EM>'C'</EM> <STRONG>THEN</STRONG><BR> credit_account(trans.acct_id, trans.amount);<BR> <STRONG>ELSE</STRONG><BR> new_status := <EM>'Rejected'</EM>;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>UPDATE</STRONG> transactions<BR> <STRONG>SET</STRONG> status = new_status<BR> <STRONG>WHERE</STRONG> <STRONG>CURRENT</STRONG> <STRONG>OF</STRONG> trans_cursor;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR> <STRONG>END</STRONG> apply_transactions;<BR><BR> <STRONG>PROCEDURE</STRONG> enter_transaction(<BR> <EM>/* Add a transaction to transactions table. */</EM><BR> acct INT, kind <STRONG>CHAR</STRONG>, amount <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> transactions<BR> <STRONG>VALUES</STRONG> (acct, kind, amount, <EM>'Pending'</EM>, <STRONG>SYSDATE</STRONG>);<BR> <STRONG>END</STRONG> enter_transaction;<BR><BR> <EM>/* Define local procedures, available only in package. */</EM><BR> <STRONG>PROCEDURE</STRONG> do_journal_entry(<BR> <EM>/* Record transaction in journal. */</EM><BR> acct INT, kind <STRONG>CHAR</STRONG>, new_bal <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> journal<BR> <STRONG>VALUES</STRONG> (acct, kind, new_bal, <STRONG>SYSDATE</STRONG>);<BR><BR> <STRONG>IF</STRONG> kind = <EM>'D'</EM> <STRONG>THEN</STRONG><BR> new_status := <EM>'Debit applied'</EM>;<BR> <STRONG>ELSE</STRONG><BR> new_status := <EM>'Credit applied'</EM>;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>END</STRONG> do_journal_entry;<BR><BR> <STRONG>PROCEDURE</STRONG> credit_account(acct INT, credit <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> <EM>/* Credit account unless account number is bad. */</EM><BR> old_balance <STRONG>REAL</STRONG>;<BR> new_balance <STRONG>REAL</STRONG>;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> balance<BR> <STRONG>INTO</STRONG> old_balance<BR> <STRONG>FROM</STRONG> accounts<BR> <STRONG>WHERE</STRONG> acct_id = acct<BR> <STRONG>FOR</STRONG> <STRONG>UPDATE</STRONG> <STRONG>OF</STRONG> balance; <EM>-- to lock the row</EM><BR><BR> new_balance := old_balance + credit;<BR><BR> <STRONG>UPDATE</STRONG> accounts<BR> <STRONG>SET</STRONG> balance = new_balance<BR> <STRONG>WHERE</STRONG> acct_id = acct;<BR><BR> do_journal_entry(acct, <EM>'C'</EM>, new_balance);<BR> <STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> NO_DATA_FOUND <STRONG>THEN</STRONG><BR> new_status := <EM>'Bad account number'</EM>;<BR> <STRONG>WHEN</STRONG> <STRONG>OTHERS</STRONG> <STRONG>THEN</STRONG><BR> new_status := SUBSTR(<STRONG>SQLERRM</STRONG>, 1, 70);<BR> <STRONG>END</STRONG> credit_account;<BR><BR> <STRONG>PROCEDURE</STRONG> debit_account(acct INT, debit <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> <EM>/* Debit account unless account number is bad or<BR> account has insufficient funds. */</EM><BR> old_balance <STRONG>REAL</STRONG>;<BR> new_balance <STRONG>REAL</STRONG>;<BR> insufficient_funds <STRONG>EXCEPTION</STRONG>;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> balance<BR> <STRONG>INTO</STRONG> old_balance<BR> <STRONG>FROM</STRONG> accounts<BR> <STRONG>WHERE</STRONG> acct_id = acct<BR> <STRONG>FOR</STRONG> <STRONG>UPDATE</STRONG> <STRONG>OF</STRONG> balance; <EM>-- to lock the row</EM><BR><BR> new_balance := old_balance - debit;<BR><BR> <STRONG>IF</STRONG> new_balance >= minimum_balance <STRONG>THEN</STRONG><BR> <STRONG>UPDATE</STRONG> accounts<BR> <STRONG>SET</STRONG> balance = new_balance<BR> <STRONG>WHERE</STRONG> acct_id = acct;<BR><BR> do_journal_entry(acct, <EM>'D'</EM>, new_balance);<BR> <STRONG>ELSE</STRONG><BR> <STRONG>RAISE</STRONG> insufficient_funds;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> NO_DATA_FOUND <STRONG>THEN</STRONG><BR> new_status := <EM>'Bad account number'</EM>;<BR> <STRONG>WHEN</STRONG> insufficient_funds <STRONG>THEN</STRONG><BR> new_status := <EM>'Insufficient funds'</EM>;<BR> <STRONG>WHEN</STRONG> <STRONG>OTHERS</STRONG> <STRONG>THEN</STRONG><BR> new_status := SUBSTR(<STRONG>SQLERRM</STRONG>, 1, 70);<BR> <STRONG>END</STRONG> debit_account;<BR><STRONG>END</STRONG> bank_transactions;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在这个包中，我们没有使用初始化部分。 </P>
<P class=title1>六、包中私有项和公有项</P>
<P>再看一下包emp_actions。包体声明了一个名为number_hired的变量，它被初始化为零。与在包说明中声明的内容不同，包体中的内容只局限于在当前包内使用。因此，在包外的PL/SQL代码就不能引用变量number_hired，这样的内容就是私有的。
</P>
<P>但是，在emp_actions的说明中声明的内容，如异常invalid_salary，它对包外的程序来说就是可见的。因此，任何PL/SQL代码都可以访问它。这样的内容就是公有的。
</P>
<P>当我们必须通过一个会话或跨事务来维护一些内容时，就需要把它们放在包体的声明部分。例如，number_hired的值在同一个会话中会被不断的更新。当会话结束时，这个值就会丢失。
</P>
<P>如果我们必须把某些内容声明为公有的，就要把它们放在包的说明中。例如，声明在包bank_transactions中的常量minimum_balance就可以被广泛地使用。
</P>
<P class=title1>七、重载包级子程序</P>
<P>PL/SQL允许两个或多个包级子程序拥有相同的名称。当我们想让一个子程序接受类型不同的相似的参数集合时，这个功能就很有效。例如，下面的包就定义了两个名为journalize过程：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> journal_entries <STRONG>AS</STRONG><BR> ...<BR> <STRONG>PROCEDURE</STRONG> journalize(amount <STRONG>REAL</STRONG>, trans_date <STRONG>VARCHAR2</STRONG>);<BR><BR> <STRONG>PROCEDURE</STRONG> journalize(amount <STRONG>REAL</STRONG>, trans_date INT);<BR><STRONG>END</STRONG> journal_entries;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> journal_entries <STRONG>AS</STRONG><BR> ...<BR> <STRONG>PROCEDURE</STRONG> journalize(amount <STRONG>REAL</STRONG>, trans_date <STRONG>VARCHAR2</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> journal<BR> <STRONG>VALUES</STRONG> (amount, TO_DATE(trans_date, <EM>'DD-MON-YYYY'</EM>));<BR> <STRONG>END</STRONG> journalize;<BR><BR> <STRONG>PROCEDURE</STRONG> journalize(amount <STRONG>REAL</STRONG>, trans_date INT) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> journal<BR> <STRONG>VALUES</STRONG> (amount, TO_DATE(trans_date, <EM>'J'</EM>));<BR> <STRONG>END</STRONG> journalize;<BR><STRONG>END</STRONG> journal_entries;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>第一个过程接受一个字符串trans_date，而第二个过程接受一个数字类型trans_date。每个过程都对数据进行了适当的处理操作。
</P>
<P class=title1>八、包STANDARD是如何定义PL/SQL环境的</P>
<P>STANDARD包定义了PL/SQL环境。这个包的说明部分定义了全局类型、异常和子程序，这些内容对PL/SQL程序来说都是可用的。例如，STANDARD包声明了能返回一个数字的绝对值的ABS函数：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FUNCTION</STRONG> ABS (n <STRONG>NUMBER</STRONG>) <STRONG>RETURN</STRONG> <STRONG>NUMBER</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>包STANDARD的内容对应用程序是可见的。我们不需要在引用这些内容的前面加限定修饰词。例如，我们可以从数据库触发器、存储子程序、Oracle工具或3GL应用程序中直接调用ABS：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>abs_diff := ABS(x - y);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>如果我们在PL/SQL中重新声明了ABS，我们的本地声明就会覆盖掉全局声明。但是，我们仍然可以通过添加限定修饰词来引用内部函数ABS：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>abs_diff := STANDARD.ABS(x - y);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>大多数的内部函数都被重载了。例如，包STANDARD包含了下面几种声明：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FUNCTION</STRONG> TO_CHAR (right <STRONG>DATE</STRONG>) <STRONG>RETURN</STRONG> <STRONG>VARCHAR2</STRONG>;<BR><STRONG>FUNCTION</STRONG> TO_CHAR (left <STRONG>NUMBER</STRONG>) <STRONG>RETURN</STRONG> <STRONG>VARCHAR2</STRONG>;<BR><STRONG>FUNCTION</STRONG> TO_CHAR (left <STRONG>DATE</STRONG>, right <STRONG>VARCHAR2</STRONG>) <STRONG>RETURN</STRONG> <STRONG>VARCHAR2</STRONG>;<BR><STRONG>FUNCTION</STRONG> TO_CHAR (left <STRONG>NUMBER</STRONG>, right <STRONG>VARCHAR2</STRONG>) <STRONG>RETURN</STRONG> <STRONG>VARCHAR2</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>PL/SQL会按照形参与实参的个数和类型来解析对函数TO_CHAR的调用。 </P>
<P class=title1>九、系统包一览</P>
<P>Oracle和各种Oracle工具都提供了系统包来帮助我们建立基于PL/SQL的应用程序。例如，Oracle提供了许多工具包，下面介绍一下其中比较典型的包。
</P>
<P class=title2>1、关于DBMS_ALERT包</P>
<P>DBMS_ALERT能让数据库触发器在特定的数据库值发生变化时向应用程序发送报警。报警是基于事务的并且是异步的(也就是它们的操作与定时机制无关)。例如，当新的股票和债券上市时公司就可以通过这个包更新来他的投资总额。
</P>
<P class=title2>2、关于DBMS_OUTPUT包</P>
<P>包DBMS_OUTPUT能让我们显示来自PL/SQL块和子程序中的输出内容，这样就会很容易地进行测试和调试。过程put_line能把信息输出到SGA的一个缓存中。我们可以通过调用过程get_line或在SQL*Plus中设置SERVEROUTPUT
ON就能显示这些信息。假设我们创建了下面的存储过程： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> calc_payroll(payroll <STRONG>OUT</STRONG> <STRONG>NUMBER</STRONG>) <STRONG>AS</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> sal, comm<BR> <STRONG>FROM</STRONG> emp;<BR><STRONG>BEGIN</STRONG><BR> payroll := 0;<BR><BR> <STRONG>FOR</STRONG> c1rec <STRONG>IN</STRONG> c1 <STRONG>LOOP</STRONG><BR> c1rec.comm := NVL(c1rec.comm, 0);<BR> payroll := payroll + c1rec.sal + c1rec.comm;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <EM>/* Display debug info. */</EM><BR> DBMS_OUTPUT.put_line(<EM>'Value of payroll: '</EM> || TO_CHAR(payroll));<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>使用下面的命令时，SQL*Plus就能显示出payroll的值： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SQL</STRONG>> <STRONG>SET</STRONG> SERVEROUTPUT <STRONG>ON</STRONG><BR><STRONG>SQL</STRONG>> VARIABLE num <STRONG>NUMBER</STRONG><BR><STRONG>SQL</STRONG>> CALL calc_payroll(:num);<BR>Value of payroll: 31225
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、关于DBMS_PIPE包</P>
<P>包DBMS_PIPE允许不同的会话通过命名管道来进行通信(管道就是一块内存区域，进程使用这个区域把消息传递给另外一个进程)。我们可以使用过程pack_message和send_message把消息封装到一个管道，然后把消息发送到同一个实例中的另一个会话中。
</P>
<P>管道的另一个终端，我们可以使用过程recieve_message和unpack_message来接受并打开要读取的消息。命名管道在很多地方都很有用。例如，我们可以用C语言编写一个收集信息的程序，然后把信息通过管道传递给存储过程。</P>
<P class=title2>4、关于UTL_FILE包</P>
<P>包UTL_FILE能让我们的PL/SQL程序读写操作系统(OS)文本文件。它提供了标准的OS流文件I/O，包括open、put、get和close操作。
</P>
<P>当我们想要读写文件的时候，我们可以⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -