oracle存储过程缺少逗号,oracle – 在PL/SQL存储过程中分割逗号分隔的字符串

这应该做你正在寻找的..它假设你的列表将永远只是数字。如果不是这样，只需将对DBMS_SQL.NUMBER_TABLE的引用更改为适用于所有数据的表类型：

CREATE OR REPLACE PROCEDURE insert_from_lists(

list1_in IN VARCHAR2,

list2_in IN VARCHAR2,

delimiter_in IN VARCHAR2 := ','

)

IS

v_tbl1 DBMS_SQL.NUMBER_TABLE;

v_tbl2 DBMS_SQL.NUMBER_TABLE;

FUNCTION list_to_tbl

(

list_in IN VARCHAR2

)

RETURN DBMS_SQL.NUMBER_TABLE

IS

v_retval DBMS_SQL.NUMBER_TABLE;

BEGIN

IF list_in is not null

THEN

/*

|| Use lengths loop through the list the correct amount of times,

|| and substr to get only the correct item for that row

*/

FOR i in 1 .. length(list_in)-length(replace(list_in,delimiter_in,''))+1

LOOP

/*

|| Set the row = next item in the list

*/

v_retval(i) :=

substr (

delimiter_in||list_in||delimiter_in,

instr(delimiter_in||list_in||delimiter_in, delimiter_in, 1, i ) + 1,

instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i+1) - instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i) -1

);

END LOOP;

END IF;

RETURN v_retval;

END list_to_tbl;

BEGIN

-- Put lists into collections

v_tbl1 := list_to_tbl(list1_in);

v_tbl2 := list_to_tbl(list2_in);

IF v_tbl1.COUNT <> v_tbl2.COUNT

THEN

raise_application_error(num => -20001, msg => 'Length of lists do not match');

END IF;

-- Bulk insert from collections

FORALL i IN INDICES OF v_tbl1

insert into tmp (a, b)

values (v_tbl1(i), v_tbl2(i));

END insert_from_lists;