Explain the usage of Oracle pipeline function in detail (split one line into multiple lines)

Time:2019-11-10

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.

summary

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!

Recommended Today

Detailed explanation of sshd service and service management command under Linux

sshd SSH is the abbreviation of secure shell, which is the security protocol of application layer. SSH is a reliable protocol which provides security for remote login session and other network services. SSH protocol can effectively prevent information leakage in the process of remote management. openssh-server Function: enable remote hosts to access the sshd service […]