3、keywordPIPELINED表明这是一个oracle管道函数,oracle管道函数的返回值类型必须为集合,在函数中,PIPE ROW语句被用来返回该集合的单个元 素,函数以一个空的RETURN 语句结束,以表明它已经完毕。 4、因为管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此能够带来可观的性能提升。 二、怎样编写管道...
1>创建一个表类型 createorreplacetype t_tableistableofnumber; 2>创建函数返回上面定义的类型 createorreplacefunctionf_pipe(snumber)returnt_table pipelinedasv_numbernumber;beginforiin1..s loop v_number :=i;piperow(v_number);endloop;return;endf_pipe; ; 3>测试 select*fromtable(f_pipe(5)) 回到...
which can be used only in a single database(as explained in OracleDatabase Object-Relational Developer's Guide). Although the return type ofa pipelined table function might appear to be a PL/SQL type,
Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on ...
Oracle Pipelined Table Functions简介 //概况 //基本上,当你希望一个PLSQL(或者java或者c)程序作为数据源,而不是表, //你可能会用到管道函数(pipelined function). //pipelined function操作起来就像一张表 //一个pl/sql函数可能会用于数据仓库的数据库里面,转换大量的数据。
CREATE OR REPLACE FUNCTION tf(stringin VARCHAR2) RETURN str_array PIPELINED IS i PLS_INTEGER; str VARCHAR2(100); tab sys.dbms_utility.uncl_array; BEGIN str := '"' || REPLACE(stringin, ',', '","') || '"'; sys.dbms_utility.comma_to_table(str, i, tab); ...
//概况 //基本上,当你希望一个PLSQL(或者java或者c)程序作为数据源,而不是表, //你可能会用到管道函数(pipelined function). //pipelined function操作起来就像一张表 //一个pl/sql函数可能会用于数据仓库的数据库里面,转换大量的数据。 //这可能包括格式化一系列
CREATEORREPLACE TYPE RESOLVE_STR ISTABLEOFVARCHAR2 (4000); 1 CREATEORREPLACEFUNCTION F_SPLIT 2 ( 3 P_STR INVARCHAR2, 4 P_DELIMITER INvarchar2 5 ) RETURN RESOLVE_STR PIPELINED 6 AS 7 j INT :=0; 8 i INT :=1; 9 lenINT :=0; ...
Note that you do not need to explicitly close the cursor variable; Oracle Database will automatically close a cursor variable created with theCURSORexpression when the table function terminates. Before exploring the impact on performance and memory, let’s verify that this pipelined table function ca...
scss 代码解读复制代码--Oracle 示例函数 CREATE OR REPLACE TYPE "T_RET_TABLE" IS TABLE OF VARCHAR2 (4000) / CREATE OR REPLACE FUNCTION "ROW_SPLIT" (var_str in string, var_split In String) return t_ret_table PIPELINED as var_tmp clob; var_element clob; n_length Number := length(var...