Rabu, 24 Juli 2013
Pada postingan ini saya
akan membahas sedikit tentang procedure – procedure yang digunakan dalam
membangun suatu basis data atau yang sering disebutkan dengan database dengan
menggunakan bahasa pemrograman mysql.untuk lebih memahami apa yang dimaksud dengan
procedure..berikut pengertian singkat mengenai procedure
Pengertian Procedure
Procedure Adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.
A.Pembahasan tentang Stored Procedure :
1. Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan.
Dengan stored procedure : algoritma yang lama dimasukkan ke dalam stored procedure di SQL server, disimpan di server (misal dengan nama DoProsesStock). Pada program, cukup ditulis perintah SQL : Exec DoProsesStock StartDate, EndDate . Selanjutnya, SQL Server akan mengeksekusi perintah proses yang anda inginkan. satu kali perubahan proses, akan berlaku untuk semua user yang terhubung ke database, bahkan saat program masih berjalan di tiap komputer user (program tidak perlu dimatikan dulu).
2. Stored procedure akan menyederhanakan perintah SELECT * FROM table yang memang rumit, misal untuk laporan. Perintahnya dan algoritma nya sama dengan yang nomor 1. Bayangkan bila anda harus membuat perintah SELECT dari sebuah tabel, tapi juga diikuti kondisi2 tertentu yang biasanya berhubungan dengan data dari tabel lain.
3. Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel2 pembantu.
Bagaimana bisa? Stored procedure, dapat membuat tabel temporary yang disimpan sementara di dalam memori server selama proses berlangsung, atau bisa juga selama program connect.
4. Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja. Komputer client dapat berupa pentium 233 MMX, tapi dapat melakukan serangkaian proses yang rumit dan tetap tidak lambat. Karena bukan client yang berpikir, tetapi server yang melakukan pekerjaan, jadi client hanya akan mendapatkan data jadinya saja.
Pembuatan Stored Procedure :
anda dapat membuat stored procedure dengan program SQl Browser apa saja. bahkan cukup dengan sebuah TMemo, ADOConnection dan ADOQuery, anda sudah dapat membuat form untuk membuat stored procedure.
tapi yang paling mudah digunakan adalah Query Analizer dari MS SQL Server.
Syntax yang digunakan adalah :
CREATE PROCEDURE nama_stored_procedurenya @parameter1 tipe_data, @parameter2 2 tipe_data AS isi procedurenya.
Penjelasan
1. nama stored_procedure tidak boleh sama dengan nama fungsi internal, misal CREATE PROCEDURE SUM, tidak boleh ada spasi, tapi bisa menggunakan karakter _
2. Untuk membuat stored procedure gunakan perintah CREATE, untuk mengedit gunakan ALTER , untuk menghapus gunakan DROP.
perintah CREATE, ALTER, DROP dapat digunakan juga untuk membuat TABLE, VIEW, TRIGGER, FUNCTION, misal CREATE VIEW, ALTER FUNCTION,dsb
3. SQL Server mengenali parameter/variabel karena ada tanda @, contoh : @nama_barang char(50), @tanggal datetime, dsb
4. Untuk deklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDate datetime, @EndDate datetime, dst..
5. Untuk memasukkan nilai ke sebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium'
SET @Web='Klik-kanan' (perintah SET hanya bisa untuk 1 variabel saja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisa digunakan untuk banyak variabel)
6. Untuk mengambil nilai dari sebuah field dari tabel ke dalam variabel dapat juga menggunakan SET / SELECT , misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalu gunakan anda(),perintah ini valid bila data yang ditemukan hanya 1, bila lebih, maka varibale @nama tidak akan ada nilai nya.
7. Untuk mengambil data dalam jumlah banyak, misal seperti array atau StringList, gunakan temporary tabel.untuk pembahasan lebih lengkap, tunggu posting berikut nya.
Contoh pembuatan Stored Procedure. Kita akan membuat sebuah Stored procedure yang berfungsi untuk menggantikan perintah SELECT yang rumit, misal untuk laporan stok barang.
dalam contoh ini ada 3 tabel yang digunakan.
1. Barang (IDBarang, NamaBarang, IDSatuan)
2. Satuan (IDSatuan, Satuan)
3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir)
laporan yang diminta adalah untuk menampilkan stock sesuai dengan periode tertentu (bisa per hari, bisa juga per minggu, tergantung inputan StartDate dan EndDate).
SQL untuk pembuatan Stored Procedure nya :
CREATE PROCEDURE LapStockBarang1 @StartDate varchar(10), @EndDate varchar(10) AS
DECLARE @tgl1 datetime, @tgl2 datetime
**(variabel StartDate tidak bisa langsung datetime karena perintah EXEC LapStockBarang dalam bentuk string)
CREATE TABLE #TStock (IDBarang varchar(5), SAwal real, Masuk real, Keluar real)
CREATE TABLE #TStock2 (IDBarang varchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)
** buat temporary table , tanda # menandai bahwa tabel ini hanya akan ada saat proses stored procedure.
SELECT @tgl1= CONVERT(datetime,@StartDate,103), @tgl2=CONVERT(datetime,@EndDate,103)
** convert varchar(string) ke tipe datetime, gunakan perintah CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy
INSERT INTO #TStock (IDBarang, SAwal)
SELECT (IDBarang, SAwal)
FROM StockBarang
WHERE tanggal= @tgl1
** masukkan saldo awal pada tanggal bulan itu
INSERT INTO #TStock (IDBarang, Masuk, Keluar)
SELECT IDBarang, SUM(Masuk), SUM(Keluar)
FROM StockBarang
WHERE tanggal BETWEEN @tgl1 AND @tgl2
GROUP BY IDBarang
** masukkan JUMLAH dari masuk dan keluar
INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)
SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)
FROM #TStock
GROUP BY IDBarang
** sekarang gabungkan data2 nya
UPDATE #TStock2 SET SAkhir= SAwal + Masuk - Keluar
SELECT t.*, b.NamaBarang, s.Satuan
FROM #TStock2 AS t, Barang AS b, Satuan AS s
WHERE t.IDBarang=b.IDBarang
ND b.IDSatuan=s.IDSatuan
ORDER BY b.IDBarang
Nah perintah di program cukup seperti ini :
EXEC LapStockbarang '01/01/2005', '31/01/2005'
Pengertian Procedure
Procedure Adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.
A.Pembahasan tentang Stored Procedure :
1. Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan.
Dengan stored procedure : algoritma yang lama dimasukkan ke dalam stored procedure di SQL server, disimpan di server (misal dengan nama DoProsesStock). Pada program, cukup ditulis perintah SQL : Exec DoProsesStock StartDate, EndDate . Selanjutnya, SQL Server akan mengeksekusi perintah proses yang anda inginkan. satu kali perubahan proses, akan berlaku untuk semua user yang terhubung ke database, bahkan saat program masih berjalan di tiap komputer user (program tidak perlu dimatikan dulu).
2. Stored procedure akan menyederhanakan perintah SELECT * FROM table yang memang rumit, misal untuk laporan. Perintahnya dan algoritma nya sama dengan yang nomor 1. Bayangkan bila anda harus membuat perintah SELECT dari sebuah tabel, tapi juga diikuti kondisi2 tertentu yang biasanya berhubungan dengan data dari tabel lain.
3. Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel2 pembantu.
Bagaimana bisa? Stored procedure, dapat membuat tabel temporary yang disimpan sementara di dalam memori server selama proses berlangsung, atau bisa juga selama program connect.
4. Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja. Komputer client dapat berupa pentium 233 MMX, tapi dapat melakukan serangkaian proses yang rumit dan tetap tidak lambat. Karena bukan client yang berpikir, tetapi server yang melakukan pekerjaan, jadi client hanya akan mendapatkan data jadinya saja.
Pembuatan Stored Procedure :
anda dapat membuat stored procedure dengan program SQl Browser apa saja. bahkan cukup dengan sebuah TMemo, ADOConnection dan ADOQuery, anda sudah dapat membuat form untuk membuat stored procedure.
tapi yang paling mudah digunakan adalah Query Analizer dari MS SQL Server.
Syntax yang digunakan adalah :
CREATE PROCEDURE nama_stored_procedurenya @parameter1 tipe_data, @parameter2 2 tipe_data AS isi procedurenya.
Penjelasan
1. nama stored_procedure tidak boleh sama dengan nama fungsi internal, misal CREATE PROCEDURE SUM, tidak boleh ada spasi, tapi bisa menggunakan karakter _
2. Untuk membuat stored procedure gunakan perintah CREATE, untuk mengedit gunakan ALTER , untuk menghapus gunakan DROP.
perintah CREATE, ALTER, DROP dapat digunakan juga untuk membuat TABLE, VIEW, TRIGGER, FUNCTION, misal CREATE VIEW, ALTER FUNCTION,dsb
3. SQL Server mengenali parameter/variabel karena ada tanda @, contoh : @nama_barang char(50), @tanggal datetime, dsb
4. Untuk deklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDate datetime, @EndDate datetime, dst..
5. Untuk memasukkan nilai ke sebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium'
SET @Web='Klik-kanan' (perintah SET hanya bisa untuk 1 variabel saja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisa digunakan untuk banyak variabel)
6. Untuk mengambil nilai dari sebuah field dari tabel ke dalam variabel dapat juga menggunakan SET / SELECT , misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalu gunakan anda(),perintah ini valid bila data yang ditemukan hanya 1, bila lebih, maka varibale @nama tidak akan ada nilai nya.
7. Untuk mengambil data dalam jumlah banyak, misal seperti array atau StringList, gunakan temporary tabel.untuk pembahasan lebih lengkap, tunggu posting berikut nya.
Contoh pembuatan Stored Procedure. Kita akan membuat sebuah Stored procedure yang berfungsi untuk menggantikan perintah SELECT yang rumit, misal untuk laporan stok barang.
dalam contoh ini ada 3 tabel yang digunakan.
1. Barang (IDBarang, NamaBarang, IDSatuan)
2. Satuan (IDSatuan, Satuan)
3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir)
laporan yang diminta adalah untuk menampilkan stock sesuai dengan periode tertentu (bisa per hari, bisa juga per minggu, tergantung inputan StartDate dan EndDate).
SQL untuk pembuatan Stored Procedure nya :
CREATE PROCEDURE LapStockBarang1 @StartDate varchar(10), @EndDate varchar(10) AS
DECLARE @tgl1 datetime, @tgl2 datetime
**(variabel StartDate tidak bisa langsung datetime karena perintah EXEC LapStockBarang dalam bentuk string)
CREATE TABLE #TStock (IDBarang varchar(5), SAwal real, Masuk real, Keluar real)
CREATE TABLE #TStock2 (IDBarang varchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)
** buat temporary table , tanda # menandai bahwa tabel ini hanya akan ada saat proses stored procedure.
SELECT @tgl1= CONVERT(datetime,@StartDate,103), @tgl2=CONVERT(datetime,@EndDate,103)
** convert varchar(string) ke tipe datetime, gunakan perintah CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy
INSERT INTO #TStock (IDBarang, SAwal)
SELECT (IDBarang, SAwal)
FROM StockBarang
WHERE tanggal= @tgl1
** masukkan saldo awal pada tanggal bulan itu
INSERT INTO #TStock (IDBarang, Masuk, Keluar)
SELECT IDBarang, SUM(Masuk), SUM(Keluar)
FROM StockBarang
WHERE tanggal BETWEEN @tgl1 AND @tgl2
GROUP BY IDBarang
** masukkan JUMLAH dari masuk dan keluar
INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)
SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)
FROM #TStock
GROUP BY IDBarang
** sekarang gabungkan data2 nya
UPDATE #TStock2 SET SAkhir= SAwal + Masuk - Keluar
SELECT t.*, b.NamaBarang, s.Satuan
FROM #TStock2 AS t, Barang AS b, Satuan AS s
WHERE t.IDBarang=b.IDBarang
ND b.IDSatuan=s.IDSatuan
ORDER BY b.IDBarang
Nah perintah di program cukup seperti ini :
EXEC LapStockbarang '01/01/2005', '31/01/2005'
Contoh
pada posting sebelumnya telah dijelaskan store procedure
adalah sekumpulan
perintah SQL yang yang disusun dalam sebuah procedure yang
mempunyai nama
dan kegunaan tertentu. pada kesempatan ini saya akan
memberikan sedikit contoh
tentang store procedure. artikel yang saya tulis ini adalah
artikel resume dari
beberapa referensi yang saya baca. ok tanpa berlama lama
lagi berikut adalah
langkah – langkah yang harus dilakukan untuk membuat
procedure pada SQL
Server.
1. Buat dua buah tabel berikut beserta values dari masing
masing tabel.
buat dengan nama tblMasuk
Kemudian buat tabel berikut dengan nama tblKeluar
nah pada contoh ini kita membuat store procedure untuk
mencari stook akhir
dari tabel barang masuk dan dikurangi dari barang keluar.
2. kemudian buka tab query baru, ketik perintah berikut
CREATE PROC sp_hitungStok AS
perintah diatas adalah perintah untuk membuat store
procedure
3. langkah selanjutnya adalah memodifikasi store procedure
diatas, karna
perintah diatas belum lengkap. untuk melakukan modifikasi
perintah diatas
buka tab pada database kita pada programmability–>store
procedure–>klik
kanan pada procedure yang kita buat tadi seperti gambar
berikut:
dan kemudian menampilkan halaman berikut ini
4. Kemudian setelah AS tambahkan perintah berikut ini
create table #tb_hasil(kodeBarang char(4), qtyBarang float)
insert into #tb_hasil
select kodeBarang, qtyBarang from tblMasuk
insert into #tb_hasilselect kodeBarang, -qtyBarang from
tblKeluar
select kodeBarang, sum(qtyBarang) AS qtyBarang from
#tb_hasil group by kodeBarang order by kodeBarang
5. Selesai
selamat mencoba,.. semoga bermanfaat,.. tetaplah berbagi
untuk sesama,…
