CODEWITHSUNDEEP
for-loopplsqloracle11g
Tobia
Tobia

Reputation: 18811

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

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

Upvotes: 0

Views: 1122

Answers (1)

Ergi Nushi
Ergi Nushi

Reputation: 863

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.

Upvotes: 1

Related Questions