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

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;

Leave a Comment

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