如何使用pipeline function获得实时输出

最新推荐文章于 2024-05-29 16:49:31 发布

iteye_2060

最新推荐文章于 2024-05-29 16:49:31 发布

阅读量136

点赞数

本文介绍Oracle管道函数的使用方法及注意事项，包括如何实现实时输出和处理DML操作，并探讨不同Oracle版本中管道函数的行为差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如何使用

pipelinefunction

获得实时输
出


create type lookup_row as 


 record ( idx number, text varchar2(20) );


create type lookups_tab as table of lookup_row;


create or replace function Lookups_Fn


 return lookups_tab


 pipelined


is


 v_row lookup_row;


begin


 for j in 1..10


 loop


 v_row := 


 case j


 when 1 then lookup_row ( 1, 'one' )


 when 2 then lookup_row ( 2, 'TWO' )


 when 3 then lookup_row ( 3, 'three' )


 when 4 then lookup_row ( 4, 'FOUR' )


 when 5 then lookup_row ( 5, 'five' )



 when 6 then lookup_row ( 6, 'SIX' )


 when 7 then lookup_row ( 7, 'seven' )


 else lookup_row ( j, 'other' )


 end;


 pipe row ( v_row );


 end loop;


 return;


end Lookups_Fn
;


很多人都知道，在普通的函数中，使用

dbms_output

输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。但你如果
需要在客户端实时的输出函数执行过程中的一些信息，在

Oracle 9i

以后则可以使用管道函数

(pipeline function)

。

 


PIPELINED

（关键字）表明这是一个管道函数，管道函数的返回值类型必须为集合，在函数中，

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;


e
nd;


/ 


在

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.273988
000 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 0
2.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. 


如果要在

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 val
ues( ' || 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 query


ORA-06512: at "NING.F_PIPELINE_TESTDML", line 8


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);


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_V
ALUE


--------------------------------------------------------------------------------


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-FE
B-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.9019
04000 PM +08:00


All done!


11 rows selected. 



在

Oracle 9205

及其之后的版本中，在

pipeline function

中使用自治事务，则必须在

pipe row

之前提交或者回滚事务，否则会报

ORA-06519

错误。

 



create or replace function f_pipeline_testdml


return MsgType


PIPELINED


as


pragma autonomous_transaction;


begin


for i i
n 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 )


 *


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;