Using PL/SQL Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as IF-THEN-ELSE, CASE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO.

This section contains the following topics:

Conditional Control With IF-THEN
Conditional Control With the CASE Statement
Iterative Control With LOOPs
Sequential Control With GOTO

Conditional Control With IF-THEN

Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN statement lets you run a sequence of statements conditionally. The forms of the statement can be IF-THEN, IF-THEN-ELSE, or IF-THEN-ELSEIF-ELSE. The IF clause checks a condition; the THEN clause defines what to do if the condition is true; and the ELSE clause defines what to do if the condition is false or null. Example: Using a Simple IF-THEN Statement in PL/SQL shows a simple use of the IF-THEN statement.

Using a Simple IF-THEN Statement in PL/SQL

DECLARE
  sal         NUMBER(8,2);
  bonus       NUMBER(6,2);
  hiredate    DATE;
  empid       NUMBER(6) := 128; -- use employee 120 for testing
BEGIN
-- retrieve the salary and the date that employee was hired, the date is checked
-- to calculate the amount of the bonus for the employee
  SELECT salary, hire_date INTO sal, hiredate FROM employees 
    WHERE employee_id = empid;
  IF hiredate > TO_DATE('01-JAN-00') THEN
     bonus := sal/20;
     DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
  END IF;
END;
/

Example: Using the IF-THEN-ELSEIF Statement in PL/SQL shows the use of IF-THEN-ELSEIF-ELSE to determine the salary raise an employee receives based on the hire date of the employee.

Using the IF-THEN-ELSEIF Statement in PL/SQL

DECLARE
  bonus    NUMBER(6,2);
  empid    NUMBER(6) := 120;
  hiredate DATE;
BEGIN
-- retrieve the date that employee was hired, the date is checked
-- to determine the amount of the bonus for the employee
  SELECT hire_date INTO hiredate FROM employees WHERE employee_id = empid;
  IF hiredate > TO_DATE('01-JAN-98') THEN
      bonus := 500;
   ELSIF hiredate > TO_DATE('01-JAN-96') THEN
      bonus := 1000;
   ELSE
      bonus := 1500;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END;
/

Conditional Control With the CASE Statement

To choose among several values or courses of action, you can use CASE constructs. The CASE expression evaluates a condition and returns a value for each case. The case statement evaluates a condition, and performs an action, such as an entire PL/SQL block, for each case. When possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements because the CASE statement is more readable and more efficient.

Example: Using the CASE-WHEN Statement in PL/SQL shows a simple CASE statement.

Using the CASE-WHEN Statement in PL/SQL

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/

Example: Using the IF-THEN_ELSE and CASE Statement in PL/SQL determines the salary raise an employee receives based on the current salary of the employee and the job ID. This complex example combines the CASE expression with IF-THEN-ELSE statements.

Using the IF-THEN_ELSE and CASE Statement in PL/SQL

DECLARE -- declare variables
   empid          NUMBER(6) := 115;
   jobid          VARCHAR2(10);
   sal            NUMBER(8,2);
   sal_raise      NUMBER(3,2); -- this is the rate of increase for the raise
BEGIN
-- retrieve the job ID and salary for the employee and 
-- assign the values to variables jobid and sal
  SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;
  CASE  -- determine the salary raise rate based on employee job ID
    WHEN jobid = 'PU_CLERK' THEN
        IF sal < 3000 THEN sal_raise := .08;
          ELSE sal_raise := .07;
        END IF;
    WHEN jobid = 'SH_CLERK' THEN
        IF sal < 4000 THEN sal_raise := .06;
          ELSE sal_raise := .05;
        END IF;
    WHEN jobid = 'ST_CLERK' THEN
        IF sal < 3500 THEN sal_raise := .04;
          ELSE sal_raise := .03;
        END IF;
    ELSE
     BEGIN
-- if no conditions met, then the following
       DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); 
     END;
  END CASE;
-- display the percent raise for the employee
  DBMS_OUTPUT.PUT_LINE('Percent salary raise for employee: ' || empid || ' is: '
                         || sal_raise );
END;
/

A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic.

Iterative Control With LOOPs

LOOP statements let you run a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence.

The FOR-LOOP statement lets you specify a range of integers, then run a sequence of statements once for each integer in the range. In Example: Using the FOR-LOOP in PL/SQL, the loop displays the number and the square of the number for numbers 1 to 10. Note that you do not have to declare or initialize the counter in the FOR-LOOP and any valid identifier can be used for the name, such as loop_counter.

Using the FOR-LOOP in PL/SQL

BEGIN
-- use a FOR loop to process a series of numbers
  FOR loop_counter IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter) 
                          || ' Square: ' || TO_CHAR(loop_counter**2));
  END LOOP;
END;
/

The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

In Example: Using WHILE-LOOP for Control in PL/SQL, the loop displays the number and the cube of the number while the number is less than or equal to 10.

Using WHILE-LOOP for Control in PL/SQL

DECLARE  -- declare variables
   i         NUMBER := 1; -- loop counter, initialize to one
   i_cubed   NUMBER;
BEGIN
-- use WHILE LOOP to process data
  WHILE i <= 10 LOOP
    i_cubed := i**3;
    DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i) 
                       || ' Cube: ' || TO_CHAR(i_cubed));
    i := i + 1;
  END LOOP;
END;
/

The EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In Example: Using the EXIT-WHEN Statement in PL/SQL, the loop completes when the value of total exceeds 25,000:

Using the EXIT-WHEN Statement in PL/SQL

DECLARE -- declare and assign values to variables
  total   NUMBER(9) := 0;
  counter NUMBER(6) := 0;
BEGIN
  LOOP
    counter := counter + 1; -- increment counter variable
    total := total + counter * counter;  -- compute total
    -- exit loop when condition is true
    EXIT WHEN total > 25000; -- LOOP until condition is met 
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) 
                       || ' Total: ' || TO_CHAR(total));  -- display results
END;
/

Sequential Control With GOTO

The GOTO statement lets you branch to a label unconditionally; however, you would usually try to avoid exiting a loop in this manner. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block.

Example: Using the GOTO Statement in PL/SQL shows the use of the GOTO statement in a loop that is testing for prime numbers. When a number can be divided into evenly (no remainder), then it is not a prime and the loop is immediately exited. Note the use of the SQL numeric function MOD to check for no (zero) remainder. See "Using Numeric Functions" for information about SQL numeric functions.

Using the GOTO Statement in PL/SQL

DECLARE  -- declare variables
  p        VARCHAR2(30);
  n        PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37
BEGIN
-- loop through divisors to determine if a prime number
  FOR j in 2..ROUND(SQRT(n)) 
  LOOP
    IF n MOD j = 0 THEN -- test for prime
      p := ' is NOT a prime number'; -- not a prime number
      GOTO print_now;
    END IF;
  END LOOP;
  p := ' is a prime number';
<<print_now>>
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);  -- display results
END;
/