CASE expressions: The expressions select a result and return it. CASE expressions can be assigned to variables, can be part of SQL statements and can be used in logical expressions. The syntax begins with CASE and ends with END.
CASE statements: They are independent statements just like other PL/SQL statements such as IF…THEN…ELSE statements. The syntax begins with CASE and ends with END CASE.
CASE Expressions
CASE expressions select a result and return it. To understand better, look at the following syntax of a CASE expression:
CASE selector
WHEN expr1 THEN result1 WHEN expr2 THEN result2 …
WHEN exprn THEN resultn [ELSE resultn+1;]
END;
From the syntax, it becomes clear that, to select the result, the CASE expression uses a selector, which is an expression whose value is used to select one of the several alternatives provided. The selector is followed by one or more WHEN clauses. The value of the selector determines which clause is executed. This is the syntax of a simple CASE expression.
Execute the following code of a simple CASE expression:
DECLARE
vGrade CHAR(1):= ‘&grade’; vGradeWiseAge VARCHAR2(25); BEGIN
VGradeWiseAge := CASE vGrade
WHEN ‘A’ THEN ‘Age between 5 and 13’ WHEN ‘B’ THEN ‘Age between 14 and 20’ WHEN ‘C’ THEN ‘Age between 21 and 50’
WHEN ‘D’ THEN ‘Age above 50’ END;
DBMS_OUTPUT.PUT_LINE (‘Grade : ’||vGrade||‘ Stands for: ’||vGradeWiseAge);
END; /
In the code, the CASE expression is being assigned to a variable vGradeWiseAge. The CASE expression uses the value in the vGrade variable (The value is accepted
using a substitution variable) as the expression. Depending on the value entered by the user, the CASE expression assigns the value to variable vGradeWiseAge.
PL/SQL also supports a searched CASE expression. It has the following syntax:
CASE
WHEN searchCondition1 THEN result1 WHEN searchCondition2 THEN result2 …
WHEN searchConditionn THEN resultn [ELSE resultn+1]
END;
Searched CASE expressions have no selectors. The WHEN clauses contain search conditions, which return a Boolean value, and not expressions that can return values of any type. Each WHEN clause can have different conditional expressions, which can take the form of searchCondition = <variableExpr> operator <variableExpr>. The operator can be any comparison operator.
Execute following code of a searched CASE expression:
DECLARE
vAge NUMBER(3):= ‘&age’; vAgeWiseGrade CHAR(25); BEGIN
vAgeWiseGrade := CASE
WHEN vAge<5 THEN ‘Membership not permitted’
WHEN vAge BETWEEN 5 AND 13 THEN ‘A’ WHEN vAge BETWEEN 14 AND 20 THEN ‘B’ WHEN vAge BETWEEN 21 AND 50 THEN ‘C’ WHEN vAge BETWEEN 50 AND 100 THEN ‘D’
END;
DBMS_OUTPUT.PUT_LINE (‘For Age : ’||vAge||‘ The
corresponding grade is: ’||vAgeWiseGrade); END;
/
In the code, the searched CASE expression is assigned to a variable vAgeWiseGrade. The block accepts the age and returns the grade based on the CASE expression.
CASE Statements
PL/SQL supports simple as well as searched CASE statements. Their differences are as follows:
Simple CASE statements:
Evaluate a single variable or expression for multiple values Do not support comparison operators in the WHEN clause Searched CASE statements:
Evaluate multiple variables or expressions Each WHEN clause can:
• Evaluate different expressions • Have comparison operators
The following examples will help us in understanding the different ways in which the two different CASE statements function.
A simple CASE statement example:
DECLARE
vGrade CHAR(1):= ‘&grade’; vGradeWiseAge VARCHAR2(25); BEGIN
CASE vGrade
WHEN ‘A’ THEN vGradeWiseAge:=‘Age between 5 and 13’; WHEN ‘B’ THEN vGradeWiseAge:=‘Age between 14 and 20’; WHEN ‘C’ THEN vGradeWiseAge:=‘Age between 21 and 50’; WHEN ‘D’ THEN vGradeWiseAge:=‘Age above 50’;
END CASE;
DBMS_OUTPUT.PUT_LINE (‘Grade : ’||vGrade||‘ Stands for: ’||vGradeWiseAge);
END; /
In the code, you will notice that the CASE statement ends with an END CASE statement, and that every executable statement following the WHEN clause is terminated with a
semicolon. The above code accepts the value of vGrade and based on it assigns different values to vGradeWiseAge variable.
A searched CASE statement example:
DECLARE
vAge NUMBER(3):= ‘&age’; vAgeWiseGrade CHAR(2); BEGIN
CASE
WHEN vAge<5 THEN vAgeWiseGrade:=‘Membership not permitted’; WHEN vAge BETWEEN 5 AND 13 THEN vAgeWiseGrade:=‘A’;
WHEN vAge BETWEEN 14 AND 20 THEN vAgeWiseGrade:=‘B’; WHEN vAge BETWEEN 21 AND 50 THEN vAgeWiseGrade:=‘C’; WHEN vAge BETWEEN 50 AND 100 THEN vAgeWiseGrade:=‘D’; END CASE;
DBMS_OUTPUT.PUT_LINE (‘For Age : ’||vAge||‘ The
END; /
In the code, you will observe that the searched CASE statement is similar to the searched CASE expression except that the former cannot be assigned to variables.
NULLIF and COALESCE Expressions
NULLIF and COALESCE expressions are a form of shorthand for PL/SQL CASE expressions.
The NULLIF expression behaves like an inverse of the NVL function. The semantics can be written as follows:
CASE
WHEN expr1=expr2 THEN NULL; ELSE expr1;
END;
Execute the following PL/SQL code:
DECLARE
vDate DATE:= ‘&Date’; vReturnDt DATE;
BEGIN
IF vDate >= SYSDATE THEN
vReturnDt:= NULLIF (vDate, SYSDATE);
DBMS_OUTPUT.PUT_LINE (‘The member has to pay a fine amount of: ’|| (vReturnDt – SYSDATE) * 1.5); ELSE
DBMS_OUTPUT.PUT_LINE (‘No fines due’); END IF;
END; /
In the code, if the date entered by the user is before the SYSDATE, the ELSE condition is executed. However, in case the date entered is after the SYSDATE or equal to the SYSDATE, the NULLIF expression is evaluated. If the vDate variable
value is after SYSDATE, then the vDate value is returned to vReturnDt variable. If vDate variable value is same as SYSDATE, then a NULL value is returned.
The COALESCE expression behaves like the NVL function, but it can take a list of values. Its semantics can be written as follows:
Expression with two arguments:
CASE
WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2
END;
Expression with three or more arguments:
CASE
WHEN expr1 IS NOT NULL THEN expr1; ELSE COALESCE (expr2, expr3,…,exprn); END CASE;