Transcript Pertemuan II

Pertemuan II
SINTAK
STORED PROCEDURE
TUJUAN:
1. Mahasiswa dapat memahami sintak-sintak
dalam stored procedure dan stored function
untuk:
•
•
•
•
•
Membuat
Menghapus
Memanggil
Melihat isi
Melihat daftar
2. Mahasiswa dapat memahami sintak untuk
gabungan statement dengan BEGIN … END
3. Mahasiswa dapat memahami ekspresi dalam
stored procedure
4. Mahasiswa dapat memahami parameter dalam
stored procedure
Sintak-sintak dalam
Stored Procedure dan Stored
Function
Sintak untuk membuat :
Procedure
CREATE PROCEDURE sp_name ([proc_parameter [,…]])
[characteristic ..] routine_body
Function
CREATE FUNCTION sp_name ([func_parameter [,…]])
RETURN type
[characteristic ..] routine_body
Dimana :
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 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
Untuk mengubah stored procedure atau function pertama
kali yang dilakukan adalah menghapus terlebih dahulu
procedure / function nya kemudian baru dibuat kembali.
Sintak untuk menghapus
DROP {PROCEDURE|FUNCTION} [IF EXIST] sp_name
Contoh:
Drop Procedure spLihatBarang;
Sintak untuk memanggil
CALL sp_name
Contoh:
Call spLihatBarang();
Contoh Membuat Prosedur
DELIMITER $$
DROP PROCEDURE IF EXISTS `pbd`.`splihatbarang`$$
CREATE PROCEDURE `pbd`.`splihatbarang` ()
BEGIN
select * from barang;
END$$
DELIMITER ;
Sintak untuk melihat isi dari procedure / functions
SHOW CREATE {PROCEDURE|FUNCTION} sp_name;
Contoh:
Show create procedure splihatbarang;
Sintak untuk melihat daftar / list fungsi dan prosedur
SHOW {PROCEDURE|FUNCTION} status;
Contoh :
Show procedure status;
Show function status;
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 (;),
• 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 ;
Contoh :
mysql > DELIMITER $$
mysql > create procedure splihatbarang()
-> begin
->
select * from barang;
->
select * from detailpembelian;
-> end;
-> $$
EKSPRESI DALAM
STORED PROCEDURE
Penggunaan stored procedure memungkinkan program
aplikasi menampilkan kolom ekspresi, yaitu kolom yang
merupakan ekspresi yang melibatkan kolom aslinya.
Misalnya dalam kasus pembelian barang terdapat
discount sebesar 0,1 dari jumlah barang yang dibeli
dikali dengan harga belinya dan total jumlah menjadi
jumlah barang yang dibeli dikali dengan harga belinya
dikali dengan 0,9
Sehingga procedure yang dibuat dengan memanfaatkan
ekspresi sebagai berikut :
DELIMITER $$
CREATE PROCEDURE ‘pbd’.`spHitungBeli`()
BEGIN
select noreference,
kodebarang,hargabeli,jumlahbarang,
jumlahbarang*hargabeli as Jumlah,
jumlahbarang*hargabeli*0.1 as Discount,
jumlahbarang*hargabeli*0.9 as Total
from detailpembelian;
END $$
DELIMITER ;
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.
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.
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;$$
CALL spInfoBeli (‘PS.001’);
CALL spInfoBeli (‘%PS%’);
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;
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 ;