PL/SQL - SPLessons

PL/SQL Conditions

Chapter 12

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Conditions

PL/SQL Conditions

shape Description

PLSQL Conditions depend on the conditional block and may or may not be executed. If the case is said to be true, it executes the statement block, and if the condition is said to be false, then the statement block doesn’t get executed. Oracle PL/SQL provides a very robust support for case evaluation in PL/SQL and makes it simple and easy to understand the constructs. There are two types of PLSQL Conditions statements. The IF statement and the other type is the CASE statement and expressions.

shape Conceptual
figure

IF Condition

shape Description

The IF PLSQL Conditions statement starts with the IF keyword, followed by a condition or a group of conditions. If the condition is true, then the group of statements inside the IF clause will be executed. The IF proclamation ends with the END IF statement followed by a semicolon and can declare any variables inside the IF clause.

shape Syntax

IF <Condition>THEN
statements
END IF;

Condition =>Value assigned in the condition.

Statement =>Executes true or false statements.

shape Examples

The below example describes the if statement.

SQL> DECLARE
  1  a number(2):=10;
  2  BEGIN
  3 a:=10;
  4  -- check the boolean condition using if statement
  5  IF(a&lt;20)THEN
  6  -- if condition is true then print the following
  7  dbms_output.put_line('a is less than 20');
  8  END IF;
  9 dbms_output.put_line('value of a is :'||a);
  10 END;
  11 /

a is less than 20
value of a is : 10
PL/SQL procedure successfully completed.

IF-ELSE Statement

shape Description

The statement of IF-THEN is succeed by an alternate successions of ELSE proclamation, which assassinate when the case is said to be False.

shape Conceptual
figure

shape Syntax

IF condition THEN
S1;
ELSE
S2;
END IF;

Condition =>Value assigned in the condition.

Statement =>Executes true or false statements s1 value or s2 value.

shape Examples

The below example explains the if-else statement.

SQL> DECLARE
  2   a number(3):=100;
  3  BEGIN
  4  --check the boolean condition using if statement
  5  IF(a&lt;20)THEN
  6  -- if condition is true then print the following
  7  dbms_output.put_line('a is less than 20');
  8  ELSE
  9  dbms_output.put_line('a is not less than 20');
 10  END IF;
 11 dbms_output.put_line('value of a is:'||a);
 12  END;
 13  /
PL/SQL is successfully executed.

IF-Else-IF statement

shape Description

This is the third form of IF statement. ELSE clause is optional when using ELSE IF. And, ELSE IF clause is also followed by a condition as IF clause and contains any number of ELSE IF statements. Oracle keeps evaluating the IF and ELSE IF statements from top to bottom until the condition is set to be true.

shape Syntax

IF <condition> THEN
statements
ELSIF<case>THEN
statements
ELSE
statements
END IF;

Condition =>Value assigned in the condition.

Statement =>Executes true or false statements.

shape Examples

The below example describes the if-else-if statement.

SQL> DECLARE
  2   a number(3):=100;
  3  BEGIN
  4  IF (a=10)THEN
  5  dbms_output.put_line('value of a is 10');
  6  ELSIF(a=20)THEN
  7  dbms_output.put_line('value of a is 20');
  8  ELSIF(a=30)THEN
  9  dbms_output.put_line('value of a is 30 ');
 10  ELSE
 11  dbms_output.put_line('None of the values is matching');
 12  END IF;
 13  dbms_output.put_line('Exact value of a is:'||a);
 14  END;
 15  /
None of the values is matching
Exact value of a is:100
PL/SQL procedure is successfully completed.

Nesting

shape Description

The nesting PLSQL Conditions can happen anywhere within the IF, ELSE IF or ELSE clauses. To prevent the code from becoming too complex, just maintain one or two level of nesting and evaluate different ways of writing the logic to keep nesting to a minimum.

shape Syntax

IF(boolean_expression 1)THEN
–assassinate when the boolean explanations 1 is true
IF(boolean_expression 2)THEN
–assassinate when the boolean explanations 2 is true
arrangement-of-proclamations;
END IF;
ELSE
–assassinate when the boolean explanations 1 is not true
else-statements;
END IF;

Condition =>Value assigned in the condition.

Statement =>Executes true or false statements.

shape Examples

The below example illustrates the nesting statement.

SQL> DECLARE
  2   a number(3):=100;
  3   b number(3):=200;
  4  BEGIN
  5   -- check the boolean condition
  6  IF(a=100) THEN
  7  -- if condition is true then check the following
  8  IF(b=200)THEN
  9  --if condition is true then print the following
 10   dbms_output.put_line('value of a is 100 and b is 200');
 11  END IF;
 12  END IF;
 13  dbms_output.put_line('Exact value of a is :'||a);
 14  dbms_output.put_line('Exact value of b is :'||b);
 15  END;
 16  /

value of a is 100 and b is 200
Exact value of a is : 100
Exact value of b is : 200 
PL/SQL procedure successfully completed.

Case statements

shape Description

Case statement is an alternate to the IF statement and makes the code more readable & compact. The case can be expressed as a statement where one can execute the conditional logic based on value comparisons or conditions. It can also be written as an expression where assigned values are returned to variables. This is very useful while executing the conditional code and assigning the values to the same variable. In the case statement, the variable values to different alternates will be compared and returns the very first match. It starts with a case keyword followed by a variable or an expression. And, the results are evaluated in the when clause from top to bottom and the clause ELSE is alternate. If the clause ELSE is not defined and there is no match, the case statement will raise a CASE_NOT_FOUND exception. The CASE END ends the CASE statement.

shape Conceptual
figure

shape Syntax

CASE{variable or expression}
WHEN value_1 THEN
statements_1
WHEN value_n THEN
statement_n
[ELSE
statements defaults]
END CASE;

Condition =>Value assigned in the condition.

Statement =>Executes true or false statements.

shape Examples

The below example illustrates the case statement.

SQL> DECLARE
  2  grade char(1):='A';
  3  BEGIN
  4  CASE grade
  5  when 'A'then dbms_output.put_line('Excellent');
  6  when 'B'then dbms_output.put_line('Very good');
  7  when 'C'then dbms_output.put_line('Well done');
  8  when 'D'then dbms_output.put_line('You passed');
  9  when 'F'then dbms_output.put_line('Better try again');
 10  else dbms_output.put_line('No such grade');
 11  END CASE;
 12  END;
 13  /

Excellent
PL/SQL procedure successfully completed.

Case expression

shape Description

The simple case expression is similar to the simple case statement, which compares a value or an expression to the values in the when clause, and returns the values from the first match to when clause. However, the difference is that here it assigns the written values of the case expression directly to a variable in the PL/SQL section. The returned value should match the datatype of the variable, it is assigned to a number, data, and character. So, for the simple case statement, assigning values to a single variable inside the when clause simplifies the code and makes it more complete and readable. There will be no END CASE clause, but contains the END clause to end the case statement. The ELSE clause is optional like case statement. If the ELSE clause is omitted, then no match will be found with the WHEN clause and doesn’t return CASE_NOT_FOUND exception instead returns only the NULL VALUE.

shape Syntax

CASE{value or expression}
WHEN VALUE_1 THEN
return_value_1
WHEN value_2
[ELSE
return_value3]
END;

Condition =>Esteem assigned in the condition.

Statement =>Executes true or false statements.

shape Examples

The below example illustrates the case expressions.

SQL> DECLARE
  2  I_ticket_priority VARCHAR2(8):='MEDIUM';
  3  I_support_tier NUMBER;
  4  BEGIN
  5  CASE I_ticket_priority
  6  WHEN 'HIGH'THEN
  7  I_support_tier:=1;
  8  WHEN 'MEDIUM'THEN
  9  I_support_tier:=2;
 10  WHEN 'LOW'THEN
 11  I_support_tier:=3;
 12  ELSE
 13  I_support_tier:=0;
 14  ELSE
 15  CASE;
 16  DBMS_OUTPUT.PUT_LINE(I_support_tier);
 17  END;
 18  /
PL/SQL program successfully executed.

Summary

shape Key Points

  • PLSQL Conditions execution – The conditional statement describes which code has to be executed.
  • IF statement – Is a programming PLSQL Conditions statement that executes a function if the case is appropriate.
  • IF else statement – If the case is true, it executes some code and if the case is false, it executes the other code
  • code.
  • IF else-if statement – For two or more codes, it executes different codes.
  • Nesting – Nesting of explicit transactions.
  • Case statement – Is an alternate to the if statement.
  • Case expression – Is similar to the simple case statement.