Fungsi , PL/SQL, dan Trigger

Dasar Teori
berikut ulasan yang dijadikan landasan dalam mengerjakan soal-soal dalam praktikum. 

Sub Program

Sub program adalah blok PL/SQL dengan nama yang dapoat menggunakan parametter serta bisa dipangguil oleh statement sql. Sub program terdiri dari prosedur untuk mengerjakan suatu proses dan function yang mengembalikan suatau nilai. Sub program itu disimpan dalam database sebagai objec schema dan disebut sebagai stored prosedure dan stured function

Compiler PL/SQL akan membuat parse tree dan pseudo code dari stored sub program itu sehingga
ketika sub program dieksekusi pertama kali, engine PL/SQL akan menjalankan pseudo code dan membawanya ke shared pool. Sub program ini dapat dijalankan ulanng tanpa perlou recompilasi karena code program dan bentuk compilasinya disimpan pada database dan statusnya ada di data dictionary.pada blok PL/SQL anonim, setiap kali blok tu dijalankan maka blok akan diparsing, dikompilasi, dan ditempatkan pada shared pool. Proses reparsing dan recompilasi tidak dilakukan jika blok telah berada di shred pool. Namun jika bentuk terkompilasi blok terseebut tidak ada di shared pool peroses parsing, kompilasi harus dilakukan ulang. 

- 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 padakolom 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];

- Fungsi

Fungsi adalah sub program yang mengembalikan suatu nilai. Fungsi terdiri dari 2 bagian yaitu spesifikasi dan body . bagian spedifikan dimulai dari keyword function hingga keyword

return. Bagian body dimulai dari keyword IS atau AS hingga keyword AND dan mencakup bagian deklarasi, kode [program dan ekseption.

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.

- Stored function

Stored function adalah fungsi yang disimpan dalam databasse seperti halnya tabel, stoder function merupakan objek schema dan bisa direferensi ole statement sql. Perubahan fungsi menjadi stored funcotion dilakukan dengan mmenyertakan klausa create yuntuk menyimpan de database namun apabila fungsi itu sudah ada, sertakan klausa or replace untuk modifikasinya

- Pepelined

Pepelined poada fungsi akan mengembalikan secara berulang hasil dari fungsi tabel yang berupa tipe koleksi ( nested table atau varray).

- Pragma restrict_references

Untuk dapat digunakan melalui statement sql, suatu stored function harus mematuhi aturan purity untuk mengontrol side effect.

- prosedur

adalah sub program yang umumnya digunakan untuk mengerjakan suatu proses. Prosedur terdiri dari 2 bagian yaitu spesifikasi dan body.

- Stored prosedure

Adalah prosedur yang disimpan pada database sebagai objek schema. Suatu prosedur dapat diubah menjadi stored prosedure dengan menyertakan klausa cretae pada definisi prosedur

- Overloading

Overloading memungkinkan prosedur dan fungsi menggunakan nama sama. Sub program yang di overload harus menggunakan parameter formal yang berbeda seperti jumlah paramter, urutan, dan tiprn datanyasub program atau metode tipe dapat di overloadanjika berada dalam paket.

- Definer dan invoker rigths

Agar user dapat ememb uat sub program pada schemanya, ia harus memiliki privilege sistem create prosedure, sedangkan pembuatan sub program pada schema user lain dimungkinkan apabila user memiliki privilege system create any prosedure.

- Status sub program

Sub program dalam database yang telah dikompilasi tanpa kesalahan akan bersetatus valid Status itu disimpan pada dictionatry sehingga dapat dideteksi bilamana objek berstatus valid atau invalid.

Fungsi PL/PGSQL

PL/SQL mengombinasikan kemampuan manipulasi data sql dengan kemampuan bahasa procedural, dengan kata lain PL/SQL adalah sql ditambah procedural language. PL/SQL merupakan bahasa berstruktur blog yang berupa fungsi, prosedur, dan blok anonym. Suatublok biasanya ditujukan untuk menyelesaikan suatu tugas tertentu dan suatu blok boleh memliki sub-blok (nested). Struktur blok PL/SQL itu terdiri dari bagian deklarasi, bagian kode program, dan bagian eksepsi untuk penanganan eror seperti Nampak di bawah ini:

[DECLARE

Declarasi variable]

BEGIN

Kode program

[EXCEPTION

Penanganan error]

END;

Selain membuat fungsi dan prosedur, PL/SQL juga digunakan untuk menghasilkan untuk mengelompokkan fungsi dan prsedur, serta untuk pembuatan kode program dalam trigger. Tentu saja

defeloper akan sering memanfaatkan pl/sql untuk membuat modul atau program baik yang dilakukan melalui SQL * Plus, Form Builder, Prosedural Builder, maupun Oracle Portal.

Blok PL/SQL anonym

Blok pl/sql anonym adalah blok PL/SQL tanpa nama dan tidak menggunakan parameter. Blok anonym tidak disimpan dalam database swhinggan tidak bisa direferensi oleh blok PL/SQL lain

Pada saat blok ini dijalankan pertama kali,menempatannya shared pool harus didahului dengan proses parsing dan kompilasi. Proses reparsing dan frekompilasi tidak dilakukan jika blok telas berada di shared pool. Apabila blok PL/SQL itu telah dikeluuarkan dari shared pool, eksekusi ulang terhadap blok PL/SQL tadi harus melalui tehapad parsing dan kompilasi. Ini desebabkan blok PL/SQL tidak disimpan di database meskipun blok itu bisa disimpan pada file system operasi.

Struktur Blok PL/SQL

• Terdapat tiga bagian :

– Bagian pendeklarasian tipe data (opsional)

– Bagian penulisan perintah

– Bagian eksepsi (opsional)

Khusus bagi oracle, ada istilah PL/SQL. PL/SQL yang merupakan singkatan dari Procedural Language/Structured Query Language merupakan sebuah penggabungan antara bahasa pemrograman prosedural (PL) dan SQL syntax.

Jadi dengan PL/SQL kita tidak perlu menggunakan sebuah bahasa pemrograman sendiri. fungsi-fungsi standard di bahasa pemrograman sudah ada di sini dan bisa langsung digabung dengan perintah SQL untuk memanipulasi database. Tapi PL/SQL cuma ada di database oracle saja

Dalam pemrograman reguler, blok blok PL/SQL anonim itu dapat diubah menjadi fungsi, prosedur, atau paket sehingga blok PL/SQL bisa direferensi melalui namanya dan menghindari duplikasipembuatan kode program.

a.    Parameter

Sub program dipanggil denhgan melewatkan nilai, variabel, atau ekspresi seseuai parameter yuang ada. Variabel yang berada pada statement pemanggil sub program atau yang disebut parameter aktual akan dikirimkan ke parameter formal, yaitu variabel yang dideklarasikan pada fungsi atatu prosedur yang dipanggil. Parameter formal dan aktual itu harus memiliki tipe data yang sama atau kompatibel.

Pemanggilan sub program untuk melewatkan parameter aktual dapat dilakukan dengan cara notasi posisi parameter, notasi nama parameter, dan notasi kombinasi. Penetapan notasi posisi melakukan pemanggiolan sub program berdasarkan posisi atau urutan parameter formal.

b. Mode parameter

Parameter formal menerima nilai atau variabel dari parameter aktual. Subprogram dapat menggunakan parameter formal sesuain mode parameter yang ditetapkan yaitu IN, OUT dan IN OUT. Secara default setiap parameter formal menggunakan mode IN yang berarti bahwa parameter itu hanya dapat dibaca atau direferensi dalam body subprogram. Mode OUT berarti parameter formal hanya dapat ditulis atau diberikan nilai, sedangkan mode IN OUT memungkinkan parameter formal untuk dibaca dan ditulis. Jadi untuk dapat mengembal\ikan berbagai nilai maka subpreogram harus menggunakan mode OUT ataub mode IN OUT, sedangkan mode IN bersifat seperti konstanta yang tidakn dapat dimodifikasi dalam body proseduryang dipanggil.

Compiler Hint NOCOPY

Proses pengiriman parameter aktual dapat dilakukan dengan dua cara yaitu by reference dan by value. Pengiriman parameter aktual secara by reference dilakukan dengan melewatkan pointer parameter aktual ke parameter formal sehinggan kedua parameter

mereferensi lokasi yang sama. Mekanisme itu berlanmhgsung pada parameter formal dengan mode IN.

Secara default, pengiriman parameter aktual pada subprigram yang menggunakan mode OUT dan IN OUT dilakukan secara by value yaitu nilai parameter aktual dicopykan ke parameter formal. Setelah eksekusi subperogram diakhiri, nilai parameter formal akan dikopiakn ke parameter aktual sehingga nilain parameter aktual berubah. Utnuk mengubah pola pengiriman by value mengjadi by reference gunakan hint compiler nocopy sehingga proses copi nilai data dapat dihindari. Penggunaan copy nilai data antara kedua parameter itu dapat menurunkan kecepatan eksekusi dan meningkatkan penggunaan memori terutama pada parameter yang menangani data berukuran besar seperti record, collection, dan tipe objek.

Nocopy menuebabkan perubahan nilai pada parametr formal segera mempengaruhi nilai parameter aktual. Ini berbeda dengan kondisi defaultnya dimana jika subprogram berakhir secara tudak normal maka parameter formal tidak akan dicopy pada parameter aktual. Sebagai suatu hingt comliper, nocopy melakukan pengiriman paremeter by reference, sebaiknya pengiriman pewaremeter aktual dilakuakan secara by value. 

- Menghapus fungsi :

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

Contoh :

DROP FUNCTION pembagian(text);

Triger

Trigger, adalah blok PL/ SQL yang disimpan dalam basis data dan akan diaktifasi saat kita melakukkan statemen-statemen dalam SQL seperti DELETE, UPDATE, atau INSERT (DML/ Data Manipulation Language) namun memiliki batasan dalam CREATE, ALTER, atau DROP (DDL/ Data Definition Language) serta terbatas juga didalam COMMIT, GRANT, ROLLBACK dll (DCL/ Data Control Language). Saya menyebut Trigger ini sebuah ‘pemicu’.

Trigger benar-benar berguna bagi kita yang sedang membuat aplikasi yang membutuhkan validasi dalam prosesnya. Ada beberapa daftar event yang harus diperhatikan dalam penggunaan trigger:

Nama Event	Keterangan
BEFORE INSERT	Diaktifkan sekali sebelum statemen INSERT
AFTER INSERT	Diaktifkan sekali setelah statemen  INSERT
BEFORE UPDATE	Diaktifkan sekali sebelum statemen UPDATE
AFTER UPDATE	Diaktifkan sekali setelah statemen UPDATE
BEFORE DELETE	Diaktifkan sekali sebelum statemen DELETE
AFTER DELETE	Diaktifkan sekali setelah statemen DELETE

Tugas Praktikum
mengerjakan soal-soal menggunakan DBMS postgreSQL

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

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

3.    Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql ! Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !

4.    Buatlah fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari bilangan yang diinputkan. 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.

Tugas Rumah
mengerjakan soal-soal menggunakan DBMS mySQL

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

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

3.    Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql ! Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !

setelah fungsi terbuat, kita coba coba fungsi tersebuat dengan cara menselectnya seperti dibawah ini:

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

      jangan lupa untuk men-delimiter nya dahulu karena pada soal nomor dua, kita telah mengubah delimiter ke default.

 setelah di delimiter, dilanjutkan dengan membuat fungsi PL/SQL.

lalu langsung di cek ya..

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

           langsung saja dibuat triggernya seperti dibawah ini :

 diatas merupakan trigger untuk insert.

 diatas merupakan trigger untuk update.
lalu, mengembalikan delimiter ke dafault dengan sintaks : 

  setelah itu, melakukan pengecekan dengan menginsertkan data.

selanjutnya menampilkan semua data dalam tabel untuk melihat hasilnya

meg-Update juga data salah salah mahasiswa dan menampilkannya.

Evaluasi Perbandingan dua DBMS
silahkan perhatikan tabel dibawah ini mengetahui perbedaan struktur sintak yag digunakan dalam dua DBMS (postgreSQL dan mySQL). pada dasarnya, semua sintak yang digunakan berbeda total.

No	Perbedaan
1	-       PostgreSQL dwi13650036=# create function konversisuhu(float) dwi13650036-# returns float dwi13650036-# as 'select ($1-32)/1.8;' dwi13650036-# language 'sql'; CREATE FUNCTION -       MySQL Mysql> create function konversi_suhu (a float) è  Returns float è  Return ((a-32)/1.8); Query OK, 0 rows affected (0.59 sec)
2	-       PostgreSQL dwi13650036=# create function cari_alamat (varchar) dwi13650036-# returns text dwi13650036-# as 'select alamat_mhs from mahasiswa dwi13650036'# where nama_mah=$1;' dwi13650036-# language 'sql'; CREATE FUNCTION -       MySQL Mysql> delimiter // Mysql> create function cari_alamat (a varchar(15)) è  Returns varchar(15) è  BeginDeclare hasil varchar(15); è  Select alamat_mhs as alamat_mahasiswa into hasil è  From mahasiswa è  Where nama_mhs=a; è  Return hasil; è  End; è  // Query OK, 0 rows affected (0.10 sec)
3	-       PostgreSQL dwi13650036=#  create function penilaian(integer) dwi13650036-#  returns text dwi13650036-#  as 'declare hasil text; dwi13650036'#  begin dwi13650036'#  if $1>100 or $1<0 then select into hasil ''nilai salah''; dwi13650036'#  elsif $1>=90 then select  into hasil ''nilai A''; dwi13650036'#  elsif $1>=70 then select into hasil ''nilai B''; dwi13650036'#  elsif $1>=60 then select into hasil ''nilai C''; dwi13650036'#  elsif $1>=50 then select into hasil ''nilai D''; dwi13650036'#  else select into hasil ''nilai E''; dwi13650036'# end if; dwi13650036'#  return hasil; dwi13650036'#  end;' dwi13650036-#  language 'plpgsql'; CREATE FUNCTION -       MySQL Mysql> delimiter // è  Create finction penilaian (x integer) è  Returns varchar(15) è Begin è Declare hasil varchar(15); è 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; è //
4	-       PostgreSQL dwi13650036=# create function ganjilgenap(integer) dwi13650036-# returns text dwi13650036-# as 'declare bilangan text; dwi13650036'# begin dwi13650036'# if $1%2=0 then select into bilangan ''genap''; dwi13650036'# else select into bilangan ''ganjil''; dwi13650036'# end if; dwi13650036'# return bilangan; dwi13650036'# end;' dwi13650036-# language 'plpgsql'; CREATE FUNCTION -       MySQL Mysql> delimiter // è  Create function ganjil_genap (x integer) è  Returns varchar(15) è  Begin è  Declare hasil varchar(15); è  If x%2=0 then è  Select “Bilangan genap” into hasil; è Else è Select “bilanagn ganjil” into hasil; è End if; è Return hasil; è End; è //
5	-          PostgreSQL Untuk membuat trigger harus membuat fungsi perubahan dahulu -          MySQL Langsung membuat trigger

Kesimpulan:

Fungsi SQL adalah suatu kumpulan query, biasanya query yang detail, panjang serta kompleks yang dikemas menjadi satu dan disimpan dalam database, dan ketika dibutuhkan tinggal diakses tanpa mengetikkan query secara lengkap(detail). Fungsi tersebut tersedia pada DBMS PostgreSQL maupun MySQL. PL/SQL merupakan bahasa yang lain untuk membuat suatu fungsi dan biasanya bahasa ini digunakan untuk membuat fungsi yang lebih kompleks. Dalam PostgreSQL sudah terdapat fungsi pl/pgsql namun kalau di MySQL belum ada. Trigger digunakan untuk memanggil  satu atau beberapa fungsi/perintah SQL secara otomatis secara otomatis sebelum atau sesudah INSERT, UPDATE maupun DELETE dari suatu data yang ada di tabel.

Kritik

Dalam praktikum kali ini, praktikan mengalami sedikit kesulitan dalam mengerjakan soal-soal di dua DBMS karena kurangnya pengetahuan praktikan tentang sintaks-sintaks dalam dua DBMS ini yang digunakan untuk membuat fungsi maupun PL/SQL dan juga. Hal itu dikarenakan semua sintak yang digunakan untuk mengerjakan soal, baik pada postgreSQL maupun mySQL berbeda total.

Saran

Agar Praktikan tidak kesulitan lagi, lebih memperbanyak bahan bacaan serta sering-sering sharing dengan teman-teman supaya dapat menambah pengetahuan praktikan.

Manfaat

Manfaat yang dapat pembaca peroleh dari membaca postingan ini adalah pembaca dapat mengetahui struktur-struktur query yang digunakan untuk membuat fungsi dan juga PL/SQL serta trigger pada dua DBMS.

Daftar Pustaka
http://download.portalgaruda.org/article.php?article=15295&val=985
http://tifosilinux.files.wordpress.com/2014/03/mengenal-pl-sql.doc
http://spacetask.wordpress.com/2011/12/07/laporan-praktikum-7-dbd-fungsi-dan-plsql/
http://teknisijaringankomputer.wordpress.com/2013/02/12/mengenal-plsql/
Raharjo Budi, Heryanto Imam, Menguasai ORACLE SQL dan PL/ SQL, Informatika, 2009.