Oracle DBA FAQ - Understanding PL/SQL Language Basics

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

(Continued from previous part...)

What Are the Execution Control Statements?

PL/SQL supports three groups of execution control statements:

IF Statements - Conditionally executes a block of statements.
CASE Statements - Selectively executes a block of statements.
LOOP Statements - Repeatedly executes a block of statements.
GOTO Statements - Unconditional changes the execution flow to a specified statement.

The script below shows some execution control statements:

DECLARE
  total NUMBER;
BEGIN
  total := 0;
  LOOP
    total := total+1;
    IF total >= 10 THEN
      GOTO print;
    END IF;
  END LOOP;
  <>
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;

This script should print this:

Total counts: 10

How To Use "IF" Statements on Multiple Conditions?

If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements:

DECLARE
  day VARCHAR2;
BEGIN
  day := 'SUNDAY';
  IF day = 'THURSDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Checking log files.');
  ELSIF day = 'TUESDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Helping developers.');
  ELSIF day = 'FRIDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Reading some papers.');
  END IF;
END;

This script should print this:

Reading some papers.

How To Use "WHILE" Statements?

If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:

DECLARE
  total NUMBER;
BEGIN
  total := 0;
  WHILE total < 10 LOOP
    total := total+1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;

This script should print this:

Total counts: 10

How To Use "FOR" Statements?

If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:

DECLARE
  total NUMBER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(total));
END;

Note that temporary variable "i" used in the FOR loop needs no declaration. This script should print this:

Total: 55

(Continued on next part...)

Part: 1 2 3 4 5

More...