CASE Expressions
CASE expressions do for expressions what CASE statements do for statements. Simple CASE expressions let you choose an expression to evaluate based on a scalar value that you provide as input. Searched CASE expressions evaluate a list of expressions to find the first one that evaluates to TRUE, and then return the results of an associated expression.
CASE expressions take the following two forms:
Simple_Case_Expression := CASE expression WHEN result1 THEN result_expression1 WHEN result2 THEN result_expression2 ... ELSE result_expression_else END; Searched_Case_Expression := CASE WHEN expression1 THEN result_expression1 WHEN expression2 THEN result_expression2 ... ELSE result_expression_else END;A CASE expression returns a single value, the result of whichever result expression is chosen. Each WHEN clause must be associated with exactly one expression (no statements). Do not use semicolons or END CASE to mark the end of the CASE expression. CASE expressions are terminated by a simple END.
Following is an example of a simple CASE expression being used with the DBMS_OUTPUT package to output the value of a Boolean variable. Recall thatPUT_LINE is not overloaded to handle Boolean types. In this example, the CASE expression converts the Boolean value in a character string, which PUT_LINE can then handle:
DECLARE boolean_true BOOLEAN := TRUE; boolean_false BOOLEAN := FALSE; boolean_null BOOLEAN; FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS BEGIN RETURN CASE flag WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' ELSE 'NULL' END; END; BEGIN DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));END;A searched CASE expression can be used to implement our bonus logic, returning the proper bonus value for any given salary:
DECLARE salary NUMBER := 20000; employee_id NUMBER := 36325; PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE(emp_id); DBMS_OUTPUT.PUT_LINE(bonus_amt); END; BEGIN give_bonus(employee_id, CASE WHEN salary >= 10000 AND salary <=20000 THEN 1500 WHEN salary > 20000 AND salary <= 40000 THEN 1000 WHEN salary > 40000 THEN 500 ELSE 0 END);END;You can use a CASE expression anywhere you can use any other type of expression. The following example uses a CASE expression to compute a bonus amount, multiplies that amount by 10, and assigns the result to a variable that is displayed via DBMS_OUTPUT:
DECLARE salary NUMBER := 20000; employee_id NUMBER := 36325; bonus_amount NUMBER;BEGIN bonus_amount := CASE WHEN salary >= 10000 AND salary <=20000 THEN 1500 WHEN salary > 20000 AND salary <= 40000 THEN 1000 WHEN salary > 40000 THEN 500 ELSE 0 END * 10; DBMS_OUTPUT.PUT_LINE(bonus_amount);END;Unlike with the CASE statement, no error is raised in the event that no WHEN clause is selected in a CASE expression. Instead, when no WHEN conditions are met, a CASE expression will return NULL.
| CASE in SQL Statements Support for CASE in SQL statements preceded support for CASE in PL/SQL; Oracle8i introduced the searched CASE expression in SQL. Unfortunately, much to the frustration of PL/SQL programmers everywhere, SQL statements executed from within PL/SQL could not use CASE expressions. For example, from SQL*Plus you could execute: SELECT CASE WHEN DUMMY='X' THEN 'Dual is OK' ELSE 'Dual is messed up' ENDFROM DUAL; However, the following PL/SQL code would not work: DECLARE dual_message VARCHAR2(20);BEGIN SELECT CASE WHEN DUMMY='X' THEN 'Dual is OK' ELSE 'Dual is messed up' END INTO dual_message FROM DUAL; DBMS_OUTPUT.PUT_LINE (dual_message);END; This frustration goes away in Oracle9i. In Oracle9i, you can have both searched and simple CASE statements, and they can be used equally from SQL, from PL/SQL, and from SQL within PL/SQL. |
4.3 The GOTO Statement
Certain PL/SQL control structures offer structured methods for processing executable statements in your program. You use an IF statement or a CASE statement to test a condition to determine which parts of your code to execute; you use a LOOP variation (described in Chapter 5) to execute a section of code more than once. In addition to these well-structured approaches to program control, PL/SQL offers the GOTO. The GOTO statement performs unconditional branching to another executable statement in the same execution section of a PL/SQLblock. As with other constructs in the language, if you use GOTO appropriately and with care, your programs will be stronger for it.
The general format for a GOTO statement is:
GOTO label_name;where label_name is the name of a label identifying the target statement. This GOTO label is defined in the program as follows:
<<label_name>>You must surround the label name with double enclosing angle brackets (<< >>). When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label. Following is a complete code block containing both a GOTO and a label:
BEGIN GOTO second_output; DBMS_OUTPUT.PUT_LINE('This line will never execute.'); <<second_output>> DBMS_OUTPUT.PUT_LINE('We are here!');END;Contrary to popular opinion (including mine), the GOTO statement can come in handy. There are cases where a GOTO statement can simplify the logic in your program. On the other hand, because PL/SQL provides so many different control constructs and modularization techniques, you can almost always find a better way to do something than with a GOTO.