Returns the result set of the ORACLE character split function.

Returns the result set of the ORACLE character split function.

ORACLE does not support direct return of table type like MSSQL. Therefore, you must first create a custom type. This statement uses a Nested Table ).

-- Nested TableCREATE or replace type split_str is table of varchar (100);/-- FunctionCREATE or replace function fn_Split (p_Str VARCHAR2, p_Delimiter VARCHAR2) RETURN split_str PIPELINEDAS v_Str VARCHAR (4000 ): = p_Str; v_Index NUMBER; v_SubLength NUMBER; BEGIN -- p_Delimiter is null IF p_Delimiter is null then for x IN 1 .. LENGTH (v_Str) loop pipe row (SUBSTR (V_Str, x, 1); end loop; RETURN; end if; v_index: = INSTR (v_Str, p_Delimiter ); WHILE v_Index <> 0 loop pipe row (SUBSTR (v_Str, 1, v_Index-1); v_SubLength: = LENGTH (v_Str)-(v_index + LENGTH (p_Delimiter)-1); v_Str: = SUBSTR (v_Str,-v_SubLength, v_SubLength); v_index: = INSTR (v_Str, p_Delimiter); end loop; pipe row (v_Str); RETURN; END; /-- Optimize (the second method is better) SELECT fn_split ('this @ is @ it', '@') FROM dual; SELECT * FROM Table (fn_split ('this @ is @ it ','@@'));

From: Unknown Conditions

Split Oracle field values by specific characters (multiple records )?

You need to write a function to split strings separated by specific characters.

Returned result set of oracle functions and stored procedures

(1) stored procedure (for example, Table a (fields include ano, aname, and aage ))
Create or replace procedure test
As
Begin
Cursor cursor_test is select * from;
Row_test a % rowtype;
Begin
For row_test in cursor_test loop
Dbms_output.put_line (row_test.ano | row_test.aname | row_test.aage );
End loop;
End test;
Call method:
(1) EXEC test;
(2) begin
Test;
End;
/