oracle 在 PL/SQL 存储过程中拆分逗号分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4004377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Splitting comma separated string in a PL/SQL stored proc
提问by Jimmy
I've CSV string 100.01,200.02,300.03 which I need to pass to a PL/SQL stored procedure in Oracle. Inside the proc,I need to insert these values in a Number column in the table.
我有 CSV 字符串 100.01,200.02,300.03，我需要将其传递给 Oracle 中的 PL/SQL 存储过程。在 proc 内部，我需要将这些值插入到表中的 Number 列中。
For this, I got a working approach from over here:
为此，我从这里得到了一种工作方法：
How to best split csv strings in oracle 9i
[2) Using SQL's connect by level.].
[2) 使用 SQL 的按级别连接。]。
Now,I've another requirement. I need to pass 2 CSV strings[equal in length] as input to PL/SQL stored proc.And, I need to break this string and insert each value from two CSV strings into two different columns in the table.Could you please let me know how to go about it?
现在，我还有一个要求。我需要传递 2 个 CSV 字符串 [长度相等] 作为 PL/SQL 存储过程的输入。而且，我需要打破这个字符串并将两个 CSV 字符串中的每个值插入到表中的两个不同列中。你能不能让我知道怎么做吗？
Example of CSV inputs: mystring varchar2(2000):='0.75, 0.64, 0.56, 0.45';
CSV 输入示例： mystring varchar2(2000):='0.75, 0.64, 0.56, 0.45';
myAmount varchar2(2000):= '0.25, 0.5, 0.65, 0.8';
myAmount varchar2(2000):= '0.25, 0.5, 0.65, 0.8';
myString values would go into Column A and myAmount values into Column B in the table.
myString 值将进入 A 列，而 myAmount 值将进入表中的 B 列。
Could you please let me know how to achieve this?
你能告诉我如何实现这一目标吗？
Thanks.
谢谢。
采纳答案by Craig
This should do what you are looking for.. It assumes your list will always be just numbers. If that is not the case, just change the references to DBMS_SQL.NUMBER_TABLE to a table type that works for all of your data:
这应该做你正在寻找的......它假设你的列表永远只是数字。如果不是这种情况，只需将 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;
回答by Michael Goldshteyn
Here is a good solution:
这是一个很好的解决方案：
FUNCTION comma_to_table(iv_raw IN VARCHAR2) RETURN dbms_utility.lname_array IS
ltab_lname dbms_utility.lname_array;
ln_len BINARY_INTEGER;
BEGIN
dbms_utility.comma_to_table(list => iv_raw
,tablen => ln_len
,tab => ltab_lname);
FOR i IN 1 .. ln_len LOOP
dbms_output.put_line('element ' || i || ' is ' || ltab_lname(i));
END LOOP;
RETURN ltab_lname;
END;
Source: CSV - comma separated values - and PL/SQL(link no longer valid)
来源：CSV - 逗号分隔值 - 和 PL/SQL（链接不再有效）
回答by Tony Andrews
I use apex_util.string_to_table to parse strings, but you can use a different parser if you wish. Then you can insert the data as in this example:
我使用 apex_util.string_to_table 来解析字符串，但如果您愿意，您可以使用不同的解析器。然后您可以插入数据，如本例所示：
declare
myString varchar2(2000) :='0.75, 0.64, 0.56, 0.45';
myAmount varchar2(2000) :='0.25, 0.5, 0.65, 0.8';
v_array1 apex_application_global.vc_arr2;
v_array2 apex_application_global.vc_arr2;
begin
v_array1 := apex_util.string_to_table(myString, ', ');
v_array2 := apex_util.string_to_table(myAmount, ', ');
forall i in 1..v_array1.count
insert into mytable (a, b) values (v_array1(i), v_array2(i));
end;
Apex_util is available from Oracle 10G onwards. Prior to this it was called htmldb_util and was not installed by default. If you can't use that you could use the string parser I wrote many years ago and posted here.
Apex_util 从 Oracle 10G 开始可用。在此之前，它被称为 htmldb_util，默认情况下未安装。如果你不能使用它，你可以使用我多年前写的并张贴在这里的字符串解析器。
回答by christian
I am not sure if this fits your oracle version. On my 10g I can use pipelined table functions:
我不确定这是否适合您的 oracle 版本。在我的 10g 上，我可以使用流水线表函数：
set serveroutput on
create type number_list as table of number;
-- since you want this solution
create or replace function split_csv (i_csv varchar2) return number_list pipelined
is
mystring varchar2(2000):= i_csv;
begin
for r in
( select regexp_substr(mystring,'[^,]+',1,level) element
from dual
connect by level <= length(regexp_replace(mystring,'[^,]+')) + 1
)
loop
--dbms_output.put_line(r.element);
pipe row(to_number(r.element, '999999.99'));
end loop;
end;
/
insert into foo
select column_a,column_b from
(select column_value column_a, rownum rn from table(split_csv('0.75, 0.64, 0.56, 0.45'))) a
,(select column_value column_b, rownum rn from table(split_csv('0.25, 0.5, 0.65, 0.8'))) b
where a.rn = b.rn
;
回答by Amarender
CREATE OR REPLACE PROCEDURE insert_into (
p_errcode OUT NUMBER,
p_errmesg OUT VARCHAR2,
p_rowsaffected OUT INTEGER
)
AS
v_param0 VARCHAR2 (30) := '0.25,2.25,33.689, abc, 99';
v_param1 VARCHAR2 (30) := '2.65,66.32, abc-def, 21.5';
BEGIN
FOR i IN (SELECT COLUMN_VALUE
FROM TABLE (SPLIT (v_param0, ',')))
LOOP
INSERT INTO tempo
(col1
)
VALUES (i.COLUMN_VALUE
);
END LOOP;
FOR i IN (SELECT COLUMN_VALUE
FROM TABLE (SPLIT (v_param1, ',')))
LOOP
INSERT INTO tempo
(col2
)
VALUES (i.COLUMN_VALUE
);
END LOOP;
END;
回答by Harikumar.S
create or replace procedure pro_ss(v_str varchar2) as
v_str1 varchar2(100);
v_comma_pos number := 0;
v_start_pos number := 1;
begin
loop
v_comma_pos := instr(v_str,',',v_start_pos);
if v_comma_pos = 0 then
v_str1 := substr(v_str,v_start_pos);
dbms_output.put_line(v_str1);
exit;
end if;
v_str1 := substr(v_str,v_start_pos,(v_comma_pos - v_start_pos));
dbms_output.put_line(v_str1);
v_start_pos := v_comma_pos + 1;
end loop;
end;
/
call pro_ss('aa,bb,cc,dd,ee,ff,gg,hh,ii,jj');
outout: aa bb cc dd ee ff gg hh ii jj
输出：aa bb cc dd ee ff gg hh ii jj
回答by YoYo
Many good solutions have been provided already. However, if he text is provided in a very simple comma delimited format or similar, and speed is of importance, then I have for you a solution with a TABLEfunction (in PL/SQL). I have also provided a rundown of some other solutions.
已经提供了许多好的解决方案。但是，如果文本以非常简单的逗号分隔格式或类似格式提供，并且速度很重要，那么我为您提供了一个带有TABLE函数的解决方案（在 PL/SQL 中）。我还提供了一些其他解决方案的概要。
Please see more on the Blog Entry on Parsing a CSV into multiple columns.
回答by Ravindra Vemula...
As for the connect byuse case, this approach should work for you:
至于connect by用例，这种方法应该适合您：
select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)
from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;
