在 PL/SQL 存储过程中拆分逗号分隔的字符串 [英] Splitting comma separated string in a PL/SQL stored proc

问题描述

我有 CSV 字符串 100.01,200.02,300.03，我需要将其传递给 Oracle 中的 PL/SQL 存储过程.在 proc 内部，我需要将这些值插入到表中的 Number 列中.

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.

为此，我从这里得到了一个可行的方法:

For this, I got a working approach from over here:

如何在 oracle 9i 中最好地拆分 csv 字符串

[2) 使用 SQL 的按级别连接.].

[2) Using SQL's connect by level.].

现在，我还有一个要求.我需要传递 2 个 CSV 字符串 [长度相等] 作为 PL/SQL 存储过程的输入.而且，我需要打破这个字符串并将两个 CSV 字符串中的每个值插入到表中的两个不同列中.你能不能让我知道怎么做吗?

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?

CSV 输入示例:mystring varchar2(2000):='0.75, 0.64, 0.56, 0.45';

Example of CSV inputs: 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 值将进入表中的 A 列，而 myAmount 值将进入 B 列.

myString values would go into Column A and myAmount values into Column B in the table.

能否请您告诉我如何实现这一目标?

Could you please let me know how to achieve this?

谢谢.

推荐答案

这应该可以满足您的要求.它假定您的列表始终只是数字.如果不是这种情况，只需将 DBMS_SQL.NUMBER_TABLE 的引用更改为适用于所有数据的表类型:

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:

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; 

这篇关于在 PL/SQL 存储过程中拆分逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！