📄 第九章 pl-sql包 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> trans_data <STRONG>AS</STRONG> <EM>-- bodiless package</EM><BR> <STRONG>TYPE</STRONG> timerec <STRONG>IS</STRONG> <STRONG>RECORD</STRONG>(<BR> minutes <STRONG>SMALLINT</STRONG>,<BR> hours <STRONG>SMALLINT</STRONG><BR> );<BR><BR> <STRONG>TYPE</STRONG> transrec <STRONG>IS</STRONG> <STRONG>RECORD</STRONG>(<BR> CATEGORY <STRONG>VARCHAR2</STRONG>,<BR> ACCOUNT INT,<BR> amount <STRONG>REAL</STRONG>,<BR> time_of timerec<BR> );<BR><BR> minimum_balance <STRONG>CONSTANT</STRONG> <STRONG>REAL</STRONG> := 10.00;<BR> number_processed INT;<BR> insufficient_funds <STRONG>EXCEPTION</STRONG>;<BR><STRONG>END</STRONG> trans_data;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>包trans_data不需要包体，因为类型、常量、变量和异常并没有实现部分。这样的包能让我们定义全局变量，可供子程序和数据库触发器使用。
</P>
<P class=title2>1、引用包的内容</P>
<P>如果要引用包内声明的类型、常量、变量、异常和子程序等，就得使用点标识： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>package_name.type_name<BR>package_name.item_name<BR>package_name.subprogram_name<BR>package_name.call_spec_name
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以从数据库触发器、存储子程序、3GL(第三代程序语言)应用程序和各种Oracle工具中引用包中的内容。例如，我们可以从SQL*Plus中调用过程hire_employee：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SQL</STRONG>> CALL emp_actions.hire_employee(<EM>'TATE'</EM>, <EM>'CLERK'</EM>, ...);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下例中，我们可以从一个嵌入到Pro*C的匿名PL/SQL块调用同样的过程。实参emp_name和job_title是主变量(即声明在主环境中的变量)。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>EXEC <STRONG>SQL</STRONG> <STRONG>EXECUTE</STRONG><BR><STRONG>BEGIN</STRONG><BR> emp_actions.hire_employee(:emp_name, :job_title, ...);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>约束 </LI></UL>
<P>我们不能直接或间接地引用远程打包变量。例如，我们不能远程调用下面的过程，因为它在参数初始化子句中引用了打包变量：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> random <STRONG>AS</STRONG><BR> seed <STRONG>NUMBER</STRONG>;<BR> <STRONG>PROCEDURE</STRONG> initialize (starter <STRONG>IN</STRONG> <STRONG>NUMBER</STRONG> := seed, ...);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>同样，我们也不能在包的内部引用主变量。 </P>
<P class=title1>四、理解包体</P>
<P>包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完全一致。否则，
PL/SQL就会抛出异常，如下例所示： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> emp_actions <STRONG>AS</STRONG><BR> ...<BR> <STRONG>PROCEDURE</STRONG> calc_bonus(date_hired emp.hiredate%<STRONG>TYPE</STRONG>, ...);<BR><STRONG>END</STRONG> emp_actions;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> emp_actions <STRONG>AS</STRONG><BR> ...<BR> <STRONG>PROCEDURE</STRONG> calc_bonus(date_hired <STRONG>DATE</STRONG>, ...) <STRONG>IS</STRONG><BR> <EM>-- parameter declaration raises an exception because 'DATE'</EM><BR> <EM>-- does not match 'emp.hiredate%TYPE' word for word</EM><BR> <STRONG>BEGIN</STRONG> ... <STRONG>END</STRONG>;<BR><STRONG>END</STRONG> emp_actions;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。
</P>
<P>在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。 </P>
<P>包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。
</P>
<P>请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的内容。
</P>
<P class=title1>五、包特性的例子</P>
<P>下面是一个名为emp_actions的包。包说明声明了类型、游标、异常和子程序： </P>
<OL>
<LI>类型EmpRecTyp和DeptRecTyp
<LI>游标desc_salary
<LI>异常invalid_salary
<LI>函数hire_employee和raise_salary
<LI>过程fire_empire和raise_salary </LI></OL>
<P>在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。创建包时，它就会被存放在Oracle数据库中供广泛地调用。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> emp_actions <STRONG>AS</STRONG><BR> <EM>/* Declare externally visible types, cursor, exception. */</EM><BR> <STRONG>TYPE</STRONG> emprectyp <STRONG>IS</STRONG> <STRONG>RECORD</STRONG>(<BR> emp_id INT,<BR> salary <STRONG>REAL</STRONG><BR> );<BR><BR> <STRONG>TYPE</STRONG> deptrectyp <STRONG>IS</STRONG> <STRONG>RECORD</STRONG>(<BR> dept_id INT,<BR> LOCATION <STRONG>VARCHAR2</STRONG><BR> );<BR><BR> <STRONG>CURSOR</STRONG> desc_salary <STRONG>RETURN</STRONG> emprectyp;<BR><BR> invalid_salary <STRONG>EXCEPTION</STRONG>;<BR><BR> <EM>/* Declare externally callable subprograms. */</EM><BR> <STRONG>FUNCTION</STRONG> hire_employee(<BR> ename <STRONG>VARCHAR2</STRONG>,<BR> job <STRONG>VARCHAR2</STRONG>,<BR> mgr <STRONG>REAL</STRONG>,<BR> sal <STRONG>REAL</STRONG>,<BR> comm <STRONG>REAL</STRONG>,<BR> deptno <STRONG>REAL</STRONG><BR> )<BR> <STRONG>RETURN</STRONG> INT;<BR><BR> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id INT);<BR><BR> <STRONG>PROCEDURE</STRONG> raise_salary(emp_id INT, grade INT, amount <STRONG>REAL</STRONG>);<BR><BR> <STRONG>FUNCTION</STRONG> nth_highest_salary(n INT)<BR> <STRONG>RETURN</STRONG> emprectyp;<BR><STRONG>END</STRONG> emp_actions;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> emp_actions <STRONG>AS</STRONG><BR> number_hired INT; <EM>-- visible only in this package</EM><BR><BR> <EM>/* Fully define cursor specified in package. */</EM><BR> <STRONG>CURSOR</STRONG> desc_salary <STRONG>RETURN</STRONG> emprectyp <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno, sal<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>ORDER</STRONG> <STRONG>BY</STRONG> sal <STRONG>DESC</STRONG>;<BR><BR> <EM>/* Fully define subprograms specified in package. */</EM><BR> <STRONG>FUNCTION</STRONG> hire_employee(<BR> ename <STRONG>VARCHAR2</STRONG>,<BR> job <STRONG>VARCHAR2</STRONG>,<BR> mgr <STRONG>REAL</STRONG>,<BR> sal <STRONG>REAL</STRONG>,<BR> comm <STRONG>REAL</STRONG>,<BR> deptno <STRONG>REAL</STRONG><BR> )<BR> <STRONG>RETURN</STRONG> INT <STRONG>IS</STRONG><BR> new_empno INT;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> empno_seq.<STRONG>NEXTVAL</STRONG><BR> <STRONG>INTO</STRONG> new_empno<BR> <STRONG>FROM</STRONG> DUAL;<BR><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp<BR> <STRONG>VALUES</STRONG> (new_empno, ename, job, mgr, <STRONG>SYSDATE</STRONG>, sal, comm, deptno);<BR><BR> number_hired := number_hired + 1;<BR> <STRONG>RETURN</STRONG> new_empno;<BR> <STRONG>END</STRONG> hire_employee;<BR><BR> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id INT) <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> <EM>/* Define local function, available only inside package. */</EM><BR> <STRONG>FUNCTION</STRONG> sal_ok(RANK INT, salary <STRONG>REAL</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> salgrade<BR> <STRONG>WHERE</STRONG> grade = RANK;<BR><BR> <STRONG>RETURN</STRONG> (salary >= min_sal) <STRONG>AND</STRONG>(salary <= max_sal);<BR> <STRONG>END</STRONG> sal_ok;<BR><BR> <STRONG>PROCEDURE</STRONG> raise_salary(emp_id INT, grade INT, amount <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> salary <STRONG>REAL</STRONG>;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> sal<BR> <STRONG>INTO</STRONG> salary<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR><BR> <STRONG>IF</STRONG> sal_ok(grade, salary + amount) <STRONG>THEN</STRONG><BR> <STRONG>UPDATE</STRONG> emp<BR> <STRONG>SET</STRONG> sal = sal + amount<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR> <STRONG>ELSE</STRONG><BR> <STRONG>RAISE</STRONG> invalid_salary;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>END</STRONG> raise_salary;<BR><BR> <STRONG>FUNCTION</STRONG> nth_highest_salary(n INT)<BR> <STRONG>RETURN</STRONG> emprectyp <STRONG>IS</STRONG><BR> emp_rec emprectyp;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> desc_salary;<BR><BR> <STRONG>FOR</STRONG> i <STRONG>IN</STRONG> 1 .. n <STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> desc_salary<BR> <STRONG>INTO</STRONG> emp_rec;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>CLOSE</STRONG> desc_salary;<BR><BR> <STRONG>RETURN</STRONG> emp_rec;<BR> <STRONG>END</STRONG> nth_highest_salary;<BR><STRONG>BEGIN</STRONG> <EM>-- initialization part starts here</EM><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp_audit<BR> <STRONG>VALUES</STRONG> (<STRONG>SYSDATE</STRONG>, <STRONG>USER</STRONG>, <EM>'emp_actions'</EM>);<BR><BR> number_hired := 0;<BR><STRONG>END</STRONG> emp_actions;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表emp_audit。同样，变量number_hired也只被初始化一次。
</P>
<P>每次hire_employee被调用的时候，变量nubmer_hired就会被更新。但是，number_hired所记录的数字是与特定的会话相关的。也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。
</P>
<P>在下面的例子中，我们把一些典型的银行事务进行打包。假设借款与贷款事务都是在营业时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -