oracle管道函数的用法-oracle trunc函数用法

oracle管道函数是一类特殊的函数，oracle管道函数返回值类型必须为集合，下面就为您将介绍oracle管道函数的语法，供您参考学习。

在普通的函数中，使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。

关键字PIPELINED表明这是一个oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元素，函数以一个空的RETURN 语句结束，以表明它已经完成。

create or replace type MsgType as table of varchar2(4000);  
/  
 
create or replace function f_pipeline_test  
return MsgType  
PIPELINED  
as  
begin  
   for i in 1 .. 10  
   loop  
   pipe row( 'Iteration ' || i || ' at ' || systimestamp );  
   dbms_lock.sleep(1);  
   end loop;  
   pipe row( 'All done!' );  
   return;  
end;  
/  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

在sql*plus中执行该函数，首先设置arraysize为1，否则服务器会按照默认的15来向客户端返回信息，这会影响我们的测试效果。

SQL> set arraysize 1  
SQL> select * from table( f_pipeline_test );  
 
COLUMN_VALUE  
--------------------------------------------------------------------------------  
Iteration 1 at 14-FEB-08 02.13.18.273988000 PM +08:00  
Iteration 2 at 14-FEB-08 02.13.19.275988000 PM +08:00  
Iteration 3 at 14-FEB-08 02.13.20.277767000 PM +08:00  
Iteration 4 at 14-FEB-08 02.13.21.279591000 PM +08:00  
Iteration 5 at 14-FEB-08 02.13.22.281366000 PM +08:00  
Iteration 6 at 14-FEB-08 02.13.23.283189000 PM +08:00  
Iteration 7 at 14-FEB-08 02.13.24.283965000 PM +08:00  
Iteration 8 at 14-FEB-08 02.13.25.285785000 PM +08:00  
Iteration 9 at 14-FEB-08 02.13.26.286570000 PM +08:00  
Iteration 10 at 14-FEB-08 02.13.27.288387000 PM +08:00  
All done!  
 
11 rows selected.  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

如果要在pipeline中执行DML操作，则必须使用自治事务，否则会报ORA-14551错误

create or replace function f_pipeline_testdml  
return MsgType  
PIPELINED  
as  
begin  
   for i in 1 .. 10  
   loop  
   insert into test values(1);  
   pipe row( 'insert into test values( ' || i || ') success at ' || systimestamp );  
   dbms_lock.sleep(1);  
   end loop;  
   pipe row( 'All done!' );  
   return;  
end;  
/  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SQL> select * from table( f_pipeline_testdml );  
select * from table( f_pipeline_testdml ) 
1.
2.

 *  
ERROR at line 1:  
ORA-14551: cannot perform a DML operation inside a query  
ORA-06512: at "NING.F_PIPELINE_TESTDML", line 8create or replace function f_pipeline_testdml  
return MsgType  
PIPELINED  
as  
pragma autonomous_transaction;  
begin  
   for i in 1 .. 10  
   loop  
   insert into test values(1);  
   commit;  
   pipe row( 'insert values ' || i || ' success at ' || systimestamp );  
   dbms_lock.sleep(1);  
   end loop;  
   pipe row( 'All done!' );  
   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:00  
insert values 2 success at 14-FEB-08 02.16.48.865559000 PM +08:00  
insert values 3 success at 14-FEB-08 02.16.49.867377000 PM +08:00  
insert values 4 success at 14-FEB-08 02.16.50.873154000 PM +08:00  
insert values 5 success at 14-FEB-08 02.16.51.874942000 PM +08:00  
insert values 6 success at 14-FEB-08 02.16.52.880781000 PM +08:00  
insert values 7 success at 14-FEB-08 02.16.53.882543000 PM +08:00  
insert values 8 success at 14-FEB-08 02.16.54.894348000 PM +08:00  
insert values 9 success at 14-FEB-08 02.16.55.896153000 PM +08:00  
insert values 10 success at 14-FEB-08 02.16.56.901904000 PM +08:00  
All done!  
 
11 rows selected.  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

在oracle9205及其之后的版本中，在pipeline function中使用自治事务，则必须在pipe row之前提交或者回滚事务，否则会报ORA-06519错误

create or replace function f_pipeline_testdml  
return MsgType  
PIPELINED  
as  
pragma autonomous_transaction;  
begin  
   for i in 1 .. 10  
   loop  
   insert 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_testdml )  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "NING.F_PIPELINE_TESTDML", line 10
这是由于在9205中修复Bug 2711518导致了自治事务的行为有所改变。如果系统从9205之前的版本升级到之后的版本，需要保证pipeline function的行为和以前版本一致，oracle提供了一个10946事件来设置和以前版本的兼容性，如果在管道函数中使用了select for update的cursor，则必须设置event回归以前的特性，否则即使在pipe row之前commit也会导致ORA-1002错误。

ALTER SYSTEM SET EVENT = "10946 trace name context forever, level 8" scope=spfile;

【编辑推荐】