PL/SQL Control Structures

Control structures are among the PL/SQL extensions to SQL. TimesTen supports the same control structures as Oracle Database.

The following control structures are discussed in this section.

Conditional Control

The IF-THEN-ELSE and CASE constructs are examples of conditional control.

In the example below, an IF-THEN-ELSE construct is used to determine the salary raise of an employee based on the current salary. The CASE construct is also used to choose the course of action to take based on the job_id of the employee.

Command> DECLARE
           jobid employees.job_id%TYPE;
           empid employees.employee_id%TYPE := 115;
           sal employees.salary%TYPE;
           sal_raise NUMBER(3,2);
         BEGIN
           SELECT job_id, salary INTO jobid, sal from employees
             WHERE employee_id = empid;
           CASE
             WHEN jobid = 'PU_CLERK' THEN
               IF sal < 3000 THEN sal_raise := .12;
               ELSE sal_raise := .09;
               END IF;
             WHEN jobid = 'SH_CLERK' THEN
               IF sal < 4000 THEN sal_raise := .11;
               ELSE sal_raise := .08;
               END IF;
             WHEN jobid = 'ST_CLERK' THEN
               IF sal < 3500 THEN sal_raise := .10;
               ELSE sal_raise := .07;
               END IF;
             ELSE
               BEGIN
                 DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
               END;
           END CASE;
         DBMS_OUTPUT.PUT_LINE ('Original salary ' || sal);
         -- Update
         UPDATE employees SET salary = salary + salary * sal_raise
         WHERE employee_id = empid;
         END;
         /
Original salary 3100
 
PL/SQL procedure successfully completed.

Iterative Control

An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition is true. Loop constructs are used to perform iterative operations.

There are three loop types:

Basic loop
FOR loop
WHILE loop

The basic loop performs repetitive actions without overall conditions. The FOR loop performs iterative actions based on a count. The WHILE loops perform iterative actions based on a condition.

This example uses a WHILE loop:

Command> CREATE TABLE temp (tempid NUMBER(6),
         tempsal NUMBER(8,2),
         tempname VARCHAR2(25));
Command> DECLARE
           sal employees.salary%TYPE := 0;
           mgr_id employees.manager_id%TYPE;
           lname employees.last_name%TYPE;
           starting_empid employees.employee_id%TYPE := 120;
         BEGIN
           SELECT manager_id INTO mgr_id
             FROM employees
             WHERE employee_id = starting_empid;
           WHILE sal <= 15000 LOOP -- loop until sal > 15000
             SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
               FROM employees WHERE employee_id = mgr_id;
           END LOOP;
           INSERT INTO temp VALUES (NULL, sal, lname);  -- insert NULL for tempid
           COMMIT;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             INSERT INTO temp VALUES (NULL, NULL, 'Not found');  -- insert NULLs
             COMMIT;
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM temp;
< <NULL>, 24000, King >
1 row found.

CONTINUE Statement

The CONTINUE statement enables you to transfer control within a loop back to a new iteration.

In this example, the first v_total assignment is executed for each of the 10 iterations of the loop. The second v_total assignment is executed for the first five iterations of the loop. The CONTINUE statement transfers control within a loop back to a new iteration, so for the last five iterations of the loop, the second v_total assignment is not executed. The end v_total value is 70.

Command> DECLARE
           v_total  SIMPLE_INTEGER := 0;
         BEGIN
           FOR i IN 1..10 LOOP
             v_total := v_total + i;
             DBMS_OUTPUT.PUT_LINE ('Total is : ' || v_total);
             CONTINUE WHEN i > 5;
             v_total := v_total + i;
             DBMS_OUTPUT.PUT_LINE ('Out of loop  Total is: ' || v_total);
           END LOOP;
         END;
         /
Total is : 1
Out of loop  Total is: 2
Total is : 4
Out of loop  Total is: 6
Total is : 9
Out of loop  Total is: 12
Total is : 16
Out of loop  Total is: 20
Total is : 25
Out of loop  Total is: 30
Total is : 36
Total is : 43
Total is : 51
Total is : 60
Total is : 70

PL/SQL procedure successfully completed.