Exception example

Simple System Defined Exception Example
========================================

create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_sal employees.salary%TYPE;
begin
select salary into v_sal from employees where employee_id=p_eno;
dbms_output.put_line('Salary is: '||v_sal);
end;
/


SQL> exec proc1(100);
Salary is: 24000

PL/SQL procedure successfully completed.

SQL> exec proc1(999);
BEGIN proc1(999); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.PROC1", line 5
ORA-06512: at line 1


create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_sal employees.salary%TYPE;
begin
select salary into v_sal from employees where employee_id=p_eno;
dbms_output.put_line('Salary is: '||v_sal);
exception
when no_data_found then
dbms_output.put_line('Employee id does not exists');
end;
/

SQL> exec proc1(999);
Employee id does not exists

PL/SQL procedure successfully comple





=============================
Simple user defined exception
=============================

create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_max_sal employees.salary%TYPE;
v_sal employees.salary%TYPE;
e_sal exception;
pragma exception_init(e_sal,-100);
begin
select MAX(salary) into v_max_sal from test1;
select salary into v_sal from test1 where employee_id=p_eno;
if v_sal >= v_max_sal then
raise e_sal;
else
dbms_output.put_line('Eligible for increment');
end if;
exception
when e_sal then
dbms_output.put_line('Not eligible for increment');
end;
/

SQL> select employee_id from test1 where salary=(select max(salary) from test1);

EMPLOYEE_ID
-----------
        100

SQL> exec proc1(101);
Eligible for increment

PL/SQL procedure successfully completed.

SQL> exec proc1(100);
Not eligible for increment

PL/SQL procedure successfully completed.