在oracle11中，拆分分隔符分隔的字符串并插入到表中

Question

我有分隔符分隔的输入字符串，它可以有大约40个令牌(数量可能会增加(，我想使用oracle11中的存储过程将这些值插入到表中；最好的方法是什么

1. 创建一个具有40 IN参数的SP并使用它进行插入
2. 创建一个带有1 IN参数的SP，该参数将接受该字符串，并拆分分隔符分隔的令牌并将其插入表中

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

例如，如果字符串类似于"abc,123,xyz,pqr,12"(此处分隔符为逗号(因此，在运行SP之后，我的表表1(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;

Answer 1

在这种特殊情况下，拆分离目标还有很长的路要走。考虑到目标表可能有很多列(是的，5是在一个不同的变量中处理每一列的数量(，我建议使用模式字典来增加一点灵活性。

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

请注意，在中间我们使用标准SQL方法将字符串拆分为子字符串表：

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

Answer 2

第一种方法是否定。

第二种可能有效。简单地说：

1. 将输入字符串分配给变量s

现在，在循环中：

1. 如果s的长度为0，则退出循环
2. 用instr查找第一个出现的分隔符("，"(。将其分配给X
3. 如果X=0，则X:=len(字符串(+1
4. X:=X-1
5. 如果X>0，则将子字符串(s，1，X(插入到表中
6. 如果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"是指返回四行(，只需在插入中使用它。将硬编码字符串替换为过程的参数，仅此而已