PL/SQL 저장 프로시저에서 쉼표로 구분된 문자열 분할

PL/SQL 저장 프로시저에서 쉼표로 구분된 문자열 분할

Oracle의 PL/SQL 저장 프로시저에 전달해야 하는 CSV 문자열 100.01,200.02,300.03이 있습니다.프로시저 내부에서 표의 숫자 열에 이 값을 삽입해야 합니다.

이를 위해 저는 여기에서 작업 방식을 얻었습니다.

오라클 9i에서 CSV 문자열을 가장 잘 분할하는 방법

[2) SQL의 수준별 연결 사용.]

자, 다른 요구사항이 있습니다.PL/SQL 저장 프로시저에 입력으로 CSV 문자열 2개를 전달해야 합니다.그리고 이 문자열을 끊고 두 CSV 문자열의 각 값을 표의 두 개의 다른 열에 삽입해야 합니다.어떻게 해야 하는지 알려주실 수 있나요?

CSV 입력의 예: mystring varchar2(2000):='0.75, 0.64, 0.56, 0.45';

myAction varchar2(2000):= '0.25, 0.5, 0.65, 0.8';

myString 값은 A열에, myAccount 값은 표의 B열에 들어갑니다.

어떻게 하면 이것을 달성할 수 있는지 알려주실 수?

감사해요.

이것은 당신이 찾고 있는 것을 할 것입니다.목록이 항상 숫자로만 표시될 것으로 가정합니다.그렇지 않은 경우 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; 

여기 좋은 해결책이 있습니다.

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;

출처: CSV - 쉼표로 구분된 값 및 PL/SQL(링크가 더 이상 유효하지 않음)

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은 Oracle 10G 이후부터 사용할 수 있습니다.이전에는 htmldb_util로 불렸으며 기본적으로 설치되지 않았습니다.만약 당신이 그것을 사용할 수 없다면 제가 수년 전에 작성해서 여기에 게시한 문자열 파서를 사용할 수 있습니다.

이것이 당신의 오라클 버전과 맞는지 잘 모르겠습니다.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
;

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;

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');

아웃: aa bb cc deef ghii jj.

이미 많은 좋은 해결책이 제공되었습니다.그러나 텍스트가 매우 단순한 쉼표로 구분된 형식이나 유사한 형식으로 제공되고 속도가 중요하다면, 저는 당신을 위한 해결책을 가지고 있습니다.TABLE함수(PL/SQL)입니다.다른 솔루션에 대한 개요도 제공했습니다.

CSV를 여러 열로 구문 분석하는 블로그 항목에 대한 자세한 내용을 참조하십시오.

에 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;

언급URL : https://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc