Praktikum Function dan Procedure pada MySQL

Pemrograman di Function dan Procedure 

pada MySQL

Dalam function dan procedure, kita bisa memasukkan logika pemrograman. Ada beberapa karakteristik pemrograman yang didukung oleh MySQL. Beberapa di antaranya adalah penggunaan variabel, kendali kondisional, dan perulangan.

Variabel

Seperti pada pemrograman pada umumnya, kita bisa menggunakan variabel lokal pada function dan procedure. Pendeklarasian variabel memiliki sintaks sebagai berikut:

DECLARE var_name [, var_name] ... type [DEFAULT value]

Nilai inisialisasi variabel dapat dilakukan menggunakan statement DEFAULT. Jika statement DEFAULT tidak digunakan, maka nilai inisialisasi variabel adalah NULL. Penamaan variabel lokal bersifat case insensitive. Berikut adalah beberapa contoh deklarasi variabel:

DECLARE total_sale INT

DECLARE x, y INT DEFAULT 0

Pemberian nilai ke sebuah variabel dilakukan dengan menggunakan statement SET. Hasil dari query juga dapat dimasukkan ke dalam variabel menggunakan SELECT INTO. Berikut … adalah beberapa contoh pemberian nilai ke variabel.

SET total_sale = 50;

SELECT COUNT(*) INTO numPekerja FROM pekerja;

Ruang lingkup variabel adalah di antara blok BEGIN … END di mana variabel tersebut didefinisikan. Variabel dapat diakses dari blok yang berada dalam blok di mana ia didefinisikan, kecuali pada blok yang memiliki deklarasi nama variabel yang sama. Berikut adalah contoh penggunaan variabel dalam function dan stored procedure.

mysql> CREATE FUNCTION addTax(gaji FLOAT(8,2))

    -> RETURNS FLOAT (8,2)

    -> BEGIN

    -> DECLARE tax FLOAT DEFAULT 0.05;

    -> RETURN gaji * (1 - tax);

    -> END ^_^

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Pada contoh di atas, dibuat sebuah function dengan variabel bernama tax. Variabel ini diset memiliki nilai default 0.05 dan digunakan untuk mengubah nilai gaji. Contoh di bawah ini menunjukkan penggunaan function addTax.

mysql> select nama_depan, addTax(gaji) from pekerja;

+------------+--------------+

| nama_depan | addTax(gaji) |

+------------+--------------+

| John       |    475000.00 |

| Alison     |    475000.00 |

| James      |    950000.00 |

| Celia      |    950000.00 |

| Robert     |    190000.00 |

| Linda      |    190000.00 |

| David      |    475000.00 |

| Hercule    |    475000.00 |

| Lincoln    |    475000.00 |

| Sherlock   |    950000.00 |

+------------+--------------+

10 rows in set, 3 warnings (0.28 sec)

Nama variabel lokal seharusnya tidak sama dengan nama kolom dalam tabel database. Jika pada statement SQL seperti SELECT terdapat referensi ke kolom tabel dengan nama yang sama, MySQL mereferensikannya sebagai nama variabel. Berikut adalah contohnya.

mysql> DELIMITER **

mysql> CREATE PROCEDURE checkScope()

    -> BEGIN

    -> DECLARE nama_depan VARCHAR(15) DEFAULT 'bob';

    -> SELECT nama_depan FROM pekerja;

    -> END **

Query OK, 0 rows affected (0.54 sec)

mysql> DELIMITER ;

mysql> call checkScope();

+------------+

| nama_depan |

+------------+

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

| bob        |

+------------+

10 rows in set (0.00 sec)

Pada contoh di atas, ketika kita melakukan pemilihan SELECT untuk nama_depan, nilai yang ditampilkan adalah nilai default dari variable nama_depan, yaitu 'bob'.

Kendali Kondisional

Seperti layaknya bahasa pemrograman, kita juga bisa mendefinisikan kendali kondisional di dalam function dan procedure. Kendali kondisional yang disediakan dalam MySQL adalah IF dan CASE.

Kendali IF

Sintaks dasar dari IF adalah sebagai berikut:

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] …

[ELSE statement_list]

END IF;

Nilai search_condition dievaluasi. Jika bernilai true, maka statement_list setelah THEN dijalankan. Namun

jika bernilai false, maka statement_list pada ELSE yang dijalankan. Penggunaan banyak kondisi dapat dilakukan dengan statement ELSEIF. Berikut adalah contoh penggunaan IF:

mysql> delimiter &&

mysql> create function hideGaji(gaji float(8,2))

    -> returns varchar(20)

    -> begin

    -> declare bayaran varchar(20);

    -> if gaji <4000 then set bayaran='Gaji Rendah';

    -> else set bayaran='Gaji Tinggi';

    -> end if;

    -> return bayaran;

    -> end &&

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select nama_depan, nama_belakang, hideGaji(gaji)from pekerja;

+------------+---------------+----------------+

| nama_depan | nama_belakang | hideGaji(gaji) |

+------------+---------------+----------------+

| John       | Doe           | Gaji Tinggi    |

| Alison     | Mathews       | Gaji Tinggi    |

| James      | Smith         | Gaji Tinggi    |

| Celia      | Rice          | Gaji Tinggi    |

| Robert     | Black         | Gaji Tinggi    |

| Linda      | Green         | Gaji Tinggi    |

| David      | Larry         | Gaji Tinggi    |

| Hercule    | Poirot        | Gaji Tinggi    |

| Lincoln    | Rhyme         | Gaji Tinggi    |

| Sherlock   | Holmes        | Gaji Tinggi    |

+------------+---------------+----------------+

10 rows in set, 3 warnings (0.00 sec)

Kendali CASE

Sintaks dari kendali CASE adalah sebagai berikut:

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

Pada sintaks di atas, case_value dibandingkan dengan semua nilai when_value sampai ditemukan yang sesuai. Jika ditemukan, maka statement_list pada WHEN yang bersesuaian akan dijalankan. Jika tidak ada nilai when_value yang sesuai, maka statement_list pada ELSE yang dijalankan (jika ada). Berikut adalah contoh penggunaan CASE:

mysql> DELIMITER ##

mysql> CREATE FUNCTION calcTax(job VARCHAR (20)))

-> RETURNS FLOAT(3,2)

-> BEGIN

-> DECLARE tax FLOAT(3,2) DEFAULT 0.05;

-> CASE job

-> WHEN 'Manager' THEN SET tax = 0.1;

-> WHEN 'Programmer' THEN set tax = 0.07;

-> WHEN 'Tester' THEN set tax = 0.06;

-> ELSE SET tax = 0.05;

-> END CASE;

-> RETURN tax;

-> END ##

Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;

mysql> SELECT nama_depan, nama_belakang, calcTax(description) FROM employee;

+------------+-----------+----------------------+

| nama_depan | nama_belakang | calcTax(description) |

+------------+-----------+----------------------+

| John | Doe | 0.07 |

| Alison | Mathews | 0.06 |

| James | Smith | 0.06 |

| Celia | Rice | 0.10 |

| Robert | Black | 0.06 |

| Linda | Green | 0.06 |

| David | Larry | 0.10 |

| Hercule | Poirot | 0.05 |

| Lincoln | Rhyme | 0.05 |

| Sherlock | Holmes | 0.05 |

+------------+-----------+----------------------+

10 rows in set (0.00 sec)

Bentuk sintaks dari CASE yang lain adalah sebagai berikut:

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

Pada sintaks di atas, search_condition di setiap klausa WHEN dievaluasi hingga ditemukan klausa WHEN yang sesuai. Jika tidak ada klausa WHEN yang sesuai, maka klausa ELSE yang dijalankan. Jika tidak ada klausa ELSE ketika semua klausa WHEN tidak sesuai, maka akan terjadi Case not found for CASE statement error. Berikut adalah contoh penggunaan sintaks CASE … WHEN tersebut:

mysql> delimiter //

mysql> create function deskripsi(pekerjaan varchar(225))

    -> returns float(3,2)

    -> begin

    -> declare des float(3,2) default 0.05;

    -> case pekerjaan

    -> when 'Manager' then set des=0.1;

    -> when 'Programer' then set des=0.07;

    -> when 'Security' then set des=0.06;

    -> else set des =0.05;

    -> end case;

    -> return des;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select nama_depan, nama_belakang, deskripsi from pekerja;

+------------+---------------+-----------+

| nama_depan | nama_belakang | deskripsi |

+------------+---------------+-----------+

| John       | Doe           |      NULL |

| Alison     | Mathews       |      NULL |

| James      | Smith         |      NULL |

| Celia      | Rice          |      NULL |

| Robert     | Black         |      NULL |

| Linda      | Green         |      NULL |

| David      | Larry         |      NULL |

| Hercule    | Poirot        |      NULL |

| Lincoln    | Rhyme         |      NULL |

| Sherlock   | Holmes        |      NULL |

+------------+---------------+-----------+

10 rows in set (0.00 sec)

Perulangan

Pada function dan procedure juga disediakan perulangan. Beberapa bentuk perulangan yang disediakan dalam MySQL adalah WHILE,REPEAT … UNTIL, dan LOOP.

Perulangan WHILE

Bentuk sintaks untuk perulangan WHILE adalah sebagai berikut:

WHILE search_condition DO

statement_list

END WHILE

Statement_list yang terdapat dalam WHILE diulang selama search_condition bernilai true.

statement_list terdiri atas satu atau lebih statement SQL, setiap statementnya dipisahkan dengan delimiter titik koma (;). Berikut adalah contoh penggunaan WHILE.

mysql> create procedure mod12(in number int(10))

    -> begin

    -> while number mod 12>0 do

    -> set number = number + 1;

    -> end while;

    -> select number;

    -> end //

Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;

mysql> call mod12(10);

+--------+

| number |

+--------+

|     12 |

+--------+

1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> call mod12(24);

+--------+

| number |

+--------+

|     24 |

+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Perulangan REPEAT … UNTIL

Sintaks dari REPEAT UNTIL … adalah sebagai berikut:

REPEAT

statement_list

UNTIL search_condition

END REPEAT

Statement_list di dalam REPEAT dilakukan secara berulang hingga ekspresi search_condition bernilai true. Oleh karena itu, sebuah REPEAT memasuki perulangan paling sedikit sebanyak satu kali. statment_list terdiri atas satu atau lebih statement, masing-masing dipisah dengan delimiter titik koma (;). Berikut adalah contoh penggunaan REPEAT … UNTIL.

mysql> create procedure repeatDemo(in number int(10))

    -> begin

    -> repeat

    -> set number = number + 1;

    -> until number mod 12 = 0

    -> end repeat;

    -> select number;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call repeatDemo(10);

+--------+

| number |

+--------+

|     12 |

+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Perulangan LOOP

Sintaks dari perulangan LOOP adalah sebagai berikut:

[begin_label:] LOOP

statement_list

END LOOP [end_label]

LOOP merupakan bentuk perulangan sederhana. Perulangan dilakukan terhadap statement_list, yang terdiri atas beberapa statement dengan dipisahkan oleh tanda titik koma (;). Statement di dalam LOOP diulang sampai LOOP berakhir. Cara mengakhiri LOOP biasanya dilakukan dengan statement LEAVE. Tanda perulangan dilakukan menggunakan ITERATE. Berikut adalah contoh penggunaan LOOP.

mysql> delimiter //mysql> delimiter //

mysql> create procedure iterateDemo(number int)

    -> begin

    -> label1: loop

    ->  set number = number + 1;

    ->  if number mod 2>0 then

    ->          iterate label1;

    ->  end if;

    ->  leave label1;

    -> end loop label1;

    -> select number;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call iterateDemo(10)

    -> ;

+--------+

| number |

+--------+

|     12 |

+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call iterateDemo(20);

+--------+

| number |

+--------+

|     22 |

+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)