MATERI PART 5 – Prak. BASISDATA

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    Meelihat 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> call show_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( in nama varchar(10), alamat varchar(10))

    -> begin

    -> insert into hoho values(nama, alamat);

    -> end #

Query OK, 0 rows affected (0.02 sec)

call isi("Didi","Sleman");

call isi("Fera","Abarawa");

call isi("Rara","Jogja");

mysql> select * from hoho;

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

| 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 hoho where nama=par_nama;

    -> end#

Query OK, 0 rows affected (0.00 sec)

Mysql> delimiter ;

Call hapus_data(“Didi”);

mysql> select * from hoho;

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

| nama | alamat  |

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

| Fera | Abarawa |

| Rara | Jogja   |

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