Procedure & Function Database MySQL - IV

 

Stored Procedure dan Function adalah rangkaian program yang disimpan dalam database dan dapat dipanggil oleh program lain atau melalui SQL Prompt

Stored procedured ditulis dalam bentuk suatu Script.

Keuntungan

• Cepat, kompilasi dilakukan di Database (kadang disebut “pre -compilation”) sehingga mengurangi traffic
• Adanya pemisahan antara database access logic dengan application logic sehingga program aplikasi menjadi lebih sederhana dan lebih ringkas (thin client concept)
• Berupa obyek dalam database, sehingga menghilangkan ketergantungan terhadap bahasa program yang digunakan
• Bersifat Portable, jika bisa berjalan di database tsb maka dipastikan jika database bisa terinstall di manapun maka store procedure pasti bisa dijalankan

Sintak membuat procedure dan function

Procedure

CREATE PROCEDURE sp_name ([proc_parameter [,…]]) [characteristic ..]

routine_body

Function

CREATE FUNCTION sp_name ([func_parameter [,…]])

RETURNS type [characteristic ..] routine_body

Keterangan :

Proc_parameter:

[IN | OUT | INOUT] param_name type

Func_parameter

Param_name type

Type :

Semua type data yang valid di MySQL.

Characteristic:

LANGUAGE SQL

[NOT] DETERMINISTIC

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

SQL SECURITY {DEFINER | INVOKER }

COMMENT ‘string’

Routine_body:

Statement SQL procedure yang valid.

Penjelasan

Secara default, routine berasosiasi dengan default database. Untuk berasosiasi secara eksplisit dengan database yang diberikan, secara spesifik diberi nama db_name.sp_name pada saat membuat routine. Jika tidak ada parameter maka empty parameter digunakan dengan   menggunakan ( ). Setiap parameter memiliki IN parameter seba gai default. IN, OUT, INOUT parameter hanya valid untuk procedure, sedangkan untuk function hanya IN parameter saja.

 RETURN type hanya berlaku untuk function.

ROUTINE_BODY berisi statement SQL yang valid. Dapat berisi statement sederhana seperti SELECT atau INSERT atau berisi gabungan beberapa statement yang dapat ditulis dengan menggunakan BEGIN .. END. Compound statement dapat berisi deklarasi, loop dan struktur kontrol yang lain.

Sintak  mengubah

Untuk mengubah stored procedure atau function pertama kali yang dilakukan adalah menghapus           terlebih dahulu procedure / function kemudian baru dibuat kembali.

DROP {PROCEDURE|FUNCTION} [IF EXIST] sp_name

Contoh:

Drop Procedure spNamaProdedureFunction;

 Sintak untuk memanggil

Call sp_name

Contoh:

Call spNamaProdedureFunction();

melihat daftar / list fungsi dan prosedur 

  Show Create {Procedure/Function} spName; 

Contoh:

Show create procedure spProcedureFunction;

 melihat daftar / list fungsi dan prosedur

Show {Procedure/Function} status;

Contoh :

Show procedure status;

show function status;

Contoh pembuatan Procedure dan Function

Membuat Database dan Table 

Pada langkah awal membuat procedure pasti memerlukan sebuah Database beserta tabel maka dari itu akan membuat database dan table terlebih dahulu. 

Create database toko; 

use toko;   

Create table barang

(

NamaBarang varchar(10), 

Satuan varchar(6), 

Harga int, 

Jumlah int, 

primary key(NamaBarang)

);

Stuktur table akan seperti gambar dibawah

1. Stored Procedure Insert barang

Delimiter // 

Create procedure insertBarang

(

in_NamaBarang varchar(10), 

in_Satuan varchar(6), 

in_Harga int, in_Jumlah int

) 

begin 

insert into barang values(in_NamaBarang, in_Satuan, in_Harga, in_Jumlah); 

end; // 

Delimiter ;

call insertBarang (‘Sikat Gigi’,’Bungkus’,’4000’,’50’);

2. Stored Procedure Update Data

delimiter // 

create procedure updateDataBarang

(

IN p_NamaBarang varchar(10), 

IN up_Harga int

) 

begin 

update barang set Harga = up_Harga WHERE NamaBarang = p_NamaBarang; 

end; // 

delimiter ;

call updateDataBarang(‘Sikat Gigi’,10000);

hasil setelah diubah

3. Stored Procedure Delete Data

Delimiter // 

create procedure deleteDataBarang

(

dr_NamaBarang varchar(10)

) 

begin

delete from barang where NamaBarang = dr_NamaBarang;

end; // 

Delimiter ;

call deleteDataBarang(‘Sabun’);

Pembuatan Function

DELIMITER $$

DROP FUNCTION IF EXISTS tampilHarga $$

CREATE FUNCTION `tampilHarga(nama carchar(10)) RETURNS int(11)

BEGIN

     DECLARE nilaiharga int(11);

     SELECT harga into nilaiharga FROM barang WHERE NamaBarang=Nama;

     RETURN nilaiharga;

END $$

DELIMITER ;

CARA MEMANGGIL FUNGSI TERSEBUT ADALAH.

mysql> SELECT tampilHarga('Sabun');

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

| tampilHarga('Sabun') |

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

| 2000                           |

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

1 row in set (0.00 sec)

mysql> SELECT NamaBarang, tampilHarga(NamaBarang) FROM Barang;

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

| NamaBarang | tampilHarga  |

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

| Sabun | 2000              |

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

1 rows in set (0.05 sec)