SE KOL AH T INGGI MAN AJE MEN

SE KOL AH T INGGI MAN AJE MEN INFO RMA TIKA DAN KOMPU TER AMIKOM PURWOKERTO STORED PROCEDURE DAN TRIGGER

Definisi Stored Procedure ü Merupakan sekumpulan perintah-perintah T-SQL yang tersimpan dengan nama tertentu dan diproses sebagai sebuah kesatuan ü Secara sederhana dapat dikatakan sebagai sebuah sub -program yang tersimpan di database ü Stored procedure mirip dengan method pada berbagai bahasa pemrograman SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Kemampuan Stored Procedure ü Menerima parameter sebagai input dan mengembalikan nilai-nilai dalam bentuk parameter output ü Mengandung perintah-perintah program yang melakukan operasi didalam database, termasuk memanggil prosedur lainnya ü Mengembalikan suatu nilai status pada pemanggilannya untuk mengindikasikan kesuksesan atau kegagalan prosedur SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Keuntungan Stored Procedure ü ü Pemrograman menjadi modular Eksekusi program menjadi lebih cepat Mengurangi lalu lintas jaringan Dapat digunakan untuk mekanisme keamanan SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Aturan Pembuatan SP ü SP hanya dibuat pada database yang aktif atau yang sedang dipakai ü Nama SP harus sesuai dengan tujuan ü Biasanya, nama SP dimulai dengan awalan SP_ atau PROC_ ex: sp_add. Customer atau proc_add. Menu ü Untuk menjalankan SP pada SQL server digunakan keyword EXECUTE atau disingkat EXEC ex: EXEC sp_add. Menu SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Aturan Pembuatan SP ü SP hanya dibuat pada database yang aktif atau yang sedang dipakai ü Nama SP harus sesuai dengan tujuan ü Biasanya, nama SP dimulai dengan awalan SP_ atau PROC_ ex: sp_add. Customer atau proc_add. Menu ü Untuk menjalankan SP pada SQL server digunakan keyword EXECUTE atau disingkat EXEC ex: EXEC sp_add. Menu SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Sintaks Stored Procedure CREATE PROCEDURE nama_proc ( @parameter_1 Tipe. Data_1, . . . , @parameter_n Tipe. Data_n ) AS BEGIN perintah_SQL END Cara Pemanggilan EXEC nama_proc EXECUTE nama_proc SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Statement Penyusun SP Nama Procedure CREATE PROCEDURE DELETE_MHS (@MHSID INTEGER) Parameter BEGIN DELETE FROM MAHASISWA WHERE ID_MHS = @MHSID; END Body SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Membuat Stored Procedure create procedure ADDMENU @id varchar (5), @nama varchar(40), @harga int as if exists(select * from menu where id. Menu = @id) update menu set nama. Menu = @nama, harga = @harga where id. Menu = @id else insert into menu values(@id, @nama, @harga) create procedure DELMENU @id varchar (5) as delete from menu where id. Menu = @id SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Memperbaiki Stored Procedure ALTER PROCEDURE pr_honor AS select inisial, namainstruktur, honor from instruktur RETURN Menghapus Stored Procedure Sintaks: DROP PROCEDURE Nama. Prosedur Mengubah Nama Stored Procedure Gunakan sintaks SP_RENAME Contoh: SP_RENAME pr_honor, pr_honor 2 SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Stored Procedure dengan Parameter ü ü ü Parameter merupakan variabel memori yang digunakan untuk menerima suatu nilai dari pemangilnya. Stored procedure dapat menggunakan parameter sehingga program aplikasi yang memanggil stored procedure dapat mengakses database yang diperlukan sesuai dengan kondisi yang diminta yaitu dengan cara mengirimkan suatu nilai ke Stored Procedure melalui parameter. Sebagai contoh aplikasi untuk mencari data, nilai pencarian akan berubah-ubah tergantung permintaan user. SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Stored Procedure dengan Parameter ü Terdapat 3 mode parameter yaitu : IN, OUT dan INOUT : • IN (default) : akan mempassingkan nilai konstan dari memori ke stored procedure • OUT : akan mengambil nilai dari prosedur • IN OUT : akan mempassingkan nilai dari memori ke dalam procedure dan memungkinkan nilai yang berbeda dari prosedur dikembalikan ke memori dengan menggunakan parameter yang sama. ü Secara default stored procedure / function memiliki parameter IN. Kata IN tidak perlu ditambahkan sebelum nama parameter. SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Stored Procedure dengan Parameter • Sintaks parameter: @param as datatype = default [OUT/OUTPUT] Contoh: CREATE PROC pr_honor (@p 1 int, @p 2 char(16), . . ) AS Deklarasi_Variable. . . SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Stored Procedure dengan Parameter • Input parameter Digunakan untuk memberikan nilai dari pemanggil ke dalam SP. Contoh: CREATE PROC pr_honor (@p 1 INT) AS … • Output parameter Digunakan untuk mengembalikan nilai non-recordset dari SP ke pemanggil. Misalnya SP untuk insert, mengembalikan nilai hasil identity. Contoh: CREATE PROC pr_honor (@p 1 INT, @p 2 CHAR(16), @pout 1 INT OUT, @pout 2 CHAR(1) OUTPUT) AS … SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Output Parameter Nilai parameter bisa diolah pada program prosedur dan untuk kemudian parameter tersebut dapat diubah dan diberikan nilainya pada program yang memanggilnya. CREATE PROC m ( @p 1 int, @p 2 int, @h int OUTPUT) AS SELECT @h= @p 1 * @p 2 SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Eksekusi Output Parameter Contoh: DECLARE @hsl INT EXEC m 10, 20, @hsl OUTPUT PRINT 'hasil = ' + str (@hsl) • Pada contoh ada 3 parameter, yaitu p 1 dan p 2 sebagai input parameter dan h sebagai output parameter. • Saat eksekusi, p 1 adalah 10, p 2 adalah 20 dan hsl merupakan hasil perkalian. SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Definisi Trigger ü ü Trigger adalah blok SQL yang disimpan dalam database dan akan diaktivasi ketika kita melakukan statemen SQL baik DELETE, UPDATE atau INSERT pada sebuah tabel. Aktivasi trigger didasarkan pada event yang terjadi di dalam tabel tersebut sehingga trigger dapat membantu menjaga integritas dan konsistensi data. Contoh implementasi trigger adalah untuk mengeset/mengubah nilai kolom dalam suatu tabel. Trigger juga berguna untuk validasi data. SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Komponen Trigger ü Komponen trigger yaitu: 1. Trigger timing 2. Trigger Event 3. Nama Tabel 4. Trigger body 5. Klausa WHEN SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Komponen Trigger timing adalah waktu kapan trigger diaktifkan. Ada tida macam trigger timing, yaitu: 1. BEFORE : trigger dijalankan sebelum DML event pada tabel 2. AFTER : trigger dijalankan setelah DML event pada tabel 3. INSTEAD OF : trigger dijalankan pada sebuah view SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Komponen Trigger Event ada tiga kemungkinan: INSERT, UPDATE atau DELETE. Pada saat trigger event UPDATE, kita dapat memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk mengaktifkan sebuah trigger. Jika tidak ditentukan, maka perubahannya akan berlaku untuk semua kolom pada semua baris. • Nama Tabel : nama tabel atau view yang berhubungan dengan trigger • Trigger Body : mendefinisikan tindakan yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah trigger menjadi aktif • SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Membuat Trigger ü ü Secara umum event trigger dibagi dua, yaitu BEFORE (sebelum) atau AFTER (sesudah). Sintak umum untuk pembuatan sebuah statement trigger adalah: CREATE TRIGGER Nama. Trigger ON Nama. Tabel FOR INSERT | UPDATE | DELETE AS Begin declare -- berisi deklarasi variabel sql_statement [. . . n] End; SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Contoh Membuat Trigger ü Membuat trigger dengan nama tr_coba yang dapat menampilkanteks ‘data menu telah dihapus’ setiap kali selesai (AFTER) melakukan delete terhadap tabel menu. Sintaknya adalah sbb: CREATE TRIGGER tr_coba ON menu FOR DELETE AS FOR DELETE BEGIN AS BEGINPrint ‘data menu telah dihapus’ END; SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Membuat Trigger CREATE TRIGGER minvoucher on transaksi for INSERT as begin declare @idcust varchar(6), @total int select @idcust = id. Customer, @total = total from transaksi update customer set nominal. Isi = (nominal. Isi @total) where id. Customer = @idcust end SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Batasan Trigger ü Ada beberapa hal yang harus diperhatikan pada saat membuat sebuah trigger, yaitu: 1. Trigger hanya dapat digunakan untuk menuliskan statemen-statemen DML (Data Manipulation Language) seperti INSERT, UPDATE dan DELETE. 2. Trigger tidak dapat mengandung statemen DDL: CREATE, ALTER maupun DROP. 3. Trigger tidak boleh berisi statemen DCL: COMMIT dan ROLLBACK SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Skema database: • PERSON (Person. ID, Initials, Last. Name, DOB, Sex) • DOCTOR (Person. ID, Room. No, Phone. No, Date. Qualified, Date. Appointed) • PATIENT( Person. ID, Date. Registered, Registered. With) • APPOINTMENT (Doctor. ID, Patient. ID, Date, Time, Duration) • TREATMENT (Doctor. ID, Patient. ID, Condition, Treatment, Start, End) SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO

Soal: • Gunakan Skema database HOSPITAL di atas untuk membuat Trigger dan Store Procedure berikut ini: a. Buat trigger yang berfungsi untuk menambah record ke tabel AUDIT(Doctor. ID, Patient ID, DATE) setelah terjadi proses penghapusan record pada tabel APPOINTMENT. Nilai dari attribut Doctor. ID dan Patien. ID adalah nilai dari attribut tersebut pada record yang dihapus dan nilai dari DATE adalah tanggal penghapusan. b. Buat Store Procedure untuk menghitung jumlah pasien berdasarkan katagori Treatment yang diterima. Store Procedure tersebut memiliki parameter input “Treatment” dan parameter output “Total. Patient” SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AMIKOM PURWOKERTO