FUNGSI DAN PROSEDUR MYSQL

FUNGSI DAN PROSEDUR

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 dipanggil
 Fungsi tersimpan menghasilkan nilai ketika dipanggil dan tetntu saja seperti fungsi biasa bisa dipanggil di dalam suatu pernyataan (misalnya dalam SELECT)
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.


Memahami Perintah-Perintah untuk Rutin
Terkait dengan pembuatan rutin (prosedur maupun fungsi tersimpan), ada beberapa perintah yang perlu dipahami.
• 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( );
+---------------------------+
| hasil |
+---------------------------+
| 0 – 1 – 2 – 3 - 7- 8 – 9 – 10 – 11 |
+---------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Mysql>
Menangani Handler
Handler berguna untuk mengatur aliran eksekusi ketika suatu kondisi yang berhubungan dengan error terjadi. Sebagai gambaran, sekiranya terdapat kunci yang kembar ketika terjadi penyisipan data, aliran eksekusi dapat diatur agar segera dihentikan atau dilanjutkan ke pernyataan berikutnya.
Untuk mendapatkan gambaran yang jelas tentang penanganan handler, pertama-tama berikan perintah sebagai berikut :
Use test
CREATE TABLE tmp (kunci CHAR(1) NOT NULL PRIMARY KEY);
Perintah diatas digunakan untuk menciptakan table bernama tmp pada data base test. Dalam hal ini table tmp hanya mengandung sebuah kolom bernama kunci yang bertindak sebagai kunci primer.
Selanjutnya, cobalah memberikan dua perintah untuk menyisipkan data ‘A’ seperti berikut :
Mysql>INSERT INTO tmp VALUES (‘A’);
Query OK, 1 row affected (0.09 sec)
Mysql> INSERT INTO tmp VALUES (‘A’);
ERROR 1062 (23000): Duplicate entry ‘A’ for key 1
Mysql>
Pada penyisipan data kedua terjadi kesalahan mengingat nilai ‘A’ sudah ada. Perhatikan bahwa kesalahan karena nilai yang kembar ini memiliki identitas SQLSTATE berupa 23000. kita akan mencoba menangani kesalahan ini melalui penanganan handler bernama CONTINUE.
Selanjutnya, cobalah menuliskan sederetan perintah didepan kedalam prosedur tersimpan, seperti berikut :
DELIMITER //
CREATE PROCEDURE tes_insert()
BEGIN
DELETE FROM tmp;
SET @kode = 1;
INSERT INTO tmp VALUES (‘A’);
SET @kode = 2;
INSERT INTO tmp VALUES (‘A’);
SET @kode = 3;
END //
DELIMITER;
Pada table diatas, DELETE FROM tmp digunakan untuk mengosongkan table tmp. Adapun perintah seperti :
SET @kode = 1;
Digunakan untuk menghasilkan ke variable global bernama kode dengan suatu nilai. Nilai ini nanti akan diperiksa setelah pemanggilan prosedur tersebut.
Setelah itu, cobalah untuk memanggil prosedur tersimpan tes_insert dan menampilkan isi variable kode. Contohnya sebagai berikut :
Mysql>CALL tes_insert();
ERROR 1062 (23000) : duplicate entry ‘A’ for key 1
Mysql> SELECT @kode;
+---------------+
| @kode |
+---------------+
| 2 |
+---------------+
1 row in set ( 0.01 sec)
Mysql>
Perhatikan bahwa isi variable kode adalah 2. hal ini menunjukan bahwa
Set @kode = 3;
tidak pernah dijalankan setelah perintah yang mengalami kesaahan yaitu
INSERT INTO tmp VALUES (‘A’);
yang kedua dijalankan.
Sekarang, hapus procedure tes_insert dengan memberikan perintah :
DROP PROCEURE tes_insert;
lalu tuliskan kode seperti berikut.
DELIMITER //
CREATE PROCEDURE tes_insert ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
SET @dummy = 0;
DELETE FROM tmp;
SET @kode = 1;
INSERT INTO tmp VALUES (‘A’);
SET @kode = 2;
INSERT INTO tmp VALUES (‘A’);
SET @kode = 3;
END //
DELIMITER;
tambahkan kode :
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
SET @dummy = 0;
Dimaksudkan untuk menangani handler SQLSTATE dengan kode 23000 (yaitu kunci kembar). Dalam hal ini, variable global dummy akan diisi dengan nol sekiranya terjadi duplikasi data.
Setelah kode diatas anda berikan, ujilah seperti berikut :
Mysql> SET @dummy = 1;
Query OK, 0 rows affected ( 0.00 sec )
Mysql> CALL tes_insert ();
Query OK, 0 rows affected ( 0.14 sec )
Mysql> SELECT @dummy;
+---------------+
| @dummy |
+---------------+
| 0 |
+---------------+
1 row in set ( 0.00 sec )
Mysql> SELECT @kode;
+---------------+
| @kode |
+---------------+
| 3 |
+---------------+
1 row in set ( 0.00 sec )
Pertama-tama, perhatikan bahwa sebelum pemanggilan prosedur tes_insert, variable dummy diisi dengan 1, tetapi setelah pemanggilan prosedur tersebut berisi 0. nilai 0 ini berkat pengaturan pada pendevinisian
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
SET @dummy = 0;
yang akan membuat variable global dummy diisi dengan 0 kalau ada kesalahan dengan kode 23000. yang menarik lagi berkat penanganan handler CONTINUE, pernyataan yang terletak sesudah pernyataan yang mengalami kesalahan akan tetap dijalankan. Itulah sebabnya, variable kode berisi 3 setelah pemanggilan test_insert Karena perintah
SET @kode = 3;
tetap dieksekusi.
Penanganan Kursor
Penanganan kursor tersedia untuk prosedur maupun fungsi tersimpan. Kursor memungkinkan anda membuat kode yang membaca baris-baris hasil suatu SELECT secara sekuensial (berurutan). Sebagai contoh, anda bisa menyalin setiap baris dari suatu hasil SELECT dan kemudian menyalinnya ke table lain.
Pendeklarasian kursor dilakukan memalalui pernyataan dengan bentuk sebagai berikut
DECLARE nama_kursor CURSOR
FOR suatu_SELECT;
Terkait dengan kursor terdapat pernyataan OPEN, FETCH, dan CLOSE. Pernyataan OPEN digunakan untuk membaca kursor. Adapun pernyataan FETCH memiliki bentuk sebagai berikut :
FETCH nama_kursor INTO nama_variabel [ nama_variabel]
Sebuah baris akan dibaca dari nama_kursor dan kemudian hasilnya diberikan kepada variable_variabel yang disebut dalam INTO. Selain itu, kursor perlu ditutup dengan menggunakan :
CLOSE nama_kursor;
Untuk memperhatikan kursor, pertama-tama berikan perintah berikut :
USE pegawai
CREATE TABLE nipnama (nip CHAR (5), nama CHAR (25) );
Selanjutnya, buatlah prosedur tersimpan seperti berikut :
DELIMITER / /
CREATE PROCEDURE tes_kursor ()
BEGIN
DECLARE nomor_peg CHAR (5);
DECLARE nama_peg CHAR (25);
DECLARE kursor CURSOR FOR
SELECT nip, nama FROM infoprib;
DELETE FROM nipnama;
Open KURSOR;
REPEAT
FETCH kursor INTO nomor_peg, nama_peg;
INSERT INTO nipnama
VALUES (nomor_peg, nama_peg);
UNTIL FALSE END REPEAT;
CLOSE kursor;
END //
DELIMITER ;
Di dalam prosedur tersimpan di atas, mula-mula variable no_peg dan nama_peg dideklarasikan sebagai variable local. Selanjutnya, kursor di deklarasikan sebagai kursor untuk pernyataan
SELECT nip, nama FROM infobrib;
Pernyataan DELETE digunakan untuk mengkosongkan table nipnama.
Pernyataan
OPEN kursor;
Digunakan untuk membuka kursor.
Pernyataan REPEAT dipakai untuk melakukan pengulangan terhadap pernyataan-pernyataan yang ada pada REPEAT-UNTIL sampai kondisi bernilai FALSE.
Pernyataan PETCH dimaksudkan untuk mengambil data pada kursor ke variable nomor_peg dan nama_peg.
Selanjutnya, INSERT digunakan untuk menyisipkan data yang terdapat pada variable nomor_peg dan nama_peg ke table nipnama.
Pernyataan
CLOSE kursor;
Digunakan untuk menutup kursor.
Setelah menuliskan kode didepan, cobalah untuk memangil prosedur tes_kursor. Contoh diperlihatkan pada gambar berikut.
Mysql> CALL test kursor();
ERROR 1329 (02000): No data to FETCH
Mysql>
Walaupun ada pesan kesalahan, Anda bisa melihat isi table nipnama. Adakah isinya? Ya, ada isinya dan sesuai dengan isi table infoprib. Lalu mengapa ada kesalahan? Penyebabanya adalah ketika tidak ada garis lagi, FETCH akan membuat pesan kesalahan ditampilkan dan eksekusi dihentikan.
Untuk menghilangkan kesalahan seperti itu, Anda perlu memodifikasi kode menjadi seperi berikut.
DELIMITER//
CREATE PROSEDUR tes_kursor ()
BEGIN
DECLARE nomor_peg CHAR (5);
DECLARE nama_peg CHAR (25);
DECLARE habis BOOLEAN DEFAULT FALSE;

DECLARE kursor CURSOR FOR
SELECT nip, nama FROM infoprib;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’
SET habis = TRUE;
DELETE FROM nipnama;

OPEN kursor;
REPEAT
PETCH kursor INTO nomor_peg, nama_peg;
IF habis = FALSE THEN
INSERT INTO nipnama
VALUES (nomor_peg, nama-peg);
END IF;
UNTIL habis END REPEAT;
CLOSE kursor;
END
//
Yang ditulis tebal adalah yang perlu Anda tambahkan modifikasi.
Pernyataan
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’
SET habis = TRUE;
Digunakan untuk mengatur agar variable local habis diisi dengan TRUE saat FETCH dieksekusi dan data tidak ada lagi.
Pernyataan IF digunakan untuk memastikan bahwa penyisipan data hanya dilakukan kalau isi variable habis bernilai FALSE (yang artinya data masih ada).
Lebih Lanjut Mengenal IN, OUT, dan INOUT
Setiap parameter pada prosedur tersimpan secara opsional bisa diawali IN, OUT, atau INOUT. IN berarti parameter sebagai masukan bagi prosedur, OUT berarti parameter sebagai keluaran, dan INOUT berartri sebagai masukan dan sekaligus keluaran.
Untuk mempraktekan OUT dan INOUT pada prosedur, berikan perintah berikut terlebih dahulu:
USE test;
Contoh berikut menunjukkan penggunaan OUT.
DELIMITER//
CREATE PROSEDUR tes_out (OUT jumlah INT)
BEGIN
SELECT COUNT (*) INTO jumlah FROM bagian;
END
//
DELIMITER;
Perhatikan bahwa parameter jumlah dinyatakan dengan OUT. Di dalam BEGIN END, pernyataan SELECT digunakan untuk memperoleh jumlah baris pada table bagian dan hasilnya diberikan ke jumlah.
Sekarang, cobalah menguji prosedur tersimpan test_out. Contoh sebagai berikut :
Mysql> SET @nilai = 0;
Query OK, 0 rows affected (0. 05 sec)
Mysql> CALL tes_out (@nilai);
Query OK, 0 rows affected (0. 69 sec)
Mysql> SELECT @nilai;
+-----------+
| @nilai |
+-----------+
| 6 |
+-----------+
1 row in set (0.00 sec)
Mysql>
Sekarang akan ditunjukan perbedaan antara OUT dan INOUT. Untuk mempraktikkan hal ini, berikan perintah berikut terlebih dahulu:
USE test
Selanjutnya, tuliskan kode seperti berikut.
DELIMITER//
CREATE PROSEDUR tes_inout (OUT x INT)
BEGIN
SET x = x + 2;
END
//
DELIMITER;
Hasil pemanggilan prosedur tersimpan tes_inout ditujukan prosedur dibawah ini.
Mysql> SET @a = 10;
Query OK, 0 rows affected (0. 06 sec)
Mysql> CALL tes_inout (@a);
Query OK, 0 rows affected (0. 05 sec)
Mysql> SELECT @a;
+-----------+
| @a |
+-----------+
|NULL |
+-----------+
1 row in set (0.00 sec)
Mysql>
Dari hasil di atas terlihat bahwa ternyata variable global a yang dilewatkan saat memanggil tes_inout malah tidak dikenal sesudah pemanggilan prosedur tersimpan. Mengapa? Karena x berkedudukan sebagai OUT sehingga tidak punya nilai awal. Oleh karena itu,
SET x = x + 2;
Akan menghasilkan nilai NULL mengingat pada awalnya x juga bernilai NULL.
Untuk mencoba efek INOUT, berikan perintah berikut terlebih dulu:
DROP PROCEDURE tes_inout;
Sekarang, cobalah berikan kode berikut ( dengan mengganti OUT menjadi INOUT terhadap contoh sebelumnya).
DELIMITER//
CREATE PROSEDUR tes_inout (INOUT x INT)
BEGIN
SET x = x + 2;
END
//
DELIMITER;
Dengan cara seperti itu, nilai awal x bergantung pada argument yang diberikan ketika prosedur tersimpan dipanggil.
Contoh berikut memberikan hasil yang berbeda dengan contoh sebelumnya.
Mysql> SET @a = 10;
Query OK, 0 rows affected (0. 06 sec)

Mysql> CALL tes_inout (@a);
Query OK, 0 rows affected (0. 05 sec)

Mysql> SELECT @a;
+-----------+
| @a |
+-----------+
|12 |
+-----------+
1 row in set (0.00 sec)
Mysql>
Tampak bahwa variable a yang semula bernilai 10 menjadi 12 setelah pemanggilan prosedur tersimpan tes_inout.
Prosedur Tersimpan dan Pernyataan PREPARE
Khusus untuk prosedur tersimpan (dan tidak berlaku untuk fungsi tersimpan), pernyataan PREPARE bisa dilibatkan. Pernyataan PREPARE dapat digunakan untuk membentuk pernyataan secara dinamis. Contoh pemakaian dapat dilihat pada contoh berikut:
USE pegawai
DELIMITER//
CREATE PROSEDUR tes_dinamis (nama_file CHAR (20))
BEGIN
DECLARE jumlah INT;
SELECT CONCAT(‘SELECT * FROM’, nama_file) INTO @perintah;
PREPARE peroleh_data FROM @perintah;
EXECUTE peroleh_data;
DEALLOCATE PREPARE peroleh_data;
END
//
DELIMITER;
Pada kode di atas, nama_file menyatakan nama table.
Pernyataan
SELECT CONCAT (‘SELECT * FROM’, nama_file) INTO @perintah;
Digunakan untuk membentuk tulisan ‘SELECT * FROM’ yang digabungkan dengan isi variable nama_file. Hasilnya diberikan ke variable global perintah.
Lalu, pernyataan PREPARE menyiapkan string yang terdapat pada variable perintah dan dibentuk menjadi perintah dalam peroleh_data. Selanjutnya, peroleh_data eksekusi melalui EXECUTE. Hasilnya ditunjukkan dibawah ini. Perlu diketahui, DEALLOCATE digunakan untuk menghapus memori yang digunakan pada pembentukan pernyataan PREPA
Mysql> CALL tes-dinamis (‘infoprib’);
+----------+-------------------------+-----------+---------------------+-----------+
| nip | nama | kota | tgl_lahir | sex |
+----------+-------------------------+-----------+---------------------+-----------+
| 12345 | Dian Permana | Yogya | 1969 - 03 – 10 | L |
| 12346 | Tamara Pebrianti | Klaten | 1980 - 01 – 01 | W |
| 12347 | Dara Permatasari | NULL | 1969 - 03 – 10 | W |
| 12348 | Dita Nurafni | Klaten | 1982 - 09 – 01 | W |
| 12349 | Dhani Akbar | Yogya | 1981- 03 – 13 | L |
| 12350 | Aan Setiawan | Yogya | 1981 - 05 – 07 | L |
| 12351 | Raul Stitompul | Bantul | 1979 - 04 – 05 | L |
| 12352 | Rahmanda | Yogya | 1982 - 08 – 04 | W |
| 12353 | Niken | Klaten | 1983 - 10 – 16 | W |
| 12354 | Inneke Safitri | Bantul | 1983 - 12 – 30 | W |
+----------+-------------------------+-----------+---------------------+------------+
10 rows in set (1. 27 sec)
Query OK, 0 rows affected (1.27 sec)
Mysql>


Batasan untuk Prosedur dan Fungsi Tersimpan
Sejauh ini ada beberapa batasan pada prosedur dan fungsi tersimpan. Pernyataan-pernyataan berikut tidak boleh digunakan pada prosedur dan fungsi tersimpan:
 CHECK TABLES
 LOCK TABLE dan UNLOCK TABLES
 LOAD DATA dan LOAD TABLE
 OPTIMIZE TABLE

Khususnya untuk fungsi tersimpan tidak boleh menggunakan pernyataan SQL dinamis PREPARE dan kerabatnya (EXECUTE dan DEALLOCATE PREPARE), SELECT tanpa INTO, FLUSH, dan perintah COMMIT serta ROLLBACK.