Scribd
Upload
32 views

Programs Procedures and Functions

1. The document contains examples of PL/SQL procedures and functions to perform operations on database tables like employees and departments. These include procedures to get employee counts by manager or department, update salaries, and insert/delete records. 2. It also includes functions to check if a number is prime, check if a part exists in the parts table, and check if a number is a palindrome. 3. Finally, it shows an example of a PL/SQL package named EMPPACK with procedures to insert, delete, and get employee counts by department. The package has a specification and body section.

Uploaded by

priyanka tata
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
32 views

Programs Procedures and Functions

1. The document contains examples of PL/SQL procedures and functions to perform operations on database tables like employees and departments. These include procedures to get employee counts by manager or department, update salaries, and insert/delete records. 2. It also includes functions to check if a number is prime, check if a part exists in the parts table, and check if a number is a palindrome. 3. Finally, it shows an example of a PL/SQL package named EMPPACK with procedures to insert, delete, and get employee counts by department. The package has a specification and body section.

Uploaded by

priyanka tata
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

1. Get the number of employees working under a given employee.

create or replace procedure nos(e1 emp.empno%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 dept.dname%type) is


cnt number;
begin
select count(*) into cnt from emp,dept where
emp.deptno=dept.deptno 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 emp.empno%type,in1 emp.sal%type) is


s1 emp.sal%type;
nusal exception;
nsal emp.sal%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 emp1.empno%type,en emp1.ename%type,j1 emp1.job%type,
m1 emp1.mgr%type,h1 emp1.hiredate%type,s1 emp1.sal%type,
c1 emp1.comm%type,d1 emp1.deptno%type);
procedure del1(e1 emp1.empno%type);
procedure noe1(d emp.deptno%type);
end emppack;

Package Body:

create or replace package body emppack is

procedure ins2(e1 emp1.empno%type,en emp1.ename%type,j1 emp1.job%type,


m1 emp1.mgr%type,h1 emp1.hiredate%type,s1 emp1.sal%type,
c1 emp1.comm%type,d1 emp1.deptno%type) is
begin
insert into emp1 values(e1,en,j1,m1,h1,s1,c1,d1);
end ins2;

procedure del1(e1 emp1.empno%type) is


begin
delete from emp1 where empno=e1;
end del1;

procedure noe1(d emp.deptno%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
1.Write a procedure to update salary of given employee by 10%
2. Write a procedure to count number of students taken the given course
3.Write 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

You might also like