oracle 2 stored procedure

PL/SQLM. Ammar Shadiq Ilmu Komputer Universitas Pendidikan Indonesia, Bandung 5 Mei 2008

1. PENDAHULUAN Pada bagian ini anda akan berkenalan secara langsung dengan contoh PL/SQL. Hal ini di lakukan agar pembaca mendapatkan gambaran mengenai materi yang akan di bahas. Adapun table yang digunakan pada bagian ini akan terus digunakan di sepanjang modul ini. 1.1. TABEL YANG DIGUNAKAN: Deret1 : (nilai NUMBER(32))CREATE TABLE deret1 (nilai number(32));

Deret2 : (nilai number(32), KOMENTAR varchar2(10))CREATE TABLE deret2 (nilai number(32), komentar varchar2(10));

Dengan Dummy Data : (1), (2), (3), (4),(5)INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO deret2 deret2 deret2 deret2 deret2 (nilai) (nilai) (nilai) (nilai) (nilai) VALUES VALUES VALUES VALUES VALUES (1); (2); (3); (4); (5);

Account : (nama varchar2 (50), saldo number(32),, cabang varchar2 (50))CREATE TABLE account (nama varchar2(50),saldo number(32),cabang varchar2(50));

Dengan Dummy Data : (shadiq, Rp. 100.000, Bogor), (Alice, Rp. 1.000.000, Bogor), (Bob, Rp. 700.000, Bandung), (Wally, Rp. 500.000, Bandung)INSERT INSERT INSERT INSERT INTO INTO INTO INTO account account account account VALUES VALUES VALUES VALUES ('shadiq',100000,'Bogor'); ('Alice',1000000,'Bogor'); ('Bob',700000,'Bandung'); ('Wally',500000,'Bandung');

2. KELEBIHAN PL/SQL PL/SQL adalah bahasa portable, pemrosesan transaksi berkinerja tinggi yang menawarkan keuntungan-keuntungan berikut : Integrasi ketat dengan SQL Performa yang lebih baik Produktifitas yang lebih tinggi Portabilitas penuh Keamanan yang ketat Akses ke paket-paket yang telah di definiskan sebelumnya Mendukung pemrograman berorientasi objek PL/SQL|1 Oracle 10g XE : Laboratorium Basis Data : Ilmu Komputer & Pendidikan Ilmu Komputer UPI

Mendukung pengembangan halaman dan aplikasi Web.

2.1. INTEGRASI KETAT DENGAN SQL SQL telah menjadi bahasa standar database karena fleksibel, powerfull dan mudah di pelajari. Beberapa perintah yang seperti-bahasa-inggris seperti SELECT, INSERT, UPDATE dan DELETE memudahkan pemanipulasian data yang tersimpan dalam database relasional. PL/SQL mengizinkan anda untuk menggunakan seluruh perintah SQL dalam memanipulasi data, mengatur cursor dan perintah-perintah transaksi, seperti juga seluruh fungsi SQL, operator-operator dan pseudo columns. Pengintegrasian ketat dengan SQL ini mengizinkan anda untuk memanipulasi data Oracle secara aman dan flexible. Sebagai tambahan PL/SQL sepenuhnya mendukung tipe data SQL, mengurangi kebutuhan untuk meng-convert data yang di kirimkan antara aplikasi anda dan database. 2.2. PEFORMA YANG LEBIH BAIK Tanpa PL/SQL, oracle harus memproses perintah SQL satu-per-satu. Program yang mengirimkan banyak perintah SQL membutuhkan komunikasi yang banyak ke database, yang berdampak pada overhead performa dan jaringan yang signifikan.

Gambar 1 : Contoh Jaringan Kompleks

Dengan PL/SQL, seluruh block statement dapat di kirimkan kepada Oracle dalam sebuah paket pengiriman. Hal ini dapat secara drastis mengurangi kapadatan lalulintas data dalam jaringan antara aplikasi dan database. Anda dapat menggunakan PL/SQL block dan subprogram untuk mengelompokkan perintah-perintah SQL kedalam satu paket sebelum mengirimkannya ke database untuk di eksekusi. PL/SQL juga memiliki fitur untuk mempercepat perintah SQL didalam LOOP. Prosedur PL/SQL dikompilasi dan disimpan dalam bentuk executable dalam server database, sekali pemanggilan dapat memulai pekerjaan yang besar.

PL/SQL|2 Oracle 10g XE : Laboratorium Basis Data : Ilmu Komputer & Pendidikan Ilmu Komputer UPI

Gambar 2 : Perbandingan Pengeksekusian perintah SQL

2.3. PRODUKTIFITAS YANG LEBIH TINGGI Bahasa pemrograman seperti Perl, Java, C++ dll dapat membaca, mengubah dan menulis data pada file. PL/SQL dapat meng-query, mengubah dan menulis data pada database. PL/SQL menghemat waktu pada perancangan dan perbaikan dengan menawarkan fitur-fitur rekayasa perangkat lunak yang lengkap, seperti penanganan exception, enkapsulasi, penyembunyian data (pengkapsulan) dan tipe data berorientasi objek. PL/SQL sama untuk seluruh platform. Setelah anda mempelajari PL/SQL dengan salah satu perangkat Oracle, anda dapat mentransfer pengetahuan anda pada perangkat oracle yang lain. 2.4. PORTABILITAS PENUH Aplikasi-aplikasi yang di tulis dalam PL/SQL dapat di jalankan pada platform dan system operasi manapun dimana oracle berjalan. Dengan PL/SQL, anda dapat menuliskan pustaka program yang dapat di bawa kemanapun dan dapat di guna ulang pada lingkungan yang berbeda. 2.5. KEAMANAN YANG KETAT Stored procedure PL/SQL mengirimkan kode aplikasi dari client ke server, dimana anda dapat melindunginya dari perusakan, menyembunyikan rincian internal, dan membatasi pengaksesan oleh user-user tertentu. Sebagai contoh, adan dapat mengizinkan suatu user untuk mengakses prosedur yang mengupdate table, tetapi tidak mengizinkan user tersebut mengakses table secara langsung, atau kepada text dari klausa UPDATE. TRIGGER yang di tuliskan dalam PL/SQL dapat mengatue atau mencatat perubahan yang terjadi pada data, memastikan bahwa seluruh perubahan mengikuti aturan bisnis yang di tetapkan. 2.6. AKSES KE PAKET-PEKET YANG TELAH DI TENTUKAN SEBELUMNYA Oracle menyediakan paket-paket yang spesifik terhadap produk tertentu yang mendefinisikan API yang dapat anda panggil dari PL/SQL untuk melakukan banyak tugas berguna. Paket-paket ini termasuk di dalamnya DBMS_ALERT untuk menggunakan database trigger, DBMS_FILE untuk PL/SQL|3 Oracle 10g XE : Laboratorium Basis Data : Ilmu Komputer & Pendidikan Ilmu Komputer UPI

membaca dan menulis file Text system operasi (OS), DBMS_HTTP untuk membuat protocol hypertext transfer (HTTP), DBMS_OUTPUT untuk menampilkan output dari block dan sum program PL/SQL, dan DBMS_PIPE untuk berkomunikasi melalui pipa data tertentu. 2.7. MEDUKUNG PEMROGRAMAN BERORIENTASI OBJEK Tipe-tipe objek adalah kakas pemodelan berorientasi-objek yang ideal, yang dapat digunakan untuk menguraingi biaya dan waktu yang dibutuhkan untuk membangun aplikasi kompleks. Selain mengizinkan anda untuk membuat komponen perangkat lunak yang modular, maintainable dan reusable, tipeobjek mengizinkan kelompok programmer-programmer yang berbeda untuk mengembangkan komponen perangkat lunak secara bersamaan. Dengan mengkapsulasi operasi dengan data, tipe objek , mengizinkan anda memindahkan perubahan code dari script SQL dan block PL/SQL kedalam metode. Sebagai tambahan tipe objek menyembunyikan rincian implementasi, sehingga anda dapat mengubah rincian implementasi tanpa mempengaruhi program client. Sebagai tambahan, tipe objek mengizinkan pemodelan data yang realistik. Relationship dan entityentity real-world yang rumit di petakan secara langsung ke tipe objek. Pemataan secara langsung ini membantu program anda untuk secara lebih baik mencerminkan kondisi dimana program anda coba simulasikan. 2.8. MENDUKUNG PENGEMBANGAN HALAMAN DAN APLIKASI WEB Anda dapat menggunakan PL/SQL untuk mengembangkan aplikasi Web dan Server Pages (PSPs).

3. FITUR-FITUR UTAMA PL/SQL 3.1. STRUKTUR BLOCK PL/SQL Block PL/SQL memiliki tiga bagian dasar : Bagian deklaratif (DECLARE) Bagian eksekusi (BEGIN, END) Bagian penanganan eksepsi (EXCEPTION) bagian yang menangani kondisi error

Gambar 3 : PL/SQL Block Structure


Hanya bagian eksekusi yang di syaratkan ada pada pembuatan PL/SQL ini, bagian lain bersifat opsional. Bagian deklaratif yang opsinal di detuliskan di awal, dimana tipe-tipe data terdefinisi, variable-variabel dan objek-objek sejenis. Objek-objek ini di manipulasi pada bagian eksekusi. Eksepsi (ERROR) yang muncul dalam eksekusi dapat di tangani dalam bagian penanganan eksepsi. Anda dapat membuat block bersarang dalam bagian deklaratif dan penanganan eksepsi, tetapi tidak di bagian deklaratif. Anda dapat mendefinisikan subprogram dalam bagian deklaratif dari block manapun. Anda dapat memanggil sub program hanya dari dalam block dimana sub program tersebut di definisikan. 3.2. BADAN PL/SQL BLOCKDECLARE /* deklarasi variabel, type dan subprogram lokal */ BEGIN /* prosedural dan SQL masuk disini */ /* BAGIAN INI YANG WAJIB */ EXCEPTION /* penanganan eksepsi (ERROR) */ END;

3.3. BAGIAN DEKLARASI VARIABEL : Contoh :DECLARE v_counter INTEGER; v_nim CHAR(5); v_nama VARCHAR(2); v_nilai NUMBER; v_nim_2 mahasiswa.nim%TYPE

/* tipe variabel mengikuti tipe field mahasiswa.nim */

mengapa menggunakan huruf depan v_? Supaya tidak bentrok dengan nama field, Contoh :INSERT INTO mahasiswa (nim, nama) VALUES (v_nim, v_nama);

Nilai variabel juga dapat diinisiasikan saat pembuatannya, Contoh :v_nilai_minimum INTEGER := 30;

Anda juga dapat membuat variable yang bersifat konstan (Tidak berubah) dengan klausa CONSTANT, dengan syarat nilai variable harus diinisiasasikan saat pembuatannya, Contoh :v_phi CONSTANT NUMBER(3,2) := 3.14;

4. ARSITEKTUR PL/SQL System Kompilasi dan Run-Time PL/SQL adalah engine yang mengcompile dan mengeksekusi block dan subprogram PL/SQL. Engine ini dapat di install di server Oracle atau dalam kakas pengembangan aplikasi seperti Oracle Form. Dibawah ini adalah ilustrasi bagaimana PL/SQL Engine memproses Anonymous Block. PL/SQL|5 Oracle 10g XE : Laboratorium Basis Data : Ilmu Komputer & Pendidikan Ilmu Komputer UPI

Gambar 4 : Arsitektur PL./SQL Engine

Secara umum PL/SQL memiliki 3 komponen utama, yaitu: Anonymous PL/SQL Block, Stored Subprogram(Stored Procedure, Stored Function) dan Trigger. 4.1. ANONYMOUS BLOCK Block PL/SQL Anonymous (tidak bernama) dapat disampaikan kepada kakas interaktif seperti SQL*Plus dan Enterprise Manager, menyimpannya dalam Oracle Precompiler atau Oracle Call Interface(OCI), atau mendefinisikannya dalam aplikasi dengan bahasa lain (Contoh : Java, C#, C++) . Berbeda dengan Subprogram(Stored Procedure, Stoded Function) Anonymous PL/SQL Block tidak dibuat (CREATE) dan tidak disimpan pada Database. Biasanya Anonymous PL/SQL Block ini di jalankan dari dalam aplikasi. Pada run-time Aplikasi akan mengirimkan anonymous PL/SQL block ini kepada database Oracle, dimana Anonymous block tersebut di compile dan di eksekusi.

Gambar 5 : Pengeksekusian Anonymous PL/SQL Block

Badan Anonymous Block (Secara Umum)DECLARE /* deklarasi variabel, type dan subprogram lokal */ BEGIN /* prosedural dan SQL masuk disini */ /* BAGIAN INI YANG WAJIB */ EXCEPTION /* penanganan eksepsi (ERROR) */ END;


Contoh Anonymous Block 1 Berikut adalah salah satu contoh Anonymous Block yang mengisi field nilai pada tabel deret1 dengan nilai deret 0, 2, 4, ... , 100./* TANPA DEKLARASI NAMA BLOCK */ DECLARE v_counter INTEGER; BEGIN v_counter := 0; loop INSERT INTO deret1(nilai) VALUES (v_counter); v_counter := v_counter + 2; IF (v_counter > 100) THEN EXIT; END IF; END LOOP; END; /

4.2 SUBPROGRAM Sub program adalah PL/SQL block yang dapat di panggil dengan sekumpulan parameter. PL/SQL memiliki dua jenis subprogram, yaitu : Procedure dan Function. Secara umum, Prosedur digunakan untuk melaksanakan aksi dan Fungsi digunakan untuk mengkomputasi suatu nilai.

Gambar 6 : Pengeksekusian Stored Subprogram

Untuk melihat Seluruh Subprogram pada Database Oracle, anda dapat menuliskan perintah berikut : Melihat semua SubprogramSELECT * FROM USER_PROCEDURES;

Melihat Subprogram tertentuSELECT * FROM USER_PROCUDERES WHERE OBJECT_NAME = ;

Melihat seluruh Subprogram yang dimiliki DBASELECT * FROM DBA_PROCEDURES;

Hal ini tidak di sarankan karena dari perintahSELECT COUNT (*) FROM DBA_PROCEDURES;

akan menghasilkan row sebanyak 12.906 PL/SQL|7 Oracle 10g XE : Laboratorium Basis Data : Ilmu Komputer & Pendidikan Ilmu Komputer UPI

4.2.1. Procedure Membuat Stored Procedure

Gambar 6 : Create Procedure

Badan Procedure (Secara Umum)CREATE OR REPLACE PROCEDURE AS /* Deklarasi variables, types, dan local subprograms. */ BEGIN /* procedural and SQL masuk disini*/ /* blok ini yang wajib*/ EXCEPTION /* penanganan eksepsi (ERROR) */ END ;

Contoh Stored Procedure 1 : Berikut adalah salah satu contoh Stored Procedure yang mengisi field nilai pada tabel deret1 dengan nilai deret 0, 2, 4, ... , 100.CREATE OR REPLACE PROCEDURE prc_isi_deret1 AS v_counter INTEGER; BEGIN v_counter := 0; loop --echo, printf, cout, system.out.oprintln DBMS_OUTPUT.PUT_LINE('Nilai Counter Saat ini = '||v_counter); INSERT INTO deret1(nilai) VALUES (v_counter); v_counter := v_counter + 2; IF (v_counter > 100) THEN EXIT; END IF; END LOOP; END prc_isi_deret1;


/

Eksekusi Stored Procedure diatas :EXEC prc_isi_deret1Catatan : untuk memperlihatkan output dari DBMS_OUTPUT.PUT_LINE anda harus terlebih dahulu mengeset variable system SERVEROUTPUT menjadi ON dengan menuliskan perintah :SET SERVEROUTPUT ON

Cek isi tabel deret1 yang sudah di modifikasi :SELECT * FROM deret1;

Menghapus Stored ProcedureDROP PROCEDURE ;

Contoh :DROP PROCEDURE prc_isi_deret1;

4.2.2. Function Membuat Stored Function

Badan Function (Secara Umum)CREATE OR REPLACE FUNCTION RETURN AS /* Deklarasi variables, types, dan local subprograms. */ BEGIN /* procedural and SQL masuk disini*/ /* blok ini yang wajib*/


EXCEPTION /* penanganan eksepsi (ERROR) */ RETURN(); /* kalusa return ini wajib di deklarasikan */ END;

Contoh Stored Function 1: Berikut adalah salah satu contoh Stored Function yang mencari nilai maksimum pada tabel deret1.CREATE OR REPLACE FUNCTION func_max_deret1 RETURN NUMBER AS v_maksimum INTEGER; BEGIN SELECT MAX(nilai) INTO v_maksimum FROM deret1; RETURN (v_maksimum); END func_max_deret1; /

Eksekusi Stored Function diatas dengan cara:SELECT func_max_deret1 FROM dual;Catatan : table dual pada oracle adalah table semu yang dapat digunakan untuk berbagai operasi yang membutuhkan table acuan semu seperti contoh diatas.

Fungsi dapat dipanggil melalui sintax SQL, contoh :SELECT nilai, func_max_deret1 from deret1;

Menghapus Stored FunctionDROP FUNCTION ;

Contoh :DROP FUNCTION func_max_deret1;

CONTROL FLOW : LOOP LOOP 1 LOOP EXIT WHEN kondisi_keluar END LOOP;

Contoh :... counter := 0; loop insert into deret1(NILAI) values (counter); counter := counter + 2;


EXIT WHEN counter > 100; end loop; ...

LOOP 2 WHILE LOOP END LOOP;

Contoh :... counter := 0; WHILE counter 100) then exit; end if; ...

IF - THEN - ELSEIF THEN ELSE END IF;

Contoh :... IF posisi = 80 THEN komentar := bagus ELSE komentar := biasa END IF; ...

IF THEN ELSEIF ELSEIF THEN ... ELSEIF THEN ... ... ELSEIF THEN ... ... ... ELSE ... ... ... ... END IF;

Contoh :... IF number = 0 THEN result := enol; ELSIF number > 0 THEN result := bilangan positif; ELSIF number < 0 THEN result := bilangan negatif; ELSE -- hmm, satu-satunya kemungkinan lain adalah angka tersebut NULL result := NULL; END IF; ...


4.2.3. variabel parameter Subprogram Ada tiga jenis parameter :IN OUT IN OUT

: variabel input (default) : variabel output : variabel input dan output

Untuk lebih jelasnya, perhatikan contoh-contoh dibawah ini : Contoh Parameter 1 (Stored Procedure):CREATE OR REPLACE PROCEDURE prc_contoh_parameter_1 (v_sampai_dengan IN integer) AS v_counter integer; BEGIN v_counter := 0; loop insert into deret1(nilai) values (v_counter); v_counter := v_counter + 2; if (v_counter> v_sampai_dengan) then exit; end if; end loop; END prc_contoh_parameter_1; /

Contoh Parameter 2 (Stored Function):CREATE OR REPLACE PROCEDURE prc_contoh_parameter_2 (v_sampai_dengan IN integer, v_keluaran_sd1 OUT integer, v_keluaran_sd2 OUT integer) AS v_counter integer; BEGIN v_counter := 0; loop insert into deret1(nilai) values (v_counter); v_counter := v_counter + 2; if (v_counter > v_sampai_dengan) then exit; end if; end loop; v_keluaran_sd := v_counter; END prc_contoh_parameter_2; /

Dengan Prosedur seperti ini, pemanggilannya dilakukan melalui block PL/SQL yang lain, contoh melalui Anon Block :declare v_keluaran integer; begin prc_contoh_parameter_2(60, v_keluaran); dbms_output.put_line('Hasil Output = ' || v_keluaran); end;


/Pertanyaan : Kenapa hasil yang dikeluarkan 62 ? Jawaban : perhatikan bahwa fungsi diatas melakukan proses tiap iterasi dengan membandingkan variabel counter. Saat iterasi sebelum terakhir (misal 60) fungsi membandingkan jika variabel counter > 60, karena nilai variabel counter belum melebihi 60 (tetapi sama dengan) maka iterasi dilakukan sekali lagi dan nilai variabel counter bertambah menjadi 62. Saat fungsi melakukan perbandingan kembali (counter > 60 [62>60]), maka kondisi tidak terpenuhi, tetapi nilai variabel counter adalah 62. Oleh karena itu hasil yang dikeluarkan adalah 62 bukan 60.

Contoh Parameter 3 :CREATE OR REPLACE PROCEDURE contoh_parameter_3 (v_sampai_dengan IN OUT INTEGER) AS v_counter integer; BEGIN v_counter integer; loop insert into deret1(nilai) values (v_counter); v_counter := v_counter + 2; if (v_counter>v_sampai_dengan) then exit; end if; end loop; v_sampai_dengan := v_counter; END contoh_parameter_3;

Dapat di perhatikan pada fungsi diatas, variabel input dan outputnya sama yaitu variabel sampai_dengan. Dengan Prosedur seperti ini, pemanggilannya dilakukan melalui block PL/SQL yang lain, contoh melalui Anon Block : dapat di perhatikan bahwa variable input dan outputnya sama.declare v_masukan_keluaran integer := 60; begin prc_contoh_parameter_3(v_masukan_keluaran); dbms_output.put_line('Hasil Output = ' || v_masukan_keluaran); end; /


BATASAN-BATASAN Variabel input : tidak dapat diubah Variabel output : tidak dapat di jadikan nilai referensi pada variabel lain. Contoh :CREATE FUNCTION contoh_parameter_salah (p_in IN integer, p_out OUT integer, p_inout INOUT integer) AS local integer; BEGIN local := p_in; local := p_inout; p_out := p_in; p_out := local p_out := 5; p_inout := p_in; p_inout := local; p_inout := 5; p_in := 5; p_in := local; p_in := p_inout; local := p_out; p_inout := p_out; p_in := p_out; p_out := p_in; END; ------ERROR, ERROR, ERROR, ERROR, ERROR, PARAH INPUT tidak boleh diisi INPUT tidak boleh diisi INPUT tidak boleh diisi OUTPUT tidak boleh digunakan OUTPUT tidak boleh digunakan

3.


ILUSTRASI VARIABEL PARAMETER FUNGSI 1. Fungsi yang menggunakan 1 parameter masukan IN dan parameter keluaran VOID (tidak ada parameter keluarnya)

2. Fungsi yang menggunakan 1 parameter masukan IN dan 2 parameter keluaran OUT

3. Fungsi yang menggunakan 2 parameter masukan IN, 1 parameter keluaran OUT dan 2 parameter masuk keluar INOUT


CURSOR MENDEKLARASIKAN CURSORDECLARE curs1 refcursor; curs2 CURSOR FOR SELECT nilai FROM deret1; curs3 CURSOR (key integer) IS SELECT * FROM deret1 WHERE nilai = key;

ketiga variabel ini bertipe data refcursor, tetapi curs1 dapat digunakan untuk semua query, sedangkan yang kedua sudah memiliki query, dan yang terakhir memiliki query yang ber parameter (key akan digantikan dengan nilai bertipe integer saat cursor di open). Catatan :curs2 CURSOR FOR SELECT * FROM deret1;

dengancurs2 CURSOR IS SELECT * FROM deret1;

memiliki fungsi yang sama

MEMBUKA CURSOR Untuk curs2 diatas, perintah untuk membukanya :OPEN curs2;

Untuk curs3 diatas, perintah untuk membukanya :OPEN curs3(23);

Untuk curs1 diatas, perintah untuk membukanya :OPEN curs1 FOR SELECT * FROM mahasiswa;

MENGGUNAKAN CURSOR Setelah cursor dibuka, nilainya dapat di manipulasi dengan perintah yang di deskripsikan disini : Untuk curs2, curs3 dan curs4 diatas, perintah untuk menggunakannya adalah :FETCH curs2 INTO v_nilai; FECTH curs3 INTO v_nilai; FETCH curs1 INTO v_nilai;

Atau jika curs1 memiliki lebih dari satu kolom, anda dapat menggunakannya dengan cara :FETCH curs1 INTO v_nilai1, v_nilai2, v_nilai3;

Setelah digunakan sebaiknya cursor ditutup untuk menghemat memory yang digunakanCLOSE ;


Ex :CLOSE curs1;

Contoh 8 :CREATE OR REPLACE FUNCTION isi_komentar1() RETURNS VOID AS $$ DECLARE v_nilai deret2.nilai%type; v_komentar deret2.komentar%type; c_nilai CURSOR IS SELECT nilai FROM deret2; BEGIN OPEN c_nilai; LOOP FETCH c_nilai INTO v_nilai; EXIT WHEN NOT FOUND; IF v_nilai>3 THEN v_komentar := 'BAGUS'; ELSE v_komentar := 'BIASA'; END IF; UPDATE deret2 SET komentar = v_komentar WHERE nilai = v_nilai; END LOOP; CLOSE c_nilai; END; $$ LANGUAGE 'plpgsql';

Contoh 9 : (Dari Presentasi Bpk Yudi Wibisono) Diketahui: Tabel JAWABAN (NIM char, NO_SOAL int, JAWAB char) Tabel KUNCI (NO_SOAL int, KUNCI char) Isilah tabel SKOR (NIM char,SKOR int) dengan aturan: jawaban benar 4 point, jawaban salah -1. Tabel Jawaban :CREATE TABLE jawaban (nim char(6), no_soal integer, jawaban char(1));

Dummy data :INSERT INTO jawaban VALUES ('123456',1,'A'); INSERT INTO jawaban VALUES('123456',2,'A'); INSERT INTO jawaban VALUES ('123456',3,'A'); INSERT INTO jawaban VALUES ('123456',4,'A'); ('123456',5,'A'); INSERT INTO jawaban VALUES ('123456',6,'A'),('123456',7,'A'),('123456',8,'A'),('123456',9,'A'),('123456',10,'A'); -- MAHASISWA 2, NIM : 234567 ('234567',1,'A'),('234567',2,'B'),('234567',3,'C'),('234567',4,'D'), ('234567',5,'B'), ('234567',6,'D'),('234567',7,'B'),('234567',8,'C'),('234567',9,'D'),('234567',10,'E'), -- MAHASISWA 3, NIM : 345678 ('345678',1,'A'), ('345678',2,'A'), ('345678',3,'C'), ('345678',4,'E'),('345678',5,'B'), ('345678',6,'D'),('345678',7,'E'),('345678',8,'A'),('345678',9,'D'),('345678',10,'A'), -- MAHASISWA 4, NIM 999999 ('999999',1,'B'), ('999999',2,'B'), ('999999',3,'D'), ('999999',4,'A'),('999999',5,'C'),


('999999',6,'A'),('999999',7,'C'),('999999',8,'B'),('999999',9,'A'),('999999',10,'A');

Tabel kunci :CREATE TABLE kunci (no_soal integer, kunci char(1));

Dummy Data :INSERT INTO kunci VALUES(1,'A');Insert (2,'A'), (3,'C'), (4,'E'), (5,'B'), (6,'D'), (7,'E'), (8,'A'), (9,'D'), (10,'C');

Dengan Summary skor dari Dummy Data jawaban mahasiswa adalah seperti berikut : Mahasiswa 1, NIM 123456 : benar (3), salah (7) = (4*3) (1*7) = 5 Mahasiswa 2, NIM 234567 : benar(5), salah (5) = (4*5) (1*5) =15 Mahasiswa 3, NIM 345678 : benar(9), salah(1) = (4*9) (1*1) = 35 Mahasiswa 4, NIM 999999 : benar(0), salah(10) = (4*0) (1*10) = -10 Tabel Skor : CREATE TABLE skor (nim char(6), skor integer); Prosedur :CREATE OR REPLACE FUNCTION isi_skor() RETURNS VOID AS $$ DECLARE v_nim skor.nim%TYPE; v_skor skor.skor%TYPE; v_no_soal jawaban.no_soal%TYPE; v_jawaban jawaban.jawaban%TYPE; v_kunci kunci.kunci%TYPE; c_mahasiswa CURSOR IS SELECT DISTINCT nim FROM jawaban; CURSOR c_jawaban; BEGIN OPEN c_mahasiswa; LOOP FETCH c_mahasiswa INTO v_nim; EXIT WHEN NOT FOUND; v_skor := 0; c_jawaban is select no_soal, jawaban from jawaban where nim = v_nim; LOOP FETCH c_jawaban INTO v_no_soal,v_jawaban; EXIT WHEN NOT FOUND; V_kunci := select kunci where no_soal = v_no_soal; IF v_jawaban v_kunci THEN v_skor := v_skor-1; ELSE v_skor := v_skor + 4; END IF; END LOOP; INSERT INTO skor VALUES (v_nim, v_skor); CLOSE c_jawaban; END LOOP; CLOSE c_mahasiswa; END; $$ LANGUAGE 'plpgsql';


Contoh 10 : (Dari Presentasi Bpk Yudi Wibisono) Tabel input: TRANS_HARIAN (TGL date, JUM_TRANS int,ID_CUST int) Buatlah SP untuk mengisi tabel output: TRANS_BULANAN (THN int,BULAN int, JUM_TRANS int, ID_CUST int) TRANS_TAHUNAN (TAHUN int, JUM_TRANS int, ID_CUST int) Tabel Trans_Harian:CREATE TABLE trans_harian (tgl timestamp, jum_trans integer, id_cust integer);

Dummy data :INSERT INTO trans_harian VALUES (to_timestamp('05 01 2000','DD MM YYYY'),12,1),(to_timestamp('06 01 2000','DD MM YYYY'),3,1),(to_timestamp('05 02 2000','DD MM YYYY'),4,2),(to_timestamp('07 01 2000','DD MM YYYY'),9,3),(to_timestamp('12 04 2000','DD MM YYYY'),1,1),(to_timestamp('05 01 2000','DD MM YYYY'),15,2),(to_timestamp('05 09 2000','DD MM YYYY'),21,1),(to_timestamp('05 10 2000','DD MM YYYY'),80,3),(to_timestamp('05 01 2001','DD MM YYYY'),8,1),(to_timestamp('05 02 2001','DD MM YYYY'),6,2),(to_timestamp('05 03 2001','DD MM YYYY'),9,3),(to_timestamp('05 04 2001','DD MM YYYY'),12,3),(to_timestamp('05 05 2001','DD MM YYYY'),13,3),(to_timestamp('05 06 2001','DD MM YYYY'),80,1),(to_timestamp('05 07 2001','DD MM YYYY'),19,2),(to_timestamp('05 08 2001','DD MM YYYY'),67,1),(to_timestamp('05 09 2001','DD MM YYYY'),999,2),(to_timestamp('05 10 2001','DD MM YYYY'),87,1),(to_timestamp('05 11 2001','DD MM YYYY'),90,2),(to_timestamp('05 12 2001','DD MM YYYY'),56,2),(to_timestamp('01 1 2002','DD MM YYYY'),76,1),(to_timestamp('15 1 2002','DD MM YYYY'),6,2),(to_timestamp('20 1 2002','DD MM YYYY'),96,3),(to_timestamp('05 2 2002','DD MM YYYY'),45,1),(to_timestamp('17 2 2002','DD MM YYYY'),67,2),(to_timestamp('13 2 2002','DD MM YYYY'),23,3),(to_timestamp('25 2 2002','DD MM YYYY'),84,1),(to_timestamp('21 2 2002','DD MM YYYY'),42,2),(to_timestamp('18 3 2002','DD MM YYYY'),45,3),(to_timestamp('12 3 2002','DD MM YYYY'),12,1),(to_timestamp('16 3 2002','DD MM YYYY'),67,2),(to_timestamp('19 3 2002','DD MM YYYY'),86,3),(to_timestamp('22 3 2002','DD MM YYYY'),56,1),(to_timestamp('23 3 2002','DD MM YYYY'),43,2),(to_timestamp('24 3 2002','DD MM YYYY'),90,3),(to_timestamp('01 4 2002','DD MM YYYY'),75,1),(to_timestamp('02 4 2002','DD MM YYYY'),45,2),(to_timestamp('11 4 2002','DD MM YYYY'),65,3),(to_timestamp('13 4 2002','DD MM YYYY'),57,1),(to_timestamp('14 5 2002','DD MM YYYY'),43,2),(to_timestamp('17 5 2002','DD MM YYYY'),93,3),(to_timestamp('28 5 2002','DD MM YYYY'),34,1),(to_timestamp('26 6 2002','DD MM YYYY'),67,2),(to_timestamp('21 7 2002','DD MM YYYY'),32,3),(to_timestamp('22 7 2002','DD MM YYYY'),43,1),(to_timestamp('23 7 2002','DD MM YYYY'),56,2),(to_timestamp('24 7 2002','DD MM YYYY'),97,3),(to_timestamp('01 8 2002','DD MM YYYY'),91,1),(to_timestamp('01 9 2002','DD MM YYYY'),23,2),(to_timestamp('05 12 2002','DD MM YYYY'),576,3);

Tabel Trans_bulananCREATE TABLE trans_bulanan(thn integer, bulan integer, jum_trans integer, id_cust integer);

Tabel Trans_tahunanCREATE TABLE trans_tahunan(thn integer, jum_trans integer, id_cust integer);

Prosedur-Prosedur : Cust_per_tahunCREATE OR REPLACE FUNCTION cust_per_tahun (v_tahun INTEGER) RETURNS REFCURSOR AS $$ DECLARE v_curs_thn REFCURSOR; BEGIN OPEN v_curs_thn FOR SELECT DISTINCT id_cust FROM trans_harian WHERE EXTRACT (year FROM tgl) = v_tahun; RETURN (v_curs_thn); END; $$ LANGUAGE 'plpgsql';

Trans_thn_custCREATE OR REPLACE FUNCTION trans_thn_cust (v_tahun INTEGER, v_id_cust INTEGER) RETURNS INTEGER AS $$


DECLARE jmlh_trans INTEGER; BEGIN SELECT INTO jmlh_trans SUM(jum_trans) FROM trans_harian WHERE id_cust = v_id_cust AND EXTRACT (YEAR FROM tgl) = v_tahun; RETURN (jmlh_trans); END; $$ LANGUAGE 'plpgsql';

Trans_bln_custCREATE OR REPLACE FUNCTION trans_bln_cust (v_tahun INTEGER, v_bulan INTEGER, v_id_cust INTEGER) RETURNS INTEGER AS $$ DECLARE jmlh_trans INTEGER; BEGIN SELECT INTO jmlh_trans SUM(jum_trans) FROM trans_harian WHERE id_cust = v_id_cust AND EXTRACT (MONTH FROM tgl) = v_bulan AND EXTRACT (YEAR FROM tgl) = v_tahun; RETURN (jmlh_trans); END; $$ LANGUAGE 'plpgsql';

Isi_summary_trans jalankan fungsi ini untuk mengisi tabel trans bulanan dan tahunan.CREATE OR REPLACE FUNCTION isi_summary_trans() RETURNS VOID AS $$ DECLARE v_tahun INTEGER; v_bulan INTEGER; v_id_cust INTEGER; v_trans_cust_thn INTEGER; v_trans_cust_bln INTEGER; c_tahun CURSOR IS SELECT DISTINCT EXTRACT (YEAR FROM tgl) FROM trans_harian; c_cust_thn REFCURSOR; BEGIN OPEN LOOP c_tahun;

FETCH c_tahun INTO v_tahun; EXIT WHEN NOT FOUND; c_cust_thn := cust_per_tahun(v_tahun); LOOP FETCH c_cust_thn INTO v_id_cust; EXIT WHEN NOT FOUND; v_trans_cust_thn := trans_thn_cust (v_tahun,v_id_cust); INSERT INTO trans_tahunan VALUES (v_tahun,v_trans_cust_thn,v_id_cust); v_bulan := 1; FOR v_bulan IN 1..12 LOOP v_trans_cust_bln := trans_bln_cust(v_tahun,v_bulan,v_id_cust); INSERT INTO trans_bulanan VALUES(v_tahun,v_bulan,v_trans_cust_bln,v_id_cust); END LOOP; END LOOP; CLOSE c_cust_thn; END LOOP; CLOSE c_tahun; END; $$ LANGUAGE 'plpgsql';


Tahun/Customer Bulan 1 2 3 4 5 6 7 8 9 10 11 12 Total

1 15

2000 2 15 4

3 9

1 8

2001 2 6

3

9 12 13 80 19 67 21 80 87 90 56 1170 999

1 76 129 68 132 34 43 91

2002 2 6 109 110 45 43 67 56 23

3 96 23 221 65 93 129

37

19

89

242

34

573

459

576 1203

Latihan (Dari Presentasi bpk Yudi Wibisono):Diketahui: Tabel USAGE (NO_TELP char(10), MULAI date, SELESAI date, ZONE_TUJUAN int) Cat: mulai dan selesai diisi tgl dan waktu (jam:menit) Tabel DISCOUNT (AWAL date, AKHIR date, PERSEN_DISC float) Cat: awal-akhir hanya digunakan waktunya saja. Contoh, awal: 21:00, akhir 23:00, discount 20% Tabel TARIF (ZONE int, TARIF_PER_MENIT int) Isilah: Tabel TAGIHAN (NO_TELP int ,JUMLAH_TAGIHAN float)


oracle 2 stored procedure

Documents