1. Get the number of employees working under a given employee.
create or replace procedure nos(e1 [Link]%type) is
cnt number;
begin
select count(*) into cnt from emp where mgr=e1;
dbms_output.put_line(cnt);
exception
when no_data_found then
dbms_output.put_line('wrong empno');
end nos;
2. Get the number of employees working in given department name.
create or replace procedure nosd(d1 [Link]%type) is
cnt number;
begin
select count(*) into cnt from emp,dept where
[Link]=[Link] and dname=d1;
dbms_output.put_line(cnt);
exception
when no_data_found then
dbms_output.put_line('wrong dept name');
end nosd;
3. Create a Procedure to accept an Empno, and a salary increase amount, if Empno is
not found or current salary is NULL then raise exceptions otherwise display total
salary.
create or replace procedure empis(e1 [Link]%type,in1 [Link]%type) is
s1 [Link]%type;
nusal exception;
nsal [Link]%type;
begin
select sal into s1 from emp where empno=e1;
if s1 is null then
raise nusal;
else
nsal:=s1+in1;
dbms_output.put_line(nsal);
end if;
exception
when nusal then
dbms_output.put_line('given emp. sal is null');
when no_data_found then
dbms_output.put_line('wrong empno');
end empis;
Programs on Functions
1. Write a program to check whether the given number is Prime or not.
INPUT:
create or replace function prime(n number) return number as
cnt number;
begin
cnt:=0;
for i in 1..n loop
if mod(n,i)=0 then
cnt:=cnt+1;
end if;
end loop;
return cnt;
end prime;
declare
num number;
count1 number;
begin
num:=#
count1:=prime(num);
if count1>2 then
dbms_output.put_line(num||''||'is not a prime number');
else
dbms_output.put_line(num|| ''||'is prime');
end if;
end;
Write a program to check for the existence of P# in the table parts
INPUT:
create or replace function ex(pno p.p#%type) return number as
pnum p.p#%type;
cnt number;
begin
cnt:=0;
select p# into pnum from p where p#=pno;
if pno=pnum then
cnt:=1;
end if;
return cnt;
end ex;
declare
n p.p#%type;
i number;
begin
n:='&n';
i:=ex(n);
if i=1 then
dbms_output.put_line('given'||''||n||'is in the table');
end if;
exception
when no_data_found then
dbms_output.put_line('given'||''||n||'is not in the table');
end;
Programs on Packages
1. Write a package “EMPPACK” with the following function/Procedures
a) To insert an Employee
b) To delete an Employee
c) To List employees in a given Dept (Deptno/Dept name)
Package Specification:
create or replace package emppack is
procedure ins2(e1 [Link]%type,en [Link]%type,j1 [Link]%type,
m1 [Link]%type,h1 [Link]%type,s1 [Link]%type,
c1 [Link]%type,d1 [Link]%type);
procedure del1(e1 [Link]%type);
procedure noe1(d [Link]%type);
end emppack;
Package Body:
create or replace package body emppack is
procedure ins2(e1 [Link]%type,en [Link]%type,j1 [Link]%type,
m1 [Link]%type,h1 [Link]%type,s1 [Link]%type,
c1 [Link]%type,d1 [Link]%type) is
begin
insert into emp1 values(e1,en,j1,m1,h1,s1,c1,d1);
end ins2;
procedure del1(e1 [Link]%type) is
begin
delete from emp1 where empno=e1;
end del1;
procedure noe1(d [Link]%type) is
c number(2);
begin
select count(empno) into c from emp where deptno=d;
dbms_output.put_line(c);
end noe1;
end emppack;
Procedure
[Link] a procedure to update salary of given employee by 10%
2. Write a procedure to count number of students taken the given course
[Link] a procedure to list the number courses taken by the given instructor
4. Write a procedure to find the sum of salaries of employees belongs to given department
name
Functions
1. Write a function to find the given number is palindrome or not
2. Write a function to count number of students taken the given course
3. Write a function to average salary of the given department
4. Write a function to find the employees working under given manager
Create a student package which find the total courses, total credits ,name of the course opted by
the student
