Issues in inserting comma separated strings to table-CodePudding

I have following 3 parameters from stored procedure

P_Param1 = 12

P_Paramj2= 'val:15,val:16'

P_param3 = 'Name:check values,Name:bv,Name:cv'

I have a table and need to insert above details and final table looks like below

proID  CatID CatName
12     15   check values
12     15   bv
12     15   cv
12     16   check values
12     16   bv
12     16   cv

I have written a query to split P_param3 as below and getting splitted values but stuck in generating loops to make a table like above.

SELECT
    regexp_substr('Name:check values,Name:bv,Name:cv', '(Name:)?(.*?)(,Name:|$)', 1, level, NULL,
                  2) AS "CatName"
FROM
    dual
CONNECT BY
    level <= regexp_count('Name:check values,Name:bv,Name:cv', 'Name:');

CodePudding user response：

Split the catIds into rows and split the catNames into rows and then CROSS JOIN them and insert.

You can do it with simple (fast) string functions using:

CREATE PROCEDURE insertCats(
  p_proid    IN table_name.proid%TYPE,
  p_catids   IN VARCHAR2,
  p_catnames IN VARCHAR2
)
IS
  c_catid_prefix   CONSTANT VARCHAR2(10) := 'val:';
  c_catid_length   CONSTANT PLS_INTEGER  := LENGTH(c_catid_prefix);
  c_catname_prefix CONSTANT VARCHAR2(10) := 'Name:';
  c_catname_length CONSTANT PLS_INTEGER  := LENGTH(c_catname_prefix);
BEGIN
  INSERT INTO table_name (proid, catid, catname)
  WITH catid_bounds (catids, spos, epos) AS (
    SELECT p_catids,
           1   c_catid_length,
           INSTR(p_catids, ',', 1   c_catid_length)
    FROM   DUAL
  UNION ALL
    SELECT catids,
           epos   1   c_catid_length,
           INSTR(catids, ',', epos   1   c_catid_length)
    FROM   catid_bounds
    WHERE  epos > 0
  ),
  catids (catid) AS (
    SELECT CASE epos
           WHEN 0
           THEN SUBSTR(catids, spos)
           ELSE SUBSTR(catids, spos, epos - spos)
           END
    FROM   catid_bounds
  ),
  catname_bounds (catnames, spos, epos) AS (
    SELECT p_catnames,
           1   c_catname_length,
           INSTR(p_catnames, ',', 1   c_catname_length)
    FROM   DUAL
  UNION ALL
    SELECT catnames,
           epos   1   c_catname_length,
           INSTR(catnames, ',', epos   1   c_catname_length)
    FROM   catname_bounds
    WHERE  epos > 0
  ),
  catnames (catname) AS (
    SELECT CASE epos
           WHEN 0
           THEN SUBSTR(catnames, spos)
           ELSE SUBSTR(catnames, spos, epos - spos)
           END
    FROM   catname_bounds
  )
  SELECT p_proid,
         i.catid,
         n.catname
  FROM   catids i CROSS JOIN catnames n;
END;
/

db<>fiddle here