Write a PL/SQL block to insert salary of an employee

PL/SQL- Exception

  • An error occurred during the execution of program is called exception in PL/SQL
  • PL/SQL provides the facility to catch errors by declaring conditions in exception block in the program and necessary action to be taken to rectify the error. Exception can be user defined (these are logical error defined by user) or internally defined.
    For example: The division by zero error.
  • Internal exceptions are raised automatically by the runtime system.
  • User defined exception should be raised explicitly by RAISE statements.
Q. Write a PL/SQL block to help user to insert salary of an employee.

I) Display the name of the employee (from the emp table) to know that, the entered salary is present in the table 'emp'.

II) Display the appropriate messages by using exceptions such as 'no_data_found'.

Answer:

Create a table 'emp':

CREATE TABLE emp ( enpno varchar(4), empname varchar(30), designation varchar2(10), category char(1), basicsalary number(4), joined date )


emp table

Exception block:

DECLARE mysal number;
sal NUMBER:=&salary;
BEGIN
SELECT basicsalary INTO mysal
FROM emp
WHERE basicsalary =sal;
exception WHEN too_many_rows THEN dbms_output.put_line('too many data found in result can''t handel ');
WHEN no_data_found THEN dbms_output.put_line('no data found for your query');
END;
/


Output:

emp salary
▲