fbpx

Procedure to display the departments, which are having total salary greater than?

VijayaTech Labs Blog
Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Procedure without Parameters:

CREATE OR REPLACE PROCEDURE dept_sal_p
IS
CURSOR C1 IS
select d.department_name,e.tot_sal from
(SELECT department_id,sum(salary) tot_sal
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000) e,departments d
where e.department_id=d.department_id;
v_id departments.department_name%TYPE;
v_sal employees.salary%type;
BEGIN
OPEN C1;
DBMS_OUTPUT.PUT_LINE(‘Departments having salary greater than 50K are :’);
LOOP
FETCH C1 INTO v_id,v_sal;
exit when C1%notfound;
DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_sal);
END LOOP;
if c1%rowcount=0 then
DBMS_OUTPUT.PUT_LINE(‘No Departments found’);
end if;
CLOSE C1;
END dept_sal_p;

Procedure with Parameter as total salary limit:

CREATE OR REPLACE PROCEDURE dept_sal_p(sal_limit IN NMUMBER)
IS
CURSOR C1 IS
select d.department_name,e.tot_sal from
(SELECT department_id,sum(salary) tot_sal
FROM employees
GROUP BY department_id
HAVING SUM(salary) > sal_limit) e,departments d
where e.department_id=d.department_id;
v_id departments.department_name%TYPE;
v_sal employees.salary%type;
BEGIN
OPEN C1;
DBMS_OUTPUT.PUT_LINE(‘Departments having salary greater than 50K are :’);
LOOP
FETCH C1 INTO v_id,v_sal;
exit when C1%notfound;
DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_sal);
END LOOP;
if c1%rowcount=0 then
DBMS_OUTPUT.PUT_LINE(‘No Departments found’);
end if;
CLOSE C1;
END dept_sal_p;

Share this post with your friends

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on skype
Share on telegram
Share on whatsapp
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top