Monday, December 09, 2013

PL/SQL GOTO Statement

I remember using the GO TO statement when I used to program in COBOL.

PL/SQL has a GOTO statement as well, which directs control to a label enclosed by a << and a >>. There must always be at least one statement after the label. On this occasion, I did not want to do anything at that point so I used a NULL; statement.

The example was run on Oracle 11.2: 

SQL> DECLARE
  2    CURSOR C1 IS
  3      SELECT BYTES
  4      FROM DBA_SEGMENTS
  5      WHERE BYTES IS NOT NULL;
  6    SMALL NUMBER;
  7    MEDIUM NUMBER;
  8    LARGE NUMBER;
  9  BEGIN
 10    SMALL := 0;
 11    MEDIUM := 0;
 12    LARGE := 0;
 13    FOR X IN C1
 14    LOOP
 15      IF X.BYTES < 1000000 THEN
 16        SMALL := SMALL + 1;
 17        GOTO DONE;
 18      END IF;
 19      IF X.BYTES < 10000000 THEN
 20        MEDIUM := MEDIUM + 1;
 21        GOTO DONE;
 22      END IF;
 23      LARGE := LARGE + 1;
 24      <<DONE>>
 25      NULL;
 26    END LOOP;
 27    DBMS_OUTPUT.PUT_LINE('Small Segments: '||SMALL);
 28    DBMS_OUTPUT.PUT_LINE('Medium Segments: '||MEDIUM);
 29    DBMS_OUTPUT.PUT_LINE('Large Segments: '||LARGE);
 30  END;
 31  /
Small Segments: 5606
Medium Segments: 1350
Large Segments: 545
 
PL/SQL procedure successfully completed.
 
SQL>

No comments: