I have a table and contains following fields in my Oracle DB

THEME_ID
DIR_ID
CLUSTER_ID
MONTH_ID

The Sample output should be like below, I am inserting as multiple row using below MERGE..INTO query. Total 12 rows needs to be inserted. As per my query it inserts only 6 because month_id not included as part of query

THEME_ID DIR_ID CLUSTER_ID MONTH_ID
23        2       a          1
23        2       c          1
23        2       v          1
23        2       a          2
23        2       c          2 
23        2       v          2
123       2       a          1
123       2       c          1
123       2       v          1
123       2       a          2
123       2       c          2
123       2       v          2

I am inserting multiple comma separate values based on THEME_ID

for eg:

P_themeId = '23,123'
P_dirId = '2'  ( only one value here always)
P_clusterId = 'a,c,v'

The below query works only for THEME_ID,DIR_ID and CLUSTER_ID. Now I want to insert MONTH_ID . This also contains multiple comma separated value as given in query. **(P_month varchar2(20) := '1,2';)** But I am not able to write a query for month part. is it possible to include this in MERGE INTO query or some other way

 PROCEDURE SP_TEST (
         P_themeId IN VARCHAR2,
        P_dirId IN NUMBER,
        P_clusterId IN VARCHAR2,
        P_createdBy IN VARCHAR2,
     P_ALL OUT SYS_REFCURSOR 
    ) AS
    **P_month varchar2(20) := '1,2';**
    BEGIN
     FOR i IN
    (SELECT trim(regexp_substr(P_themeId, '[^,] ', 1, LEVEL)) l
    FROM dual
      CONNECT BY LEVEL <= regexp_count(P_themeId, ',') 1
    )

    LOOP

     MERGE INTO TBL_TEST TA USING
      (SELECT TMP1.CLUSTERID
      FROM
        (SELECT trim(regexp_substr(P_clusterId, '[^,] ', 1, LEVEL)) CLUSTERID
    FROM dual
      CONNECT BY LEVEL <= regexp_count(P_clusterId, ',') 1 ) TMP1
      ) TMP ON (TA.CLUSTER_ID = TMP.CLUSTERID AND TA.THEME_ID = i.l )
     
      
      
    WHEN NOT MATCHED THEN
      INSERT
        ( THEME_ID,DIR_ID, CLUSTER_ID,CREATED_BY
        ) VALUES
        ( i.l,P_dirId,TMP.CLUSTERID,P_createdBy
        );

     END LOOP;


       OPEN P_ALL FOR SELECT
                            
                              'INserted' AS msgs
                          FROM
                              dual;

    END SP_TEST;

CodePudding user response：

Rather than using a PL/SQL loop (or nested loops), you could do all the unnesting of the lists within the merge statement, using cross-joined subquery factoring (CTEs):

CREATE OR REPLACE PROCEDURE SP_TEST (
    P_themeId IN VARCHAR2,
    P_dirId IN NUMBER,
    P_clusterId IN VARCHAR2,
    P_createdBy IN VARCHAR2,
    P_ALL OUT SYS_REFCURSOR 
) AS
    P_month varchar2(20) := '1,2';
BEGIN
    MERGE INTO TBL_TEST ta
    USING
    (
        WITH themes (theme_id) AS (
            SELECT regexp_substr(P_themeId, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_themeId, ',')   1
        ),
        clusters (cluster_id) AS (
            SELECT regexp_substr(P_clusterId, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_clusterId, ',')   1
        ),
        months (month_id) AS (
            SELECT regexp_substr(P_month, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_month, ',')   1
        )
        SELECT t.theme_id, P_dirId as dir_id, c.cluster_id, m.month_id
        FROM themes t
        CROSS JOIN clusters c
        CROSS JOIN months m
    ) tmp
    ON (
        ta.theme_id = tmp.theme_id
        AND ta.dir_id = tmp.dir_id
        AND ta.cluster_id = tmp.cluster_id
        AND ta.month_id = tmp.month_id
    )
    WHEN NOT MATCHED THEN
    INSERT (THEME_ID, DIR_ID, CLUSTER_ID, MONTH_ID, CREATED_BY)
    VALUES (tmp.theme_id, tmp.dir_id, tmp.cluster_id, tmp.month_id, P_createdBy);

    OPEN P_ALL FOR SELECT 'INserted' AS msgs FROM dual;
END SP_TEST;
/

db<>fiddle

or cross apply or cross--join lateral with inline views:

CREATE OR REPLACE PROCEDURE SP_TEST (
    P_themeId IN VARCHAR2,
    P_dirId IN NUMBER,
    P_clusterId IN VARCHAR2,
    P_createdBy IN VARCHAR2,
    P_ALL OUT SYS_REFCURSOR 
) AS
    P_month varchar2(20) := '1,2';
BEGIN
    MERGE INTO TBL_TEST ta
    USING
    (
        SELECT t.theme_id, P_dirId as dir_id, c.cluster_id, m.month_id
        FROM (
            SELECT regexp_substr(P_themeId, '(.*?)(,|$)', 1, LEVEL, NULL, 1) AS theme_id
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_themeId, ',')   1
        ) t
        CROSS JOIN LATERAL (
            SELECT regexp_substr(P_clusterId, '(.*?)(,|$)', 1, LEVEL, NULL, 1) AS cluster_id
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_clusterId, ',')   1
        ) c
        CROSS JOIN LATERAL (
            SELECT regexp_substr(P_month, '(.*?)(,|$)', 1, LEVEL, NULL, 1) AS month_id
            FROM dual
            CONNECT BY LEVEL <= regexp_count(P_month, ',')   1
        ) m
    ) tmp
    ON (
        ta.theme_id = tmp.theme_id
        AND ta.dir_id = tmp.dir_id
        AND ta.cluster_id = tmp.cluster_id
        AND ta.month_id = tmp.month_id
    )
    WHEN NOT MATCHED THEN
    INSERT (THEME_ID, DIR_ID, CLUSTER_ID, MONTH_ID, CREATED_BY)
    VALUES (tmp.theme_id, tmp.dir_id, tmp.cluster_id, tmp.month_id, P_createdBy);

    OPEN P_ALL FOR SELECT 'INserted' AS msgs FROM dual;
END SP_TEST;
/

db<>fiddle

Either way that generates 12 rows with your sample strings:

You don't really need PL/SQL, but it looks like you want to have a PL/SQL wrapper around the merge.