Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 4.5 Control Structures

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

4.5 Control Structures

The programming statements in SPL that make it a full procedural complement to SQL are described in this section.

4.5.1 IF Statement

IF statements let you execute commands based on certain conditions. SPL has four forms of IF:

    IF ... THEN

    IF ... THEN ... ELSE

    IF ... THEN ... ELSE IF

    IF ... THEN ... ELSIF ... THEN ... ELSE

4.5.1.1 IF-THEN

IF boolean-expression THEN
  statements
END IF;

IF-THEN statements are the simplest form of IF. The statements between THEN and END IF will be executed if the condition is TRUE. Otherwise, they are skipped.

In the following example an IF-THEN statement is used to test and display employees who have a commission.

DECLARE
    v_empno         emp.empno%TYPE;
    v_comm          emp.comm%TYPE;
    CURSOR emp_cursor IS SELECT empno, comm FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    COMM');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_comm;
        EXIT WHEN emp_cursor%NOTFOUND;
--
--  Test whether or not the employee gets a commission
--
        IF v_comm IS NOT NULL AND v_comm > 0 THEN
            DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
            TO_CHAR(v_comm,'$99999.99'));
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    COMM
-----    -------
7499     $300.00
7521     $500.00
7654    $1400.00

4.5.1.2 IF-THEN-ELSE

IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition evaluates to false.

The previous example is modified so an IF-THEN-ELSE statement is used to display the text Non-commission if the employee does not get a commission.

DECLARE
    v_empno         emp.empno%TYPE;
    v_comm          emp.comm%TYPE;
    CURSOR emp_cursor IS SELECT empno, comm FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    COMM');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_comm;
        EXIT WHEN emp_cursor%NOTFOUND;
--
--  Test whether or not the employee gets a commission
--
        IF v_comm IS NOT NULL AND v_comm > 0 THEN
            DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
            TO_CHAR(v_comm,'$99999.99'));
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || 'Non-commission');
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    COMM
-----    -------
7369     Non-commission
7499  $   300.00
7521  $   500.00
7566     Non-commission
7654  $  1400.00
7698     Non-commission
7782     Non-commission
7788     Non-commission
7839     Non-commission
7844     Non-commission
7876     Non-commission
7900     Non-commission
7902     Non-commission
7934     Non-commission

4.5.1.3 IF-THEN-ELSE IF

IF statements can be nested so that alternative IF statements can be invoked once it is determined whether or not the conditional of an outer IF statement is TRUE or FALSE.

In the following example the outer IF-THEN-ELSE statement tests whether or not an employee has a commission. The inner IF-THEN-ELSE statements then test whether the employee’s total compensation exceeds or is less than the company average.

DECLARE
    v_empno         emp.empno%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_avg           NUMBER(7,2);
    CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp; 
BEGIN
--
--  Calculate the average yearly compensation in the company
--
    SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
    DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||
        TO_CHAR(v_avg,'$999,999.99'));
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    YEARLY COMP');
    DBMS_OUTPUT.PUT_LINE('-----    -----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_sal, v_comm;
        EXIT WHEN emp_cursor%NOTFOUND;
--
--  Test whether or not the employee gets a commission
--
        IF v_comm IS NOT NULL AND v_comm > 0 THEN
--
--  Test if the employee's compensation with commission exceeds the average
--
            IF (v_sal + v_comm) * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
                    TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||
                    ' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
                    TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||
                    ' Below Average');
            END IF;
        ELSE
--
--  Test if the employee's compensation without commission exceeds the average
--
            IF v_sal * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
                    TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || '  ' ||
                    TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
            END IF;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;

Note: The logic in this program can be simplified considerably by calculating the employee’s yearly compensation using the NVL function within the SELECT command of the cursor declaration, however, the purpose of this example is to demonstrate how IF statements can be used.

The following is the output from this program.

Average Yearly Compensation: $  53,528.57
EMPNO    YEARLY COMP
-----    -----------
7369  $  19,200.00 Below Average
7499  $  45,600.00 Below Average
7521  $  42,000.00 Below Average
7566  $  71,400.00 Exceeds Average
7654  $  63,600.00 Exceeds Average
7698  $  68,400.00 Exceeds Average
7782  $  58,800.00 Exceeds Average
7788  $  72,000.00 Exceeds Average
7839  $ 120,000.00 Exceeds Average
7844  $  36,000.00 Below Average
7876  $  26,400.00 Below Average
7900  $  22,800.00 Below Average
7902  $  72,000.00 Exceeds Average
7934  $  31,200.00 Below Average

When you use this form, you are actually nesting an IF statement inside the ELSE part of an outer IF statement. Thus you need one END IF statement for each nested IF and one for the parent IF-ELSE.

4.5.1.4 IF-THEN-ELSIF-ELSE

  IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements ] ...]
[ ELSE
    statements ]
  END IF;

IF-THEN-ELSIF-ELSE provides a method of checking many alternatives in one statement. Formally it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF is needed.

The following example uses an IF-THEN-ELSIF-ELSE statement to count the number of employees by compensation ranges of $25,000.

DECLARE
    v_empno         emp.empno%TYPE;
    v_comp          NUMBER(8,2);
    v_lt_25K        SMALLINT := 0;
    v_25K_50K       SMALLINT := 0;
    v_50K_75K       SMALLINT := 0;
    v_75K_100K      SMALLINT := 0;
    v_ge_100K       SMALLINT := 0;
    CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp; 
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_empno, v_comp;
        EXIT WHEN emp_cursor%NOTFOUND;
        IF v_comp < 25000 THEN
            v_lt_25K := v_lt_25K + 1;
        ELSIF v_comp < 50000 THEN
            v_25K_50K := v_25K_50K + 1;
        ELSIF v_comp < 75000 THEN
            v_50K_75K := v_50K_75K + 1;
        ELSIF v_comp < 100000 THEN
            v_75K_100K := v_75K_100K + 1;
        ELSE
            v_ge_100K := v_ge_100K + 1;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
    DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
    DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
    DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
    DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
    DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K);
END;

The following is the output from this program.

Number of employees by yearly compensation
Less than 25,000 : 2
25,000 - 49,9999 : 5
50,000 - 74,9999 : 6
75,000 - 99,9999 : 0
100,000 and over : 1

4.5.2 RETURN Statement

The RETURN statement terminates the current function, procedure or anonymous block and returns control to the caller.

There are two forms of the RETURN Statement. The first form of the RETURN statement is used to terminate a procedure or function that returns void. The syntax of the first form is:

        RETURN;

The second form of RETURN returns a value to the caller. The syntax of the second form of the RETURN statement is:

        RETURN expression;

expression must evaluate to the same data type as the return type of the function.

The following example uses the RETURN statement returns a value to the caller:

CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;

4.5.3 GOTO Statement

The GOTO statement causes the point of execution to jump to the statement with the specified label. The syntax of a GOTO statement is:

        GOTO label

label is a name assigned to an executable statement. label must be unique within the scope of the function, procedure or anonymous block.

To label a statement, use the syntax:

<<label>> statement

statement is the point of execution that the program jumps to.

You can label assignment statements, any SQL statement (like INSERT, UPDATE, CREATE, etc.) and selected procedural language statements. The procedural language statements that can be labeled are:

      IF

      EXIT

      RETURN

      RAISE

      EXECUTE

      PERFORM

      GET DIAGNOSTICS

      OPEN

      FETCH

      MOVE

      CLOSE

      NULL

      COMMIT

      ROLLBACK

      GOTO

      CASE

      LOOP

      WHILE

      FOR

GOTO statements cannot transfer control into a conditional block or sub-block, but can transfer control from a conditional block or sub-block.

The following example verifies that an employee record contains a name, job description, and employee hire date; if any piece of information is missing, a GOTO statement transfers the point of execution to a statement that prints a message that the employee is not valid.

CREATE OR REPLACE PROCEDURE verify_emp (
    p_empno         NUMBER
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_hiredate      emp.hiredate%TYPE;
BEGIN
    SELECT ename, job, hiredate
        INTO v_ename, v_job, v_hiredate FROM emp
        WHERE empno = p_empno;
    IF v_ename IS NULL THEN
        GOTO invalid_emp;
    END IF;
    IF v_job IS NULL THEN
        GOTO invalid_emp;
    END IF;
    IF v_hiredate IS NULL THEN
        GOTO invalid_emp;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' validated without errors.');
    RETURN;
    <<invalid_emp>> DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' is not a valid employee.');
END;

GOTO statements have the following restrictions:

    ● A GOTO statement cannot jump to a declaration.

    ● A GOTO statement cannot transfer control to another function or procedure.

    ● A label should not be placed at the end of a block, function or procedure.

4.5.4 CASE Expression

The CASE expression returns a value that is substituted where the CASE expression is located within an expression.

There are two formats of the CASE expression - one that is called a searched CASE and the other that uses a selector.

4.5.4.1 Selector CASE Expression

The selector CASE expression attempts to match an expression called the selector to the expression specified in one or more WHEN clauses. result is an expression that is type-compatible in the context where the CASE expression is used. If a match is found, the value given in the corresponding THEN clause is returned by the CASE expression. If there are no matches, the value following ELSE is returned. If ELSE is omitted, the CASE expression returns null.

CASE selector-expression
  WHEN match-expression THEN
    result
[ WHEN match-expression THEN
    result
[ WHEN match-expression THEN
    result ] ...]
[ ELSE
    result ]
END;

match-expression is evaluated in the order in which it appears within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first match-expression is encountered that equals selector-expression, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of match-expression equals selector-expression then result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.

The following example uses a selector CASE expression to assign the department name to a variable based upon the department number.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE v_deptno
                WHEN 10 THEN 'Accounting'
                WHEN 20 THEN 'Research'
                WHEN 30 THEN 'Sales'
                WHEN 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    ENAME     DEPTNO    DNAME
-----    -------   ------    ----------
7369     SMITH       20      Research
7499     ALLEN       30      Sales
7521     WARD        30      Sales
7566     JONES       20      Research
7654     MARTIN      30      Sales
7698     BLAKE       30      Sales
7782     CLARK       10      Accounting
7788     SCOTT       20      Research
7839     KING        10      Accounting
7844     TURNER      30      Sales
7876     ADAMS       20      Research
7900     JAMES       30      Sales
7902     FORD        20      Research
7934     MILLER      10      Accounting

4.5.4.2 Searched CASE Expression

A searched CASE expression uses one or more Boolean expressions to determine the resulting value to return.

CASE WHEN boolean-expression THEN
    result
[ WHEN boolean-expression THEN
    result
 [ WHEN boolean-expression THEN
    result ] ...]
[ ELSE
    result ]
END;

boolean-expression is evaluated in the order in which it appears within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first boolean-expression is encountered that evaluates to TRUE, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of boolean-expression evaluates to true then result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.

The following example uses a searched CASE expression to assign the department name to a variable based upon the department number.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE
                WHEN v_deptno = 10 THEN 'Accounting'
                WHEN v_deptno = 20 THEN 'Research'
                WHEN v_deptno = 30 THEN 'Sales'
                WHEN v_deptno = 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    ENAME     DEPTNO    DNAME
-----    -------   ------    ----------
7369     SMITH       20      Research
7499     ALLEN       30      Sales
7521     WARD        30      Sales
7566     JONES       20      Research
7654     MARTIN      30      Sales
7698     BLAKE       30      Sales
7782     CLARK       10      Accounting
7788     SCOTT       20      Research
7839     KING        10      Accounting
7844     TURNER      30      Sales
7876     ADAMS       20      Research
7900     JAMES       30      Sales
7902     FORD        20      Research
7934     MILLER      10      Accounting

4.5.5 CASE Statement

The CASE statement executes a set of one or more statements when a specified search condition is TRUE. The CASE statement is a stand-alone statement in itself while the previously discussed CASE expression must appear as part of an expression.

There are two formats of the CASE statement - one that is called a searched CASE and the other that uses a selector.

4.5.5.1 Selector CASE Statement

The selector CASE statement attempts to match an expression called the selector to the expression specified in one or more WHEN clauses. When a match is found one or more corresponding statements are executed.

  CASE selector-expression
  WHEN match-expression THEN
    statements
[ WHEN match-expression THEN
    statements
[ WHEN match-expression THEN
    statements ] ...]
[ ELSE
    statements ]
  END CASE;

selector-expression returns a value type-compatible with each match-expression. match-expression is evaluated in the order in which it appears within the CASE statement. statements are one or more SPL statements, each terminated by a semi-colon. When the value of selector-expression equals the first match-expression, the statement(s) in the corresponding THEN clause are executed and control continues following the END CASE keywords. If there are no matches, the statement(s) following ELSE are executed. If there are no matches and there is no ELSE clause, an exception is thrown.

The following example uses a selector CASE statement to assign a department name and location to a variable based upon the department number.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    v_loc           dept.loc%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME     '
        || '     LOC');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------'
        || '     ---------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        CASE v_deptno
            WHEN 10 THEN v_dname := 'Accounting';
                         v_loc   := 'New York';
            WHEN 20 THEN v_dname := 'Research';
                         v_loc   := 'Dallas';
            WHEN 30 THEN v_dname := 'Sales';
                         v_loc   := 'Chicago';
            WHEN 40 THEN v_dname := 'Operations';
                         v_loc   := 'Boston';
            ELSE v_dname := 'unknown';
                         v_loc   := '';
        END CASE;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || RPAD(v_dname, 14) || ' ' ||
            v_loc);
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    ENAME     DEPTNO    DNAME          LOC
-----    -------   ------    ----------     ---------
7369     SMITH       20      Research       Dallas
7499     ALLEN       30      Sales          Chicago
7521     WARD        30      Sales          Chicago
7566     JONES       20      Research       Dallas
7654     MARTIN      30      Sales          Chicago
7698     BLAKE       30      Sales          Chicago
7782     CLARK       10      Accounting     New York
7788     SCOTT       20      Research       Dallas
7839     KING        10      Accounting     New York
7844     TURNER      30      Sales          Chicago
7876     ADAMS       20      Research       Dallas
7900     JAMES       30      Sales          Chicago
7902     FORD        20      Research       Dallas
7934     MILLER      10      Accounting     New York

4.5.5.2 Searched CASE statement

A searched CASE statement uses one or more Boolean expressions to determine the resulting set of statements to execute.

  CASE WHEN boolean-expression THEN
    statements
[ WHEN boolean-expression THEN
    statements
[ WHEN boolean-expression THEN
    statements ] ...]
[ ELSE
    statements ]
  END CASE;

boolean-expression is evaluated in the order in which it appears within the CASE statement. When the first boolean-expression is encountered that evaluates to TRUE, the statement(s) in the corresponding THEN clause are executed and control continues following the END CASE keywords. If none of boolean-expression evaluates to TRUE, the statement(s) following ELSE are executed. If none of boolean-expression evaluates to TRUE and there is no ELSE clause, an exception is thrown.

The following example uses a searched CASE statement to assign a department name and location to a variable based upon the department number.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    v_loc           dept.loc%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp; 
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME     '
        || '     LOC');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------'
        || '     ---------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        CASE
            WHEN v_deptno = 10 THEN v_dname := 'Accounting';
                                    v_loc   := 'New York';
            WHEN v_deptno = 20 THEN v_dname := 'Research';
                                    v_loc   := 'Dallas';
            WHEN v_deptno = 30 THEN v_dname := 'Sales';
                                    v_loc   := 'Chicago';
            WHEN v_deptno = 40 THEN v_dname := 'Operations';
                                    v_loc   := 'Boston';
            ELSE v_dname := 'unknown';
                                    v_loc   := '';
        END CASE;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || RPAD(v_dname, 14) || ' ' ||
            v_loc);
    END LOOP;
    CLOSE emp_cursor;
END;

The following is the output from this program.

EMPNO    ENAME     DEPTNO    DNAME          LOC
-----    -------   ------    ----------     ---------
7369     SMITH       20      Research       Dallas
7499     ALLEN       30      Sales          Chicago
7521     WARD        30      Sales          Chicago
7566     JONES       20      Research       Dallas
7654     MARTIN      30      Sales          Chicago
7698     BLAKE       30      Sales          Chicago
7782     CLARK       10      Accounting     New York
7788     SCOTT       20      Research       Dallas
7839     KING        10      Accounting     New York
7844     TURNER      30      Sales          Chicago
7876     ADAMS       20      Research       Dallas
7900     JAMES       30      Sales          Chicago
7902     FORD        20      Research       Dallas
7934     MILLER      10      Accounting     New York

4.5.6 Loops

With the LOOP, EXIT, CONTINUE, WHILE, and FOR statements, you can arrange for your SPL program to repeat a series of commands.

4.5.6.1 LOOP

LOOP
    statements
END LOOP;

LOOP defines an unconditional loop that is repeated indefinitely until terminated by an EXIT or RETURN statement.

4.5.6.2 EXIT

EXIT [ WHEN expression ];

The innermost loop is terminated and the statement following END LOOP is executed next.

If WHEN is present, loop exit occurs only if the specified condition is TRUE, otherwise control passes to the statement after EXIT.

EXIT can be used to cause early exit from all types of loops; it is not limited to use with unconditional loops.

The following is a simple example of a loop that iterates ten times and then uses the EXIT statement to terminate.

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 1;
    LOOP
        EXIT WHEN v_counter > 10;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

The following is the output from this program.

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

4.5.6.3 CONTINUE

The CONTINUE statement provides a way to proceed with the next iteration of a loop while skipping intervening statements.

When the CONTINUE statement is encountered, the next iteration of the innermost loop is begun, skipping all statements following the CONTINUE statement until the end of the loop. That is, control is passed back to the loop control expression, if any, and the body of the loop is re-evaluated.

If the WHEN clause is used, then the next iteration of the loop is begun only if the specified expression in the WHEN clause evaluates to TRUE. Otherwise, control is passed to the next statement following the CONTINUE statement.

The CONTINUE statement may not be used outside of a loop.

The following is a variation of the previous example that uses the CONTINUE statement to skip the display of the odd numbers.

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 0;
    LOOP
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
        CONTINUE WHEN MOD(v_counter,2) = 1;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
    END LOOP;
END;

The following is the output from above program.

Iteration # 2
Iteration # 4
Iteration # 6
Iteration # 8
Iteration # 10

4.5.6.4 WHILE

WHILE expression LOOP
    statements
END LOOP;

The WHILE statement repeats a sequence of statements so long as the condition expression evaluates to TRUE. The condition is checked just before each entry to the loop body.

The following example contains the same logic as in the previous example except the WHILE statement is used to take the place of the EXIT statement to determine when to exit the loop.

Note: The conditional expression used to determine when to exit the loop must be altered. The EXIT statement terminates the loop when its conditional expression is true. The WHILE statement terminates (or never begins the loop) when its conditional expression is false.

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 1;
    WHILE v_counter <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

The same result is generated by this example as in the prior example.

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

4.5.6.5 FOR (integer variant)

FOR name IN [REVERSE] expression .. expression LOOP
    statements
END LOOP;

This form of FOR creates a loop that iterates over a range of integer values. The variable name is automatically defined as type INTEGER and exists only inside the loop. The two expressions giving the loop range are evaluated once when entering the loop. The iteration step is +1 and name begins with the value of expression to the left of .. and terminates once name exceeds the value of expression to the right of ... Thus the two expressions take on the following roles: start-value .. end-value.

The optional REVERSE clause specifies that the loop should iterate in reverse order. The first time through the loop, name is set to the value of the right-most expression; the loop terminates when the name is less than the left-most expression.

The following example simplifies the WHILE loop example even further by using a FOR loop that iterates from 1 to 10.

BEGIN
    FOR i IN 1 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
    END LOOP;
END;

Here is the output using the FOR statement.

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

If the start value is greater than the end value the loop body is not executed at all. No error is raised as shown by the following example.

BEGIN
    FOR i IN 10 .. 1 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
    END LOOP;
END;

There is no output from this example as the loop body is never executed.

Note: SPL also supports CURSOR FOR loops (see Section 4.8.7).

4.5.7 Exception Handling

By default, any error occurring in an SPL program aborts execution of the program. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section. The syntax is an extension of the normal syntax for a BEGIN block:

[ DECLARE
    declarations ]
  BEGIN
    statements
  EXCEPTION
    WHEN condition [ OR condition ]... THEN
      handler_statements
  [ WHEN condition [ OR condition ]... THEN
      handler_statements ]...
  END;

If no error occurs, this form of block simply executes all the statements, and then control passes to the next statement after END. If an error occurs within the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error that occurred. If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END. If no match is found, the error propagates out as though the EXCEPTION clause were not there at all. The error can be caught by an enclosing block with EXCEPTION; if there is no enclosing block, it aborts processing of the subprogram.

The special condition name OTHERS matches every error type. Condition names are not case-sensitive.

If a new error occurs within the selected handler_statements, it cannot be caught by this EXCEPTION clause, but is propagated out. A surrounding EXCEPTION clause could catch it.

The following table lists the condition names that may be used:

Table 4-4-2 Exception Condition Names

Condition Name

Description

CASE_NOT_FOUND

The application has encountered a situation where none of the cases in a CASE statement evaluates to TRUE and there is no ELSE condition.

COLLECTION_IS_NULL

The application has attempted to invoke a collection method on a null collection such as an uninitialized nested table.

CURSOR_ALREADY_OPEN

The application has attempted to open a cursor that is already open.

DUP_VAL_ON_INDEX

The application has attempted to store a duplicate value that currently exists within a constrained column.

INVALID_CURSOR

The application has attempted to access an unopened cursor.

INVALID_NUMBER

The application has attempted to convert a string to a numeric literal that cannot be converted (applicable only to SQL statements).

NO_DATA_FOUND

No rows satisfy the selection criteria.

OTHERS

The application has encountered an exception that hasn’t been caught by a prior condition in the exception section.

SUBSCRIPT_BEYOND_COUNT

The application has attempted to reference a subscript of a nested table or varray beyond its initialized or extended size.

SUBSCRIPT_OUTSIDE_LIMIT

The application has attempted to reference a subscript or extend a varray beyond its maximum size limit.

TOO_MANY_ROWS

The application has encountered more than one row that satisfies the selection criteria (where only one row is allowed to be returned).

VALUE_ERROR

The application has tried to convert a string to a numeric literal that cannot be converted (applicable only to procedural statements).

ZERO_DIVIDE

The application has tried to divide by zero.

User-defined Exception

See Section 4.5.8

4.5.8 User-defined Exceptions

Any number of errors (referred to in PL/SQL as exceptions) can occur during program execution. When an exception is thrown, normal execution of the program stops, and control of the program transfers to the error-handling portion of the program. An exception may be a pre-defined error that is generated by the server, or may be a logical error that raises a user-defined exception.

User-defined exceptions are never raised by the server; they are raised explicitly by a RAISE statement.

A user-defined exception is raised when a developer-defined logical rule is broken; a common example of a logical rule being broken occurs when a check is presented against an account with insufficient funds. An attempt to cash a check against an account with insufficient funds will provoke a user-defined exception.

You can define exceptions in functions, procedures, packages or anonymous blocks. While you cannot declare the same exception twice in the same block, you can declare the same exception in two different blocks.

Before implementing a user-defined exception, you must declare the exception in the declaration section of a function, procedure, package or anonymous block. You can then raise the exception using the RAISE statement:

      DECLARE    
          exception_name EXCEPTION;
          
      BEGIN    
          ...    
          RAISE exception_name;    
          ...    
      END;	

exception_name is the name of the exception.

Unhandled exceptions propagate back through the call stack. If the exception remains unhandled, the exception is eventually reported to the client application.

User-defined exceptions declared in a block are considered to be local to that block, and global to any nested blocks within the block. To reference an exception that resides in an outer block, you must assign a label to the outer block; then, preface the name of the exception with the block name:

        block_name.exception_name

Conversely, outer blocks cannot reference exceptions declared in nested blocks.

The scope of a declaration is limited to the block in which it is declared unless it is created in a package, and when referenced, qualified by the package name. For example, to raise an exception named out_of_stock that resides in a package named inventory_control a program must raise an error named:

        inventory_control.out_of_stock

The following example demonstrates declaring a user-defined exception in a package. The user-defined exception does not require a package-qualifier when it is raised in check_balance, since it resides in the same package as the exception:

      CREATE OR REPLACE PACKAGE ar AS    
        overdrawn EXCEPTION;    
        PROCEDURE check_balance(p_balance NUMBER, p_amount NUMBER);    
      END;
          
      CREATE OR REPLACE PACKAGE BODY ar AS    
         PROCEDURE check_balance(p_balance NUMBER, p_amount  NUMBER)    
         IS     
         BEGIN    
             IF (p_amount > p_balance) THEN    
               RAISE overdrawn;    
             END IF;    
          END;

The following procedure (purchase) calls the check_balance procedure. If p_amount is greater than p_balance, check_balance raises an exception; purchase catches the ar.overdrawn exception. purchase must refer to the exception with a package-qualified name (ar.overdrawn) because purchase is not defined within the ar package.

      CREATE PROCEDURE purchase(customerID INT, amount NUMERIC)    
      AS    
        BEGIN    
           ar.check_ balance(getcustomerbalance(customerid), amount);    
             record_purchase(customerid, amount);    
        EXCEPTION    
           WHEN ar.overdrawn THEN    
             raise_credit_limit(customerid, amount*1.5);    
        END;

When ar.check_balance raises an exception, execution jumps to the exception handler defined in purchase:

      EXCEPTION    
           WHEN ar.overdrawn THEN    
             raise_credit_limit(customerid, amount*1.5);

The exception handler raises the customer’s credit limit and ends. When the exception handler ends, execution resumes with the statement that follows ar.check_balance.

4.5.9 PRAGMA EXCEPTION_INIT

PRAGMA EXCEPTION_INIT associates a user-defined error code with an exception. A PRAGMA EXCEPTION_INIT declaration may be included in any block, sub-block or package. You can only assign an error code to an exception (using PRAGMA EXCEPTION_INIT) after declaring the exception. The format of a PRAGMA EXCEPTION_INIT declaration is:

      PRAGMA EXCEPTION_INIT(exception_name,     
                            {exception_number | exception_code})

Where:

exception_name is the name of the associated exception.

exception_number is a user-defined error code associated with the pragma. If you specify an unmapped exception_number, the server will return a warning.

exception_code is the name of a pre-defined exception. For a complete list of valid exceptions, see the Postgres core documentation, available at the EnterpriseDB website at:

The previous section (User-defined Exceptions) included an example that demonstrates declaring a user-defined exception in a package. The following example uses the same basic structure, but adds a PRAGMA EXCEPTION_INIT declaration:

      CREATE OR REPLACE PACKAGE ar AS    
        overdrawn EXCEPTION;    
        PRAGMA EXCEPTION_INIT (overdrawn, -20100);    
        PROCEDURE check_balance(p_balance NUMBER, p_amount NUMBER);    
      END;
          
      CREATE OR REPLACE PACKAGE BODY ar AS    
         PROCEDURE check_balance(p_balance NUMBER, p_amount  NUMBER)    
         IS     
         BEGIN    
             IF (p_amount > p_balance) THEN    
               RAISE overdrawn;    
             END IF;    
          END;

The following procedure (purchase) calls the check_balance procedure. If p_amount is greater than p_balance, check_balance raises an exception; purchase catches the ar.overdrawn exception.

      CREATE PROCEDURE purchase(customerID int, amount NUMERIC)    
      AS    
        BEGIN    
           ar.check_ balance(getcustomerbalance(customerid), amount);    
             record_purchase(customerid, amount);    
        EXCEPTION    
           WHEN ar.overdrawn THEN    
            DBMS_OUTPUT.PUT_LINE ('This account is overdrawn.');    
            DBMS_OUTPUT.PUT_LINE ('SQLCode :'||SQLCODE||' '||SQLERRM );      
      END;

When ar.check_balance raises an exception, execution jumps to the exception handler defined in purchase.

      EXCEPTION    
           WHEN ar.overdrawn THEN    
            DBMS_OUTPUT.PUT_LINE ('This account is overdrawn.');    
            DBMS_OUTPUT.PUT_LINE ('SQLCode :'||SQLCODE||' '||SQLERRM );  

The exception handler returns an error message, followed by SQLCODE information:

      This account is overdrawn.    
      SQLCODE: -20100 User-Defined Exception 

The following example demonstrates using a pre-defined exception. The code creates a more meaningful name for the no_data_found exception; if the given customer does not exist, the code catches the exception, calls DBMS_OUTPUT.PUT_LINE to report the error, and then re-raises the original exception:

      CREATE OR REPLACE PACKAGE ar AS    
        overdrawn EXCEPTION;    
        PRAGMA EXCEPTION_INIT (unknown_customer, no_data_found);    
        PROCEDURE check_balance(p_customer_id NUMBER);    
      END;
          
      CREATE OR REPLACE PACKAGE BODY ar AS    
         PROCEDURE check_balance(p_customer_id NUMBER)    
         IS     
         DECLARE    
           v_balance NUMBER;    
         BEGIN    
           SELECT balance INTO v_balance FROM customer    
             WHERE cust_id = p_customer_id;    
         EXCEPTION WHEN unknown_customer THEN    
           DBMS_OUTPUT.PUT_LINE('invalid customer id');    
           RAISE;    
         END;

4.5.10 RAISE_APPLICATION_ERROR

The procedure, RAISE_APPLICATION_ERROR, allows a developer to intentionally abort processing within an SPL program from which it is called by causing an exception. The exception is handled in the same manner as described in Section 4.5.7. In addition, the RAISE_APPLICATION_ERROR procedure makes a user-defined code and error message available to the program which can then be used to identify the exception.

RAISE_APPLICATION_ERROR(error_number, message);

Where:

error_number is a negative integer value that is returned in a variable named SQLCODE when the procedure is executed. error_number must be a value between -20000 and -20999.

message is a string literal or expression that is returned in a variable named SQLERRM.

For additional information on the SQLCODE and SQLERRM variables, see Section 4.12, Errors and Messages.

The following example uses the RAISE_APPLICATION_ERROR procedure to display a different code and message depending upon the information missing from an employee.

CREATE OR REPLACE PROCEDURE verify_emp (
    p_empno         NUMBER
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
BEGIN
    SELECT ename, job, mgr, hiredate
        INTO v_ename, v_job, v_mgr, v_hiredate FROM emp
        WHERE empno = p_empno;
    IF v_ename IS NULL THEN
        RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
    END IF;
    IF v_job IS NULL THEN
        RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
    END IF;
    IF v_mgr IS NULL THEN
        RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
    END IF;
    IF v_hiredate IS NULL THEN
        RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' validated without errors');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;

The following shows the output in a case where the manager number is missing from an employee record.

EXEC verify_emp(7839);

SQLCODE: -20030
SQLERRM: EDB-20030: No manager for 7839

Previous PageTable Of ContentsNext Page