Selasa, 20 September 2011

Database Server : Stored Procedure, Function, dan Trigger pada SQL Server

Terdapat 3 bagian penting Program yang tersimpan dalam database Server

1. Stored Procedure
Sejumlah perintah dalam kode program dan beberapa parameter input, dan sejumlah hasil atau keluaran. Cara pemanggilannya dengan mengeksekusi nama proscdure tersebut.
2. Stored Function 
Fungsi hampir sama dengan stored procedure. Function mempunyai sejumlah paramter input dan hanya mengembalikan satu output, standar perintah yang digunakan menggunakan perintah standar SQL.
3. Trigger 
Suatu program yang akan melakukan respon dalam aktivitas database, berkaitan perintah-perintah DML (insert, update, delete) operasional pada tabel. Biasanya digunakan untuk otomatisasi atau validasi.
Pemrograman Stored Prosedure dalam MySQL.
Stored procedure adalah permasalah yang cukup kompleks untuk dibahas, adapun bagian penting pada stored procedure :
Kapan program akan diabut, parameter yang akan dilewatkan dan hasil yang dikeluarkan, interaksi dalam basis data, menciptakan stored precedure, fungsi dan trigger dalam bahasa pemrograman.
Apa yang dibutuhkan? 
• MySQL Server
• Teks editor vi, emacs,
• MySQL Query Browser, SQLyog dll
Manciptakan Stored Procedure
Beberapa perintah untuk membuat stored procedure:
CREATE PROCEDURE , CREATE FUNCTION , atau CREATE TRIGGER
Contoh :
DELIMITER $$
DROP PROCEDURE IF EXISTS Kata()$$;
CREATE PROCEDURE Kata()
BEGIN
SELECT ‘AKAKOM’;
END$$
DELIMITER ;







  • DELIMITER $$ akhir dari peritah body (badan/skrip mysql)
  • DROP PROCEDURE IF EXISTS Kata()$$; perintah untuk menghapus jika prosedurnya sudah pernah ada.
  • CREATE PROCEDURE Kata( ) mencitapkan prosedur degan nama Kata ( isi parameter kalau ada ),
  • BEGIN awal perintah/blok perintah dan END akhir perintah
  • SELECT perintah menapilkan

  • Cara memanggilanya :
    Enter password: ****
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 6.0.4-alpha-community-log MySQL Community Server (GPL)
    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
    mysql> use coba;
    Database changed
    mysql> CALL Kata();
    +——–+
    | AKAKOM |
    +——–+
    | AKAKOM |
    +——–+
    1 row in set (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
    mysql>
    Variabel
    Variabel dalam bahasa SQL, fungsinya sama dengan bahasa pemrograman paxda umumnya.
    Perintahnya menggunakan DECLARE, dan untuk memberikan nilai menggunakan perintah SET.
    Parameter
    Parameter adalah suatu nilai yanga akan dilewatkan atau dimasukan untuk diproses dalam fungsi atau prosedure.
    Contoh
    DELIMITER$$
    CREATE PROCEDURE Demo_Var()
    BEGIN
    DECLARE x int;
    DECLARE y int;
    SET x=10;
    SET y=20;
    SELECT x+y;
    END$$;
    DELIMITER;
    CALL Demo_var
    Hasil
    +——+
    | x+y |
    +——+
    | 30 |
    +——+
    1 row in set (0.00 sec)
    Sebuah Procedure dengan mengunakan parameter masukan, akan diproses dalam fungsi dan kemudian timapilkan, hasilnya.
    Contoh :
    DELIMITER$$
    CREATE PROCEDURE my_akar(masukan INT)
    BEGIN
    DECLARE hasil FLOAT;
    SET hasil= SQRT(masukan);
    SELECT hasil;
    END$$;
    DELIMITER;
    Cara memanggil prosedur
    CALL my_akar(25);
    Hasil keluaran sebagai berikut :
    mysql> CALL my_akar(25);
    +——-+
    | hasil |
    +——-+
    | 5 |
    +——-+
    1 row in set (0.03 sec)
    Query OK, 0 rows affected (0.05 sec)
    Prosedure menggunakan parameter masukan dan keluaran (INT dan OUT)
    Contoh :
    DELIMITER$$
    CREATE PROCEDURE my_sqrt(masukan INT, OUT nilai_keluar FLOAT)
    BEGIN
    SET nilai_keluar= SQRT(masukan);
    END$$;
    DELIMITER;
    Cara menjalankan prosedur ada dua tahap
    CALL my_sqrt(9,@out_value)
    SELECT @out_value
    Penjelasan :
    Memampilkan nilai keluaran :
    mysql> CALL my_akar(25);
    +——-+
    | hasil |
    +——-+
    | 5 |
    +——-+
    1 row in set (0.03 sec)
    Query OK, 0 rows affected (0.05 sec)
    mysql> CALL my_sqrt(9,@out_value) ;
    Query OK, 0 rows affected (0.05 sec)
    mysql> SELECT @out_value;
    +————+
    | @out_value |
    +————+
    | 3 |
    +————+
    Memampilkan nilai keluaran :
    Kondisional
    Perintah kondisional, untuk melakukan uji kondisi terapat satu masukan dan dua piliahan dari keluaran, kondisi benar atau asalah. Perintahnya menggunakan IF, atau CASE.
    DELIMITER$$
    CREATE PROCEDURE Diskon(pembayaran NUMERIC(10,2),OUT diskon NUMERIC(8,2))
    BEGIN
    IF (pembayaran > 100000) THEN
    SET diskon=pembayaran * 0.8;
    ELSEIF (pembayaran > 500000) THEN
    SET diskon=pembayaran * 0.9;
    ELSE
    SET diskon=pembayaran;
    END IF;
    END$$;
    DELIMITER;
    Cara memanggilnya :
    mysql> CALL diskon(100000,@new_price) ;
    Query OK, 0 rows affected (0.05 sec)
    mysql> SELECT @new_price;
    +————+
    | @new_price |
    +————+
    | 100000.00 |
    +————+
    1 row in set (0.00 sec)
    Loop
    Loop adalah proses perulangan. Dalam MYSQL ada 3 type loop :
    • Loop sederhana menggunakan LOOP dan END LOOP c
    • Loop yang disertai kontrol kondisi benar atau salah jika benar akan loop, perintannya menggunakan WHILE dan END WHILE
    • Perulangan yang menggunakan REAPEAT dan UNTIL
    Contoh :
    DELIMITER$$
    CREATE PROCEDURE Loop_sederhana()
    BEGIN
    DECLARE konter INT DEFAULT 0;
    atas:LOOP
    SET konter=konter+1;
    IF konter=10 THEN
    LEAVE atas;
    END IF;
    SELECT konter;
    END LOOP atas;
    SELECT ‘Konter ke 10′;
    END$$;
    DELIMITER;
    LEAVE untuk mangarah ke label atas.
    Interaktif dengan Database
    Stored Prosedure juga basi melibatkan data dalam tabel. Ada 4 bagian tipe interaksi antara lian :







  • Hasil stored procedure dari perintah SQL menghasilkan nilai balik satu baris yang dimasukan ke dalam variabel lokal.
  • Create “cursor” hasil iterasi, yanng mengasilkan baris disimpan ke kursor
  • Eksekusi perintah SQL menghasilkan, dengan perintah CALL progarm akan menghasilkan sejumlah baris.
  • Untuk perintah INSERT, UPDATE, DELETE, tidak mengasilkan record set

  • SELECT INTO Menggunakan Variabel Lokal
    Dalam Stored procedure seperti halnya pemroraman biasa, terdapat variabel lokal, yaitu variabel yang menampung data sementara. Untuk mengisikan nilai variabel tersebut dengan perintah INTO
    Contoh :
    Buatalah tabel JUALAN
    CREATE TABLE JUALAN(no_tran char (5),
    kd_plg char(5),
    tgl date,
    nil_tran int);
    Tambahkan rekaman
    INSERT INTO JUALAN VALUES(’00001′,’P0001′,’2009-07-10′,4000000);
    INSERT INTO JUALAN VALUES(’00002′,’P0001′,’2009-07-11′,5000000);
    INSERT INTO JUALAN VALUES(’00003′,’P0002′,’2009-07-11′,700000);
    INSERT INTO JUALAN VALUES(’00004′,’P0003′,’2009-09-10′,4030000);
    Menciptakan Stored procedure
    DELIMITER$$
    DROP PROCEDURE IF EXISTS pelanggan_jualan;
    $$
    CREATE PROCEDURE pelanggan_jualan(in_kd_plg char(5))
    BEGIN
    DECLARE Total_jual INT ;
    SELECT SUM(nil_tran) INTO Total_jual
    FROM JUALAN
    WHERE kd_plg = in_kd_plg;
    SELECT CONCATE(‘Jumlah Transaksi Pelanggan No,:’,in_kd_plg,’ adalah =’,Total_jual);
    END;
    $$
    Penjelasan
    • DELIMITER : awal skrip
    • DROP PROCEDURE IF EXISTS : menghapus jika prosedure pelanggan_jualan ada, jika tidak diabaikan.
    • BEGIN : awal blok perintah (proses)
    • DECLARE Total_jual INT : bagian deklarasi variabel
    • SELECT SUM(nil_tran) INTO Total_jual FROM JUALAN WHERE kd_plg = in_kd_plg; : perintah SQL menpilkan
    • SELECT CONCATE(‘Jumlah Transaksi Pelanggan No,:’,in_kd_plg,’ adalah =’,Total_jual); : hasil ditampilkan kelayar dengan keterangannya
    • END; akhir dari blok proses
    Cara memanggil Stored precedure seperti pada gambar berkut :
    Melibatkan Tabel
    DELIMITER$$
    DROP PROCEDURE IF EXISTS cari;
    $$
    CREATE PROCEDURE cari(in_no_mhs char(9))
    BEGIN
    SELECT no_mhs,nama,alamat,agama
    FROM mhs
    WHERE no_mhs=in_no_mhs;
    END;
    $$
    Cara menjalankan Stored Procedure
    Contoh kasus   Bualah tabel :   Tabel NILAI   CREATE TABLE nilai (no_mhs char(9), kd_mk char(5), nilai char(1), primary key (no_mhs,kd_mk));   INSERT INTO NILAI VALUES('05541001','T1001','A'); INSERT INTO NILAI VALUES('05541001','P1201','C'); INSERT INTO NILAI VALUES('05541001','T1201','A'); INSERT INTO NILAI VALUES('05541001','T2202','B'); INSERT INTO NILAI VALUES('05541001','T1401','B'); INSERT INTO NILAI VALUES('05541001','P2202','A'); INSERT INTO NILAI VALUES('05541001','T3201','B');     INSERT INTO NILAI VALUES('05541002','T1001','D'); INSERT INTO NILAI VALUES('05541002','P1201','E'); INSERT INTO NILAI VALUES('05541002','T1201','C'); INSERT INTO NILAI VALUES('05541002','T2202','C'); INSERT INTO NILAI VALUES('05541002','T1401','D'); INSERT INTO NILAI VALUES('05541002','P2202','B'); INSERT INTO NILAI VALUES('05541002','T3201','B');     Tabel MKULIAH   CREATE TABLE mkuliah (kd_mk char(5), nm_kul char(25), sks int, primary key (kd_mk));   INSERT INTO mkuliah VALUES('T10001','Bahasa Inggris I',2); INSERT INTO mkuliah VALUES('T10002','Bahasa Inggris II',2); INSERT INTO mkuliah VALUES('TI2001','Kalkulus I',3); INSERT INTO mkuliah VALUES('T22001','Kalkulus II',3); INSERT INTO mkuliah VALUES('TI4001','Algoritma dan Pemrogaman',3); INSERT INTO mkuliah VALUES('T22002','Jaringan Komputer',3); INSERT INTO mkuliah VALUES('T32001','Basis Data',3); INSERT INTO mkuliah VALUES('P12001','Prak. Pemprogaman Web 1',1); INSERT INTO mkuliah VALUES('P22001','Prak. Pemprogaman Web 2',1); INSERT INTO mkuliah VALUES('P12001','Prak. Pemprogaman Dasar',1);     Menapilkan nilai no_mhs='05541001' SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks FROM nilai,mkuliah WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'   mysql> use akademik; Database changed mysql> SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks -> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+-------+-------------------------+-------+------+ | no_mhs | kd_mk | nm_kul | nilai | sks | +----------+-------+-------------------------+-------+------+ | 05541001 | P1201 | Prak. Pemprogaman Dasar | C | 1 | | 05541001 | P2202 | Prak. Pemprogaman Web 2 | A | 1 | | 05541001 | T1001 | Bahasa Inggris I | A | 2 | | 05541001 | T2202 | Kalkulus II | B | 3 | | 05541001 | T3201 | Basis Data | B | 3 | +----------+-------+-------------------------+-------+------+ 5 rows in set (0.00 sec) mysql>   Manampilkan trasnkip dengan bobot nilai, sks dan jumlah dan bobot   mysql> SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks -> CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 -> WHEN nilai.nilai='B' THEN mkuliah.sks * 3 -> WHEN nilai.nilai='C' THEN mkuliah.sks * 2 -> WHEN nilai.nilai='D' THEN mkuliah.sks * 1 -> ELSE 0 -> END bobot -> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+-------+-------------------------+-------+------+-------+ | no_mhs | kd_mk | nm_kul | nilai | sks | bobot | +----------+-------+-------------------------+-------+------+-------+ | 05541001 | P1201 | Prak. Pemprogaman Dasar | C | 1 | 2 | | 05541001 | P2202 | Prak. Pemprogaman Web 2 | A | 1 | 4 | | 05541001 | T1001 | Bahasa Inggris I | A | 2 | 8 | | 05541001 | T2202 | Kalkulus II | B | 3 | 9 | | 05541001 | T3201 | Basis Data | B | 3 | 9 | +----------+-------+-------------------------+-------+------+-------+ 5 rows in set (0.00 sec)   mysql>   menghitung IP   mysql> SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks, -> SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 -> WHEN nilai.nilai='B' THEN mkuliah.sks * 3 -> WHEN nilai.nilai='C' THEN mkuliah.sks * 2 -> WHEN nilai.nilai='D' THEN mkuliah.sks * 1 -> ELSE 0 -> END) as jum_bobot -> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+---------+-----------+ | no_mhs | jum_sks | jum_bobot | +----------+---------+-----------+ | 05541001 | 10 | 32 | +----------+---------+-----------+ 1 row in set (0.00 sec) mysql>   Ip= jum_bobot/jum_sks   mysql> SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks, -> SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 -> WHEN nilai.nilai='B' THEN mkuliah.sks * 3 -> WHEN nilai.nilai='C' THEN mkuliah.sks * 2 -> WHEN nilai.nilai='D' THEN mkuliah.sks * 1 -> ELSE 0 -> END) as jum_bobot, -> SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 -> WHEN nilai.nilai='B' THEN mkuliah.sks * 3 -> WHEN nilai.nilai='C' THEN mkuliah.sks * 2 -> WHEN nilai.nilai='D' THEN mkuliah.sks * 1 -> ELSE 0 -> END) /SUM(mkuliah.sks) as ip -> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+---------+-----------+--------+ | no_mhs | jum_sks | jum_bobot | ip | +----------+---------+-----------+--------+ | 05541001 | 10 | 32 | 3.2000 | +----------+---------+-----------+--------+ 1 row in set (0.00 sec) mysql>   Manggunakan Stored Procedure  
    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_transkrip`(in_no_mhs char(9))
    BEGIN
    SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks,
    CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4
    WHEN nilai.nilai='B' THEN mkuliah.sks * 3
    WHEN nilai.nilai='C' THEN mkuliah.sks * 2
    WHEN nilai.nilai='D' THEN mkuliah.sks * 1
    ELSE 0
    END bobot
    FROM nilai,mkuliah
    WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs=in_no_mhs
    /*urut semerter */
    ORDER BY  SUBSTR(nilai.kd_mk,2,1);
    END$$
    DELIMITER ;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_ip`(in_no_mhs char(9))
    BEGIN
    SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks,
    SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4
    WHEN nilai.nilai='B' THEN mkuliah.sks * 3
    WHEN nilai.nilai='C' THEN mkuliah.sks * 2
    WHEN nilai.nilai='D' THEN mkuliah.sks * 1
    ELSE 0
    END) as jum_bobot,
    SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4
    WHEN nilai.nilai='B' THEN mkuliah.sks * 3
    WHEN nilai.nilai='C' THEN mkuliah.sks * 2
    WHEN nilai.nilai='D' THEN mkuliah.sks * 1
    ELSE 0
    END) /SUM(mkuliah.sks) as ip
    FROM nilai,mkuliah
    WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs=in_no_mhs;
    END$$
    DELIMITER ;
    Daftar Pustaka
    Steven Feuerstein, Guy Harrison, 2006, MySQL Stored Procedure Programming, O’Reilly

    4 Komentar:

    1. mas artikelnya bgus,,sy sebagai pemula elajar sql...sy mau bertanya..mohon bantuanya,,,sy mau membuat Store Procedur dengan declare dan set nama databasenya bisakah kalau iya gm caranya?? karena sy igin bs dengan gampang merubah nama database ketika menjalankan sp sy,,mohon petunjuknya,,,terima kasih ats bantuan dan waktunya..bs d emal ke sy:: bpkp.ridwan@gmail.com

      BalasHapus
    2. Ass...saya tidak menyanka kalau saya sudah bisa sesukses ini dan ini semua berkat bantuan MBAH RAWA GUMPALA,saya yang dulunya bukan siapa-siapa bahkan saya juga selalu dihina orang dan alhamdulillah kini sekaran saya sudah punya segalanya,itu semua atas bantuan beliau.Saya sangat berterimakasih banyak kepada MBAH RAWA GUMPALA atas bantuan nomor dan dana ghaibnya,alhamdulillah kini saya sudah bisa membuka usaha kembali yang dulunya pakum karna masalah faktor ekonomi dan kini kami sekeluarga sudah sangat serba berkecukupan dan tidak pernah lagi hutang sana sini,,bagi anda yang punya masalah keuangan jangan sungkan-sungkan untuk menhubungi MBAH RAWA GUMPALA karna insya allah beliau akan membantu semua masalah anda dan baru kali ini juga saya mendaptkan para normal yang sangat hebat dan benar-benar terbukti nyata,ini bukan hanya sekedar cerita tapi inilah kisah nyata yang benar-benar nyata dari saya




      (((( BUKA BLO DANA GHAIB DAN NOMOR GHAIB MBAH RAWA GUMPALA ))))







      Ass...saya tidak menyanka kalau saya sudah bisa sesukses ini dan ini semua berkat bantuan MBAH RAWA GUMPALA,saya yang dulunya bukan siapa-siapa bahkan saya juga selalu dihina orang dan alhamdulillah kini sekaran saya sudah punya segalanya,itu semua atas bantuan beliau.Saya sangat berterimakasih banyak kepada MBAH RAWA GUMPALA atas bantuan nomor dan dana ghaibnya,alhamdulillah kini saya sudah bisa membuka usaha kembali yang dulunya pakum karna masalah faktor ekonomi dan kini kami sekeluarga sudah sangat serba berkecukupan dan tidak pernah lagi hutang sana sini,,bagi anda yang punya masalah keuangan jangan sungkan-sungkan untuk menhubungi MBAH RAWA GUMPALA karna insya allah beliau akan membantu semua masalah anda dan baru kali ini juga saya mendaptkan para normal yang sangat hebat dan benar-benar terbukti nyata,ini bukan hanya sekedar cerita tapi inilah kisah nyata yang benar-benar nyata dari saya.

      BalasHapus
    3. terimakasih atas info yang agan berikan, semoga bermanfaat bagi para programer.

      BalasHapus