Oracle splits strings into arrays and traverses the arrays to insert them into the table.

Oracle splits strings into arrays and traverses the arrays to insert tables. 1. defines a custom TYPE, create or replace type ty_str_split is table of VARCHAR2 (4000 ); 2. Define a function create or replace function fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split PIPELINED www.2cto.com IS j INT: = 0; I INT: = 1; len INT: = 0; len1 INT: = 0; str VARCHAR2 (4000); BEGIN len: = LENGTH (p_str); len1: = LENGTH (p_delimiter); WHILE j <len LOOP j: = INSTR (p _ Str, p_delimiter, I); IF j = 0 THEN j: = len; str: = SUBSTR (p_str, I); PIPE ROW (str ); IF I> = len THEN www.2cto.com EXIT; end if; ELSE str: = SUBSTR (p_str, I, j-I); I: = j + len1; PIPE ROW (str ); end if; end loop; RETURN; END fn_split; 3. String Array application */PROCEDURE CJLK_UNIT_DOWN (P_ERRCD in out number, P_ERRMSG in out VARCHAR2, contains IN VARCHAR2, P_JSDWBH IN VARCHAR2, p_JSDWMC IN VARCHAR2, P_Z T in varchar default 'A') as cursor L_JSDWBH is select * from table (CAST (FN_SPLIT (P_JSDWBH, ';') AS ty_str_split); www.2cto.com BEGIN P_ERRCD: = 0; p_ERRMSG: = ''; update aqgl. cjlk set jsdwbh = P_JSDWBH, JSDWMC = P_JSDWMC, ZT = 'B' where xh = P_CJLK_XH; for I IN L_JSDWBH LOOP INSERT INTO AQGL. CJLK_DWFK (XH, PID, JSDWBH, JSDWMC, ZT) VALUES (sys_guid (), P_CJLK_XH, I. COLUMN_VALUE, GET_DWMC (I. column_value), P_ZT); END LO OP; COMMIT; exception when others then rollback; P_ERRCD: = SQLCODE; P_ERRMSG: = SQLERRM; END; P_JSDWBH contains multiple;-separated strings.
Author: Blue Paper