Chapter 3 Control Structures

Based on PL/SQL User s Guide

Any programming language supports the three type of control structures; sequence, selection and iteration. SQL is database language which support only sequence structure. PL/SQL as procedural language support all three plus the interface to SQL.

3.1 Selective Control Statements

The following selection statements are supported in PL/SQL: IF-THEN, IF-THEN-ELSE. OF-THEN-ELSEIF-..-ELSE.

Syntax:

IF boolean_expression THEN

PL/SQL or SQL statements

END IF ;

IF boolean_expression THEN

PL/SQL or SQL statements

ELSE

PL/SQL or SQL statements

END IF ;

IF boolean_expression THEN

PL/SQL or SQL statements

ELSEIF boolean_expression THEN

PL/SQL or SQL statements

ELSEIF boolean_expression THEN

PL/SQL or SQL statements

ELSE

PL/SQL or SQL statements

END IF ;

Examples:

(1) Write procedure that will list top N salaries in the employee list.

To run the stored procedure from SQL/PLUS do call it from PL/SQL block:

-- begin top_n_salaries(3) ; end ;

Note that the following stored procedure is for demo only. Actually, you should stored the records in array instead of print them on screen.

CREATE OR REPLACE PROCEDURE TOP_N_SALARIES( N NUMBER ) AS

cnt NUMBER := 0;

CURSOR emp_c IS

SELECT * from emp ORDER BY sal DESC;

BEGIN

FOR c in emp_c LOOP

IF cnt < n THEN

DBMS_OUTPUT.put_line(RPAD(c.ename, 15) || ' ' || c.sal );

cnt := cnt + 1;

ELSE

exit ;

END IF;

END LOOP;

END ;

/

(2). Calculate salary with bonus. The bonus amount is based on the

-- Give bonus based on the salary:

-- SALARY BONUS

-->3000 1000

-->1500 500

--others 100

-- Notice that ELSEIF is not supported on the system the

-- PL/SQL block is tested.

DECLARE

bonus NUMBER ;

salary NUMBER ;

CURSOR emp_cur IS

SELECT * FROM emp ;

BEGIN

FOR c in emp_cur LOOP

salary := c.sal ;

IF salary > 3000 THEN

bonus := 1000;

ELSE IF (salary > 1500) THEN

bonus := 500;

ELSE

bonus := 100;

END IF ;

END IF;

DBMS_OUTPUT.put_line( RPAD(c.ename, 15) || ' ' || TO_CHAR(c.sal, '$9,999') || '

|| bonus );

END LOOP;

END ;

/

3.2 Iterative Control Statements (LOOP Statements)

There are three loop statement in PL/SQL; LOOP, WHILE-LOOP, and FOR-LOOP. To exit a loop, EXIT or EXIT WHEN can be used.

Note that if you have a lot output, you may see buffer overflow message when using DBMS_OUTPUT. The following command will help you overcome the problem:

Many more variables that Set command can set.

SQL> SET SERVEROUTPUT ON SIZE 5000

It will allow you to print message from PL/SQL with DBMS_OUTPUT package. There is a buffer size limit (default to 2000 byte). If the number is exceeded, error ORA-20000 ORU-10027, Buffer overflow, limit of 2000 byte.

Use the following command to show the server output setting:

SHOW SERVEROUTPUT

to see mode, buffer size and format of server output.

1. EXIST and EXIT-WHEN statements

The unconditional and condition exit statements will exit loop (any of the three kinds of loops in PL/SQL). The control is passed to the next statement follows the loop in EXIT or EXIT WHEN statement is called. To end the PL/SQL block, use RETURN statement.

Syntax:

EXIT ;

EXIT WHEN boolean_expression ;

Example:

DECLARE

I NUMBER := 0 ;

BEGIN

FOR c in emp_c LOOP

EXIT WHEN cnt >= n ;

DBMS_OUTPUT.put_line(RPAD(c.ename, 15) || ' || c.sal );

cnt := cnt + 1;

END LOOP;

END ;

2. LOOP statement:

Syntax:

LOOP -- unconditional infinite loop.

Sequence of PL/SQL and SQL statements

-- there should be EXIT or EXIT-WHEN statement

END LOOP ;

3. WHILE-LOOP Statement:

Syntax:

WHILE boolean_express LOOP

PL/SQL and SQL statements

END LOOP;

Before each execution of statements inside loop, the boolean expression is evaluated first, and if the result true the loop will continue.

Example: Find employees with the lowest salaries that make up 3000 or more.

DECLARE

cnt NUMBER := 0;

total FLOAT := 0;

s FLOAT ;

CURSOR c IS

select sal from emp order by sal ;

BEGIN

open c ;

while total < 3000 LOOP

fetch c into s ;

total := total + s;

cnt := cnt + 1;

end loop;

DBMS_OUTPUT.put_line( cnt || ' salaries sum up to ' || 3000 );

close c;

END ;

4. FOR-LOOP

LOOP and WHILE-LOOP are used in the situation where the number of loops is unknown. The FOR-LOOP is used in cases that the number of iterations is known.

Syntax:

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP

PL/SQL and SQL statements;

END LOOP ;

Notice that

1. Loop control variable counter is implicit defined as local variable of INTEGER type. You don t have defined it explicitly.

2. No matter the option RESERVE is used or not, the relationshisp, Lower_bound <= upper_bound, must hold for loop to iterate at least once.

3. When RESERVE is used, the counter is initialized with upper_bound and is decreased 1 at a time.

4. The lower and upper bound can be literal, variable and expression. However, they must be evaluated to an integer.

Examples:

DECLARE

I NUMBER : = 7 ;

BEGIN

-- 15 to 5 are printed

FOR I IN REVERSE 5 .. 15 LOOP

DBMS.OUTPUT.put_line ( I ) ;

END LOOP;

DBMS.OUTPUT.put_line ( I ) ; -- 7 is printed.

END ;

BEGIN

SELECT COUNT( empno ) INTO emp_count FROM emp

FOR I IN 1..emp_count LOOP

END LOOP ;

END ;

Scope of For Loop control Variable

1 The control variable counter is defined within the FOR-LOOP. It is undefined outside the loop and is not accessible therefore. If a variable is defined outside of the loop has the same name as loop control variable, the outside variable is not accessible inside the loop.

2 To refer the variable defined the outside the loop with the same name as loop control variable, use the following schema

<<main>> -- define label main

DECLARE

I NUMBER : = 7 ;

BEGIN

-- 15 to 5 are printed

FOR I IN REVERSE 5 .. 15 LOOP

-- access variable defined outside of loop

DBMS.OUTPUT.put_line ( I, main.I ) ;

END LOOP;

DBMS.OUTPUT.put_line ( I ) ; -- 7 is printed.

END ;

3. The same scope is applied to nested loop. That is following nested loop is legal:

<<outer>> -- label.

FOR I IN 1..10 LOOP

FOR I IN 1..10 LOOP

K = I * outer.I ;

END LOOP;

END LOOP ;

Using EXIT to exit FOR LOOP:

(1)

FOR I 1 .. 10 LOOP

FETCH c INTO emp_rec ;

EXIT WHEN c%NOTFOUND ;

END LOOP ;

(2) Exit will exit one loop you want to exit the more than one layer of loop, use a label to mark the loop and use the label in EXIT statement (including EXIT WHEN statement):

<<outer>>

FOR I IN 1..10 LOOP

FOR J in 20 .. 30 LOOP

EXIT outer ; -- exit nested loop!

END LOOP;

END LOOP outer ;

3.3 Sequential Control Statements: GOTO and NULL Statements

GOTO statement is hardly used in many program. Use it only if that GOTO will simplify the program. Usually, if you want go from deep nested loop to loop several level back, use GOTO statement.

Remarks:

(1) The label must be placed before an executable statement or a PL/SQL block

(2) GOTO can go to inner block to outside block

(3) GOTO cannot go into IF statement nor loop statement.

(4) GOTO cannot go from outer loop into inner loop.

(5) GOTO cannot go outside of sub program (procedure or function).

(6) GOTO cannot jump from exception part to executable part.

(1) The GOTO Statement

Syntax:

GOTO update_emp ;

<<update_emp>>

UPDATE emp set sal = 1000 where empno = eno ;

The label must be placed before an executable statement or a PL/SQL block. It cannot be replace before non-executable statement such as END IF, END LOOP. The following is illegal.

BEGIN

FOR I IN 1 .. 10 LOOP

GOTO finish ; -- syntax error.

END LOOP;

<<finish>>

END ;

To fix the problem, add NULL statement after the label finish. The NULL is a executable statement.

(2) NULL Statement

NULL statement is an executable statement that does nothing but transferring control to next statement. The NULL statement can be used to improve the readability.

Example:

EXCEPTION

WHEN ZERO_DIVIDE THEN

ROLLBACK ;

WHEN out_of_stock THEN

INSERT INTO   

WHEN OTHERS THEN

NULL ;

END ;

IF bool_exp THEN

Statements

ELSE

NULL ;

END IF ;