过程函数程序包.doc

1、过程、函数、程序包涉及到的资料使用 NOCOPY 编译提示传递大型数据结构假定子程序声明了一个 IN 模式参数、一个 OUT 模式参数和一个 IN OUT 模式参数。在调用子程序时，IN 模式的是按引用传递的，即把指向 IN 模式的实参指针赋给形参。所以，两个参数引用都指向同一块内存地址,这块内存存放了实参的值。 默认情况下，OUT 和 IN OUT 模式的参数都是按值传递的。就是把实参的值拷贝到对应的形参上。然后，如果子程序正常结束，被赋到OUT 和 IN OUT 形参上的值就会拷贝到对应的实参上。 当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消

2、耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY 提示来让编译器按引用传递 OUT 和 IN OUT 模式的参数。在下面的例子中，我们请求编译器按引用的方式来传递 IN OUT 参数 my_staff： DECLARETYPE Staff IS VARRAY(200) OF Employee;PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS . 记住，NOCOPY 只是一个提示，而不是指令。所以，编译器也许仍旧会把 my_staff 按值传递，即使我们已经发出请求了。但是，通常情况下 NOCOPY 是可以成功的。下例中，

3、我们把一个含有 25000 条记录的本地嵌套表中分别传递给两个没有任何功能的过程。没有使用 NOCOPY 的记录花费 21 秒，而使用的花费不到 1 秒： SQL SET SERVEROUTPUT ONSQL GET test.sql1 DECLARE2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;3 emp_tab EmpTabTyp := EmpTabTyp(NULL); - initialize4 t1 NUMBER(5);5 t2 NUMBER(5);6 t3 NUMBER(5);7 PROCEDURE get_time (t OUT NUMBER)

4、IS8 BEGIN SELECT TO_CHAR(SYSDATE,SSSSS) INTO t FROM dual; END;9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS10 BEGIN NULL; END;11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS12 BEGIN NULL; END;13 BEGIN14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;15 emp_tab.EXTEND(24999, 1); -

5、 copy element 1 into 22500016 get_time(t1);17 do_nothing1(emp_tab); - pass IN OUT parameter18 get_time(t2);19 do_nothing2(emp_tab); - pass IN OUT NOCOPY parameter20 get_time(t3);21 dbms_output.put_line(Call Duration (secs);22 dbms_output.put_line(-);23 dbms_output.put_line(Just IN OUT: | TO_CHAR(t2

6、- t1);24 dbms_output.put_line(With NOCOPY: | TO_CHAR(t3 - t2);25* END;SQL /Call Duration (secs)-Just IN OUT: 21With NOCOPY: 0 1、权衡 NOCOPY 所带来的良好性能NOCOPY 能为我们带来良好的性能，但它也能带来以下几个方面的影响： 1. 因为 NOCOPY 只是一个提示，不是指令，所以编译器可以把 NOCOPY 参数按值或按引用的方式传递给子程序。所以，如果子程序因发生未捕获异常而退出时，我们就不能再信赖实参中的值了。 2. 默认地，如果子程序异常退出，赋给 OU

7、T 和 IN OUT 参数的值就不会拷贝到对应的实参上，这看起来有点像回滚操作。但是，对于按引用传递的 NOCOPY 参数来说，我们对形参所作的更改会立即在对应的实参上体现出来。所以，即使子程序是因异常发生而结束，它所做的变更内容也不会“回滚“。 3. 目前，RPC 协议允许我们只按值传递参数。例如，如果我们把一个含有 NOCOPY 参数的本地过程传到远程站点，这些参数就不再按引用传递了。 还有，使用 NOCOPY 会增加参数别名出现的可能性。 2、NOCOPY 的限制在以下几种情况中，PL/SQL 编译器会忽略 NOCOPY 提示而直接使用按值传递参数的方法(不发生错误的情况下)： 1. 实

8、参是索引表中的一个元素。这个限制并不适用于整个索引表。 2. 实参是受约束的(如精度或 NOT NULL 等)。这个约束不会扩展到元素或属性。同样，对长度受限的字符串也不适用。 3. 实参和形参都是记录，其中一个或两个使用了%ROWTYPE 或%TYPE 声明，且在记录中对应域的约束不同。 4. 实参和形参都是记录，实参是作为游标 FOR 循环的索引而被声明的(隐式声明)，记录之间对应域的约束不同。 5. 实参传递需要进行隐式地数据类型转换。 6. 子程序被外部或远程过程调用Authid 参数authid 代表两种权限： 定义者权限（difiner right 默认），执行者权限（invoke

9、r right）。定义者权限说明这个 procedure 中涉及的表，视图等对象所需要的权限只要定义者拥有权限的话就可以访问。执行者权限则需要调用这个 procedure 的用户拥有相关表和对象的权限。Pragma autonomous_transaction 参数在触发器中操作触发此触发器的表,用 PRAGMA AUTONOMOUS_TRANSACTION 选项。1、为何使用自治事务 无法回滚的审计一般情况下利用触发器禁止某些对表的更新等操作时，若记录日志，则触发器最后抛出异常时会造成日志回滚。利用自治事务可防止此点。避免变异表即在触发器中操作触发此触发器的表在触发器中使用 DDL 写数据库

10、对数据库有写操作（INSERT、UPDATE、DELETE 、CREATE、ALTER 、COMMIT）的存储过程或函数是无法简单的用 SQL 来调用的，此时可以将其设为自治事务，从而避免 ORA-14552（无法在一个查询或 DML 中执行 DDL、COMMIT、ROLLBACK） 、ORA-14551（无法在一个查询中执行 DML 操作）等错误。需要注意的是函数必须有返回值，但仅有 IN 参数（不能有 OUT 或 IN/OUT 参数） 。开发更模块化的代码在大型开发中，自治事务可以将代码更加模块化，失败或成功时不会影响调用者的其它操作，代价是调用者失去了对此模块的控制，并且模块内部无法引用

11、调用者未提交的数据。2、 如何工作 事务控制DECLARE 整个块都是属于父事务的，自治事务从离 PRAGMA 后的第一个 BEGIN 开始，只要此 BEGIN 块仍在作用域，则都属于自治事务。例如在 DECLARE 模块中声明一个写数据库的函数，则此函数虽然在自治事务所在存储过程执行，但其属于父事务；而自治事务中调用的任何函数和存储过程、激发的任何触发器等均为此自治事务的一部分。自治事务可以嵌套，嵌套深度等只受 INIT.ORA 参数 TRANSACTIONS（同时并发的事务数，缺省为 SESSIONS 的 1.1 倍）制约。作用域(1)包中的变量自治事务可看到并修改父事务的变量，父事务也会

12、察觉到这一改变，且不存在回滚问题。(2)会话设置/参数自治事务与父事务共享同一个会话环境，通过 ALTER SESSION 作的修改对整个会话均有效。但 SET TRANSACTION 是事务级的，仅对提起修改的事务有效。(3)数据库修改父事务已提交的修改对自治事务可见，未提交的对自治事务不可见，自治事务的修改对父事务是否可见取决于隔离级别（Isolation Level） 。对于游标，取决于其打开的位置，若其在父事务中打开，则之前父事务未提交的修改对其是有效的，在自治事务中这些修改也可见；而在自治事务中打开，则父事务未提交的修改不可见。若使用缺省的 READ COMMITTED 隔离级别，则

13、自治事务的修改对父事务可见；若改用 SERIALIZABLE，则不可见。(4)锁父事务与自治事务是完全不同的事务，因此无法共享锁等。结束一个自治事务必须提交一个 COMMIT、ROLLBACK 或执行 DDL。保存点无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。3、 最后说明 不支持分布式事务截至 8.1.7 在自治事务中不支持分布式事务仅可用 PL/SQL 全部事务回滚若自治事务出错，则全部回滚，即便父事务有异常处理模块。事务级临时表每个会话仅一个事务可访问事务级临时表（多个会话中的事务可并发操作） 。4、可能遇到的错误ORA-06519 检查到活动自治事务，回滚退出自治

14、事务时没有提交、回滚或 DDL 操作ORA-14450 试图访问正在使用的事务级临时表ORA-00060 等待资源时检查到死锁PARALLEL_ENABLE 参数PARALLEL_ENABLE 选项能声明一个在并发 DML 操作的从属会话 (slave session)中被安全调用的存储函数。主(logon)会话的状态不会被从属会话所共享。每个从属会话都有它自己的状态，这是在会话开始时初始化的。函数的结果不应依赖于会话(静态)变量的状态。否则结果就可能随着会话而发生变化。 The PARALLEL_ENABLE option declares that a stored function ca

15、n be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) vari

16、ables. Otherwise, results might vary across sessions.DETERMINISTIC 参数提示 DETERMINISTIC 能帮助优化程序避免冗余的函数调用。如果存储函数的调用跟前一次调用时所使用的参数相同，优化程序就直接选出前一次的计算结果值。函数结果不应该依赖于会话变量或模式对象的状态。否则结果会随着调用而发生变化。只有DETERMINISTIC 函数才允许被函数索引或是参数 query_rewrite_enabled 为 TRUE 的实体化视图调用。 The hint DETERMINISTIC helps the optimizer av

17、oid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINIS

18、TIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.Pipelined 参数很多人都知道，在普通的函数中，使用 dbms_output 输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。但你如果需要在客户端实时的输出函数执行过程中的一些信息，在 Oracle 9i 以后则可以使用管道函数(pipeline function)。 PIPELINED（关键字）表明这是一个管道函数，管道函数的返回值类型必须为集合，在函数中

19、，PIPE ROW 语句被用来返回该集合的单个元素，函数则以一个空的 RETURN 语句结束，以表明它已经完成。 create or replace type MsgType as table of varchar2(4000);/create or replace function f_pipeline_testreturn MsgTypePIPELINEDasbeginfor i in 1 10looppipe row( Iteration | i | at | systimestamp );dbms_lock.sleep(1);end loop;pipe row( All done! )

20、;return;end;/在 sql*plus 中执行该函数，大家需要首先设置 arraysize 为 1，否则服务器会按照默认的 15 来向客户端返回信息，这会影响我们的测试效果。 SQL set arraysize 1SQL select * from table( f_pipeline_test );COLUMN_VALUE-Iteration 1 at 14-FEB-08 02.13.18.273988000 PM +08:00Iteration 2 at 14-FEB-08 02.13.19.275988000 PM +08:00Iteration 3 at 14-FEB-08 02

21、.13.20.277767000 PM +08:00Iteration 4 at 14-FEB-08 02.13.21.279591000 PM +08:00Iteration 5 at 14-FEB-08 02.13.22.281366000 PM +08:00Iteration 6 at 14-FEB-08 02.13.23.283189000 PM +08:00Iteration 7 at 14-FEB-08 02.13.24.283965000 PM +08:00Iteration 8 at 14-FEB-08 02.13.25.285785000 PM +08:00Iteration

22、 9 at 14-FEB-08 02.13.26.286570000 PM +08:00Iteration 10 at 14-FEB-08 02.13.27.288387000 PM +08:00All done!11 rows selected. 如果要在 pipeline 中执行 DML 操作，则必须使用自治事务，否则会报 ORA-14551 错误 create or replace function f_pipeline_testdmlreturn MsgTypePIPELINEDasbeginfor i in 1 10loopinsert into test values(1);pip

23、e row( insert into test values( | i | ) success at | systimestamp );dbms_lock.sleep(1);end loop;pipe row( All done! );return;end;/SQL select * from table( f_pipeline_testdml );select * from table( f_pipeline_testdml )*ERROR at line 1:ORA-14551: cannot perform a DML operation inside a queryORA-06512:

24、 at “NING.F_PIPELINE_TESTDML“, line 8create or replace function f_pipeline_testdmlreturn MsgTypePIPELINEDaspragma autonomous_transaction;beginfor i in 1 10loopinsert into test values(1);commit;pipe row( insert values | i | success at | systimestamp );dbms_lock.sleep(1);end loop;pipe row( All done! )

25、;return;end;/SQL select * from table( f_pipeline_testdml );COLUMN_VALUE-insert values 1 success at 14-FEB-08 02.16.47.855158000 PM +08:00insert values 2 success at 14-FEB-08 02.16.48.865559000 PM +08:00insert values 3 success at 14-FEB-08 02.16.49.867377000 PM +08:00insert values 4 success at 14-FEB

26、-08 02.16.50.873154000 PM +08:00insert values 5 success at 14-FEB-08 02.16.51.874942000 PM +08:00insert values 6 success at 14-FEB-08 02.16.52.880781000 PM +08:00insert values 7 success at 14-FEB-08 02.16.53.882543000 PM +08:00insert values 8 success at 14-FEB-08 02.16.54.894348000 PM +08:00insert v

27、alues 9 success at 14-FEB-08 02.16.55.896153000 PM +08:00insert values 10 success at 14-FEB-08 02.16.56.901904000 PM +08:00All done!11 rows selected.在 Oracle 9205 及其之后的版本中，在 pipeline function 中使用自治事务，则必须在 pipe row 之前提交或者回滚事务，否则会报 ORA-06519 错误。 create or replace function f_pipeline_testdmlreturn MsgT

28、ypePIPELINEDaspragma autonomous_transaction;beginfor i in 1 10loopinsert into test values(1);pipe row( insert values | i | success at | systimestamp );dbms_lock.sleep(1);end loop;pipe row( All done! );commit;return;end;/SQL select * from table( f_pipeline_testdml );select * from table( f_pipeline_te

29、stdml )*ERROR at line 1:ORA-06519: active autonomous transaction detected and rolled backORA-06512: at “NING.F_PIPELINE_TESTDML“, line 10此处是由于在 9205 中修复 Bug 2711518 导致了自治事务的行为有所改变。如果系统从 9205 之前的版本升级到之后的版本，需要保证 pipeline function 的行为和以前版本一致，Oracle 提供了一个 10946 事件来设置和以前版本的兼容性，如果在管道函数中使用了 select for upda

30、te 的 cursor，则必须设置 event 回归以前的特性，否则即使在 pipe row 之前 commit 也会导致出现 ORA-1002错误。 ALTER SYSTEM SET EVENT = “10946 trace name context forever, level 8“ scope=spfile;12.1.4 对象类型对象类型包含属性和方法。-这里只举出一个简单的例子，另章详细讲解create or replace type Point as object(x number,y number,member function ToString return varchar2,p

31、ragma restrict_references(ToString, rnds, wnds, rnps, wnps),member function Distance(p in Point default Point(0, 0) return number,pragma restrict_references(Distance, rnds, wnds, rnps, wnps),member function Plus(p in Point) return Point,pragma restrict_references(Plus, rnds, wnds, rnps, wnps),member

32、 function Times(n in number) return Point,pragma restrict_references(Times, rnds, wnds, rnps, wnps);/create or replace type body Point asmember function ToString return varchar2 isv_Result varchar2(20);v_xString varchar2(8) := substr(to_char(x), 1, 8);v_yString varchar2(8) := substr(to_char(y), 1, 8

33、);beginv_Result := ( | v_xString | , ;v_Result := v_Result | v_yString | );return v_Result;end ToString;member function Distance(p in Point default Point(0, 0) return number isbeginreturn sqrt(power(x - p.x, 2) + power(y - p.y, 2);end Distance;member function Plus(p in Point) return Point isv_Result

34、 Point;beginv_Result := Point(x + p.x, y + p.y);return v_Result;end Plus;member function Times(n in number) return Point isv_Result Point;beginv_Result := Point(x * n, y * n);return v_Result;end Times;end;/Point 类型模拟了 x-y 笛卡尔坐标系上的一个点。1 对象类型语法上类似于包，因为它们都有头和体。像包一样，其对象体也依赖于其对象头。2 像上例中的 x 和 y，属性的声明类似于 P

35、L/SQL 变量。像 ToString,Distance,Plus 和 Times 一样，方法的声明也类似于 PL/SQL子程序，只是它们具有关键词 member。3 像 PL/SQL 记录一样，可以使用点符号来指示目标属性。一旦定义了类型，就可以声明该类型的对象，并调用其上的方法。declarev_Point1 Point := Point(1, 2);v_Point2 Point;v_Point3 Point;beginv_Point2 := v_Point1.Times(4);v_Point3 := v_Point1.Plus(v_Point2);dbms_output.put_line

36、(Point 2: | v_Point2.ToString);dbms_output.put_line(Point 3: | v_Point3.ToString);dbms_output.put_line(Distance between origin and point 1: |v_Point1.Distance);dbms_output.put_line(Distance between point 1 and point 2: |v_Point2.Distance(v_Point2);end;-outputPoint 2: (4, 8)Point 3: (5, 10)Distance b

37、etween origin and point 1: 2.23606797749978969640917366873127623544Distance between point 1 and point 2: 012.1.5 大对象另章详细讲解略12.1.6管道化表函数为了让 PL/SQL 函数返回数据的多个行，必须通过返回一个 ref curosr 或一个数据集合来实现。ref cursor 的这种情况局限于从查询中选择的数据，而整个集合在可以返回前必须进行具体化。表函数是返回整个行的集(通常是一个集合)的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函

38、数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快的返回。create type MyType as object(field1 number,field2 varchar2(50);create type MyTypeList as table of MyType;create or replace function PipelineMe return MyTypeListpipelined asv_MyType MyType;beginfor v_Count in 1 20 loopv_MyType := MyType(v_Count,

39、 Row | v_Count);pipe row(v_MyType);end loop;return;end PipelineMe;管道化表函数必须返回一个集合。在函数中，pipe row 语句被用来返回该集合的单个元素，该函数必须以一个空的 return 语句结束，以表明它已经完成。一旦创建了函数，就可以使用 table 操作符从 SQL 查询中调用它。select * from table(PipelineMe);-另附表函数基本表函数是 Oracle9i 开始提供的函数。在 PL/SQL、C 或 Java 中可以实现管道技术和并行化转换功能。所谓表函数就是能够产生一组作为输出的行集合的函

40、数，其核心思想是：将一个运算的结果直接输出到下一个运算中，并不需要创建临时关系表来保存中间结果。显然，利用管道技术和并行化可节省创建临时表关系和重新读取中间结果的开销。表函数也能将一组行的集合作为输入。一个输入集合参数或者是集合类型，如可变数组或 PL/SQL 表，或是游标变量 ref cursor。在 oracle 早期版本中，进行大批量数据传输时，PL/SQL 函数不能将游标作为输入，也不能实施并行化或管道技术，它或需要使用中间表，或需要较大的内存，其装载过程极大影响了系统的性能。从 oracle9i 开始，提供的表函数消除了以往的限制，直接使用管道技术和并行化转换提高了系统性能。表函数对

41、内存要求不高，也不需要创建中间表。表函数的出现扩展了数据库的功能：1 可从一个函数返回多行；2SQL 子查询的结果(即选择多行)可直接传递给函数；3 函数也可将游标作为输入；4 函数可被并行化；5 为进一步的处理增量地返回结果；6 可在 PL/SQL 中使用本地 PL/SQL 接口，也可以在 Java 或 C 中使用 ODCI。当将一个 PL/SQL 或 Java 或 C 程序作为数据源，或将外部文件中的数据导入到表中时，就可通过表函数来实现。这与 SQL*Loader 的作用相同，且操作简单性能好。所以，表函数主要应用在数据的提取、转换和大批量数据装载环境中。使用表函数时，将其放在 from

42、 子句中像查询物理表一样进行查询。表函数的创建与一般函数的创建相同。创建格式：create or replace function schema.name (parameter ,parameter .)return return_datatypeauthid definer | current_userparallel_enablecluster parameter by (col_name ,col_name .) |order parameter by (col_name ,col_name .)deterministic pipelined using implementation_t

43、ypeaggregate update value with external contextusing implemetation_typeis | aspragma autonomous_transaction;declaration statementsbeginexecutable statementsexception exception handler statementsend name;其中，创建表函数的关键子句是 pipelined、pipe row()以及 return。在 create function 子句中，有一个新的选项 pipelined。该选项告诉 oracle

44、 返回函数处理的结果，并不需要等待全部执行完或获得完整的结果集，结果集是一次一行的流水操作，因此不需要额外的内存空间或建立中间表。pipe row 语句是通过 pipelined 发送给函数调用者的接口。由于定义的是函数，所以，必须由 return，但并不提供任何返回参数，因为函数是通过 pipe row语句返回的。实际上，return 的作用是将控制权返回给调用者，并考虑 no_data_found 的异常。以下是创建表函数的过程：1)创建一个对象类型或 PL/SQL 记录；2)为该对象类型或 PL/SQL 记录创建一个对应的集合类型；3)创建返回该集合的函数；4)使用 pipelined

45、结构将该函数定义为管道；5)使用 pipe row 语句返回集合中的一个元素，主要用于将结果返回给调用者；6)为 return 指定空的参数，其目的是表明函数功能完成并将控制权交给调用者。1 常规方法step1:定义要返回的数据类型(即数据格式)。此处创建一个对象类型。create or replace type RegObjectFormat as object(reg_number char(3),reg_date date,discription varchar2(25);step2:为预先定义的对象类型 RegObjectFormat 创建一个集合类型(表类型)。create or r

46、eplace type RegTableType as table of RegObjectFormat;将要创建的表函数打包在一个包中，也可不定义在包中。在函数的定义中加上 pipelined 关键词就表明该函数使用了管道技术。create or replace package RegTestPack asfunction PipeTab_func return RegTableType pipelined;end;create or replace package body RegTestPack asfunction PipeTab_func return RegTableTypepip

47、elined isbeginfor i in 1 5 looppipe row(RegObjectFormat(i, sysdate + i, row | i);end loop;return;end;end;创建表函数必须在其声明中使用 pipelined 关键词；一旦要输出记录，则使用关键词 pipe row；表函数必须以 return 语句结束，return 并不指定任何值。step3:测试管道表函数在 SQL 语句中使用表函数的数据时，必须在 from 子句中加入关键词 table，这就可以和常规表一样使用了。这与嵌套表的使用基本类似。select * from table(RegTe

48、stPack.PipeTab_func();表函数有双层含义：1 定义中不带 pipelined 和 pipe row 关键词，这样，它用于 select 和 from 子句中，但表函数的返回类型一定是集合类型。2 定义中带有 pipelined 和 pipe row 关键词。使用它，它也用在 from 子句中，通过 select 查询输出。只不过它充分利用了管道特性，执行效率比表函数的方法快得多。create or replace package RegTestPack asfunction PipeTab_func return RegTableType pipelined;function Tab_func return RegTableType;end;create or replace package body RegTestPack asfunction PipeTab_func return RegTableTypepipelined isbeginfor i in 1 5 looppipe row(RegObjectFormat(i, sysdate + i, row | i);end loop;return;end;function Tab_func return RegTa