Select Case Statement with an Insert

Question

I am a little bit confused because of my SQL. I am trying to use the "Select Case Statement" to validate if a specific association exists, when existing it shouldn't do the insert statement. However, I can't use an insert statement in the "Then" clause. What am I doing wrong?

declare
cursor persons is
    (Select Person_id
     From PERSON_Table2Association_table p2c
     Where P2C.ID in
        (Select id
        From Association_table
        Where codepath like '&Old_Code_Path'
        and tree_id = 10
    );
new_codepath VARCHAR2(128) := '&New_Code_Path';
Begin
For person in persons
LOOP
select case
    when not exists(Select Person_id
                    From PERSON_Table2Association_table p2c
                    Where P2C.ID in
                        (Select ctn.id
                         From Association_table ctn
                         Where codepath like '&New_Code_Path2'
                         and tree_id = 10)
                         and person_id = person.person_id
                   )
    then (insert into PERSON_Table2Association_table -- here appears the error
          Values ((Select ENTITYID.getnextval from dual),
          0,
          (Select id From Association_table Where codepath = new_codepath and tree_id = 10),
          person.person_id,
          9999,
          null,
          'ACCESS')
         );
    else DBMS_OUTPUT.put_line('Person has already this association')
END LOOP;
COMMIT;
END;

I hope you can help me. Thank you.

Please provide sample data and desired results. Explain the logic you want to implement. Cursors are rarely necessary when querying databases. — Gordon Linoff, Sep 21 '20 at 12:07
Have a look on this question and you should get your answer : https://stackoverflow.com/questions/13503408/if-not-exists-in-function-plsql — Olivier Depriester, Sep 21 '20 at 12:09
Currently your CASE is a case *expression* within a Select list, but you probably want a case *statement* (remove the Select keyword) https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm . IMHO there's no need at all to use a cursor, it's a simple MERGE. — dnoeth, Sep 21 '20 at 12:16

score 1 · Answer 1 · edited Sep 21 '20 at 12:43

I believe the solution you are intending would look something like the code below

Some of the syntax issues that I corrected were

Missing parentheses at the end of your cursor declaration
Removed the SELECT in the beginning of the loop and changed it to an IF...THEN...ELSE
Added ; at then end of your DBMS_OUTPUT.PUT_LINE
Added END IF

    DECLARE
        CURSOR persons IS
            (SELECT Person_id
               FROM PERSON_Table2Association_table p2c
              WHERE P2C.ID IN (SELECT id
                                 FROM Association_table
                                WHERE codepath LIKE '&Old_Code_Path' AND tree_id = 10));
        new_codepath   VARCHAR2 (128) := '&New_Code_Path';
    BEGIN
        FOR person IN persons
        LOOP
            IF NOT EXISTS
                   (SELECT Person_id
                      FROM PERSON_Table2Association_table p2c
                     WHERE     P2C.ID IN (SELECT ctn.id
                                            FROM Association_table ctn
                                           WHERE codepath LIKE '&New_Code_Path2' AND tree_id = 10)
                           AND person_id = person.person_id)
            THEN
                INSERT INTO PERSON_Table2Association_table                     -- here appears the error
                     VALUES ((SELECT ENTITYID.getnextval FROM DUAL),
                             0,
                             (SELECT id
                                FROM Association_table
                               WHERE codepath = new_codepath AND tree_id = 10),
                             person.person_id,
                             9999,
                             NULL,
                             'ACCESS');
            ELSE
                DBMS_OUTPUT.put_line ('Person has already this association');
            END IF;
        END LOOP;
        COMMIT;
    END;

Select Case Statement with an Insert

1 Answers1