封装成了一个函数,入参(字符串,分隔符):
CREATE OR REPLACE FUNCTION split_string(
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2
) RETURN SYS.ODCIVARCHAR2LIST
AS
v_start_pos NUMBER := 1;
v_delimiter_pos NUMBER;
v_split_values SYS.ODCIVARCHAR2LIST;
BEGIN
v_split_values := SYS.ODCIVARCHAR2LIST();
LOOP
v_delimiter_pos := INSTR(p_str, p_delimiter, v_start_pos);
IF v_delimiter_pos = 0 THEN
v_split_values.EXTEND;
v_split_values(v_split_values.COUNT) := SUBSTR(p_str, v_start_pos);
EXIT;
END IF;
v_split_values.EXTEND;
v_split_values(v_split_values.COUNT) := SUBSTR(p_str, v_start_pos, v_delimiter_pos - v_start_pos);
v_start_pos := v_delimiter_pos + LENGTH(p_delimiter);
END LOOP;
RETURN v_split_values;
END;
/
调用函数:
DECLARE
v_str VARCHAR2(50) := '3,4,5,6';
v_split_values SYS.ODCIVARCHAR2LIST;
BEGIN
v_split_values := split_string(v_str, ',');
FOR i IN 1..v_split_values.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_split_values(i));
END LOOP;
END;
/