Belajar MySQL Part#5 - Cara Membuat Stored Procedure di MySQL

Tutorial Hari Ini
Pengantar Stored Procedure
Membuat Dan Menjalankan Stored Procedure
Menghapus Stored Procedure
Pembahasan Materi Selanjutnya
Penutup

Pengantar Stored Procedure

Cara membuat Stored Procedure di MySQL adalah wajib dikuasai oleh seorang proggrammer, jika dapat menguasai stored procedure akan dapat membuat lebih efisien dalam pembuatan code program. Halo sahabat semua, apa khabar? pada tutorial hari ini merupakan kelanjutan dari tutorial sebelumnya yaitu pembahasan tentang bagaimana cara membangun relasi tabel serta cara menggabungkan beberapa tabel dengan perintah Inner join, Left Join, dan Right Join.

Jadi, Apa yang dimaksud stored procedure (prosedur tersimpan) di MySQL?

Stored procedure merupakan sekumpulan dari perintah-perintah SQL yang tersimpan dalam suatu database, pada umumnya digunakan pada perintah DML(select, insert, update, dan delete). Pada sebuah stored procedure dapat diberikan parameter sesuai dengan kebutuhan, sehingga dengan menerapkan stored procedure yang disimpan idalam suatu database program aplikasi akan dapat digunakan lebih dinamis berdasarkan parameter yang diberikan, juga stored procedure dapat dikombinasikan dengan fungsi-fungsi pernyataan bersyarat dan berkondisi seperti fungsi IF, While.

Apa tujuannya dari stored procedure? Salah satu tujuan atau kegunaan dari stored procedure, yaitu untuk mengefisienkan pembuatan code program pada suatu bahasa pemrograman ketika akan membuat program CRUD(Create, Read, Update, Delete) menggunakan database MySQL. Stored procedure dibuat dan disimpan hanya satu kali pada sebuah database, selanjutnya dapat dipanggil kembali, sehingga prosedur dengan nama yang sama dalam program dapat lebih cepat dieksekusi. Jenis perintah-perintah dalam stored procedure meliputi menambah data(insert), mengubah data (update), menghapus data(delete), dan memilih/menyeleksi data (select).

Baca juga: SQL Stored Procedures

Beberapa kelebihan menggunakan stored procedure:

Performa program aplikasi akan lebih baik, pada sebuah program aplikasi yang menerapkan Stored Procedure kemudian dikompilasi dan disimpan di cache memory selanjutnya dapat digunakan lagi. Hal ini membuat waktu akan lebih cepat ketika stored procedure dengan nama yang sama dieksekusi kembali.
Dari sisi keamanan akan lebih meningkat, bila sebuah aplikasi memanfaatkan pernyataan query atau sql dibuat dengan stored procedure program aplikasi tersebut dapat menghindari serangan terhadap injection SQL.
Dapat menghindari penulisan code program yang berulang-ulang.

Membuat Dan Menjalankan Stored Procedure

Untuk membuat prosedur tersimpan (stored procedure) tidak lepas dari perintah-perintah DML (Data Manifulation Language) atau statement SQL. Secara umum aturan penulisannya, sbb:

Sintaks:
DELIMITER $$
CREATE PROCEDURE nama_prosedur()
BEGIN
perintah_sql
END$$

DELIMITER ;

Untuk menjalankan atau memanggil stored procedure Sintaksnya, sbb:

call nama_prosedur()

Agar dapat lebih mudah dipahami ikuti langkah-langkah latihan berikut ini. Dari dialog layar Laragon, klikØ MenuØ MySQLØ Start MySQL, selanjutnya klik Terminal.

Ketik perintah-perintah sql pada terminal, sbb:

Latihan1: Penerapan perintah DML-Select.

Buka database puskesmas_db,selanjutnya tampilkan semua data obat dari tabel obat.

use puskesmas_db

DELIMITER $$
CREATE PROCEDURE DaftarObat()
BEGIN
SELECT * FROM obat;
END$$
DELIMITER $$

Selanjutnya jalankan: call DaftarObat()

Latihan2: Penerapan perintah DML-Select. Berdasarkan field yang dipilih.

Selanjutnya tampilkan data obat berdasarkan field yang dipilih kode_obat,nama_obat, kemasan, harga_obat dari tabel obat.

Perintahnya:

DELIMITER $$

CREATE PROCEDURE DaftarHarga()
BEGIN
SELECT kode_obat,nama_obat,kemasan,harga_obat

FROM obat

END$$

DELIMITER ;
Jalankan: call DaftarHarga()

Latihan3: Penerapan perintah DML-Select. Dengan menggunakan kondisi.

tampilkan data obat untuk field kode_obat,nama_obat,jenis,harga_obat dari tabel obat, berdasarkan harga_obat di bawah lima puluh ribu rupiah.

Perintahnya:

DELIMITER $$
CREATE PROCEDURE DaftarHargaDenganKondisi()
BEGIN
   SELECT kode_obat,nama_obat,jenis,harga_obat
   FROM obat
   WHERE harga_obat<50000;

END$$

DELIMITER ;

Jalankan: call DaftarHargaDenganKondisi()

Latihan4: Penerapan perintah DML-Select. Dengan menggunakan kondisi dan parameter.

tampilkan data obat untuk field kode_obat,nama_obat,jenis,harga_obat dari tabel obat, untuk harga_obat berdasarkan kondisi dan parameter untuk berbagai nilai yang dibawah ditentukan oleh user. Perintahnya:

DELIMITER $$

CREATE PROCEDURE DaftarHargaBerparameter(kondisiHarga int)

BEGIN

select kode_obat,nama_obat,jenis,harga_obat

from obat

where harga_obat<kondisiHarga;

END$$

DELIMITER ;

Pengujian-1: call DaftarHargaBerparameter(25000)

Pengujian-2: call DaftarHargaBerparameter(50000)

Pengujian-3: call DaftarHargaBerparameter(150000)

Latihan5: Penerapan perintah DML-Insert.

Perintah insert berfungsi untuk menambah data/record, biasanya perintah insert banyak diterapkan ketika membuat CRUD pada sebuah bahasa pemrograman, untuk penggunaan secara interaktif atau langsung ikuti perintah-perintah berikut ini.

Perintahnya:

DELIMITER $$

CREATE PROCEDURE INSERTPengguna(

nip INT(10),pass CHAR(10),level_user VARCHAR(10)

)

BEGIN

INSERT INTO pengguna

VALUES (nip, pass, level_user);

END$$

Pengujian: CALL INSERTPengguna(5,'414','Laporan');

Untuk contoh tabel Dokter:

DELIMITER $$
CREATE PROCEDURE DokterINSERT(kode_dok Char(10),

nama_dok varchar(25),kelamin char(15),spesialis varchar(30),

alamat_dok varchar(50),kota_dok varchar(15),telp_dok varchar(30),

email varchar(50))
BEGIN
INSERT INTO dokter VALUES (kode_dok,nama_dok,kelamin,spesialis, alamat_dok,kota_dok,telp_dok,email);
END$$
Pengujian:CALL DokterINSERT('D0005','Darmayuda','Pria','Penyakit Dalam' ,'Jl. ABG No.100','Bandung','Bandung','022-78654321', 'darma.yuda@ymail.com');

Latihan6: Penerapan perintah DML-Update.

Perintah update berfungsi untuk mengubah data/record, sama dengan perintah insert perintah update banyak diterapkan ketika membuat CRUD pada sebuah bahasa pemrograman.

Perintahnya:

DELIMITER $$

CREATE PROCEDURE DokterUPDATE(kode_dok char(10),nama_dok varchar(25),

kelamin char(15),spesialis varchar(30), alamat varchar(50),

kota char(15),telpon varchar(30),email varchar(50))

BEGIN

UPDATE dokter SET nama_dok=nama_dok,kelamin=kelamin,

spesialis=spesialis,alamat=alamat,kota=kota,telpon=telpon,

email=email

WHERE kode_dok=kode_dok;

END$$

DELIMITER ;

Latihan7: Penerapan perintah DML-Delete.

Perintah delete berfungsi untuk menghapus data / record, sama dengan perintah insert dan update banyak diterapkan ketika membuat CRUD pada sebuah bahasa pemrograman.

Perintahnya:

DELIMITER $$

CREATE PROCEDURE DokterDELETE(kode_dok char(10))

BEGIN

DELETE FROM dokter WHERE kode_dok=kode_dok;

END$$

DELIMITER ;

Catatan: untuk pengujian perintah Update & Delete dapat diterapkan pada program aplikasi.

Menghapus Stored Procedure

Pada perintah-perintah stored procedure yang sudah dibuat kadang-kadang sudah tidak diperlukan lagi untuk hal tersebut stored procedure harus di hapus. Untuk menghapus nama stored procedure perintahnya, sbb:

Sintaks:
drop procedure nama_prosedur();

Contoh: drop procedure DaftarHarga();

Menampilkan Nama-nama Stored Procedure

Kadang kala bagi seorang user atau programmer setelah membuat sekian stored procedure tentunya tidak mudah untuk diingat jika tidak memiliki daftar catatan tersendiri.

Berikut adalah aturan penulisan untuk menampilkan daftar nama-nama dari stored procedure yang telah dibuat pada suatu database yang digunakan.

Sintaks:

SELECT routine_name, routine_type, routine_schema

FROM information_schema.routines

WHERE routine_type = ‘PROCEDURE’ AND

routine_schema = ‘database_name’;

Keterangan :

routine_name, artinya nama dari stored procedure.

routine_type, artinya pada kasus ini stored procedure yang nilainya “PROCEDURE“.

routine_schema, artinya menunjukkan nama database stored procedure tersebut tersimpan.

Latihan:
SELECT routine_name, routine_type, routine_schema
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'puskesmas_db';

Hasilnya, sbb:

Materi Selanjutnya

Penutup

Sekian pembahasan tutorial hari ini, tentang pembuatan stored procedure seperti penerapan pada perintah-perintah DML(Insert, Update, Delete, serta select). Selanjutnya akan dibahas cara pembuatan trigger. Sekian dan terima kasih sudah mengikuti terus tutorial ini, semoga bermanfaat, syalam penulis.