Wednesday 4 July 2012

NIM / Nama : 10.41010.0233 / M. Mirza Alif Umanda
Dosen : Tan Amelia


STORED PROCEDURE
Membuat STORE PROCEDURE di SQL sangat penting untuk dikuasai terutama bagi ingin mendalami bahasa pemrograman aplikasi yang berkaitan dengan DATABASE . Karena didalam STORE PROCEDURE ini kita akan melakukan perhitungan-perhitungan data yang hasilnya berupa dataset langsung dapat kita ambil melalui program aplikasi yang kita bikin.Mungkin Anda pernah mendengar dengan program Client Server.
Maksud dari program Client Server adalah kita membuat suatu program yang diletakkan diserver berupa bahasa SQL dimana didalam program tersebut biasanya ditugaskan untuk melakukan perhitungan-perhitungan data dimana hasil akhirnya dapat kita ambil dan ditampilkan lewat program-program aplikasi yang berhubungan langsung dengan end user. Jadi STORE PROCEDURE tersebut letaknya di Sever, sedangkan program aplikasi yang berhubungan langsung dengan end user letaknya di komputer Client. Untuk itulah diistilahkan dengan program Client Server. Penguasaan SQL wajib hukumnya bagi yang ingin mengembangkan suatu program dengan berbasis DATABASE. Kalau tidak kita akan mendapat suatu kesulitan yang sangat luar biasa dalam mengembangkan suatu program aplikasi. Apalagi bahasa SQL ini sangat welcome sekali terhadap berbagai macam jenis bahasa. Semuanya rata-rata mengenali bahasa ini. Kita kembali ke awal, dengan menggunakan STORE PROCEDURE traffic (lalu lintas) data dijaringan dapat dikurangi, karena perhitungan-perhitungan sudah dilakukan diserver dan program di Client tinggal mengambil hasil akhirnya saja yang akan ditampilkan kepada end user.
sp01
Disini saya akan membuat contoh membuat STORE PROCEDURE dengan memakai SQL Server dengan menggunakan fasilitas Query Analyzer.
Untuk memudahkan saya akan berikan contoh kasus sederhana dibawah ini :
Kita mempunyai dua tabel, yang pertama tb_masuk dimana tabel ini berisi quantity barang masuk. Dan yang kedua tabel tb_keluar yang berisi data quantity barang keluar. Sekarang hitung jumlah quantity dengan rumus qty_akhir = qtymasuk-qtykeluar.
tb_masuk
tb_keluar
KodeQtyKodeQty
100150100210
100225100311
100112100415
100315100620
10062610025
10043010033
100125100120
10022310062
Pertama kali buka Query Analyzer, dan diharapkan Anda sudah bisa membuat CREATE DATABASE dan CREATE TABLE dengan isinya seperti tabel diatas.
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)
sp02

sp03

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…..
sp04

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
“create table #tb_hasil(kode char(4), qty float)” merupakan tabel temporary, setelah procedure selesai dijalankan table ini otomatis terhapus.
sp05

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 :
sp06

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
Lengkapnya sbb :
sp07

Kemudian di running, terus untuk memanggilnya :
exec sp_hitungStok isi_paramater
contoh : exec sp_hitung ’1001′
silakan jalankan, kita akan mendapatkan hasil berikut :
sp08

Yang akan tampil hanya kode ’1001′

FUNCTION
Studi kasus yang akan diambil di sini terkait dengan data yang disajikan sebagai berikut:
1.Kode Karyawan Nama Karyawan
2.K001 ROSIHAN ARI YUANA, S.PD
3.K002 DWI AMALIA FITRIANI, S.PD
4.K003 FAZA FAUZAN, S.PD
5.K004 NADA HASANAH, S.PD

dengan asumsi bahwa data di atas sudah disimpan di database.
Apabila Anda perhatikan data di atas, maka nama karyawan semuanya ditulis dalam huruf kapital (termasuk titlenya). Padahal dalam aturan penulisan title untuk S.PD yang benar adalah S.Pd. Nah yang jadi masalah adalah, kita tidak mungkin mengedit data satu-persatu. Apabila cuma hanya sejumlah kecil data maka tentu saja gak masalah. Tetapi bagaimana kalau datanya ratusan bahkan ribuan? Tentu membutuhkan waktu proses pengeditan yang lama.
Lantas bagaimana caranya untuk mengubah data tersebut dengan cepat? Untunglah dalam SQL terdapat function REPLACE() yang digunakan untuk menggantikan suatu substring dalam string tertentu.
Bagaimana dengan sintaksnya? Sintaks dari REPLACE() adalah sebagai berikut:

1.REPLACE(string, substring yang akan diganti, substring penggati)

Function ini dapat diletakkan dalam statement SELECT maupun UPDATE. Adapun contoh penggunaan REPLACE() pada SELECT adalah

1.SELECT REPLACE('www.rosihanari.net', 'www', 'http://www');

Maksud statement di atas adalah mengganti substring ‘www’ dengan ‘http://www’ pada string www.google.com. Sehingga output dari query tersebut adalah ‘http://www.google.com. Karena function REPLACE() ini diletakkan dalam SELECT maka tidak akan mengubah record apapun dalam tabel database.
Sekarang akan kita implementasikan function ini ke tujuan semula yaitu mengganti semua title S.PD dengan S.Pd untuk semua data karyawan. Karena tujuannya untuk mengubah data, maka kita gunakan perintah UPDATE.
1.UPDATE tabelKaryawan
2.SET namaKaryawan = REPLACE(namaKaryawan, 'S.PD', 'S.Pd');

Maksud dari statement SQL di atas adalah mengubah data karyawan pada tabel ‘tabelKaryawan’, terutama pada field ‘namaKaryawan’ yaitu nama karyawan yang baru adalah nama karyawan lama dengan title S.PD diganti S.Pd.
Anda juga bisa melakukan update data menggunakan REPLACE() hanya pada data tertentu yang dalam hal ini dibatasi dengan klausa WHERE, misalnya:

1.UPDATE tabelKaryawan
2.SET namaKaryawan = REPLACE(namaKaryawan, 'S.PD', 'S.Pd')
3.WHERE kodeKaryawan < 'K003';

Maksud dari statement di atas adalah mengubah title karyawan hanya untuk karyawan yang berkode K001 dan K002.


TRIGGER
Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE.
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
Saya akan memberi contoh sederhana untuk mudah memahami bagaimana cara kerja trigger ini.
Misalkan kita punya data di table DaftarSiswa
DaftarSiswa
KodeStatus
1001
1002
1003
Di tabel yang lain kita juga punya tabel Daftar Nilai :
DaftarNilai
KodeNilai
Daftar nilai ini belum ada isinya yang nantinya akan kita isi dengan perintah INSERT.
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

Untuk menganalisa lihat isi kedua tabel diatas dengan perintah
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
KodeStatus
1001Lulus
1002Tidak Lulus
1003Lulus
DaftarNilai
KodeNilai
100170
100250
100380
Disini terlihat saat kita melakukan perintah INSERT di tabel daftarNilai secara otomatis program trigger melakukan pengisian pada tabel daftarSiwa pada kolom “status”
Cukup sampai disini dulu penjelasan saya mengenai TRIGGER.

3 comments:

  1. saya coba syntax ini untuk field status-nya tidak keluar jika nilai dibawah 60

    ReplyDelete
  2. iya,sdh dicoba status dibawah 60 kok tidak muncul.apa yg kurang ya?

    ReplyDelete
  3. script yang insert di tb daftarsiswa error mulu kak

    ReplyDelete