PBD Stored Procedure, Function, dan Trigger pada SQL Server

Nama/nim        : Candra Kurniawan/10.41010.0259
Dosen              : Tan Amelia
Tugas               : Pengertian dari Stored procedure,Function,dan Trigger pada SQL Server

Stored Procedure

             

       Stored Procedure adalah kumpulan perintah SQL yang diberi nama dan disimpan di server. Stored Procedure biasanya berisi perintah-perintah umum yang berhubungan dengan database pada server, dan menghasilkan sekumpulan record. Stored Procedure ditulis dalam bentuk sebuah script.

       Stored Procedure akan meningkatkan performasi database, meningkatkan sekuritas database. Performasi ditingkatkan melalui pemakaian tempat penyimpanan lokal (lokal terhadap database), program yang telah terkompilasi terlebih dahulu, dan pemakaian cache. Setiap kali sebuah perintah atau Stored Procedure dikirimkan ke server untuk diproses, server harus menentukan apakah dikirimkan ke server untuk diproses, server harus menentukan apakah pengirim mempunyai hak untuk menjalankan perintah tersebut dan apakah perintah tersebut benar, Setelah hak dan perintah diperiksa dan benar, SQL Server akan membuat rencana eksekusi.

      Stored Procedure lebih efisien dari perintah biasa, karena procedure disimpan si server pada saat dibuat dan dijalankan di server. Stored Procedure yang berisi sekumpulan perintah Transact-SQL akan dipanggil oleh sebuah perintah Transact-SQL.
Sebelum Stored Procedure dibuat, sintaks penulisan perintah dicek kebenarannya. Jika tidak Error, nama procedure disimpan di tabel SysObjects dan teksnya disimpan di tabel SysComments. Pertama kali Stored Procedure dijalankan, rencana eksekusi dibuat dan Stored Procedure dikompilasi. Pemrosesan berikutnya akan lebih cepat, karena SQL Server tidak perlu mengecek sintaks dan membuat rencana eksekusi atau mengkompilasi procedure.

      Sekali Stored Procedure dibuat, Anda dapat memanggilnya pada saat diperlukan. Cara ini meningkatkan modularitas dan memungkinkan pemakaian ulang sebuah program. Pemakaian ulang program akan memudahkan pemeliharaan sebuah database. Jika aturan bisnis berubah, Anda hanya perlu mengubah Stored Procedure, sedangkan pemanggilannya tetap.

      Stored Procedure dapat menerima parameter, dan menghasilkan parameter, memberikan umpan balik dalam bentuk kode status dan teks, dan dapat memanggil procedure lain. Stored Procedure menjamin sekuritas dengan isolasi dan enkripsi. User diberi hak untuk menjalankan Stored Procedure, tetapi tidak mempunyai hak untuk mengakses database secara langsung. Dengan enkripsi, user tidak dapat membaca perintah-perintah yang dipakai dalam Stored Procedure.

Tahap – tahap dalam menggunakan Stored Procedure  di Mysql yaitu :

• Tahap pertama adalah membuat table pada database test dengan SQL Yog 
• Taap kedua adalah mengisi data dengan SQL Yog
• Tahap ketiga adalah merancang script stored procedure 
• Tahap keempat adalah membuat program java untuk membuat dan menjalankan stored procedure
• Tahap kelima adalah membuat file PHP untuk menjalankan program java di PHP
• Tahap keenam adalah menjalankan program PHP tersebut di browser
•  Tahap ketujuh adalah menampilkan hasil query dari stored procedure tersebut
• Tahap kedelapan adalah menjalankan program PHP tersebut di browser

Pembuatan Strore Procedure

Syntax :

CREATE PROCEDURE procedure1               /* nama */

(IN parameter1 INTEGER)                            /* parameter */

BEGIN                                                        /* awal blok */

  DECLARE variable1 CHAR(10);                  /* variabel */

  IF parameter1 = 17 THEN                          /* awal IF */

   SET variable1 = 'burung';                          /* assignment */

  ELSE

   SET variable1 = 'kelelawar';                      /* assignment */

  END IF;                                                    /* akhir IF */

  INSERT INTO table1 VALUES (variable1);  /* statement */

END                                       

                                Function

             

             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.

Fungsi SQL Aggregate : fungsi yang menghasilkan nilai tunggal dari perhitungan sebuah nilai dari sebuah kolom, yaitu:
AVG() – memberikan nilai rata-rata dari sebuah kolom numerik
COUNT() – memberikan jumlah baris atau data yang sesuai dengan kriteria yang diberikan
FIRST() – memberikan nilai pertama dari sebuah kolom
LAST() – memberikan nilai terakhir dari sebuah kolom
MAX() – memberikan nilai terbesar dari sebuah kolom
MIN() – memberikan nilai terkecil dari sebuah kolom
SUM() – memberikan jumlah dari sebuah kolom numerik

Fungsi SQL Scalar : fungsi yang menghasilkan nilai tunggal dari nilai yang dimasukkan, yaitu :
UCASE() – merubah menjadi huruf kapital
LCASE() – merubah menjadi huruf kecil
MID() – mengekstrak karakter dari sebuah data teks
LEN() – menghitung jumlah karakter dari sebuah data teks
ROUND() – membulatkan data numerik ke nilai terdekat
NOW() – memberikan data tanggal dan waktu dari sistem
FORMAT() – memberikan format dari sebuah data yang akan ditampilkan

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;
/

                                                                   TRIGGER

                Trigger adalah Stored Procedure khusus yang dijalankan secara otomatis pada saat atau sesudah modifikasi data. Perintah-perintah yang akan mengaktifkan Trigger adalah UPDATE, INSERT, dan DELETE. Trigger dipakai untuk menjaga integritas data dan mengimplementasikan aturan bisnis yang kompleks. Trigger dibuat dengan memakai bahasa Transact-SQL atau SQL Enterprise Manager. Tugas-tugas manajemen Trigger meliputi mengubah, mengganti nama, menampilkan, menghapus dan membuat Trigger tidak aktif.

                Trigger memakai dua tabel maya yaitu Inserted dan Deleted untuk mendeteksi modifikasi data. Untuk memprogram Trigger, Anda harus mendalami kedua tabel tersebut dan bahasa Transact-SQL. Untuk menjaga integritas data, Anda dapat memakai beberapa alternatif, yaitu Trigger atau Konstrain (Rule). Anda juga dapat memakai kunci utama dan kunci unik untuk mengidentifikasi baris dalam sebuah tabel secara unik. Anda juga dapat memakai nilai default dan domain untuk membatasi nilai-nilai yang diperbolehkan pada sebuah kolom. Referential Integrity dipakai untuk menjamin keabsahan hubungan antar tabel. Sebaliknya Anda memakai konstrain-konstrain tersebut sebelum memilih Trigger, karena Trigger dapat melakukan proses yang lebih rumit tetapi beban sistem lebih berat.

Pakailah Trigger untuk kasus-kasus berikut:
- Jika pemakaian cara deklaratif tidak dapat memenuhi kebutuhan yang diperlukan. Misalnya, buatlah Trigger untuk mengubah sebuah nilai numerik dalam tabel jika sebuah record di dalam tabel tersebut dihapus.
- Sebuah perubahan harus mengubah tabel-tabel lain yang terhubung. Misalnya jika data order ditambahkan, tabel lain yang menyimpan data stok akan berubah.
- Jika database didenormalisasi dan memerlukan cara otomasi untuk mengubah data redundan yang ada dibeberapa tabel.
- Jika sebuah nilai pada sebuah tabel harus divalidasi dengan data pada tabel lain.
- Jika diperlukan pesan kesalahan dan penangganan kesalahan secara khusus.

Kemampuan-kemampuan yang dimiliki Trigger:
- Trigger dapat menjaga referential integrity dengan melakukan perubahan data atau penghapusan kunci tamu (Foreign Key) di dalam database.
- Trigger dapat bekerja pada beberapa kolom dalam sebuah database bahkan pada objek di luar database. Anda juga dapat memakai Trigger pada View.
- Sebuah Trigger dapat melakukan beberapa aksi dan Trigger dapat diaktifkan oleh beberapa event. Misalnya Anda membuat sebuah Trigger yang akan diaktifkan jika terjadi proses INSERT, UPDATE atau DELETE. Dengan perintah Transact-SQL, Anda dapat mendefinisikan aturan bisnis untuk tiap event.

                 Selain kemampuan-kemampuan tersebut, Trigger juga mempunyai keterbatasan. Trigger tidak dapat dibuat pada sebuah tabel sistem atau temporer, meskipun perintah di dalama Trigger mengacu ke tabel sistem atau tabel temporer.
Trigger tidak dapat diaktifkan secara manual, tetapi diaktifkan secara otomatis jika terjadi event INSERT, UPDATE, dan DELETE. Dalam Trigger selalu berisi satu atau beberapa event tersebut sebelum perintah Transact-SQL didefinisikan. Tipe-tipe Trigger sesuai dengan event-nya. Misalnya Anda membuat sebuah Trigger UPDATE sehingga jika terjadi pengubahan data pada sebuah tabel, Trigger tersebut dapat diaktifkan. Sebuah Trigger dapat diaktifkan oleh beberapa event.

                  Pada saat terjadi INSERT atau UPDATE yang diaktifkan Trigger, Trigger menyimpan data baru atau data hasil modifikasi di dalam sebuah tabel yang bernama INSERTED. Jika terjadi DELETE yang mengaktifkan Trigger, data yang dihapus akan tersimpan ditabel yang bernama Deleted. Tabel tersebut berada dimemori dan dibaca oleh Trigger dengan perintah-perintah Transact-SQL. Kemampuan ini penting anda ketahui, karena pada umumnya Trigger membandingkan data dalam tabel Inserted dan Deleted dengan data baru sebelum proses Commit. Dengan kedua tabel tersebut, Trigger dapat melakukan proses Roll-Back.

Posted in: