Control structures are probably the most useful (and important) part of EDB-SPL. With EDB-SPL's control structures, you can manipulate EnterpriseDB data in a very flexible and powerful way.
IF statements let you execute commands based on certain conditions. EDB-SPL has four forms of IF:
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSE IF
IF ... THEN ... ELSIF ... THEN ... ELSE
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
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
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.
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
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 CASEand the other that uses a selector.
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
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
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 CASEand the other that uses a selector.
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
statement;...
[ WHEN match-expression THEN
statement;...
[ WHEN match-expression THEN
statement;...
...]]
[ ELSE
statement;... ]
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. statement; is an EDB-SPL statement terminated by a semi-colon. Note that there may be more than one statement following the THEN clause. 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
A searched CASE statement uses one or more boolean expressions to determine the resulting set of statements to execute.
CASE WHEN boolean-expression THEN
statement;...
[ WHEN boolean-expression THEN
statement;...
[ WHEN boolean-expression THEN
statement;...
...]]
[ ELSE
statement;... ]
END;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
With the LOOP, EXIT, WHILE, and FOR statements, you can arrange for your EDB-SPL function to repeat a series of commands.
LOOP
statements
END LOOP;LOOP defines an unconditional loop that is repeated indefinitely until terminated by an EXIT or RETURN statement.
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
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
FOR name IN 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 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.
By default, any error occurring in an EDB-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. But 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, or if there is none 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.