PL.SQL

CREATE OR REPLACE FUNCTION FIND_AVG(D_ID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
AVG_SALARY EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT AVG(SALARY) INTO avg_salary
FROM EMPLOYEES
WHERE department_id = d_id;
RETURN avg_salary;
END FIND_AVG;

--INPUT: EMPLOYEE ID
--OUTPUT: BOOLEAN (EMPLOYEE HAS SALARY HIGHER THAN AVERAGE OF HIS DEPARTMENT)






CREATE OR REPLACE FUNCTION EVAL_SAL(E_ID EMPLOYEES.EMPLOYEE_ID% TYPE,
OUT_SALARY OUT EMPLOYEES.SALARY%TYPE)
RETURN BOOLEAN
IS
E_SAL EMPLOYEES.SALARY%TYPE;
AVG_SAL EMPLOYEES.SALARY%TYPE;
D_ID EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
SELECT SALARY,DEPARTMENT_ID INTO E_SAL,D_ID
FROM EMPLOYEES
WHERE employee_id = E_ID;
out_salary :=E_SAL;
SELECT AVG(SALARY) INTO avg_sal
FROM EMPLOYEES
WHERE department_id = d_id;
IF E_SAL> avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END EVAL_SAL;

SELECT * FROM EMPLOYEES;

DECLARE
X BOOLEAN;
Y EMPLOYEES.SALARY%TYPE :=0;
BEGIN
X:= eval_sal(195,Y);
IF X THEN
DBMS_OUTPUT.put_line(Y|| ' ABOVE AVERAGE');
ELSE
DBMS_OUTPUT.put_line(Y||' BELOW AVERAGE');
END IF;
END;





DECLARE
AVG_S EMPLOYEES.SALARY%TYPE;
BEGIN
AVG_S := FIND_AVG(80);
DBMS_OUTPUT.put_line(AVG_S);
END;





DECLARE
CURSOR C_E IS
SELECT FIRST_NAME,SALARY
FROM EMPLOYEES
WHERE department_id = 80;
F_N EMPLOYEES.FIRST_NAME%TYPE;
E_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
OPEN C_E;
LOOP
FETCH C_E INTO f_n, e_sal;
EXIT WHEN C_E%NOTFOUND;
DBMS_OUTPUT.put_line(f_n ||' '|| e_sal);
END LOOP;
CLOSE C_E;
END;







DECLARE
CURSOR TEST_E IS
SELECT SALARY, FIRST_NAME, department_id
FROM EMPLOYEES;
D_ID EMPLOYEES.DEPARTMENT_ID%TYPE;
E_SAL EMPLOYEES.SALARY%TYPE;
E_N EMPLOYEES.FIRST_NAME%TYPE;
D_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE;
BEGIN
OPEN TEST_E;
LOOP
FETCH TEST_E INTO e_sal, e_n, d_id;
SELECT DEPARTMENT_NAME INTO D_NAME FROM DEPARTMENTS WHERE department_id = d_id;
EXIT WHEN TEST_E%NOTFOUND;
IF E_SAL>FIND_AVG(D_ID) THEN
DBMS_OUTPUT.put_line(E_N ||' ABOVE AVERAGE '|| d_name);
ELSE
DBMS_OUTPUT.put_line(E_N ||' BELOW AVERAGE '|| d_name);
END IF;
END LOOP;
CLOSE TEST_E;
END;