ng plesh

PENDAHULUAN

Latar Belakang

Basis data adalah kumpulan informasi yang disimpan di dalam komputer secara sistematik sehingga dapat diperiksa menggunakan suatu program komputer untuk memperoleh informasi dari basis data tersebut. Perangkat lunak yang digunakan untuk mengelola dan memanggil kueri (query) basis data disebut sistem manajemen basis data (database management system, DBMS). Sistem basis data dipelajari dalam ilmu informasi.

Dengan didukung perkembangan teknologi, kesulitan tersebut dapat diatasi dengan melalui MySQL. Dengan MySQL ini kita dapat mengubah sebuah tabel dengan menggunakan Structured Query Language (SQL). Sebuah database MySQl disusun oleh beberapa tabel yang didalam setiap tabel terdapat beberapa kolom Tujuan

Mahasiswa mampu menyelesaikan permasalahan yang ada pada studi kasus menggunakan perintah-perintah SQL dengan benar dan dapat menampilkan hasil yang benar.

1

TEORI

STRUCTURED QUERY LANGUAGE (SQL)SELECT DENGAN OPERATOR PERBANDINGAN,

LOGIKA DAN CLAUSA LIKE

1. OPERATOR PERBANDINGANBerikut adalah beberapa operator perbandingan yang digunakan pada sintax mysql:a. =, >, <, >=, <=, <>

Penjelasan:

Nama Deskripsi= Sama dengan> Lebih besar< Lebih kecil

>=Lebih besar atau sama dengan

<=Lebih kecil atau sama dengan

<> Tidak sama dengan

Format penulisan:

Contoh:table_bukuISBN judul Jml_hal harga penerbit1111 Dunia Sophie 765 10000 Mizan2222 The Lost Symbol 867 10000 Gramedia3333 A Morcking Bird 685 10000 Gramedia4444 The Secret 687 11000 Gramedia5555 Piano di Tepi Pantai 859 10000 Andi

a.

Output:judul HargaThe Secret 11000

b.

2

SELECT [nama kolom1], [nama kolom2], dstFROM [nama tabel]WHERE [nama kolom] [operator perbandingan][nilai]

SELECT judul, harga FROM tabel_bukuWHERE harga <> 10000;

SELECT * FROM tabel_buku WHERE harga = 10000;

SELECT [nama kolom1], [nama kolom2], dstFROM [nama tabel]WHERE [nama kolom] BETWEEN [nilai1] AND[nilai2]

SELECT * FROM tabel_buku WHERE harga BETWEEN 9000 AND11000;

Output:ISBN judul Jml_hal harga penerbit1111 Dunia Sophie 765 10000 Mizan2222 The Lost Symbol 867 10000 Gramedia3333 A Morcking Bird 685 10000 Gramedia5555 Piano di Tepi Pantai 859 10000 Andi

b. BETWEENMelakukan select berdasarkan rentang nilai tertentu.

Format Penulisan:


Output:ISBN judul Jml_hal harga penerbit1111 Dunia Sophie 765 10000 Mizan2222 The Lost Symbol 867 10000 Gramedia4444 The Secret 687 11000 Gramedia

2. OPERATOR LOGIKABerikut adalah beberapa operator logika yang digunakan

pada sintax mysql:AND, OR, XOR


3

SELECT * FROM tabel_buku WHERE harga = 10000AND penerbit = ‘gramedia’;

1.

Output:ISBN judul Jml_hal harga penerbit2222 The Lost Symbol 867 10000 Gramedia3333 A Morcking Bird 685 10000 Gramedia

2.


3.


3. Clausa LikeBerikut adalah beberapa klausa yang digunakan pada sintax

mysql: “%” dan” _”

Penjelasan:Nama Deskripsi

% Mencocokkan dengan berapapun jumlah karakter_ Mencocokkan hanya dengan satu karakter

Penggunaan LIKE:Penggunaan Deskripsi

“Prak%” Mencari string yang diawali dengan karakter “Prak”“%Prak” Mencari string yang diakhiri dengan karakter “Prak”

“%Prak%” Mencari string yang mengandung karakter “Prak”

“Prak_”Mencari string yang diawali dengan karakter “Prak” dan diakhiri 1 karakter

“_Prak”Mencari string yang diakhiri dengan karakter “Prak” dan diawali 1 karakter

“_Prak_” Mencari string yang mengandung karakter “Prak”

4

SELECT * FROM tabel_buku WHERE harga = 10000OR penerbit = ‘gramedia’;

SELECT * FROM tabel_buku WHERE harga = 10000XOR penerbit = ‘gramedia’;

yang diawali dan diakhiri dengan 1 karakter

Format Penulisan:


Output:ISBN judul Jml_hal harga penerbit1111 Dunia Sophie 765 10000 Mizan4444 The Secret 687 11000 Gramedia

STRUCTURED QUERY LANGUAGE (SQL)SELECT, ORDER BY, ALIASING, KALKULASI FIELD

5

SELECT [nama kolom1], [nama kolom2], dstFROM [nama tabel]WHERE [nama kolom] LIKE [klausa LIKE]

SELECT * FROM tabel_buku WHERE judul LIKE‘Worl%’;

SELECT *FROM BarangORDER BY Harga DESC, nama_barang ASC;

DAN CLAUSA BETWEEN

Data yang diambil dari satu atau beberapa table kadang membutuhkan pengurutan, baik itu menaik maupun menurun. Perintah SQL yang memungkinkan untuk mengurutkan data menggunakan ORDER BY. Selain pengurutan data, terkadang dibutuhkan suatu field baru yang merupakan hasil perhitungan dari field-field lainnya. Field hasil perhitungan tersebut disebut dengan field kalkulasi. Pengambilan data kadang-kadang juga dilakukan pada field tertentu, misalnya dari tahun 2000 sampai tahun 2002 dan lain-lain. Pengambilan data seperti itu bias dengan mudah dilakukan dengan menggunakan perintah BETWEEN.

1. ORDER BYMerupakan perintah yang digunakan untuk mengurutkan

data berdasarkan field tertentu.Sintaks untuk menambahkan record:

Perintah ORDER BY akan mengurutkan data berdasarkan field tertentu. Pengurutan bisa dilakukan berdasarkan satu atau beberapa field. Untuk setiap field tersebut, bisa diurutkan menaik ASC ataupun menurun DESC. Penggunaan perintah ORDER BY tanpa kata kunci ASC/DESC akan dianggap sebagai ASC(menaik).

Misalnya terdapat table Barang sebagai berikut:Field Tipe Data Keterangankode_barang INTVARCHAR[30] PRIMARY KEYnama_barang VARCHAR[30]Harga INTStock TINYINT

Perintah untuk menampilkan data barang yang diurutkan berdasarkan harga mulai dari harga tertinggi, kemudian untuk barang dengan harga yang sama akan diurutkan berdasarkan nama dari A-Z:

2. BETWEEN

6

SELECT <`field1`, `field2`, …, `fieldn`>FROM <`table name`>[WHERE expression]ORDER BY <`field1` [ASC/DESC],… `fieldn` [ASC/DESC]>[LIMIT a,b]

SELECT <`field1`, `field2`, …, `fieldn`>FROM <`table name`>[WHERE <`field`> BETWEEN <value1> AND <value2>][LIMIT a,b]

Perintah ini digunakan untuk mengambil data pada interval tertentu. Perintah ini akan mengikuti klausa WHERE.

Contoh perintah untuk mengambil data barang dengan harga antara 2000 sampai 10000 adalah sebagai berikut:

3. FIELD KALKULASIKadangkala kita ingin menampilkan sebuah data yang

dihasilkan dari kombinasi beberapa field. Field kalkulasi memungkinkan semua itu. Field Kalkulasi adalah sebuah field yang dihasilkan dari kombinasi field-field yang ada dalam table. Field hitungan tidak benar-benar ada dalam table database, hanya bersifat on-the fly dalam statement SELECT. Field yang dihasilkan tidak mempunyai nama, tetapi dapat diberikan alternative nama dengan pemberian alias. Misalnya jika ingin mengetahui berapakah uang yang akan diterima untuk masing-masing barang jika terjual semua, maka perintah SQL-nya adalah sebagai berikut:

Perintah di atas akan menghasilkan sebuah field kalkulasi tanpa nama, sehingga kurang informative. Agar field tersebut memiliki nama, maka dapat digunakan alias, seperti perintah di bawah ini:

Urutan perintah untuk keseluruhan klausa adalah sebagai berikut:

7

SELECT *FROM BarangWHERE Harga BETWEEN 2000 AND 10000

SELECT nama_barang, (harga*stock)FROM Barang

SELECT nama_barang, (harga*stock) AS totalUangFROM Barang

SELECT <`field1`, `field2`, …, `fieldn`>FROM <`table name`>[WHERE expression]ORDER BY <`field1` [ASC/DESC],… `fieldn` [ASC/DESC]>[LIMIT a,b]

SELECT AVG(nama_kolom) FROM nama_tabel;

SELECT MIN(nama_kolom) FROM nama_tabel;

STRUCTURED QUERY LANGUAGE (SQL)FUNGSI AGREGASI DAN CLAUSA HAVING

Dalam beberapa kasus sering dibutuhkan perhitungan kelompok data seperti mencari jumlah total data (baris) dalam sebuah table, mencari berapa nilai maksimum dari sebuah kolom pada table, atau bahkan mencari nilai rata-rata sebuah kolom pada table. Hal tersebut dimungkinkan dalam menggunakan perintah SQL yang disebut dengan Fungsi Agregasi. Fungsi agregasi digunakan untuk melakukan operasi pada kelompok-kelompok baris data, fungsi ini akan menghasilkan satu baris data untuk setiap kelompok baris data yang ada. Yang termasuk fungsi agregasi adalah: Average : AVG Minimum : MIN Maximum : MAX Total : SUM Count : COUNT

Fungsi Agregasi dapat diterapkan pada seluruh data (baris) pada sebuah table menjadi satu himpunan ataupun dibagi menjadi beberapa kelompok himpunan pada tabel tersebut. Agar dapat dikelompokkan menjadi beberapa himpunan maka digunakan Klausa GROUP BY. Adanya Klausa GROUP BY memungkinkan mkeluaran eksekusi perintah SQL tersebut menghasilkan satu atau lebih data (baris). Dengan banyaknya baris yang kemungkinan dihasilkan maka data keluaran tersebut juga dapat diseleksi kembali menggunakan Klausa HAVING.1. Perintah Average (AVG)

Fungsi AVG digunakan untuk memperoleh nilai rata-rata dari seluruh nilai pada suatu kolom.

Sintaks:

2. Perintah Minimum (MIN)Digunakan untuk memperoleh nilai minimum (yang terkecil) dari suatu kolom.

Sintaks:

3. Perintah Maximum (MAX)Digunakan untuk memperoleh nilai maksimum (yang terbesar) dari suatu kolom.

8

SELECT MAX(nama_kolom) FROM nama_tabel;

SELECT COUNT(nama_kolom) FROM nama_tabel;

SELECT nama_kolom_group, COUNT(nama_kolom)FROM nama_tabel GROUP BY nama_kolom_group;

SELECT nama_kolom_group, Fungsi_Agregat(nama_kolom)FROM nama_tabel GROUP BY nama_kolom_group Having kondisi;

SELECT SUM(nama_kolom) FROM nama_tabel;

Sintaks:

4. Perintah Total (SUM)Digunakan untuk memperoleh nilai penjumlahan seluruh baris pada suatu kolom.

Sintaks:

5. Perintah Count (COUNT)Fungsi agregasi count sering digunakan untuk menghitung jumlah baris dalam tabel.

Sintaks:

6. Fungsi Agregasi dengan Klausa GROUP BYJika fungsi agregasi nomor 1 sampai 5 ingin diterapkan pada kelompok himpunan baris data, maka digunakan klausa GROUP BY. Baris-baris data yang memiliki nilai yang sama pada satu kolom jika diterapkan klausa group by maka akan dimasukkan dalam satu kelompok.

Sintaks:

7. Fungsi Agregasi dengan Klausa HAVINGKlausa Having hamper sama dengan WHERE, hanya klausa ini diterapkan pada SQL yang menggunakan Klausa Group By. Klausa Having digunakan untuk menyeleksi hasil kelompok baris yang dihasilkan oleh Klausa Group By, dalam kasus agregasi dapat diterapkan seleksi pada kolom agregasi.

Sintaks:

9

STRUCTURED QUERY LANGUAGE (SQL)SELECT DENGAN MENGGUNAKAN JOIN

Perintah JOIN pada MySQL digunakan untuk menghubungkan dua atau lebih tabel. Tipe-tipe join yang biasa digunakan dalam query adalah INNER, LEFT, RIGHT dan OUTER. Untuk lebih memudahkan dalam memahami perbedaan dari masing-masing tipe JOIN maka digunakan dua table berikut.

Table tb_mahasiswaid nama mata_kuli

ah1 ADI 12 IKA 13 BUDI 24 WATI 55 DODI NULL

Dari table tb_mahasiswa di atas dapat dilihat bahwa field mata_kuliah mengacu ke table lain (dalam hal ini table matakuliah)

Table matakuliahid mata_kuliah1 Basis Data2 Logika

Pemrograman3 Sistem Informasi4 Struktur Data5 Otomata

Untuk menampilkan nama mahasiswa beserta nama mata kuliah yang diambilnya dapat digunakan query SELECT dengan perintah JOIN untuk

10

menggabungkan kedua table di atas. Hasil dari query yang dijalankan tergantung dari tipe JOIN yang digunakan.

1. INNER JOINTipe ini merupakan tipe JOIN yang paling sering digunakan.

Hasil dari query SELECT dengan tipe INNER JOIN adalah set dari record yang memenuhi syarat yang disebutkan pada klausa ON. Sebagai contoh:

Perintah SELECT bertujuan untuk menampilkan “nama” dari table “tb_mahasiswa” dan “nama_matakuliah” dari tabe “tb_matakuliah”. Karena kedua field yang ingin ditampilkan berasal dari dua table yang berbeda, maka kedua table tersebut harus digabungkan. Perintah FROM di atas menggabungkan table “tb_mahasiswa” dengan table “tb_matakuliah” secara INNER JOIN. Hubungan kedua table itu didefinisikan melalui perintah ON yaitu dengan menyamakan field “mata_kuliah” pada table “tb_mahasiswa” dengan field “id” pada table “tb_matakuliah”. Hasil dari perintah di atas adalah sebagai berikut:

nama

mata_kuliah

ADI Basis DataIKA Basis DataBUDI

Logika Pemrograman

WATI

Otomata

Hasil dari query di atas tidak menampilkan nama DODI karena nilai field “mata_kuliah” pada table “tb_mahasiswa” untuk mahasiswa DODI adalah NULL, dimana nilai NULL ini tidak dapat ditemukan padanannya pada field “id” di table “tb_matakuliah”. Hasil query SELECT menggunakan INNER JOIN, jika digambarkan menggunakan notasi himpunan adalah sebagai berikut:

2. LEFT JOIN

11

SELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa INNER JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.id;

SELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa RIGHT JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.id;

SELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa LEFT JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.id;

Bagaimana jika hasil dari query yang diinginkan adalah untuk menampilkan semua nama mahasiswa beserta mata kuliah yang diambil walaupun mahasiswa itu belum mengambil mata kuliah apapun? Tipe LEFT JOIN dapat digunakan untuk mendapatkan hasil yang diinginkan. LEFT JOIN akan menghasilkan suatu set record yang menampilkan semua baris dari table yang terletak di kiri (dalam hal ini “tb_mahasiswa”) tanpa memperhatikan apakah baris-baris tersebut memiliki padanan pada table sebelah kanan (“tb_matakuliah”).

Hasil query di atasa adalah sebagai berikut:nama

mata_kuliah

ADI Basis DataIKA Basis DataBUDI

Logika Pemrograman

WATI

Otomata

DODI

(NULL)

Hasil dari LEFT JOIN jika digambarkan menggunakan notasi himpunan adalah sebagai berikut:

3. RIGHT JOINPerintah RIGHT JOIN akan menampilkan semua entry dari

table sebelah kanan dari perintah JOIN walaupun ada baris pada table sebelah kanan yang tidak memiliki padanannya pada tabel sebelah kiri. Sebagai contoh: missal hasil dari query yang diinginkan adalah untuk menampilkan semua mata kuliah yang ada walaupun tidak ada mahasiswa yang mengambil mata kuliah tersebut. Perintah query untuk mendapatkan hasil tersebut adalah:

12

SELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa LEFT JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.idUNIONSELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa RIGHT JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.id;

Hasil query di atas adalah:

nama mata_kuliahADI Basis DataIKA Basis DataBUDI Logika

Pemrograman(NULL)

Struktur Data

(NULL)

Sistem Informasi

WATI Otomata

Jika diperlihatkan menggunakan himpunan, hasil query tersebut adalah:

4. OUTER JOINPerintah OUTER JOIN akan menampilkan seluruh isi dari

kedua table tanpa memperhatikan apakah masing baris pada kedua table memiliki pasangan pada table lainnya atau tidak. Ketika tidak ditemukan padanannya maka nilai dari field tersebut akan diisi dengan NULL. Perintah OUTER JOIN ini tidak terlalu bermanfaat dibandingkan dengan INNER, LEFT ataupun RIGHT. OUTER JOIN ini tidak diimplementasikan di MySQL, namun hasil yang sama dapat diperoleh menggunakan perintah UNION serta LEFT dan RIGHT JOIN.

13

Hasil perintah query di atas adalah sebagai berikut:

nama mata_kuliahADI Basis DataIKA Basis DataBUDI Logika

Pemrograman(NULL)

Struktur Data

(NULL)

Sistem Informasi

WATI OtomataDODI (NULL)

Jika diperlihatkan menggunakan himpunan, hasil query tersebut adalah:

Catatan:Jika nama field yang digunakan pada klausa ON adalah sama pada kedua tabel, maka untuk menghindari penggunaan nama table dan nama field untuk referensi dapat digunakan perintah USING.

Tabel tb_mahasiswaid nama id_kuliah1 ADI 12 IKA 13 BUDI 24 WATI 55 DODI NULL

Tabel tb_matakuliahId_kuliah

mata_kuliah

1 Basis Data2 Logika

Pemrograman3 Sistem Informasi4 Struktur Data

14

SELECT tb_mahasiswa.nama, tb_matakuliah.nama_matakuliahFROM tb_mahasiswa RIGHT JOIN tb_matakuliahON tb_mahasiswa.mata_kuliah=tb_matakuliah.id;

5 Otomata

Dari kedua table di atas dapat dilihat bahwa field yang digunakan untuk menghubungkan table-tabel tersebut adalah “id_kuliah”. Karena nama field referensi pada kedua table adalah sama (“id_kuliah”) maka query INNER JOIN pada halaman 2 dapat diubah menjadi:

STRUCTURED QUERY LANGUAGE (SQL)SELECT DENGAN OPERATOR UNION DAN IN

1. UNIONMySQL Union adalah statemen yang mengkombinasikan dua

buah atau lebih resultset dari multi sql statemen SELECT sehingga menjadi satu buah resultset. Union Statemen memiliki beberapa ketentuan sebagai berikut:

a. Jumlah kolom/field dari setiap statemen SELECT harus sama.

15

SELECT [nama_kolom1],[nama_kolom2],dst FROM [nama_tabel1]UNIONSELECT [nama_kolom1],[nama_kolom2],dst FROM [nama_tabel2]

SELECT kota FROM tabel_pelangganUNIONSELECT kota FROM tabel_suplier

b. Tipe data kolom/field dari setiap statemen SELECT harus kompatibel.Secara default Statemen UNION akan menghapus semua

record duplikat dari resultset. Apabila Anda ingin record duplikat tetap ditampilkan maka pada resultset tuliskan secara explicit UNION ALL.

Format Penulisan:

Contoh:a. tabel_pelanggan

id_pelanggan

nama_pelanggan

alamat kota provinsi

1 Isaraja Jalan Ponegoro Medan Sumatra Utara

2 Mikkha Jalan Ponegoro Bandung Jawa Barat3 Novita Dewi Jalan Patimura Badung Bali4 Fatin Jalan Teuku

UmarSurabaya Jawa Timur

5 Seena Jalan Hasanudin padang Sumatra Barat

b. tabel_suplierid_suplier

nama_suplier

alamat kota provinsi

1 Paloma Jalan Angkasa Denpasar Bali2 Gandaria Jalan Patimura Sibolga Sumatra

Utara3 Metro Jalan Teuku

UmarBandung Jawa Barat

4 Global Jalan Salwana Bukittinggi Sumatra Barat

5 MNC Jalan Werdapura Denpasar Bali

Query:

16

SELECT [nama_kolom1],[nama_kolom2],dstFROM [nama_tabel]WHERE [nama_kolom] IN ([nilai1,[nilai2],…);

SELECT * FROM tabel_buku WHERE penerbit IN (Gramedia, Andi);

Output:kotaMedanBandungBadungSurabayaPadangDenpasarSilabogaBukittinggi

2. INOperator IN memungkinkan Anda untuk menentukan

beberapa nilai dalam WHERE clause.

Format Penulisan:

Contoh:c. tabel_buku

ISBN judul Jml_hal harga penerbit1111 Dunia Sophie 765 9000 Mizan2222 The Lost Symbol 867 10000 Gramedia3333 A Morcking Bird 685 30000 Gramedia4444 The Secret 687 11000 Gramedia5555 Piano di Tepi Pantai 859 20000 Andi

Query:

Output:ISBN judul Jml_hal harga penerbit2222 The Lost Symbol 867 10000 Gramedia3333 A Morcking Bird 685 30000 Gramedia

17

4444 The Secret 687 11000 Gramedia5555 Piano di Tepi Pantai 859 20000 Andi

STRUCTURED QUERY LANGUAGE (SQL)VIEW

DBMS menyediakan suatu mekanisme untuk ‘menyembunyikan’ detil data tertentu yang disebut dengan VIEW. View merupakan sebuah tabel semu / tabel lojik, dimana datanya berasal dari satu atau lebih tabel lain yang disebut sebagai tabel sumber. View dibuat untuk memudahkan pengguna menampilkan data.

View dapat dibuat (create), diedit (alter), dan dihapus (drop). Pemanggilan sebuah view dalam perintah SQL sama dengan memanggil tabel fisik, karena view adalah tabel lojik (semu), yang dianggap sama oleh DBMS.

adapun perintah-perintah yang berhubungan dengan VIEW, adalah:1. Membuat VIEW (CREATE)

CREATE VIEW v AS <query expression>Dimana:

v adalah nama VIEW<query expression> adalah ekspresi query yang keluarannnya akan menjadi tabel semu / lojik dari view tersebut, biasanya menggunakan perintah SELECT. Query expression ini bias dari satu tabel fisik atau lebih, dan dapat menangani semua perintah-perintah SQL.

2. Mengedit VIEW (ALTER)ALTER VIEW v AS <query expression>

3. Menghapus VIEW (DROP)DROP VIEW [IF EXIST] v [,vn]Dimana:

vn adalah nama view lainnya jika ingin dihapus lebih dari satu view

4. Memanggil / menggunakan VIEW dalam SQLMemanggil View sama dengan memanggil tabel fisik dalam MySQL, yaitu menggunakan perintah:SELECT atribut,[atribut] FROM v [WHERE p]Dimana:

v adalalah nama view, atribut nama view tersebut sama adalah atribut yang dipanggil perintah yang terdapat dalam view tersebut (query expression).

18

STRUCTURED QUERY LANGUAGE (SQL)STORED PROCEDURE DAN CURSOR

1. Deklarasi Stores ProcedureDefinisi dari stored procedure adalah suatu bagian dari

deklarasi kode SQL yang disimpan di catalog database dan dapat dipanggil/dijalankan oleh suatu program, trigger ataupun stored procedure lainnya. Stored procedure juga dapat memanggil dirinya sendiri (recursive) akan tetapi tidak semua versi MySQL mendukung stored procedure yang recursive.

Beberapa keuntungan dari stored procedure adalah:1. Stored procedure meningkatkan performa aplikasi. Setelah

selesai dibuat maka stored procedure akan dikompilasi dan disimpan pada catalog database. Stored procedure berjalan lebih cepat dibandingkan perintah SQL yang belum dikompilasi yang dikirim oleh aplikasi.

2. Stored procedure mengurangi traffic dari database dan aplikasi karena tidak perlu mengirimkan perintah SQL yang belum dikompilasi. Yang perlu dikirimkan oleh aplikasi ke database server adalah nama dari stored procedurenya dan hasilnya akan dikirim balik oleh database server ke aplikasi pemanggil.

3. Stored procedure dapat digunakan berulang-ulang dan transparan terhadap semua aplikasi yang ingin menggunakannya.

4. Stored procedure adalah aman karena database administrator dapat memberikan akses yang sesuai ke tiap stored procedure tanpa memberikan akses langsung ke tabel.

Akan tetapi, stored procedure juga memiliki kekurangan sebagai berikut:1. Stored procedure membuat database server memiliki beban

yang tinggi dalam hal memori dan prosesor.

2. Stored procedure hanya mengandung perintah-perintah deklarasi MySQL sehingga sangat sulit untuk menulis prosedur yang kompleks yang dibutuhkan oleh aplikasi.

3. Stored procedure tidak dapat di-debug.

19

DELIMITER //CREATE PROCEDURE AmbilSemuaProduk()BEGIN

SELECT * FROM Produk;END //DELIMITER;

CALL nama_stored_procedure();

CALL AmbilSemuaProduk();

DECLARE nama_variabel tipe_data(size) DEFAULT nilai_default

Contoh dari stored procedure yang menampilkan semua produk yang ada pada tabel “Produk” adalah sebagai berikut:

Perintah DELIMITER digunakan untuk mengubah delimiter standar (“;”) menjadi yang lain, dalam hal ini tanda “//”. Fungsi delimiter ini adalah untuk memisahkan antara satu perintah SQL dengan yang lainnya. Pembuatan stored procedure dimulai dengan perintah CREATE PROCEDURE yang diikuti dengan nama procedure-nya. Isi dari stored procedure berupa perintah-perintah deklarasi SQL diletakkan di antara BEGIN dan END. Stored procedure dapat dijalankan menggunakan perintah:

Untuk memanggil stored procedure AmbilSemuaProduk dilakukan dengan perintah:

Keluaran dari perintah di atas adalah semua data yang ada pada tabel Produk.

2. Deklarasi VariabelVariabel digunakan pada stored procedure untuk menyimpan

hasil secara langsung. Untuk mendeklarasikan suatu variabel perintah yang digunakan adalah:

Mengikuti perintah DECLARE adalah nama dari variabel. Nama variabel ini harus mengikuti konvensi penamaan variabel dan tidak boleh sama dengan nama tabel atau kolom pada database. Selanjutnya adalah tipe data yang dapat berupa semua tipe yang disediakan oleh MySQL seperti INT, VARCHAR, DATETIME dan lain sebagainya. Disamping tipe data juga perlu didefinisikan ukuran (size) dari tipe data tersebut. Ketika mendeklarasikan variabel maka nilai awal dari variabel tersebut adalah NULL. Nilai default NULL ini dapat diubah dengan mendefinisikan perintah DEFAULT pada bagian akhir dari deklarasi varibel tersebut. Sebagai

20

DECLARE x,y DEFAULT 0;

SET total_penjualan = 10000;

DECLARE total_penjualan INT DEFAULT 0;SELECT SUM(harga_jual) INTO total_penjualan FROM produk;

contoh untuk mendeklarasikan variabel total_penjualan dengan tipe data INT yang nilai defaultnya adalah 0 maka digunakan perintah:

Setelah melakukan deklarasi variabel maka untuk mengisikan nilai ke variabel tersebut dapat dilakukan menggunakan perintah SET. Sebagai contoh:

Pengisian nilai variabel ini juga dapat dilakukan menggunakan perintah SELECT … INTO.

Pada contoh di atas variabel total_penjualan dideklarasikan dengan tipe data INT dan nilai default 0, kemudian total_penjualan diisikan nilai yang merupakan penjumlahan dari harga_jual pada tabel produk. Ruang lingkup suatu variabel tergantung dari deklarasinya. Jika variabel tersebut dideklarasikan di dalam stored procedure maka ruang lingkupnya adalah sampai dengan perintah END dari stored procedure itu. Jika dideklarasikan di dalam suatu blok BEGIN/END maka variabel itu ruang lingkupnya hanya di dalam blok tersebut. Suatu variabel yang dimulai dengan tanda ‘@’ merupakan variabel session. Variabel tersebut akan ada sampai session-nya berakhir.

3. ParameterStored procedure juga dapat menerima parameter. Pada

MySQL mode dari suatu parameter dapat berupa IN, OUT dan INOUT.1. IN, merupakan mode default dari parameter pada MySQL. IN

mengindikasikan bahwa suatu parameter merupakan masukan bagi stored procedure namun semua perubahan di dalam stored procedure tidak akan mengubah parameter.

2. OUT, mode ini mengindikasikan bahwa stored procedure dapat mengubah nilai dari parameter dan mengirimkan balik ke program yang memanggil.

3. INOUT, mode ini merupakan kombinasi dari IN dan OUT.

Sintaks untuk mendefinisikan suatu parameter pada stored procedure adalah:

21

MODE nama_param tipe_param(size_param)

DELIMITER //CREATE PROCEDURE AmbilKotaPerNegara(IN namaNegara VARCHAR(255)BEGIN

SELECT namaKota, telpon FROM KotaWHERE Negara = namaNegara;

END //DELIMITER;

CALL AmbilKotaPerNegara(‘INDONESIA’);

DELIMITER $$CREATE PROCEDURE TotalOrder(IN orderStatus VARCHAR(25), OUT total INT)BEGIN

SELECT count(orderNumber) INTO total FROM ordersWHERE status = orderStatus;

END$$DELIMITER

nama_param merupakan nama dari parameter. Nama ini tidak boleh sama dengan nama kolom ataupun tabel dan mengikuti tata cara penamaan variabel. Setelah nama parameter diikuti dengan tipe data dari parameter dan ukurannya. Masing-masing parameter dipisahkan dengan tanda “,” kalau stored procedure tersebut memiliki lebih dari satu parameter. Contoh di bawah

adalah untuk menampilkan semua kota pada satu negara:

Seperti dilihat di atas, stored procedure menerima paramtere namaNegara dengan tipe VARCHAR yang panjang maksimumnya adalah 255. Hasil dari stored procedure ini adalah menampilkan semua namaKota dan telpon dari tabel Kota yang negara-nya sama dengan parameter yang diberikan. Sebagai contoh: untuk menampilkan semua kota di Negara Indonesia maka digunakan perintah:

Contoh berikut digunakan untuk menampilkan jumlah record penjualan yang status penjualannya merupakan suatu parameter.

22

CALL TotalOrder(‘Dikapalkan’,@total);

DECLARE nama_cursor CURSOR FOR perintah_select;

OPEN nama_cursor;

FETCH nama_cursor INTO daftar_variabel;

CLOSE nama_cursor;

Misalnya diinginkan untuk mendapatkan jumlah order yang statusnya adalah “Dikapalkan” maka perintahnya adalah:

4. Perulangan dengan CursorMySQL mendukung penggunaan Cursor pada stored

procedure, function dan trigger. Cursor digunakan untuk melakukan iterasi pada himpunan record dari suatu tabel yang dihasilkan oleh suatu query dan melakukan proses untuk masing-masing record tersebut. Cursor pada MySQL memiliki beberapa sifat sebagai berikut:1. Read Only: artinya cursor tidak dapat diupdate.

2. Non-scrollable: cursor hanya dapat berjalan satu arah, tidak bias dilompati maupun disuruh maju atau mundur pada result set.

3. Asensitive: update pada tabel yang diacu oleh cursor tidak disarankan karena dapat memberikan hasil yang tidak diinginkan.

Untuk mendeklarasikan cursor digunakan perintah berikut:

Setelah itu cursor harus dibuka menggunakan perintah OPEN:

Selanjutnya untuk mendapatkan baris berikutnya danmemindahkan cursor ke baris tersebut dilakukan menggunakan perintah FETCH:

Pada akhirnya ketika baris yang dibaca telah habis maka cursor harus ditutup menggunakan perintah CLOSE:

23

DELIMITER $$DROP PROCEDURE IF EXISTS CursorProc$$CREATE PROCEDURE CursorProc()BEGIN

DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;

DECLARE prd_code VARCHAR(255);DECLARE cur_product CURSOR FOR SELECT

productCode FROM products;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET no_more_products = 1;/* for logging information */CREATE TABLE infologs (

id int(11) NOT NULL AUTO_INCREMENT,Msg varchar(255) NOT NULL,PRIMARY KEY (id)

);OPEN cur_product;FETCH cur_product INTO prd_code;REPEAT

SELECT quantityInStock INTO quantity_in_stock

FROM products WHERE productCode = prd_code;IF quantity_in_stock < 100THEN

INSERT INTO infologs(msg) VALUES (prd_code);END IF;FETCH cur_product INTO prd_code;UNTIL no_more_products = 1

END REPEAT;CLOSE cur_product;SELECT * FROM infologs;DROP TABLE infologs;END$$DELIMITER;

Satu hal yang perlu diingat ketika menggunakan cursor adalah untuk mendeklarasikan handler NOT FOUND agar tidak terjadi pesan kesalahan ketika tidak ada data. Berikut adalah contoh penggunaan cursor pada stored procedure:

24

Stored procedure di atas adalah satu contoh sederhana dimana hasil yang sama bias didapatkan melalui query biasa. Pada contoh di atas digunakan suatu cursor pada tabel “products” untuk membaca semua kode produk (“productCode”) yang ada. Setelah itu dilakukan pemeriksaan untuk masing-masing kode produk. Jika jumlah stock yang dimiliki (“quantity_in_stock”) untuk suatu kode produk adalah kurang dari 100 maka kode produk tersebut dicatat pada suatu tabel sementara (“infologs”). Setelah proses looping selesai dilakukan maka hasil dari tabel sementara ini ditampilkan ke layar menggunakan perintah SELECT.

STRUCTURED QUERY LANGUAGE (SQL)TRIGGER

Trigger merupakan sekumpulan perintah atau sintaks yang akan secara otomatis dijalankan jika terjadi operasi tertentu dalam tabel

25

DELIMITER $$CREATE TRIGGER [nama trigger][BEFORE/AFTER] [INSERT/UPDATE/DELETE]ON [nama tabel]FOR EACH ROW BEGIN[SQL STATEMENT]

END$$DELIMITER;

DELIMITER $$CREATE TRIGGER after_insertAFTER INSERT ON penjualanFOR EACH ROW BEGIN

INSERT INTO log (deskripsi, waktu_jual, kode_user)

VALUES('Insert data ke tabel pelanggan', now(), NEW. kode_user);END$$

DELIMITER;

atau view. Trigger digunakan untuk memanggil satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE atau DELETE dari suatu tabel. Sebagai contoh misalnya kita ingin menyimpan id pelanggan secar otomatis ke tabel ‘log’ sebelum menghapus data di tabel pelanggan.

Adapun jenis perubahan yang dijalankan dalam trigger adalah:a) BEFORE INSERT on TABEL1: menjalankan proses di dalamnya

pada saat sebelum melakukan operasi INSERT di TABEL1b) AFTER INSERT on TABEL1: menjalankan proses di dalamnya

pada saat sesudah melakukan operasi INSERT di TABEL1c) BEFORE UPDATE on TABEL1: menjalankan proses di dalamnya

pada saat sebelum melakukan operasi UPDATE di TABEL1d) AFTER UPDATE on TABEL1: menjalankan proses di dalamnya

pada saat sesudah melakukan operasi UPDATE di TABEL1e) BEFORE DELETE on TABEL1: menjalankan proses di dalamnya

pada saat sebelum melakukan operasi DELETE di TABEL1f) AFTER DELETE on TABEL1: menjalankan proses di dalamnya

pada saat sesudah melakukan operasi DELETE di TABEL1

Format Penulisan:

Refernsi “OLD dan “NEW”Karena trigger digunakan pada saat terjadi perubahan row data,

maka kita perlu referensi ke row sebelum dan sesudah perubahan. Untuk ini ada dua alias yang berfungsi untuk hal tersebut yaitu OLD dan NEW.

Sesuai namanya, OLD digunakan untuk referensi sebelum perubahan dan NEW untuk referensi sesudah perubahan.

Contoh:Berikut ini contoh trigger yang akan mencatat aktivitas ke tabel log setiap terjadi proses insert ke tabel pelanggan:

26

HASIL DAN PEMBAHASAN

1. Hasil Percobaan1. Mengimport database “northwind” ke dalam SQLyog. Langkah-

langkahnya sebagai berikut: Buka file northwind.sql kemudian copy query tersebut ke

dalam SQL yog.Screenshot:

27

Setelah semua query pada northwind.sql di copy ke SQL yog, langkah selanjutnya yaitu eksekusi semua query yang telah di copy.

Setelah berhasil dieksekusi, database northwind telah berhasil dibuat

Screenshot:

2. Analisis Hasil Percobaan

28

CREATE VIEW ProductPrice ASSELECT ProductName,UnitPrice FROM products pINNER JOIN suppliers s ON p.SupplierID=s.SupplierIDINNER JOIN categories c ON p.CategoryID=c.CategoryIDWHERE s.CompanyName LIKE "G%" AND(c.CategoryName = 'Produce' OR c.CategoryName ='Seafood' OR c.CategoryName ='Dairy Products')AND p.UnitsInStock<11;

Skema Basis Data Penjualan (Northwind)

STUDI KASUS

1. Menampilkan ProductName dan UnitPrice dari produk yang di supply oleh perusahaan yang berawalan G dengan kategori Produce, Seafood atau Dairy Product. View Hanya menampilkan product yang UnitInStock tidak lebih dari 10.

29

Screenshot:

2. Menampilkan Nama category dan jumlah barang (bukan jumlahan dari stock, 1 id barang dihitung 1, berapapun stocknya) di dalam kategori tersebut. Apabila ada kategori yang jumlah barangnya nol, harus tetap ditampilkan.

Screenshot:

3. Menampilkan namapegawai (FirstName dan LastName menjadi satu kolom), jumlah order yang ditangani dari pegawai yang menangani order terbanyak di urutan ke-5

Screenshot:

4. Menampilkan data OrderID, ProductName, UnitPrice, Quantity, Discount, SubTotal dari customer yang perusahaan customernya bernama Quick-Stop dan pegawai dengan last

30

CREATE VIEW CategoryStock ASSELECT CategoryName,COUNT(ProductID) AS stock_barang FROM products p LEFT JOIN categories c ON p.CategoryID=c.CategoryID GROUP BY CategoryName;

CREATE VIEW EmployeesOrder ASSELECT CONCAT(FirstName,' ',LastName) AS nama_pegawai,COUNT(OrderID) AS jumlah_order FROM employees eINNER JOIN orders o ON o.EmployeeID=e.EmployeeIDGROUP BY e.EmployeeIDORDER BY jumlah_order DESC LIMIT 4,1;

name Fuller dan menggunakan jasa pengiriman selain Speedy Express. Anggap diskon sudah fix(bukan dalam persen lagi).

CREATE VIEW DetailCustomer ASSELECT o.OrderID,p.ProductName,od.UnitPrice,od.Quantity,od.Discount,(od.Quantity*od.UnitPrice-(od.Quantity*od.UnitPrice*od.Discount)) AS SubTotalFROM products pINNER JOIN orderdetails od ON p.ProductID=od.ProductIDINNER JOIN orders o ON o.OrderID=od.OrderIDINNER JOIN employees e ON e.EmployeeID=o.EmployeeIDINNER JOIN customers c ON c.CustomerID=o.CustomerIDINNER JOIN shippers s ON o.ShipVia=s.ShipperIDWHERE c.CompanyName='Quick-Stop' ANDe.LastName='Fuller' ANDs.CompanyName<>'Speedy Express';

Screenshot:

5. Menampilkan data Nama Pegawai (FirstName dan LastName digabung) dan nama Supervisornya (ReportsTo) dari semua

pegawai.

Screenshot:

6. Buatlah stored procedure untuk menyisipkan data ke tabel Shippers

31

CREATE VIEW DetailEmployees ASSELECT CONCAT(FirstName,' ',LastName) AS nama_pegawai,ReportsTo AS Supervisor FROM employees;

DELIMITER $$CREATE PROCEDURE InsertDataShippers(IN CompanyName VARCHAR(40),IN telp VARCHAR(24))BEGIN

INSERT INTO shippers(CompanyName,Phone)VALUES(CompanyName,telp);

END $$DELIMITER;

SET @CompanyName='Cv. Gabeng';SET @telp=92253;

Screenshot:

7. Buatlah stored procedure untuk menampilkan data ProductName dan Golongan Harga setiap Product. Dimana field golongan harga ada 3 yaitu: jika harga di bawah 5 berarti murah, 5 sampai 10 termasuk golongan menengah, selain itu mahal.

DELIMITER $$DROP PROCEDURE IF EXISTS GolonganProduct $$CREATE PROCEDURE GolonganProduct()BEGIN

DECLARE flag INT;DECLARE kode INT;DECLARE nama VARCHAR(40);DECLARE harga DECIMAL;DECLARE cur_product CURSOR FOR SELECT ProductID

FROM products;DECLARE CONTINUE HANDLER FOR NOT FOUND SET

flag=1;CREATE TABLE temp(

NamaProduct VARCHAR(40),golongan VARCHAR(10));

OPEN cur_product;FETCH cur_product INTO kode;REPEAT

SELECT ProductName INTO namaFROM products WHERE ProductID=kode;SELECT UnitPrice INTO hargaFROM products WHERE ProductID=kode;IF harga<5 THEN

INSERT INTO temp VALUES(nama,'murah');

ELSEIF harga BETWEEN 5 AND 10 THENINSERT INTO temp

VALUES(nama,'menengah');ELSE

INSERT INTO temp VALUES(nama,'mahal');

END IF;FETCH cur_product INTO kode;

32

DELIMITER $$CREATE PROCEDURE InsertDataShippers(IN CompanyName VARCHAR(40),IN telp VARCHAR(24))BEGIN

INSERT INTO shippers(CompanyName,Phone)VALUES(CompanyName,telp);

END $$DELIMITER;

SET @CompanyName='Cv. Gabeng';SET @telp=92253;

UNTIL flag=1END REPEAT;CLOSE cur_product;SELECT * FROM temp;DROP TABLE temp;

END $$DELIMITER;

CALL GolonganProduct();

Screenshot:

8. Buatlah stored procedure untuk menampilkan country dari semua employees. Country yang sama ditampilkan hanya sekali. Cara menampilkan country adalah ditampilkan ke dalam 1 baris, setiap country dipisahkan tanda semicolon(;). Misalnya : USA;UK.

DELIMITER $$DROP PROCEDURE IF EXISTS EmployeesCountry $$CREATE PROCEDURE EmployeesCountry()BEGIN

DECLARE flag INT;DECLARE kode INT;DECLARE negara VARCHAR(15);DECLARE

country_employees_temp,country_employees VARCHAR(15);

DECLARE cur_employees CURSOR FOR SELECT EmployeeID FROM employees;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;

SELECT country INTO country_employees_temp FROM employees WHERE EmployeeID=1;

OPEN cur_employees;FETCH cur_employees INTO kode;REPEAT

SELECT country INTO negara FROM employees

WHERE EmployeeID=kode;IF negara<>country_employees_temp

33

THENSELECT

CONCAT(country_employees_temp,';',negara) INTO country_employees;

END IF;FETCH cur_employees INTO kode;UNTIL flag=1

END REPEAT;CLOSE cur_employees;SELECT country_employees;

END $$DELIMITER;

CALL EmployeesCountry();

Screenshot:

9. Buatlah trigger untuk mengurangi nilai stock pada product ketika orderdetails ditambah.

Screenshot:

34

DELIMITER$$CREATE TRIGGER updateStokAFTER INSERT ON orderdetailsFOR EACH ROW BEGIN

UPDATE products SET UnitsInStock=UnitsInStock-new.Quantity

WHERE ProductID=new.ProductID;END$$DELIMITER ;

10.Buatlah tabel log_shipper dengan field yang sama dengan shippers. Kemudian buatlah trigger untuk menambahkan data pada log_shipper ketika ada data yang dihapus dari shippers- Membuat tabel log_shipper

CREATE TABLE log_shipper (ShipperID INT(11) PRIMARY KEY,CompanyName VARCHAR(40),Phone VARCHAR(24));

Screenshot:

- trigger untuk menambahkan data pada log_shipper ketika ada data yang dihapus dari shippers

DELIMITER $$CREATE TRIGGER insert_logAFTER DELETE ON shippersFOR EACH ROW BEGIN

INSERT INTO log_shipper VALUES(old.ShipperID,old.CompanyName,old.Phone);END$$

DELIMITER;

Screenshoot

Data tabel pada tabel shipper dan log_shipper sebelum terjadi delete data

Selanjutnya data pada tabel shipper akan dilakukan delete data dengan shipperID=4.

DELETE FROM shippers WHERE ShipperID=4;

Berikut screenshot dari tabel shipper dan log_shipper setelah ShipperID=4 dihapus

35

KESIMPULAN

Dari pembahasan mengenai Studi kasus di atas dapat kita tarik kesimpulan:

1. Structured Query Language (SQL) sangat berperan penting dalam menyelesaikan suatu permasalahan yang berhubungan dengan Basis Data.

36

DAFTAR PUSTAKA

2013. Modul 3 Praktikum Basis Data .Jurusan Ilmu Komputer. Universitas Udayana








MySQL Server Documentation, http://dev.mysql.com

MySQL Tutorials, http://www.devshed.com

37

http://www.devshed.com/

http://dev.mysql.com/

ng plesh

Documents