Contoh dan Pembahasan Tentang Stored Procedure, Function, dan Trigger

NIM/NAMA : Filzah Apritasari / 10.41010.0224
Dosen        :  Tan Amelia
Tugas         : Contoh dan Pembahasan Tentang Stored Procedure, Function, dan Trigger


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.

Syntax Stored Procedure :

CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ][,...n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

Argument
Procedure_name
Merupakan nama procedure yang unik (tidak boleh ada nama stored procdure yang sama dalam satu database). 
Number
Ini akan berfungsi jika anda ingin menghapus stored procedure yang mempunyai nama sama pada group server yang berbeda.
@parameter
parameter berfungsi sebagai passing variable antara stored procedure dengan pemanggilnya.
Data_type
Merupakan tipe data dari parameter
VERIYING
Hasil pengembalian mendukung out parameter, hanya diaplikasikan untuk parameter bertipe cursor.
OUTPUT 
Mengindikasikan bahwa parameter berupa return parameter.
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }
RECOMPILE mengindikasikan bahwa stored procedure akan selalu dibuat pada run time
ENCRYPTION mengindikasikan bahwa stored procedure tersebut di-encrypt
FOR REPLICATION
Mengindikasikan bahwa stored procedure hanya dapat dijalankan pada replikasi dan tidak diijinkan dijalankan oleh subscriber.
Sql_statement
Merupakan pendeklarasian proses stored procedure itu sendiri.

Menciptakan stored procedure

CREATE PROCEDURE p1 () SELECT * FROM barang; //

p1 = nama prosedur

() = daftar parameter

SELECT * FROM barang; = bodi prosedur

Mengembalikan delimiter:

delimiter ;

Memanggil stored procedure

mysql> call p1();

+------+--------------------+-------+--------+--------+

| kode | nama               | jenis | harga  | jumlah |

+------+--------------------+-------+--------+--------+

| 001  | Coca cola          | F     |   7500 |     20 |

| 002  | Mi Sedap rasa Soto | A     |    800 |     24 |

| 003  | Kecap ABC          | A     |   2500 |     20 |

| 004  | Kaos oblong        | B     |  15000 |      5 |

| 005  | VCD player         | C     | 235000 |      1 |

| 008  | Ballpoint          | D     |   2500 |     15 |

| 010  | Celana Jeans       | B     |  65000 |      5 |

| 011  | Buku Gambar        | D     |   2000 |     12 |

| 012  | Jaket              | B     |  50000 |      6 |

+------+--------------------+-------+--------+--------+

9 rows in set (0.36 sec)

menghapus procedure

mysql> drop procedure p1;

Parameter

1. Tanpa parameter

CREATE PROCEDURE p5

() ...

2. Satu parameter input

CREATE PROCEDURE p5

([IN] nama tipe-data) ...

3. Satu parameter output

CREATE PROCEDURE p5

(OUT nama tipe-data) ...

4. Satu parameter untuk input dan output

CREATE PROCEDURE p5

(INOUT nama tipe-data) ...

Contoh IN

mysql> CREATE PROCEDURE p5(p INT) SET @x = p //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL p5(12345)//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

+-------+

| @x    |

+-------+

| 12345 |

+-------+

1 row in set (0.00 sec)

Contoh OUT

mysql> CREATE PROCEDURE p6 (OUT p INT)

-> SET p = -5 //

mysql> CALL p6(@y)//

mysql> SELECT @y//

+------+

| @y   |

+------+

| -5   |

+------+

p adalah nama parameter output. Saat pemanggilan, variabel sesi @y dilewatkan.

Dalam bodi prosedur, parameter diisi dengan -5.

Efeknya sama dengan statemen “SET @y = -5;”.

Gabungan statemen

diapit dengan blok begin ... end

CREATE PROCEDURE p7 ()

BEGIN

  SET @a = 2;

  SET @b = 10;

  SELECT nama, @a * jumlah FROM barang WHERE jumlah >= @b;

END; //

Variabel

dideklarasikan dengan statemen DECLARE

CREATE PROCEDURE p8 ()

BEGIN

  DECLARE a INT;

  DECLARE b INT;

  SET a = 2;

  SET b = 10;

  SELECT nama, a * jumlah FROM barang WHERE jumlah >= b;

END; //

Function

Function adalah jenis PL/SQL block yang menghasilkan satu nilai. Secara umum, function digunakan melakukan perhitungan, mengecek eksistensi dan kevalidan suatu data. Function bisa dilibatkan dalam expresi. Function bisa disimpan dalam database sebagai object schema, sehingga suatu function bisa digunakan berulangkali tanpa harus melakukan parsing dan compile ulang.

Perbedaan Function dan Stored Procedure

1. Function bisa mengembalikan suatu nilai balik (return value)

2. Pada Function parameter yang diperbolehkan hanya parameter IN

3. Function bisa langsung dipanggil dari perintah SELECT SQL

CREATE [OR REPLACE] FUNCTION function_name

[(parameter1 [mode1] datatype1,

RETURN datatype

IS | AS

PL/SQL block;

Parameter yang berlaku pada function hanya parameter IN.

Privilege yang dibutuhkan CREATE PROCEDURE atau CREATE ANY PROCEDURE
Untuk demo tentang function connect sebagai user SCOTT.
Gunakan tabel EMP, buat function yang mengembalikan SAL dari EMPNO yang dikirim.

-- periksa struktur tabel EMP

SQL> DESC emp;

 Name                    Null?    Type

 ----------------------- -------- -------------

 EMPNO                   NOT NULL NUMBER(4)

 ENAME                            VARCHAR2(10)

 JOB                              VARCHAR2(9)

 MGR                              NUMBER(4)

 HIREDATE                         DATE

 SAL                              NUMBER(7,2)

 COMM                             NUMBER(7,2)

 DEPTNO                           NUMBER(2)

CREATE OR REPLACE FUNCTION GET_SAL  (P_EMPNO   NUMBER)

RETURN NUMBER

AS

   V_SAL    NUMBER(10);

BEGIN

  SELECT SAL INTO V_SAL

  FROM EMP  WHERE EMPNO=P_EMPNO;

  -- Nilai yang dikeluarkan

  RETURN V_SAL;

END;

/

Berbagai cara menjalankan Function.
– Menjalankan function sebagai parameter dari suatu procedure

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(GET_GAJI(7369));

800

– Menjalankan funtion sebagai bagian dari ekspresi

SQL> SET AUTOPRINT ON

SQL> VARIABLE G_HASIL NUMBER;

SQL> EXECUTE :G_HASIL := GET_GAJI(7788);

PL/SQL procedure successfully completed.

G_HASIL

----------

3000

– Menjalankan function pada perintah SELECT

SQL> SELECT EMPNO,ENAME,GET_SAL(EMPNO) FROM EMP;

       NIP NAMA       GET_SAL(EMPNO)

---------- ---------- -------------

      7369 SMITH                800   

      7499 ALLEN               1600

      7521 WARD                1250

      7566 JONES               2975

      7654 MARTIN              1250

Contoh lain function

CREATE OR REPLACE FUNCTION PAJAK(P_SAL NUMBER, PCT NUMBER)

RETURN NUMBER

AS

BEGIN

   RETURN (P_SAL * (PCT/100));

END;

/

SQL> SELECT EMPNO,ENAME,SAL,PAJAK(SAL,10)

2  FROM EMP;

     EMPNO ENAME             SAL PAJAK(SAL,10)

---------- ---------- ---------- -------------

      7369 SMITH             800            80

      7499 ALLEN            1600           160

      7521 WARD             1250           125

      7566 JONES            2975         297.5

      7654 MARTIN           1250           125

      7698 BLAKE            2850           285

      7782 CLARK            2450           245

      7788 SCOTT            3000           300

      7839 KING             5000           500

Trigger

Trigger merupakan store procedure 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.

Insert : Triger aktif saat baris baru dimasukkan ke dalam tabel

Update : Triger aktif saat ada baris diperbaharui dimasukkan di dalam tabel

Delete :Triger aktif saat baris dihapus dalam tabel

Bentuk dasar perintahnya :

CREATE TRIGGER nama_trigger ON nama_tabel FOR INSERT, UPDATE, DELETE AS isi statement-statement Anda disini. GO

Saya akan memberi contoh sederhana untuk mudah memahami bagaimana cara kerja trigger ini.

Misalkan kita punya data di table DaftarSiswa

DaftarSiswa
Kode	Status
1001
1002
1003

Di tabel yang lain kita juga punya tabel Daftar Nilai :

DaftarNilai
Kode	Nilai

Daftar nilai ini belum ada isinya yang nantinya akan kita isi dengan perintah INSERT.

Kolom Status pada tabel DaftarSiswa akan kita isi dengan “Lulus” dan “Tidak Lulus” secara automatis saat kita melakukan INSERT dan UPDATE pada tabel DaftarNilai.

Cara membuat dan mengisi kedua tabel diatas :

CREATE TABLE daftarSiswa (kode char(4), status char(10))

CREATE TABLE daftarnilai (kode char(4), nilai float)

Pada tabel DaftarSiswa diisi dengan perintah berikut :

INSERT INTO daftarSiswa VALUES(’1001′,”)

INSERT INTO daftarSiswa VALUES(’1002′,”)

INSERT INTO daftarSiswa VALUES(’1003′,”)

Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftarNilai.

CREATE TRIGGER tr_status      ON daftarnilai FOR INSERT, UPDATE AS DECLARE @kode char(4) DECLARE @nilai float SELECT @kode = kode, @nilai = nilai FROM daftarNilai IF @nilai >= 60 UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode go

Untuk menganalisa lihat isi kedua tabel diatas dengan perintah

SELECT* FROM daftarSiswa

SELECT* FROM daftarNilai

Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :

INSERT INTO daftarNilai values(’1001′,70)

INSERT INTO daftarNilai values(’1002′,50)

INSERT INTO daftarNilai values(’1003′,80)

Setelah dijalankan Anda bisa lihat kembali isi tabel daftarSiswa dan daftarNilai.

DaftarSiswa
Kode	Status
1001	Lulus
1002	Tidak Lulus
1003	Lulus

DaftarNilai
Kode	Nilai
1001	70
1002	50
1003	80

Disini terlihat saat kita melakukan perintah INSERT di tabel daftarNilai secara otomatis program trigger melakukan pengisian pada tabel daftarSiwa pada kolom “status”