Tugas PBD Pertemuan 3

NAMA/NIM  : Fitriana Faristia / 10.41010.0206
Dosen             : Tan Amelia
Tugas              :Contoh dan Pembahasan Stored Prosedure, Function, Trigger

STORED PROCEDURE

Stored procedure adalah prosedur (spt subprogram dalam bhs pemrograman) yang disimpan di dalam database.

MySQL mendukung dua jenis “rutin” (subprogram):

• stored procedure yang dapat dipanggil,
• fungsi yang menghasilkan nilai yang dapat dipakai dalam statemen SQL lain.

Contoh

Statemen yang menciptakan strored procedure

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                                       /* akhir blok  */

Mengapa Strored Procedure

Dengan stored procedure eksekusi menjadi cepat. Tidak ada kompilasi. Peningkatan kecepatan datang dari reduksi lalu-lintas jaringan. Jika ada pekerjaan pengecekan berulang, looping, multiple statement, dikerjakan dengan pemanggilan tunggal ke prosedur yang telah disimpan ke server.

Stored procedure adalah komponen. Andaikan aplikasi kemudian ditulis dalam bahasa berbeda, tidak ada masalah, karena logika berada didalam database bukan dalam aplikasi.

Stored procedure adalah portable. Stored procedure ditulis dalam SQL, Anda bisa jalankan pada setiap platform dimana MySQL dijalankan disitu.

Memulai klien MySQL

Cek versi:

SHOW VARIABLES LIKE 'version';

atau

SELECT VERSION();

Hasilnya:

mysql> SHOW VARIABLES LIKE 'version';

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

| Variable_name | Value                   |

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

| version       | 5.0.22-community-max-nt |

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

1 row in set (0.01 sec)

mysql> select version();

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

| version()               |

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

| 5.0.22-community-max-nt |

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

1 row in set (0.08 sec)

Jika kita melihat bilangan ‘5.0.x’, berarti strored procedure akan bekerja.

Contoh database

Menggunakan database dbjual:

USE dbjual;

mysql> select * from barang;

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

| 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.09 sec)

Memilih delimiter

mysql> DELIMITER //

Delimiter adalah karakter atau string yang memberi tahu MySQL bahwa kita telah selesai menulis statemen SQL. Sebelumnya, delimiter selalu semicolon (tanda ;). Hal ini diperlukan karena stored procedure terdiri atas sejumlah statemen, dan setiap statemen harus diakhiri dengan semicolon.

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

Contoh OUT

INOUT

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

(3)
SET SERVEROUTPUT ON
DECLARE
D NUMBER(10);
BEGIN
D := determinan(1, 1, -6);
DBMS_OUTPUT.PUT_LINE(‘Nilai determinan = ‘ || TO_CHAR(D));
END;
/

TRIGGER

Trigger adalah prosedur tersimpan yang secara otomatis dijalankan apabila data di dalam tabel berubah karena eksekusi perintah SQL INSERT, UPDATE, atau DELETE. Salah satu dari penggunaannya yang paling umum adalah untuk menerapkan pembatasan yang lebih kompleks dari yang telah diijinkan melalui pembatasan CHECK, yang berfungsi membatasi informasi yang disisipkan ke dalam kolom. Trigger bisa dibuat bersama dengan perintah INSERT, yang akan melakukan query ke tabel lain dan mengembalikan nilai logik yang membantu membatasi data yang diberikan kepada kolom tertentu.
Sebagai contoh trigger bisa dibuat untuk menjalankan replikasi misalnya apabila sebuah baris disisipkan ke dalam databse Z, maka sebuah baris dengan informasi yang sama akan ditambahkan ke dalam database Y. Atau apabila sebuah baris dihapus dari sebuah tabel maka trigger akan menghapus baris lain yang berhubungan dengan baris tersebut pada tabel lain. Trigger dibuat sebagai sebuah transaksi dan bisa dimundurkan apabila ada masalah yang dideteksi.
Trigger dieksekusi jika terjadi suatu database “event”
Database event adalah : Insert, Update dan Delete.
Trigger akan beraksi jika terjadi , misalnya :
- Sebuah baris baru(record) dimasukkan ke tabel.
- Peremajaan record
- Penghapusan record
Hal ini disebabkan sebuah trigger terdiri atas :
- Uraian kapan trigger dilepaskan
- Tabel yang diasosiasikan dengan trigger tersebut.
- Program Transact-SQL yang dijalankan oleh Trigger tersebut.
Syntax Trigger :
CREATE TRIGGER trigger_name ON table { {FOR { [DELETE] [,] [UPDATE] [,][INSERT] } [NOT FOR REPLICATION] AS
sql_statement [...n] } | {FOR { [INSERT] [,] [UPDATE] } [NOT FOR REPLICATION] AS { IF UPDATE (column) [{AND | OR} UPDATE (column)] [...n] | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) { comparison_operator} column_bitmask [...n] } sql_statement [...n] } }
Jika menggunakan event :
- For Insert
Trigger aktif pada saat record baru
- For Update
Trigger aktif pada saat record diubah
- For Delete
Trigger aktif pada saat record dihapus
Untuk melihat efek trigger, berikut adalah contoh sebuah tabel sederhana. Trigger akan dilepaskan, setiap kali tabel tersebut diisi dengan record baru.
Contoh Trigger For Insert :
Create table pegawai
(no_peg int Primary Key,
nama varchar(16)
)
Create Trigger tra_inspeg
On pegawai
For Insert
As
Print “Record baru : “+
Convert(char(30), getdate(),103)
Select * from inserted
Trigger akan dibuat dan setiap kali sesudah record baru diisi ke tabel,maka program trigger akan aktif. Di dalam trigger secara implisit dapat diakses “koleksi” record yang dimasukkan (“inserted”) maupun kumpulan record yang dihapus (“deleted”).
Record tersebut masuk ke dalam tabel virtual yang bernama “Inserted” dan “Deleted”.
Pada kasus “Insert” hanya ada tabel virtual “Inserted”, sedangkan pada “Delete” hanya terdapat tabel virtual “Deleted”.
Untuk “Update” muncul 2 buah tabel virtual, record yang lama masuk ke tabel “Deleted”, sedangkan record yang baru masuk ke tabel virtual “Inserted”.

1. Update 

CREATE TRIGGER Update_phoneName ON authors
FOR UPDATE AS
DECLARE @AuthorID VARCHAR(11)
DECLARE @NewphoneName VARCHAR(50)
SELECT @AuthorID = (SELECT _id FROM Inserted)
SELECT @NewphoneName = (SELECT lName + ', ' +au_fName FROM Inserted)
UPDATE TriggerTest SET au_name = @NewphoneName WHERE no_phne = @AuthorID
-------------------------------------
UPDATE authors SET au_fname = 'Fifi'
WHERE no_phne = '08933833939'


      2. Insert

CREATE TABLE [dbo].[TriggerTest]
([au_id] [int] NULL,
[au_name] [varchar] (50))
-------------------------------------
CREATE TRIGGER Insert_phoneName ON authors
FOR INSERT AS
DECLARE @AuthorID VARCHAR(11)
DECLARE @phoneName VARCHAR(50)
SELECT @AuthorID = (SELECT au_id FROM Inserted)
SELECT @ConcatName = (SELECT au_lName + ', ' + au_fName FROM Inserted)
INSERT TriggerTest values (@AuthorID,@ConcatName)
-------------------------------------
INSERT authors (no_phne, au_lname, au_fname, contract)
VALUES ('08933833939', 'Fifi', 'Pixy', 1)


        3. DELETE

CREATE TRIGGER Delete_phoneName
ON authors FOR DELETE AS
DECLARE @AuthorID VARCHAR(11)
SELECT @AuthorID =
(SELECT au_id FROM Deleted)
DELETE FROM TriggerTest WHERE phone = @AuthorID
-------------------------------------
DELETE FROM authors WHERE au_lname = 'Fifi'

Posted in: