Contoh stored procedure pada mysql

Membuat Function, Stored Procedur dan View pada MySQL Bayu Lesmana Putra [email protected]

Abstrak Function dan Stored Procedure merupakan fitur utama yang paling penting di MySQL 5. Function dan Stored Procedure merupakan suatu kumpulan perintah atau statement yang disimpan dan dieksekusi di server database MySQL. Dengan SP (Stored Procedure), kita dapat menyusun program sederhana berbasis sintaks SQL untuk menjalankan fungsi tertentu. Hal ini menjadikan aplikasi yang kita buat lebih efektif dan efisien. Sedangakan view mirip dengan Stored Procedure. Dalam implementasinya, view biasa digunakan untuk menyederhanakan query yang kompleks untuk keperluan reporting. View dapat terdiri dari satu atau lebih query, termasuk nested query. Record pada sebuah view ada yang dapat dimanipulasi, dan ada pula yang tidak, tergantung DBMS yang digunakan.

Kata Kunci: MySQL, Stored Procedure & Function, View

Pendahuluan Setiap database mempunyai fasilitas yang memungkinkan aplikasi-aplikasi untuk menyimpan dan memanipulasi data. Selain itu, database juga memberikan fasilitas lain yang lebih spesifik yang dipakai untuk menjamin konsistensi hubungan antar tabel dan integritas data di dalam database. Referential integrity merupakan sebuah mekanisme untuk mencegah putusnya hubungan master/detail. Jika user mencoba menghapus sebuah field pada tabel master sehingga record di tabel detail menjadi yatim (tidak

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

mempunyai

induk),

referential

integrity

akan

mencegahnya.

Stored

Procedure/Function, dan View merupakan komponen dan fitur database, yang dengan keunikan fungsi masing-masing dapat dimanfaatkan untuk menjaga, mengelola, dan membantu kinerja database engineer dalam upaya terjaminnya integritas sebuah database.

Pembahasan Sekarang masuk ke bahasan utama, yaitu implementasi. Untuk menerapkan PROCEDURE, FUNCTION dan VIEW dibutuhkan suatu relasi, misalkan: mahasiswa dan prodi, sebagaimana yang diilustrasikan dengan perintah SQL di bawah ini.  Membuat database “akademik” mysql> create database akademik;  Menggunakan database mysql> use akademik;  Membuat tabel “mahasiswa” mysql>

create

varchar(25),

table

alamat

mahasiswa(nim

varchar(50),

char(5),

kode_prodi

nama

char(3),

primary key(nim));  Membuat tabel “prodi” mysql> create table prodi(kode_prodi char(3), nama_prodi varchar(25),

jurusan

varchar(20),

primary

key(kode_prodi));  Membuat relasi antara tabel “mahasiswa” dengan “prodi” mysql> alter table mahasiswa add foreign key(kode_prodi) references prodi(kode_prodi);  Menginputkan 5 data ke tabel “prodi”

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

 Menginputkan 4 data ke tabel “mahasiswa”

 Menampilkan data dari tabel “prodi”

 Menampilkan data dari tabel “mahasiswa”

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

1.

STORED PROCEDURE/FUNCTION

Untuk membuat stored procedure/function pada database digunakan pernyataan CREATE PROCEDURE atau CREATE FUNCTION. 1.1 PROCEDURE Sintak : CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body Keterangan :  sp_name: Nama routine yang akan dibuat  proc_parameter: Parameter stored procedue, terdiri dari :  IN : parameter yang digunakan sebagai masukan.  OUT : parameter yang digunakan sebagai keluaran  INOUT : parameter yang digunakan sebagai masukan sekaligus keluaran.  routine_body: terdiri dari statemen prosedur SQL yang valid. Agar lebih jelas, perhatikan contoh penggunaannya berikut ini.

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

Contoh 1 :

Dari contoh diatas terlihat bahwa parameter “x” (sebagai OUT) digunakan untuk menampung hasil dari perintah routine_body. Pernyataan “into x”, inilah yang mengakibatkan “x” menyimpan informasi nama (sebagai kolom yang ter-select). Untuk menjalankan procedure digunakan statemen call. Pernyataan “call pMhsIlkom(@Nama)” menghasilkan informasi yang kemudian disimpan pada parameter “@Nama”. Kemudian untuk menampilkan informasi ke layar digunakan pernyataan “select @Nama”. Contoh 2 :

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

Dari contoh yang kedua ini terlihat bahwa parameter “z” (sebagai IN) digunakan sebagai jalur untuk masukan routine dan parameter “x” dan “y” digunakan untuk menampung hasil dari perintah routine_body. Pernyataan “into x, y”, inilah yang mengakibatkan “x” dan “y” menyimpan informasi nama dan alamat (sebagai kolom yang ter-select). Pernyataan “call pMhs(@Nama, @Alamat)” menghasilkan informasi yang kemudian disimpan pada parameter @Nama dan @Alamat, sedangkan parameter “z” digunakan untuk menampung string ‘P01’ yang kemudian digunakan untuk memproses routine_body . Kemudian untuk menampilkan informasi ke layar digunakan pernyataan “select @Nama, @Alamat”. Jika diperhatikan pada contoh2 dan contoh2, dalam membuat routine selalu menggunakan delimiter. Hal ini digunakan untuk mengubah pernyataan delimiter dari “;” ke “//” ketika suatu procedure sedang didefinisikan. Sehingga sebelum delimiter ditutup, meskipun sudah ditekan enter masih dianggap satu-kesatuan perintah. Jika menggunakan perintah delimiter, maka untuk menutupnya digunakan karakter backslash (‘\’) karena karakter ini merupakan karakter escape untuk MySQL.

1.2 FUNCTION Secara default, routine (procedure/function) diasosiasikan dengan database yang sedang aktif. Untuk dapat mengasosiasikan routine secara eksplisit dengan database yang lain, buat routine dengan format: db_name.sp_name. MySQL mengijinkan beberapa routine berisi statemen DDL, seperti CREATE dan DROP. MySQL juga mengijinkan beberapa stored procedure (tetapi tidak stored function) berisi statemen SQL transaction, seperti COMMIT. Stored function juga berisi beberapa statemen baik yang secara eksplisit atau implisit commit atau rollback. Sintak : CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement or statements Keterangan :  sp_name : Nama routine yang akan dibuat.  proc_parameter : Spesifikasi parameter sebagai IN, OUT, atau INOUT valid hanya untuk PROCEDURE. (parameter FUNCTION selalu sebagai parameter IN)  returns: Klausa RETURNS dispesifikan hanya untuk suatu FUNCTION. Klausa ini digunakan untuk mengembalikan tipe fungsi, dan routine_body harus berisi suatu statemen nilai RETURN.  comment: Klausa COMMENT adalah suatu ekstensi MySQL, dan mungkin digunakan untuk mendeskripsikan stored procedure. Informasi ini ditampilkan dengan statemen SHOW CREATE PROCEDURE dan SHOW CREATE FUNCTION.

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

Contoh :

Dari contoh diatas terlihat bahwa parameter “x” diperlakukan sebagai IN karena sebagaimana dijelaskan sebelumnya bahwa fungsi hanya bisa dilewatkan dengan parameter IN. Kemudian untuk pengembalian nilainya, digunakan tipe data dengan kisaran nilai tertentu (dalam hal ini char(40)) dengan diawali pernyataan returns. Pernyataam “concat('Nama : ', x)” merupakan routine_body yang akan menghasilkan gabungan string “Nama :” dengan nilai dari parameter “x” yang didapat ketika fungsi ini dieksekusi. Perintah yang digunakan untuk mengeksekusi fungsi adalah “select fcNamaMHS('Sholihun')”dan “select fcNamaMHS('Bayu Lesmana Putra')” . Menampilkan status fungsi tertentu:

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

2. VIEW

Sintak : CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Keterangan :  create: Statemen ini digunakan untuk membuat suatu view baru, atau mengganti suatu view yang telah ada (exist) jika klausa OR REPLACE diberikan.  select_statement: Suatu statemen SELECT yang menyediakan definisi dari view. Statemen ini dapat men-select dari tabel dasar atau view yang lain. Statemen ini membutuhkan CREATE VIEW privilege untuk view, dan beberapa privilege untuk setiap kolom terpilih oleh statemen SELECT.  [(column_list)]: Daftar kolom yang akan dipilih.

View termasuk dalam komponen database. Secara default, suatu view baru dibuat ke dalam database yang diaktifkan. Untuk membuat secara eksplisit di dalam suatu database tertentu, maka buatlah nama view dengan format: db_name.view_name. Contoh yang akan diberikan adalah view untuk menyimpan informasi detail mahasiswa, dalam hal ini melibatkan 2 tabel, yaitu mahasiswa dan prodi.

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

Penutup Store Procedure / Function dan View merupakan komponen yang terdapat dalam database, berikut beberapa keuntungan menggunakan komponen tersebut antara lain:  Lebih cepat. Hal ini karena kumpulan perintah query dijalankan langsung diserver. Berbeda dengan jika dijalankan secara sekuensial di bahasa pemrograman, akan lebih lambat karena harus “bolak-balik” antara client dan server.  Menghilangkan duplikasi proses, pemeliharaan yang mudah. Pada dasarnya operasi yang terjadi di suatu aplikasi terhadap database adalah sama. Secara umum, didalam aplikasi biasanya terdapat operasi untuk validasi data inputan, menambahkan record baru, mengubah record, menghapus record dan sebagainya. Dengan SP, mungkin kita dapat menghindari adanya duplikasi proses yang kurang lebih sama, sehingga pemeliharaannya juga jadi lebih mudah.  Meningkatkan keamanan database. Database akan lebih aman karena aplikasi yang memanggil SP tidak perlu mengetahui isi di dalamnya. Sebagai contoh, dalam proses menambahkan data (insert), kita membuat suatu SP khusus. Dengan demikian, saat client atau aplikasi akan menambahkan data (insert) maka tidak perlu

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

tahu nama tabelnya, karena hanya cukup memanggil SP tersebut dengan mengirimkan parameter yang diinginkan.

Referensi http://pusat-bahasa.info/relo/download/119542039mohriyan-MySQL2.pdf

Biografi Penulis Bayu Lesmana Putra Adalah Lulusan Teknik Komputer, Fakultas Teknologi Informasi Politeknik Harapan Bersama, Tegal (D3, 2012) dan Saat ini sedang menempuh program S1 di STMIK Raharja Tangerang dengan Jurusan Teknik Informatika dengan konsentrasi Software Engineer. Kegiatan sehari-hari adalah sebagai karyawan di sebuah perusahaan swasta dengan posisi IT Engineer. Kegiatan lain sedang belajar programmer, web developer serta sistem jaringan. Penulis dapat dihubungi melalui email di [email protected] dan [email protected], YM b4yu_tk09, Facebook Bayu Lesmana.

Lisensi Dokumen: Copyright © 2008-2014 ilmuti.org Seluruh dokumen di ilmuti.org dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari ilmuti.org

Apa itu stored procedure MySQL?

Stored Procedure adalah sebuah prosedur layaknya subprogram (subrutin) di dalam bahasa pemrograman reguler yang tersimpan di dalam katalog basis data. Beberapa kelebihan yang ditawarkan stored procedure antara lain : mengingkatakan performa, mereduksi trafik jaringan, reusable, dan meningkatkan kontrol sekuriti.

Jelaskan apa yang dimaksud dengan stored procedure?

Stored procedure ini merupakan suatu blok program yang dapat dipanggil secara berulang-ulang dalam bentuk script. Dengan menggunakan stored procedure maka seluruh aplikasi akan terdistribusi dan ketika mengeksekusi satu perintah CALL aplikasi dapat langsung membentuk sejumlah task yang dapat berjalan di dalam server.

Apa yang dimaksud dengan perintah store procedure dan buat contoh dari store prosedure?

Stored procedure merupakan salah satu objek routine (Program) yang tersimpan pada database MySQL dan dapat digunakan untuk alternatif menggantikan berbagai macam kumpulan perintah yang sering kita gunakan, seperti misalnya menambahkan sebuah data di table tertentu, menghapus, ataupun juga mengupdate data.

Apa itu stored procedure dan function?

Pendahuluan Stored Procedure dan Function adalah rangkaian program yang disimpan dalam database dan dapat dipanggil oleh program lain atau melalui SQL Prompt Stored procedured ditulis dalam bentuk suatu Script. Type : Semua type data yang valid di MySQL.