All for Joomla All for Webmasters

PL/SQL If

PL/SQL supports the programming language features like conditional statements and iterative statements. Its programming constructs are similar to how you use in programming languages like Java and C++.

Syntax for IF Statement:

There are different syntaxes for the IF-THEN-ELSE statement.

Syntax: (IF-THEN statement):

  1. IF condition
  2. THEN
  3. Statement: {It is executed when condition is true}
  4. END IF;

This syntax is used when you want to execute statements only when condition is TRUE.

Syntax: (IF-THEN-ELSE statement):

  1. IF condition
  2. THEN
  3.    {…statements to execute when condition is TRUE…}
  4. ELSE
  5.    {…statements to execute when condition is FALSE…}
  6. END IF;

This syntax is used when you want to execute one set of statements when condition is TRUE or a different set of statements when condition is FALSE.

Syntax: (IF-THEN-ELSIF statement):

  1. IF condition1
  2. THEN
  3.    {…statements to execute when condition1 is TRUE…}
  4. ELSIF condition2
  5. THEN
  6.    {…statements to execute when condition2 is TRUE…}
  7. END IF;

This syntax is used when you want to execute one set of statements when condition1 is TRUE or a different set of statements when condition2 is TRUE.

Syntax: (IF-THEN-ELSIF-ELSE statement):

  1. IF condition1
  2. THEN
  3.    {…statements to execute when condition1 is TRUE…}
  4. ELSIF condition2
  5. THEN
  6.    {…statements to execute when condition2 is TRUE…}
  7. ELSE
  8.    {…statements to execute when both condition1 and condition2 are FALSE…}
  9. END IF;

It is the most advance syntax and used if you want to execute one set of statements when condition1 is TRUE, a different set of statement when condition2 is TRUE or a different set of statements when both the condition1 and condition2 are FALSE.

When a condition is found to be TRUE, the IF-THEN-ELSE statement will execute the corresponding code and not check the conditions any further.

If there no condition is met, the ELSE portion of the IF-THEN-ELSE statement will be executed.

ELSIF and ELSE portions are optional.

Example of PL/SQL If Statement

Let’s take an example to see the whole concept:

  1. DECLARE
  2.    a number(3) := 500;
  3. BEGIN
  4.    — check the boolean condition using if statement 
  5.    IF( a < 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;

After the execution of the above code in SQL prompt, you will get the following result:

a is not less than 20
value of a is : 500
PL/SQL procedure successfully completed.
PinIt
submit to reddit

Leave a Reply

Top