Notes

--FUNCTION 1
CREATE OR REPLACE FUNCTION CETAK_NILAI_AKHIR(NIM_MHS CHAR, KD_MK VARCHAR2, NID_DOSEN VARCHAR2)
RETURN NUMBER IS
NILAI_AKHIR NUMBER;
NILAI_TUGAS NUMBER;
NILAI_UTS NUMBER;
NILAI_UAS NUMBER;
BEGIN
SELECT N.TUGAS, N.UTS, N.UAS INTO NILAI_TUGAS, NILAI_UTS, NILAI_UAS
FROM HR.MAHASISWA MHS, HR.NILAI N, HR.MATAKULIAH MK, HR.DOSEN D
WHERE MHS.NIM = N.NIM AND N.NID = D.NID AND N.KODE_MK = MK.KODE_MK
AND N.NIM = NIM_MHS AND N.KODE_MK = KD_MK AND N.NID = NID_DOSEN;
NILAI_AKHIR := (0.3*NILAI_UTS) + (0.3*NILAI_UAS) + (0.4*NILAI_TUGAS);
RETURN NILAI_AKHIR;
END;

--FUNCTION 2
CREATE OR REPLACE FUNCTION CETAK_JUMLAH_SKS(NIM_MHS CHAR)
RETURN NUMBER IS
JUMLAH_SKS NUMBER;
BEGIN
SELECT SUM(MK.SKS) INTO JUMLAH_SKS
FROM HR.MAHASISWA MHS, HR.NILAI N, HR.MATAKULIAH MK
WHERE MHS.NIM = N.NIM AND N.KODE_MK = MK.KODE_MK AND MHS.NIM = NIM_MHS;
RETURN JUMLAH_SKS;
END;

--PROCEDURE
CREATE OR REPLACE PROCEDURE CETAK_MATAKULIAH_MAHASISWA(PNIM_MHS CHAR) IS
NIM_MHS CHAR(11);
FNAME VARCHAR2(30);
JUMLAH_SKS NUMBER(3);
BEGIN
SELECT MHS.NIM, MHS.NAMA_DEPAN||' '||MHS.NAMA_BELAKANG, CETAK_JUMLAH_SKS(MHS.NIM)
INTO NIM_MHS, FNAME, JUMLAH_SKS
FROM HR.MAHASISWA MHS, HR.NILAI N
WHERE MHS.NIM = N.NIM AND MHS.NIM = PNIM_MHS GROUP BY MHS.NIM, MHS.NAMA_DEPAN||' '||MHS.NAMA_BELAKANG;
DBMS_OUTPUT.PUT_LINE('NIM : ' || NIM_MHS);
DBMS_OUTPUT.PUT_LINE('NAMA LENGKAP : ' || FNAME);
DBMS_OUTPUT.PUT_LINE('JUMLAH SKS : ' || JUMLAH_SKS);
DBMS_OUTPUT.PUT_LINE('=====================================');

FOR MHS_MATAKULIAH IN
(SELECT MK.KODE_MK, MK.NAMA_MK, CETAK_NILAI_AKHIR(MHS.NIM, MK.KODE_MK, D.NID) AS NILAI_AKHIR
FROM HR.MAHASISWA MHS, HR.NILAI N, HR.MATAKULIAH MK, HR.DOSEN D
WHERE MHS.NIM = N.NIM AND N.NID = D.NID AND N.KODE_MK = MK.KODE_MK AND MHS.NIM = PNIM_MHS) LOOP
DBMS_OUTPUT.PUT_LINE(MHS_MATAKULIAH.KODE_MK ||' '|| MHS_MATAKULIAH.NAMA_MK ||' '|| MHS_MATAKULIAH.NILAI_AKHIR);
END LOOP;
END;

--TEST JALAN
EXEC CETAK_MATAKULIAH_MAHASISWA('04410150023');