Penggunaan Store Procedure dalam SQL
Dalam tulisan ini akan membahas tentang penggunaaan store procedure dalam Microsoft SQL Server. Untuk tulisan berikutnya akan di lanjutkan dengan penggunaan store procedure secara lebih mendalam.
Apakah Store Procedure itu?
Store Procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas/task khusus. Dalam Store Procedure dapat berisi beberapa statement dan setiap statement di kelompokan untuk satu object database.
Bagaimana cara membuat store procedure?
Untuk membuat store procedure, kita hanya menjalankan perintah “create procedure” diikuti dengan SQL script. Kita bisa meembuat store procedure melalui Query Analyzer pada MS SQL Server, atau dengan menggunakan menu New Procedure pada Enterprise Manager.
Kerangka sederhana Store Procedure
CREATE PROC procedure_name
[ { @parameter data_type }
]
AS sql_statement
[ { @parameter data_type }
]
AS sql_statement
Store Procedure terdiri dari:
1. Statemen CREATE PROC {PROCEDURE}
2. Nama Procedure;
3. Parameter list
4. SQL statement.
Banyak option lainnya dalam mendefinisikan store procedure, dalam tulisan ini hanya beberapa saja yang disebutkan, hanya sebagai gambaran awal dalam membuat store procedure.
Keuntungan menggunakan Store Procedure
Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:
1. Performance
Semua perintah SQL, yang kita kirimkan ke database server melalui kumpulan action yang disebut dengan execution. Hal ini menjadikan perintah SQL yang kita kirimkan melalui beberapa proses sebelum data dikembalikan ke client.
User mengirimkan request untuk mengeksekusi store procedure.SQL Server kemudian mengecek apakah ada syntax error. Mengidentifikasi dan melakukan pengecekan alias pada FROM clausa.Membuat query plan. Meng-compile dan kemudian mengeksekusi query plan untuk kemudian mengembalikan data yang diminta.
Ada banyak proses yang tidak kita ketahui yang dijalankan, kemudian apakah dengan menggunakan store procedure kita akan mem bypass hal tersebut ?
Di satu sisi, ya. Versi terdahulu pada SQL Server menyimpan execution plan yang telah dieksekusi dalam system tables, membuat sebagian pre-compiled. Hal ini meningkatkan performance, karena server tidak melakukan kompilasi store procedure setiap kali ia dipanggil.
Pada versi terbaru SQL server, ada banyak perubahan pemrosesan statement. Store Procedure disimpan dalam procedure cache ketika di panggil, membuat subsequent lebih cepat dipanggil.
2. Security
Store Procedure memberikan keuntungan yang baik dalam hal security. Dengan menggunakan store procedure, kita bisa memberikan permission untuk user yang ditunjuk untuk dapat mengakses data, menekan immense coding yang perlu kita lakukan pada Application Client. Ini adalah cara terbaik untuk mengontrol akses ke data.
3. Modifications/Maintenance
Jika kita menggunakan store procedure untuk mengakses database, setiap perubahan pada database dapat dipantau berdasarkan client application. Hal ini dimungkinkan karena kita dapat tahu persis dimana data diakses, dan kita juga tahu dimana kita harus melakukan perubahan. Hal ini berarti kita tidak perlu pusing kepala dengan ribuan baris source code pada client application untuk menemukan baris mana yang perlu dirubah.
4. Minimal processing at the client.
Ketika membuat aplikasi client/server, normalnya adalah client yang bertanggung jawab mengenai integritas data yang masuk ke dalam database. Mengelola Primary Key,Foreign Key, semuanya dilakukan oleh client, dan database server hanya bertugas menyimpan data yang di berikan client.
Dengan menggunakan store proocedure membantu kita untuk membuat batch perintah SQL, yang bisa digunakan untuk memanage transaction,constraints dll. Hanya sedikit daya yang ditulis pada client apllication, menjadikan aplikasi client menjadi lebih ringan. Aplikasi ini akan lebih terfokus pada menampilkan data untuk keperluan user dan aplikasi client tidak tau banyak mengenai database.
Sebagai contoh. Jika kita memiliki database yang berisi ribuan rows dan ratusan table. Kita memerlukan beberapa perhitungan sebelum melakukan update pada setiap record. Jika kita mengambil data secara lengkap ke client, dan meminta client computer untuk memproses data secara lengkap, bisa dibayangkan apa yang ditimbulkan. Tetapi jika client bisa mengeksekusi store procedure, ketika kita telah selesai melakukan calculasi untuk melakukan update pada record.Client tidak perlu melakukan proses calculasi. Hal ini juga menekan proses yang terjadi di client, dan server yang menjalankan kalkulasinya.
5. Network traffic
Aplikasi pada client selalu me request/ mengirimkan data ke database server. Data ini dikirimkan sebagai packet dan dikirimkan ke jaringan(Network) ke server.
penggunaan store procedure. Untuk penjelasan syntax pada penggunaan store procedure akan di bahas pada tulisan berikutnya.
LATIHAN STORE PROCEDURE DAN VIEW
Buat View berikut :
CREATE VIEW [dbo].[View_barang]
AS
SELECT KodeBarang, NamaBarang, Harga
FROM dbo.Barang
Buat Store Procedure berikut :
create PROCEDURE SP_A
@pnamabarang char(1)
AS
BEGIN
select * FROM dbo.View_Barang
where namabarang like @pnamabarang + '%'
END
Eksekusi Store Procedure SP_A berikut :
Exec SP_A ‘a’ à maka dimunculkan semua barang yang berawalan a
Exec SP_A ‘b’ à maka dimunculkan semua barang yang berawalan b
Bagaimana jika kita ingin membuat Store Procedure untuk memunculkan nama barang berakhiran sebuah karakter? Bagaimana pula jika mengandung sebuah karakter? Bagaimana pula jika mengandung sebuah kata tertentu?
STUDI KASUS PEMBUATAN STORE PROCEDURE UNTUK DATA PENJUALAN
Buat SP_1, untuk melihat barang-barang yang telah dijual. Filternya adalah kode barang tertentu.
View | FJ.TglFJ, FJ.NoFJ, FJDet.KodeBarang, Barang.NamaBarang, FJDet.Qty, FJDet.Harga, TotalHarga (perkalian antara Qty dan Harga) |
Parameter Input | @pKodeBarang nvarchar(15) |
Eksekusi | Exec SP_1 ‘ B-00001’  |
Buat SP_2, untuk melihat barang-barang yang telah dijual. Filternya adalah kode barang tertentu dan Periode tertentu.
View | FJ.TglFJ, FJ.NoFJ, FJDet.KodeBarang, Barang.NamaBarang, FJDet.Qty, FJDet.Harga, TotalHarga (perkalian antara Qty dan Harga) |
Parameter Input | @pKodeBarang Nvarchar(15) @pTgAwal DateTime @pTgAkhir DateTime |
Eksekusi | Exec SP_2 ‘ B-00001’, ‘06/16/2005 00:00:00’, ‘06/17/2005 23:59:59’ Catatan : Jam 23:59:59 wajib diisikan, jika tidak maka transaksi pada tanggal 17 Juni 2005 tidak akan dimunculkan, kecuali yang jamnya 00:00:00 |
Buat SP_3, untuk melihat barang-barang terlaris yang telah dijual. Filternya adalah Ranking Jumlah Barang Terlaris.
View | FJDet.KodeBarang, Barang.NamaBarang, TotalQty TotalQty adalah hasil Sum(FJDet.Qty) |
Parameter Input | @pJlhTerlaris |
Eksekusi | Exec SP_3 ‘10’  |
Buat SP_4, untuk melihat Total Penjualan Per Pelanggan per periode
View | FJ.KodePelanggan, Pelanggan.NamaPelanggan, FJ.TglFJ, FJ.TotalFaktur |
Parameter Input | @pTgAwal, @pTgAkhir |
Eksekusi | Exec SP_4 ‘06/01/2005’, ‘06/30/2005 23:59:59’ Output : Kode Pelanggan, Nama Pelanggan, Total Jual per Periode (Total Jual per Periode à Sum(TotalFaktur) |
StoreProcedure Lanjutan
Digabung dengan IF-ELSE-END
SP berisi perintah Insert
Buat SP untuk menambah data pelanggan, sebelum insert diperiksa dahulu apakah kodepelanggannya sudah ada atau belum, tolak jika sudah ada. BErikut SPnya :
Ditolak jika kodepelanggan sudah ada
Diterima jika kodepelanggan belum ada
SP berisi perintah Update
Untuk mengeksekusi SP_UpdatePelanggan sbb :
INISIALISASI VARIABEL
Ada dua cara untuk inisialisasi variable, menggunakan Set dan Select.
--Contoh 1 : Deklarasikan variable dan tipe datanya, kemudian isi nilainya dan cetak hasilnya
Declare @X int
Set @X = 5
Print @X
--Contoh 2 :--Deklarasikan variable dan tipe datanya, kemudian isi nilainya diambil dari field TotalFaktur pada table FJ dan cetak hasilnya
Declare @TF money
Select @TF = TotalFaktur
From FJ
Where NoFJ=’FJ-000001’
Print @TF
Contoh:
1. CREATE PROCEDURE SP_LihatBarang
AS
BEGIN
SET NOCOUNT ON;
SELECT * from Barang
END
GO
2. CREATE PROCEDURE SP_LihatBarangAwalan
@Awalan varchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT * from Barang
where NamaBarang like @Awalan+'%'
END
GO
3. CREATE PROCEDURE SP_LihatBarangKataTertentu
@kata varchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT * from Barang
where NamaBarang like '%'+@kata+'%'
END
GO
4. CREATE PROCEDURE SP_JumlahBarang
AS
BEGIN
DECLARE @x int
DECLARE @y int
DECLARE @z int
SELECT @x = COUNT(NamaBarang) from Barang
where HargaBeli <100000
print @x
SELECT @y = COUNT(NamaBarang) from Barang
where HargaBeli between 100000 and 500000
print @y
SELECT @z = COUNT(NamaBarang) from Barang
where HargaBeli >500000
print @z
END
GO
5. CREATE PROC SP_Penjualan
@Tahun int
AS
BEGIN
DECLARE @JansdMar decimal(18,2)
DECLARE @AprsdJun decimal(18,2)
DECLARE @JulsdSep decimal(18,2)
DECLARE @OktsdDes decimal(18,2)
SELECT @JansdMar = SUM(TotalFaktur) from FJ
where TglFJ between '01/01/'+ convert(varchar(4),@Tahun) and '03/31/'+ convert(varchar(4),@Tahun)+' 23:59:59'
print @JansdMar
SELECT @AprsdJun = SUM(TotalFaktur) from FJ
where TglFJ between '04/01/'+ convert(varchar(4),@Tahun) and '06/30/'+ convert(varchar(4),@Tahun)+' 23:59:59'
print @AprsdJun
SELECT @JulsdSep = SUM(TotalFaktur) from FJ
where TglFJ between '07/01/'+ convert(varchar(4),@Tahun) and '09/30/'+ convert(varchar(4),@Tahun)+' 23:59:59'
print @JulsdSep
SELECT @OktsdDes = SUM(TotalFaktur) from FJ
where TglFJ between '10/01/'+ convert(varchar(4),@Tahun) and '12/31/'+ convert(varchar(4),@Tahun)+' 23:59:59'
print @OktsdDes
END
GO
6. CREATE PROCEDURE SP_Juni2005
AS
BEGIN
DECLARE @pelanggan int, @barang int, @Qty int, @d money
SELECT @pelanggan = COUNT (DISTINCT KodePelanggan)
FROM FJ
WHERE TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59'
SELECT @barang = COUNT (DISTINCT b.NamaBarang)
FROM FJDet fd JOIN FJ f ON (fd.NoFJ = f.NoFJ) JOIN Barang b ON (b.KodeBarang = fd.KodeBarang)
WHERE TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59'
SELECT @Qty = SUM (fd.Qty)
FROM FJDet fd JOIN FJ f ON (fd.NoFJ = f.NoFJ)
WHERE TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59'
SELECT @d = SUM (TotalFaktur)
FROM FJ
WHERE TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59'
SELECT @pelanggan, @barang, @Qty, @d
END
7. CREATE PROCEDURE SPDeletePelanggan
@pKodePelanggan varchar(15), @pNamaPelanggan varchar(60), @pPiutang money
AS
BEGIN
IF EXISTS (SELECT KodePelanggan from dbo.Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
DELETE FROM [SIPP].[dbo].[Pelanggan]
WHERE KodePelanggan = @pKodePelanggan
SELECT 'Data terhapus'
END
ELSE
BEGIN
SELECT 'data gagal terdelete'
END
END
GO
8. CREATE PROCEDURE SPUpdatePelanggan
@pKodePelanggan varchar(15), @pNamaPelanggan varchar(60), @pPiutang money
AS
BEGIN
IF EXISTS (SELECT KodePelanggan from dbo.Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
UPDATE [SIPP].[dbo].[Pelanggan]
SET [KodePelanggan] = @pKodePelanggan
,[NamaPelanggan] = @pNamaPelanggan
,[Piutang] = @pPiutang
WHERE KodePelanggan = @pKodePelanggan
SELECT 'Data BERHASIL'
END
ELSE
BEGIN
SELECT 'data UPDATE GAGAL'
END
END
GO
9. CREATE PROCEDURE SPInsertPelanggan
@pKodePelanggan varchar(15), @pNamaPelanggan varchar(60), @pPiutang money
AS
BEGIN
IF EXISTS (SELECT KodePelanggan from dbo.Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
SELECT 'Data sudah ada'
END
ELSE
BEGIN
INSERT INTO [SIPP].[dbo].[Pelanggan]
([KodePelanggan]
,[NamaPelanggan]
,[Piutang])
VALUES
(@pKodePelanggan
,@pNamaPelanggan
,@pPiutang)
SELECT 'data berhasil'
END
END
GO
10. CREATE PROCEDURE SPCekPelanggan
@pKodePelanggan varchar(15), @pNoFJ varchar(12), @pTotalFaktur money
AS
BEGIN
IF EXISTS (SELECT KodePelanggan
from dbo.Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
IF EXISTS (SELECT NoFJ
from dbo.FJ
where NoFJ = @pNoFJ)
BEGIN
SELECT 'Data sudah ada'
END
ELSE
BEGIN
INSERT INTO [SIPP].[dbo].[FJ]
([NoFJ]
,[KodePelanggan]
,[TotalFaktur])
VALUES
(@pNoFJ
,@pKodePelanggan
,@pTotalFaktur)
UPDATE [SIPP].[dbo].[Pelanggan]
SET [KodePelanggan] = @pKodePelanggan
,[Piutang] = Piutang+@pTotalFaktur
WHERE KodePelanggan = @pKodePelanggan
SELECT 'data berhasil'
END
end
else
begin
select 'kode tidak terdaftar'
end
END
GO
1. Buat SP_HapusPelanggan
2. Buat SP_TambahFJ (
a. Parameter @NoFJ, @TglFJ, @KodePelanggan, @TotalFaktur
b. Deskripsi : insert berhasil jika NoFJ belum ada dan Kodepelanggan sudah ada
c. Perintah : Insert terhadap table FJ dan Update Pelanggan untuk field Piutang=isnull(Piutang,0) + @Total Faktur
d. Algo :
Jika NoFJ sudah ada cetak, tidak bisa insert
Jika NoFJ belum ada
Jika KodePelanggan ada, lakukan insert FJ dan Update Piutang
Jika Kode Pelanggan tidak ada, cetak kodepelangan belum ada
3. 1. SP_Lihat_Pasien (@Tahun char(2), @Bulan char(2)) untuk menampilkan pasien yang terdaftar pada tahun dan bulan tsb (sesuai parameter) Catatan : Format ID_Pasien sbb : ‘P’+ Tahun +Bulan + Nomor Urut per Bulan
a. Buat SQL untuk membuat Store Procedure tsb
CREATE PROCEDURE SP_Lihat_Pasien
@Tahun char(2), @Bulan char(2)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
from dbo.t_pasien
where id_pasien like '%' + @Tahun + @Bulan + '%'
END
GO
b. Buat SQL untuk menjalankan SP tersebut
exec dbo.SP_lihat_Pasien '07', '10'
GO
2. SP_Lihat_GolDarah (@Gol_Darah char(2)) untuk menampilkan data pasien yang bergolongan darah tertentu
a. Buat SQL untuk membuat Store Procedure tsb
alter PROCEDURE SP_Lihat_GolDarah
@Gol_Darah char(2)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
from dbo.t_pasien
where gol_darah = @Gol_Darah
END
GO
b. Buat SQL untuk menjalankan SP tersebut
exec dbo.SP_Lihat_GolDarah 'A'
GO
Tidak ada komentar:
Posting Komentar