oracle中字符串分解为数组并遍历数组插入表中
oracle中字符串分解为数组并遍历数组插入表中
1、定义一个自定义类型
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
2、定义一个函数
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、字符串数组的应用
*/
PROCEDURE CJLK_UNIT_DOWN(
P_ERRCD IN OUT NUMBER,
P_ERRMSG IN OUT VARCHAR2,
P_CJLK_XH IN VARCHAR2,
P_JSDWBH IN VARCHAR2,
P_JSDWMC IN VARCHAR2,
P_ZT 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 LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_ERRCD:=SQLCODE;
P_ERRMSG:=SQLERRM;
END;
其中P_JSDWBH包含了多个用;分隔的字符串。
作者 蓝红石
oracle中字符串分解为数组并遍历数组插入表中 
