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