LANDASAN TEORI

FUNGSI, PL/PGSQL, DAN TRIGGER

A.1 Fungsi dalam perspektif PostgreSQL

Fungsi SQL adalah sebuah kumpulan query, biasanya query yang detail dan panjang yang dibungkus menjadi

satu dan disimpan dalam database dan kemudian apabila diperlukan hanya tinggal mengaksesnya tanpa

mengetikan query detail.
Sedangkan untuk memunculkan fungsi dapat menggunakan query \df. Ada beberapa konsep yang menarik dari

fungsi antara lain:
• Bahasa yang dipakai dapat didefenisikan sendiri dengan tersedianya parameter LANGUAGE, tanpa harus

mengkompilasi ulang PostgreSQL.
• Kita dapat membuat dua buah fungsi dengan nama yang sama namun parametermasukkannya yang berbeda,

baik tipe data maupun jumlahnya.

Ada beberapa faktor yang perlu diperhatikan dalam membuat fungsi antara lain:
• Nama Fungsi
• Nomor dari fungsi argument
• Tipe data dari setiap argument
• Tipe dari hasil fungsi
• Fungsi action
• Bahasa yang digunakan oleh fungsi action.
Berikut contoh sederhana pembuatan fungsi SQL untuk perkalian dari tiga inputan :

db_personal=> CREATE FUNCTION perkalian (FLOAT, FLOAT, FLOAT)
db_personal-> RETURNS FLOAT
db_personal-> AS ‘SELECT ($1 + $2) * $3;’
db_personal-> LANGUAGE ‘sql’;
CREATE

db_personal=> SELECT perkalian (10,10,10);
perkalian
———–
200
(1 row)
A.2 Fungsi dalam Perspektif MySQL

Function dan Stored Procedure merupakan fitur utama yang paling penting di MySQL 5. Function dan Stored Procedure merupakan suatu kumpulan perintah atau statement yang disimpan dan dieksekusi di server database MySQL. Dengan SP (Stored Procedure), kita dapat menyusun program sederhana berbasis sintaks SQL untuk menjalankan fungsi tertentu. Hal ini menjadikan aplikasi yang kita buat lebih efektif dan efisien.
Berikut ini beberapa keuntungan menggunakan Stored Procedure:
 Lebih cepat. Hal ini karena kumpulan perintah query dijalankan langsung diserver. Berbeda dengan jika dijalankan secara sekuensial di bahasa pemrograman, akan lebih lambat karena harus “bolak-balik” antara client dan server.
 Menghilangkan duplikasi proses, pemeliharaan yang mudah. Pada dasarnya operasi yang terjadi di suatu aplikasi terhadap database adalah sama. Secara umum, di alam aplikasi biasanya terdapat operasi untuk validasi data inputan, menambahkan record baru, mengubah record, menghapus record dan sebagainya. Dengan SP, mungkin kita
dapat menghindari adanya duplikasi proses yang kurang lebih sama, sehingga pemeliharaannya juga jadi lebih mudah.
 Meningkatkan keamanan database. Dengan adanya SP, database akan lebih aman karena aplikasi yang memanggil SP tidak perlu mengetahui isi di dalamnya. Sebagai contoh, dalam proses menambahkan data (insert), kita membuat suatu SP khusus. Dengan demikian, saat client atau aplikasi akan menambahkan data (insert) maka tidak perlu tahu nama tabelnya, karena hanya cukup memanggil SP tersebut dengan mengi rimkan parameter yang diinginkan.
Selanjutnya, Stored Procedure dari segi bentuk dan sifatnya terbagi menjadi 2 (dua), yaitu FUNCTION dan PROCEDURE. Perbedaan utama antara function dan procedure adalah terletak
pada nilai yang dikembalikannya (di -return). Function memiliki suatu nilai yang dikembalikan (di-return), sedangkan procedure tidak. Umumnya suatu procedure hanya berisi suatu kumpulan proses yang tidak menghasilnya value, biasanya hanya menampilkan saja. Sebagai catatan bahwa dalam modul ini jika terdapat istilah SP (Stored Procedure) maka yang dimaksud adalah Function dan Procedure.

Sebagai contoh sederhana, kita akan membuat suatu SP yang akan menampilkan string “Hello World!” di layar hasil. Berikut ini perintah query untuk membuat SP tersebut:

DELIMITER $$ CREATE PROCEDURE hello()
BEGIN
SELECT “Hello World!”;
END$$
DELIMITER ;

Untuk memanggil procedure tersebut, gunakanlah CALL. Berikut ini contoh pemanggilan procedure dan hasil tampilannya:

CALL hello();
Hasilnya sebagai berikut:
+————–+
| Hello World! |
+————–+
| Hello World! |
+————–+

Membuat SP
Untuk membuat SP baru, berikut ini bentuk umumnya:
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

Contoh 1. Procedure untuk menghitung jumlah pelanggan

DELIMITER $$
CREATE PROCEDURE jumlahPelanggan()
BEGIN
SELECT COUNT(*) FROM pelanggan;
END$$
DELIMITER ;

Cara pemanggilan dari procedure diatas adalah dengan menggunakan CALL jumlahPelanggan(). Hasilnya akan ditampilkan jumlah record dari table pelanggan.

Berikut ini bentuk lain dari contoh diatas:

DELIMITER $$
CREATE PROCEDURE jumlahPelanggan2(OUT hasil AS INT)
BEGIN
SELECT COUNT(*) INTO hasil FROM pelanggan;
END$$
DELIMITER ;

Pada bentuk procedure yang kedua di atas (jumlahPelanggan2), kita menyimpan hasil dari procedure ke dalam satu variabel bernama hasil yang bertipe INT. Perbedaan dari kedua bentuk di atas adalah, pada bentuk kedua, kita dapat memanggil procedure dengan SELECT, sedangkan pada yang pertama tidak bisa. Berikut ini contoh pemanggilan untuk procedure yang kedua:

mysql> CALL jumlahPelanggan2(@jumlah);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @jumlah AS `Jumlah Pelanggan`;
+——————+
| Jumlah Pelanggan |
+——————+
| 5 |
+——————+
1 row in set (0.02 sec)

Contoh 2. Procedure untuk menghitung jumlah item barang yang pernah dibeli oleh satu pelanggan.

DELIMITER $$
CREATE PROCEDURE
jumlahItemBarang (pelanggan VARCHAR(5))
BEGIN
SELECT SUM(detil_pesan.jumlah)
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
AND pesan.id_pelanggan=pelanggan;
END$$
DELIMITER ;

Contoh 3. Function untuk menghitung jumlah produk yang tersedia (stock) untuk satu produk tertentu.

DELIMITER $$
CREATE FUNCTION jumlahStockBarang(produk VARCHAR(5))
RETURNS INT
BEGIN
DECLARE jumlah INT;
SELECT COUNT(*) INTO jumlah FROM produk
WHERE id_produk=produk;
RETURN jumlah;
END$$
DELIMITER ;

Untuk memanggil suatu function, kita tidak menggunakan CALL, tetapi langsung dapat memanggil dengan SELECT. Berikut ini contoh pemanggilan untuk fungsi di atas.

SELECT jumlahStockBarang(‘B0001′);
Dan berikut ini hasilnya:
+—————————-+
| jumlahStockBarang(‘B0001′) |
+—————————-+
| 1 |
+—————————-+

Mengubah SP
Untuk mengubah SP yang sudah ada, berikut ini bentuk umumnya:
ALTER {PROCEDURE | FUNCTION} sp_name
[characteristic ...]

Menghapus SP
Untuk menghapus SP yang sudah ada, berikut ini bentuk umumnya:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Sintaks Dasar dalam SP
SP dapat dikatakan sebagai bahasa pemrograman yang berada di dalam database. Oleh karena itu, tentunya terdapat sintaks-sintaks tertentu berhubungan dengan SP tersebut, misalnya bagaimana untuk mendeklarasikan variabel, penyeleksian kondisi, perulangan dsb. Pada bagian ini akan diuraikan beberapa sintaks dasar SP yang didukung oleh MySQL.

Variabel digunakan untuk menyimpan suatu nilai secara temporer (sementara) di memory. Variabel akan hilang saat sudah tidak digunakan lagi. Variabel dalam MySQL sebelum dapat digunakan, pertama kali harus dideklarasikan terlebih dahulu. Berikut ini bentuk umum pendeklarasian suatu variabel di MySQL:

DECLARE variable_name DATATYPE [DEFAULT value];
Contohnya:
DECLARE jumlah INT;
DECLARE kode VARCHAR(5);
DECLARE tgl_lahir DATE DEFAULT ‘1982-10-20’;

Setelah dideklarasikan, suatu variabel dapat diisi dengan suatu nilai sesuai dengan tipe data yang didefinisikan saat pendeklarasian. Untuk mengisikan nilai ke dalam suatu variabel, digunakan perintah SET. Format umumnya sebagai berikut:

SET variable_name = expression|value;
Contohnya:
SET jumlah = 10;
SET kode = (SELECT id_pelanggan FROM pelanggan LIMIT 1);
SET tgl_lahir = now();

Berikut ini contoh function hitungUmur() untuk menghitung umur seseorang saat ini berdasarkan tahun kelahiran yang diberikan.

DELIMITER $$
CREATE FUNCTION hitungUmur (lahir DATE)
RETURNS INT
BEGIN
DECLARE thn_sekarang, thn_lahir INT;
SET thn_sekarang = YEAR(now());
SET thn_lahir = YEAR (lahir);
RETURN thn_sekarang – thn_lahir;
END$$
DELIMITER ;

Dengan adanya fasilitas penyeleksian kondisi, kita dapat mengatur alur proses yang terjadi dalam database kita. Di MySQL, penyeleksian kondisi terdiri dari IF, IF…ELSE dan CASE. Berikut ini bentuk umum ketiga perintah tersebut: bBerikut ini contoh penggunaan perintah IF dalam fungsi cekPelanggan() dimana fungsi ini memeriksa apakah pelanggan sudah pernah melakukan t ransaksi pemesanan barang. Jika sudah pernah, tampilkan pesan berapa kali melakukan pemesanan, jika belum tampilkan pesan belum pernah memesan.

DELIMITER $$
CREATE FUNCTION cekPelanggan (pelanggan varchar(5))
RETURNS VARCHAR (100)
BEGIN
DECLARE jumlah INT;
SELECT COUNT(id_pesan) INTO jumlah FROM pesan
WHERE id_pelanggan=pelanggan;
IF (jumlah > 0) THEN
RETURN CONCAT(“Anda sudah bertransaksi sebanyak “,
jumlah, ” kali”);
ELSE
RETURN “Anda belum pernah melakukan transaksi”;
END IF;
END$$
DELIMITER ;

Dan berikut ini contoh penggunaan perintah CASE dalam fungsi getDiskon() dimana fungsi ini menentukan diskon berdasarkan jumlah pesanan yang dilakukan.

DELIMITER $$
CREATE FUNCTION getDiskon(jumlah INT) RETURNS int(11)
BEGIN
DECLARE diskon INT;
CASE
WHEN (jumlah >= 100) THEN
SET diskon = 10;
WHEN (jumlah >= 50 AND jumlah < 100) THEN
SET diskon = 5;
WHEN (jumlah >= 20 AND jumlah < 50) THEN
SET diskon = 3;
ELSE SET diskon = 0;
END CASE;
RETURN diskon;
END$$
DELIMITER ;
A.3 Pendukung Fungsi :

PostgreSQL memiliki fungsi yang dapat mengubah suatu nilai dalam suatu kolom atau barismenjadi huruf besar. Fungsi tersebut bernama upper(nama_kolom), berfungsi memanggilfungsi upper dengan nama_kolom sebagaii argumen sehingga menghasilkan nilai pada kolom dalam huruf besar. Berikut Struktur SQL untuk menampilkan data dalam
huruf besar semua :
SELECT upper ([nama kolom]) FROM [nama tabel];
Berikut struktur SQL untuk menampilkan data dimana huruf pertama saja yang besar :
SELECT initcap ([nama kolom]) FROM [nama tabel];
A.4 Fungsi PL/PGSQL

PL/PGSQL merupakan bahasa yang lain untuk membuat sebuah fungsi, biasanya bahasa ini digunakan untuk menangani fungsi yang lebih kompleks. Pl/pgsql sudah terdapat dalam instalasi PostgreSQL.
Keuntungan penggunaan Fungsi PL/PGSQL :
1. Meningkatkan kinerja karena mengurangi pengiriman kode dari klien ke server.
2. Meningkatkan keamanan karena pengaksesan data tertentu ditangani dalam server.
3. Meningkatkan konsistensi data saat sejumlah aplikasi memanggil prosedur yang sama;
Sebaliknya kelemahannya yaitu server akan lebih terbebani karena banyak proses yang harus ditangani. Sedangkan query PL/PGSQL agar lebih mudah di pahami akan dibagi menjadi 2 yaitu pembuatan fungsi dan pembuatan definisi
- Berikut Struktur pembuatan fungsi dalam pl/pgsql

1. Pembuatan fungsi :

CREATE [OR REPLACE] FUNCTION nama_fungsi ( argtype , … ])
RETURNS tipe_data
AS ‘definisi’
LANGUAGE ‘plpgsql’;

2. Pembuatan definisi :

DECLARE nama_variable tipe_data /* deklarasi variabel, type */
BEGIN
/* prosedural dan SQL masuk disini seperti select, update dan sebagainya*/
Return nama_variable /* blok ini yang wajib */
END;

- Menghapus fungsi :
DROP FUNCTION nama_fungsi(paramater, parameter, parameter … );
Contoh :
DROP FUNCTION pembagian(text);

Berikut ini implementasi dari contoh penggunaan fungsi dengan bahasa PL/PGSQL:

db_personal=> CREATE FUNCTION pl_caripegawai (integer)
db_personal-> RETURNS text
db_personal-> AS ‘DECLARE hasil TEXT;
db_personal’> BEGIN
db_personal’> SELECT INTO hasil nama as nama_mahasiswa
db_personal’> FROM pegawai
db_personal’> WHERE id = $1;
db_personal’> RETURN hasil;
db_personal’> END;’
db_personal-> LANGUAGE ‘plpgsql’;
CREATE

db_personal=> SELECT pl_caripegawai (1);
pl_caripegawai
—————–
Hendro
(1 row)
db_personal=> SELECT pl_caripegawai (2);
pl_caripegawai
——————–
Tika
(1 row)