Laporan Praktikum DBD VIII : Fungsi, PL/SQL, dan Trigger

BAB I

Landasan Teori
Function
Fungsi dapat didefinisikan sebagai bagian dari perintah/statement yang merubah beberapa nilai masukkan menjadi nilai baru (keluaran/sahil). Beberapa fungsi telah tersedia dan tinggal dipakai (fungsi standar) dan dapat [ula dibuat sendiri sebagai fungsi baru (user defined function). Semua fungsi ditulis nama yang diikuti kurung (). Contoh fungsi power (a, b) untuk menghitung a pangkat b, fungsi left (st, n) untuk mengambil n huruf depan/terkiri dari st. Contoh pemanggilan :


Update mhs set inisial = left (namadepan,1) + left (namabelakang, 1). Mengisi kolom inisial pada tabel mhs dengan 2 huruf yang diambil dari 1 huruf namadepan dan 1 huruf namabelakang

select nama, ‘Hari Lahir ‘=datepart(dw,tgllahir) from mhs

Menampilkan nama dan hari lahir semua data mahasiswa
-------------------------------------------------------------------------------------

Kelompok Fungsi :
Fungsi standar yang tersedia, dikelompokkan sebagai berikut :
Fungsi untuk Konfigurasi
Fungsi untuk manipulasi kursor
Fungsi untuk tanggal & jam
Fungsi Matematika
Fungsi Agregat
Fungsi Metadata
Fungsi untuk Security
Fungsi untuk manipulasi string
Fungsi untuk Sistem
Fungsi untuk statistic
Fungsi untuk teks & gambar
-------------------------------------------------------------------------------------
Membuat Fungsi Sendiri :
Ada beberapa jenis fungsi yang dapat kita buat sendiri (User Defined Function), yaitu :
Inline Table – valued Function
Multi-statement– valued Function
Scalar– valued Function
-------------------------------------------------------------------------------------
Contoh Soal dan Jawaban :
Cari jumlah barang di faktur tertentu dengan parameter NoFJ(tipenya integer)

CREATE FUNCTION hitungjumlahbarang (@nofj varchar(12))
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO

select distinct nofj,dbo.hitungjumlahbarang(nofj) from fjdet
-------------------------------------------------------------------------------------
Cari Total Qty*Harga dari tabel FJDet, parameternya NoFJ(tipenya money)

CREATE FUNCTION total (@nofj varchar(12))
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(qty*harga) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO

select distinct nofj,dbo.total(nofj) from fjdet
-------------------------------------------------------------------------------------
Jumlah Nama Barang mengandung kata tertentu pada tabel Barang, parameternya Nama Barang (tipenya integer)

CREATE FUNCTION jumlahnamabarang (@nama varchar(50))
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from Barang
where namabarang like '%' + @nama + '%'
RETURN @Result
END
GO

select kodebarang,namabarang from barang
where namabarang like '%Roda%'
select distinct dbo.jumlahnamabarang('Roda') from Barang
-------------------------------------------------------------------------------------
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)
Fungsi PL/SQL
Procedural Language/Structure Query Language atau yang biasa disingkat dengan PL/SQL merupakan sebuah pengabungan antara bahasa pemrograman procedural (PL) dan SQL syntax, definisi dari PL/SQL sendiri adalah suatu blok yang berisi skrip-skrip bahasa peosedural, PL/SQL adalah fasilitas yang disediakan oleh Oracle sehingga pengguna dapat memanfaatkan konsep pemrograman. PL/SQL dapat digunakan sebagai perintah untuk memanipulasi data yang ada dalam database Oracle. PL/SQL membentuk pemrograman terstruktur dalam memproses data.

Beberapa kelebihan PL/SQL dalam database Oracle :

• PL/SQL dapat digunakan di server sehingga client hanya dapat mengakses didalam server.
• Penggunaan PL/SQL mudah dimengerti oleh setiap pengguna.
• PL/SQL dapat didesain khusus untuk database Oracle dalam menggunakan program aplikasi.

STRUKTUR PL/SQL
Struktur PL/SQL mirip dengan struktur bahasa pascal atau delphi yang menggunakan struktur blok, sehingga akan mempermudah pengertian dalam pemrograman dengan PL/SQL. Struktur Blok berisi perintah SQL dengan kondisi yang berbeda. Perintah PL/SQL dapat menangani kesalahan saat dijalankan. Setiap pengetikan dengan menggunakan PL/SQL dalam SQL*Plus selalu diakhiri dengan tanda /(slash). Sintaks penggunaan PL/SQL adalah sebagai berikut :

Declare
       Begin
       Exception
End

Struktur diatas dapat dijelaskan sebagai berikut :
1. Bagian Judul (Header)
Bagian ini hanya digunakan jika PL/SQL diberikan nama,misalnya untuk prosedur atau fungsi. Bagian ini berisi nama blok,daftar parameter, dan pengembalian hasil (return) jika blok adalahfungsi.

2. Bagian Deklarasi (declaration)
Bagian ini untuk membuat deklarasi mengenai semua variable dan konstanta yang direferensikan dalam pernyataan PL/SQL. Bagian deklarasi ini dimulai dengan perintah DECLARE. Jika tidak ada variable atau konstanta yang ingin dideklarasikan bagian ini boleh dihilangkan, bersifat optional.

3. Bagian Eksekusi (Execution).
Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan ditulis. Bagian eksekusi ini harus dimulai dengan perintah BEGIN.

4. Bagian Perkecualian (Exception)
Bagian ini memuat cara menangani kesalahan-kesalahan (error) pada waktu eksekusi program PL/SQL, bersifat optional. Jika program tidak memuat cara menangani kesalahan, bagian ini boleh dihilangkan.Setiap pernyataan PL/SQL harus diakhiri dengan tanda titik koma(;) dan semua program PL/SQL harus diakhiri dengan perintah END. Pada bagian deklarasi ditempatkan variable dan konstanta yang dipakai oleh pernyataan PL/SQL yang dibuat.

Perbedaan variabel dengan konstanta adalah :

• Konstanta adalah nilai ditentukan pada saat deklarasi dibuat sehingga nilainya tetap pada saat program dieksekusikan.
• Variabel dapat menerima nilai baru atau sebaliknya diubah pada saat program dieksekusi.

Sintaks dasar fungsi sebagai berikut:

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

Dalam fungsi, kita bisa memasukkan logika pemrograman. Beberapa diantaranya adalah penggunaan variabel, kendali kondisional, dan perulangan.

Declare
Pendeklarasian variabel memiliki sintaks seperti berikut:

DECLARE var_name [. var_name] . . . type [DEFAULT value]

Contohnya:

DECLARE nilai INT
                    DECLARE x, y INT DEFAULT

DBMS_OUTPUT merupakan paket yang disediakan oleh Oracle PL/SQL dan PUT_LINE merupakan salah satu prosedur yang dikemas. Menampilkan nilai-nilai pada SQL Plus * terminal yang harus diaktifkan dengan SERVEROUTPUT SET ON terlebih dahulu. Untuk menjalankan kode sampel ini, login ke SQL * Plus. PL/SQL blok diakhiri dengan tanda garis miring/atau garis byitself.

Trigger
Trigger adalah sebuah objek database yang diasosiasikan dengan sebuah tabel dan akan aktif (terpicu/trigger) ketika sebuah event terjadi pada tabel tersebut. Event adalah sebuah proses yang akan dilakukan oleh sistem ketika menemui suatu kejadian yang dipicu oleh perilaku dari luar sistem. Dalam sistem database perilaku di luar sistem yang memicu adanya event adalah pada saat user melakukan modifikasi data, maka secara otomatis trigger akan dijalankan. Trigger hanya akan terjadi ketika ada eksekusi INSERT,UPDATE,DELETE pada tabel yang bersangkutan.

Trigger dibuat sesuai dengan keperluan. Ada kalanya trigger perlu dibuat, dan kadangkala tidak perlu dibuat. Trigger perlu dibuat pada saat membentuk sebuah aksi tertentu terhadap suatu event dan memusatkan operasi global Trigger tidak perlu dibuat, jika fungsionalitas yang diperlukan ada pada Oracle server dan duplikat atau sama dengan fungsi trigger yang lain.

Trigger dapat digunakan untuk mengubah data sebelum proses INSERT dilakukan atau untuk memberikan nilai default. Misalnya mengubah data yang diluar nilai yang diperbolehkan. Misalnya, jika ada pengisian nilai di atas 100 maka akan dijadikan 100. Anda dapat menyimpan data suatu record ke tabel lain (misalnya history) sebelum data tersebut diupdate atau didelete. Sehingga semua perubahan data dapat dilacak semenjak data itu dibuat.

Trigger timing :
Untuk tabel : BEFORE, AFTER
Untuk view : INSTEAD OF
Trigger event : INSERT, UPDATE atau DELETE
Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger.
Tipe trigger : Baris atau Pernyataan (statement).
Klausa WHEN : untuk kondisi pembatasan.
Trigger body : bagian prosedur yang dituliskan pada trigger.

Create Trigger nama_trigger
           { Before | After }
           { INSERT | UPDATE | DELETE }
ON nama_tabel FOR EACH ROW
            statement-statement

BAB II

Hasil Praktikum
1. Buatlah fungsi konversi suhu dari Fahrenheit ke derajat celcius dengan rumus konversi sebagai berikut : celcius (a – 32) / 1.8 ! Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !

Untuk membuat konversi suhu dari farenheit ke celcius query fungsi yang digunakan sebagai berikut : CREATE FUNCTION konversi_suhu (suhu FLOAT) RETURNS FLOAT RETURN (1$-32)/1.8

2. Buatlah fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan nama mahasiswa. Kemudian jalankan dengan perintah SELECT !

Untuk mencari alamat berdasarkan nama mahasiswa query fungsi yang dipakai adalaj : create function alamat(a text) returns text return (select alamat_mah from mahasiswa where nama_mah = a);

3. Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa PL/SQL !

Nilai > 100 atau Nilai<0	Nilai Salah
Nilai ≥ 90	Nilai A
70 ≤ Nilai < 90	Nilai B
60 ≤ Nilai < 70	Nilai C
50 ≤ Nilai < 60	Nilai D
0 ≤ Nilai < 50	Nilai E

Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !

Berikut query untuk mencari nilai :
create function penilaian (a integer) returns text begin declare nilai text;
if a > 100 or a < 0 then set hasil = ''nilai_salah'';
elseif a >= 90 then set hasil = ''nilai_A'';
elseif a >= 70 and a < 90 then set hasil = ''nilai_B'';
elseif a >= 60 and a < 70 then set hasil = ''nilai_C'';
elseif a >= 50 and a < 60 then set hasil = ''nilai_D'';
else set hasil = ''nilai_E'';
end if;
return hasil;
end//

4. Buatlah fungsi menggunakan PL/PGSQL untuk mencari bilangan ganjil atau genap dari bilangan yang iinputkan. Kemudian jalankan dengan perintah SELECT !

5. Tambahkan kolom modifikasi pada tabel mahasiswa. Dimana setiap ada insert atau update maka tanggal pada kolom modifikasi akan menunjukkan tanggal perubahan tersebut dilakukan.

Untuk memasukkan tanggal secara otomatis dapat menggunakan query :

create trigger test before insert on mahasiswa for each row begin set new.tanggal = current()

create trigger test_upd before update on mahasiswafor each row begin set new.tanggal = current()

BAB III

Perbandingan MySQL dan Postgres

Pernyataan parameternya yang diberikan harus jelas, dan harus ditambah variabel baru. Pada postgres tidak perlu menambahkan variabel tapi langsung ada type-nya, sedangkan pada MySQL harus ditambahkan nama variabel sendiri. Untuk postgres dipakai bahasa  PL/SQL atau PL/PGSWL, sedangkan di MySQL tidak ada bahasa PL/SQL atau PL/PGSWL. Pada query if dan else if dalam postgre menggunakan select into variable(values) sedangkan dalam MySQL query if dan elseif menggunakan then return concat(values).

Kesimpulan

Perbedaan query antara kedua DBMS ini sangat kelihatan. Di postgres kita dapat menggunakan fungsi dibarengi dengan language ‘sql’ atau fungsi yang lebih kompleks lagi dengan language ‘plpgsql’ untuk mengakhiri statement dari fungsi yang dibuat. Sedangkan pada MySQL tidak memerlukan hal tersebut, tetapi menggunakan delimiter karena statementnya banyak. Sebab jika tidak begitu sebuah fungsi tidak akan berhasil dibuat.

Selanjutnya perbedaan pada perintah trigger, perbedaannya adalah pada trigger eventnya. Dalam Postgre trigger eventnya bisa langsung diisi dua statement seperti insert or update. Sedangkan pada MySQL harus membuat trigger event secara terpisah, jadi pertama membuat trigger insert dan kedua membuat trigger update.

Berdasarkan materi yang disajikan dapat ditarik kesimpulan sebagai berikut:

• Stored Procedure/Function merupakan kumpulan perintah SQL yang diberi nama dan disimpan di server SQL. Stored Procedure biasanya berisi perintah-perintah umum yang berhubungan dengan database, baik perintah DDL (data definition language) maupun DML (data manipulation language).

Trigger merupakan kumpulan perintah SQL yang secara otomatis dijalankan untuk merespon sebuah perintah tertentu. Biasanya, secara fisik trigger menjadi satu dengan table atau view.

Kritik dan Saran

Banyak perbedaan pada antara query yang terdapat di postgreSQL dan mySQL. Sehingga menuntut agar pengguna tidak mudah menyerah dan terus mencoba memahami perbedaan kedua query dari DBMS yang berbeda agar dapat menyesuaikan dengan lingkungan kerja nantinya yang belum tentu menggunakan satu DBMS yang kita kuasai saja.

Harapan

Semoga para pembaca dapat mempraktikkan kedua DBMS dengan baik.

Daftar Pustaka

http://bahasagundar.blogspot.com/2012/04/pengenalan-plsql.html
http://apriliawakhyuni.blogspot.com/2010/05/pengenalan-plsql.html
http://uzi-online.blogspot.com/2009/08/definisi-plsql.html
http://database-alkaizer.blogspot.com/2011/01/function.html
http://www.slideshare.net/oenkpup/pemograman-basis-data-trigger
http://www.rey1024.com/2008/05/pengenalan-trigger-function-procedure-dan-view-pada-mysql/