Mempelajari Mysql Store Procedure

PROSEDUR TERSIMPAN (STORED PROCEDURE)

Prosedur tersimpan juga digunakan untuk meringkas perintah SQL, tetapi dalam prosedur tersimpan beberapa perintah SQL. Prosedur tersimpan tidak harus mengembalikan nilai seperti yang terjadi pada fungsi tersimpan.

Untuk membuat prosedur tersimpan menggunakan perintah
CREATE PROCEDURE.

CREATE PROCEDURE nama_prosedur ([variable_parameter[, …]])
BEGIN
…
Isi_prosedur
… END

Variabel parameter :
[ IN | OUT | INOUT ] nama_variabel tipe_variabel
Variabel parameter dalam prosedur tersimpan dibedakan menjadi tiga jenis yaitu :
1. IN

Variabel  parameter  hanya  dapat  digunakan  untuk  menerima  input. Tipe  IN merupakan nilai default dari variabel parameter.

2. OUT
Variabel parameter hanya dapat digunakan untuk menyimpan hasil output.
3. INOUT

Variabel   parameter   dapat   menerima   input   dan   juga   dapat   digunakan   untuk menyimpan hasil output.

Contoh :
mysql> DELIMITER  //
mysql> CREATE PROCEDURE prosedurl(a CHAR(35), OUT b INT)
-> BEGIN
-> select COUNT(nik) INTO b FROM karyawan
->    WHERE nama = a;
-> END
-> // Query OK, 0 rows affected(0.00 sec)

Perintah diatas akan membuat sebuah prosedur tersimpan yang bernama prosedur1 dengan dua buah variabel parameter, yaitu variabel a yang bertipe data CHAR(35) dan variabel b yang bertipe data INT. Variabel b mempunyai tipe parameter OUT sehingga variabel b hanya dapat digunakan untuk menyimpan hasil output, sedangkan variabel a tidak dideklarasikan tipe variabel parameternya, sehingga variabel a akan bertipe parameter IN karena IN merupakan nilai default, yang berarti bahwa variabel a hanya digunakan untuk menerima nilai input dari user.

Isi prosedur tersimpan dimulai dengan karakter BEGIN dan diakhiri dengan karakter END. Isi prosedur1 berisi perintah SELECT yang digunakan untuk menghitung jumlah karyawan yang mempunyai nama sesuai dengan nilai a.

Hasil output dari perintah SELECT akan disimpan dalam variabel b (ditandai dengan keyword INTO b).

Sebelum membuat prosedur tersimpan, karakter pemroses atau disebut juga dengan karakter delimiter harus diganti terlebih dahulu. Karakter delimiter adalah karakter yang digunakan oleh MySQL untuk memproses perintah ketika menemui karakter tersebut. Karakter ; (titik koma) merupakan karakter default. Perlu mengganti karakter ini dengan karakter yang lain agar MySQL tidak segera memproses perintah ketika menemui ; tetapi akan memproses ketika menemui karakter lain yang digunakan, hal ini disebabkan karena kita akan sering menggunakan karakter ; pada isi prosedur tersimpan. Untuk mengganti karakter yang akan dipakai sebagai karakter delimiter, menggunakan perintah DELIMITE, pada contoh diatas karakter yang digunakan sebagai karakter delimiter adalah //.

Untuk mengembalikan karakter delimiter menjadi karakter semula menggunakan perintah berikut setelah selesai membuat obyek prosedur tersimpan.

mysql> DELIMITER   ;

Untuk menjalankan prosedur1 menggunakan perintah CALL .

Contoh :

mysql> CALL prosedur1('Arman', @b );

Query OK,   0 rows  affected  (0.00 5ec)

Pernyataan diatas digunakan untuk memanggil prosedur tersimpan yang bernama prosedur1. Pada pemanggilan prosedur1 diinputkan nilai Arman. Nilai Arman disimpan dalam variabel parameter a. Nilai ini akan digunakan oleh isi prosedur1 sehingga isi dari prosedur1 menjadi:

Select COUNT(nik) INTO b FROM karyawan

WHERE nama = ‘Arman’;

Perintah diatas digunakan untuk menghitung jumlah (COUNT) karyawan yang bernama Arman. Data hasil perhitungan jumlah karyawan ini akan disimpan dalam variabel @b karena hanya variabel b yang bertipe parameter OUT. Hasil pemanggilan prosedur1 dapat dilihat melalui perintah SELECT seperti berikut :

Variabel @b sudah berisi nilai dari hasil output isi prosedur1. Dari hasil yang diperoleh ada dua karyawan yang bernama Arman. Contoh lain pembuatan prosedur tersimpan :

Pernyataan diatas membuat sebuah prosedur tersimpan beernama prosedur2. Didalam prosedur2 dideklarasikan tiga buah variable parameter, yaitu variable a yang bertipe data INT, b yang bertipe data CHAR(60) dan c yang bertipe data VARCHAR(100).

Isi dari prosedur2 adalah perintah menambahkan record baru kedalam tabel karyawan dengan nilai yang terdapat dalam variable a, b dan c. Nilai dari variabel a ditambahkan kedalam kolom nik, nilai dari variabel b ditambahkan kedalam kolom nama dan nilai dari variabel c ditambahkan kedalam kolom alamat.

Contoh pemanggilan prosedur2 adalah :

Nilai-nilai dari variabel ini akan digunakan oleh perintah INSERT yang ada dalam isi prosedur2, sehingga perintah INSERT dalam prosedur2 menjadi :

INSERT INTO karyawan(nik, nama, alamat) VALUES(113, ’Dian’, ’Jl. Utara No. 7’);

Untuk melihat hasil penambahan dari pemanggilan prosedur2 dapat dilihat melalui perintah SELECT seperti berikut :

Dengan prosedur2 terlihat perbedaan antara prosedur tersimpan dan fungsi tersimpan. Prosedur tersimpan tidak harus mengembalikan nilai karena semua perintah telah diproses dalam isi prosedur tersimpan, berbeda dengan obyek fungsi tersimpan yang harus mengembalikan nilai.

Untuk menghapus sebuah obyek prosedur tersimpan menggunakan perintah DROP DROP PROCEDURE [IF EXISTS nama_prosedur

Contoh :

mysql> DROP PROCEDURE prosedurl;
Query OK, 0 rows affected (0.05 sec)

Terima kasih, semoga bermanfaat.