Oracle pipeline function is a kind of special function. The return value type of Oracle pipeline function must be set
If you need some information during the execution of the real-time output function of the client, you can use the pipeline function after Oracle 9i.
The keyword pipeline indicates that this is an Oracle pipeline function, and the return value type of the Oracle pipeline function must be a collection
--Create a collection to accept the returned value 1st.create or replace type type_split as table of varchar2(4000); --Create pipe functions create or replace function split(p_string varchar2, p_sep varchar2 := ',') return type_split pipelined --The information output by DBMS output needs to be returned to the client once the server executes the complete function --Pipelined indicates that this is a pipeline function, and the return value type of Oracle pipeline function must be set --The pipe row statement is used to return a single element of the collection as v_string varchar2(4000) := p_string; idx Number; begin loop --IDX is the first location idx := instr(v_string, p_sep); if idx > 0 then --, the previous data is added to row /, and the later data is the string used in the next cycle pipe row(substr(v_string, 1, idx - 1)); v_string := substr(v_string, idx + length(p_sep)); else exit; end if; end loop; --Return is required after execution return ; end; test: select a.cust_po,b.column_value proqepi from ( select cust_po,proqepi from cux_custpo_info_t where cust_po='PX90806001-4' ) a,(table(split(a.proqepi,','))) b
The test was successful.
The above is the usage of Oracle pipeline function introduced by Xiaobian to you (one line is divided into multiple lines), hoping to help you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!
If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!