Sunday, September 04, 2011

IF Statement

Tested on an Oracle 9 database.
You can incorporate conditional control in PL/SQL with IF / ELSE / END IF.
1E6 and 1E7 mean 1 multiplied by 10^6 and 10^7 respectively:

SQL> SET ECHO ON SERVEROUTPUT ON TAB OFF TRIMSPOOL ON
SQL> DECLARE
  2  CURSOR C1 IS
  3    SELECT BYTES FROM DBA_SEGMENTS
  4    WHERE BYTES IS NOT NULL;
  5  SMALL NUMBER;
  6  MEDIUM NUMBER;
  7  LARGE NUMBER;
  8  BEGIN
  9  SMALL := 0;
 10  MEDIUM := 0;
 11  LARGE := 0;
 12  FOR X IN C1
 13  LOOP
 14    IF X.BYTES < 1E6 THEN
 15      SMALL := SMALL + 1;
 16    ELSE
 17      IF X.BYTES >= 1E7 THEN
 18        LARGE := LARGE + 1;
 19      ELSE
 20        MEDIUM := MEDIUM + 1;
 21      END IF;
 22    END IF;
 23  END LOOP;
 24  DBMS_OUTPUT.PUT_LINE('Small Segments: '||SMALL);
 25  DBMS_OUTPUT.PUT_LINE('Medium Segments: '||MEDIUM);
 26  DBMS_OUTPUT.PUT_LINE('Large Segments: '||LARGE);
 27  END;
 28  /
Small Segments: 3113
Medium Segments: 1596
Large Segments: 608

PL/SQL procedure successfully completed.

SQL>


This example uses ELSIF instead of ELSE ... IF:

SQL> DECLARE
  2  CURSOR C1 IS
  3    SELECT BYTES FROM DBA_SEGMENTS
  4    WHERE BYTES IS NOT NULL;
  5  SMALL NUMBER;
  6  MEDIUM NUMBER;
  7  LARGE NUMBER;
  8  BEGIN
  9  SMALL := 0;
 10  MEDIUM := 0;
 11  LARGE := 0;
 12  FOR X IN C1
 13  LOOP
 14    IF X.BYTES < 1E6 THEN
 15      SMALL := SMALL + 1;
 16    ELSIF X.BYTES >= 1E7 THEN
 17      LARGE := LARGE + 1;
 18    ELSE
 19      MEDIUM := MEDIUM + 1;
 20    END IF;
 21  END LOOP;
 22  DBMS_OUTPUT.PUT_LINE('Small Segments: '||SMALL);
 23  DBMS_OUTPUT.PUT_LINE('Medium Segments: '||MEDIUM);
 24  DBMS_OUTPUT.PUT_LINE('Large Segments: '||LARGE);
 25  END;
 26  /
Small Segments: 3113
Medium Segments: 1596
Large Segments: 608

PL/SQL procedure successfully completed.

SQL>

No comments:

Post a Comment