FUNCTION, PROSEDUR AND TRIGGER

Materi Stored Procedures, Functions dan Trigger pada SQL Server

http://aprilysaputra.blogspot.com/2011/09/materi-stored-procedures-functions-dan.html

# Stored Procedures

1. Stored procedure pada dasarnya adalah sebuah program yang disimpan di dalam database server, karena stored procedure ini dapat menerima suatu input parameter dan output parameter serta dapat menghasilkan suatu message succes atau error.

2. Stored procedure sangat bermanfaat untuk aplikasi database, baik untuk meningkatkan kinerja maupun untuk pemeliharaan. Karena setelah procedure dijalankan di server, akses berikutnya akan menjadi lebih mudah dan cepat karena eksekusi telah tersimpan di dalam memory.

3. Stored procedure juga dapat menjalankan procedure yang lain, dan juga dapat memberitahukan bahwa ia telah dijalankan dengan sukses atau gagal.

4. Stored procedure dapat menerima parameter input, dengan menggunakan variabel lokal, dan menghasilkan data dengan menggunakan output parameter, return code, result set dari statement SELECT.

# Functions

Function adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value).
Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.

Bentuk Umum :

CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)
RETURN tipe_data AS
variabel_1 tipe_data;
…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;
END;

Statemen RETURN tipe_data diatas menunjukkan bahwa function akan mengembalikan nilai dengan tipe data tertentu
Statemen RETURN nilai_yang_dikembalikan berfungsi untuk mengembalikan nilai yang telah diproses dalam function

Contoh Function Tanpa Parameter :
(1)
CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS
S VARCHAR2(20)
BEGIN
S := ‘HALLO SEMUA’;
RETURN S;
END;
/

(2)
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;
/

Contoh Function Dengan Parameter :
(1)
CREATE OR REPLACE FUNCTION 
pangkat (bil INTEGER, n INTEGER)
RETURN INTEGER AS
HASIL INTEGER(10);
I INTEGER;
BEGIN
HASIL := 1;
FOR I IN 1..n LOOP
HASIL := HASIL * bil;
END LOOP;
RETURN HASIL;
END;
/

(2)
SET SERVEROUTPUT ON
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
/

Contoh Function Dalam Function :
(1)
CREATE OR REPLACE FUNCTION kuadrat (X NUMBER)
RETURN NUMBER AS
HASIL NUMBER(10);
BEGIN
HASIL := X * X;
RETURN HASIL;
END;
/

(2)
CREATE OR REPLACE FUNCTION determinan
(a NUMBER, b NUMBER, c NUMBER)
RETURN NUMBER AS
D NUMBER(10);
BEGIN
D := kuadrat(b) – (4 * a * c);
RETURN D;
END;
/

# Trigger pada SQL

Trigger adalah blog pada PL/SQL atau prosedur yang berhubungan dengan tabel, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event. Trigger merupakan store prosedur yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE dan DELETE. INSERT, UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Tipe dari Trigger adalah :

1. Application trigger : diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi.

2. Database trigger : diaktifkan pada saat terjadi event yang berhubunga dengan data (seperti operasi DML) atau event yang berhubungan dengan system (misal logon atau shut down) yang terjadi pada sebuah skeam atau database.

Trigger perlu dibuat pada saat :

1. Membuat sebuah aksi tertentu terhadap suatu event.

2. Memusatkan operasi global.

Trigger tidak perlu dibuat, jika :

1. Fungsionalitas yang diperlukan suatu ada pada Oracle server.

2. Duplikat atau sama dengan fungsi trigger yang lain.

Sintak penulisan dari database trigger, berisi komponen berikut :

1. Trigger timing :

            a. Untuk tabel : BEFORE, AFTER

            b. Untuk view : INSTEAD OF

2. Trigger event : INSERT, UPDATE, atau DELETE

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

4. Tipe trigger : baris atau pernyataan (statement)

5. Klausa WHEN : untuk kondisi pembatasan pada trigger

6. Trigger body : bagian prosedur yang dituliskan pada trigger.

Trigger timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger triming, yaitu :

1. BEFORE : trigger dijalankan sebelum DML event pada tabel.

2. AFTER : trigger dijalankan setelah DML event pada tabel.

3. INSTEAD OF : trigger dijalankan pada sebuah view.

Trigger event ada 3 kemungkinan : INSERT, UPDATE, atau DELETE. Pada saat trigger event UPDATE,  kita dapat memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk mengaktifkan sebuah trigger (contoh : UPDATE OF salary ...). Jika tidak ditentukan, maka perubahanya akan berlaku untuk semua kolom pada semua baris.

Tipe trigger ada 2 macam yaitu :

1. Statement : trigger dijalankan sekali saja pada saat terjadi sebuah event. Statement trigger juga dijalankan sekali, meskipun tidak ada satupun baris yang dipengaruhi oleh event yang terjadi

2. Row : trigger dijalankan pada setiap baris yang dipengaruhi oleh terjadinya sebuah event. Row trigger tidak dijalankan jika event dari trigger tidak berpengaruh pada satu baris pun.

Trigger body mendefinisikan tindakan yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah trigger menjadi aktif.