Oracle PL/SQL for loop: select query depending on condition

Question

I have a procedure body with a loop on the results of a query:

for src in (
    /* query A */
) loop
    /* 
     * loop body...
     */
end loop;

But I need to select between two or more different 'query A's depending on some condition. The queries are very different, but they all have the same resulting shape, namely one number column.

Is there a way to avoid having to repeat the loop code? Can I do something like this?

if /* some condition */ then
    query_A := /* ... */;
else
    query_A := /* ... */;
end if;

for src in (query_A) loop
    /* 
     * loop body...
     */
end loop;

I'm on Oracle 11g

Ergi Nushi · Accepted Answer · 2020-06-05 08:54:53Z

1

Yes, you can use cursor like so:

DECLARE
    query_a     VARCHAR2(256);
    cur         SYS_REFCURSOR;
BEGIN
    IF /* some condition */ THEN
        query_a := 'SELECT column FROM table WHERE col1 = condition';
    ELSE
        query_a := 'SELECT column FROM another_table WHERE col2 = condition';
    END IF;

    OPEN cur FOR query_a;
    LOOP
        FETCH cur INTO var1, var2...varN; --> depending on how many rows you are retrieving
        EXIT WHEN cur%notfound;
        /* 
         * loop body...
         */
    END LOOP;
END;

Just note that this method will degrade your performance if your table starts to grow.

edited Jun 5, 2020 at 8:54

answered Jun 4, 2020 at 15:58

Ergi Nushi

8731 gold badge6 silver badges18 bronze badges

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

4 Comments

Tobia Over a year ago

why is query_a a varchar? do I need to put the text of the query in there?

Ergi Nushi Over a year ago

Yes, you can build your query dynamically and then run it. I edited the answer, check it out.

Tobia Over a year ago

Thanks. I'll wait a few days to see if other solutions crop up, otherwise I'll accept yours.

Ergi Nushi Over a year ago

@Tobia Also take heed of the note.

Collectives™ on Stack Overflow

Oracle PL/SQL for loop: select query depending on condition

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related