Monday, March 2, 2009

MYSQL STORE PROCEDURE

STORE PROCEDURE adalah salah satu feature yang terdapat di mysql 5.0 sedang-kan store procedure sendiri adalah
kumpulan-kumpulan SQL berupa rountine yang di simpan dalam database MySQL Server.

manfaat di store pocedure ini adalah:
  1. akan memperkecil trafik request dari aplikasi ke database karena semua proses bisnis dilakukan di database mysql dan aplikasi akan menerima hasil proses saja.
  2. ketika sebuah aplikasi yang didevelop terdiri dari berbagai bahasa pemrograman (misalkan desktop dan web) yang mana kedua tipe aplikasi tersebut membutuhkan hasil dan operasi database yang sama.
  3. untuk meningkatkan securiti, misalkan sebuah aplikasi yang login dengan user abcd tidak dapat mengakses tabel2 tertentu secara langsung tetapi mesti melalui store procedure, dengan cara ini akan meningkatkan ke-valid-tan data



oke berikut ini cara membuat store pocedure

dalam contoh dibawah ini kasus tentang sistem informasi sekolah, yang mana store procedure yang dibuat akan menangani tentang menghasilkan data siswa yang difilter bedasarkan kelas.


sturuktur tabel siswa

CREATE TABLE `data_siswa`.`tbl_siswa` (
`nis` char(10) NOT NULL,
`nama` varchar(255) NOT NULL,
`kelas` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1


store procedure untuk menampilkan data siswa dalam bentuk resultset

DELIMITER $$

CREATE PROCEDURE sp_tampil_siswa_kelas(p_kelas int)
BEGIN
SELECT * FROM tbl_siswa where KELAS = p_kelas;
END$$

DELIMITER ;


penjelasannya sebagai berikut:
  • DELIMITER = adalah untuk memberi tahu kepada myql soal delimiter yang digunakan, secara default menggunakan ; jadi bila ada tanda ; mysql akan mengartikan akhir dari statement, pada contoh di atas delimeter yang digunakan $$ jadi akhir statementnya adalah $$
  • CREATE PROCEDURE = adalah header untuk membuat store procedure
  • BEGIN END = adalah body dari store procedure, jadi semua SQL nya di tulis disini.
contoh pemanggilannya seperiti dibawah ini:

call sp_tampil_siswa_kelas(2);


oke bro... semoga membatu... selamat mencoba juga...

37 comments:

  1. bos coba bantu ane ya..., ini SP yang ada di MSSQL 2000 :
    ============================================
    CREATE PROCEDURE [dbo].[AutoNumber]
    @NoMember varchar(10) OUTPUT
    AS
    DECLARE @NoTertinggi numeric,
    @Indek int
    Select @NoTertinggi=MAX(CAST(RIGHT([No Member],8)As Numeric))
    From Member
    Set @NoMember = 'NM'
    If @NoTertinggi is null
    set @NoTertinggi = 0
    set @NoTertinggi = @NoTertinggi + 1
    set @Indek = LEN(@NoTertinggi)
    ulang:
    if @Indek <> 8
    begin
    set @NoMember = @NoMember + '0'
    set @Indek = @Indek + 1
    goto ulang
    end
    set @NoMember = @NoMember + cast(@NoTertinggi as varchar)

    GO
    =============================================
    kalo di mysql jadinya gimana ya...?, please help me

    ReplyDelete
  2. @anonim, silakan merujuk ke sini, om :

    http://rizky.prihanto.web.id/2009/04/xpautokode.html

    ReplyDelete
  3. Bozz..,klo nampilin datanya.. di php pake mysqli ada contoh g'..? sblmnya makasih y.. :)

    ReplyDelete
  4. he..he... slh ksh profile, y itu td bozz yg Anonim,sama ajarin script buat ngilangin property block trs klik kanan copy,brusan aq tes blok mo ngopy ulang commentq koq g' bs he..he.. :) klo bs langsung aj YM ke IDqu bozz : blackant200885, Salut sama si bozz.. mantepzzz..

    ReplyDelete
  5. oke bozz..
    klw lagi mood boz...
    dan klw gw bisa
    ntar di posting di blog aja...

    ReplyDelete
  6. thank Bro atas sharingnya . . . . . . , sebenarnya mana bagusan pake view apa store prosedure saya baru belajar pake mySql dan vb6 dan function tu apa seh . . .? sorry ya banyak nanya. thank before.

    ReplyDelete
  7. view dan store procedure ngak yang lebih bagus dan bukan sesuatu yang bisa dibandingkan, karena keduanya memiliki peran yang berbeda.

    store procedure atau view yang dipakai tergantung dari pesoalannya. apa itu dan buat apa view dapat dilihat di http://dendieisme.blogspot.com/2009/02/view-di-mysql.html

    apa itu function dan gunanya function dpt dilihat di
    http://dendieisme.blogspot.com/2009/03/membuat-function-di-mysql.html

    ReplyDelete
  8. bisa bantu ngga? saya punya sp dari interbase nih kalo di mySQL ny gmn?

    CREATE PROCEDURE REKAPNONSIMPANPINJAM (
    PTGL1 VARCHAR(25),
    PTGL2 VARCHAR(25)
    ) RETURNS (
    XTANGGAL TIMESTAMP,
    XJENIS VARCHAR(50),
    XTYPE VARCHAR(20),
    XNILAI DOUBLE PRECISION
    ) AS
    BEGIN
    FOR
    SELECT TANGGAL,JNSTRANS,TYPETRANS,NILAI FROM NONSIMPANPINJAM
    WHERE TANGGAL BETWEEN :PTGL1 AND :PTGL2 AND TAG_SHU = 0
    ORDER BY TYPETRANS,TANGGAL
    INTO :XTANGGAL, :XJENIS, :XTYPE, :XNILAI
    DO
    BEGIN

    SUSPEND;
    END
    END

    ReplyDelete
  9. BOzz Dendi..,klo blh minta ajarin ato ksh contoh script buat form php yg ada ListBox,CheckBox,ComboBox yg kyak yg di Portofolionya yg Sirap Statistik Pajak Reklame, itu pake plugin JQuery ato apa..?

    ReplyDelete
  10. @Semot Ireng
    itu ngak pake JQUERY mas...
    tapi bikinya pake Adobe Flex...

    ReplyDelete
  11. Bos dendi, mau tanya ini, untuk MySQL, apakah ada keuntungan dari sisi kecepatan klo menggunakan Stored Procedure ?

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. iya ada. karena store prosedur disimpan didalam MySQL sehinga sangat mengurangi jumlah lalu lintas atau bolak-balik melalui jaringan antara database mysql dan aplikasi anda.

    ReplyDelete
  14. Terima kasih atas jawabannya ..
    Mau tanya 1 lagi nih bos. Bagaimana dengan view di mysql? Rencana saya mau menyingkat query yang banyak joinnya menjadi sebuah view. Apakah bisa lebih ringan dan meningkatkan speed juga? karena ada yang bilang view di MySQL tidak seperti view di SQLServer yang bisa meningkatkan speed dan lebih ringan

    ReplyDelete
  15. Dengan menggunakan view selain menyederhanakan pemanggilan query yang ribet, dapat meningkatkan performace karena mysql meyimpan hasil view tersebut di cache, apabila telah terjadi perubahan dari sumber table maka data akan mengambil dari sumber table dan mengupdate cache.

    --IMHO--

    ReplyDelete
  16. mas, numpang tanya neh. perbandingan store procedur ama trigger seperti apa?

    ReplyDelete
  17. berbeda mas dalam penggunaannya..

    Store Procedure : adalah sekumpulan sql (kita juga dpa menuliskan logik program seperti pernyataan dan pengulangan)berupa routine yang disimpan dalam database


    Triger : adalah sebuah aksi yang dilakukan oleh mysql ketika terjadi sebuah event/kejadian pada suatu tabel, misalkan ketika terjadi penambahan data pada sebuah table maka mysql akan menjalankan sebuah query atau memanggil strore procedure.

    ReplyDelete
  18. Om, saya mau nanya nih,,, apakah stored procedure di mysql bisa, buat acuan report, utk crystal report,..kebetulan saya mau migration ke mysql, selam ini saya pakai SQL 2000 emang sih lebih gampang tapi kebentur licency-nya OM...Muahallllllll

    ReplyDelete
  19. emang berapa ribu dolar gitu om ???

    saya belum pernah nih punya pengalama mysql ke crytal report...

    ReplyDelete
  20. bos kalo mau bikin view harus pake inner join nya yah ? maaf saya pemula buat mysql ^_^

    ReplyDelete
  21. bikin view tidak harus query mengandung join.

    ReplyDelete
  22. jadi asalkan udah ada tabel sama isi tabel nya kita udah bisa bikin view nya yah ?

    ReplyDelete
  23. iya betul asalkan sudah ada tabel nya...

    ReplyDelete
  24. Mas dendie, ane mw nanya nih.. Apa bener kalo di View tdk boleh mengandung variabel/parameter..? cz q coba bkin view yg mengandung variabel & perhitungannya tp mlah error..
    Thanks be4.

    ReplyDelete
  25. iya.. klw mau menuliskan logika pemrograman kemudian deklarasi variable seperti layaknya bahasa pemrograman dapat di dituliskan di store procedure dan function yang kemudian di panggil di dalam view.

    ReplyDelete
  26. saya baru mau belajar bikin sp di mysql,sebelumnya saya pake mssql.untuk melihat sp di mysql itu dimana ya ?? misalnya untuk edit ulang
    terima kasih sebelumnya

    ReplyDelete
  27. Kalau Anda menggunakan MySQL Client seperti MySQL AB, Navicat, SQLYog sangat mudah cukup klik Database yang dituju maka akan maka muncul semua SP nya. Tetapi apabila Anda hanya memiliki console base saja dapat lihat caranya ini http://dendieisme.blogspot.com/2010/12/mysql-show-procedure-function-triggers.html

    ReplyDelete
  28. bro..boleh nanya ngak.?aq kewalahan dalam membuat query di phpadmin (Mysql)tentang insert 100 rows ke dalam 1 table dan 1 field..tahu ngak cara penulisannya query, tanpa kita inputkan satu persatu..?

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. This comment has been removed by the author.

    ReplyDelete
  31. @hesa contohnya seperti ini mas

    CREATE PROCEDURE `doiterate`()
    BEGIN
    DECLARE p1 int;
    SET p1 = 0;

    label1: LOOP
    SET p1 = p1 + 1;
    INSERT INTO pegawai (nama) values ('dendie');
    IF p1 < 100 THEN ITERATE label1; END IF;
    LEAVE label1;
    END LOOP label1;

    END;

    kemudian cukup dengan memangilnya store procedure tersebut

    call doiterate();

    ReplyDelete
  32. bos dendi, kalo convert varchar di sql spt contoh ini gimana ya, thanks,
    ALTER PROCEDURE [dbo].[SP_VA_INVOICE_LIST]
    @aevi_entity_id AS varchar(20) ,
    @aevi_branch_id AS varchar(20) ,
    @aevi_sub_branch_id AS varchar(20),
    @aevi_month AS varchar(2) ,
    @aevi_year AS varchar(4) ,
    @aevi_cust_bill_1 AS varchar(50) ,
    @aevi_invoice_no AS varchar(20)
    AS
    SET NOCOUNT ON

    DECLARE @sqlstr VARCHAR(4000)

    DECLARE
    @calmonth AS INT,
    @imonth AS INT,
    @iyear As INT,
    @isWhere as Smallint

    BEGIN
    SET @sqlstr = 'SELECT ai_so_number, ai_invoice_number, ai_cust_bill_1, ai_cust_bill_name, ai_entity_id , ai_branch_id, br_branch_desc, ai_sub_branch_col, '
    SET @sqlstr = @sqlstr + 'MONTH(ai_due_date) as ai_month, YEAR(ai_due_date) ai_year, br_sub_branch_desc, (DATEPART(week,GETDATE()) - DATEPART(week,(GETDATE()-DATEPART(day,GETDATE())+1))) as WeekCutOff, '
    SET @sqlstr = @sqlstr + 'ai_due_date, ai_no_of_angsuran , ai_original_invoice_currency, ai_prd_group_rd, ai_kwitansi_no, ai_kwitansi_status, ai_collector_id, '
    SET @sqltr = @sqlstr + ' bla..bla..bla''
    EXEC (@sqlstr)
    END

    ReplyDelete
  33. This comment has been removed by a blog administrator.

    ReplyDelete
  34. thenkyu artikel yang singkat dan cukup jelas

    ReplyDelete
  35. saya mau tanya, klau "....chaset = latin1". itu maksudnya apa ya pak..??

    terima kasih.

    ReplyDelete