1. WAP to display the detail of employees belonging to department no.
10 using
cursor and exception handling.
declare
a emp%rowtype;
begin
select * into a from emp where deptno=10;
dbms_output.put_line(a.empno||a.ename||a.sal);
exception
when too_many_rows
then
dbms_output.put_line('more than one row');
end;
Output:
Statement processed.
more than one row
2. WAP to display the sum salary of a given department no.
declare
a emp.sal%type;
begin
select sum(sal) into a from emp where deptno=60 group by deptno;
dbms_output.put_line(a);
exception
when no_data_found
then
dbms_output.put_line('no data found');
end;
Output:
Statement processed.
no data found
3. WAP to raise a exception if sum sal of a given deptno is gt than 8000.
declare
a emp.sal%type;
sall_greater exception;
begin
select sum(sal) into a from emp where deptno=10 group by deptno;
if a>8000 then raise abc;
else dbms_output.put_line(a);
end if;
exception
when sal_greater then
dbms_output.put_line('Sum of salary of this department is greater than 8000');
end;
Output:
Statement processed.
Sum of salary of this department is greater than 8000
4. WAP to display the names of employee of a given department.
declare
name emp.ename%type;
begin
select ename into name from emp where deptno=10;
dbms_output.put_line(name);
exception
when no_data_found
then
dbms_output.put_line('no data found');
when too_many_rows
then
dbms_output.put_line('too many rows');
end;
Output:
Statement processed.
too many rows
5. WAP to display the name of a given department with user define exception.
declare
name emp.ename%type;
abc exception;
begin
select ename into name from emp where deptno=50;
if name is null then raise abc;
else
dbms_output.put_line(name);
end if;
exception
when abc then
dbms_output.put_line('Name should not be null');
when too_many_rows then
dbms_output.put_line('Too many rows');
end;
Output:
Statement processed.
Name should not be null
6. WAP to raise an exception when the data entered for emp table contain a null
value for deptno.
declare
a emp.ename%type:='AAA';
b emp.sal%type:=5000;
c emp.deptno%type;
v_eno emp.empno%type:=102;
abc exception;
begin
select deptno into c from emp where empno=101;
if c is null then raise abc;
else insert into emp(empno,ename,sal,deptno) values(v_eno,'a',b,c);
dbms_output.put_line('one row updated');
end if;
exception
when abc then
update emp set ename='a' , sal=b , deptno=50 where empno=101;
dbms_output.put_line('one row updated through exception block');
end;
Output:
Statement processed.
one row updated through exception block
