Fungsi, PL/PGSQL dan Trigger

FUNGSI, PL/PGSQL DAN TRIGER

A. LANDASAN TEORI

1. FUNGSI-FUNGSI DALAM MYSQL

      Fungsi merupakan suatu rutin khusus yang disediakan oleh MYSQL untuk melakukan manipulasi suatu data. Bentuk umum : nama_fungsi(argumen1, argumen2,...,...); Argumen1, argumen2 adalah argumen atau parameter yang dibutuhkan oleh fungsi. Fungsi digunakan sebagai bagian dari perintah select. Select fungsi(ekspresi) from nama_tabel; from nama_tabel tidak harus diisi.
Fungsi-fungsi yang dibahas dilampiran ini hanya fungsi-fungsi umum yang sering digunakan saja. Fungsi-fungsi tersebut dibagi menjadi beberapa kategori dan disusun secara alfabet sbg berikut :

• Fungsi String (String Function)
• Fungsi Numerik (Numeric Function)
• Fungsi Tanggal dan Waktu (Date and Time Function)
• Fungsi Pembanding (Comparison Function)
• Fungsi Agregate (Agregate Function)
• Fungsi Lain-Lain (Miscellaneous Functions)

Penulisan fungsi yang benar adalah nama_fungsi( ), dan bukannya nama_fungsi ( ). perhatikan bahwa posisi tanda kurung ( ) selalu menempel dengan nama fungsinya. Bila tidak, maka Mysql akan menampilkan pesan eror.

Beberapa simbol digunakan dalam penulisan fungsi dengan maksud :

* Simbol kurung siku "[" dan "]" digunakan untuk mengapit fungsi yang bersifat pilihan (optional). Bila ada lebih dari satu fungsi didalam tanda kurung siku, maka hanya salah satu dari fungsi tersebut yang dapat dipilih.

* Simbol kurung kurawal "{" dan "}" digunakan untuk mengapit fungsi yang bersifat mutlak. Bila ada lebih dari satu fungsi didalam tanda kurung kurawal, maka anda harus memilih salah satu dari pilihan yang ada.

* Simbol garis tegak lurus "|" digunakan sebagai pemisah antara pilihan yang ada.

* Simbol titik berulang "...." menandakan adanya pengulangan fungsi sebelumnya.
* Fungsi MySQL ditulis dengan huruf kapital tebal. Sedangkan nama database, tabel dan sebagainya ditulis dengan huruf biasa miring.

>> Fungsi String (String Functions)
* ASCII (string)- digunakan untuk memberikn nilai ASCII dari sebuah karakter. Bila yang dimasukkan adalah sebuah kata atau sederetan huruff/karakter, maka yang diberikan nilai ASCII-nya adalah karakter pada urutan paling kiri.

* CHAR (Numerik1)- memberikan bentuk ASCII dari suatu bilangan. Bila yang dimasukkan adalah NULL, maka hasilnya selalu diabaikan.

* BIN (numerik)- Memberikan bentuk binary dari suatu bilangan. Bila yang dimasukkab adalah karakter hurf, maka hasilnya selalu 0.

* OCT (numerik)- Menghasilkan n menjadi format bilangan oktal. Alematif fungsi ini adalah dengan menggunakan fungsi CONV.

>> Fungsi Tanggal dan Waktu (Date and Time Functions)

* CURDATE( )- Menghasilkan tanggal sistem/komputer saat ini, dengan format 'yyyy-mm-dd' atau 'yyyymmdd'.

* CURRENT_DATE- fungsi ini menghasilkan tanggal sistem saat ini, dengan format tampilan 'yyyy-mm-dd' atau 'yyyymmdd'. Fungsi current_date sama dengan fungsi curdate( ), hanya saja tidak menggunakan tanda kurung seperti layaknya format penulisan fungsi lainnya. dan tisak bisa melakukan matematis penambahan dan pegurangan tanggal.

* CURRENT_TIME- Fungsi ini akan menghasilkan jam saat ini dengan format tampilan 'hh-mm-ss' atau 'hhmmss'.

* CURRENT_TIMESTAMP- Fungsi ini menghasilkan tanggal dan jam saat ini,

Berikut adalah pembuatan fungsi perkalian 3 inputan :

CREATE FUNCTION perkalian (FLOAT, FLOAT, FLOAT)

RETURNS FLOAT

AS 'SELECT' ($1 + $2) * $3;'

LANGUAGE 'sql';

2. FUNGSI PL/PLSQL

       PL/PGSQL adalah prosedural language yang loadable (dapat langsung digunakan tanpa restart server) pada sistem database 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,

Kelemahannya adalah :

Server akan lebih terbebani karena banyak proses yang harus ditangani. Sedangkan query PL/PGSQL agar lebih mudah dipahami akan dibagi menjadi 2 yaitu pembuatan fungsi dan pembuatan definisi.

**Berikut struktur pembuatan fungsi dalam pl/pgsql

-- Pembuatan fungsi

CREATE [OR REPLACE] FUCNTION nama_fungsi (argtype,..])
RETURNS tipedata

AS 'definisi'

LANGUAGE 'plpgsql';

-- Pembuatan definisi :

DECLARE nama_variable tipe_data //deklarasi variabel, type//

BEGIN

//prosedural dan SQL masuk disini seperti select, update, dan sbg//

RETURN nama_variable //blok ini yang wajib diisi//

END;

-- Menghapus Fungsi :

DROP FUNCTION nama_fungsi (parameter, parameter, parameter...)

ada beberapa yang harus diperhatikan pada bahasa ini:

* Declaration - Digunakan untuk mendeklarasikan variabel yang digunakan dalam fungsi.

syntax umumnya adalah :

name [constant] type [not null] [{ default | : - } expression ];

* Alias untuk parameter fungsi - Digunakan untuk memberikan nama pada parameter fungsi. Secara default nama parameter adalah angka yang urut sesuai definisi parameter fungsi.

* Tipe data dinamis - tipe data variable tersebut dapat berubah saat function dijalankan, tipe data seperti ini banyak dibutuhkan jika kita ingin membuat variable yang menampung data dari sebuah kolom pada suatu tabel.

syntax yang digunakan :

variable%type

user_id user.user_id%type;

* Return, Exit - merupakan hasil value dari fungsi.

3. TRIGGER

      Trigger adalah blok pl/sql atau prosedur yang berhubungan dengan table, view, skema, atau database yang dijalankan secara implisit pada saat terjadi sebuah event. Trigger merupakan stored procedure yang dijalankan secara automatis saat user melakukan modifikasi pada tabel. Modifikasi data yang dilkukan pada tabel yaitu berupa perintah insert, update, delete, dan mereka bisa digabung menjadi satu dinamakan Multiple Trigger.

Tipe dari Trigger adalah :

- Aplication Trigger : Diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi.

- Database Trigger : Diaktifkan pada saat terjadi event yang berhubungan dengan data (spt operasi DML) atau event yang berhubungan dengan sistem.

Trigger perlu dibuat saat :

- Membentuk sebuah aksi tertentu terhadap suatu event.

- Memusatkan operasi global.

Trigger tidak perlu dibuat jika :

- Fungsionalitas yang diperlukan suatu ada pada oracle server.

- Duplicat atau sama dengan fungsi trigger yang lain.

Syntax penulisan dari database trigger :

1. Trigger Timing

- Untuk tabel : BEFORE, AFTER.

- Untuk baris : INSTEAD OF

2. Trigger Event : INSERT, UPDATE, DELETE.

3. Nama Tabel : yaitu nama tabel atau view yang berhubungan dengan trigger. 

4. Tipe Trigger : Baris atau pernyataan (statement)

5. Klausa When : Untuk kondisi pembatasan.

6. Trigger Body : Bagian procedure yang dituliskan pada trigger.

Syntax pembuatan Trigger :

CREATE TRIGGER (nama_trigger)

(BEFORE | AFTER) (DELETE | INSERT | UPDATE)

ON nama_tabel

FOR EACH ROW statement

B. TUJUAN 

- Mahasiswa memahami dan menggunakan fungsi dalam database PostgreSQL.

- Mahasiswa memahami dasar penggunaan PL/PGSQL dan Trigger.

C. HASIL PRAKTIKUM

1. Buatlah fungsi konversi suhu dari fahrenheit ke derajat celcius dengan rumus konversi sebagai berikut : celcius = (5*(nilai Fahreinheit-32))/9! Kemudian jalankan hasilnya dengan select fungsi tersebut.

Pertama kita harus membuat delimiter terlebih dahulu yakni :

delimiter //

mysql> create function konversi_suhu (x float)
       -> returns float
       -> return ((x-32)/1.8);

Untuk mengecek suhunya.

select konversi_suhu (32);

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

mysql> create function cari_mahasiswa (x varchar(20))

        -> returns varchar(20)

        -> begin

        -> declare hasil varchar(20);

       -> select alamat_mah as alamat_mahasiswa into hasil

        -> from mahasiswa

        -> where nama_mah=x;

        -> return hasil;

        -> end;

        -> //

Untuk mencari mahasiswa

mysql> select cari_mahasiswa ('sinta');
      -> //

3. Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql!

Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut!

mysql> create function nilai (x integer)
       -> returns text
       -> begin
       -> declare hasil varchar(20);
       -> if x>100 or x<0 then select "Nilai Salah" into hasil;
       -> elseif x>=90 then select "Nilai A" into hasil;
       -> elseif x>=70 then select "Nilai B" into hasil;
       -> elseif x>=60 then select "Nilai C" into hasil;
       -> elseif x>=50 then select "Nilai D" into hasil;
       -> else select "Nilai E" into hasil;
       -> end if;
       -> return hasil;
       -> end;
       -> //

Untuk mengecek nilai..

4. Buatlah fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari bilangan yang diinputkan. Kemudian jalankan dengan perintah SELECT!

mysql> create function cari_bil (x integer)
         -> returns text
         -> begin
         -> declare hasil_text;
         -> if x%2=0 then select "Bilangan Genap" into hasil;
         -> else select "Bilangan Ganjil" into hasil;
         -> end if;
         -> return hasil;
         -> end;
         -> //

Untuk mengecek bilangan genap dan ganjil

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

pertama buatlah delimiter menjadi default dengan cara

delimiter ;

Kemudian tambahkan kolom

alter table mahasiswa add modifikasi date;

lalu buatlah delimiter lagi dengan cara

delimiter // 

kemudian lakukan perintah untuk membuat trigger insert. 

mysql> create trigger modifikasi                  -> before insert                  -> for each row       
         -> begin       
         -> set new.modifikasi = sysdate();       
         -> end;        
         -> //

Ini syntax untuk membuat trigger untuk update

mysql> create trigger modifikasi2
         -> before update
         -> on mahasiswa
         -> for each row
         -> begin
         -> set new.modifikasi = sysdate();
         -> end;
         -> //

Lakukan percobaan pada insert. Masukkan data

mysql> insert into mahasiswa (nim_mah, nama_mah, alamat_mah, no_telp, id_fak, gender) values (23, 'visa', 'banyuwangi', '087533225666', 2, 'P');

         -> //

Ini tampilannya setelah diinsertkan.

Selanjutnya adalah mengupdate data.

mysql> update mahasiswa set alamat_mah = 'banyumas' where nim_mah = 18;        -> //

Ini untuk melakukan pengecekan setelah mengupdate data.

D. PERBANDINGAN POSTGRE DAN MYSQL


-- Untuk membuat fungsi konversi suhu
Pada Postgre

postgres=#  create function konversi_suhu (float)

postgres-#   returns float

postgres-#   as 'select (($1-32)/1.8);'

postgres-#   language 'sql';

Pada Mysql

mysql> create function konversi_suhu (x float)

        -> returns float

        -> return ((x-32)/1.8);

-- Untuk membuat fungsi pencarian alamat mahasiswa berdasarkan nama.

Pada Postgre

postgres=#  create function cari_mahasiswa (varchar)

postgres-#   returns text

postgres-#   as 'select (alamat_mah) from mahasiswa;'

postgres-#   language 'sql';

Pada Mysql

mysql> create function cari_mahasiswa (x varchar(20))

        -> returns varchar(20)

        -> begin

        -> declare hasil varchar(20);

        -> select alamat_mah as alamat_mahasiswa into hasil

        -> from mahasiswa

        -> where nama_mah=x;

        -> return hasil;

        -> end;

        -> //

-- Untuk mencari fungsi nilai dengan menggunakan bahasa pl/sql seperti pada soal nomer 3.

Pada Postgre

postgres=# create function nilai

postgres-#  returns text

postgres-#  as 'declare nilai_hasil

postgres'#  begin

postgres'#  case

postgres'#  begin

postgres'#  case

postgres'#  when $1 > 100 or $1 < 0 then nilai_hasil = ''nilai salah'';

postgres'#  when $1 >= 90 then nilai_hasil= ''nilai A'';

postgres'#  when $1 >= 70 then nilai_hasil = ''nilai B'';

postgres'#  when $1 >= 60 then nilai_hasil = ''nilai C'';

postgres'#  when $1 >= 50 then nilai_hasil = ''nilai D'';

postgres'#  else nilai_hasil = ''nilai E'';

postgres'#  end case;

postgres'#  return nilai_hasil;

postgres'#  end;'

postgres-#  language 'plpgsql';

Pada Mysql

mysql> create function nilai (x integer)

        -> returns text

        -> begin

        -> declare hasil varchar(20);

        -> if x>100 or x<0 then select "Nilai Salah" into hasil;

        -> elseif x>=90 then select "Nilai A" into hasil;

        -> elseif x>=70 then select "Nilai B" into hasil;

        -> elseif x>=60 then select "Nilai C" into hasil;

        -> elseif x>=50 then select "Nilai D" into hasil;

        -> else select "Nilai E" into hasil;

        -> end if;

        -> return hasil;

        -> end;

        -> //

-- Untuk mencari bilangan ganjil dan genap

Pada Postgre

postgres=# create function cari_bil (integer)

postgres-#  returns text

postgres-#  as 'declare hasil text;

postgres'#  begin case

postgres'#  when $1%2=0 then hasil =''nilai genap'';

postgres'#  when $1%2=1 then hasil =''nilai ganjil'';

postgres'#  end case ;

postgres'#  return hasil;

postgres'#  end;'

postgres-#  language 'plpgsql';

Pada Mysql

mysql> create function cari_bil (x integer)

         -> returns text

         -> begin

         -> declare hasil_text;

         -> if x%2=0 then select "Bilangan Genap" into hasil;

         -> else select "Bilangan Ganjil" into hasil;

         -> end if;

         -> return hasil;

         -> end;

         -> //

-- Untuk membuat fungsi trigger 

Pada Postgre

postgres=# create function modif_trigger()

postgres-# returns opaque

postgres-# as 'begin

postgres'# new.modifikasi := current_date;

postgres'# return new;

postgres'# end;'

postgres-# language 'plpgsql';

Pada Mysql

Kalau pada mysql sepengetahuan saya langsung dimasukkan perintahnya.

-- Untuk membuat perintah insert

Pada Postgre

postgres=# create trigger modif

postgres-# before insert

postgres-# on mahasiswa

postgres-# for each row

postgres-# execute procedure modif_trigger ();

Pada Mysql

mysql> create trigger modifikasi                -> before insert                -> on mahasiswa       
       -> for each row       
       -> begin       
       -> set new.modifikasi = sysdate();       
       -> end;        
       -> //

E. KESIMPULAN

-  Fungsi merupakan suatu rutin khusus yang disediakan oleh MYSQL untuk melakukan manipulasi suatu data.

- PL/PGSQL adalah prosedural language yang loadable (dapat langsung digunakan tanpa restart server) pada sistem database PostgreSQL.

- Trigger adalah blok pl/sql atau prosedur yang berhubungan dengan table, view, skema, atau database yang dijalankan secara implisit pada saat terjadi sebuah event. Trigger merupakan stored procedure yang dijalankan secara automatis saat user melakukan modifikasi pada tabel.

F. PENUTUP

    Tak ada gading yang tak retak, begitu juga dengan laporan saya tiap minggunya. Harap dimaklumi dan sekian terimakasih..

G. DAFTAR PUSTAKA

Modul Praktikum DBD bab VIII

http://www.haritsthinkso.com/2010/01/pengertian-trigger-pada-sql.html

http://dimasmughni21.wordpress.com/2012/02/25/pengertian-trigger/

https://rizqitohopi12.wordpress.com/2013/04/11/fungsi-fungsi-dalam-mysql/

http://www.slideshare.net/ibrahimnaki/fungsi-fungsi-dalam-mysql

http://richmadiyanti25.wordpress.com/2013/04/25/penggunaan-trigger-dalam-sql/

https://catetanreza.wordpress.com/2013/12/22/cara-membuat-trigger-di-mysql/