FUNCTION DAN PROCEDURE

MATERI PART 5 – Prak. BASISDATA

FUNCTION DAN PROSEDURE

LABORATORIUM 4 – BASISDATA

MENU PART INI    :

1. Intro Stored Routine

2. Function

a.   Membuat function

b.   Melihat function yang telah dibuat

c.   Menghapus function

3. Procedure

a.   Membuat procedure

b.   Parameter dalam procedure

                               i.     Parameter IN

                             ii.     Parameter OUT

                            iii.     Parameter INOUT

                             iv.     Melihat procedure yang telah dibuat

                               v.     Menghapus procedure

4. Pemrograman di Function dan Procedure

a.   Variabel

b.   Kendali Kondisional.

                               i.     Kendali IF

                             ii.     Kendali CASE

c.   Perulangan

                               i.   Perulangan WHILE

                             ii.   Perulangan REPEAT … UNTIL

                            iii.   Perulangan LOOP

1. Fuction

Sebuah function dapat digunakan secara langsung dalam statement SELECT, UPDATE, dan DELETE. Hasil dari function dapat dikembalikan sebagai output. Sebuah function hanya dapat mengembalikan sebuah nilai saja.

1.1    Membuat fuction

Sebelum kita membuat function terlebih dahulu kita siapkan sebuah tabel terlebih dahulu.

mysql> select*from pekerja;

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

| nama_depan | nama_belakang |

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

| John       | Doe           |

| Alison     | Mathews       |

| James      | Smith         |

| Celia      | Rice          |

| Robert     | Black         |

| Linda      | Green         |

| David      | Larry         |

| Hercule    | Poirot        |

| Lincoln    | Rhyme         |

| Sherlock   | Holmes        |

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

10 rows in set (0.00 sec)

Selanjutnya kita membuat function

mysql> delimiter //

mysql> create function nama_lengkap(in_nama_depan varchar(15), in_nama_belakang varchar(15))

    -> returns varchar(35)

    -> begin

    -> return concat(in_nama_depan,' ',in_nama_belakang);

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select nama_lengkap(nama_depan,nama_belakang) from pekerja;

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

| nama_lengkap(nama_depan, nama_belakang) |

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

| John Doe                                 |

| Alison Mathews                           |

| James Smith                              |

| Celia Rice                               |

| Robert Black                             |

| Linda Green                              |

| David Larry                              |

| Hercule Poirot                           |

| Lincoln Rhyme                            |

| Sherlock Holmes                          |

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

10 rows in set (0.00 sec)

1.2    Melihat fuction yang telah dibuat

Function apa saja yang telah kita buat dapat dilihat menggunakan statement SHOW FUNCTION STATUS.

mysql> show function status;

1.3    Menghapus function

Function yang telah dibuat dapat dihapus menggunakan DROP FUNCTION nama_function.

mysql> DROP FUNCTION full_name;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW FUNCTION STATUS;

Empty set (0.00 sec)

2. Procedure

Procedure dapat berisi statement SQL (INSERT, UPDATE, DELETE, SELECT) atau operasi lain yang disimpan dalam database. Sebuah procedure dapat dipanggil menggunakan statement CALL nama_procedure disertai parameter yang diperlukan.

2.1    membuat procedure

sintaks untuk membuat procedure adalah sebagai berikut;

CREATE PROCEDURE nama_procedure (parameter_procedure())routine_body

Berikut contoh pembuatan procedure:

mysql> delimiter //

mysql> create procedure show_pekerja()

    -> begin

    -> select*from pekerja;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> alter table <nama tabel> add nama kolom (tipe data)(20);

mysql> alter table <nama tabel> modify <nama field> tipe data(15);

mysql> update <nama tabel>  where no_plat='B-3454-SDE';

mysql> call show_pekerja ();

namatabel = pekerja

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

| nama_depan | nama_belakang | alamat | gaji    | pekerjaan |

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

| John       | Doe           | Sleman |  500000 | Programer |

| Alison     | Mathews       | Sleman |  500000 | Programer |

| James      | Smith         | Bantul | 1000000 | Manager   |

| Celia      | Rice          | Klaten | 1000000 | Manager   |

| Robert     | Black         | Klaten |  200000 | Security  |

| Linda      | Green         | Bantul |  200000 | Security  |

| David      | Larry         | Bantul |  500000 | Programer |

| Hercule    | Poirot        | Klaten |  500000 | Programer |

| Lincoln    | Rhyme         | Klaten |  500000 | Programer |

| Sherlock   | Holmes        | Sleman | 1000000 | Manager   |

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

10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

2.2    parameter dalam procedure

Procedure menerima parameter (proc_parameter) yang didefinisikan sebagai berikut:

proc_parameter:

[ IN | OUT | INOUT ] param_name type

Dalam parameter procedure, didefinisikan jenis parameter, yaitu IN, OUT, atau INOUT. Bila tidak dispesifikasikan saat pembuatan, maka default jenis parameter yang dipilih adalah IN.

·         Parameter IN mengirimkan sebuah nilai ke dalam procedure. Procedure bisa saja merubah nilai parameter ini, namun perubahan tersebut tidak visibel terhadap pemanggil procedure ketika procedure tersebut selesai.

·         Parameter OUT mengirimkan nilai dari procedure ke pemanggil. Nilai inisial dari parameter ini adalah NULL dan nilainya visibel terhadap pemanggil.

·         Parameter INOUT diinisialisasi oleh pemanggil, dapat dimodifikasi oleh procedure, dan perubahan nilai parameter visibel terhadap pemanggil ketika procedure selesai.

2.2.1          parameter IN

Berikut adalah contoh penggunaan parameter IN:

mysql> delimiter $$

mysql> create procedure getPekerjaByAlamat (in namaAlamat varchar(255))

    -> begin

    -> select*from pekerja where alamat LIKE namaAlamat;

    -> end $$

Mysql> delimiter ;

Pada contoh di atas, dibuat procedure getPekerjaByAlamat dengan satu parameter masukan berjenis IN bernama namaAlamat. Procedure ini digunakan untuk menampilkan data pada tabel pekerja dengan nama kota sesuai parameter masukan. Pemanggilan procedure ini dapat dilihat pada contoh di bawah ini. Pada contoh ini, kita memasukkan Sleman” sebagai parameter masukan procedure.

mysql> call getPekerjaByAlamat("Sleman");

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

| nama_depan | nama_belakang | Alamat |

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

| John       | Doe           | Sleman |

| Alison     | Mathews       | Sleman |

| Sherlock   | Holmes        | Sleman |

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

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

2.2.2          parameter OUT

berikut adalah contoh penggunaan parameter OUT;

mysql> delimiter :)

mysql> create procedure getNumPekerja (out numPekerja int)

    -> begin

    -> select count(*) into numPekerja from pekerja;

    -> end :)

Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

Pada contoh di atas, dibuat procedure untuk menampilkan jumlah pekerja dalam tabel. Hasil query tersebut disimpan dalam variabel numPekerja dengan statement INTO numPekerja. Pemanggilan pekerja dengan parameter OUT dilakukan dengan menggunakan variabel session yang diawali dengan karakter @. Pemanggilan procedure getNumPekerja ditunjukkan sebagai berikut:

mysql> call getNumPekerja(@num);

Query OK, 1 row affected (0.05 sec)

mysql> select @num;

+------+

| @num |

+------+

|   10 |

+------+

1 row in set (0.00 sec)

2.2.3          parameter INOUT

berikut adalah contoh penggunaan parameter INOUT:

mysql> delimiter ^^

mysql> create procedure increase(inout number int)

    -> begin

    -> set number = number + 15;

    -> end ^^

Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

Pada contoh di atas, kita membuat procedure increase untuk menambahkan input dengan nilai 15. Memodifikasi nilai parameter input dilakukan dengan menggunakan SET. Contoh berikut memperlihatkan bagaimana memanggil procedure increase. Kita mendefinisikan terlebih dahulu variabel session @num dengan nilai 100. Kemudian setelah pemanggilan increase, nilai @num menjadi 115.

mysql> set @num = 100;

Query OK, 0 rows affected (0.00 sec)

mysql> call increase(@num);

Query OK, 0 rows affected (0.00 sec)

mysql> select @num;

+------+

| @num |

+------+

|  115 |

+------+

1 row in set (0.00 sec)

2.2.4          melihat procedure yang telah dibuat

Procedure yang telah kita buat dapat dilihat menggunakan statement SHOW PROCEDURE STATUS sebagai berikut:

mysql> show procedure status;

2.2.5          menghapus procedure

Procedure yang telah kita buat dapat dihapus menggunakan DROP PROCEDURE.

Mysql> DROP PROCEDURE increaseGaji;

INPUT DATA DENGAN PROCEDURE

mysql> create table hoho( nama varchar(10), alamat varchar(10));

Query OK, 0 rows affected (0.13 sec)

mysql> delimiter #

mysql> create procedure isi_data( in nama_depan varchar(10), nama_belakang varchar(10), alamat varchar(20), gaji int(8), pekerjaan varchar (15))

    -> begin

    -> insert into pekerja values(nama_depan, nama_belakang, alamat, gaji, pekerjaan);

    -> end #

Query OK, 0 rows affected (0.02 sec)

call isi_data(‘Dono’, ‘Setiawan’, ‘Bandung’, 1200000, ‘Kuli’);

call isi_data(‘Chika’, ‘Safira’, ‘Surabaya’, 2350000, ‘OfficeGirl’);

mysql> select * from pekerja;

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

| nama | alamat  |

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

| Fera | Abarawa |

| Rara | Jogja   |

| Didi | Sleman  |

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

HAPUS DATA DENGAN PROCEDURE

mysql> delimiter #

mysql> create procedure hapus_data(in par_nama varchar(10))

    -> begin

    -> delete from pekerja where nama=par_nama;

    -> end#

Query OK, 0 rows affected (0.00 sec)

Mysql> delimiter ;

Call hapus_data(“Didi”);

mysql> select * from pekerja;

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

| nama | alamat  |

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

| Fera | Abarawa |

| Rara | Jogja   |

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

4.Pemrograman di Function dan Procedure

Di 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.

4.1 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.

Tambahkan kolom gaji pada tabel pekerja

mysql> alter table pekerja add gaji int;

Query OK, 10 rows affected (0.35 sec)

mysql> delimiter ^_^

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)

SELECT addTax(10000);

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'.

4.2 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.

4.2.1 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 hitungGaji(gaji float)

    -> returns varchar(20)

    -> begin

    -> declare bayaran varchar(20);

    -> if gaji <400000 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, hitungGaji(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)

4.2.2 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:

Tambahkan kolom pekerjaan

mysql> alter table pekerja add pekerjaan varchar(30);

Query OK, 11 rows affected (0.21 sec)

Tambahkan data menggunakan insert

mysql> insert into pekerja values('aaa','bbb',300000,'Manager');

Query OK, 1 row affected (0.03 sec)

mysql> DELIMITER ##

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

-> RETURNS FLOAT(3,2)

-> BEGIN

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

-> CASE pekerjaan

-> 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(pekerjaan) FROM pekerja;

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

| 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(pekerjaan) 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)

4.3 Perulangan

Pada function dan procedure juga disediakan perulangan. Beberapa bentuk perulangan yang disediakan

dalam MySQL adalah WHILE,REPEAT … UNTIL, dan LOOP.

4.3.1 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> delimiter //

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)

4.3.2 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> delimiter ^

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

    -> begin

    -> repeat

    -> set number = number + 1;

    -> until number mod 10 = 0

    -> end repeat;

    -> select number;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call repeatDemo(14);

+--------+

| number |

+--------+

|     20 |

+--------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

4.3.3 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> create procedure iterateDemo(number int)

    -> begin

    -> label1: loop

    ->  set number = number + 1;

    ->  if number mod 7>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 |

+--------+

|     14 |

+--------+

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)

Read More