Filzah Apritasari: September 2011

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();

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

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

| 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

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

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

isi statement-statement Anda disini.

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

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

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”

Selasa, 27 September 2011

Contoh dan Pembahasan Tentang Stored Procedure, Function, dan Trigger