PLSQL Conditional Statements

Oracle Provides following types of statement as shown below

  • IF
  • IF ELSE
  • ELSEIF

IF (Synatx):

IF condition then

statements;

END IF;

IF ELSE (Syntax)

IF condition then

Statements;

ELSE

Statements;

END IF;

To check more number of conditions, then we are using “ ELSIF ”

ELSIF (Syntax):

IF            condition1 then

                Statements;

ELSIF      condition2 then

                Statements;

ELSIF      conditions then

                Statements;

ELSE      

                Statements;

END IF;

Write a program to print message based on parameter values

Example with Syntax:

DECLARE

                A Number(10);

BEGIN

                SELECT deptno

                INTO a from dept

                WHERE deptno = &deptno;

IF A = 10 then

Dbms_output.put_line(‘TEN’);

ELSIF A = 20 then

                Dbms_output.put_line(‘TWENTY’);

ELSIF A = 30 then

                Dbms_output.put_line(‘THIRTY’);

ELSE

                Dbms_output.put_line(‘OTHERS’);

END IF;

END;

After Execution above script we will get following notification:

  • Enter Value for Deptno: 10

Output=> TEN

  • Enter Value for Deptno: 20

Output => TWENTY

  • Enter Value for Deptno: 30

Output => THIRTY

  • Enter Value for Deptno: 50

Output => ORA 1403: no data found

When a PLSQL block contains “ SELECT INTO clause ” and also if requested data is not available in a table then oracle server returns an error “ ORA 1403: no data found ”.

In the above program, if we are using emp table in place of dept and also requested data is available, then oracle server returns an error “ ORA-1422: exact fetch return more than requested number of row ”, because whenever “ SELECT INTO clause ” try to return more than one value then oracle server returns “ ORA-1422 ” Error.

Whenever PLSQL block contains pure DML statements and also if requested data is not available in a table then oracle server does not return any error message to handle these types of blocks we are using “ IMPLICIT CURSOR ATTRIBUTES ”

Example with Syntax:

BEGIN

                DELETE FROM emp

                WHERE empno = 9999;

END;

  • Output: PLSQL Procedure Successfully Completed