Final Project Oracle & PL/SQL

Tujuan pembelajaran

1. Merangkai lima tabel relational beserta constraint dan dataset bersama.
2. Mengintegrasikan sequence, view, trigger audit, procedure, function, cursor, exception, transaksi, dan package.
3. Membangun pkg_penjualan_demo lengkap dengan validasi dan lock stok FOR UPDATE.
4. Menguji jalur sukses/gagal, rollback, dan batas kompilasi Oracle secara jujur.
5. Mempresentasikan ERD, kontrak API, bukti data, serta keterbatasan proyek latihan.

Pokok bahasan / praktikum

Prasyarat: Selesaikan modul 02/03 dan 11–13. Wajib memakai seq_penjualan_demo, v_total_penjualan_demo, audit_stok_demo, trg_audit_stok_demo yang telah dibuat modul 13; definisinya tersedia penuh di modul 13. Jangan membuat ulang/reset sequence atau menghapus data. pkg_latihan_demo bukan package final.

Video pembelajaran

Video utama — fokus bab ini

Project Oracle SQL/PLSQL bertahap: tabel relasional, DML, JOIN dan logika transaksi pada sistem perbankan. Menggantikan tutorial satu procedure.

Oracle SQL & PL/SQL Full Banking Project 🔥 | Step-by-Step with Real-Time Example
Udayit Tech Academy · Buka video utama di YouTube

Video tambahan sesuai subtopik

1. Real-World Oracle PL/SQL for Modern Developers
   Koenig Solutions

   Fokus: Pendamping penerapan procedure, function, trigger, package dan error handling pada sistem Oracle.

   Referensi praktik integrasi; bukan implementasi persis pkg_penjualan_demo.

Batas cakupan dan panduan menonton: Domain video adalah perbankan, sedangkan tugas kelas tetap penjualan. Gunakan pola integrasi dan pengujian, bukan menyalin schema banking. Kelengkapan seluruh komponen final project dinilai dari implementasi penjualan pada halaman, bukan dari menonton video.

Video merupakan pendamping, bukan pengganti keseluruhan bab. Pemeriksaan relevansi memakai transkrip yang tersedia, deskripsi pembuat dan bab waktu; bukan klaim seluruh durasi telah ditonton. Versi/editor atau bahasa dapat berbeda. Praktikum tetap mengikuti SQL/PLSQL Oracle pada halaman dan pilihan XE atau FreeSQL login. Jika pemutar dibatasi oleh YouTube, gunakan tautan Buka video.

Praktikum Oracle XE atau FreeSQL

Gunakan FreeSQL dengan login atau SQL Developer yang terhubung ke XE. Baca panduan dua jalur praktikum. Jalankan kode sesuai urutan dan keterangan tiap langkah.

Output contoh adalah prediksi berdasarkan dataset, bukan bukti bahwa kode telah dijalankan pada akun Anda. Periksa koneksi, hak membuat objek, autocommit dan panel DBMS_OUTPUT. Slash pada baris sendiri adalah pemisah mode skrip klien tertentu, bukan statement PL/SQL.

Gunakan tabel pertemuan02 dan data pertemuan03 atau setup index sekali. Periksa keadaan data bila latihan sebelumnya pernah di-commit. Jangan menjalankan ulang CREATE TABLE/INSERT atau menghapus data secara buta.

SELECT table_name,constraint_name,constraint_type,status FROM user_constraints
 WHERE table_name IN ('PELANGGAN_DEMO','PRODUK_DEMO','PENJUALAN_DEMO','DETAIL_JUAL_DEMO','PEGAWAI_DEMO')
 ORDER BY table_name,constraint_name;
SELECT object_name,object_type,status FROM user_objects
 WHERE object_name IN ('SEQ_PENJUALAN_DEMO','V_TOTAL_PENJUALAN_DEMO','AUDIT_STOK_DEMO','TRG_AUDIT_STOK_DEMO')
 ORDER BY object_name;
SELECT trigger_name,status FROM user_triggers WHERE trigger_name='TRG_AUDIT_STOK_DEMO';
SELECT sequence_name,min_value,max_value,increment_by,cycle_flag FROM user_sequences
 WHERE sequence_name='SEQ_PENJUALAN_DEMO';
SELECT id_jual,id_pelanggan,status FROM penjualan_demo WHERE id_jual IN (101,102,103) ORDER BY id_jual;

CREATE OR REPLACE PACKAGE pkg_penjualan_demo AS
 FUNCTION hitung_total(p_jumlah IN NUMBER,p_harga IN NUMBER) RETURN NUMBER;
 PROCEDURE proses(p_pelanggan IN NUMBER,p_produk IN NUMBER,p_jumlah IN NUMBER,p_id OUT NUMBER);
 PROCEDURE lapor_stok;
END pkg_penjualan_demo;
/
CREATE OR REPLACE PACKAGE BODY pkg_penjualan_demo AS
 FUNCTION hitung_total(p_jumlah IN NUMBER,p_harga IN NUMBER) RETURN NUMBER IS
 BEGIN
  IF p_jumlah IS NULL OR p_jumlah <= 0 OR p_jumlah <> TRUNC(p_jumlah) OR p_jumlah > 999999 THEN
   RAISE_APPLICATION_ERROR(-20031,'Jumlah harus integer positif maksimal 999999');
  END IF;
  IF p_harga IS NULL OR p_harga <= 0 OR p_harga <> ROUND(p_harga,2) OR p_harga > 9999999999.99 THEN
   RAISE_APPLICATION_ERROR(-20032,'Harga harus positif, maksimal dua desimal dan sesuai kapasitas kolom');
  END IF;
  RETURN p_jumlah*p_harga;
 END hitung_total;
 PROCEDURE proses(p_pelanggan IN NUMBER,p_produk IN NUMBER,p_jumlah IN NUMBER,p_id OUT NUMBER) IS
  v_pelanggan pelanggan_demo.id_pelanggan%TYPE;
  v_harga produk_demo.harga%TYPE;
  v_stok produk_demo.stok%TYPE;
  v_total NUMBER;
 BEGIN
  p_id := NULL;
  IF p_jumlah IS NULL OR p_jumlah <= 0 OR p_jumlah <> TRUNC(p_jumlah) OR p_jumlah > 999999 THEN
   RAISE_APPLICATION_ERROR(-20031,'Jumlah harus integer positif maksimal 999999');
  END IF;
  IF p_pelanggan IS NULL OR p_pelanggan <= 0 OR p_pelanggan <> TRUNC(p_pelanggan) THEN
   RAISE_APPLICATION_ERROR(-20033,'Pelanggan tidak valid');
  END IF;
  IF p_produk IS NULL OR p_produk <= 0 OR p_produk <> TRUNC(p_produk) THEN
   RAISE_APPLICATION_ERROR(-20034,'Produk tidak valid');
  END IF;
  BEGIN
   SELECT id_pelanggan INTO v_pelanggan FROM pelanggan_demo WHERE id_pelanggan=p_pelanggan;
  EXCEPTION WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR(-20033,'Pelanggan tidak ditemukan');
  END;
  BEGIN
   SELECT harga,stok INTO v_harga,v_stok FROM produk_demo WHERE id_produk=p_produk FOR UPDATE;
  EXCEPTION WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR(-20034,'Produk tidak ditemukan');
  END;
  v_total := hitung_total(p_jumlah,v_harga);
  IF v_stok < p_jumlah THEN RAISE_APPLICATION_ERROR(-20035,'Stok tidak cukup'); END IF;
  SELECT seq_penjualan_demo.NEXTVAL INTO p_id FROM dual;
  INSERT INTO penjualan_demo(id_jual,id_pelanggan,tanggal,status)
  VALUES(p_id,v_pelanggan,SYSDATE,'BARU');
  INSERT INTO detail_jual_demo(id_jual,id_produk,jumlah,harga)
  VALUES(p_id,p_produk,p_jumlah,v_harga);
  UPDATE produk_demo SET stok=stok-p_jumlah WHERE id_produk=p_produk;
 END proses;
 PROCEDURE lapor_stok IS
  CURSOR c_stok IS SELECT id_produk,nama_produk,harga,stok FROM produk_demo ORDER BY id_produk;
 BEGIN
  FOR r IN c_stok LOOP
   DBMS_OUTPUT.PUT_LINE(r.id_produk || ': ' || r.nama_produk || ', harga=' || r.harga || ', stok=' || r.stok);
  END LOOP;
 END lapor_stok;
END pkg_penjualan_demo;
/

SELECT name, type, line, position, text
FROM user_errors
WHERE name IN ('PKG_PENJUALAN_DEMO','TRG_AUDIT_STOK_DEMO')
ORDER BY name, type, sequence;
SELECT object_name,object_type,status FROM user_objects WHERE object_name IN ('PKG_PENJUALAN_DEMO','TRG_AUDIT_STOK_DEMO','V_TOTAL_PENJUALAN_DEMO') ORDER BY object_name,object_type;

-- Pemanggil memiliki seluruh transaksi; mulai tanpa pekerjaan tertunda.
DECLARE
 v_id NUMBER;
 v_stok_awal NUMBER;
 v_stok_akhir NUMBER;
 v_audit_awal NUMBER;
 v_audit_akhir NUMBER;
 v_total NUMBER;
 v_jumlah NUMBER;
BEGIN
 SELECT stok INTO v_stok_awal FROM produk_demo WHERE id_produk=11;
 SELECT COUNT(*) INTO v_audit_awal FROM audit_stok_demo WHERE id_produk=11;
 pkg_penjualan_demo.proses(1,11,2,v_id);
 SELECT stok INTO v_stok_akhir FROM produk_demo WHERE id_produk=11;
 SELECT total INTO v_total FROM v_total_penjualan_demo WHERE id_jual=v_id;
 SELECT COUNT(*) INTO v_audit_akhir FROM audit_stok_demo WHERE id_produk=11;
 IF v_stok_akhir <> v_stok_awal-2 OR v_audit_akhir <> v_audit_awal+1 OR v_total <> 60000 THEN
  RAISE_APPLICATION_ERROR(-20991,'Uji sukses tidak memenuhi stok/audit/total seed');
 END IF;
 DBMS_OUTPUT.PUT_LINE('ID=' || v_id || ', total=' || v_total);
 pkg_penjualan_demo.lapor_stok;
 -- Kebijakan uji: batalkan jalur sukses agar seed utuh.
 ROLLBACK;
 SELECT COUNT(*) INTO v_jumlah FROM detail_jual_demo WHERE id_jual=v_id;
 IF v_jumlah <> 0 THEN RAISE_APPLICATION_ERROR(-20992,'Detail uji masih ada setelah rollback'); END IF;
 SELECT COUNT(*) INTO v_jumlah FROM penjualan_demo WHERE id_jual=v_id;
 SELECT stok INTO v_stok_akhir FROM produk_demo WHERE id_produk=11;
 SELECT COUNT(*) INTO v_audit_akhir FROM audit_stok_demo WHERE id_produk=11;
 IF v_jumlah <> 0 OR v_stok_akhir <> v_stok_awal OR v_audit_akhir <> v_audit_awal THEN
  RAISE_APPLICATION_ERROR(-20992,'Rollback sukses tidak memulihkan data/audit');
 END IF;
 DBMS_OUTPUT.PUT_LINE('Rollback sukses: header hilang, stok dan audit kembali');
EXCEPTION WHEN OTHERS THEN
 ROLLBACK;
 RAISE;
END;
/

-- Setiap kasus memakai savepoint milik pemanggil dan memeriksa kode error.
DECLARE
 v_id NUMBER;
 v_total NUMBER;
 v_header_awal NUMBER;
 v_detail_awal NUMBER;
 v_audit_awal NUMBER;
 v_n NUMBER;
 PROCEDURE uji_gagal(p_pelanggan NUMBER,p_produk NUMBER,p_jumlah NUMBER,p_error NUMBER) IS
 BEGIN
  SAVEPOINT kasus_14;
  BEGIN
   pkg_penjualan_demo.proses(p_pelanggan,p_produk,p_jumlah,v_id);
  EXCEPTION WHEN OTHERS THEN
   IF SQLCODE <> p_error THEN RAISE; END IF;
   DBMS_OUTPUT.PUT_LINE('Ditolak sesuai harapan: ' || SQLERRM);
   ROLLBACK TO kasus_14;
   RETURN;
  END;
  ROLLBACK TO kasus_14;
  RAISE_APPLICATION_ERROR(-20993,'Kasus invalid justru diterima');
 END;
 PROCEDURE uji_harga(p_harga NUMBER) IS
 BEGIN
  BEGIN
   v_total := pkg_penjualan_demo.hitung_total(1,p_harga);
  EXCEPTION WHEN OTHERS THEN
   IF SQLCODE <> -20032 THEN RAISE; END IF;
   DBMS_OUTPUT.PUT_LINE('Harga ditolak: ' || SQLERRM);
   RETURN;
  END;
  RAISE_APPLICATION_ERROR(-20994,'Harga invalid justru diterima');
 END;
BEGIN
 SELECT COUNT(*) INTO v_header_awal FROM penjualan_demo;
 SELECT COUNT(*) INTO v_detail_awal FROM detail_jual_demo;
 SELECT COUNT(*) INTO v_audit_awal FROM audit_stok_demo;
 uji_gagal(1,11,NULL,-20031);
 uji_gagal(1,11,0,-20031);
 uji_gagal(1,11,-1,-20031);
 uji_gagal(1,11,1.5,-20031);
 uji_gagal(NULL,11,1,-20033);
 uji_gagal(9999,11,1,-20033);
 uji_gagal(1,9999,1,-20034);
 uji_gagal(1,14,1,-20035);
 uji_harga(NULL);
 uji_harga(0);
 uji_harga(-100);
 uji_harga(100.123);
 ROLLBACK;
 SELECT COUNT(*) INTO v_n FROM penjualan_demo;
 IF v_n <> v_header_awal THEN RAISE_APPLICATION_ERROR(-20995,'Header berubah'); END IF;
 SELECT COUNT(*) INTO v_n FROM detail_jual_demo;
 IF v_n <> v_detail_awal THEN RAISE_APPLICATION_ERROR(-20995,'Detail berubah'); END IF;
 SELECT COUNT(*) INTO v_n FROM audit_stok_demo;
 IF v_n <> v_audit_awal THEN RAISE_APPLICATION_ERROR(-20995,'Audit berubah'); END IF;
 DBMS_OUTPUT.PUT_LINE('Semua kasus gagal sesuai harapan; jumlah data/audit tetap');
EXCEPTION WHEN OTHERS THEN
 ROLLBACK;
 RAISE;
END;
/

SELECT c.id_pelanggan,c.nama,COUNT(p.id_jual) AS jumlah_penjualan,
       NVL(SUM(v.total),0) AS total_penjualan
FROM pelanggan_demo c LEFT JOIN penjualan_demo p ON p.id_pelanggan=c.id_pelanggan
LEFT JOIN v_total_penjualan_demo v ON v.id_jual=p.id_jual
GROUP BY c.id_pelanggan,c.nama ORDER BY c.id_pelanggan;
SELECT p.id_jual,NVL(c.nama,'Anonim') AS pelanggan,d.id_produk,pr.nama_produk,
       pkg_penjualan_demo.hitung_total(d.jumlah,d.harga) AS subtotal
FROM penjualan_demo p LEFT JOIN pelanggan_demo c ON c.id_pelanggan=p.id_pelanggan
JOIN detail_jual_demo d ON d.id_jual=p.id_jual
JOIN produk_demo pr ON pr.id_produk=d.id_produk ORDER BY p.id_jual,d.id_produk;
SELECT LEVEL AS tingkat,id_pegawai,nama,id_manajer
FROM pegawai_demo START WITH id_manajer IS NULL
CONNECT BY PRIOR id_pegawai=id_manajer ORDER SIBLINGS BY id_pegawai;
BEGIN
 pkg_penjualan_demo.lapor_stok;
END;
/

-- OPSIONAL: bukan bagian uji rollback. Menambah penjualan dan audit permanen.
-- Jalankan hanya di schema latihan sendiri setelah menyetujui perubahan seed.
DECLARE
 v_id NUMBER;
BEGIN
 pkg_penjualan_demo.proses(1,11,1,v_id);
 COMMIT;
 DBMS_OUTPUT.PUT_LINE('Penjualan disimpan dengan ID=' || v_id);
EXCEPTION WHEN OTHERS THEN
 ROLLBACK;
 RAISE;
END;
/
SELECT * FROM v_total_penjualan_demo ORDER BY id_jual;
SELECT id_produk,stok FROM produk_demo WHERE id_produk=11;
SELECT id_produk,operasi,stok_lama,stok_baru,waktu FROM audit_stok_demo ORDER BY waktu;

Tugas / latihan

1. Kerjakan pada XE lokal ATAU FreeSQL login schema sendiri; tampilkan ERD dan constraint seluruh lima tabel, tanpa tugas administrasi wajib.
2. Gunakan dependensi modul 13 dan bangun pkg_penjualan_demo dengan signature tepat; sertakan seluruh specification/body, no hidden commit.
3. Jalankan preflight, USER_ERRORS, pemanggil sukses/gagal, laporan, dan assertion rollback; lampirkan hasil aktual serta ID sequence yang mungkin berlubang.
4. Presentasikan fungsi, procedure, cursor, exception, trigger, sequence/view, dan ownership transaksi, termasuk batas sistem bukan production-safe.
5. Kumpulkan script lengkap, matriks expected/actual, bukti seed 101–103 tetap ada, dan penjelasan apakah cell COMMIT opsional dilewati.

Pertanyaan analisis

1. Jika pemanggil menangkap exception proses lalu meneruskan transaksi tanpa rollback, risiko apa yang terjadi pada lock dan DML yang sudah berhasil?
2. Mengapa constraint, validasi PL/SQL, audit, dan assertion pemanggil saling melengkapi tetapi tidak membuat sistem siap produksi?

Pengumpulan

Tugas boleh dikerjakan di Oracle XE lokal atau FreeSQL dengan login; cukup pilih salah satu. Kumpulkan satu laporan melalui LMS/kanal yang ditetapkan dosen. Cantumkan Nama, NPM, pertemuan dan lingkungan yang dipakai. Sertakan kode SQL/PLSQL sebagai teks, output aktual atau pesan error, serta jawaban analisis.

Jika FreeSQL menyediakan tautan berbagi yang dapat diakses dosen, tautan boleh ditambahkan sebagai bukti pendamping, bukan pengganti hasil eksekusi yang terdokumentasi. Jangan membagikan password, token atau koneksi pribadi.

Hasil yang belum dieksekusi diberi label “prediksi”. Screenshot ilustrasi browser bukan bukti praktikum Oracle.

Presentasi project: jelaskan skema, jalankan query/proses, tampilkan kasus sukses dan gagal, lalu terangkan integritas serta transaksi.

Rubrik penilaian

• Ketepatan konsep dan rancangan: 20%
• Kebenaran query/program: 30%
• Pengujian normal, batas dan gagal: 20%
• Analisis serta penjelasan hasil: 20%
• Identitas dan kerapian laporan: 10%

Referensi resmi

• Oracle 19c — PL/SQL Packages
• Oracle 19c — PL/SQL Subprograms
• Oracle 19c — Cursors Overview
• Oracle 19c — PL/SQL Triggers
• Oracle 19c — CREATE SEQUENCE
• Oracle 19c — CREATE VIEW
• Oracle 19c — SELECT, FOR UPDATE
• Oracle 19c — COMMIT
• Oracle 19c — USER_ERRORS

Panduan XE21c digunakan untuk konteks instalasi lokal; dokumentasi SQL/PLSQL untuk konsep bahasa. Versi FreeSQL/editor dapat berbeda, sehingga periksa fitur dan pesan aktual.