Prak. SBD 2 - Store Procedure [PDF]

Citation preview

4/8/2020



STORED PROCEDURE Sigit Setyowibowo, ST., MMSI STMIK PPKIA Pradnya Paramita Malang



cara menggunakan aplikasi navicat 1. Setelah selesai menginstall navicat nya pasti ada ikon navicat dan klik 2 X ya gan untuk membuka aplikasinya. 2. Nah setelah dibuka maka tampilan nya akan seperti dibawah ini



1



4/8/2020



cara menggunakan aplikasi navicat 3. Nahjika kita mau membuka database baru kita klik saja di CONNECTION, kemudian pilih jenis database apa yang mau kita buka



cara menggunakan aplikasi navicat 4. Disini saya akan mencoba membuka database saya sendiri yaitu database MYSQL. (maka di step no.3 kita memilih nya yg MYSQL). Dan kemudian kita isi sesuai penjelasan nya sebagai berikut : - Connection name : Nama terserah agan mau namaiin apa :-) - Port : port defautnya mysql : 3306, sql server : 1433, dll - user namae : sesuaikan dengan username database yang mau agan buka. - Password : sesuaikan dengan password database yang mau agan buka.



2



4/8/2020



cara menggunakan aplikasi navicat 5. Kemudian jika sudah terisi sesui informasi database nya, Kita test connection dulu ya gan sebelum disimpan. Nah jika semua nya benar dan di test connection pasti hasil nya sukses seperti gambar ini :



cara menggunakan aplikasi navicat 6. Setelah itu baru kita OK dan otomatis akan tampil database yang kita buka barusan gan. Disini misal nya kan pake database saya. Dan isi database saya seperti berikut :



3



4/8/2020



cara menggunakan aplikasi navicat 7. Jika sudah terbuka database nya, Maka sudah terserah agan mau gimana sesuai rencana agan sendiri.. hahaha Sebagai contoh saya akan membuka database personalia dan kemudian saya buka table pegawai. Dan hasil nya bisa di lihat seperti gambar ini gan :



cara menggunakan aplikasi navicat 7. Setelah itu anda klik new query. Coba anda ketik Select * from pegawai Kemudian anda klik tombol run



4



4/8/2020



cara menggunakan aplikasi navicat



Jalankan perintah ini • select nama from pegawai where Tmplahir like 'M%'; • SELECT nama, tmplahir FROM pegawai WHERE tmplahir IN(‘Blitar', 'madiun'); • SELECT nama, gaji FROM pegawai WHERE gaji BETWEEN 9000 AND 11000;



5



4/8/2020



STORED PROCEDURE



STORED PROCEDURE • Merupakan sekumpulan sintaks SQL yang tersimpan pada server • Memiliki beberapa keunggulan ‒ Karena sintaks sql pada stored procedure tersimpan pada server maka pemanggilan lebih cepat. ‒ Reuseable artinya cukup ditulis sekali dapat digunakan berkalikali ‒ Meningkatkan keamanan



6



4/8/2020



STORED PROCEDURE • beberapa kelemahan dalam penggunaan stored procedure • Beban kerja server meningkat karena prosedur yang seharusnya dapat dikerjakan pada aplikasi pemanggil harus dikerjakan pada server • Lebih sulit dalam debugging ketika strored procedure yang dibuat semakin banyak.



Menggunakan MySQL DELIMITER untuk prosedur tersimpan • Perhatikan bahwa Anda akan mempelajari sintaks membuat prosedur tersimpan di tutorial berikutnya. DELIMITER $$ CREATE PROCEDURE sp_name() BEGIN -- statements END $$ DELIMITER ;



Bila memakai MySql Browser Workbench dan Navicat tidak perlu delimiter



Dalam kode ini: 1. ubah pembatas default menjadi $$ atau // 2. gunakan (;) di badan prosedur tersimpan dan $$ atau // setelah kata kunci AKHIR untuk mengakhiri prosedur tersimpan. 3. ubah pembatas default kembali ke titik koma (;)



7



4/8/2020



Membuat Stored Procedure • Terdapat tiga komponen dalam membuat stored procedure • Input parameters : menanganiinput • Output paramters: menanganioutput yang dihasilkan • Body : berisistatement SQL untukdieksekusi • Sintaks: Create procedure nama_prosedur(param1, param2,…,paramn) Begin



end



CONTOH 1 • Membuat Stored Procedure showAllPegawai() untuk menampilkan seluruh Pegawai CREATE PROCEDURE showAllPegawai() BEGIN SELECT * FROM pegawai; END



8



4/8/2020



Cara menulis store procedure



Cara menulis store procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `ShowAllPegawai`() BEGIN #Routine body goes here... Select * from pegawai; END



9



4/8/2020



CONTOH 1 Memanggil stored procedure CALL showAllPegawai();



Jadi, setiap ingin menampilkan Pegawai kita tidak perlu membuat kode SQL seperti biasanya. Cukup simpan kode SQL di Stored Procedure dan bisa kita panggil dan gunakan berulang – ulang



Menampilkan Daftar Prosedur yang Disimpan • Pernyataan berikut menunjukkan semua prosedur tersimpan di server MySQL saat ini:



SHOW PROCEDURE STATUS;



10



4/8/2020



Menampilkan Daftar Prosedur yang Disimpan Jika Anda hanya ingin menunjukkan prosedur tersimpan dalam basis data tertentu, Anda dapat menggunakan klausa WHERE di SHOW PROCEDURE STATUS seperti yang ditunjukkan dalam pernyataan berikut:



SHOW PROCEDURE STATUS WHERE search_condition; Sebagai contoh, pernyataan ini mencantumkan semua prosedur tersimpan dalam sampel database Personalia: SHOW PROCEDURE STATUS WHERE db = ‘personalia';



Menampilkan Daftar Prosedur yang Disimpan Jika Anda ingin menemukan prosedur tersimpan yang namanya mengandung kata tertentu, Anda dapat menggunakan klausa LIKE sebagai berikut:



Pernyataan berikut menunjukkan semua prosedur tersimpan yang namanya mengandung kata ALL:



SHOW PROCEDURE STATUS LIKE '%ALL%'



11



4/8/2020



Menghapus Stored Procedure • Untuk menghapus stored procedure dapat menggunakan perintah drop diikuti dengan nama prosedur yang akan dihapus • Sintaks:



Drop procedure nama_prosedur;



Drop procedure selectAllPegawai;



VARIABLE



12



4/8/2020



VARIABLE adalah objek data bernama yang nilainya dapat berubah selama eksekusi Prosedur Tersimpan. Kita biasanya menggunakan variabel dalam Prosedur Tersimpan untuk menyimpan hasil langsung. Variabel-variabel ini bersifat lokal untuk Prosedur Tersimpan.



MySQL Stored Procedure Variables Various MySQL variables



Global



Session



Local



Variabel session merupakan variable



Variabel local merupakan variable



Variabel global ada dan berlaku di



yang mempunyai scope dan life time



yang



semua session. Variabel ini dibuat oleh



pada session saat koneksi ke server



Procedure/Function atau di Triger, dan



system bukan oleh user. Variabel



MySQL dimulai dan diakhiri. Bisa



hanya



session



digunakan



Procedure/Function atau di Triger



dapat



diakses



dengan



lintas



Prosedur/Fungsi



dideklarasikan



bisa



digunakan



di



Stored



di



dalam



memberi awalan @@ pada nama



Tersimpan selama dalam satu session



dimana



variable yang dimaksud.



yang sama



dideklarasikan.



SET @jumlah = 100;



DECLARE jumlah INT DEFAULT 8;



SET @jumlah = @jumlah + 140;



SET jumlah = jumlah + 10;



SELECT @jumlah;



SELECT jumlah;



SELECT @@version; SELECT @@global.version;



variable



tersebut



13



4/8/2020



Deklarasi variable Untuk mendeklarasikan suatu variable dalam stored procedure diperlukan suatu perintah DECLARE. Stuktur dasar dalam mendeklarasikan variable adalah



DECLARE nama_variable tipedata(panjang) DEFAULT nilai; Penjelasan dari masing-masing perintah tersebut adalah 1. DECLARE berguna untuk menyatakan atau mendefinisikan suatu variable, kondisi, penanganan, dan cursor yang bersifat lokal dalam stored procedure. Penulisannya harus berada diantara bagian tubuh stored procedure yaitu diantara BEGIN dan END. 2. nama_variable ditulis setelah pernyataan DECLARE dan dalam penamaan variable ditulis bebas asalkan nama variable TIDAK SAMA dengan nama parameter dalam stored procedure dan nama kolom atau field dalam table database. 3. tipedata(panjang) harus ditentukan tipe data dan panjang atau ukurannya dan ditulis setelah pendeklarasian nama variable. Tipe data yang dimaksud seperti INT, VARCHAR, TIME, DATE, dll. 4. DEFAULT merupakan pendeklarasian suatu nilai awal pada variable tersebut.



Deklarasi variable Sebagai contoh dalam pendeklarasian variable sebagai berikut



/* Pendeklarasian hanya satu variable */ DECLARE angka INT DEFAULT 0; /* Pendeklarasian lebih dari satu variable */ DECLARE angka, hasil, sisa INT DEFAULT 0; Contoh berikut mendeklarasikan variabel bernama totalSale dengan tipe data DEC (10,2) dan nilai default 0,0 sebagai berikut: DECLARE totalSale DEC(10,2) DEFAULT 0.0; DECLARE x, y INT DEFAULT 0;



14



4/8/2020



Menetapkan variabel Setelah variabel dideklarasikan, variabel siap digunakan. Untuk menetapkan nilai variabel, Anda menggunakan pernyataan SET: SET variable_name = value; Penjelasan dari struktur diatas adalah 1. SET digunakan untuk inisialisasi variable dengan nilai baru dan menggunakan operator assignment (=) atau (:=). 2. nama_variable harus sama dengan pendeklarasian nama variable sebelumnya untuk mengubah nilai dalam variable tersebut. 3. nilai_baru merupakan nilai baru yang diberikan kepada variable tersebut.



Menetapkan variabel /* Menggunakan tanda = */ DECLARE angka INT DEFAULT 0; SET angka = 10; /* Menggunakan tanda := */ DECLARE angka INT DEFAULT 0; SET angka := 10; DECLARE total INT DEFAULT 0; SET total = 10; Nilai dari total variabel adalah 10 setelah penugasan.



15



4/8/2020



Menetapkan variabel Selain pernyataan SET, Anda bisa menggunakan pernyataan SELECT INTO untuk menetapkan hasil Query ke variabel seperti yang ditunjukkan dalam contoh berikut: DECLARE departemenCount INT DEFAULT 0; SELECT COUNT(*) INTO departemenCount FROM departemen; Pertama, deklarasikan variabel bernama departemenCount dan inisialisasi nilainya menjadi 0. Kemudian, gunakan pernyataan SELECT INTO untuk menetapkan variabel departemenCount dengan jumlah record dari tabel departemen



Menetapkan variabel /* Menggunakan perintah SET */ CREATE PROCEDURE tampilAngka() BEGIN DECLARE angka INT DEFAULT 0; SET angka := 10; SELECT angka; CREATE DEFINER=`root`@`localhost` END PROCEDURE `tampilAngka`() BEGIN #Routine body goes here... DECLARE angka INT DEFAULT 0; Penulisan procedurnya SET angka := 10; SELECT angka; END



16



4/8/2020



Caranya penulisan procedurnya



Menetapkan variabel /* Menggunakan perintah INTO */ CREATE PROCEDURE hitungPegawai() BEGIN DECLARE jumlah INT DEFAULT 0; SELECT COUNT(*) INTO jumlah FROM pegawai; END



17



4/8/2020



Lingkup variabel Local variables



Variabel lokal dinyatakan dalam prosedur tersimpan dan hanya valid dalam blok BEGIN… END di mana mereka dideklarasikan. Variabel lokal dapat memiliki tipe data SQL apa pun. Contoh berikut menunjukkan penggunaan variabel lokal dalam prosedur tersimpan.



Lingkup variabel Local variables



CREATE PROCEDURE test() BEGIN DECLARE var1 INT DEFAULT 0; SET var1=var1+5; SELECT var1; END



18



4/8/2020



Lingkup variabel Local variables CREATE PROCEDURE my_procedure_Local_Variables() BEGIN /* mendeklarasikan variabel lokal */ DECLARE a INT DEFAULT 10; DECLARE b, c INT; /* menggunakan variabel lokal */ SET a = a + 100; SET b = 2; SET c = a + b; /*110 2 112*/ BEGIN /* variabel lokal di blok bersarang */ DECLARE c INT; SET c = 5; /* variabel lokal c diutamakan di atas salah satu dari nama yang sama yang dinyatakan dalam blok terlampir. */ SELECT a, b, c; /*110 2 5*/ END; SELECT a, b, c; /*110 2 112*/ END



Penulisan procedurnya CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure_Local_Variables`() BEGIN #Routine body goes here... DECLARE a INT DEFAULT 10; DECLARE b, c INT; /* menggunakan variabel lokal */ SET a = a + 100; SET b = 2; SET c = a + b; SELECT a, b, c; BEGIN /* variabel lokal di blok bersarang */ DECLARE c INT; SET c = 5; /* variabel lokal c diutamakan di atas salah satu dari nama yang sama yang dinyatakan dalam blok terlampir. */ SELECT a, b, c; END; SELECT a, b, c; END



19



4/8/2020



Lingkup variabel Local variables CALL my_procedure_Local_Variables();



Lingkup variabel Variabel pengguna Variable ini hanya dikenal per koneksi. Jika anda membuat variable dalam sebuah koneksi database, maka koneksi lain (yang dilakukanoleh anda atau orang lain) tidak akan mengenal variable tersebut. (SESSION variable) Untuk membuat variable tersebut dapat menggunakan perintah SET.



20



4/8/2020



Lingkup variabel Variabel pengguna CREATE PROCEDURE my_procedure_User_Variables() BEGIN SET @x = 15; SET @y = 10; SELECT @x, @y, @x-@y; END



CALL my_procedure_User_Variables() ;



Lingkup variabel Variabel pengguna



SET @nama='Shelly'; SET @umur=20; SELECT @nama,@umur; SET @umur=@umur+1; SELECT @nama,@umur;



Shelly, 20 Shelly, 21



21



4/8/2020



variabel • Untuk melihat variable system dan nilainya, dapat menggunakan perintah



SHOW VARIABLES



Latihan Soal 4: • Buatlah prosedur untuk menampilkan pegawai yang tempat lahirnya surabaya dengan nama showPegSurabaya • Buatlah procedur untuk menampilkan pegawai yang gajinya antara 5000 sampai 9000 dengan nama showPegGaji • Buatlah prosedur identitas yang isinya sebegai berikut: SET @nama=‘ganti nama anda'; SET @nim= =‘ganti nim anda'; SET @nim= =‘ganti prodi anda'; SELECT @nama,@nim, @prodi;



22