procedure tersimpan

Pengertian Prosedur dan Fungsi Tersimpan
Yang dimaksud dengan prosedur tersimpan (stored procedure) adalah suatu modul yang berisi kumpulan pernyataan SQL yang ditujukan untuk melaksanakan tugad tertentu dan letaknya ada pada server. Modul ini bisa dipanggil oleh klien, sedangkan pengeksekusian dilakukan di server.

Keuntungan penggunaan prosedur tersimpan adalah sebagai berikut:
1. Meningkatkan kinerja karena mengurangi pengiriman kode dari klien ke server, mengingat modul berada pada server.
2. Meningkatkan keamanan karena pengaksesan data tertentu ditangani dalam server, tidak melalui pengaksesan secara langsung oleh klien.
3. Meningkatkan integritas data (konsistensi data) saat sejumlah aplikasi memanggil prosedur tersimpan yang sama.

Sebaliknya, kelemahan yang terjadi sebagai akibat keuntungan-keuntungan tersebut, server akan lebih terbebani, karena banyak proses yang harus ditangani oleh server.

Pada MySQL, terdapat istilah prosedur tersimpan dan fungsi tersimpan (stored function). Perbedaannya:
 Prosedur tersimpan tidak menghasilkan nilai ketika dipanggilv
 Fungsi tersimpan menghasilkan nilai ketika dipanggil dan tetntu saja seperti fungsi biasa bisa dipanggil di dalam suatu pernyataan (misalnya dalam SELECT)v

Nilai yang dihasilkan oleh fungsi tersimpan biasa disebut nilai balik (return value)
Prosedur dan fungsi yang tersimpan dikenal dengan sebutan rutin (routine).
Cara Menciptakan Prosedur Tersimpan
CREATE PROCEDURE nama_prosedurtersimpan ([parameter_prosedur [,…]])
bagian_kode
Dalam hal ini:
• nama_prosedurtersimpan menyatakan nama prosedur tersimpan
• parameter_prosedur menyatakan definisi untuk parameter prosedur tersimpan
• bagian_kode berupa pernyataan-pernyataan SQL
Adapun bentuk parameter_prosedur berupa:
[ IN I OUT I INOUT ] nama_parameter tipe
Setiap parameter secara opsional bisa diawali IN, OUT, atau INOUT. IN berarti parameter sebagai masukan bagi prosedur. OUT berarti parameter sebagai keluaran. INOUT berarti sebagai masukan sekaligus keluaran.

Menjalankan Prosedur Tersimpan
Untuk memanggil prosedur tersimpan, gunakan pernyataan CALL. Pernyataan ini memiliki bentuk:
CALL nama_pt ([parameter[,…]])
Dalam hal ini, nama_pt adalah nama prosedur tersimpan. 
Cara Menciptakan Fungsi Tersimpan
Fungsi tersimpan diciptakan melalui pernyataan CREATE FUNCTION. Kaidah dasarnya seperti berikut:
CREATE FUNCTION nama_ft ([param_fung [,…]])
RETURNS tipe
bagian_kode
Dalam hal ini:
• nama_ft menyatakan nama fungsi tersimpan
• param_fung menyatakan definisi untuk parameter fungsi tersimpan
• RETURNS tipe berguna untuk menentukan tipe nilai balik
• Bagian_kode berupa pernyataan-pernyataan SQL
Berbeda dengan prosedur tersimpan, semua parameter pada fungsi tersimpan hanya berfungsi sebagai masukan.
Memperoleh Informasi Prosedur dan Fungsi Tersimpan
MySQL menyediakan perintah SHOW PROCEDURE dan SHOW FUNCTION untuk memperoleh informasi tentang prosedur dan fungsi tersimpan. Kaidah pernyataan:
SHOW { PROCEDURE I FUNCTION } STATUS [ LIKE ‘pola’ ]
Bila pola disertakan, informasi nama prosedur atau fungsi tersimpan yang cocok dengan pola akan ditampilkan.
Menghapus Prosedur atau Fungsi Tersimpan
Bila dikehendaki untuk menghapus suatu prosedur atau fungsi tersimpan, bisa menggunakan pernyataan DROP PROCEDURE atau PROCEDURE FUNCTION. Kaidah kedua pernyataan tersebut:
DROP { PROCEDURE I FUNCTION} [ IF EXISTS] nama
Tanda { I } berarti yang ada di dalamnya bisa berupa salah satu (yaitu PROCEDURE atau FUNCTION). Bagian IF EXISTS bersifat opsional; artinya “jika ada”. Bila IF EXISTS disertakan, pernyataan tidak akan menimbulkan kesalahan walaupun prosedur atau fungsi tersimpan nama tidak pernah didefinisikan.

Pernyataan Majemuk BEGIN…END
Bagian kode pada prosedur maupun fungsi tersimpan bisa berupa satu atau beberapa pernyataan. Bila terdapat beberapa pernyataan, Anda perlu meletakan pernyataan-pernyataan tersebut dalam sebuah BEGIN…END. Dalam hal ini, BEGIN…END mewakili pernyataan majemuk (sebuah pernyataan yang sebenarnya tersusun atas sejumlah pernyataan).
Sebuah pernyataan majemuk BEGIN…AND juga bisa diberi label.
Kaidahnya:
[ awal_label: ] BEGIN
[ daftar_pernyataan ]
END [ akhir_label ]

• Pernyataan DECLAR
Pernyatan DECLARE berguna untuk mendefinisikan variabel, kondisi, penangan, dan kursor yang berifat local bagi rutin. Secara umum, pernyataan ini harus berada dalam BEGIN…END dan keberadaannya harus sebelum pernyataan-pernyataan yang lain.
Variabel dapat digunakan untuk menyimpan nilai dan nilainya bisa diubah sewaktu-waktu. Pendefinisian variabel dilakukan dengan mengikuti kaidah seperti berikut: 

DECLARE nama_var [ , . . . ] tipe [ DEFAULT nilai ]

Dalam hal ini, tipe menyatakan tipe data untuk variable nama_var dan DEFAULT nilai digunakan untuk memberikan nilai awal bagi variable bersangkutan. Kaidah diatas juga menyatakan bahwa dimungkinkan untuk mendefinisikan beberapa variable sekaligus sepanjang tipenya sama.

• Pernyatan SET
Pernyataan SET digunakan untuk memberikan nilai ke variable.
Kaidahnya:
SET nama_var = ekspr [ , nama_var = ekspr ] . . .

Bedasarkan sintak di atas, sebuah pernyataan SET dapat digunakan untuk mengisi sebuah variable dengan nilai masing-masing.

• Pernyataan SELECT . . . INTO
Pernyataan SELECT . . . INTO berguna untuk memberikan hasil dari pernyatan SELECT ke dalam suatu variable. Kaidahnya:
SELECT nama_kolom [ , . . . ]
INTO nama_var [ ,. . . ] ekspresi_tabel
Pada sintaks di atas, ekspresi_tabel dapat berupa seperti FROM nama_tabel atau dalam bentuk yang lebih kompleks seperti melibatkan klausa seperti WHERE. Perlu diketahui, harus dipastikan bahwa hasil SELECT hanya berupa satu baris.
Contoh penggunaan DECLARE dan SELECT … INTO:
USE pegawai
DELIMITER //
CREATE FUNCTION jumbagian ()
RETURENS INT
BEGGIN
DECLARE jumlah INT;

SELECT COUNT (*) INTO jumlah
FROM bagian;
END
//
DELINITER ;

Penjelasan untuk kode di atas adalah seperti berikut.
• Pernyataan
DECLARE jumlah INT ;
Digunakan untuk mendeklarasikan variable jumlah yang bertipe INT (bilangan bulat)
• Pernyataan
SELECT COUNT (*) INTO jumlah
FROM bagian ;
Digunakan untuk memperoleh jumlah baris dari table bagian dan hasilnya diberikan ke variable jumlah.
• Pernyataan
RETURN jumlah ;
Memberikan nilai balik fungsi berupa isi dari variable jumlah.
berikut menunjukan pengujian fungsi jumbagian () 
mysql > SELECT jumbgaian () ;
+-------------------+
| jumbagian () |
+-------------------+
| 6 |
+-------------------+
1 row in set ( 0.13 set )

mysql >


• Kontruksi Pengatur Aliran Eksekusi

Untuk keperluan pengaturan aliran eksekusi di dalam prosedur atau fungsi tersimpan, MySQL menyediakan berbagai kontruksi yang terkait dengan pengambilan keputusan dan pengulangan. Implementasinya berupa perintah IF, CASE, LOOP, ITERATE, REPEAT, WHILE, dan LEAVE.
Pernyataan IF
Pernyataan IF berguna untuk melakukan pengambilan keputusan berupa pemilihan eksekusi didasarkan suatu keadaan. Bentuknya:
IF kondisi THEN daftar_pernyataan
[ ELSEIF kondisi THEN daftar_pernyataan ] . . .
[ ELSEIF daftar_pernyataan ]
END IF
Daftar pernyataan yang terletak sesudah THEN dieksekusi hanya kalau kondisi didepan THEN bernilai (TRUE). Bagian ELSEIF digunakan untuk menyeleksi kembali kalau keadaan sebelumnya bernilai salah (FALSE).
Contoh berikut menunjukan prosedur yang akan menampilkan nilai terkevil dari dua buah parameternya.
USE pegawai
DELIMITER //
CREATE PROCEDURE tes_if ( x INT, y INT )
BEGIN
IF x < y THEN
SELECT CONCAT ( ‘ Terkecil : ‘, x ) ;
ELSEIF ;
END
//
DELIMITER ;
Contoh pemanggilan fungsi tes_if ()

Mysql > CALL tes_if ( 10, 22 );
+------------------------------------+
| CONTAC ( ‘ Terkecil : ‘, x ) |
+------------------------------------+
| Terkecil : 10 |
+------------------------------------+
1 row in set ( 0. 00 sec )

Query ok, 0 rows affected ( 0. 00 sec )

Mysql > CALL tes_if (22, 10 ) ;
+------------------------------------+
| CONTAC ( ‘ Terkecil : ‘, y ) |
+------------------------------------+
| Terkecil : 10 |
+------------------------------------+
1 row in set ( 0. 00 sec )

Query ok, 0 rows affected ( 0. 00 sec )

Mysql >

Pernyataan CASE
Pernyataan CASE merupakan alternative lain untuk IF dengan kondisi yang bertingkat. Bentuknya:

CASE case_value

WHEN search_condition THEN statement_list
[ WHEN search_condition THEN statement_list ] . . .
[ else statement_list ]

END CASE

Seleksi akan dilakukan dimulai dari WHEN teratas. Kalau ada yang memenuhi (kondisi bernilai benar / TRUE) maka pernyataan yang terletak sesudah THEN akan dieksekusi dan kemudian menuju ke END CASE. Kalau suatu WHEN diperiksa tidak memenuhi, maka WHEN berikutnya akan diperiksa. Bagian ELSE akan dieksekusi kalau tak ada WHEN yang cocok.

Contoh penggunaan CASE:

DELIMITER //
CREATE PROCEDURE tes_case ( nilai INT )
BEGIN
DECLARE skor CHAR (50) ;

CASE
WHEN nilai > 100 OR nilai < 0 THEN
SET skor = ‘Nilai salah’ ;
WHEN nilai > = 90 THEN
SET skor = ‘skor: A’ ;
WHEN nilai > = 70 THEN
SETskor = ‘skor: B’ ;
WHEN nilai > = 60 THEN
SET skor = ‘skor: C’ ;
WHEN nilai > = 50 THEN
SET skor = ‘skor: D’ ;
ELSE
SET skor = ‘skor: E’ ;
END CASE ;
SELECT skor ;
END
//
DELIMITER ;
Pada contoh diatas digunakan untuk memberikan gambaran penggunaan CASE dalam suatu prosedur untuk menghasilkan Skor A, Skor B, hingga skor E bergantung pada keadaan berikut. 
Nilai > 100 Nilai < 0 Nilai salah
Nilai ≥ 90 Skor A
70 ≤ Nilai < 90 Skor B
60 ≤ Nilai < 70 Skor C
50 ≤ Nilai < 60 Skor D
0 ≤ Nilai < 50 Skor E
Pernyataan LEAVE
Pernyataan LEAVE digunakan untuk keluar dari suatu konstruksi yang memiliki suatu label. Bentuknya:
Leave nama_label;
Pernyataan LOOP
Pernyataan LOOP berguna untuk melakukan pengulangan. Bentuknya:
[ awal_label : ] LOOP
Statement_list
END LOOP [ akhir_label ]
Daftar pernyataan yang berada antara LOOP dan END LOOP akan terus dieksekusi sampai ada suatu pernyataan untuk keluar pengulangan dieksekusi. Dalam hal ini, pernyataan yang digunakan untuk keluar pengulangan berupa pernyataan LEAVE. Contoh berikut menunjukkan penggunaan LOOP dan LEAVE. 
DELIMITER//
CREATE PROSEDUR tes_loop( )
BEGIN
DECLARE pencacah INT;
SET pencacah = 0;

SELECT CONCAT (‘Nilai awal pencacah : ’ , pencacah);
Ulang : LOOP
SET pencacah = pencacah + 1;
IF pencacah > 10 THEN LEAVE ulang; END IF;
END LOOP ulang;
SELECT CONCAT (‘Nilai akhir pencacah : ’ , pencacah);
END
//
DELIMITER;
Pada prosedur tes_loop, variable pecahan dideklarasikan sebagai variable local yang bertipe INT. Mula-mula variable tersebut diiisi dengan 0. Selanjutnya, pernyataan
SELECT CONCAT ( ‘Nilai awal pecahan : ‘, pecahan ) ;
Digunakan untuk menampilkan tulisan ‘Nilai awal pecahan : ‘diikuti dengan isi variable pecahan.
Pernyataan LOOP digunakan untuk mengeksekusi pernyataan-pernyataan yang ada didalamnya secara terus-menerus, sampai ada perintah untuk keluar dari pernyetaan tersebut.
Pernyataan yang ada dalam LOOP yang pertama adalah
SET pecahan = pecahan + 1 ;
Pernyataan tersebut untuk menaikkan isi variable pecahan sebesar 1.
Pernyataan kedua
IF pecahan > 10 THEN LEAVE ulang ; END IF ;
Digunakan untuk LEAVE saat variable pecahan bernilai 11 (yakin ketika kondisi pecahan > 10 bernilai benar). Ketika LEAVE dijalankan, Pengulangan menjadi berakhir. Dengan demikian, pernyataan selanjutnya yang dieksekusi adalah
SELECT CONCAT ( ‘ Nilai akhir pecahan : ‘, pecahan ) ;
Pernyataan REPEAT
Seperti halnya LOOP, pernyataan REAPET juga berguna untuk melakukan pengulangan. Bentuknya:
[awal_label :] REAPET
Daftar pernyataan
UNTIL kondisi_pengulangan
END REAPET [ akhir_label ]
Pada pernyataan ini, bagian daftar_pernyataan akan dieksekusi terus-menerus sampai kondisi_pengulangan benilai benar (TRUE). 
DECLARE pecahan INT DEFAULT 0 ;
Digunakan untuk mendeklarasikan variable local pertama pecahan yang bertipe INT dan diberi nilai awal sebesar 0.
Pernytaan REAPET digunakan untuk menjalankan pernyataan 
SET pecahan = pecahan + 1 ;
Sampai kondisi pecahan > 10 bernilai benar. Kondisi ini tercapai saat pecahan bernilai 11.
Pernyataan WHILE
Pernyataan WHILE juga digunakan untuk melakukan pengulangan terhadap sejumlah pernyataan. Bentuknya adalah:
[ awal_label : ] WHILE kondisi_pengulangan DO
daftar_pernyataan
END WHILE [ akhir_label ]
Berada dengan LOOP atau REAPET, WHILE akan menguji kondisi_pengulangan terlebih dulu.Selama kondisi tersebut bernilai benar, bagian daftar_pernyataan akan dieksekusi secara terus-menerus. Berikut contoh penggunaan pernyataan WHILE.
DELIMITER//
CREATE PROSEDUR tes_while( )
BEGIN
DECLARE pencacah INT;
SET pencacah = 0;
SELECT CONCAT (‘Nilai awal pencacah : ’ , pencacah);
WHILE pencacah < 11 DO
SET pencacah = pencacah + 1;
END WHILE;
SELECT CONCAT (‘Nilai akhir pencacah : ’ , pencacah);
END
//
DELIMITER;
Pernyataan
SET pecahan = pecahan + 1 ;
Dijalankan terus-menerus selama kondisi pecahan < 11 bernilai benar. Pada saat varibel pecahan bernilai 11, kondisi pengulangan menjadi salah. Akibatnya, eksekusi terhadap pernyataan
SET pecahan = pecahan + 1 ;
Tidak dilakukan lagi.
Pernyataan ITERATE
Pernyataan ITERATE memiliki bentuk pemakaian sebagai berikut:
ITERATE label
Penggunaan pernyataan ini hanya berada dalam konstruksi LOOP, REAPET, dan WHILE.Kegunaannya adalah untuk mengarahkan eksekusi menuju ke suatu label. Dengan kata lain, pernyataan ini di gunakan untuk “mengulang kembali”.
Berikut contoh penggunaan ITERATE :
DELIMETER//
CREATE PROSEDUR tes_interate( )
BEGIN
DECLARE pencacah INT DEFAULT 0;
DECLARE hasil CHAR (50);
SET hasil = ‘0’;
Ulang: WHILE pencacah < 11 DO
SET pencacah = pencacah + 1;
IF pecacah > 3 AND pencacah < 7 THEN 
ITERATE ulang;
END IF;
SELECT CONCAT (hasil, ‘-‘, pencacah) 
INTO hasil;
END WHILE;
SELECT hasil;
END
//
DELIMITER;
Pada contoh di atas, mula-mula variable pencacah di isi dengan nol dan hasil dengan string ‘0’.
Pernyataan WHILE di gunakan untuk melakukan pengulangan selama variable pecahan di bawah 10. Di dalam WHILE, mula-mula varibel pecahan dinaikan sebesar satu melalui pernyataan SET. Kemudian, pernyataan IF digunakan untuk menjalankan pernyataan ITERATE ulang sekiranya isi varibel pecahan lebih besar 3 dan kurang dari 7. Dengan demikian, ITERATE dijalankan ketika bernilai 4, 5, dan 6. Pada saa seperti itu, pernyataan
SELECT CONCAT (hasil, ‘_’ , pecahan)
INTO hasil ; 
tidak dijalankan. Itulah sebabnya pada hasil pengujian ITERATE di bawah ini nilai 4, 5, dan 6 tidak muncul.
Mysql> CALL tes_iterate( );