代码之家 › 专栏 › 技术社区 › Laxmikant

拆分分隔符分隔字符串并插入到oracle 11中的表中

plsql stored-procedures oracle regex

Laxmikant · 技术社区 · 6 年前

我有分隔符分隔的输入字符串,它可以有大约40个标记(数字可能会增加),我想使用oracle11中的存储过程将这些值插入表中;

创建一个具有40个IN参数的SP并使用它插入。

创建一个参数为1的SP,它将接受该字符串并拆分分隔符分隔的标记,然后将它们插入表中

如果第二种方法看起来不错,那么请建议如何实现它??

"abc,123,xyz,pqr,12" (此处分隔符为逗号) 因此,在运行SP my table table1(A varchar2,B Number,C varchar2,D varchar2,E Number)之后,应该有如下条目

A  | B | C | D | E
abc|123|xys|pqr |12

我提出了下面的解决方案不确定性能,有没有更好的方法来做同样的事情?

declare
  string_to_parse varchar2(2000) := 'abc,123,xyz,pqr,12';
  A varchar2(4);
  B number;
  C varchar2(4);
  D varchar2(4);
  E number;
begin

  string_to_parse := string_to_parse||',';

   A  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 1);
   B  := TO_NUMBER(REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 2));
   C  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 3);
   D  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 4);
   E  := TO_NUMBER(REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 5));
   dbms_output.put_line('A ' || A || ' B ' || B || ' c ' || c || ' D ' || D || ' E ' || E);
--insert into table
end;

2 回复 | 直到 6 年前

diziaq 6 年前

在这种特殊情况下,分裂离目标还有很长的路要走。考虑到一个目标表可能有很多列(是的,在一个不同的变量中处理每个列需要5个),我建议使用schema dictionary来增加一些灵活性。

让我们看一个接受两个参数的过程:表名和包含逗号分隔值列表的字符串。这里假设表只有字符串、数字和时间列。要实现完整的版本,请在过程的开头添加对所有必需数据类型的处理。

select level as column_id, 
       REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) as column_val 
  from dual connect by REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) is not null;

整个过程如下:

  create or replace procedure myInsertInto(pi_table_name  char,
                                           pi_values_list char)
  is
    v_statement     varchar2(30000) := 'INSERT INTO %TABLE_NAME% (%COLUMNS_LIST%) VALUES (%VALUES_LIST%)';
    v_columns_list  varchar2(10000);
    v_values_list   varchar2(10000);
  begin

    SELECT LISTAGG(T.column_name, ',') within group (order by T.column_id) ,
           LISTAGG( -- implement specific types handling here
                    CASE
                    WHEN S.column_val IS NULL
                      THEN 'NULL'
                    WHEN T.data_type = 'NUMBER'
                      THEN S.column_val
                    WHEN T.data_type IN ('DATE', 'TIMESTAMP') 
                      THEN 'TIMESTAMP ''' || S.column_val || ''''
                    WHEN T.data_type like '%CHAR%' 
                      THEN '''' || S.column_val || ''''                    
                    ELSE 'NULL'
                    END, 
           ',') within group (order by T.column_id)
    into v_columns_list,
         v_values_list
    from user_tab_cols T,
         (select level as column_id, REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) as column_val 
            from dual connect by REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) is not null) S
   where T.table_name = pi_table_name
     and T.column_id = S.column_id;

    if v_columns_list IS NULL then
      raise_application_error(-20000, 'Not found columns for table ' || pi_table_name);
    end if;

    -- finalizing the statement
    v_statement := replace(v_statement, '%TABLE_NAME%', pi_table_name);      
    v_statement := replace(v_statement, '%COLUMNS_LIST%', v_columns_list);      
    v_statement := replace(v_statement, '%VALUES_LIST%', v_values_list);

    execute immediate v_statement;
  end;
  /

那就这样用吧

create table MY_TABLE (
  col_a VARCHAR2(10),
  col_b NUMBER,
  col_c VARCHAR2(10),
  col_d DATE,
  col_E VARCHAR2(10) default 'DEFAULT'
);



begin
  myInsertInto('MY_TABLE', 'abc,123,xyz,2018-01-02 23:01:10,pqr' );
  myInsertInto('MY_TABLE', 'def,345,mkr' );
  myInsertInto('MY_TABLE', 'fgh' );
end;
/

-1

piezol 6 年前

第一种方法是禁止。

简单地说:

将输入字符串赋给变量s。

如果s的长度为0,则退出循环
用instr查找第一个出现的分隔符(',')。分配给X
X:=X-1
如果X>0,然后将substr(s,1,X)插入表中
如果X>0,则s:=substr(s,X+1,len(s))

我没有对它进行测试,有一些明显的方法可以优化它(例如,不用将子字符串赋回s,而是可以存储当前已解析部分的“左端索引”)。

但是有更好的方法-在纯sql中执行。不幸的是,我不知道您的oracle版本是否支持所有功能,但请尝试选择以下选项:

with 
my_input_string as (
   select 'my,delimited,,,,,,input,string' s from dual
),
string_to_rows as (
   select trim(regexp_substr(s, '[^,]+', 1, LEVEL)) col 
    from my_input_string
 connect by instr(my_input_string.s, ',', 1, LEVEL - 1) > 0
)
select *
  from string_to_rows
 where col is not null

如果它有效(我所说的'works'是指-返回四行),只需在insert中使用它。