Decision Making in PL/SQL

Last Updated : 14 Aug, 2024

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension to SQL that allows for procedural programming within databases. It features various conditional statements to control the flow of execution based on specific conditions.

In this article, We will learn about the various PL/SQL Conditional Statements in detail with the help of examples and so on.

PL/SQL Conditional Statements

PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL used in Oracle databases to write procedural code. It includes various conditional statements that allow developers to execute different blocks of code based on specific conditions. Decision-making statements in programming languages decide the direction of the flow of program execution. Conditional Statements available in PL/SQL are defined below:

IF THEN
IF THEN ELSE
NESTED-IF-THEN
IF THEN ELSIF-THEN-ELSE Ladder

1. IF THEN

if then the statement is the most simple decision-making statement. It is used to decide whether a certain statement or block of statements will be executed or not i.e if a certain condition is true then a block of statement is executed otherwise not.

Syntax:

if condition then
-- do something
end if;

Here, condition after evaluation will be either true or false. if statement accepts boolean values – if the value is true then it will execute the block of statements below it otherwise not. if and endif consider as a block here.

Example:

SQL

declare -- declare the values here  begin  if condition then dbms_output.put_line('output');  end if;  dbms_output.put_line('output2'); end;

SQL

-- pl/sql program to illustrate If statement declare num1 number:= 10; num2 number:= 20;  begin  if num1 > num2 then dbms_output.put_line('num1 small'); end if;  dbms_output.put_line('I am Not in if');  end;

As the condition present in the if statement is false. So, the block below the if statement is not executed. Output:

I am Not in if

2. IF THEN ELSE

The if statement alone tells us that if a condition is true it will execute a block of statements and if the condition is false it won’t. But what if we want to do something else if the condition is false. Here comes the else statement. We can use the else statement with if statement to execute a block of code when the condition is false.

Syntax:-

if (condition) then
    -- Executes this block if
    -- condition is true
else 
    -- Executes this block if
    -- condition is false

Example:-

SQL

-- pl/sql program to illustrate If else statement declare num1 number:= 10; num2 number:= 20;  begin  if num1 < num2 then dbms_output.put_line('i am in if block');  ELSE dbms_output.put_line('i am in else Block'); end if;  dbms_output.put_line('i am not in if or else Block'); end;

Output:-

i'm in if Block
i'm not in if and not in else Block

The block of code following the else statement is executed as the condition present in the if statement is false after calling the statement which is not in block(without spaces).

3. NESTED-IF-THEN

A nested if-then is an if statement that is the target of another if statement. Nested if-then statements mean an if statement inside another if statement. Yes, PL/SQL allows us to nest if statements within if-then statements. i.e, we can place an if then statement inside another if then statement. Syntax:-

if (condition1) then
   -- Executes when condition1 is true
   if (condition2) then 
     -- Executes when condition2 is true
   end if; 
end if;

SQL

-- pl/sql program to illustrate nested If statement declare num1 number:= 10; num2 number:= 20; num3 number:= 20;  begin if num1 < num2 then dbms_output.put_line('num1 small num2');    if num1 < num3 then     dbms_output.put_line('num1 small num3 also');   end if;  end if;  dbms_output.put_line('after end if'); end;

Output:-

num1 small num2
num1 small num3 also
after end if

4. IF THEN ELSIF-THEN-ELSE Ladder

Here, a user can decide among multiple options. The if then statements are executed from the top down. As soon as one of the conditions controlling the if is true, the statement associated with that if is executed, and the rest of the ladder is bypassed. If none of the conditions is true, then the final else statement will be executed. Syntax:-

if (condition) then
    --statement
elsif (condition) then
    --statement
.
.
else
    --statement
endif

Flow Chart:-

Example:-

SQL

-- pl/sql program to illustrate if-then-elif-then-else ladder declare num1 number:= 10; num2 number:= 20;  begin  if num1 < num2 then dbms_output.put_line('num1 small');  ELSEIF num1 = num2 then dbms_output.put_line('both equal');  ELSE dbms_output.put_line('num2 greater'); end if;  dbms_output.put_line('after end if'); end;

Output:-

num1 small
after end if

Conclusion

PL/SQL conditional statements are essential for effective procedural programming in Oracle databases. The IF THEN and IF THEN ELSE statements provide straightforward decision-making, while NESTED-IF-THEN supports complex nested logic. The IF THEN ELSIF-THEN-ELSE ladder allows handling multiple conditions in a structured manner.

Math Functions in PL/SQL

devanshuagarwal

Improve

Article Tags :