07/05/2012
Pengertian Stored Procedure
STORED PROCEDURE
• Prosedur (sub program)yang terdiri dari bagian deklaratif dan prosedural statemen SQL yang tersimpan pada katalog database MySQL • Diaktifkan dengan memanggil nama dari stored procedure tersebut dari DBMS(query editor, procedure yang lain) atau program aplikasi • Statemen deklaratif (CREATE, UPDATE, SELECT dll) • Procedural language (IF-THEN-ELSE dan WHILE DO) • Ketika memanggil stored procedure kita dapat menentukan Input dan Output prameter.
Mengapa SP?
Mengapa SP?
Dalam membuat aplikasi database, kita bisa meletakkan fungsi-fungsi yang merupakan bisnis prosesnya dengan 2 pilihan
2. Meletakkan fungsi-fungsi bisnis di dalam database sebagai store procedure, dan object database yang lain mempunyai keuntungan:
1. Meletakkan fungsi-fungsi bisnis di dalam bahasa programming yang digunakan. Untuk aplikasi yang kompleks, cara ini memiliki kelemahan:
Jika processing banyak terjadi, performance akan turun Jika ada pengembangan lain dan memakai bahasa pemrograman lain, maka fungsi bisnis harus ditulis ulang Harus jeli mengenai konsistensi update suatu table yang mempunyai hubungan dengan tabel lain
– Performance cepat – Konsistensi data tabel akan terjaga – Jika ada pengembangan lain yang memakai bahasa pemrograman berbeda, tidak perlu menulis ulang fungsi-fungsi bisnis tersebut
1
07/05/2012
Dimana :
Sintak Stored Procedure & Function
Proc_parameter: [IN | OUT | INOUT] param_name type Func_parameter
Sintak untuk membuat : Procedure CREATE PROCEDURE sp_name ([proc_parameter [,…]]) routine_body
Function
Param_name type Type : Semua type data yang valid di MySQL. Routine_body: Statement SQL procedure yang valid.
CREATE FUNCTION sp_name ([func_parameter [,…]]) RETURN type routine_body
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 sebagai 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 untuk mengubah ALTER {PROCEDURE|FUNCTION}
Sintak untuk menghapus DROP {PROCEDURE|FUNCTION} [IF EXIST] sp_name
Contoh: Drop Procedure spLihatBarang;
Sintak untuk memanggil CALL sp_name
Contoh: Call spLihatBarang();
2
07/05/2012
Contoh Membuat Prosedur
Sintak untuk melihat isi dari procedure / functions SHOW CREATE {PROCEDURE|FUNCTION} sp_name;
DELIMITER $$ DROP PROCEDURE IF EXISTS `pbd`.`splihatbarang`$$ CREATE PROCEDURE `pbd`.`splihatbarang` () BEGIN select * from barang; END$$ DELIMITER ;
Contoh: Show create procedure splihatbarang;
Sintak untuk melihat daftar / list fungsi dan prosedur SHOW {PROCEDURE|FUNCTION} status;
Contoh : Show procedure status; Show function status;
Contoh :
Sintak untuk gabungan statement • Kadangkala dalam suatu stored routines dan trigger dibutuhkan untuk menulis beberapa buah statement sekaligus. Gabungan statement / compound statement tersebut diawali dengan BEGIN dan diakhiri dengan END. Diantara BEGIN … END, dapat terdiri dari satu atau banyak statement dan masing-masing statement harus diakhiri dengan tanda semikolon (;),
mysql > DELIMITER $$ mysql > create procedure splihatbarang() -> begin -> select * from barang; -> select * from detailpembelian; -> end; -> $$
• Karena setiap statement harus diakhiri dengan semikolon (;) maka diperlukan untuk mengganti delimiter dari ; menjadi delimiter yang diinginkan, misalnya dengan menggunakan // atau $$. Pengubahan delimiter ini akan membuat setiap statement dalam stored routine dapat menggunakan ;
3
07/05/2012
STORED PROCEDURE dengan PARAMETER Parameter merupakan variabel memori yang digunakan untuk menerima suatu nilai dari pemangilnya. Stored procedure dapat menggunakan parameter sehingga program aplikasi yang memanggil stored procedure dapat mengakses database yang diperlukan sesuai dengan kondisi yang diminta yaitu dengan cara mengirimkan suatu nilai ke Stored Procedure melalui parameter. Sebagai contoh aplikasi untuk mencari data, nilai pencarian akan berubah-ubah tergantung permintaan user.
Contoh : Pemakaian IN Parameter DELIMITER $$ CREATE PROCEDURE spInfoBeli(pKodeBarang varchar(20)) BEGIN SELECT NoReference, KodeBarang, HargaBeli, JumlahBarang, HargaBeli*JumlahBarang*0.9 as Total From detailPembelian Where KodeBarang like pKodeBarang; End;$$
Terdapat 3 mode parameter yaitu :IN, OUT dan INOUT : • IN (default) akan mempassingkan nilai konstan dari memori ke stored procedure • OUT akan mengambil nilai dari prosedur • IN OUT akan mempassingkan nilai dari memori ke dalam procedure dan memungkinkan nilai yang berbeda dari prosedur dikembalikan ke memori dengan menggunakan parameter yang sama. Secara default stored procedure / function memiliki parameter IN. Kata IN tidak perlu ditambahkan sebelum nama parameter.
Pemakaian OUT Parameter DELIMITER $$ CREATE PROCEDURE ‘pbd’.`spoutparam`(OUT param1 INT) BEGIN select count(*) into param1 from barang; END $$ DELIMITER ; Mysql > CALL spoutparam(@coba); Mysql > select @coba;
CALL spInfoBeli (‘PS.001’); CALL spInfoBeli (‘%PS%’);
4
07/05/2012
Pemakaian IN dan OUT parameter DELIMITER $$ DROP PROCEDURE IF EXISTS `pbd`.`film_in_stock` $$ CREATE PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATA BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count; END $$ DELIMITER ;
Pemakaian INOUT Parameter DELIMITER $$ CREATE PROCEDURE `spPhone`(INOUT phone varchar(25)) BEGIN select concat('(',substring(phone,1,3),')',substring( pho ne,4,3),' - ',substring(phone,7)) into phone; END $$ DELIMITER ;
5
