NESTED IF's in PL/SQL

Question

I am trying to write nested IFs in a pl/sql block. How do I arrange properly.

IF r1.CABLE_TYPE = "A" THEN
var_root = FC_CPSCBPR1.C_111_SCPSCBP

Logic I have to include now

If var_root is "TRUE" /*If the value is populated*/
  THEN 
       IF ...
            THEN
Elsif var_root is "FALSE" /*If the value is not found*/

      THEN

Not sure if this is part of your issue but you need single quotes for character literals, e.g. `'A'`, not `"A"`. — William Robertson, Oct 25 '16 at 22:02
Your question is unclear. What trouble are you having with nested IF statements? — APC, Oct 26 '16 at 06:51

Gerrat · Accepted Answer · 2016-10-25T21:42:46.820

Have a look at this example for nested IF statements.
You need an END IF to end each.

DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus  NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
      IF whatever_else_you_like THEN
        do_something_here;
      END if;
    ELSE
      IF sales > quota THEN
        bonus := 50;
      ELSE
        bonus := 0;
      END IF;
    END IF;     
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);     
    UPDATE employees
    SET salary = salary + bonus 
    WHERE employee_id = emp_id;
  END p;
BEGIN
  p(10100, 10000, 120);
  p(10500, 10000, 121);
  p(9500, 10000, 122);
END;
/

You can also have as many ELSIF statements as you need. eg.

DECLARE
  PROCEDURE p (sales NUMBER)
  IS
    bonus  NUMBER := 0;
  BEGIN 
    IF sales > 50000 THEN
      bonus := 1500;
    ELSIF sales > 35000 THEN
      bonus := 500;
    ELSE
      bonus := 100;
    END IF;

    DBMS_OUTPUT.PUT_LINE (
      'Sales = ' || sales || ', bonus = ' || bonus || '.'
    );
  END p;
BEGIN
  p(55000);
  p(40000);
  p(30000);
END;
/

If I ask, I have to include one more IF statement when my bonus := (sales - quota)/4 is satisfied. — Soom Satyam, Oct 25 '16 at 21:37
@SoomSatyam: I added an IF statement in there - you should be able to nest these to arbitrary levels using the examples above. — Gerrat, Oct 25 '16 at 21:47

score 1 · Answer 2 · edited Sep 25 '17 at 02:22

Look at this PL/SQL block structure example - you need to remember to end each one of IF statement as a closed block of code using END IF and semicolon ;.

BEGIN

  IF 10 > 5 THEN

    IF 10 < 20 THEN
      dbms_output.put_line('statement 1 from nested if');
    ELSE
      dbms_output.put_line('statement 2 from nested if');
    END IF;

  ELSE
    dbms_output.put_line('statement not from nested if');
  END IF;

END;

NESTED IF's in PL/SQL

2 Answers2