 | Documentation |
|---|---|
14.5. Control StructuresControl structures are probably the most useful (and important) part of SPL. With SPL's control structures, you can manipulate EnterpriseDB data in a very flexible and powerful way. 14.5.1. IF StatementIF statements let you execute commands based on certain conditions. SPL has four forms of IF:
14.5.1.1. IF-THENIF 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
14.5.1.2. IF-THEN-ELSEIF 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
14.5.1.3. IF-THEN-ELSE IFIF 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;
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. 14.5.1.4. IF-THEN-ELSIF-ELSEIF 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
14.5.2. CASE ExpressionThe 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. 14.5.2.1. Selector CASE ExpressionThe 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
14.5.2.2. Searched CASE ExpressionA 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
14.5.3. CASE StatementThe 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. 14.5.3.1. Selector CASE StatementThe 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 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
14.5.3.2. Searched CASE statementA 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
14.5.4. LoopsWith the LOOP, EXIT, CONTINUE, WHILE, and FOR statements, you can arrange for your SPL program to repeat a series of commands. 14.5.4.1. LOOPLOOP
statements
END LOOP;LOOP defines an unconditional loop that is repeated indefinitely until terminated by an EXIT or RETURN statement. 14.5.4.2. EXITEXIT [ 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
14.5.4.3. CONTINUECONTINUE [ WHEN expression ];
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. When CONTINUE is used, then the next iteration of the innermost loop is begun. 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
14.5.4.4. WHILEWHILE 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.
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
14.5.4.5. FOR (integer variant)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.
14.5.5. Exception HandlingBy 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. 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. 14.5.6. Raise Application ErrorThe procedure, RAISE_APPLICATION_ERROR, provides the capability 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 described in Section 14.5.5. 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);
error_number is an integer value or expression that is returned in a variable named, SQLCODE, when the procedure is executed. 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 14.9. 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
|