在PL/SQL存储过程中分割逗号分隔的字符串

问题描述

我有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存储过程中分割逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！