How To Write PL/SQL block using Pre-defined Exception Handling.

INPUT

declare

            lno empl.no%type;

            tmpsal empl.sal%type;

            incr empl.sal%type;

            raise_par number(5);

begin

            lno:=&no;

            select sal,rate into tmpsal,raise_par from empl where no=lno;

            if tmpsal<3000 then

                        update empl set sal=5000 where no=lno;

                        dbms_output.put_line('empno'||lno||' salary incremented by 5000 ');

            else

                        incr:=tmpsal*raise_par/100;

                        update empl set sal=sal+incr where no=lno;

                        insert into inr values(lno,tmpsal,raise_par,incr);

                        dbms_output.put_line('empno '||lno||' salary increment by '||raise_par);

            end if;

exception

            when no_data_found then

                        dbms_output.put_line('empno '||lno||' not belongs to company');      

           

end;

/

OUTPUT

Enter value for no: 104

old   7:  lno:=&no;

new   7:  lno:=104;

empno 104 salary increment by 25

PL/SQL procedure successfully completed.

Enter value for no: 110

old   7:  lno:=&no;

new   7:  lno:=110;

empno 110 not belongs to company

PL/SQL procedure successfully completed.

Table:- inr

    EMP_NO     SALARY   INR_RATE    INR_SAL

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

       104       5000         25       1250