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?

Community · Accepted Answer · 2017-05-23 10:33:38Z

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, 2017 at 10:33

CommunityBot

11 silver badge

answered Sep 17, 2014 at 4:06

Matt Mc

9,3376 gold badges57 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Over a year ago

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.

dandfra Over a year ago

It works, but I've found a better solution (in my opinion at least) here: stackoverflow.com/questions/13217600/…

Sandeep · Accepted Answer · 2014-09-17 16:00:57Z

0

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;

answered Sep 17, 2014 at 16:00

Sandeep

3492 gold badges3 silver badges9 bronze badges

Collectives™ on Stack Overflow

PLSQL Call Procedure If Exists Clause

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related