Oracle PL/SQL – Replace GOTO’s with LOOP’s

GOTO’s should not be part of any modern programming language (IMHO). They are bad for so many reasons and should be avoided at all costs. I guess they were carried over from the Assembly and later the Basic language days. Good code (procedure/function/block) should have a single entry point and a single exit point to make it readable and maintainable. I often see GOTO’s in code database developers put out and I cringe!

In any case, if you have GOTO’s in your PL/SQL code and need to replace them with LOOP’s which are more readable, I show how to do this below with a simple “BEFORE” and “AFTER” example with relevant replacements highlighted.

BEFORE – Using GOTO

FOR vcAccounting IN cAccounting
LOOP
    BEGIN
        IF (NOT SOMETHING)
            GOTO L_ACTUALS;
        END IF

        IF (SOMETHINGELSE)
            --PROCESS...
        ELSE
            GOTO L_ACTUALS;
        END IF

        BEGIN
            --MORE PROCESSING
        END;

        << L_ACTUALS >> NULL;
    EXCEPTION
    WHEN xNonAcct_Err THEN
        ...
    END;
    IF vStatRecWritten THEN
        ...
    END IF;
END LOOP; -- vcAccounting IN cAccounting

AFTER – Using LOOP

FOR vcAccounting IN cAccounting
LOOP
    BEGIN
        WHILE TRUE LOOP
            IF (NOT SOMETHING)
                EXIT;
            END IF

            IF (SOMETHINGELSE)
                --PROCESS...
            ELSE
                EXIT;
            END IF

            BEGIN
                --MORE PROCESSING
            END;

            EXIT;
        END LOOP;

    EXCEPTION
    WHEN xNonAcct_Err THEN
        ...
    END;
    IF vStatRecWritten THEN
        ...
    END IF;
END LOOP; -- vcAccounting IN cAccounting

You see how simple that was? Now, you don’t have an excuse to use GOTO’s in PL/SQL (or anywhere else)!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s