Case Expressions Database Management System

Case Expressions Database Management System

Case Expressions Database Management System : A CASE expression selects a result and returns it. To select the result, the CASE expression uses a selector, an expression whose value is used to select one of several alternatives. The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed.

PL/SQL also provides a searched CASE expression, which has the form:

CASE

WHEN search_condition1 THEN result 1

WHEN search_condition2 THEN result2

WHEN search_condition N THEN resultN

[ELSE result N+1;]

END;

/

A searched CASE expression 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.

CASE Expressions: Example

SET SERVEROUTPUT ON

DECLARE

var_grade CHAR(1) := UPPER(‘&p_ grade’);

var_appraisal VARCHAR2(20);

BEGIN

Var_appraisal :=

CASE var_grade

WHEN ‘A’ THEN ‘Excellent’

WHEN ‘B’ THEN ‘Very Good’

WHEN ‘C’ THEN ‘Good’

ELSE ‘No such grade’

END,

DBMS_OUTPUT.PUT _LINE (‘Grade: ‘|| var grade ||

Appraisal’ || var_appraisal);

END;

/

CASE Expressions : Example

In the example on the slide, the CASE expression uses the value in the var_grade variable as the expression. This value is accepted from the user using a Substitution variable. Based on the value entered by the user, the CASE expression evaluates the value of the var_appraisal variable based on the value of the var_grade value. The output of the above example will be as follows:

Iterative Control: LOOP Statements

PL/SQL provides a number of facilities to structure loops to repeat a statement or sequence of

statements multiple times.

Looping Constructs are the second type of Control structure. PL/SQL provides the following types of loops

  • Basic loop that perform repetitive actions without overall conditions
  • FOR loops that perform iterative control of actions based on a count
  • WHILE loops that perform iterative control of actions based on a Condition

 

Use the EXIT statement to terminate loops.