LAPORAN PRAKTIKUM 7 BASIS DATA-STORED PROCEDURE PDF

Preview

Summary

LAPORAN MODUL VII PRAKTIKUM BASIS DATA Disusun untuk Memenuhi Matakuliah Praktikum Basis Data Dibimbing oleh Triyanna Widyaningtyas, S.T., M.T. Oleh: Dwitha Fajri Ramadhani 160533611410 Ika Damayanti 160533611505 S1 PTI’16 OFF B UNIVERSITAS NEGERI MALANG FAKULTAS TEKNIK JURUSAN TEKNIK ELEKTRO PRODI ...

Description

LAPORAN MODUL VII

PRAKTIKUM BASIS DATA Disusun untuk Memenuhi Matakuliah Praktikum Basis Data Dibimbing oleh Triyanna Widyaningtyas, S.T., M.T.

Oleh: Dwitha Fajri Ramadhani

160533611410

Ika Damayanti

160533611505

S1 PTI’16 OFF B

UNIVERSITAS NEGERI MALANG FAKULTAS TEKNIK JURUSAN TEKNIK ELEKTRO PRODI S1 PENDIDIKAN TEKNIK INFORMATIKA April 2017

1. Judul STORED PROCEDURE 2. Dasar Teori 2.1 Procedure Procedure adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.

2.2 Stored Procedure Stored procedure adalah sebuah prosedur layaknya subprogram (subrutin) di dalam bahasa pemograman regular yang tersimpan di dalam katalog basisdata. Di bawa ini merupakan bagian sintaks dari Stored procedure:

1. Adanya dukungan Stored Procedure akan membantu ke dalam stored procedure di SQL server, disimpan di server (misal dengan nama DoProsesStock). Pada program, cukup ditulis perintah SQL : Exec DoProsesStock StartDate, EndDate . Selanjutnya, SQL Server akan mengeksekusi perintah proses yang anda inginkan. satu kali perubahan proses, akan berlaku untuk semua user yang terhubung ke database, bahkan saat program masih berjalan di tiap komputer user (program tidak perlu dimatikan dulu). 2. Stored procedure akan menyederhanakan perintah SELECT * FROM table yang memang rumit, misal untuk laporan. Bayangkan bila anda harus membuat perintah SELECT dari sebuah tabel, tapi juga diikuti kondisi-kondisi tertentu yang biasanya berhubungan dengan data dari tabel lain. 3. Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali table-tabel pembantu. Bagaimana bisa? Stored procedure, dapat membuat tabel temporary yang disimpan sementara di dalam memori server selama proses berlangsung, atau bisa juga selama program connect. 4. Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja. Komputer client dapat berupa pentium 233 MMX, tapi dapat melakukan serangkaian proses yang rumit dan tetap tidak lambat. Karena bukan client yang

berpikir, tetapi server yang melakukan pekerjaan, jadi client hanya akan mendapatkan data jadinya saja. 2.3 Kelebihan Stored Procedure Beberapa kelebihan yang ditawarkan stored procedure antara lain:

•

•

Meningkatkan performa

•

Reusable

•

Mereduksi trafik jaringan

Meningkatkan kontrol sekuriti.

2.4 Kekurangan Stored Procedure Di balik kelebihannya, stored prosedure juga memiliki kekurangan, di antaranya : •

•

Berpotensi meningkatkan beban server Penulisannya tidak mudah. (memerlukan pengetahuan spesifik).

2.5 Sintaks Stored Prosedure

Penjelasan 2.5.1 Nama stored_procedure tidak boleh sama dengan nama fungsi internal, misal CREATE PROCEDURE SUM, tidak boleh ada spasi, tapi bisa menggunakan karakter _ (underscores) 2.5.2 Untuk membuat stored procedure gunakan perintah CREATE, untuk mengedit gunakan ALTER , untuk menghapus gunakan DROP. Perintah CREATE, ALTER, DROP dapat digunakan juga untuk membuat TABLE, VIEW, TRIGGER, FUNCTION, misal CREATE VIEW, ALTER FUNCTION, dsb. 2.5.3 SQL Server mengenali parameter/variabel karena ada tanda @, contoh : @nama_barang char(50), @tanggal datetime, dsb 2.5.4 Untuk

deklarasi

parameter

di

Stored

Procedure

gunakan

DECLARE

contoh : DECLARE @StartDate datetime, @EndDate datetime, dst.. 2.5.5 Untuk memasukkan nilai ke sebuah parameter, gunakan SET atau SELECT, contoh : SET @nama='Itanium' SET @Web='Klik-kanan' (perintah SET hanya bisa untuk 1 variabel saja) SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisa digunakan untuk banyak variabel)

2.5.6 Untuk mengambil nilai dari sebuah field dari tabel ke dalam variabel dapat juga menggunakan SET / SELECT , misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalu gunakan anda(),perintah ini valid bila data yang ditemukan hanya 1, bila lebih, maka varibale @nama tidak akan ada nilai nya. Untuk mengambil data dalam jumlah banyak, misal seperti array atau StringList, gunakan temporary tabel.untuk pembahasan lebih lengkap, tunggu posting berikut nya. 2.5.7 Untuk memenggil stored procedure, digunakan poerintah CALL (beberapa DBMS ada yang menggunakan EXECUTE).

Dalam implementasi nyata, penggunakan stored procedure sering melibatkan parameter. Di MySQL, parameter stored procedure dibedakan menjadi tiga mode : IN, OUT, dan INOUT. 2.5.7.1 IN Parameter yang merupakan mode default ini mengindikasikan bahwa sebuah parameter dapat di-pass ke stored procedure tetapi nilainya tidak dapat diubah (dari dalam stored procedure). 2.5.7.2 OUT Mode ini mengindikasi bahwa stored procedure dapat mgubah parameter dan mengirimkan kembali ke program pemanggil. 2.5.7.3 INOUT Mode ini pada dasarnya merupakan kombinasi dari mode IN dan OUT. Sintaks pendefinisian parameter diperhatikan sebagian berikut :

Stored procedure dapat mencerminkan beragam operasi data, misalnya seleksi, penambahan, pengubahan, penghapusan, dam juga operasi – operasi DLL. Seperti halnya procedure di bahasa pemograman, stored procedure juga dapat melibatkan variabel, pernyataan kondisional, dan pengulangan.

2.6 Contoh Pembuatan Stored Procedure Kita akan membuat sebuah Stored procedure yang berfungsi untuk menggantikan perintah SELECT yang rumit, misal untuk laporan stok barang. Dalam contoh ini ada 3 tabel yang digunakan. 1. Barang (IDBarang, NamaBarang, IDSatuan) 2. Satuan (IDSatuan, Satuan) 3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir)

Laporan yang diminta adalah untuk menampilkan stock sesuai dengan periode tertentu (bisa per hari, bisa juga per minggu, tergantung inputan StartDate dan EndDate). SQL untuk pembuatan Stored Procedure nya : CREATE PROCEDURE

LapStockBarang1 @StartDate varchar(10), @EndDate

varchar(10) AS DECLARE @tgl1 datetime, @tgl2 datetime **(variabel StartDate tidak bisa langsung datetime karena perintah EXEC LapStockBarang dalam bentuk string) CREATE TABLE #TStock (IDBarang varchar(5), SAwal real, Masuk real, Keluar real) CREATE TABLE #TStock2 (IDBarang varchar(5), SAwal real, Masuk real, Keluar real, SAkhir real) ** buat temporary table , tanda # menandai bahwa tabel ini hanya akan ada saat proses stored procedure. SELECT @tgl1= CONVERT(datetime,@StartDate,103), @tgl2=CONVERT(datetime, @EndDate,103) **

convert

varchar(string)

ke

tipe

datetime,

gunakan

perintah

CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy INSERT INTO #TStock (IDBarang, SAwal) SELECT (IDBarang, SAwal) FROM StockBarang WHERE tanggal= @tgl1 ** masukkan saldo awal pada tanggal bulan itu INSERT INTO #TStock (IDBarang, Masuk, Keluar) SELECT IDBarang, SUM(Masuk), SUM(Keluar) FROM StockBarang WHERE tanggal BETWEEN @tgl1 AND @tgl2 GROUP BY IDBarang ** masukkan JUMLAH dari masuk dan keluar INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar) SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar) FROM #Tstock GROUP BY IDBarang ** sekarang gabungkan data2 nya UPDATE #TStock2 SET SAkhir= SAwal + Masuk – Keluar SELECT t.*, b.NamaBarang,s.Satuan FROM #TStock2 AS t, Barang AS b, Satuan AS s WHERE t.IDBarang=b.IDBarang AND b.IDSatuan=s.IDSatuan ORDER BY b.IDBarang

3. Latihan Sebelum melakukan tugas latihan, praktikan membuka XAMPP CONTROL PANEL dan memilih Start pada Module MySQL dan Apache kemudian klik Admin atau mengakses localhost/phpmyadmin pada browser.

Gambar 1. Start MySQL dan Apache

Maka akan tampil seperti pada gambar dibawah ini :

Gambar 2. Tampilan phpmyadmin

3.1 Stored Procedure Pada praktikum ke 7 ini praktikkan membuat database baru dengan nama modul7_005, kemudian mengekspor data dari database modul 6, dikarenakan struktur beserta isi tabel sama dengan modul 6. 3.1.1

Pembuatan Stored Procedure

Pembuatan stored procedure dengan menggunakan CREATE, dengan sintaks seperti berikut : DELIMITER// CREATE PROCEDURE ‘nama_procedure’ ( ‘parameter1’, ’parameter2’ )BEGIN ...... END//

Gambar 1. CREATE PROCEDURE getMHS();

Pada latihan pertama ini membuat prosedur untuk menampilkan semua data mahasiswa, perintah DELIMITER digunakan untuk mengubah delimiter standar, langkah ini umumnya dilakukan ketika isi stored procedure mengandung titik koma yang merupakan delimiter standar di SQL. Pernyataan diantara BEGIN dan END merupakan badan(body) stored procedure. Karena prosedur tersebut digunakan untuk menampilkan semua data mahasiswa sehingga pada bagian badan prosedur menggunakan sintaks seleksi semua data pada tabel mahasiswa yaitu SELECT * FROM mahasiswa. Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 2. CREATE PROCEDURE getMHS() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 2, menandakan pembuatan prosedur getMHS berhasil dibuat. Prosedur getMHS telah dibuat, langkah selanjutnya adalah eksekusi pemanggilan prosedur getMHS.

3.1.2

Eksekusi Query

Gambar 3. Eksekusi PROCEDURE getMHS();

Perintah CALL berfungsi untuk memanggil prosedur. Pada pernyataan CALL getMHS(); berarti mengindikasikan bahwa memanggil prosedur getMHS tanpa parameter, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menampilkan semua data pada tabel mahasiswa, seperti pada gambar dibawah ini.

Gambar 4. Hasil eksekusi PROCEDURE getMHS();

3.2 Parameter IN

Gambar 5. CREATE PROCEDURE getMKBySMT();

Pada latihan 1 stored procedur memperlihatkan bentuk default atau tanpa parameter, sedangkan pada latihan 2 ini membuat stored procedure dengan mendefinisikan parameter yang nantinya dapat digunakan oleh pernyataan di badan prosedur. Praktikkan membuat prosedur dengan nama getMKBySMT dengan paramter SMT yang bertipe data INT dan length 1, IN pada prosedur diatas merupakan parameter mode default yang digunakan untuk mengindikaskan bahwa sebuah parameter dapat di-pass ke dalam stored procedure tetapi nilainya tidak dapat diubah (dari dalam stored procedure). Pada badan prosedur menyeleksi data pada tabel matakuliah dimana parameter SMT sama dengan field SEMESTER pada tabel matakuliah. Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 6. CREATE PROCEDURE getMKBySMT() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 6, menandakan pembuatan prosedur getMKBySMT berhasil dibuat. Langkah selanjutnya adalah eksekusi pemanggilan getMKBySMT.

Gambar 7. Eksekusi PROCEDURE getMKBySMT();

Untuk memanggil stored procedure yang memiliki parameter, maka kita harus menspesifikasikan argumennya. Pada pernyataan di atas berarti mengindikasikan bahwa memanggil prosedur getMKBySMT dengan argumen 3, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menampilkan semua data pada tabel matakuliah yang memiliki data SEMESTER sama dengan 3, seperti pada gambar dibawah ini.

Gambar 8. Hasil Eksekusi PROCEDURE getMKBySMT();

Apabila pemanggilan stored procedure di atas mengabaikan argumen, DBMS akan merespon dengan pesan kesalahan (ERROR).

Gambar 9. CREATE PROCEDURE getMkBySemSks();

Selain menggunakan sebuah parameter, stored procedure juga dapat mendefinisikan lebih dari satu parameter. Praktikkan membuat prosedur dengan nama getMkBySemSks dengan paramter Smt yang bertipe data INT dan length 2, Sks yang bertipe data INT dan length 2. IN pada prosedur diatas merupakan parameter mode default yang digunakan untuk mengindikaskan bahwa sebuah parameter dapat di-pass ke dalam stored procedure tetapi nilainya tidak dapat diubah (dari dalam stored procedure). Pada badan prosedur menyeleksi data pada tabel matakuliah dimana parameter SMT sama dengan field SEMESTER dan parameter Sks sama dengan field SKS pada tabel matakuliah. Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 10. CREATE PROCEDURE getMkBySemSks() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 10, menandakan pembuatan prosedur getMkBySemSks berhasil dibuat. Langkah selanjutnya adalah eksekusi pemanggilan getMkBySemSks.

Gambar 11. Eksekusi PROCEDURE getMkBySemSks(3,2);

Untuk pemanggilan stored prosedur dengan parameter lebih dari satu sama seperti pemanggilan stored prosedur dengan 1 parameter, kita harus menspesifikasikan argumen dari kedua parameter terlebih dahulu. Pada pernyataan di atas berarti mengindikasikan bahwa memanggil prosedur getMkBySemSks dengan argumen Smt sama dengan 3 dan Sks sama dengan 2, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menampilkan semua data pada tabel matakuliah yang memiliki data SEMESTER sama dengan 3 dan SKS sama dengan 2, seperti pada gambar dibawah ini.

Gambar 12. Hasil Eksekusi PROCEDURE getMkBySemSks(3,2);

Apabila pemanggilan stored procedure di atas mengabaikan salah satu argumen, DBMS akan merespon dengan pesan kesalahan (ERROR).

3.2.1

Variabel

Gambar 13. Deklarasi variabel @SMT=3 dan eksekusi;

Di MySQL, pengguna juga bisa mendeklaraskan variabel global – ruang lingkup session – dengan menggunakan perintah SET disertai notasi @. Pada pernyataan diatas mendeklarasikan variabel @SMT diinisialisasi dengan nilai 3, kemudian seleksi dari variabel tersebut. Untuk mengeksekusi dengan klik Go atau kirim.

Gambar 14.Hasil dari eksekusi deklarasi variabel @SMT=3;

Setelah pendeklarasian tersebut maka menampilkan hasil dari variabel yang telah dideklarasikan, karena inisialisasi nilai @SMT=3, maka hasil dari eksekusi tersebut juga menampilkan hasil 3.

Gambar 15. Deklarasi variabel @smt=3, dan eksekusi procedure getMKBySMT;

Pengguna juga bisa memanfaatkan variabel yang telah dideklarasikan untuk operasi-operasi lain, misalnya sebagai argumen dari stored procedure. Pada pernyataan diatas praktikkan mendeklarasikan variabel @smt dengan nilai 3, kemudian praktikkan melakukan pemanggilan prosedur getMKBySMT dengan parameter @smt, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menampilkan semua data pada tabel matakuliah yang memiliki data SEMESTER sama dengan 3, karena inisialisasi variabel @smt adalah 3, hasil eksekusi pernyataan diatas seperti pada gambar dibawah ini.

Gambar 16. Hasil eksekusi procedure getMKBySMT dengan variabel @smt=3;

3.2.2

Penambahan Data

Pada operasi penambahan data, data-data tekait diisikan melalui argumen, selanjutnya isi stored procedure akan memasukka data ke dalam tabel.

Gambar 17. CREATE PROCEDURE AddDosen();

Praktikkan membuat prosedur dengan nama AddDosen dengan parameter KODE_DOS yang bertipe data VARCHAR dan length 10, NAMA_DOS yang bertipe data VARCHAR dan length 50, ALAMAT_DOS yang bertipe data VARCHAR dan length 100. IN pada prosedur diatas merupakan parameter mode default yang digunakan untuk mengindikaskan bahwa sebuah parameter dapat di-pass ke dalam stored procedure tetapi nilainya tidak dapat diubah (dari dalam stored procedure). Pada badan prosedur INSERT INTO tabel dosen VALUES (kode_dos, nama_dos, alamat_dos). Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 18. CREATE PROCEDURE AddDosen() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 18, menandakan pembuatan prosedur AddDosen berhasil dibuat. Langkah selanjutnya adalah eksekusi pemanggilan AddDosen. Gambar 19. Eksekusi PROCEDURE AddDosen(‘212’,’GUNAWAN’,’JL. AMBARAWA’);

Pada pernyataan CALL AddDosen(‘212’,’GUNAWAN’,’JL. AMBARAWA’); berarti mengindikasikan bahwa memanggil prosedur AddDosen, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menambahkan data 212, GUNAWAN, JL. AMBARAWA. Eksekusi tersebut berhasil dengan ditandai adanya gambar seperti dibawah ini.

Gambar 20. Eksekusi penambahan data PROCEDURE AddDosen(‘212’,’GUNAWAN’,’JL. AMBARAWA’) berhasil dibuat;

Setelah eksekusi penambahan data dengan pemanggilan stored procedure, kemudian praktikkan seleksi tabel dosen dengan pernyataan seperti ini : Gambar 21. Seleksi tabel dosen();

Sehingga data pada tabel dosen seperti dibawah ini :

Gambar 22. Hasil dari seleksi tabel dosen;

3.3 Parameter OUT Dalam konteks bahasa pemrograman, parameter OUT analog dengan passing-byreference. Dengan demkian, parameter ini nilainya bisa diubah oleh store procedure.

Gambar 23. CREATE PROCEDURE JumlahDosen();

Praktikkan membuat prosedur dengan nama Jumlah_Dosen dengan parameter jumlah_dos yang bertipe data INT dan length 3. OUT pada prosedur diatas merupakan parameter mengindikasikan bahwa stored procedure dapat mengubah parameter dan mengirimkan kembali ke program pemanggil. Pada badan prosedur menyeleksi data dengan COUNT (KODE_DOS) dimana perhitungan data KODE_DOS dimasukkan kedalam parameter jumlah_dos yang telah diinisialisasikan. Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 24. CREATE PROCEDURE JumlahDosen() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 24, menandakan pembuatan prosedur Jumlah_Dosen berhasil dibuat. Langkah selanjutnya adalah eksekusi pemanggilan Jumlah_Dosen.

Gambar 25. Eksekusi PROCEDURE JumlahDosen(@jumlah_dosen);

Untuk mengeksekusi stored procedur dengan parameter OUT, dibutuhkan argumen yang spesifik. Pada pernyataan di atas berarti memanggil prosedur Jumlah_Dosen dengan memanggil parameter @jumlah_dosen, sehingga dari hasil eksekusi pemanggilan prosedur tersebut menampilkan perhitungan data dosen pada tabel dosen.

Gambar 26. Hasil Eksekusi PROCEDURE JumlahDosen(@jumlah_dosen);

Hasil dari COUNT (KODE_DOS) adalah 6, karena data yang terdapat pada tabel dosen berjumlah 6, antara lain dosen dengan KODE_DOS 10, 11, 12, 13, 14, dan 212. Parameter mode OUT juga bisa dikombinasikan dengan mode IN.

3.4 Parameter IN OUT

Gambar 27. CREATE PROCEDURE CountBySks();

Praktikkan membuat prosedur dengan nama CountBySks dengan parameter var yang bertipe data INT dan length 3. INOUT pada prosedur diatas merupakan parameter yang pada dasarnya merupakan kombinasi dari mode IN dan OUT. Pada badan prosedur menyeleksi data dengan COUNT (KD_MK) dimana perhitungan data KD_MK dimasukkan kedalam parameter var yang telah diinisialisasikan dan juga menggunakan klausa WHERE SKS sama dengan var. Untuk mengeksekusi pembuatan stored procedure dengan klik Go atau kirim.

Gambar 28. CREATE PROCEDURE CountBySks() berhasil dibuat;

Setelah dieksekusi maka akan tampil seperti gambar 28, menandakan pembuatan prosedur CountBySks berhasil dibuat. Langkah selanjutnya adalah eksekusi pemanggilan CountBySks.

Gambar 29. Deklarasi variabel @SKS=2 dan eksekusi;

Pada pernyataan diatas praktikkan mendeklarasikan variabel @SKS dengan nilai 2, kemudian praktikkan melakukan seleksi @SKS, sehingga dari hasil eksekusi pemanggilan tersebut menampilkan nilai data pada variabel @SKS, karena inisialisasi variabel @SKS adalah 2, hasil eksekusi pernyataan diatas seperti pada gambar dibawah ini.

Gambar 30. Hasil dari deklarasi variabel @SKS=2 dan eksekusi;

Gambar 31. Deklarasi variabel @SKS=2 dan eksekusi procedure CountBySks(@SKS);

Pada pernyataan diatas praktikkan mendeklarasikan variabel @SKS dengan nilai 2, kemudian praktikkan melakukan pemanggilan prosedur CountBySks dengan parameter @SKS dan seleksi @SKS, sehingga dari hasil eksekusi pemanggilan prosedur...