PLSQL Call Procedure If Exists Clause

Question

I'm using Oracle 9i.

Cue pseudo-code!

I have Stored Procedure A:

PROCEDURE stored_proc_a
IS
BEGIN
    insert into...
END;

Then, I have Stored Procedure B:

PROCEDURE stored_proc_b
IS
BEGIN
    stored_proc_a
    WHERE NOT EXISTS (SELECT * FROM...);
END;

As you can see from my pseudo-code, I would like to call procedure A from procedure B, if a given row does not exist in a table.

I can't find any documentation that would suggest that the WHERE EXISTS clause can be used with a procedure call (the examples show its use with INSERT, UPDATE and DELETE).

Can I use WHERE EXISTS with a procedure call, and if not, what would be the correct code to do a procedure call based on the absence of a particular record in a table?

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

The correct way of doing this is the following:

PROCEDURE stored_proc_b
    IS
        num_rows number;
    BEGIN
        select COUNT(*)  into num_rows
            FROM my_table
            WHERE my_table.xyz = 123; -- (whatever is appropriate)

        if num_rows < 1
        then
            stored_proc_a;
        end if;
    END;

Figured this out thanks to Nicholas Krasnov and WBAR for their info on other posts.

edited May 23 '17 at 10:33

Community

1
1

answered Sep 17 '14 at 04:06

Matt Mc

8,882
6
53
89

Minor nitpick; I'd add an additional condition WHERE ROWNUM < 2 to your SELECT statement as you only need to determine existence you only need to look for 1 row. – Ben Sep 17 '14 at 05:24
It works, but I've found a better solution (in my opinion at least) here: https://stackoverflow.com/questions/13217600/if-exists-condition-not-working-with-plsql – dandfra Dec 14 '18 at 12:51

score 0 · Answer 2 · answered Sep 17 '14 at 16:00

Another way of achieving the same, in case you you want to call it for multiple rows and want to use data from Table in procedure B-

PROCEDURE stored_proc_b
IS
BEGIN
    FOR rec IN (SELECT COL1 FROM <<TABLE1>> T1 WHERE NOT EXISTS (SELECT * FROM <<TABLE2>> T2...WHERE T1.JOIN_COL = T2.JOIN_COL))
    LOOP
        stored_proc_a;
    END LOOP;    
EXCEPTION
    WHEN OTHERS THEN
    rollback;
END;

PLSQL Call Procedure If Exists Clause

2 Answers2