Stored Procedure M. Saefudin SKom, MMSI
Kompetensi Dasar: 1. Memahami tujuan stored procedure 2. Memahami penerapan stored procedure
Indikator: 1. Mampu menjelaskan manfaat dari stored procedure 2. Memahami dan mampu membuat serta menggunakan stored procedure terhadap basis data
Stored procedure • Stored procedure (store procedure) merupakan sekumpulan perintahperintah SQL yang tersimpan dengan nama tertentu dan diproses sebagai sebuah kesatuan. • Secara sederhana store procedure merupakan sub-program yang tersimpan di database.
• Store procedure berisi perintahperintah umum dalam bentuk sebuah script yang berhubungan dengan database dan menghasilkan sekumpulan record.
Kegunaan Store Procedure • Store procedure lebih efisien daripada perintah biasa karena sekali sebuah store procedure dibuat, kita dapat memanggilnya pada saat diperlukan. • Cara ini akan meningkatkan modularitas dan memungkinkan pemakaian ulang sebuah program. • Pemakaian kembali program akan memudahkan pemeliharaan sebuah database. Apabila suatu saat aturan bisnisnya berubah maka kita tinggal mengubah store procedure, sedangkan pemanggilannya tetap.
Hal-hal yang dapat dilakukan Store procedure 1. Menerima parameter sebagai input dan mengembalikan nilai dalam parameter output. 2. Mengandung perintah-perintah program yang melaksanakan operasi di dalam database 3. Mengembalikan suatu nilai status pada pemanggilnya untuk mengindikasikan kesuksesan atau kegagalan procedure (dan alasan mengapa gagal)
Keuntungan menggunakan store procedure Keuntungan menyimpan kode program di database dibandingkan dengan menyimpan kode program di aplikasi : 1. Pemrograman menjadi modular. Kita dapat membuat store procedure sekali, menyimpannya dalam database, dan memanggilnya berulang kali dari program kita. Store procedure dapat dimodifikasi tanpa harus mengubah kode dari program yang memanggil.
2. Eksekusi program akan menjadi lebih cepat. Menjalankan program untuk mengakses database tentu saja akan lebih cepat jika dilakukan di dalam database itu sendiri. 3. Mengurangi lalu lintas jaringan. Ini akan sangat terasa jika kita menggunakan aplikasi client server yang mengakses database di komputer (server) lain. Mengirimkan nama store procedurenya saja tentu akan lebih sedikit daripada mengirimkan baris-baris perintah yang ada di dalamnya.
4. Dapat digunakan untuk mekanisme keamanan. User dapat diberi hak untuk menjalankan store procedure walaupun dia tidak memiliki hak untuk menjalankan perintahperintah yang ada di dalamnya.
Membuat Store Procedure Sintaks umum: CREATE PROCEDURE Nama_Procedure @Paramater_1 TipeData_1, …, @Parameter_n TipeData_N AS Perintah_Perintah SQL
•
Perintah membuat store procedure dengan menuliskan CREATE PROCEDURE Nama_Procedure atau dengan disingkat CREATE PROC Nama_Procedure.
•
Perintah-perintah SQL dapat berupa SELECT, DELETE atau UPDATE.
•
Untuk menyatakan parameter input dalam store procedure adalah dengan menyertakan simbol @ diikuti nama parameter pada sintaks di atas.
•
Pada saat sebuah store procedure dibuat, SQL Server mengecek perintah Transact-SQL yang ada di dalamnya.
•
Jika ada kesalahan maka akan ditampilkan pesan kesalahannya.
•
Namun jika tidak ada kesalahan maka store procedure akan disimpan dengan cara menulis nama dan informasi lain di tabel SysObjects dan perintah-perintahnya disimpan di tabel SysComments yang ada di database yang bersangkutan.
Pembuatan Store Procedure Tanpa Parameter Input Store procedure untuk menampilkan kode mata kuliah dan nama mata kuliah.
Store procedure yang telah dibuat di atas, kita jalankan cukup dengan menuliskan nama procedure tersebut secara langsung. data_matakuliah Pada store procedure di atas tidak menerima parameter input.
Pembuatan Store Procedure dengan Parameter Input Untuk menyertakan parameter input dalam store procedure adalah dengan menuliskan simbol @ diikuti dengan nama parameter.
Pemanggilan Store Procedure Untuk menjalankan store procedure di atas, kita harus menuliskan nama store procedure diikuti dengan suatu nilai sebagai parameter inputnya.
Apabila tidak menyertakan suatu nilai sebagai parameter input dalam pemanggilan procedure tersebut maka muncul pesan kesalahan
Pembuatan Store Procedure dengan Parameter Input Default Nilai yang dijadikan parameter input diatur pada saat pemanggilan procedure. Di samping itu kita juga dapat memberikan nilai default dalam procedure.
Pemanggilan store procedure Untuk menjalankan store procedure di atas tidak perlu menyertakan nilai yang akan dijalankan parameter input karena nilainya telah ditentukan dari dalam procedure.
Stored procedure untuk menambah data CREATE PROCEDURE Proc_Tambah_MataKuliah @Kode_MK char(5), @Nama_MK varchar(30), @SKS int, @Kode_prasyarat char(5) AS INSERT INTO Mata_Kuliah VALUES (@Kode_MK,@Nama_MK,@SKS,Kode_prasyarat)
Sebelum store procedure di atas dijalankan, kita perlu melihat banyaknya baris yang tersimpan dalam tabel mata_kuliah: select count (*) as [Banyaknya Mata Kuliah] from mata_kuliah
Banyaknya baris tabel Mata_Kuliah
Proc_Tambah_MataKuliah ’DT045’,’Pemrograman Internet’,4,’DT041’
Setelah penambahan data di atas maka banyaknya baris yang tersimpan dalam tabel mata_kuliah menjadi:
Store procedure untuk mengupdate data Store procedure untuk mengupdate bobot SKS yang terdapat dalam tabel mata_kuliah: CREATE PROCEDURE Proc_Ubah_SKS @Kode_MK char(5), @SKS int AS UPDATE Mata_Kuliah SET SKS=@SKS WHERE Kode_MK=@Kode_MK Misalkan kita akan mengubah bobot SKS dari mata kuliah berkode DT005: Proc_Ubah_SKS ‘DT005’,3
Store procedure untuk menghapus data CREATE PROCEDURE Proc_Hapus_MataKuliah @Kode_MK char(5) AS DELETE FROM Mata_Kuliah WHERE Kode_MK=@Kode_MK Data mata kuliah yang akan dihapus misalkan berkode DT005: Proc_Hapus_MataKuliah ‘DT005’ Setelah menghapus satu baris data tersebut, banyaknya baris data dalam tabel mata_kuliah menjadi:
Menggunakan 3 tabel berikut:
CREATE PROCEDURE Proc_IPK @NIM char(10) AS select khs.nim,mahasiswa.nama,(sum( case when khs.nilai='A' then 4*mata_kuliah.sks when khs.nilai='B' then 3*mata_kuliah.sks when khs.nilai='C' then 2*mata_kuliah.sks when khs.nilai='D' then 1*mata_kuliah.sks else 0 end )/sum(mata_kuliah.sks)) as IPK from khs inner join mata_kuliah on khs.kode_mk=mata_kuliah.kode_mk inner join mahasiswa on khs.nim=mahasiswa.nim and mahasiswa.nim=@NIM group by khs.nim,mahasiswa.nama
Proc_IPK ’04.01.1990’
Mengubah store procedure ALTER PROCEDURE Proc_IPK @NIM char(10) AS select khs.nim,mahasiswa.nama,(sum( case when khs.nilai='A' then 4.0*mata_kuliah.sks when khs.nilai='B' then 3.0*mata_kuliah.sks when khs.nilai='C' then 2.0*mata_kuliah.sks when khs.nilai='D' then 1.0*mata_kuliah.sks else 0.0 end )/sum(mata_kuliah.sks)) as IPK from khs inner join mata_kuliah on khs.kode_mk=mata_kuliah.kode_mk inner join mahasiswa on khs.nim=mahasiswa.nim and mahasiswa.nim=@NIM group by khs.nim,mahasiswa.nama
Menghapus store procedure Penghapusan store procedure Proc_IPK di atas dapat dilakukan dengan perintah, DROP PROCEDURE Proc_IPK
Latihan Ubahlah PROCEDURE Proc_IPK supaya nilai IPK tersebut ditampilkan dengan panjang tiga angka, yaitu satu angka di depan tanda decimal dan dua angka saja di belakang tanda desimalnya.
