31. CONTROL STATEMENTS

CONTROL STATEMENTS

Control flow

1.   if statement

2.   case statement

If Statements

1. If condition:

Syntax:-

if condition then

                   Do1; (stmts)

          Else if condition then

                   Do2; (stmts)

          Else do3; (stmts)

End if;

 ------------------------------------------------------------------------------------

2. Nested if:

Syntax:-

If condition then

Do1;

If condition then

          Do3;

Else

Do4;

End if;

Else

Do2;

End if;

Ex:-

  declare

     v number(5) :=&n;

  begin

     if v>1000 then

     dbms_output.put_line('hellow given number is > 1000');

  else

     dbms_output.put_line('number is < 1000');

  end if;

  end;/

Output:-

Case Statements

Case:

1.   Simple case

2.   Search case

1. Simple Case Syntax:

          Case [columns/expressions]

                   When condition then

                             Do1;  (stmts)

                   When condition then

                             Do2;  (stmts)

                   When condition then

                             Do3;  (stmts)

                   Else

                             Do4; (stmts)

          End case;

 -------------------------------------------------------------------------------------

2. Search case:

Ex:-

SELECT sal, CASEWHEN sal =5000

THEN'A'

WHEN sal >=3000

THEN'B'

ELSE'C'

ENDCASE,job, deptno

FROM emp

Output: 

------------------------------------------------------------------------------------------------------------------

Ex:-

SELECTSUM(CASE

WHEN sal =5000

THEN sal

WHEN sal >=3000

THEN sal

ELSE sal

END

) sum_sal

FROM emp

 Output:

-------------------------------------------------------------------------------------------------------------------

Ex:-

          Declare

          vsal emp.sal%type;

vno emp.empno%type:=&n;

begin

select sal into vsal from emp where empno = vno;

case when vsal = 5000 then

dbms_output.put_line('sal = 5000');

when vsal >= 3000 then

dbms_output.put_line('sal = 3000');

else

dbms_output.put_line('sal < 3000');

end case;

end;

/

Output:

Note:- In the absence of else part if all of the conditions are false then it throws on an error i.e. case not found. (it throws err ORA-06592, please check below o/p)

Ex:-

declare

vsal emp.sal%type;

vno emp.empno%type:=&n;

begin

select sal into vsal from emp where empno = vno;

case

when vsal > 5000 then

dbms_output.put_line('sal = 5000');

when vsal > 6000 then

dbms_output.put_line('sal = 6000');

end case;

end;

/

Output:

-----------------------------------------------------------------------------------------------------------------

Simple case:-  It is not allow special operators

Ex:-

declare

vsal emp.sal%type;

vno emp.empno%type:=&n;

begin

select sal into vsal from emp where empno = vno;

case vsal

when 5000 then

dbms_output.put_line('sal = 5000');

when 3000 then

dbms_output.put_line('sal = 3000');

else

dbms_output.put_line('sal < 3000');

end case;

end;

/

Output: