第23章 使用子程序和包

1、 掌握存储子程序与数据字典的交互方式及如何从SQL语句中调用存储子程序了解存储子程序和局部子程序的区别掌握存储子程序在Oracle8i和Oracle9i中的新特性。l子程序和包可以存储在数据字典中（子程序是用命令CREATE OR REPLACE创建）l可以从其他PL/SQL语句块中调用已创建的子程序。l子程序可以在语句块的声明段定义，以这种方式定义的子程序叫做局部子程序。l包则必须存储在数据字典中，而不能是局部的。l当使用命令CREATE OR REPLACE创建子程序时，该子程序就被存储在数据字典中。l子程序的信息可以通过各种数据字典视图来访问。l局部子程序:是一个在PL/SQL语句块的声

2、明段中声明的子程序。l子函数只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。其他语句块不能调用该函数，因为该函数对其他语句块来说是不可见的。l局部子程序也可以声明为存储子程序声明段的一部分l局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明l预先声明 ：局部PL/SQL子程序的名称是标识符，必须在被引用前声明。l局部子程序也可以重载存储子程序局部子程序该类子程序以编译后生成的伪代码形式p-code存储在数据库中。当调用该类子程序时，不需进行编译即可运行局部子程序被编译为包含它的语句块的一部分。如果其所在语句块是匿名块并需要多次运行时，则该子程序就必须每次进行编译存储

3、子程序可以从对该子程序具有EXECUTE特权的用户所提交的任何语句块中调用局部子程序只能从包含子程序的语句块中调用由于存储子程序与调用块的相互隔离，调用块具有代码少，易于理解的特点。除此之外，子程序和调用块还可以分开维护局部子程序和调用块同处于一个块内，所以容易引起混淆。如果修改了调用块的话，则该块调用的子程序作为所属块的一部分也要重新编译可以使用DBMS_SHARED_POOL.KEEP包过程来把编译后p-code代码存储在共享池中。这种方式可以改善程序性能局部子程序自身不能存储在共享池中独立的存储子程序不能进行重载，但同一包内的子程序可以重载同一块中的局部子程序可以重载优势：l可以由多个数

4、据库用户共享隐含影响：l存储子程序间的依赖性l包状态的处理方法l运行存储子程序l包所需要的特权l依赖：数据字典记录了存储过程或函数所引用的所有Oracle对象。该过程或函数就依赖于这些存储的对象。l自动重编译:如果一个依赖对象失效，PL/SQL引擎将在该对象再次被调用时对其重新进行编译。 l包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包体依赖于包头 。l如何确定无效 :通过数据字典在不断地跟踪对象间依赖关 l当第一次实例化一个包时，将从磁盘中读入该包的伪代码并将其放入系统全局工作区SGA的共享池中。l

5、包的运行时状态，即打包的变量和游标，则存放在用户全局区（UGA）的会话存储区中。这就保证了每个会话都将有其运行时状态的副本。l可串行复用包和非串行复用包：PL/SQL 2.3版及更高版本允许程序员对包做可串行复用标志。 可串行复用包非串行复用包运行时状态保存在SGA中，每次数据库调用后都将该运行时状态释放运行时状态保存在UGA中，其生存期与数据库会话相同所用的最大内存与同时使用该包的用户数成正比所用的最大内存与当前登录的用户数成正比l存储子程序和包都是数据库字典中的对象，因而，它们属于特殊的数据库用户或模式。如果用户被授予了正确的特权，则它们就可以访问这些对象。l当创建一个存储对象时，考虑到子

6、程序内部的访问可用性，特权和角色也开始起作用。l为了能够对表进行访问，必须使用SELECT，INSERT，UPDATE和DELETE对象特权。GRANT语句把这些特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是EXECUTE。 emp EmployeeRaisedSalary 结果将被保存在UserA. Temp_table 图 23-13 UserA 拥有的数据库对象 Raise_Salary Temp_table UserA UserB emp EmployeeRaisedSalary 结果将保存在 UserA.temp_table 图 23-14 UserA 和 UserB

7、 所拥有的 temp_table Raise_Salary temp_table UserA UserB UserB.temp_table 将不会被修改 temp_table l子程序使用显式地（而不是通过角色）授权其所有者的特权而执行。 l为了使通过角色授权的特权作用在存储子程序和触发器内被使用，每次运行过程时，必须对该特权进行检查。 lOracle8i提供了不同的外部引用解决方案。在调用者的权限子程序中，外部引用是通过调用者而不是所有者的特权设置而执行的。调用者的权限程序是通过使用AUTHID子句而创建，该语句只适用于独立子程序、包说明和对象类型说明。l在包内部或对象类型中的独立子程序必须

8、都是调用者子程序或都是定义者子程序，而不能混合。 AUTHID的语法如下：的语法如下：CREATE OR REPLACE FUNCTION function_nameparameter_list RETURN return_typeAUTHID CURRENT_USER | DEFINER IS | ASfunction_body;CREATE OR REPLACE PROCEDURE procedure_nameparameter_listAUTHID CURRENT_USER | DEFINER IS | ASfunction_body;CREATE OR REPLACE PACKAGE

9、package_spec_nameAUTHID CURRENT_USER | DEFINER IS | ASpackage_spec;CREATE OR REPLACE TYPE type_nameAUTHID CURRENT_USER | DEFINER IS | AS OBJECTtype_spec;lPL/SQL 2.1版（Oracle 7的7.1版）及更高版本从SQL语句中调用子程序 l函数的使用受纯度等级限制l从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，如果从SQL语句调用函数时，必须指定所有参数。l必须使用定位符（Positinal Notation

10、）,而不能使用命名符（Name Notation）。 lOracle8i提供了TRUST关键字，用来代替或补充纯度等级参数。l从Oracle8i起，从DML语句中调用的函数既不可以从正在被该DML语句修改的数据库表读取数据，也不能对其进行修改，但该函数可以更新其他表。 l共享池锁定l包体长度的限制 l优化参数lDBMS_SHARED_POOL包允许程序员把一个对象锁定在共享池中。l当该对象被锁定后，除非由程序申请对其清除，否则无论共享池有多满，也不管是否有程序访问该对象，该对象将常驻在共享池中。l这种处理方法有利于提高程序的运行效率，因为从系统的磁盘重新装入对象要进行大量读写操作。锁定对象还有

11、助于最大限度地减少共享池的碎片。 l编译器对包体长度的限制如下 ：lDiana树中的节点数。在Oracle8i之前的版本中，Diana节点的最大数目是32K，Oracle8i以上版本将包体和类型体的限制扩充到了64兆字节的容量。l编译器生成的临时中间变量的容量最多21K字节。l入口点的数量。一个包体最多可以有32K个入口点，入口点可以是过程或函数。l字符串的数量。PL/SQL对字符串的限制单位是232。 l使用DETERMINISTIC和PARALLEL_ENABLE关键字，PL/SQL编译优化器将会对调用PL/SQL函数进行优化。 l该关键字要放在函数的返回类型和IS或AS子句之间 。CRE

12、ATE OR REPLACE FUNCTION function_nameparameter_listRETURN return_typeDETERMINISTICPARALLEL_ENABLEIS|ASfunction_body;l通过访问各种数据库字典视图来查询子程序的信息。user_objects视图包含了当前用户拥有的所有对象的信息。user_source视图包含对象的源程序代码。user_errors视图则包含编译错误的信息。l预先声明可以解决函数之间相互调用的问题。l子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编译。l在PL/