PLSQL

PL/SQL CONDITIONS

PL/SQL CONDITIONS

All the decision-making structures that require the programmer specifies one or more conditions that to be evaluated or to be tested by the program, along with the statements or that statements to be executed that is if the condition is determined to be false. Given below is the structure found in most of the programming languages –


Decision making statements in PL/SQL

 

This language provides the following types of decision-making statements. 

1.IF-THEN statement

This is the simplest form of the IF control statement, that is frequently used in the Decision making and changing the control flow for the program execution. This associates a condition with the sequence of statements that are enclosed by the keywords THEN and END IF. If the condition is said to be TRUE, then the statements are executed and if the condition is said to be FALSE or NULL, then the IF statement does nothing.
 


PL/SQL if-then statement

Syntax:

IF condition THEN 
S;
END IF;
Where condition is said to be Boolean or the relational condition and S is a simple or the compound statement. Let us see the example 
IF (x==10) THEN
c := c+1;
END IF;
/
If the given Boolean expression evaluates to true, then the code within the if statement will get executed, else if it evaluates to false, then the first set of the code after the end of the IF statement gets executed. 
Example:
DECLARE
a number (2) := 8;
BEGIN
a:= 8;
-- check the Boolean condition using if statement 
IF (a<20) THEN
dbms_output.put_line(‘a is less than 20’);
END IF;
dbms_output.put_line(‘value of a is :’ || a);
END;
/

When we execute the code, we get following the result:
 a is less than 20
value of a is: 8

PL/SQL procedure successfully completed.

 

2.IF-THEN-ELSE statement

This is followed by a sequence of IF-THEN statements and that can be followed by an optional sequence of ELSE statements, that are executed when the condition is FALSE.


PL/SQL if-then-else statement

Syntax:

IF condition THEN 
S1;
ELSE
S2;
END IF;

PL/SQL procedure successfully completed.
 

3.IF-THEN-ELSEIF statement

This condition statement allows you to choose between several alternatives. This condition statement can be followed by an optional ELSEIF…ELSE statements. This ELSIF clause lets us for adding additional conditions.

When we use this condition statement, we have to remember certain points like

  • It is ELSIF, not ELSEIF
  • This statement can have zero or one ELSE’S and this comes after any ELSIF’S.
  •  This statement can have zero or one ELSIF’S and this comes before any ELSE.
  • If the ELSIF succeeds, remaining ELSIF’S OR ELSE’S will not be tested.

Syntax:

IF (boolean_expression 1) THEN 
S1; -- executes when the boolean expression 1 is true.
ELSIF (boolean_expression 2) THEN 
S2; -- executes when the boolean expression 2 is true.
ELSIF (boolean_expression 3) THEN
S3; -- executes when the boolean expression 3 is true.
ELSE
S4; -- executes when the none of the above condition is true.
END IF;
   Example:
DECLARE 
   x number(3) := 10; 
BEGIN 
   IF ( x = 10 ) THEN 
      dbms_output.put_line(' x is 10' ); 
   ELSIF ( x = 20 ) THEN 
      dbms_output.put_line(' x is 20' ); 
   ELSE 
       dbms_output.put_line('None of the values is matching'); 
   END IF; 
   dbms_output.put_line(' value of x is: '|| x );  
END; 
/
When we execute the above code, we will get following result:
x is 10 
Exact value of x is: 100  

PL/SQL procedure successfully completed

4.Case statement

It is same like IF statement, this statement selects one sequence of statements to execute. Therefore, to select the sequence, the CASE statement uses a selector rather than the multiple Boolean expressions. Selector is said to be an expression, that is the value of which is used to select one of the several alternatives.


case statement in PL/SQL

Syntax:
CASE selector
WHEN ‘value1’ THEN S1;
WHEN ‘value1’ THEN S2;
WHEN ‘value1’ THEN S3;
WHEN ‘value1’ THEN S4;
…..
ELSE Sn; -- default case
END CASE;

Example:
DECLARE 
   grade char(1) := 'B'; 
BEGIN 
   case  
      when grade = 'A' then dbms_output.put_line('Excellent'); 
      when grade = 'B' then dbms_output.put_line('Very good'); 
      else dbms_output.put_line('No such grade'); 
   end case; 
END; 
/
When we execute the above code, we get following oputput1:
Very Good
PL/SQL procedure successfully complete

5.Searched CASE statement

This condition statement has no selector and the WHEN clauses of the statement contain search conditions that gives Boolean values.

 


case statement in PL/SQL

Syntax:
CASE
WHEN selector = ‘value1’ THEN S1;
WHEN selector = ‘value2’ THEN S2;
WHEN selector = ‘value3’ THEN S3;
WHEN selector = ‘value4’ THEN S4;
….
ELSE Sn; -- default case
END;
 Example: 
DECLARE 
   grade char(1) := 'A'; 
BEGIN 
   case  
      when grade = 'A' then dbms_output.put_line('Excellent'); 
      when grade = 'B' then dbms_output.put_line('Very good'); 
      else dbms_output.put_line('No such grade'); 
   end case; 
END; 
/
When we execute the above code, we get following output
Excellent
PL/SQL procedure successfully completed.
Nested IF-THEN-ELSE
It is legal in PL/SQL programming for nesting the IF-ELSE statements, that means we can use one IF or ELSIF statement inside another IF or ELSE IF statements.


Syntax:
IF (boolean_expression 1) THEN
-- executes when the given boolean expression 1 is said to be true
IF (boolean_expression 2) THEN
-- executes when the given boolean expression 2 is said to be true
Sequence of statements;
END IF;
ELSE 
-- executes when the given boolean expression 1 is said to be not true
END IF;
 Example:
DECLARE 
   a number(3) := 10; 
   b number(3) := 20; 
BEGIN 
   -- check the boolean condition  
   IF( a = 10 ) THEN 
   -- if condition is true then check the following  
      IF( b = 200) THEN 
      -- if condition is true then it  prints the following  
      dbms_output.put_line('Value of a is 10 and b is 20' ); 
      END IF; 
   END IF; 
   dbms_output.put_line(' value of a is : ' || a ); 
   dbms_output.put_line('value of b is : ' || b ); 
END; 
/

When we execute the above code, we get following output
Value of a is 10 and b is 20 
value of a is: 10
 value of b is: 20

PL/SQL procedure successfully completed.