Contoh 3.1: Membuat suatu store procedure supaya menghapus semua pertandingan pemain berdasarkan nomor pemain yang ditentukan

MODUL 3 SISTEM BASIS DATA PROCEDURAL DATABASE OBJECTS : STORE PROCEDURE E-mail Author :

[email protected]

1. Pendahuluan Dalam pertemuan sekarang kita akan membahas mengenai objek prosedur yang terdapat di database, biasanya dipanggil dengan sebuatan store procedure atau prosedur database. Kita mulai dengan apa yang dimaksud dengan /definisi store procedure. Store procedure ialah Penyimpanan suatu kumpulan kode (procedure) yang terdiri dari deklarasi dan prosedur perintah SQL dalam katalog database dan dapat diaktifkan/ digunakan dengan cara memanggil melalui suatu program, suatu trigger, dan store procedure lainnya. Jadi, store procedure merupakan suatu kumpulan kode. Kode ini bisa terdiri dari deklarasi perintah SQL , seperti CREATE, UPDATE, dan SELECT, mungkin saja ditambahkan perintah‐perintah prosedural, seperti IF‐THEN‐ELSE, WHILE‐ DO. Yang mana kode akan disimpan dan buat menjadi suatu Strore Procedure, oleh karena itu, kode‐kode yang dibuat bukan bagian dari program, melainkan, disimpan dalam suatu katalog. Pengaktifan/ pemanggilan store procedure dapat disamakan dengan pemanggilan prosedur dalam bahasa prosedural. Dalam memanggil suatu store procedure, pertamakali kita membuat suatu perintah SQL baru. Dimana pemanggilan store procedure, anda bisa menetukan parameter input ataupun output. Sebagai tamahan dalam pendefisian store procedure. Pemanggilan store procedure dapat dilakukan dalam store procedure lainnya. Kita coba ilustrasikan dan praktekan store procedure dengan contoh yang sederhana.

Contoh 3.1: Membuat suatu store procedure supaya menghapus semua pertandingan pemain berdasarkan nomor pemain yang ditentukan. CREATE PROCEDURE `DELETE_MATCHES` (IN `P_PLAYERNO` INTEGER) BEGIN DELETE FROM `MATCHES` WHERE `PLAYERNO` = `P_PLAYERNO`; END

Setiap store procedure terdiri dari 3 bagian, yaitu sebuah daftar paramenter, sebuah Body, dan sebuah nama. Dalam prosedur diatas hanya terdapat satu parameter, yaitu P_PLAYERNO (Nomor pemain). Kata IN dalam parameter menyatakan parameter input. Nilai dalam parameter tersebut akan digunakan dalam prosedur, kecuali setelah procedur tersebut dieksekusi, variabel yang digunakan saat pemanggilan tetap tidak mengalami perubahan. Penempatan/ penentuan body prosedur, dilakukan antara Keyword BEGIN dan END. dalam contoh diatas, body prosedur masih mudah karena masih terdiri dari satu perintah DELETE. Sesuatu yang baru dalam perintah diatas ialah penggunaan parameter P_PLAYERNO. Nama prosedur sama seperti pada database haruslah unik , Bisa saja namanya sama seperti nama table‐table yang terdapat pada database yang besangkutan. Hasil dari pembuatan prosedur diatas ialah tidak mengeksekusi perintah DELETE, syntax yang terdapat dalam perintah diatas hanya membandingkan, jika syntax tersebut telah sesuai dengan yang disimpan dalam catalog. Untuk mengaktifkan suatu store procedure, dengan menggunakan perintah CALL. Contoh 3.2: Membuang semua pertandingan pemain dengan nomor 8 menggunakan prosedur DELETE_MATCHES. CALL `DELETE_MATCHES` (8);

Gambar 3.1 memperlihatkan bagaimana sebuah store procedure diproses. Pada blok kiri mempresentasikan dimana program suatu prosedur. Blok tengah mempresentasikan database server, dan yang kanan mempresentasikan

Procedural Database Objects : Store Procedure

| 1


[email protected]

database dan katalog. Proses awal dimulai dengan pemanggilan prosedur oleh program(Langkah 1). Database server menerima pemanggilan tersebut dan mencari procedure yang sesuai didalam catalog(Step 2). Selanjutnya, prosedure tersebut dieksekusi(langkah 3). Prosedur tersebut mehasilkan suatu menambahkan baris baru, atau dalam kasus yang contohkan, kita membuang suatu baris. Ketika prosedur berhasil sudah selesai, maka prosedur akan mengembalikan suatu nilai/hasil.

Gambar 3. 1 Langkah‐langkah pemprosesan Store Procedure

2. Parameter Dalam Sebuah Store Procedure Suatu Store Procedure memiliki kosong, 1, atau lebih parameter. Melalui parameter suatu prosedur dapat berkomunikasi dengan dunia luar. Ada tiga tipe parameter yang dapat digunakan. Dengan parameter input, data/ nilai darai parameter inputan dapat digunakan dalam strore prosedur. Sebagi contoh dapat dilihat pada contoh 3.1 yang berisikan satu parameter input, paramenter tersebut menujukan nomor permain mana dalam pertadingan yang akan dibuang. Store prosedur dapat menggunakan parameter output untuk menampung hasil yang akan dikembalikan/ nilai yang didapatkan dalam proses yang terdapat di dalam prosedur. Sebagai contoh kita lihat contoh kasus 3.3, store procedure ini dibuat untuk mencari nama pemain, hasil dari pencarian tersbut akan disimpan dalam parameter out. Tipe ketiga ialah parameter input/output parameter. Sesuai dengan namanya, parameter ini dapat digunakan sama halnya dengan parameter input atau output. Contoh 3.3: Membuat suatu strore procedure untuk mencari nama pemain dan nama pemain tersebut disimpan dalam parameter output. CREATE PROCEDURE `SELECT_PLAYERNAME`(OUT `P_NAME` VARCHAR(60), IN `P_PLAYERNO` INT) BEGIN SELECT `NAME` INTO `P_NAME` FROM `PLAYERS` WHERE `PLAYERNO` = `P_PLAYERNO`; END

Contoh 3.4: Membuat suatu strore procedure supaya mengenerate nomor sesuai dengan algoritma FIBONACCI. CREATE PROCEDURE `FIBONACCI` (INOUT `NUMBER1` INTEGER, INOUT `NUMBER2` INTEGER, INOUT `NUMBER3` INTEGER) BEGIN SET `NUMBER3` = `NUMBER1` + `NUMBER2`; IF `NUMBER3` > 10000 THEN SET `NUMBER3` = `NUMBER3` - 10000; END IF; SET `NUMBER1` = `NUMBER2`; SET `NUMBER2` = `NUMBER3`;


| 2


[email protected]

END;

Sebuah store procedure bisa saja tak membutuhkan suatu parameter, akan tetapi tanda kurung pembuka dan penutup haruslah ada. Pembuatan nama untuk parameter tidak boleh sama dengan nama kolom. Jika kita merubah P_PLAYERNO dalam contoh kasus 3.1 dengan PLAYERNO, MySQL tidak akan menghasilkan pesan error, perintah DELETE akan mengagap PLAYERNO yang kedua diaggap sebagai nama kolom, bukan nama parameter. Alhasil, pemanggilan prosedur, store procedure akan menghapus semua pemain. 3. Body dalam sebuah Store Procedure Body dalam sebuah store procedure berisikan semua perintah yang akan dieksekusi ketika store procedure itu di panggil. Suatu body diawali dengan keyword BEGIN dan diakhiri dengan keyword END. Diantara, semua perintah kita dapat mentetukan tipe dari perintah tersebut. Kita sudah mempelajari perintah SQL pada pertemuan sebelumnya(seperti, perintah DML, DCL, dan DDL). Dan strore procedure dapat menggunakan semua itu. Store procedure juga dapat menggunakan perintah dalam bahasa pemograman lainnya, seperti IF‐THEN‐ELSE, WHILE‐DO, REPEAT‐UNTIL. Tambahannya, kita dapat menyimpan hasil dari perintah SELECT dengan menggunakan perintah khusus yaitu perintah INTO, dapat mendeklarasikan variabel lokal, dan dapat menentukan nilainnya. Dengan sebuah blok BEGIN‐END. Suatu perintah dapat dikelompokan menjadi sebuah perintah. Dalam kesempatan lain blok akan dipanggila dengan perintah COUMPOUND. Dalam faktanya, body store prosedur merupakan suatu blok BEGIN‐END(perintah COUMPOUND). Blok bisa saja bersarang, dengan kata lain kita dapat membuat suatu subblok dalam blok BEGIN‐END. Dan hal tersebut diperbolehkan dalam store procedure : BEGIN BEGIN BEGIN END; END; END;

Catatan untuk setiap perintah yang termasuk didialam blok BEGIN‐END harus diakhiri dengan semicolon. Itu dibutukan untuk menunjukan akhir body dalam store procedure. Kita dapat menambahkan suatu Label untuk blok BEGIN‐END. Contohnya. BLOK1: BEGIN BLOK2: BEGIN BLOK3: BEGIN END BLOK1; END BLOK2; END BLOK3;

Ada dua keuntungan yang didapatkan saat menggunakan Label. Pertama, pelabelan dapat mempermudah menentukan sampai sejauh mana BEGIN dan END‐nya. Khsusunya saat dalam suatu store procedure terdapat banyak prosedur. Kedua, beberapa perintah SQL seperti LEAVE, dan ITERATION(Pengulangan) membutuhkan hal tersebut. Mengenai perintah SQL LEAVE dan ITERATION akan dibahas dalam kesempatan lain. 4. Variabel lokal Dalam sebuah store procedure, variabel lokal dapat dideklarasikan. Variabel tersebut dapat digunakan untuk penyimpanan sementara dari hasil yang dihasilkan. Jika kita membutuhkan suatu variabel lokal dalam store procedure. Langkah awalnya mendeklarasikannya dengan menggunakan perintah DECLARE. Apabila anda sudah mempelajari


| 3


[email protected]

pemograman dalam bahasa PHP, pendekalarsian variabel lokal mirip dengan pendeklarasian suatu variabel pada pemograman dalam bahasa PHP. Dengan mendeklarasikannya terlebih dahulu. Kita dapat menetukan tipe data dari variabel yang bersangkutan dan menginisialisasinya sesuai kebuuhan kita. Tipe data yang dapat didukung, mungkin saja sama dengan tipe data saat kita menggunakan dalam perintah CREATE TABLE Contoh 3.5: Mendeklarasikan sebuah variabel numeric dan alpanumerik. DECLARE `NUM1` DECIMAL(7,2); DECLARE ÀLPHA` VARCHAR(20);

Banyak variabel boleh saja memiliki tipe yang sama dalam dengan satu kali pendeklarasi dengan menggunkan perintah DECLARE. Contoh 3.6: Mendeklarasikan dua variabel integer. DECLARE `NUMBER1`, `NUMBER2 ` INTEGER;

Penambahan sebuah ekpresi DEFAULT memberikan suatu nilai awal(inisialisasi) bagi variabel. Contoh 3.7: Buat suatu store procedure dimana didalamnya terdapat inisialisi sebuah nilai yang disimpan dalam variabel lokal. Selajutnya, anda panggil strore procedure tersebut. CREATE PROCEDURE `TEST` (OUT `NUMBER1` INTEGER) BEGIN DECLARE `NUMBER2` INTEGER DEFAULT 100; SET `NUMBER1` = `NUMBER2`; END; CALL `TEST` (@NUMBER); SELECT @NUMBER;

Hasilnya: @NUMBER ‐‐‐‐‐‐‐ 100 Nilai default untuk ekpresi DEFAULT tidak hanya terbahas pada literal saja, tetapi mungkin saja berupa ekspresi coumpound, subquery. Contoh 3.8: Buat sebuah store procedure dimana inisialisasi variabel lokal diambil dari nomor pemain yang terdapat di tabel PLATERS. CREATE PROCEDURE `TEST` (OUT `NUMBER1` INTEGER) BEGIN DECLARE `NUMBER2` INTEGER; DEFAULT (SELECT COUNT(*) FROM `PLAYERS`); SET `NUMBER1` = `NUMBER2`; END;

Pendeklarasian variabel dapat dilakukan seperti dalam blok BEGIN‐END. Setelah dideklarasikan, suatu variabel dapat digunakan pada blok yang bersangkutan, termasuk semua subblok dari blok tersebut. Variabel yang dideklarasikan dalam suatu blok tidak akan bisa/ tidak akan dikenali dalam blok lainnya.


| 4


[email protected]

B1 : BEGIN DECLARE V1 INTEGER; B2 : BEGIN DECLARE V2 INTEGER; SET V2 = 1; SET V1 = V2; END B2; B3 : BEGIN SET V1 = V2; END B3; SET V2 = 100; END B1;

Lihat pendeklarasian variabel diatas, variabel dengan nama V1 akan bisa saja digunakan dalam semua blok. V2, hanya bisa digunakan pada sublok pertama atau yang dikelan dengan B2. Dalam subblok selanjutnya, variabel yang digunakan tidak kitehaui atau dikenali, perintah SET pada blok ini akan ditolak. Begitupun dengan perintah SET yang terakhir tidak akan bisa diterima/ ditolak. Jangan terkecoh variabel lokal dan user variabel pada dasarnya kedua variabel ini berbeda, dalam modul pertama yang membahas mengenai common element. Kita sudah bahas mengenai user variabel, perbedaan yang pertama adalah pendefinisian nama variabel variabel lokal tidak menggunakan tanda/lambang @ didepan nama variabel. Perbedaan lainnya user variabel akan selalu ada selama session masih digunakan. Variabel lokal akan segera menghilang setelah memproses blok BEGIN‐END yang mana setelah pendeklarasinya selesai. User variabel dapat digunakan didalam dan diluar store procedure. Sedangkan variabel lokal tidak bisa digunakan diluar store procedure. Catatan bahwa MySQL tidak mendukung array sebagai variabel lokal. 5. Perintah SET Dalam modul pertama yang membahas mengenai common element, Kita sudah bahas bagiaman kita memasukan suatu nilai untuk user variabel dengan menggunkan perintah SET. Perintah yang sama dapat digunakan untuk memasukan suatu nilai untuk variabel lokal. Dalam bagian‐bagian sebelumnya diperlihatkan beberapa contoh dengan menggunakan perintah SET. Mari kita perikasa contoh dibawah ini : SET VAR1 = 1; SET VAR1 := 1; SET VAR1 = 1, VAR2 = VAR1;

Dalam contoh terakhir diatas, pertama suatu nilai disimpan dalam variabel VAR1, dan selajutnya nilai tersebut disimpan pada variabel VAR2 memalui VAR1. 6. Perintah FLOW‐CONTROLS Kita sudah tahu bahwa perintah prosedural dapat digunakan dalam body store procedure. PENDEFINISIAN ::= | | <while statement> | | | | ::= IF THEN <statement list> [ ELSEIF THEN <statement list> ]... [ ELSE <statement list> ] END IF


| 5


[email protected]

::= { CASE <scalar expression> WHEN <scalar expression> THEN <statement list> [ WHEN <scalar expression> THEN <statement list> ]... [ ELSE <statement list> ] END CASE } | { CASE WHEN THEN <statement list> [ WHEN THEN <statement list> ]... [ ELSE <statement list> END CASE } <while statement> ::= [ : WHILE DO <statement list> END WHILE [ ] ::= [ : ] REPEAT <statement list> UNTIL END REPEAT ::= [ : ] LOOP <statement list> END LOOP [ ] ::= LEAVE ::= ITERATE <statement list> ::= { <statement in body> ; }... ::= [ : ] BEGIN <statement list> END [ ] ::=

Kita mulai dengan contoh perintah IF. Contoh 3.9: Buat sebuah store procedure menetukan nilai tertinggi antara 2 variabel parameter. CREATE PROCEDURE `DIFFERENCE` (IN `P1` INTEGER, IN `P2` INTEGER, OUT `P3` INTEGER) BEGIN IF `P1` > `P2` THEN SET `P3` = 1; ELSEIF `P1` = `P2` THEN SET `P3` = 2; ELSE SET `P3` = 3; END;

Contoh 3.10: Buat sebuah store procedure Create a stored yang menunjukan jumlah baris terbanyak pada tabel PLAYERS atau PENALTIES. CREATE PROCEDURE `LARGEST` (OUT `T` CHAR(10)) BEGIN IF (SELECT COUNT(*) FROM `PLAYERS`) > (SELECT COUNT(*) FROM `PENALTIES`) THEN SET T = 'PLAYERS'; ELSEIF (SELECT COUNT(*) FROM `PLAYERS`) = (SELECT COUNT(*) FROM `PENALTIES`) THEN SET T = 'EQUAL'; ELSE SET T = 'PENALTIES'; END IF; END;

Perintah CASE bisa saja dibuat seperti menetukan kompleksitas IF‐THEN‐ELSE. Perintah IF sudah dicontohkakn dalam contoh 3.9, sebagai contoh untuk perintah CASE, mari kita rubah dari contoh 3.9 CREATE PROCEDURE `DIFFERENCE2` (IN `P1` INTEGER, IN `P2` INTEGER,


| 6


[email protected]

OUT `P3` INTEGER) BEGIN CASE WHEN P1 > P2 THEN SET P3 = 1; WHEN P1 = P2 THEN SET P3 = 2; ELSE SET P3 = 3; END CASE; END;

MySQL mendukung pembuatan tiga perintah pengulangan : perintah REPEAT, WHILE, dan LOOP.

Contoh 3.11: Membuat sebuah store procedure untuk menghitung tahun, bulan dan hari diantara dua tanggal. CREATE PROCEDURE ÀGE` (IN `START_DATE` DATE, IN ÈND_DATE` DATE, OUT `YEARS` INTEGER, OUT `MONTHS` INTEGER, OUT `DAYS` INTEGER) BEGIN DECLARE NEXT_DATE, PREVIOUS_DATE DATE; SET YEARS = 0; SET PREVIOUS_DATE = START_DATE; SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR; WHILE NEXT_DATE < END_DATE DO SET YEARS = YEARS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR; END WHILE; SET MONTHS = 0; SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH; WHILE NEXT_DATE < END_DATE DO SET MONTHS = MONTHS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH; END WHILE; SET DAYS = 0; SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY; WHILE NEXT_DATE <= END_DATE DO SET DAYS = DAYS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY; END WHILE; END;

Ikuti pengeksekusian store procedure atas sebagai berikut: SET @START = '1991-01-12'; SET @END = '1999-07-09'; CALL ÀGE` (@START, @END, @YEAR, @MONTH, @DAY); SELECT @START, @END, @YEAR, @MONTH, @DAY;

Dengan perintah WHILE, pengecekan akan dilakukan pertama kali apakah kondisi yang ditentukan sudah sesuai, apabila sesuai barulah perintah yang terdapat didalam perintah WHILE akan dieksekusi. Dengan perintah REPEAT perintah yang terdapat didalamnya akan dieksekusi terlebih dahulu, baru pemeriksaan kondisi, apakah kondisi yang ditentukan telah sesuai atau tidak. Contoh kasus pengulangan menggunakan perintah WHILE pada contoh 3.11 kita modifikasi menggunkan perintah REPEAT : SET YEARS = -1; SET NEXT_DATE = START_DATE; REPEAT SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 YEAR; SET YEARS = YEARS + 1; UNTIL NEXT_DATE > END_DATE END REPEAT;

Sebelum membahas lebih lanjut perintah‐perintah pengulangan, kita bahas terlebih dahulu mengenai perintah LEAVE. dimana bisa menghentikan suatu proses yang terjadi dalam blok BEGIN‐END. Tetapi, blok yang bersangkutan harus menggunakan label.


| 7


[email protected]

Contoh 3.12: Buat sebuah strore procedure dimana didalam sebuah blok dapat keluar sebelum semua perintah dieksekusi. CREATE PROCEDURE `SMALL_EXIT` (OUT P1 INTEGER, OUT P2 INTEGER) BEGIN SET P1 = 1; SET P2 = 1; BLOCK1 : BEGIN LEAVE BLOCK1; SET P2 = 3; END; SET P1 = 4; END;

Jika kita mengeksekusi store procedure diatas, hasil yang didapatkan oleh variabel P2 sama dengan 1, dan nilai untuk variabel P1 sama dengan 4, perintah SET setelah perintah LEAVE tidak akan dieksekusi, akan tetapi perintah SET setelah blok BLOCK1 akan diekesekusi. Dengan menggunakan perintah LOOP, kita tidak usah menggunakan kondisi untuk menjalakan perintah yang ada didalmnya, kita bisa gunakan perintah LEAVE untuk keluar dari perulangan yang terjadi. Contoh kasus pengulangan menggunakan perintah WHILE pada contoh 3.11 kita modifikasi menggunkan perintah LOOP: SET YEARS = 0; SET PREVIOUS_DATE = START_DATE; SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR; YEARS_LOOP: LOOP IF NEXT_DATE > END_DATE THEN LEAVE YEARS_LOOP; END IF; SET YEARS = YEARS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR; END LOOP YEARS_LOOP;

Contoh 3.13: Buat sebuah store procedure yang tidak memberikan respon selama beberapa detik. CREATE PROCEDURE `WAIT` (IN `WAIT_SECONDS` INTEGER) BEGIN DECLARE END_TIME INTEGER; DEFAULT NOW() + INTERVAL WAIT_SECONDS SECOND; WAIT_LOOP: LOOP IF NOW() > END_TIME THEN LEAVE WAIT_LOOP; END IF; END LOOP WAIT_LOOP; END;

Perintah ITERATE adalah kebalikan dari perintah LEAVE, dimana perintah LEAVE digunakan untuk keluar dari suatu pengulangan, sedangkan ITERATE digunakan untuk mengulangi pengulangan. Contoh 3.14: Buat suatu store procedure dengan menggunakan perintah ITERATE. CREATE PROCEDURE ÀGAIN` (OUT `RESULT` INTEGER) BEGIN DECLARE COUNTER INTEGER DEFAULT 1; SET RESULT = 0; LOOP1: WHILE COUNTER <= 1000 DO SET COUNTER = COUNTER + 1; IF COUNTER > 100 THEN LEAVE LOOP1; ELSE ITERATE LOOP1; END IF; SET RESULT = COUNTER * 10; END WHILE LOOP1; END;


| 8

Contoh 3.1: Membuat suatu store procedure supaya menghapus semua pertandingan pemain berdasarkan nomor pemain yang ditentukan

Recommend Documents