Oracle SQL&PLSQL课件：第23章 使用子程序和包.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第,23,章,使用子程序和包,本章要点,掌握存储子程序与数据字典的交互方式及如何从,SQL,语句中调用存储子程序,了解存储子程序和局部子程序的区别,掌握存储子程序在,Oracle8i,和,Oracle9i,中的新特性。,子程序位置,子程序和包可以存储在数据字典中（子程序是用命令,CREATE OR REPLACE,创建）,可以从其他,PL/SQL,语句块中调用已创建的子程序。,子程序可以在语句块的声明段定义，以这种方式定义的子程序叫做局部子程序。,包则必须存储在数据字典中而不能是局部的,。,存储子程序和数据字典,当使用命令,CREATE OR REPLACE,创建子程序时，该子程序就被存储在数据字典中。,除源文本外，子程序以伪码存储。读入时存储在系统全局工作区（,SGA）,的共享池中，以便多个用户同时进行访问。,子程序的信息可以通过各种数据字典视图来访问。,局部子程序,局部子程序,:,是一个在,PL/SQL,语句块的声明段中声明的子程序。,子函数只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。其他语句块不能调用该函数，因为该函数对其他语句块来说是不可见的。,局部子程序（续）,局部子程序也可以声明为存储子程序声明段的一部分,局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明,预先声明,：,局部,PL/SQL,子程序的名称是标识符，必须在被引用前声明。,局部子程序也可以重载,存储子程序和局部子程序的比较,存储子程序,局部子程序,该类子程序以编译后生成的伪代码形式,p-code,存储在数据库中。当调用该类子程序时，不需进行编译即可运行,局部子程序被编译为包含它的语句块的一部分。如果其所在语句块是匿名块并需要多次运行时，则该子程序就必须每次进行编译,存储子程序可以从对该子程序具有,EXECUTE,特权的用户所提交的任何语句块中调用,局部子程序只能从包含子程序的语句块中调用,由于存储子程序与调用块的相互隔离，调用块具有代码少，易于理解的特点。除此之外，子程序和调用块还可以分开维护,局部子程序和调用块同处于一个块内，所以容易引起混淆。如果修改了调用块的话，则该块调用的子程序作为所属块的一部分也要重新编译,可以使用,DBMS_SHARED_POOL.KEEP,包过程来把编译后,p-code,代码存储在共享池中。这种方式可以改善程序性能,局部子程序自身不能存储在共享池中,独立的存储子程序不能进行重载，但同一包内的子程序可以重载,同一块中的局部子程序可以重载,存储子程序和包的注意事项,优势：,可以由多个数据库用户共享,隐含影响：,存储子程序间的依赖性,包状态的处理方法,运行存储子程序和包所需要的特权,子程序的依赖性,依赖：数据字典记录了存储过程或函数所引用的所有,Oracle,对象。该过程或函数就依赖于这些存储的对象。,自动重编译,:,如果一个依赖对象失效，,PL/SQL,引擎将在该对象再次被调用时对其重新进行编译。,包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包体依赖于包头,。,如何确定无效,:,通过,数据字典在不断地跟踪对象间依赖关,系。,包运行时状态,当,第一次实例,化,一个,包时，将从磁盘中读入该包的伪,代码,并将其,放入,系统,全局工作区,SGA的,共享,池中。,包的,运行时,状态，即,打包,的,变量,和游标，则,存放,在用户,全局,区（UGA）的,会话存储区,中。这就,保证,了,每个会话,都将有其,运行时,状态的,副本,。,可串行复用包和非串行复用包：,PL/SQL 2.3,版及更高版本允许程序员对包做可串行复用标志,。,可串行复用包的运行时状态保存在,SGA,而不是,UGA,中，并仅在每次数据库调用期间有效。,语法,：,PRAGMA SERIALLY_REUSABLE；,该语句在包头，包体中使用。,包运行时状态,可串行复用包,非串行复用包,运行时状态保存在,SGA,中，每次数据库调用后都将该运行时状态释放,运行时状态保存在,UGA,中，其生存期与数据库会话相同,所用的最大内存与同时使用该包的用户数成正比,所用的最大内存与当前登录的用户数成正比,特权和存储子程序,存储子程序和包都是数据库字典中的对象，因而，它们属于特殊的数据库用户或模式。如果用户被授予了正确的特权，则它们就可以访问这些对象。,当创建一个存储对象时，考虑到子程序内部的访问可用性，特权和角色也开始起作用。,EXECUTE,特权,为了能够对表进行访问，必须使用,SELECT,，,INSERT,，,UPDATE,和,DELETE,对象特权。,GRANT,语句把这些特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是,EXECUTE,。,在默认情况下，子程序以其拥有者的特权而执行。,存储子程序和角色,子程序使用显式地（而不是通过角色）授权其所有者的特权而执行。,为了使通过角色授权的特权作用在存储子程序和触发器内被使用，每次运行过程时，必须对该特权进行检查。,调用者的权限与定义者的权限,Oracle8i,提供了不同的外部引用解决方案。在调用者的权限子程序中，外部引用是通过调用者而不是所有者的特权设置而执行的。调用者的权限程序是通过使用,AUTHID,子句而创建，该语句只适用于独立子程序、包说明和对象类型说明。,在包内部或对象类型中的独立子程序必须都是调用者子程序或都是定义者子程序，而不能混合。,AUTHID,的语法如下：,CREATE OR REPLACE FUNCTION function_name,parameter_list RETURN return_type,AUTHID CURRENT_USER|DEFINER IS|AS,function_body;,CREATE OR REPLACE PROCEDURE procedure_name,parameter_list,AUTHID CURRENT_USER|DEFINER IS|AS,function_body;,CREATE OR REPLACE PACKAGE package_spec_name,AUTHID CURRENT_USER|DEFINER IS|AS,package_spec;,CREATE OR REPLACE TYPE type_name,AUTHID CURRENT_USER|DEFINER IS|AS OBJECT,type_spec;,在,SQL,语句中使用存储函数,PL/SQL 2.1,版（,Oracle 7,的,7.1,版）及更高版本,从,SQL,语句中调用子程序,根据用户定义函数的使用场合以及运行的数据库版本，函数必须满足不同限制。,函数的使用受纯度等级限制。纯度等级定义了函数读取或修改哪种数据结构。,纯度等级,纯度等级,根据函数纯度等级，它调用将受到如下限制：,任何从,SQL,语句调用的函数不能修改任何数据库表（,WNDS）。,为了实现远程运行（通过数据库连接）或并行运行，函数不能读取或写入包变量的值（,RNPS,和,WNPS）,从,SELECT，VALUES，,或,SET,子句调用的函数可以写入包变量。所有其他子句中的函数都必须具有,WNPS,纯度等级。,函数仅仅与它所调用的子程序具有同样的纯度等级。,不管存储,PL/SQL,函数具有何种纯度等级，都不能从,CREATE TABLE,或,ALTER TABLE,命令的,CHECK,约束子句中调用该存储,PL/SQL,函数。也不能使用该存储,PL/SQL,函数来为一个列指定缺省值,用户函数从,SQL,语句中调用满足的要求,函数必须要存储在数据库中，其形式可以是独立的函数，或作为包的一部分。不能是局部的,函数只能使用,IN,参数，不能使用,IN OUT,或,OUT,形参只能使用数据库类型，而不能使用诸如,BOOLEAN,或,RECORD,之类的,PL/SQL,类型。,函数的返回类型也必须是数据库类型,函数不能使用,COMMIT,或,ROLLBACK,约束当前事务，也不能回滚到函数运行前的断点。,函数也不能发出任何,ALTER SESSION,或,ALTER SYSTEM,命令。,默认参数,从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，如果从,SQL,语句调用函数时，必须指定所有参数。,必须使用定位符（,Positinal,Notation,）,而不能使用命名符（,Name Notation,在,Oracle8i,中从,SQL,语句调用函数,Oracle8i,提供了,TRUST,关键字，用来代替或补充纯度等级参数。若使用了它，那么在编译标识中列出的限制就不会强制执行。,从,Oracle8i,起，从,DML,语句中调用的函数既不可以从正在被该,DML,语句修改的数据库表读取数据，也不能对其进行修改，但该函数可以更新其他表。,包的辅助功能,共享池锁定,包体长度的限制,优化参数,共享池锁定,DBMS_SHARED_POOL,包允许程序员把一个对象锁定在共享池中。,当该对象被锁定后，除非由程序申请对其清除，否则无论共享池有多满，也不管是否有程序访问该对象，该对象将常驻在共享池中。,这种处理方法有利于提高程序的运行效率，因为从系统的磁盘重新装入对象要进行大量读写操作。锁定对象还有助于最大限度地减少共享池的碎片。,共享池锁定,DBMS_SHARED_POOL.KEEP,过程：用来在共享池中锁定对象。包、触发器、序列等可被锁定，独立过程和函数不能所定,DBMS_SHARED_POOL.UNKEEP,过程,DBMS_SHARED_POOL.SIZES,过程：它把共享池的内容输出到屏幕,DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD,过程：停止释放对象,包体长度的限制,编译器对包体长度的限制如下,：,Diana,树中的节点数。在,Oracle8i,之前的版本中，,Diana,节点的最大数目是,32K,，,Oracle8i,以上版本将包体和类型体的限制扩充到了,64,兆字节的容量。,编译器生成的临时中间变量的容量最多,21K,字节。,入口点的数量。一个包体最多可以有,32K,个入口点，入口点可以是过程或函数。,字符串的数量。,PL/SQL,对字符串的限制单位是,2,32,。,优化参数,使用,DETERMINISTIC,和,PARALLEL_ENABLE,关键字，,PL/SQL,编译优化器将会对调用,PL/SQL,函数进行优化。,该关键字要放在函数的返回类型和,IS,或,AS,子句之间,。,CREATE OR REPLACE FUNCTION function_name,parameter_list,RETURN return_type,DETERMINISTIC,PARALLEL_ENABLE,IS|AS,function_body;,本章小结,通过访问各种数据库字典视图来查询子程序的信息。,user_objects,视图包含了当前用户拥有的所有对象的信息。,user_source,视图包含对象的源程序代码。,user_errors,视图则包含编译错误的信息。,预先声明可以解决函数之间相互调用的问题。,子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编译。,在,PL/SQL,中使用的函数可以使用额外的限制，这被称为纯度等级。,共享池是在子程序运行时包含已编译子程序伪代码及其他代码的,SGA,的一部分。,KEEP,过程用来在共享池中锁定对象。,UNKEEP,过程实现从共享池中删除锁定对象。,SIZES,过程用来把共享池的内容输出到屏幕。,PL/SQL,编译优化器使用,DETERMINISTIC,和,PARALLEL_ENABLE,关键字对调用,PL/SQL,函数进行优化。,