PL/SQL Programs – Branching Statements

Like any procedural programming language, PL/SQL programs can also using Branching structures to control flow of logic. The conditional or branching statements of PL/SQL are

  • IF-THEN-END IF
  • IF-THEN-ELSE-END IF
  • IF-THEN-ELSIF-END IF
  • CASE-WHEN-ELSE-END

PL/SQL programs -Conditional Statements

The conditional statements are used in PL/SQL programs when you have to execute a set of statements when a specific condition is true.

IF-THEN-END IF

In the simple IF-THEN-END IF statement the condition given with IF is checked. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, statements after END IF are executed.

Syntax

IF (condition) THEN

                Then Statements block

END IF;

PL/SQL Programs IF-THEN ENDIF

Example

DECLARE
inVal NUMBER:=10;
BEGIN
IF (MOD(inVal,2)=0) THEN
    DBMS_OUTPUT.PUT_LINE('Even Number');
END IF;
END;

IF-THEN-ELSE-END IF

In the IF-THEN-ELSE – END IF statement the condition given after IF is checked. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, statements after ELSE are executed. After following either ELSE or THEN, the statements after END IF are executed

Syntax

IF (condition) THEN

                Then Statements block

ELSE
                ELSE Statements block

END IF;

IF THEN ELSE
DECLARE
inVal NUMBER:=35;
BEGIN
IF (MOD(inVal,2)=0) THEN
    DBMS_OUTPUT.PUT_LINE('Even Number');
ELSE
    DBMS_OUTPUT.PUT_LINE('Odd Number');
END IF;
END;

IF-THEN-ELSIF-THEN-END IF

In the IF-THEN-ELSIF-THEN – END IF statement, IF condition is checked at entry point. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, the ELSIF condition is checked. If ELSIF condition becomes true the statements after THEN are executed else statements after ELSE are executed.

Syntax

IF (condition) THEN

ELSIF (contition) THEN

                Then Statements block

ELSE
                ELSE Statements block

END IF;

IF THEN ELSIF END IF

Example

DECLARE
inVal NUMBER:=67;
BEGIN
IF (inVal=50) THEN
    DBMS_OUTPUT.PUT_LINE('Exact Half');
ELSIF (inVal>50) THEN
    DBMS_OUTPUT.PUT_LINE('Gone Forward');
ELSE   
    DBMS_OUTPUT.PUT_LINE('Gone Backward');
END IF;
END;

CASE-WHEN-ELSE-END CASE

CASE-WHEN-ELSE-END CASE statement is like a nested IF-THEN-ELSE statements. With CASE keyword the selector variable or expression is given. Wherever the values after WHEN matches the selected the following block of statements are executed. Then the control is passed to END CASE. If no WHEN value matches with the selector, the statements block of ELSE is executed.

Syntax

CASE selector-name|expression

WHEN value1 THEN Statement block 1;

WHEN value1 THEN Statement block 2;

WHEN value  n THEN Statement block n;

[ELSE Statement block;]

END CASE;

CASE WHEN THEN END CASE

Example

DECLARE
intday NUMBER:=4;
BEGIN
CASE intday
WHEN 1 THEN DBMS_OUTPUT.PUT_LINE ('Sunday');
WHEN 2 THEN DBMS_OUTPUT.PUT_LINE ('Monday');
WHEN 3 THEN DBMS_OUTPUT.PUT_LINE ('Tuesday');
WHEN 4 THEN DBMS_OUTPUT.PUT_LINE ('Wednesday');
WHEN 5 THEN DBMS_OUTPUT.PUT_LINE ('Thursday');
WHEN 6 THEN DBMS_OUTPUT.PUT_LINE ('Friday');
WHEN 7 THEN DBMS_OUTPUT.PUT_LINE ('Saturday');
ELSE DBMS_OUTPUT.PUT_LINE ('WRONG DAY NUMBER');
END CASE;
END;

Note: You can nest the branching statements in your PL/SQL programs.

2 Comments

  1. harsh said:

    bohot acha example diye ho

    November 9, 2020
    Reply
    • admin said:

      Thanks Harsh.

      November 9, 2020
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *