Dosen : Tan Amelia
Tugas : Contoh Stored Procedures, Functions dan Trigger
STIKOM Surabaya
- Stored Procedures
tb_masuk | tb_keluar | |||
Kode | Qty | Kode | Qty | |
1001 | 50 | 1002 | 10 | |
1002 | 25 | 1003 | 11 | |
1001 | 12 | 1004 | 15 | |
1003 | 15 | 1006 | 20 | |
1006 | 26 | 1002 | 5 | |
1004 | 30 | 1003 | 3 | |
1001 | 25 | 1001 | 20 | |
1002 | 23 | 1006 | 2 |
Kemudian pertama-tama bikin perintah seperti dibawah ini
CREATE PROC sp_hitungStok AS
Perintah tersebut di running terlebih dahulu.
Kemudian diedit melalui langkah-langkah berikut :
Klik Object Browser (F8)
muncul gambar seperti diatas, pilih database yang digunakan dan di expand, kemudian pilih folder Store Procedure, disini merupakan daftar dari Store Procedure termasuk Store Procedure yang kita bikin diatas. Cari nama Store Procedure yang telah kita bikin yaitu sp_hitungStok. Kemudian klik kanan dan edit…..
setelah ALTER PROC sp_hitungStok AS sisipkan perintah ini :
create table #tb_hasil(kode char(4), qty float) insert into #tb_hasil select kode, qty from tb_masuk insert into #tb_hasil select kode, qty from tb_keluar select kode, sum(qty)from #tb_hasil group by kode order by kode |
Setelah perintah disisipkan silakan di running terlebih dahulu procedure ini dengan menekan tombol Ctrl+E.
Setelah itu ditutup tidak apa-apa, atau masuk dalam window lain dengan menekan tombol Ctrl+W, pilih window sebelumnya atau untitled1.
Berikan perintah untuk memanggil procedure yang dibikin tadi dengan perintah berikut :
exec sp_hitungStok
kemudian perintah ini diblok, dan di running dengan Ctrl+E
Untuk lebih jelas hasilnya dalam bentuk grid Anda harus men-setting menu Query-Result in Grid
Anda akan melihat hasilnya :
Tabel hasil tersebut merupakan penjumlahan dari tabel tb_masuk dan tb_keluar.
Store Procedure dengan Parameter
Dengan contoh yang sama kita akan membuat suatu store procedure dengan parameter.
Seperti contoh diatas kita harus melakukan pengeditan dengan mengklik kanan Store Procedure yang kita bikin dan berikan perintah berikut :
ALTER PROC sp_hitungStok @kode char(4) AS create table #tb_hasil(kode char(4), qty float) insert into #tb_hasil select kode, qty from tb_masuk where kode = @kode insert into #tb_hasil select kode, qty from tb_keluar where kode = @kode select kode, sum(qty)from #tb_hasil group by kode order by kode |
Kemudian di running, terus untuk memanggilnya :
exec sp_hitungStok isi_paramater
contoh : exec sp_hitung ’1001′
silakan jalankan, kita akan mendapatkan hasil berikut :
Yang akan tampil hanya kode ’1001′
- Functions
(1)
CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS
S VARCHAR2(20)
BEGIN
S := ‘HALLO SEMUA’;
RETURN S;
END;
/
(2)
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;
/
Contoh Function Dengan Parameter :
(1)
CREATE OR REPLACE FUNCTION
pangkat (bil INTEGER, n INTEGER)
RETURN INTEGER AS
HASIL INTEGER(10);
I INTEGER;
BEGIN
HASIL := 1;
FOR I IN 1..n LOOP
HASIL := HASIL * bil;
END LOOP;
RETURN HASIL;
END;
/
(2)
SET SERVEROUTPUT ON
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
/
Contoh Function Dalam Function :
(1)
CREATE OR REPLACE FUNCTION kuadrat (X NUMBER)
RETURN NUMBER AS
HASIL NUMBER(10);
BEGIN
HASIL := X * X;
RETURN HASIL;
END;
/
(2)
CREATE OR REPLACE FUNCTION determinan
(a NUMBER, b NUMBER, c NUMBER)
RETURN NUMBER AS
D NUMBER(10);
BEGIN
D := kuadrat(b) – (4 * a * c);
RETURN D;
END;
/
(3)
SET SERVEROUTPUT ON
DECLARE
D NUMBER(10);
BEGIN
D := determinan(1, 1, -6);
DBMS_OUTPUT.PUT_LINE(‘Nilai determinan = ‘ || TO_CHAR(D));
END;
/
- Trigger
INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.
Bentuk dasar perintahnya :
CREATE TRIGGER nama_trigger
ON nama_tabel
FOR INSERT, UPDATE, DELETE
AS
isi statement-statement Anda disini.
GO
|
Misalkan kita punya data di table DaftarSiswa
DaftarSiswa | |
---|---|
Kode | Status |
1001 | |
1002 | |
1003 |
DaftarNilai | |
---|---|
Kode | Nilai |
Kolom Status pada tabel DaftarSiswa akan kita isi dengan “Lulus” dan “Tidak Lulus” secara automatis saat kita melakukan INSERT dan UPDATE pada tabel DaftarNilai.
Cara membuat dan mengisi kedua tabel diatas :
CREATE TABLE daftarSiswa (kode char(4), status char(10))
CREATE TABLE daftarnilai (kode char(4), nilai float)
Pada tabel DaftarSiswa diisi dengan perintah berikut :
INSERT INTO daftarSiswa VALUES(’1001′,”)
INSERT INTO daftarSiswa VALUES(’1002′,”)
INSERT INTO daftarSiswa VALUES(’1003′,”)
Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftarNilai.
CREATE TRIGGER tr_status ON daftarnilai FOR INSERT, UPDATE AS DECLARE @kode char(4) DECLARE @nilai float SELECT @kode = kode, @nilai = nilai FROM daftarNilai IF @nilai >= 60 UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode go |
SELECT* FROM daftarSiswa
SELECT* FROM daftarNilai
Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :
INSERT INTO daftarNilai values(’1001′,70)
INSERT INTO daftarNilai values(’1002′,50)
INSERT INTO daftarNilai values(’1003′,80)
Setelah dijalankan Anda bisa lihat kembali isi tabel daftarSiswa dan daftarNilai.
DaftarSiswa | |
---|---|
Kode | Status |
1001 | Lulus |
1002 | Tidak Lulus |
1003 | Lulus |
DaftarNilai | |
---|---|
Kode | Nilai |
1001 | 70 |
1002 | 50 |
1003 | 80 |
No comments:
Post a Comment