STORED PROCEDURE & FUNCTION

DASAR TEORI

Dengan database, data atau informasi dapat disimpan secara permanen. Informasi yang tadinya di dalam variabel, akan segera hilang bersamaan dengan selesainya eksekusi program aplikasi. Untuk itu diperlukan database untuk menyimpan informasi yang ingin dipertahankan saat eksekusi selesai. Salah satu sistem database (DBMS) populer saat ini adalah MySQL. Terdapat beberapa alasan mengapa MySQL dipilih sebagai DBMS, diantaranya: freeware, didukung hampir semua bahasa pemrograman populer saat ini, database tercepat (metode one-sweep multijoin), dan komunitas yang besar.

Dalam implementasinya, sering programmer hanya memanfaatkan fitur table.

Tahukah Anda, sejak MySQL versi 5.x telah tersedia fitur lain yang sangat membantu terutama untuk aplikasi terpusat (client-server) atau tersebar (distributed), yaitu: Trigger, Stored Procedure, Stored Function, dan View. Tutorial kali ini akan membahas

implementasi keempat fitur tersebut.

PROCEDURE

Sintak :

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

[characteristic ...] routine_body

Keterangan :

_ sp_name: Nama routine yang akan dibuat

_ proc_parameter: Parameter stored procedue, terdiri dari :

_ IN : parameter yang digunakan sebagai masukan.

_ OUT : parameter yang digunakan sebagai keluaran

_ INOUT : parameter yang digunakan sebagai masukan sekaligus keluaran.

_ routine_body: terdiri dari statemen prosedur SQL yang valid.

Agar lebih jelas, perhatikan contoh penggunaannya berikut ini.

_ Contoh 1:

mysql> delimiter //

mysql> create procedure pMhsIlkom(OUT x varchar(25))

-> begin

-> select nama into x from mahasiswa where kode_prodi='P01';

-> end

-> //

mysql> call pMhsIlkom(@Nama);

-> select @Nama;

-> //

Dari contoh diatas terlihat bahwa parameter “x” (sebagai OUT) digunakan untuk

menampung hasil dari perintah routine_body. Pernyataan “into x”, inilah yang

mengakibatkan “x” menyimpan informasi nama (sebagai kolom yang ter-select).

Untuk menjalankan procedure digunakan ststemen call. Pernyataan “call

pMhsIlkom(@Nama)” menghasilkan informasi yang kemudian disimpan pada

parameter “@Nama”. Kemudian untuk menampilkan informasi ke layar digunakan

pernyataan “select @Nama”.

_ Contoh 2:

mysql> delimiter //

mysql> create procedure pMhs(out x varchar(25), out y varchar(3), in z

char(3))

-> begin

-> select nama,alamat into x,y from mahasiswa where kode_prodi=z;

-> end

-> //

mysql> call pMhs(@Nama,@Alamat,'P01');

mysql> select @Nama, @Alamat;

FUNCTION

Secara default, routine (procedure/function) diasosiasikan dengan database

yang sedang aktif. Untuk dapat mengasosiasikan routine secara eksplisit dengan

database yang lain, buat routine dengan format: db_name.sp_name.

MySQL mengijinkan beberapa routine berisi statemen DDL, seperti CREATE

dan DROP. MySQL juga mengijinkan beberapa stored procedure (tetapi tidak stored

function) berisi statemen SQL transaction, seperti COMMIT. Stored function juga

berisi beberapa statemen baik yang secara eksplisit atau implisit commit atau rollback.

Sintak :

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

RETURNS type

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

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

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

Keterangan :

_ sp_name: Nama routine yang akan dibuat

_ proc_parameter: Spesifikasi parameter sebagai IN, OUT, atau INOUT valid

hanya untuk PROCEDURE. (parameter FUNCTION selalu sebagai parameter IN)

_ returns: Klausa RETURNS dispesifikan hanya untuk suatu FUNCTION. Klausa ini

digunakan untuk mengembalikan tipe fungsi, dan routine_body harus berisi suatu

statemen nilai RETURN.

_ comment: Klausa COMMENT adalah suatu ekstensi MySQL, dan mungkin

digunakan untuk mendeskripsikan stored procedure. Informasi ini ditampilkan dengan

statemen SHOW CREATE PROCEDURE dan SHOW CREATE FUNCTION.

_ Contoh:

mysql> delimiter //

mysql> create function fcNamaMHS(x char(25)) returns char(40)

-> return concat('Nama : ', x);

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> select fcNamaMHS('Sholihun');

CONTOH SINTAK 

1) Implementasi topic ke Stored Procedure dan Function.

PROCEDURE:

MELALUI  SQL 2000:

# Procedure Insert TAMU (untuk penambahan data yang ada pada table Tamu)

create procedure TamuHotel

(@ID_TAMU varchar(10),

@NAMA char(10),

@ALAMAT char(20),

@NO_TELP numeric)

as begin

insert into TAMU values (@ID_TAMU, @NAMA, @ALAMAT, @NO_TELP);

end

go

exec TamuHotel 'it7','james','surabaya','0098123222';

# Procedure Insert PETUGAS (untuk  menambahkan data pada table Petugas)

create procedure PETUGASHOTEL

(@ID_PETUGAS varchar(20),

@ID_KAMAR varchar(10),

@ID_METROOM varchar(20),

@NAMA_PETUGAS varchar(50),

@ALAMAT_PETUGAS varchar(100),

@NO_TLP_PETUGAS numeric,

@JENIS_PETUGAS varchar(50),

@GAJI int)

as begin

insert into PETUGAS values (@ID_PETUGAS, @ID_KAMAR, @ID_METROOM, @NAMA_PETUGAS, @ALAMAT_PETUGAS, @NO_TLP_PETUGAS, @JENIS_PETUGAS, @GAJI);

end

go

exec PETUGASHOTEL  'ptg6','ik2','mt2','gogon', 'Jl. Jarak gg 3', '49102', 'Room Service', '1500000';

# Procedure Update PETUGAS (untuk melakukan update/perubahan data pada table Petugas).

create procedure update_petugas

(@ID_PETUGAS varchar(20),

@ID_KAMAR varchar(10),

@ID_METROOM varchar(20),

@NAMA_PETUGAS varchar (50),

@ALAMAT_PETUGAS varchar (100),

@NO_TLP_PETUGAS numeric,

@JENIS_PETUGAS varchar (50),

@GAJI int)

as update PETUGAS 

set NAMA_PETUGAS=@NAMA_PETUGAS, ALAMAT_PETUGAS=@ALAMAT_PETUGAS, NO_TLP_PETUGAS=@NO_TLP_PETUGAS, JENIS_PETUGAS=@JENIS_PETUGAS, GAJI=@GAJI where ID_PETUGAS=@ID_PETUGAS

go

exec update_petugas 'ptg6','ik2','mt2','yoyok','Jl. jarak gg 3','49102','Room','1500000'

# Procedure Insert Info Pembayaran (untuk menambahkan data pada table Info Pembayaran)

create procedure info

(@ID_TRANSAKSI varchar(20),

@DOWN_PAYMENT int,

@KAMAR_PERMALAM int,

@SEWA_METROM int)

as begin

insert into INFO_PEMBAYARAN values (@ID_TRANSAKSI, @DOWN_PAYMENT, @KAMAR_PERMALAM, @SEWA_METROM);

end

go

exec info 'tr6','0','2','0';

# Procedure Insert MELAKUKAN (untuk menambahkan data pada table Melakukan)

create procedure sp_melakukan

(@ID_TRANSAKSI varchar(20),

@ID_TAMU char(10))

as begin

insert into MELAKUKAN values (@ID_TRANSAKSI, @ID_TAMU);

end

go

exec sp_melakukan 'tr6','it6';

FUNCTION :

# Function Total Pembayaran 

CREATE FUNCTION TRANSAK

(@JENIS_KAMAR varchar(20),

@JUMLAH_KAMAR integer,

@HARGA_KAMAR integer,

@KAMAR_PERMALAM integer,

@HARGA_RUANGAN integer,

@SEWA_METROM integer)

RETURNS INT

AS BEGIN

declare @TOTAL_PEMBAYARAN integer

set @TOTAL_PEMBAYARAN= (select (KAMAR.JUMLAH_KAMAR*KAMAR.HARGA_KAMAR)*(INFO_PEMBAYARAN.KAMAR_PERMALAM)+(MEETING_ROOM.HARGA_RUANGAN*INFO_PEMBAYARAN.SEWA_METROM)

"TOTAL_PEMBAYARAN"

from INFO_PEMBAYARAN, MEETING_ROOM, KAMAR)

return @TOTAL_PEMBAYARAN

END

-

select (KAMAR.JUMLAH_KAMAR*KAMAR.HARGA_KAMAR)*(INFO_PEMBAYARAN.KAMAR_PERMALAM)+(MEETING_ROOM.HARGA_RUANGAN*INFO_PEMBAYARAN.SEWA_METROM)

"TOTAL_PEMBAYARAN" from INFO_PEMBAYARAN, MEETING_ROOM, KAMAR

-

select ID_TRANSAKSI, ID_KAMAR, ID_METROOM, JUMLAH_KAMAR, HARGA_KAMAR, KAMAR_PERMALAM, HARGA_RUANGAN, SEWA_METROM,dbo.TRANSAK(JUMLAH_KAMAR, HARGA_KAMAR, KAMAR_PERMALAM, HARGA_RUANGAN, SEWA_METROM) as TOTAL_PEMBAYARAN from INFO_PEMBAYARAN, KAMAR, MEETING_ROOM

select ID_TRANSAKSI, ID_KAMAR, ID_METROOM, dbo.TRANSAK (KAMAR.JUMLAH_KAMAR,KAMAR.HARGA_KAMAR,INFO_PEMBAYARAN.KAMAR_PERMALAM,MEETING_ROOM.HARGA_RUANGAN,INFO_PEMBAYARAN.SEWA_METROM)

AS TOTAL_PEMBAYARAN from INFO_PEMBAYARAN, MEETING_ROOM, KAMAR

# Function Jumlah Transaksi (untuk menjumlahkan semua jumlah Transaksi yang ada)

CREATE FUNCTION JUMLAH_TRANSAKSI_YANG_ADA (@JUMLAH_TRANSAKSI varchar(50))  RETURNS integer  BEGIN select @JUMLAH_TRANSAKSI=count(ID_TRANSAKSI) from INFO_PEMBAYARAN; RETURN @JUMLAH_TRANSAKSI; END

select count(ID_TRANSAKSI) as JUMLAH_TRANSAKSI from INFO_PEMBAYARAN

# Function Jumlah Gaji Petugas (untuk menjumlahkan semua jumlah gaji Petugas)

CREATE FUNCTION JUMLAHGAJI_PETUGAS (@JUMLAH_GAJI varchar(50))  RETURNS integer  BEGIN select @JUMLAH_GAJI=sum(GAJI) from PETUGAS; RETURN @JUMLAH_GAJI; END

select sum(GAJI) as JUMLAH_GAJI from PETUGAS

MELALUI ORACLE:

PROCEDURE:

#PROSEDURE INSERT_DATA_KAMAR

create or replace 

PROCEDURE INSERT_DATA_KAMAR (ID_KAMAR in char, JUMLAH_KAMAR in char, JENIS_KAMAR in char, HARGA_KAMAR in number)

AS

BEGIN

INSERT INTO KAMAR VALUES (ID_KAMAR, JUMLAH_KAMAR, JENIS_KAMAR, HARGA_KAMAR);

commit;

END;

CALL INSERT_TAMU_HOTEL ('it6','tr6','pesek','jl.raya selatan','08929890298');

SELECT * FROM TAMU;

#PROCEDURE INSERT_DATA_MELAYANI

create or replace 

PROCEDURE INSERT_DATA_MELAYANI(ID_PETUGAS IN VARCHAR, ID_KAMAR IN CHAR )

AS

BEGIN

INSERT INTO MELAYANI VALUES (ID_PETUGAS, ID_KAMAR);

COMMIT;

END;

CALL INSERT_TAMU_HOTEL ('ptg6','ik2');

SELECT * FROM MELAYANI;

#PROCEDURE INSERT_DATA_PETUGAS

create or replace 

PROCEDURE INSERT_DATA_PETUGAS (ID_PETUGAS in varchar,NAMA in varchar, ALAMAT in varchar, NO_TELP in number, JENIS_PETUGAS char, GAJI int)

AS

BEGIN

INSERT INTO PETUGAS VALUES (ID_PETUGAS,NAMA, ALAMAT, NO_TELP, JENIS_PETUGAS, GAJI);

commit;

END;

CALL INSERT_TAMU_HOTEL ('ptg6','susi','jl.buahnaga','983900','Bell Boy','1500000');

SELECT * FROM PETUGAS;

#PROCEDURE INSERT_DATA_TRANSAKSI

create or replace 

PROCEDURE INSERT_DATA_TRANSAKSI (ID_TRANSAKSI IN CHAR, DOWN_PAYMENT IN CHAR, LAMA_INAP IN CHAR, TOTAL_PEMBAYARAN IN CHAR)

AS

BEGIN

INSERT INTO TRANSAKSI VALUES (ID_TRANSAKSI,DOWN_PAYMENT, LAMA_INAP, TOTAL_PEMBAYARAN);

COMMIT;

END;

CALL INSERT_TAMU_HOTEL ('tr6','0','2 malam','1500000');

SELECT * FROM TRANSAKSI;

#PROSEDURE UPDATE_MELAYANI

create or replace 

PROCEDURE UPDATE_MELAYANI (iID_PETUGAS IN VARCHAR, vID_KAMAR IN CHAR)

IS

BEGIN  

UPDATE MELAYANI

SET MELAYANI.ID_KAMAR=vID_KAMAR

WHERE (MELAYANI.ID_PETUGAS=iID_PETUGAS);

END;

CALL UPDATE_MELAYANI ('ptg6','ik3');

SELECT * FROM MELAYANI;

FUNCTION :

#FUNCTION TOTAL PETUGAS

create or replace

FUNCTION JUMLAH_GAJI_PETUGAS (GAJI in int)

RETURN integer 

AS iTOTAL integer;

BEGIN

SELECT SUM (GAJI) into iTOTAL FROM PETUGAS;

RETURN iTOTAL;

END JUMLAH_GAJI_PETUGAS;

SELECT SUM(GAJI) "iTOTAL" FROM PETUGAS;

#FUNCTION RATA-RATA GAJI

create or replace

FUNCTION RATA_RATA_GAJI_PETUGAS (GAJI in int)

RETURN integer 

AS iRATA_RATA integer;

BEGIN

SELECT AVG (GAJI) into iRATA_RATA FROM PETUGAS;

RETURN iRATA_RATA;

END RATA-RATA_GAJI_PETUGAS;

SELECT AVG (GAJI) "iRATA_RATA" FROM PETUGAS;

#FUNCTION GAJI MAKSIMAL

create or replace

FUNCTION GAJI_MAX_PETUGAS (GAJI in int)

RETURN integer 

AS iGAJI_MAX integer;

BEGIN

SELECT MAX(GAJI) into iGAJI_MAX FROM PETUGAS;

RETURN iGAJI_MAX;

END GAJI_MAX_PETUGAS;

SELECT MAX(GAJI) "iGAJI_MAX" FROM PETUGAS;

     

#FUNCTION GAJI MINIMAL

create or replace

FUNCTION GAJI_MIN_PETUGAS (GAJI in int)

RETURN integer 

AS iGAJI_MIN integer;

BEGIN

SELECT MIN(GAJI) into iGAJI_MIN FROM PETUGAS;

RETURN iGAJI_MIN;

END GAJI_MIN_PETUGAS;

SELECT MIN(GAJI) "iGAJI_MIN" FROM PETUGAS;

     

#FUNCTION TOTAL TRANSAKSI    

create or replace

FUNCTION JUMLAH_TRANSAKSI_YANG_ADA (GAJI in int)

RETURN integer 

AS iGAJI_COUNT integer;

BEGIN

SELECT COUNT(GAJI) into iGAJI_COUNT FROM PETUGAS;

RETURN iGAJI_COUNT;

END JUMLAH_TRANSAKSI_YANG_ADA;

SELECT COUNT(*) "JUMLAH_TRANSAKSI_YANG_ADA" FROM PETUGAS;