拆分多列分号分隔的字符串并创建记录 [英] Split multi column semicolon separated string and create records

问题描述

我有一个存储过程，可以接收三个字符串参数

I have a stored procedure which receives three string parameters

1. p_ReqIDs VARCHAR2
2. p_ItemIDs VARCHAR2
3. p_Qtys    VARCHAR2

上面的3个字符串变量包含以分号分隔的值，如下所示:

The above 3 string variables contain semicolon-separated values like this:

p_ReqIDs  := "56;56;56;"  
p_ItemIDs := "3;2;1;"  
p_Qtys    := "400;300;200;"  

我需要拆分值并创建像这样的行:

I need to split values and create rows like this:

p_ReqIDs   p_ItemIDs   p_Qtys  
------------------------------------
  56           3       400  
  56           2       300  
  56           1       200  

我还需要将查询拆分并插入表中的查询.

I need the query which splits and insert into table also.

谢谢

推荐答案

我确实很喜欢:)简短而甜美.

i did like this :) short and sweet.

create or replace procedure proc1(p_ReqID varchar2,p_ItemID varchar2,p_ItemQty varchar2)
is
begin
 insert into test(req_id,item_id,itemqty)
 select regexp_substr(req_id,'[^;]+',1,level),
         regexp_substr(item_id,'[^;]+',1,level),
         regexp_substr(item_qty,'[^;]+',1,level)
  from  (
      select p_ReqID Req_ID,p_ItemID item_id,p_ItemQty item_qty
      from dual
      )
  connect by regexp_substr(req_id,'[^;]+',1,level) is not null;
end;

这篇关于拆分多列分号分隔的字符串并创建记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！