oracle管道函數(shù)是一類特殊的函數(shù)，oracle管道函數(shù)返回值類型必須為集合，下面就為您將介紹oracle管道函數(shù)的語(yǔ)法，供您參考學(xué)習(xí)。

在普通的函數(shù)中，使用dbms_output輸出的信息，需要在服務(wù)器執(zhí)行完整個(gè)函數(shù)后一次性的返回給客戶端。如果需要在客戶端實(shí)時(shí)的輸出函數(shù)執(zhí)行過程中的一些信息，在oracle9i以后可以使用管道函數(shù)(pipeline function)。

關(guān)鍵字PIPELINED表明這是一個(gè)oracle管道函數(shù)，oracle管道函數(shù)的返回值類型必須為集合，在函數(shù)中，PIPE ROW語(yǔ)句被用來返回該集合的單個(gè)元素，函數(shù)以一個(gè)空的RETURN 語(yǔ)句結(jié)束，以表明它已經(jīng)完成。

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

在sql*plus中執(zhí)行該函數(shù)，首先設(shè)置arraysize為1，否則服務(wù)器會(huì)按照默認(rèn)的15來向客戶端返回信息，這會(huì)影響我們的測(cè)試效果。

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.  
 

如果要在pipeline中執(zhí)行DML操作，則必須使用自治事務(wù)，否則會(huì)報(bào)ORA-14551錯(cuò)誤

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

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 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.  

在oracle9205及其之后的版本中，在pipeline function中使用自治事務(wù)，則必須在pipe row之前提交或者回滾事務(wù)，否則會(huì)報(bào)ORA-06519錯(cuò)誤

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 )  

  *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "NING.F_PIPELINE_TESTDML", line 10
這是由于在9205中修復(fù)Bug 2711518導(dǎo)致了自治事務(wù)的行為有所改變。如果系統(tǒng)從9205之前的版本 升級(jí)到之后的版本，需要保證pipeline function的行為和以前版本一致，oracle提供了一個(gè)10946事件來設(shè)置和以前版本的兼容性，如果在管道函數(shù)中使用了select for update的cursor，則必須設(shè)置event回歸以前的特性，否則即使在pipe row之前commit也會(huì)導(dǎo)致ORA-1002錯(cuò)誤。

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

【編輯推薦】

oracle自定義函數(shù)的使用

計(jì)算時(shí)間差的Oracle函數(shù)

Oracle日期函數(shù)簡(jiǎn)介

創(chuàng)建Oracle包的語(yǔ)法

Oracle TRIM函數(shù)語(yǔ)法介紹