Notes

--PERCOBAAN INI DILAKUKAN DI ORACLE DATABASE SENDIRI, DENGAN LOG-IN MENGGUNAKAN USER HR



--NAMA FUNCTION, NAMA PROCEDURE, DAN NAMA VARIBALE MOHON BANGET UNTUK DIUBAH, KECUALI YANG SOAL 1 "CREATE TABLE", TINGGAL IKUTI SYNTAX NYA DISITU.




--Create Table
CREATE TABLE DEPARTMEN AS SELECT * FROM DEPARTMENTS;

--Function 1
CREATE OR REPLACE FUNCTION CETAK_DEPT_ID(DEPT_ID_AKHIR NUMBER)
RETURN NUMBER IS
DEPT_ID NUMBER;
BEGIN
DEPT_ID := DEPT_ID_AKHIR + 10;
RETURN DEPT_ID;
END;

--Funtion 2
CREATE OR REPLACE FUNCTION CETAK_MANAGER_ID(MAN_ID NUMBER)
RETURN NUMBER IS
FIND_MANAGER NUMBER;
BEGIN
SELECT COUNT(E.EMPLOYEE_ID) INTO FIND_MANAGER
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID AND D.MANAGER_ID = MAN_ID;

IF FIND_MANAGER > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

--Function 3
CREATE OR REPLACE FUNCTION CETAK_LOCATION_ID(LOC_ID NUMBER)
RETURN NUMBER IS
FIND_LOCATION NUMBER;
CHECK_LOCATION NUMBER;
BEGIN
SELECT COUNT(L.LOCATION_ID), COUNT(D.LOCATION_ID) INTO FIND_LOCATION, CHECK_LOCATION
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID AND L.LOCATION_ID = LOC_ID;

IF FIND_LOCATION > 0 THEN
IF CHECK_LOCATION < 3 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0;
END IF;
END;

--Procedure
CREATE OR REPLACE PROCEDURE TAMBAH_DATA_DEPARTEMEN(PDEPT_NAME VARCHAR2, PMANAGER_ID NUMBER, PLOCATION_ID NUMBER) IS
DATA_DEPT_ID NUMBER(4);
BEGIN
SELECT CETAK_DEPT_ID(MAX(DEPARTMENT_ID)) INTO DATA_DEPT_ID FROM DEPARTMENTS;

IF CETAK_MANAGER_ID(PMANAGER_ID) = 1 THEN
IF CETAK_LOCATION_ID(PLOCATION_ID) = 1 THEN
INSERT INTO DEPARTEMEN VALUES (DATA_DEPT_ID, PDEPT_NAME, PMANAGER_ID, PLOCATION_ID);
DBMS_OUTPUT.PUT_LINE('DATA BERHASIL DIMASUKKAN');
ELSE
DBMS_OUTPUT.PUT_LINE('DATA GAGAL DIMASUKKAN');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('DATA GAGAL DIMASUKKAN');
END IF;
END;

--TEST JALAN
EXEC TAMBAH_DATA_DEPARTEMEN('COBA', 100, 1400);