Benefit of Conditional and Decision Making Statements in PL/SQL Block #DOTC_Mdb

Conditional and  Decision Making Statements in PL/SQL.

 

Today, we will discuss conditions in PL/SQL block which can be used in Procedures, Functions etc.

PL/SQL condition is vastly used in sub-programs were programmer can specify one or more condition evaluated by the program , along with a statement or statements to be executed if the condition is determined to be true it will execute that statement  optionally we have option to execute  other statements if condition  false. In other word we can say Decision Making is just like condition based where if it satisfy the condition it will give the result else we can execute other statement in a program. Condition in PL/SQL is similar to other programming language like C++, Java etc. 

 

Types of Decision Making Statements in PL/SQL:

IF-THEN

IF-THEN-ELSE

IF-THEN-ELSIF

NESTED-IF

CASE

 

IF –THEN  -- The IF-THEN statement is mainly used to execute a particular section of codes only when the condition is true . The condition always give Boolean that is True or false .  It is a basic conditional statement which will allow the program to execute or skip a particular piece of code based on the conditions.

 

Syntax of IF-THEN statement

 

IF <condition: returns Boolean>

THEN

 -executed only if condition is True

 <execute_block>  

END if;

 

In Above Syntax : if condition is true then only <execute_block> will execute , if the condition is false sql will not go to execute_block as it will directly go to END if block . In simple word sql statement will execute only if condition is true.

Pictorial representation of condition and Decision making statement in PL/SQL statement for better understanding which follows all types of decision making statement.

 

Let’s take Simple example of IF- THEN in PL/SQL block by using employee table :

 

DECLARE

v_count number ;

BEGIN

Select count(*) into v_count from employee;

IF v_count>1 THEN

Update employee set salary=30000 where dept_no=30;

END IF;

dbms_output.put_line(‘Condition not satisfied !');

END;

/

Output of above PL/SQL block depends if there is more than 1 records in employee table then it simply update the salary in employee table , but if condition is false it will give Condition not satisfied !.

 

IF-THEN-ELSE Condition :

The IF-THEN-ELSE statement is basically used to select between two choices based on the condition if first condition is not true it will go to else condition and give the result.

Below is the Syntax of IF-THEN-ELSE

IF <condition: returns Boolean>

THEN

            -execute  if the condition is TRUE

            <excute_block>

ELSE

            -execute if the condition is false

            <execute_block>

END if;

 

In Above Syntax it will check the first condition is true it will execute otherwise it will execute the else statement in block.

 

Let’s take one example from same employee table where we see if the count is greater than 1 it will increment the salary else it will insert hard coded values in a table for salary column.

DECLARE

v_count NUMBER;

BEGIN

select count(*) into v_count from employee;

IF v_count >1  THEN

update employee set salary=salary*30 where dept_30;

ELSE

insert into employee(salary) values(50000);

END IF;

END;

/

 

Here we simply check if the count is greater than 1 it will update the employee table for salary increment and if condition is false it will manually insert the salary value in employee table.

 

IF-THEN-ELSIF Statement in PL/SQL Block:

The IF-THEN-ELSIF statement is basically used where one alternative should be chosen from a set of alternatives, where each alternative has its own conditions to be satisfied. If the first condition is true then it will executed and it will not go to remaining condition. The ELSE block will be executed if no conditions is satisfied.

 

Syntax of IF-THEN-ELSIF Statement in PL/SQL Block

IF <conditionl: returns Boolean>

THEN

-execute only if the condition is TRUE

 <execute_block>

ELSIF <condition2 returns Boolean>

<execute_block>

ELSIF <condition3 returns Boolean>

 <execute_block3>

ELSE

<execute_block>

END if;

 

In Above Syntax ELSE is optional  as the ELSE block will be executed if no conditions is satisfied. here the controller found any condition that returns <TRUE>. In this case, the corresponding action_block will be executed and the controller will exit this IF-statement block and will start executing the remaining code.

 

Let’s  see one simple example to check division against mark in PL/SQL block by using IF-THEN-ELSIF statement.

 

DECLARE

v_mark NUMBER :=85;

BEGIN

IF( v_mark >= 70) THEN

dbms_output.put_line(‘1^stDivision’);

ELSIF(v_mark >= 40 AND mark < 70) THEN

dbms_output.put_line(‘2^ndDivision');

ELSIF(v_mark >=35 AND mark < 40) THEN

dbms_output.put_line(‘3^rdDivision);

ELSE

dbms_output.put_line(‘Fail’);

END IF;

END;

/

 

In Above program since the first condition is true so output will be 1^st Division , as in variable we put the value 85 which satisfy the irst condition , so according to value we pass it will check each statement and give the output . if we pass value 30 then it will go to ELSE and output will be Fail.

 

NESTED IF :

 NESTED-IF statement is mainly used by programmers to place one or more 'IF' condition inside another 'IF' condition's <execute_block> other than normal statements.Each IF condition  have a separate END IF  statement which marks the end-of-scope in that particular <execute_block>.The  IF statement will consider the nearest END IF statement as an end for that statement condition.

 

Syntax of Nested IF Statement:

IF <conditionl:  returns Boolean>

THEN

            —executed if the condition is TRUE

            <execute_block1 starts>

            IF <condition2:  returns Boolean>

            THEN

            <excute_block2>

            END IF; —END IF for condition2

<execute_block1 ends>

END IF; —END IF for  condition1

 

In the Above Syntax  the outer IF contains one more IF statement in its execute block. If  condition1 is TRUE, then control will be executing <execute_block1> and checks the condition2. If condition2 is TRUE , then <execute_block2>  also be executed. If condition2 is FALSE then, SQL will skip the <execute_block2>.

 

CASE Statement In PL/SQL:  Case statement in PL/SQL is similar to the IF Else statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple True / False that is Boolean expressions. Unlike IF-THEN-ELSIF, the CASE statement can also be used in SQL statements. ELSE block in CASE statement holds the sequence that needs to be executed when none of the condition got satisfied.

 

Syntax for Case Statement in PL/SQL:

 

CASE (expression)

 WHEN <valuel> THEN action_blockl;

 WHEN <value2> THEN action_block2;

 WHEN <value3> THEN action_block3;

 ELSE action_block_default;

END CASE;

 

In above Syntax we can see not much if then as it is in sequential order and we can put multiple condition in CASE , also we can say It’s bit shortcut method then IF-THEN ELSE. Also CASE statement can be directly use in SQL statement.

Below is the simple example of CASE  for the same marks which we used in  IF-THEN-ELSIF statement.

 

DECLARE

   v_mark char(1) := 'B';

BEGIN

   CASE v_mark

      when 'A' then dbms_output.put_line('Distinction with First Division');

      when 'B' then dbms_output.put_line('1^st Division');

      when 'C' then dbms_output.put_line('2^nd Division');

      when 'D' then dbms_output.put_line('3^rd Division ');

      when 'F' then dbms_output.put_line('Fail');

      else dbms_output.put_line('No Marks');

   END CASE;

END;

/

Output will be 1st Division as we initialize the value of v_mark is B and it's satisfy the condition.Here we see by using Case we can put multiple condition against the requirement only we put the case name and when condition we can print the required output. It’s easy to use as compare to IF-THEN else , but it’s for programmer to opt which one he likes as sometime programmer prefer IF –THEN ELSE instead of Case . One main Benefit of CASE is we can simply use in SQL statement:

 

CASE Statement in  SQL by using employee Table.

 

SELECT emp_id,

CASE

  WHEN emp_id = 10004 and dept_name = 'IT' THEN  'Bangalore office'

  WHEN emp_id= 1005 and dept_name = 'HR'  THEN 'Delhi office'

END

FROM employee;

Here in SQL directly also we can put multiple condition and get the required output by simply using when then condition. Once you know the conditional statement you can explore my PL/SQL procedure/Function blog where you can use Conditional statement in program .  For reference you can follow my below blog

https://dheeraj60.blogspot.com/2020/05/overview-of-plsql-stored-procedure-and.html

https://dheeraj60.blogspot.com/2020/05/stored-function-uses-and-benefits-in.html

 

This is all about Conditional and  Decision Making Statements in PL/SQL. Please go through this blog and try to write conditional statement in PL/SQL Procedures ,Functions  and Packages and let me know if you have any doubts.

 

Thanks