Development and Programming
Oracle PL/SQL: IF-THEN-ELSIF Statement vs. Case statement , CASE Expression vs. Decode Function
Decode is a very useful and handy function for Oracle queries. It replaces the complex If-Then-Else logic, which is used to display different things based on different values in a column. This is usually used for pivoting purposes.
Syntax is like:
DECODE(expr, search, result
[, search, result ]...
[, default ]
DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.
The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.
The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.
You could use the decode function in an SQL statement as follows:
decode(supplier_id, 10000, 'IBM',
10002, 'Hewlett Packard',
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
result := 'Gateway';
The decode function will compare each supplier_id value, one by one.
PL/SQL also provides a searched CASE statement, which has the form:
WHEN search_condition1 THEN sequence_of_statements1;
WHEN search_condition2 THEN sequence_of_statements2;
WHEN search_conditionN THEN sequence_of_statementsN;
END CASE [label_name];
The searched CASE statement has no selector. Also, its WHEN clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:
WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
WHEN grade = 'C' THEN dbms_output.put_line('Good');
WHEN grade = 'D' THEN dbms_output.put_line('Fair');
WHEN grade = 'F' THEN dbms_output.put_line('Poor');
ELSE dbms_output.put_line('No such grade');
The search conditions are evaluated sequentially. The Boolean value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. If any WHEN clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.
If none of the search conditions yields TRUE, the ELSE clause is executed. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:
ELSE RAISE CASE_NOT_FOUND;
Exceptions raised during the execution of a searched CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.
PL/SQL also provides a searched CASE expression, which has the form:
WHEN search_condition1 THEN result1
WHEN search_condition2 THEN result2
WHEN search_conditionN THEN resultN
A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a Boolean value, which lets you test different variables or multiple conditions in a single WHEN clause. An example follows:
WHEN grade = 'A' THEN 'Excellent'
WHEN grade = 'B' THEN 'Very Good'
WHEN grade = 'C' THEN 'Good'
WHEN grade = 'D' THEN 'Fair'
WHEN grade = 'F' THEN 'Poor'
ELSE 'No such grade'
The search conditions are evaluated sequentially. The Boolean value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. After any WHEN clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields TRUE, the optional ELSE clause is executed. If no WHEN clause is executed and no ELSE clause is supplied, the value of the expression is NULL.
The DECODE fucntion can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN.
The traditional CASE expression can compare discrete values, the searched CASE expression can evaluate any expression and allowes the use of operators like > and BETWEEN (eliminating most of the contortions) and different values to be compared in different branches of the statement (eliminating most nesting).
The equivelance case statement is:
when 'A' then 'Active'
when 'T' then 'Terminated'
when status = 'A' then 'Active'
when status = 'T' then 'Terminated'
© 2019 Digcode.com. All rights reserved.