Scribd
Upload
17 views4 pages

Task 12

The document contains PL/SQL programs demonstrating exception handling for predefined and user-defined exceptions, as well as the creation of a package specification and body for DML operations on a student table. It includes code snippets for selecting employee details, updating salaries, and performing insert, delete, and update operations. The programs also handle errors such as no data found and too many rows, providing appropriate output messages.

Uploaded by

vvraormhs
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views4 pages

Task 12

The document contains PL/SQL programs demonstrating exception handling for predefined and user-defined exceptions, as well as the creation of a package specification and body for DML operations on a student table. It includes code snippets for selecting employee details, updating salaries, and performing insert, delete, and update operations. The programs also handle errors such as no data found and too many rows, providing appropriate output messages.

Uploaded by

vvraormhs
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Task-12

1. Write a PL/SQL program to handle predefined exceptions.


Program:
declare
v_id number(6):=&employee_id;
v_sal emp.salary%type;
v_name emp.ename%type;
v_job emp.job%type;
begin
select ename, salary into v_name, v_sal from emp where empno=v_id;
DBMS_OUTPUT.PUT_LINE(v_name||q'['s salary is ]'||v_sal);
select job into v_job from emp where ename=v_name;
DBMS_OUTPUT.PUT_LINE(v_name||q'['s job is ]'||v_job);
EXCEPTION
when no_data_found then
DBMS_OUTPUT.PUT_LINE('No employee with ID:'||v_id);
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('Many employees with Name:'||v_name);
when others then
DBMS_OUTPUT.PUT_LINE('Some other error occured');
end;
/
Output:

2. Write a PL/SQL program to handle user defined exception.


Program:

DECLARE
v_dept number:=&department_id;
e_nodept exception;
BEGIN
update emp set salary=salary+1050 where depno=v_dept;
IF SQL%notfound then
raise e_nodept;
ELSE
DBMS_OUTPUT.PUT_LINE(SQL%rowcount||' rows updated');
END IF;
EXCEPTION
when e_nodept then
DBMS_OUTPUT.PUT_LINE('No Department with ID:'||v_dept);
END;
/
Output:

3)Write a PL/SQL code to create


a. Package specification.
Program:
create or replace package pack_dml is
procedure proc_dml(p_id number,choice number);
END pack_dml;
/
Output:

b. Package body for the insert, retrieve, update and delete operations on student table.
Program:
create or replace package body pack_dml is
procedure proc_dml(p_id number,choice number) is
v_name varchar2(20);
v_total number;
BEGIN
case choice
when 1 then
DBMS_OUTPUT.PUT_LINE('Insertion...');
insert into emp values(p_id,'Mahesh','supervisor',2,7,35000,1000,'14-sep-04');
when 2 then
DBMS_OUTPUT.PUT_LINE('Deletion...');
delete from emp where empno=p_id;
when 3 then
DBMS_OUTPUT.PUT_LINE('Updation...');
update emp set commission=commission+100 where empno=p_id;
when 4 then
select ename,sal into v_name,v_total from emp where empno=p_id;
DBMS_OUTPUT.PUT_LINE('salary of '||v_name||' is '||v_total);
end case;
DBMS_OUTPUT.PUT_LINE('DML operation performed on '||SQL%rowcount||' rows');
END proc_dml;
END pack_dml;
/

BEGIN
pack_dml.proc_dml(&EmpID,&choice1or2or3or4);
END;
/

Output:

You might also like