Notes

--1.
CREATE TABLE LoginManager (USER_ID VARCHAR(10), PASSWORD VARCHAR(20) );

--2.

CREATE OR REPLACE PACKAGE PKG_HRD IS
FUNCTION F_PCT_DEPT_WITH_EMP RETURN NUMBER;
FUNCTION F_CHK_MGR_ID (PEMPID VARCHAR) RETURN BOOLEAN;
MGR NUMBER(3);

END PKG_HRD;

-----------------------------------------F1
CREATE OR REPLACE PACKAGE BODY PKG_HRD IS
FUNCTION F_PCT_DEPT_WITH_EMP RETURN NUMBER IS
V_BAGIAN NUMBER(3);
V_KAR NUMBER(3);
BEGIN
SELECT D.DEPARTMENT_NAME, COUNT(E.EMPLOYEE_ID) AS KARYAWAN INTO V_BAGIAN, V_KAR
FROM HR.EMPLOYEES E JOIN HR.DEPARTMENTS D
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;

END;

FUNCTION F_CHK_MGR_ID(PEMPID VARCHAR) RETURN BOOLEAN IS
V_EMPID NUMBER(3);
V_MGR NUMBER(3);
BEGIN
SELECT EMPLOYEE_ID, COUNT(MANAGER_ID) INTO V_EMPID, V_MGR
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = PEMPID;
IF V_MGR > 0 THEN
RETURN TRUE;
MGR :=1;
ELSE
RETURN FALSE;
MGR :=0;
END IF;
END;
END;