comlab.ilkom.unsri.ac.idcomlab.ilkom.unsri.ac.id/wp-content/uploads/2016/10/... · Web viewTips...
Transcript of comlab.ilkom.unsri.ac.idcomlab.ilkom.unsri.ac.id/wp-content/uploads/2016/10/... · Web viewTips...
MODUL PRAKTIKUM
BASIS DATA II
Comlab Fakultas Ilmu Komputer
UNIVERSITAS SRIWIJAYA
2016
1
Universitas Sriwijaya
Fakultas Ilmu Komputer
Laboratorium
LEMBAR PENGESAHAN
MODUL PRAKTIKUM
SISTEM MANAJEMEN MUTU
ISO 9001 : 2008
No. Dokumen …… Tanggal 09 Mei 2016Revisi 0 Halaman 2 Dari 64
MODUL PRAKTIKUM
Mata Kuliah Praktikum : Basis Data II
Kode Mata Kuliah Praktikum :
SKS : 1
Program Studi : Sistem Informasi
Semester : 2 (Genap)
DIBUAT OLEH DISAHKAN OLEH DIKETAHUI OLEH
TIM LABORAN LABORATORIUM FASILKOM
UNSRI
TIM DOSEN SISTEM INFORMASI FASILKOM
UNSRI
KEPALA LABORATORIUM
2
DAFTAR ISI
MODUL I : Pengenalan Mysql.....................................................................
4
MODUL II : Data Definition Language (DDL) ........................................... 7
MODUL III : Data Manipulation Language (DML)....................................... 12
MODUL IV : Fungsi Agregat............................................................................ 16
MODUL V : Retrieve, Group, Filter, dan Patter Matching........................... 17
MODUL VI : View dan Join............................................................................ 21
MODUL VII : Data Control Language (DCL).............................................. 30
MODUL VIII : Dasar-dasar PL/SQL.............................................................. 42
MODUL IX : Procedure dan Fungsi............................................................ 60
MODUL X : Trigger................................................................................... 65
3
PRAKATA
Dengan memanjatkan puji syukur kehadirat Allah SWT yang telah melimpahkan
rahmat dan karunia-Nya kepada penyusun, sehingga dapat menyelesaikan buku panduan
praktik Basis Data ini. Sholawat dan salam juga kita curahkan kepada baginda yang
mulia Rosulullah SAW beserta keluarga, sahabat dan pengikutnya hingga akhir zaman.
Buku ini merupakan salah satu bahan ajar pendukung untuk mata kuliah Basis
Data. Dari buku ini diharapkan mahasiswa dapat dengan mudah mempelajari, memahami,
dan mempraktikkan materi – materi yang telah diajarkan pada kelas teori mata kuliah
Basis Data. Kemudian buku ini diharapkan dapat menjadi referensi untuk pemecahan
permasalahan umum di luar materi perkuliahan. Sebagian besar isi dari buku ini
merupakan rangkuman dari sumber-sumber yang telah dibuat penulis lain. Penyusun
berharap agar buku ini dapat bermanfaat bagi semua kalangan pembaca. Terima kasih
untuk semuanya yang telah memberikan banyak kritik dan saran serta dukungan dalam
penyusunan buku ini.
Palembang, Januari 2015
Penyusun
4
MODUL I
Pengenalan MySQL
Tujuan :
Setelah menyelesaikan modul ini, anda diharapkan dapat :
1. Mengenal lingkungan kerja MySQL
2. Mengenal format perintah di MySQL
3. Mengenal perintah-perintah sederhana di MySQL
Dasar Teori
MySQL adalah suatu perangkat lunak database relasi (Relational Database
Management System atau RDBMS), seperti halnya ORACLE, Postgresql, MS SQL, dan
sebagainya. MySQL dikembangkan sekitar tahun 1994 oleh sebuah perusahaan
pengembang software dan konsultan database bernama MYSQL AB yang berada di
Swedia. Waktu itu perusahaan tersebut masih bernama TcX DataKonsult AB, dan tujuan
awal dikembangkannya MySQL adalah untuk mengembangkan aplikasi berbasis web
pada client. MySQL menyebut produknya sebagai database open source terpopuler di
dunia. Berdasarkan riset dinyatakan bahwa bahwa di platform Web, dan baik untuk
kategori open source maupun umum, MySQL adalah database yang paling banyak
dipakai. Menurut perusahaan pengembangnya, MySQL telah terpasang di sekitar 3 juta
komputer. Puluhan hingga ratusan ribu situs mengandalkan MySQL bekerja siang malam
memompa data bagi para pengunjungnya.
Format Perintah
Berikut adalah ketentuan-ketentuan memberi perintah pada MySQL:
Setiap perintah harus diakhiri dengan tanda titik koma , kecuali untuk
perintahtertentu, misal : quit
Setiap perintah akan disimpan dalam buffer (memori sementara) untuk
menyimpan histori perintah-perintah yang pernah diberikan.
5
Perintah dapat berupa perintah SQL atau perintah khusus MySQL.
Perintah-perintah dalam lingkungan MySQL tidak menerapkan aturan case
sensitive, tetapi case insensitive yaitu perintah bisa dituliskan dalam huruf besar
atau pun huruf kecil.
Aturan case sensitive diterapkan pada penamaan objek-objek dalam database
seperti nama database atau nama table, namun aturan ini hanya ada dalam
lingkungan Unix dan Linux.
Ada beberapa tanda yang sering muncul di prompt :
Start dan Stop MySQL
Berikut cara memulai MySQL dengan menggunakan bantuan XAMPP. Aktifkan Xampp Control Panel Application, klik start apache dan mysql.
Aktifkan command prompt, lalu ketik seperti gambar berikut:
6
Sedangkan untuk stop atau keluar dari MySQL dapat menggunakan perintah : \q, exit dan
quit.
7
MODUL II
Data Definition Language (DDL)
Tujuan:
Setelah menyelesaikan modul ini, anda diharapkan dapat:
1. Membuat database dan tabel dengan data definition language
2. Mampu memodifikasi tabel
Dasar Teori
DDL (Data Definition Language), DDL merupakan kelompok perintah yang
berfungsi untuk mendefinisikan atribut-atribut basis data, tabel, atribut(kolom), batasan-
batasan terhadap suatu atribut, serta hubungan antar tabel. Yang termasuk dalam
kelompok DDL ini adalah CREATE, ALTER, dan DROP.
a. Syntax Membuat Database : CREATE DATABASE namadatabase;
Namadatabase tidak boleh mengandung spasi dan tidak boleh memiliki
nama yang sama antar database. Berikut ini perintah untuk membuat database
dengan nama rental : CREATE DATABASE CV_SEJAHTERA;
Syntax tambahan untuk menampilkan daftar nama database yang ada pada
mysql menggunakan perintah : SHOW DATABASES;
b. Memilih Database : USE namadatabase;
8
Sebelum membuat suatu tabel, terlebih dahulu harus memilih salah satu
database sebagai database aktif yang akan digunakan untuk menyimpan tabel-
tabel, Berikut ini perintah untuk menggunakan database dengan nama rental :
USE RENTAL;
c. Syntax Menghapus Database : DROP DATABASE namadatabase;
Database yang akan dihapus sesuai dengan namadatabase. Berikut ini
perintah
untuk menghapus database dengan nama rental : DROP DATABASE RENTAL;
d. Membuat Tabel : CREATE TABLE namatabel2 ( Field1 TipeData1,Field2 TipeData2);
Nama tabel tidak boleh mengandung spasi (space). Field1 dan TipeData1
merupakan nama kolom pertama dan tipe data untuk kolom pertama. Jika ingin
membuat tabel dengan kolom lebih dari satu, maka setelah pendefinisian tipe data
sebelumnya diberikan tanda koma (,).
Berikut ini perintah untuk membuat tabel dengan nama barang :
e. Menampilkan Tabel
Untuk menampilkan daftar nama tabel yang ada pada database yang
sedang
aktif/digunakan (dalam hal ini database rental) : SHOW TABLES;
f. Menampilkan Atribut Tabel : DESC namatabel;
Untuk menampilkan deskripsi tabel (dalam hal ini jenisfilm) syntaxnya
adalah : DESC barang;
9
g. Syntax Menghapus Tabel : DROP TABLE namatabel;
Tabel yang akan dihapus sesuai dengan namatabel, berikut ini perintah
untuk
menghapus tabel dengan nama jenisfilm : DROP TABLE BARANG;
h. Mendefinisikan Null/Not Null : CREATE TABLE namatabel ( Field1 TipeData1 NOT
NULL, Field2 TipeData2);
i. Mendefinisikan Primary Key Pada Tabel
Terdapat tiga cara untuk mendefinisikan primary key. Berikut ini adalah
Syntax mendefinisikan primary key untuk Field1
CREATE TABLE namatabel(Field1 TipeData1 NOT NULL PRIMARY KEY, Field2
TipeData2);
Atau
CREATE TABLE namatabel ( Field1 TipeData1, Field2 TipeData2, PRIMARY
KEY(Field1));
Atau
ALTER TABLE namatabel ADD CONSTRAINT namaconstraint PRIMARY KEY
(namakolom);
j. Menghapus Primary Key Pada Tabel
Cara 1 : Jika primary key dibuat dengan menggunakan alter table :
ALTER TABLE namatabel DROP CONSTRAINT namaconstraint;
Cara 2 : Jika primary key dibuat melalui create table :
ALTER TABLE namatabel DROP PRIMARY KEY;
k. Menambah Kolom Baru Pada Tabel : ALTER TABLE namatabel ADD fieldbaru tipe;
Namatabel adalah nama tabel yang akan ditambah fieldnya. Fieldbaru
adalah nama kolom yang akan ditambahkan, tipe adalah tipe data dari kolom yang
akan ditambahkan.
Berikut ini contoh perintah untuk menambah kolom keterangan dengan tipe data
varchar(25):
ALTER TABLE JENISFILM ADD KETERANGAN VARCHAR(25);10
Untuk meletakkan field diawal, tambahkan sintaks first :
ALTER TABLE PELANGAN ADD COLUMN KODE CHAR(5) FIRST;
Untuk menyisipkan field setelah field tertentu, tambahkan sintaks after :
ALTER TABLE PELANGAN ADD COLUMN PHONE CHAR(5) AFTER ALAMAT;
l. Mengubah Tipe Data atau Lebar Kolom Pada Tabel : ALTER TABLE NAMATABEL
MODIFY COLUMN FIELD TIPE
Namatabel adalah nama tabel yang akan diubah tipe data atau lebar
kolomnya. Field adalah kolom yang akan diubah tipe data atau lebarnya. Tipe
adalah tipe data baru atau tipe data lama dengan lebar kolom yang berbeda.
Berikut ini contoh perintah untuk mengubah tipe data untuk kolom keterangan
dengan char(20) :
ALTER TABLE JENISFILM MODIFY COLUMN KETERANGAN VARCHAR(20);
m. Mengubah Nama Kolom : ALTER TABLE namatabel CHANGE COLUMN
namalamakolom namabarukolom tipedatabaru;
Namatabel adalah nama tabel yang akan diubah nama kolomnya,
namalamakolom adalah kolom yang akan diganti namanya, namabarukolom
adalah nama baru kolom, tipedatanya adalah tipe data dari kolom tersebut. Berikut
ini contoh perintah untuk mengubah nama kolom keterangan menjadi ket :
ALTER TABLE JENISFILM CHANGE COLUMN KETERANGAN KET VARCHAR(20);
n. Menghapus Kolom Pada Tabel : ALTER TABLE namatabel DROP COLUMN
namakolom;
o. Membuat dan Menghapus Index
Index berfungsi mempercepat proses pencarian data dalam suatu tabel.
Adanya index pada suatu field tabel menyebabkan proses pencarian otomatis akan
dilakukan terlebih dahulu ke dalam index, apabila ditemukan baru akan
diambilkan data sesungguhnya dari tabel, apabila tidak ditemukan dalam index,
sudah dipastikan bahwa data tesebut memang tidak ada dalam tabel. Index juga
dapat dibuat untuk setiap kolom yang akan dijadikan kriteria tertentu untuk
pencarian data sehingga proses pencariannya akan lebih cepat. Ada perintah untuk
membuat dan menghapus index, tapi tidak ada perintah untuk merubah index.
Perhatikan contoh berikut :
CREATE INDEX IDXNOMOR ON MAHASISWA(NIM);
Atau11
ALTER TABLE MAHASISWA ADD INDEX IDXNIM(NIM);
Untuk menghapus index :
DROP INDEX IDXNIM ON MAHASISWA;
Atau
ALTER TABLE MAHASISWA DROP INDEX IDXNIM;
Praktik !
1. Buat sebuah database dengan nama coba !
2. Buat sebuah tabel dengan nama mahasiswa di dalam database coba !
3. Tambahkan sebuah kolom : keterangan (varchar 15), sebagai kolom terakhir !
4. Tambahkan kolom nim (int 11) di awal (sebagai kolom pertama) !
5. Sisipkan sebuah kolom dengan nama phone (varchar 15) setelah kolom alamat
varchar(15) !
6. Ubah kolom nim menjadi char(11) !
7. Ubah nama kolom phone menjadi telepon (varchar 20) !
8. Hapus kolom keterangan dari tabel !
9. Ganti nama tabel menjadi student!
10. Buat index untuk tabel tersebut !
11. Jadikan nim sebagai primary key !
Evaluasi dan Pertanyaan
1. Tulis semua perintah-perintah SQL percobaan di atas beserta outputnya !
2. Apa kegunaan dari index di tabel ?
3. Apa maksud dari int (11) ?
4. Ketika kita melihat struktur tabel dengan perintah desc, ada kolom Null yang
berisi Yes dan No. Apa maksudnya ?
Kesimpulan
12
TULISKAN JAWABAN ANDA PADA LEMBAR KERJA
PRAKTIKUM (LKP)
MODUL III
Data Manipulation Language (DML)
Tujuan :
Setelah menyelesaikan modul ini, Anda diharapkan dapat :
1. Mengenal data manipulation language dan mampu menggunakannya
2. Mampu mengelola record dan retrieve data
Dasar Teori
DML (Data Manipulation Language) DML adalah kelompok perintah yang
berfungsi untuk memanipulasi data dalam basis data, misalnya untuk pengambilan,
penyisipan, pengubahan dan penghapusan data. Perintah yang termasuk dalah kategori
DML adalah : INSERT, DELETE, UPDATE dan SELECT.
1. I N S E R T
Perintah INSERT digunakan untuk menambahkan baris pada suatu tabel. Terdapat dua
cara untuk menambah baris, yaitu:
Cara 1: Menambah baris dengan mengisi data pada setiap kolom :
INSERT INTO namatabel VALUES (nilai1,nilai2,nilai-n);
Cara 2 : Menambah baris dengan hanya mengisi data pada kolom tertentu :
INSERT INTO namatabel (kolom1,kolom2,kolom-n) VALUES (nilai1,nilai2,nilai-n);
Keterangan :
Jika data bertipe string, date atau time (contoh : action, horor, 2007-11-10) maka
pemberian nilainya diapit dengan tanda petik tunggal ('horor') atau petik ganda ("horor").
Jika data bertipe numerik (2500, 400) maka pemberian nilainya tidak diapit tanda petik
tunggal maupun ganda.
2. DELETE13
Perintah DELETE digunakan untuk menghapus satu baris, baris dengan kondisi
tertentu atau seluruh baris. Syntax : DELETE FROM namatabel [WHERE kondisi];
Perintah dalam tanda [] bersifat opsional untuk menghapus suatu baris dengan
suatu kondisi tertentu.
3. UPDATE
Perintah UPDATE digunakan untuk mengubah isi data pada satu atau beberapa kolom
pada suatu table. Syntax :
UPDATE namatabel SET kolom1 = nilai1, kolom2 = nilai2 [WHERE kondisi];
Perintah dalam tanda [] bersifat opsional untuk mengubah suatu baris dengan suatu
kondisi tertentu.
4. SELECT
Perintah SELECT digunakan untuk menampilkan isi dari suatu tabel yang dapat
dihubungkan dengan tabel yang lainnya.
a. Menampilkan data untuk semua kolom menggunakan asterisk (*) :
SELECT * FROM namatabel;
b. Menampilkan data untuk kolom tertentu :
SELECT kolom1,kolom2,kolom-n FROM namatabel;
c. Menampilkan data dengan kondisi data tertentu dengan klausa WHERE:
SELECT * FROM namatabel WHERE kondisi;
14
MODUL IV
FUNGSI AGREGAT
Tujuan:
15
Setelah menyelesaikan modul ini, Anda diharapkan dapat :
1. Mahir menggunakan perintah fungsi agregat
Dasar Teori
Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
a. C OU N T
Perintah yang digunakan untuk menghitung jumlah baris suatu kolom pada tabel.
Contoh: Perintah untuk menghitung jumlah baris kolom jenis pada tabeljenisfilm:
SELECT COUNT(namafield) FROM nama_tabel;
b. SUM
Perintah yang digunakan untuk menghitung jumlah nilai suatu kolom pada tabel.
Contoh : perintah untuk menghitung jumlah nilai kolom harga pada tabel jenisfilm :
SELECT SUM(namafield) FROM nama_tabel;
c. AVG
Perintah yang digunakan untuk menghitung rata- rata dari ni lai suatu kolom pada
tabel. Contoh : perintah untuk menghitung rata-rata dari kolom harga pada tabel
jenisfilm:
SELECT AVG(namafield) FROM nama_tabel;
d. MIN
Perintah yang digunakan untuk menampilkan nilai terkeci l dari suatu kolom pada
tabel. Contoh : perintah untuk menampilkan nilai terkecil dari kolom harga pada tabel
jenisfilm:
SELECT MIN(namafield) FROM nama_tabel;
e. MAX
Perintah yang digunakan untuk menampilkan nilai terbesar dari suatu kolom pada
table. Contoh : perintah untuk menampi lkan nilai terbesar dari kolom harga pada table
jenisfilm :
SELECT MAX(namafield) FROM nama_tabel;
Praktik!!
1. Buat sebuah nama database dengan nama perdagangan.
2. Buat sebuah table dengan nama barang dimana ketentuannya seperti dibawah ini :
16
3. Isi data ke dalam table barang seperti berikut ini :
4. Tampilkan semua isi record pada table barang.
5. Ubah nama barang DVD Player menjadi TAPE dan stok barang menjadi 25.
6. Tampilkan satu baris / record data yang telah diubah tadi yaitu record dengan nama
DVD Player.
7. Hapus kode barang yang bernama MOUSE.
8. Tampilkan record / data yang mempunyai satuan barang bernilai UNIT.
9. Tampilkan jumlah baris dan kolom dari field kode barang dan nama barang pada table
barang.
10. Tampilkan jumlah stok barang dan jumlah harga barang pada table barang.
11. Tampilkan jumlah rata – rata harga barang pada table barang
12. Tampilkan jumlah stok barang terkecil pada table barang
13. Tampilkan jumlah stok barang terbesar pada table barang
MODUL V
RETRIEVE, GROUP, FILTER DAN PATTER MATCHING
Tujuan :
17
TULISKAN JAWABAN ANDA PADA LEMBAR KERJA
PRAKTIKUM (LKP)
Setelah melakukan percobaan ini, Anda diharapkan dapat :
1. Mampu meretrieve data dan mengelompokkannya
2. Mampu menampilkan data dengan pencocokan pola / karakter
Dasar Teori
RETRIEVE SQL dengan GROUP BY dan HAVING
Klausa GROUP BY digunakan untuk melakukan pengelompokkan data. Syntax :
SELECT field1,SUM(field2) FROM namatable GROUP BY field1;
Sebagai contoh, terdapat table barang dengan data sebagai berikut :
Klausa HAVING digunakan untuk menentukan kondisi bagi klausa GROUP BY.
Kelompok yang memenuhi HAVING saja yang akan dihasilkan. Syntax :
SELECT field1 FROM namatabel GROUP BY field1 HAVING COUNT(field2);
PATTERN MATCHING (Pencocokan Pola / Karakter)
Fungsi string digunakan untuk menampilkan data yang di dasarkan pada
pencarian dengan karakter. Pada pencarian data digunakan syntax LIKE, pada dasarnya
syntax LIKE hampir sama dengan syntax = .
Bedanya kalau syntax = , maka pencarian karakter harus sesuai dengan kata yang
kita buat tetapi dengan menggunakan LIKE karakter yang akan kita tampilkan tidak harus
lengkap hanya dengan menuliskan salah satu huruf atau kata saja, maka semua data yang
akan kita cari akan ditampilkan.
SQL mempunyai dua symbol khusus yang dipakai untuk pencocokan pola :
1. % : digunakan untuk mencocokkan karakter sebelum atau sesudah tana %;
2. _ : diguanakan untuk mencari karakter sebanyak jumlah tanda _.
Contoh:
LIKE ‘%GLASGOW%’ artinya digunakan untuk mencari data pada kolom tertentu yang
megandung karakter ‘GLASGOW’.
Bentuk umumnya:
18
SELECT * FROM nama_tabel WHERE nama_kolom LIKE ‘char%’;
SELECT * FROM nama_tabel WHERE nama_kolom LIKE ‘%char’;
SELECT * FROM nama_tabel WHERE nama_kolom LIKE ‘%char%’;
SELECT * FROM nama_tabel WHERE nama_kolom NOT LIKE ‘%char%’;
SELECT * FROM nama_tabel WHERE nama_kolom LIKE ‘_’;
Praktik 1!
Tampilkan record / data hanya kolom satuan barang dan digabungkan dengan jumlah stok
barang yang dikelompokkan berdasarkan kolom satuan barang pada table barang diatas!
Evaluasi dan Pertanyaan !
1. Jika syntax ini : SELECT kode_barang, satuan_barang, SUM(stok_barang) from barang
GROUP BY satuan_barang. Apa yang akan terjadi ?. Jelaskan!
2. Jika syntax ini diketikkan pada cmd: SELECT nama_barang, satuan_barang,
SUM(stok_barang) GROUP BY satuan_barang. Apa yang akan terjadi ?. Jelaskan!
Praktik 2!
1. Buat table dengan nama penjualan dengan ketentuan sebagai berikut :
2. Isi data table penjualan seperti dibawah ini :
3. Buat SQL sehingga tampilannya seperti berikut :
19
4. Buat SQL sehingga tampilannya sebagai berikut :
ID PRODUK TOTAL
4 50
5. Buat table barang dan isi datanya seperti dibawah ini :
kode_brg nama_brg harga_modal harga_beli stokB01 Sabun 2000 2500 15
B02 Pasta Gigi 2500 3000 15
B03 Sikat Gigi 3000 4000 10
B04 Rokok 6000 7000 30
B05 Korek Api 500 600 10
6. Buat SQL sehingga tampilannya sebagai berikut :
kode_brg nama_brg harga_modal harga_beli stokB02 Pasta Gigi 2500 3000 15
B03 Sikat Gigi 3000 4000 10
B05 Korek Api 500 600 10
7. Buat SQL sehingga tampilannya sebagai berikut :
kode_brg nama_brg harga_modal harga_beli stokB01 Sabun 2000 2500 15
B03 Sikat Gigi 3000 4000 10
8. Buat SQL sehingga tampilannya sebagaia berikut :
kode_brg nama_brg harga_modal harga_beli stokB01 Sabun 2000 2500 15
B02 Pasta Gigi 2500 3000 15
B03 Sikat Gigi 3000 4000 10
20
B05 Korek Api 500 600 10
9. Buat SQL sehingga tampilannya sebagai berikut :
kode_brg nama_brg harga_modal harga_beli stokB04 Rokok 6000 7000 30
10. Buat SQL sehingga tampilannya sebagai berikut :
kode_brg nama_brg harga_modal harga_beli StokB01 Sabun 2000 2500 15
B04 Rokok 6000 7000 30
Evaluasi dan Pertanyaan :
1. Tulis semua perintah – perintah SQL pada percobaan di atas beserta outputnya !
2. Berikan kesimpulan Anda !
MODUL VI
VIEW DAN JOIN
21
TULISKAN JAWABAN ANDA PADA LEMBAR KERJA
PRAKTIKUM (LKP)
Tujuan:
Setelah melakukan percobaan ini, Anda diharapkan dapat :
1. Mampu menampilkan data dengan view
2. Mampu menampilkan gabungan data dari tabel yang berbeda.
Dasar Teori
View
Merupakan salah satu objek database, yang secara logika merepresentasikan
sub himpunan dari data yang berasal dari satu atau lebih tabel. Kegunaan view adalah
untuk membatasi akses database, membuat query kompleks secara mudah, mengijinkan
independensi data dan untuk menampilkan view (pandangan) data yang berbeda dari data
yang sama.
Sintak dari View adalah :
CREATE [OR REPLACE] [<algorithm attributes>]
VIEW [database.]< name> [(<columns>)]
AS <SELECT statement> [<check options>];
Keterangan :
create: Statemen ini digunakan untuk membuat suatu view baru, atau mengganti suatu
view yang telah ada (exist) jika klausa OR REPLACE diberikan.
select_statement: Suatu statemen SELECT yang menyediakan definisi dari view.
Statemen ini dapat men-select dari tabel dasar atau view yang lain. Statemen ini
membutuhkan CREATE VIEW privilege untuk view, dan beberapa privilege untuk setiap
kolom terpilih oleh statemen SELECT.
[(column_list)]: Daftar kolom yang akan dipilih.
Contoh:
a) Membuat View:
CREATE VIEW pelanggan_simpati AS
SELECT nama,alamat,tgl_lahir,telepon
FROM pelanggan WHERE telepon REGEXP ’^081[23]‘
22
ORDER BY nama;
b) Cara mengaksesnya:
SELECT * FROM pelanggan_simpati;
SELECT nama,alamat FROM pelanggan_simpati;
Join
Operasi Join, Join merupakan operasi yang digunakan untuk
menggabungkan dua tabel atau lebih dengan hasil berupa gabungan dari kolom-
kolom yang berasal dari tabel-tabel tersebut. Pada join sederhana, tabel-tabel
digabungkan dan didasarkan pada pencocokan antara kolom pada tabel yang
berbeda.
Pada MySQL, perintah join ada beberapa macam yaitu :
– Inner Join
– Outer Join
– Cross Join
– Union Join
A. Inner Join
Inner join merupakan jenis join yang paling umum yang dapat digunakan pada
semua database. Jenis ini dapat digunakan bila ingin merelasikan dua set data yang ada di
tabel, letak relasinya setelah pada perintah ON pada join.
Bentuk baku perintah inner join :
SELECT <field1>,<field2>,<fieldn> FROM <tabel1> INNER JOIN <tabel2>
ON <key.tabel1> = <key.tabel2>
Contoh :
– Tampilkan nip, nama dan gaji semua pegawai.
select i.nip,i.nama,p.gaji from infoprib i inner join pekerjaan
p on i.nip = p.nip;
B. Outer Join
23
Outer join merupakan jenis join yang sedikit berbeda dengan inner join. Pada
MySQL, bentuk perintah untuk menerapkan outer join ada 2 yaitu :
SELECT <field1>,<field2>,<fieldn> FROM <tabel1> LEFT JOIN <tabel2> ON
<key.tabel1> = <key.tabel2>
dan
SELECT <field1>,<field2>,<fieldn> FROM <tabel1> RIGHT JOIN <tabel2>
ON <key.tabel1> = <key.tabel2>
Left join digunakan dalam situasi ketika ingin mengembalikan semua elemen data set A,
terlepas dari apakah nilai kunci ada dalam data set B.
Right join digunakan dalam situasi ketika ingin mengembalikan semua elemen data set B,
terlepas dari apakah nilai kunci ada dalam data set A.
Contoh :
– Tampilkan nip, nama dan gaji semua pegawai
select i.nip,i.nama,p.gaji from infoprib i left join pekerjaan p
on i.nip = p.nip;
Pada contoh diatas, tabel infoprib sebagai tabel yang posisinya di kiri sedangkan tabel
pekerjaan berada pada posisi sebelah kanan. Sehingga bila perintah diatas diganti dengan:
select i.nip,i.nama,p.gaji from infoprib i left join pekerjaan p on
i.nip = p.nip;
hasilnya akan berkebalikan dengan left join.
Untuk lebih memahami, perhatikan diagram dibawah ini.
Bila ingin menampilkan semua data yang ada di tabel sebelah kiri baik yang
mempunyai pasangan ataupun yang tidak mempunyai pasangan dengan data pada tabel
di sebelah kanan, maka gunakan left join. Sedangkan bila yang terjadi adalah
kebalikannya, yaitu ingin menampilkan semua data yang ada di sebelah kanan baik yang 24
mempunyai pasangan ataupun yang tidak mempunyai pasangan dengan data pada tabel
di sebelah kiri, maka gunakan right join.
C. Cross Join
Cross join kadangkala disebut juga sebagai Cartesian Product. Bila
menggunakan cross join, maka hasil dari cross join akan menciptakan hasil yang
didasarkan pada semua kemungkinan kombinasi baris dalam kedua set data. Dengan
demikian, jumlah baris yang dikembalikan adalah N M , dimana N✕ adalah jumlah baris
dalam kumpulan data A dan M jumlah baris dalam kumpulan data B. Jelas, jumlah baris
dalam cross join dapat menjadi sampah.
Bentuk perintah dari cross join adalah :
SELECT <field1>,<field2>,<fieldn> FROM <tabel1> CROSS JOIN <tabel2>
atau
SELECT <field1>,<field2>,<fieldn> FROM <tabel1>, <tabel2>
Sebagai contoh adalah :
– Tampilkan nip, nama dan gaji semua pegawai
select i.nip,i.nama,p.gaji from infoprib i cross join pekerjaan p;
select i.nip,i.nama,p.gaji from infoprib i, pekerjaan p;
D. Union Join
Union didukung oleh MySQL mulai dari versi 4.0. Pemakaian union dapat
menyederhanakan perintah persyaratan OR yang bertingkat. Bila dalam sebuah query
menghasilkan pemakaian perintah OR yang lebih dari satu sehingga dapat membuat
bingung, sebagai gantinya digunakan perintah UNION. Union dapat dikatakan sebagai
perintah untuk menggabungkan hasil query sql yang fungsinya sama dengan perintah OR.
Agar lebih jelas, perhatikan contoh berikut ini.
Contoh :
– Tampilkan nip, nama dan gaji dari pegawai yang bekerja di bagian akuntansi dan SDM.
(anggap saja perintah sql yang digunakan sudah kompleks)
Bila menggunakan perintah OR, maka perintah sqlnya akan seperti dibawah ini :
25
select i.nip,i.nama,p.gaji from infoprib i join pekerjaan p on
i.nip=p.nip where p.kode_bag="3" or p.kode_bag="4";
Tetapi perintah OR dapat diganti dengan perintah UNION dan bila di jalankan
akan
menghasilkan hal yang sama.
(select i.nip,i.nama,p.gaji from infoprib i join pekerjaan p on
i.nip=p.nip where p.kode_bag="3") UNION
(select i.nip,i.nama,p.gaji from infoprib i join pekerjaan p on
i.nip=p.nip where p.kode_bag="4")
Praktik 1 !
Persiapan data:
1. Buatlah sebuah database dengan nama: dbperusahaan!
2. Buatlah tabel karyawan dengan struktur seperti berikut:
3. Buatlah tabel jabatan dengan struktur seperti berikut:
4. Buatlah tabel gaji dengan struktur seperti berikut:
26
5. Isilah record tabel karyawan sehingga tampilan seperti ini!
6. Isilah record tabel jabatan sehingga tampilannya seperti ini!
7. Isilah record tabel gaji sehingga tampilannya seperti ini!
Praktik View dan Join!
1. Buatlah SQL untuk menampilkan NIP,NAMA,JABATAN,GAJI dari tabel-tabel tersebut! Tampilkan hasilnya!
2. Buatlah view yang menampilkan NIP,NAMA,JABATAN,GAJI dari tabel-tabel tersebut! Tampilkan hasilnya!
3. Buatlah view yang menampilkan maksimum gaji karyawan seperti ini!
27
4. Buatlah view yang menampilkan minimal gaji karyawan!
5. Buatlah view yang menampilkan jumlah gaji karyawan!
6. Buatlah view yang menampilkan rata-rata gaji karyawan!
Praktik 2 !
Persiapan data:
1. Buatlah sebuah database dengan nama:
2. Buatlah tabel pengarang dengan struktur seperti berikut:
28
3. Isilah record tabel pengarang seperti berikut!
4. Buatlah tabel penerbit dengan struktur seperti berikut!
5. Isilah tabel penerbit seperti berikut!
6. Buatlah tabel buku dengan struktur seperti berikut!
7. Isilah tabel buku seperti berikut!
29
Pertanyaan:
Buatlah SQL dan view untuk menampilkan kolom-kolom di bawah ini!
1. kdbuku, judul, nama pengarang2. kdbuku, judul, nama penerbit3. kdbuku, judul, nama pengarang, nama penerbit4. tambahkan perintah kegunaan inner join, outer join, right join, dan left join
30
MODUL VII
DATA CONTROL LANGUAGE (DCL)
Tujuan:
Setelah menyelesaikan modul ini, anda diharapkan dapat:
1. Mengetahui dan memahami perintah Data Control Language
2. Dapat menggunakan perintah Data Control Language
Data Control Language (DCL) merupakan perintah-perintah yang dapat
digunakan untuk menjaga keamanan basis data. Perintah tersebut dapat dipakai untuk
menentukan akses basis data hanya dapat dilakukan oleh orang-orang tertentu dan dengan
macam akses yang dibatasi pula.
Adapun Objek-Objek DCL dalam Mysql diantaranya :
a. COMMIT
Adalah perintah yang berfungsi untuk mengendalikan pengeksekusian
transaksi yang menyetujui rangkaian perintah yang berhubungan erat dengan perintah
yang sebelumnya telah berhasil dilakukan.
Keadaan Data Sebelum COMMIT:
• Operasi manipulasi data terutama mempengaruhi database buffer, oleh sebab itu
keadaan datasebelumnya dapat diperbaiki.
• User yang sedang connect (current user) dapat me-review hasil dari operasi
manipulasi data dengan melakukan query terhadap table
• User lain tidak dapat melihat hasil dari operasi manipulasi data dari current user.
Oracle memberlakukan read consistency untuk menjamin bahwa setiap user melihat
data seperti yang ada pada saat commit terakhir.
31
• Baris-baris data yang sedang diubah akan dikunci (locked), user lain tidak
dapat mengubah data yang sedang diubah tersebut.
Kita menggunakan COMMIT untuk membuat seluruh perubahan data menjadi permanen.
• Hal-hal yang mengikuti COMMIT:
• Seluruh perubahan data ditulis ke database
• Keadaan data sebelumnya secara permanen hilang
• Seluruh user dapat melihat hasil dari transaksi
• Penguncian terhadap baris-baris data yang diubah akan dibebaskan, baris-baris
data tersebut sekaran tersedia kembali bagi user lain untuk melakukan perubahan data
berikutnya.
• Seluruh savepoints dihapus.
b. ROLLBACK
Adalah perintah yang berfungsi untuk mengendalikan pengeksekusian
transaksi yang membatalkan transaksi yang dilakukan karena adanya kesalahan atau
kegagalan pada salah satu rangkaian perintah.
c. Tabel USER dari database MySQL
Tabel user adalah tabel yang ada dalam database MySQL. Tabel user hanya
diperuntukkan bagi seorang Administrator (root). Tabel user bersifat global, apapun
perubahan yang terjadi pada tabel ini akan mempengaruhi jalannya keseluruhan system
MySQL.
Dari kolom yang didapat dari tabel user untuk MySQL yang embeded pada PHP Instant
adalah sebagai berikut :
32
Penjelasan fungsi :
33
Sintaks Umum
1. Menampilkan data pada kolom host, user dan password saja
SELECT host, user, password FROM user;
2. Menghapus semua user tanpa identitas
DELETE FROM user WHERE user=’’;
3. Mengganti password
UPDATE user SET password=password(‘xxxxxxxxxx’) WHERE user=’root’;
4. Perintah FLUSH.
FLUSH PRIVILEGES;
34
Perintah FLUSH PRIVILEGES adalah suatu perintah untuk mengaktifkan
perubahan-perubahan yang terjadi pada user, seperti hak akses, penggantian password
pada user, dsb. Perintah FLUSH PRIVILEGES ini hukumnya wajib dilaksanakan setelah
Anda melakukan perubahan (apapun juga) secara langsung ke dalam tabel user atau ke
dalam database mysql.
Contoh penggunaan :
1. Menggunakan database mysql
use mysql;
2. Menambah user =’Winda’ dan password=’w1nd’
insert into user (User,Password) values(‘Winda’,’w1nd’);
3. Menambah user =’Febe’ dan password=’4567’
insert into user (User,Password) values(‘Febe’,’4567’);
4. Menambah user =’Elfrida’ dan password=’9812’
insert into user (User,Password) values(‘Elfrida’,’9812’);
5. Menampilkan user dan password tabel user
select user,password from user;
6. Menghapus semua user tanpa identitas
Delete from user where user=’’;
7. Menampilkan semua isi dari tabel user
Select * from user;
8. Mengubah password untuk user winda dengan ‘w1nd4’
Update user set password =’w1nd4’ where user=’Winda’;
9. Mengubah password untuk user Febe dengan ‘f3b3’
Update user set password =’f3b3’ where user=’Febe’;
10. Mengubah password untuk user Elfrida dengan ‘fr1d4’
Update user set password =’ fr1d4’ where user=’Elfrida’;
35
d. Tabel Tables_Priv dari database MySQL
Tabel_priv berfungsi mengatur tabel apa saja yang dapat diakses oleh seorang
user, berikut jenis izin aksesnya. Tingkat akses hanya untuk tabel. Pada prinsipnya
hanya bekerja seperti db table, kalau tidak digunakan untuk tabel sebagai ganti
database.
Sintaks Umum:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES,
INDEX, ALTER, CREATE VIEW, SHOW VIEW
Contoh penggunaan:
1. Insert into tables_priv(User,table_name,table_priv)
values(’Febe,Elfrida’,’Point_Of_Sales.jenis’,’select’);
2. Insert into tables_priv(User,table_name,table_priv)
values(’Winda’,’Point_Of_Sales.jenis’,’select,insert’);
3. Insert into tables_priv(User,table_name,table_priv)
values(’Elfrida’,’Point_Of_Sales.item’,’select,insert,update’);
4. Insert into tables_priv (User,Table_name,Table_priv)
values (’Admin’,’Point_Of_Sales.user , Point_Of_Sales.Jenis, Point_Of_Sales.item,
Point_Of_Sales.JualGlobal,, Point_Of_Sales.JualDetail’, ’SELECT, INSERT, UPDATE,
DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, ALTER, CREATE
VIEW, SHOW VIEW’);
5. Insert into tables_priv (User,Table_name,Table_priv)
values(’kasir’,’Point_Of_Sales.jualDetail’,’select,insert,show view’);
6. Insert into tables_priv (User,Table_name,Table_priv) values(’Cewek’,’Point_Of_Sales.jenis, Point_Of_Sales.item, Point_Of_Sales.jualGlobal, Point_Of_Sales.jualDetail’,’select,insert,update,show view’);
30
7. update tables_priv set host=’localhost’ where user=’Admin’;
8. update tables_priv set host=’192.168.10.2’ where user=’Cewek’;
9. update tables_priv set grantor =’ r oot@ l o c a l h o s t ’ where user=’Admin’;
10. delete from tables_priv where user=’cewek’;
e. GRANT
Grant berfungsi untuk membuat user baru dan memberikan hak istimewa. Grant
adalah salah satu privilege untuk tabel. Grant digunakan untuk memberikan
privilege kepada tabel yang didefinisikan kepada pemakai lain. Privilege untuk
pemakai dalam perintah grant didefinisikan dengan menggunakan nama-nama
privilege. Nama privilege memudahkan administrator untuk dapat memberikan
priivilege tanpa harus tahu apa nama field dan tabel yang harus diisi.
Perintah grant secara otomatis akan menambah data pemakai apabila data
nama pemakai yang disertakan pada perintah tersebut belum ada dalam tabel user.
Perintah grant memudahkan administrator untuk tidak perlu melakukan perintah
pendefinisian privilege dengan menggunakan sql. Karena dengan menggunakan sql,
kita harus hafal nama tabel yang harus diisi, field apa saja yang harus diisi, jumlah field
yang harus diisi. Kesalahan mudah dilakukan dengan menggunakan perintah sql
karena ketidaktelitian atau ketidakhafalan nama tabel dan nama field yang harus diisi.
Sintak Umum:
GRANT hak_akses ON nama_tabel TO pemakai;
GRANT ALL PRIVILEGES ON database_name.* TO ‘myuser’ IDENTIFIED
BY ‘mypassword’;
Contoh Penggunaan :
1. GRANT SELECT ON Point_Of_Sales.jenis TO Febe;
31
2. GRANT SELECT ON Point_Of_Sales.jenisTO Winda;
3. GRANT SELECT ON Point_Of_Sales.item TO Elfrida;
4. GRANT ALL PRIVILEGES ON Point_Of_Sales.User TO Admin;
5. GRANT ALL ON Point_Of_Sales.jualDetail TO Admin
6. SHOW GRANTS FOR r oot@ l o c a l ho s t ;
7. SHOW GRANTS FOR Admin;
8. GRANT SELECT,INSERT ON Point_Of_Sales.jualDetail TO kasir;
9. GRANT SELECT(Kode,Nama) ON Point_Of_Sales.jenis TO Elfrida;
10. GRANT UPDATE(kodeItem,NmItem,kategori,Harga) ON Point_Of_Sales.item TO
Elfrida;
f. REVOKE
Untuk menghapus batasan hak akses yang telah diatur dengan menggunakan perintah
GRANT, digunakan perintah REVOKE.
Sintak umum :
REVOKE hak_akses ON nama_tabel FROM na m a Acc oun t@ n a m a H o s t;
Atau menghapus batasan hak akses untuk database & tabel :
REVOKE hak_akses ON nama_database.nama_tabel FROM user;
Atau menghapus batasan hak akses untuk kolom tertentu :
REVOKE hak_akses(field1,field2, field3,…) ON
nama_database.nama_tabel FROM user;
Penulisan perintah REVOKE: Hak Akses (field) : kita harus memberikan sedikitnya satu hak akses.
32
Untuk setiap hak akses yang diberikan, dapat juga diberikan daftar field yang diletakkan
dalam kurung, dan dipisahkan dengan tanda koma. Contoh: REVOKE select (nim,
nama), update, insert(nim), …
NamaTabel: merupakan nama tabel yang dikenal hak akses tersebut.
Harus ada sedikitnya satu nama tabel. Dapat menggunakan simbol asterik (*) untuk
mewakili semua tabel pada database aktif. Penulisan namaTabel dapat juga diikuti oleh
nama database diikuti nama tabel yang dipisahkan dengan tanda titik. Menggunakan
simbol *.* berarti semua database dan semua tabel yang dikenai hak akses tersebut.
n am a A cc oun t @n amaH o s t : jika nama account tidak ada, tidak pernah diberikan hak
akses dengan perintah GRANT sebelumnya maka akan terjadi error.
Contoh Penggunaan:
1. REVOKE SELECT ON Point_Of_Sales.jenis FROM Febe;
2. REVOKE SELECT ON Point_Of_Sales.jenis FROM Winda;
3. REVOKE SELECT ON Point_Of_Sales.item FROM Elfrida;
4. REVOKE ALL PRIVILEGES ON Point_Of_Sales.user FROM Admin;
5. REVOKE ALL ON Point_Of_Sales.jualDetail FROM Admin;
6. REVOKE SELECT,INSERT ON Point_Of_Sales.jualDetail FROM kasir;
7. REVOKE SELECT(Kode,Nama) ON Point_Of_Sales.jenis FROM Elfrida;
8. REVOKE UPDATE(kodeItem,NmItem,kategori,Harga) ON Point_Of_Sales.item
FROM Elfrida;
9. REVOKE INSERT ON Point_Of_Sales.jenis FROM Winda;
10. REVOKE ALL ON Point_Of_Sales.item FROM PUBLIC;
33
g. SHOW PROCESSLIST
Digunakan untuk menampilkan kegiatan apa saja yang terjadi pada MySQL
server atau menampilkan informasi mengenai thread yang dieksekusi di server. Bila
terdapat kegiatan yang membahayakan kita sebagai admin dapat menghentikan dengan
perintah KILL atau MySQLAdmin
Sintak umum :
SHOW PROCESSLIST;
Contoh penggunaan : SHOW PROCESSLIST;
h. KILL
Kill berfungsi menghentikan thread server / untuk membunuh proses yang sedang
berjalan.
Sintak Umum : KILL nomor_Id; Contoh penggunaan :
Kill 2;
34
i. OPTIMIZE TABLE
Tabel yang sering mengalami proses penghapusan dan penambahan akan
menyebabkan struktur yang tidak teratur secara fisik atau telah terjadi fragmentasi.
Penghapusan data dalam jumlah besar mempunyai peluang terjadinya fragmentasi. Terutama
untuk data bertipe VARCHAR, TEXT atau BLOB. Tidak semua DBMS dapat melakukan
fragmentasi, kita dapat melihat dukungan setiap DBMS.
Untuk mengatasi masalah fragmentasi solusinya adalah melakukan OPTIMIZE
TABLE. Untuk MySQL versi 3.23 keatas mendukung fasilitas OPTIMIZE TABLE. Perlu
diketahui pada saat OPTIMIZE TABLE dikerjakan, semua tabel akan di-lock (terkunci).
Proses fragmentasi sebaiknya dilakukan secara berkala, misalnya setiap minggu atau setiap
bulan.
Sintak umum:
OPTIMIZE TABLE tabel_1, tabel_2, tabel_3, tabel_n;
Contoh penggunaan:
OPTIMIZE TABLE Point_Of_Sales.User,Point_Of_Sales.item,
Point_Of_Sales.jualGlobal;
Praktik
1. Gunakan user / database pembelian yang telah dibuat pada latihan sebelumnya,
kemudian tambahkan sebuah table KARYAWAN
Column pada table karyawan :
35
Data pada table karyawan :
2. Kemudian ketik perintah berikut ini :
INSERT KARYAWAN VALUES ('K003', 'Cici', 'Perumnas');
UPDATE KARYAWAN SET alamat=’bukit besar’
WHERE NIK=’K003’; COMMIT;
SELECT * FROM KARYAWAN ;
3. Menurut anda apa yang terjadi pada table karyawan ?
4. Menurut anda apa fungsi dari commit ?
5. Ketik perintah berikut ini
DELETE * FROM
karyawan ;
ROLLBACK;
SELECT * FROM karyawan;
DELETE FROM karyawan WHERE
NIK=’K002’; SELECT * FROM
karyawan;
COMMIT;
6. Menurut anda apa fungsi dari rollback ?
36
MODUL VIII
DASAR – DASAR PL/SQL
Tujuan :
Setelah menyelesaikan modul ini, anda diharapkan dapat:
1. Memahami skrip-skrip bahasa prosedural dan struktur blok PL/SQL.
PL/SQL (Procedural Language/Structure Query Language) adalah suatu blok yang berisi
skrip-skrip bahasa prosedural. PL/SQL merupakan bahasa pemrograman procedural. PL/SQL
dapat meningkatkan kinerja database.
Structured query language (SQL) merupakan bahasa untuk berkomunikasi dengan database
relasional yang bersifat deklaratif (bukan prosedural) dengan menyatakn hasil (bukan cara
memperoleh hasil ). Sebagai contoh, statemen SQL dibawah ini akan menampilkan informasi
pelanggan dipontianak yang diurutkan secara ascending pada nama pelanggan.
SELECT nama, alamt, telp FROM pelanggan
WHERE kota = ‘PONTIANAK’ ORDER BY nama;
Tabel pelanggan mungkin saja memiliki indeks pada kolom nama, atau pada kolom kota,
atau mungkin pula tidak terdapat indeks. Untuk kecepatan akses, SQL melalui database engine
akan menentuakn cara tercepat memproses data, karena itu user tidak perlu menetukan
bagaimana cara mengambil data. Jadi SQL merupakan bahasa yang deklaratif tau
fungsional karena user hanya menjelaskan data yang diinginkan. Berbeda dengan bahasa
procedural pascal atau C yang akan melinbatkan proses seperti melakukan pengulangan
sampai akhir tabel atau kondisi kota terpenuhi, melakukan perbandingan kondisi kota yang
dicari maupun pengurutan nama.
Statement SQL dapat dikelompokkan atas;
Data Manipulation Language. Statemen yang digunakan untuk memanipulasi data dalam
objek schema melalui perintah select, insert, update, delete, explain plan, dan lock table.
37
Data Definition Language. Statement untuk membuat dan menghapus objek schema mealui
perintah create, alter, drop, rename, truncate, serta grant, revoke, analyze, comment, audit, dan
noaudit.
Statement Kontrol Transaksi yang emastiakn transaksi berlangsung secara normal
untuk konsistensi data, contohnya perintah commit, rollback, savepoint, dan set transaction.
Statement control Session yang mengatur agar suatu session memiliki karakteristik
tertentu dengan perintah alter sesion dan set role.
Statement control system yang mengelola instance database memalui perintah alter system.
Tipe Data
• Numerik
– NUMBER, BINARY_INTEGER, DEC, DOUBLE PRECISION, INTEGER,
INT, NUMERIC, REAL, SMALLINT
• Karakter
– VARCHAR2, CHAR, LONG
• DATE
• BOOLEAN
• ROWID
Tipe Data tambahan :
• RECORD
• ARRAY
Variable
Adalah sebuah peubah yang digunakan untuk menampung sebuah nilai di memori komputer.
Contoh:
DECLARE
X integer;
38
.....
Alamat varchar2(40); No_induk char(8);
BEGIN
X := 12;
Alamat := ‘Gelatik Dalam 391, Bandung’; No_induk := ‘DOG29549’;
END;
Konstanta
Digunakan untuk menyimpan sebuah nilai di memori komputer.Nilai yang disimpan
bersifat tetap (konstan).
Contoh :
DECLARE
pi CONSTANT real:= 3.14;
lebar CONSTANT integer := 100;
Komentar
Digunakan untuk memudahkan proses maintenance
Jenis komentar:
• /* ... */ : untuk beberapa baris komentar
• -- ... : untuk satu bari komentar
Contoh:
/* Ini adalah komentar Oracle */
-- Ini juga komentar Oracle
A. Struktur PL/SQL
Semua program PL/SQL harus mengikuti struktur pemrograman sebagai berikut:
Bagian Judul (Header)
39
Bagian ini hanya digunakan jika PL/SQL diberikan nama, misalnya untuk
prosedur atau fungsi. Bagian ini berisi nama blok, daftar parameter, dan
pengembalian hasil (Retrun) jika blok adalah fungsi.
Bagian Deklarasi (Declaration)
Bagian ini untuk membuat deklarasi mengenai semua variable dan konstanta yang
direferensikan dalam pernyataan PL/SQL. Bagian deklarasi ini dimulai dengan
perinyah DECLARE. Jika tidak ada variable atau konstanta yang ingin
dideklarasikan, bagian ini boleh dihilangkan.
Bagian Eksekusi (Execution)
Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan ditulis. Bagian
eksekusi ini harus dimulai dengan perintah BEGIN.
Bagian Perkecualian (Exception)
Bagian ini memuat cara menangani kesalahan-kesalahan (error) pada waktu
eksekusi program PL/SQL. Jika program tidak memuat cara menangani kesalahan,
bagian ini boleh dihilangkan. Setiap pernyataan PL/SQL harus diakhiri dengan
tanda titik-koma (;) dan semua program PL/SQL harus diakhiri dengan perintah
END.
Contoh suatu program PL/SQL lengkap adalah sebagai berikut:PROCEDUR ulangan
DECLARE
ulang NUMBER;
stop EXCEPTION;BEGIN
ulang := 0;
LOOPulang := ulang + 1;
Dbms_output.put(‘ulang #’);
Dbms_output.put_line
(to_char(ulang));
IF ulang > 100 THEN
RAISE stop;END IF;
END LOOP;
EXCEPTION
WHEN stop THEN
Dbms_output.put_line(‘Ulangan selsesai’);
END;
40
Program PL/SQL ini akan menghitung ulangan 100 kali dan kemudian diakhiri
dengan pesan ‘Ulangan selesai’.
B. Bagian Deklarasi
Pada bagian ini ditempatkan deklarasi semua variable dan konstanta (constant)
yang akan dipakai oleh pernyataan PL/SQL yang dibuat.
Perbedaan variable dengan konstanta adalah:
Konstanta (constant) – nilai ditentukan pada saat deklarasi dibuat sehingga nilainya
tetap pada saat program dieksekusikan.
Variabel – dapat menerima nilai baru atau nilainya diubah pada saat program
dieksekusikan. Waktu mendeklarasikan suatu variable atau konstanta, harus ditentukan
tipe data yang akan dipakai. Sehingga pada waktu mendeklarasikan variable atau
konstanta, tipe data SQL dapat dipakai. Di samping tipe data SQL, PL/SQL
mendukung sejumlah tipe datanya sendiri sebagai berikut :
BOOLEAN – dipakai untuk menyatakan data logika, yaitu TRUE (benar), FALSE
(salah), dan NULL (kosong).
BINARY_INTEGER – digunakan untuk mendeklarasikan bilangan yang tidak
mempunyai angka decimal. Tipe data NATURAL dan POSITIVE merupakan subset dari
BINARY_-INTEGER.
%TYPE – tipe data ini jika dipakai menandakan bahwa veriabel yang
dideklarasikan sama dengan tipe data dari kolom table tertentu. Ini mempermudah
deklarasi variable untuk menghitung tipe data kolom-kolom table yang sudah dibuat.
%ROWTYPE - tipe data ini menandakan bahwa sekelompok variable tertentu
adalah sama dengan tipe data dari row suatu table tertentu.
Table dan Record – tipe data komposit (composite datatype) untuk pemakaian yang
lebih kompleks.
Contoh deklarasi konstanta:DECLARE
Temperatur_asal NUMBER := 0;
Nilai_mula NUMBER (2) := 100;
Contoh deklarasi variable :DECLARE
Temperatur NUMBER (3.2);
41
Nama VARCHAR2 (30);
Alamat %TYPE;
Pada saat deklarasi, panjang tipe data dapat ditentukan (bounded) atau tidak (unbounded).
Jika ditentukan, maka jumlah memori yang dialokasikan untuk tipe data tersebut juga
ditentukan sesuai dengan deklarasi.
Tips untuk mendeklarasikan variabel :
Nama variable tidak boleh menggunakan kata kunci (key word) yang digunakan oleh
PL/SQL seperti : IF, ELSE, LOOP, WHILE, FOR, EXIT, NULL, dan END. Pilih nama
variable yang ada hubungannya dengan program yang akan dibuat. Tentukan aturan
pemberian nama (naming convention) yang sehingga mudah diingat.
Pergunakan konstanta untuk menggantikan bilangan tetap. Hapus variable-variabel yang
tidak dipakai lagi. Jika variable mewakili (represent) kolom-kolom suatu table,
pergunakan tipe data %TYPE waktu deklarasi.
Tabel PL/SQL
Tabel PL/SQL sebetulnya dapat diibaratkan semacam array satu dimensi
yang mempunyai satu kolom tunggal yang nilai-nilai datanya diakses dengan
menggunakan indeks. Indeks yang dapat dipakai hanya satu tipe yaitu
BINARY_INTEGER.
Perlu diperhatikan bahwa table PL/SQL tidak sama dengan table SQL yang dapat
memilki banyak kolom-kolom dan memiliki relational integrity. Kegunaan table PL/SQL
adalah untuk menampung data-data yang dihasilkan oleh program PL/SQL. Karena
PL/SQL tidak menyediakan fasilitas input/output sendiri, maka hasil yang didapat oleh
PL/SQL tidak begitu saja diakses oleh seorang pemakai atau program lain. Untuk itu
Oracle menyediakan dua cara untuk mengakses data-data yang dihasilkan oleh
PL/SQL, yaitu dengan menggunakan paket DBMS_OUTPUT.
Syntax yang harus digunakan untuk mendeklarasikan table PL/SQL adalah :TYPE nama_tipe IS TABLE OF
nama_tabel .nama_kolom%TYPE
INDEX BY BINARY_INTEGER;
Contoh mendeklarasikan table PL/SQL :SQL> DECLARE
2 TYPE tipr_no_pembeli IS TABLE OF42
3 Pembeli .no_pembeli%TYPE
4 INDEX BY BINARY_INTEGER;
Record PL/SQL
Record PL/SQL adalah tipe data komposit lain yang dapat dideklarasikan dengan
syntax sebagai berikut:TYPE nama_tipe IS RECORD
(nama_kolom tipe_data_kolom,
…
nama_kolom tipe_data_kolom) ;
Contoh deklarasi suatu record :SQL> DECLARE
2 TYPE record_no_pembeli IS RECORD
3 (no_pembeli pembeli .no_pembeli%TYPE,
4 nama_pembeli pembeli .nama%TYPE
5 alamat pembeli .alamat%TYPE
6 gaji number (10) ) ;
Record ini menggunakan tiga kolom dari table pembeli yang sudah ada dan satu kolom
baru yang tidak ada didalam table pembeli.
C. Bagian Eksekusi
Pada bagian ini pernyataan PL/SQL akan kita buat yang menentukan tujuan program
PL/SQL dibuat. Pernyataan pada bagian ini dapat digolongkan atas:
Pernyataan Penentuan (Assignment)
Pernyataan penentuan umumnya ditandai dengan penggunaan operator sama-dengan
(:=) seperti contoh : nomer_awal := 1; nomer_akhir :=
100; temp := tem +
1;
temp_c := (5/9)*(temp_f – 32);
Pernyataan Flow Control
Pernyataan flow control ini merupakan pernyataan yang paling sering dipakai untuk
mengatur langkah-langkah suatu program. Operator relasi yang dapat digunakan antara
lain:
43
IF, ELSE, ELSEIF
LOOP, END LOOP
EXIT, EXIT WHEN
WHILE LOOP, END LOOP
FOR LOOP, END LOOP
GOTO
NULL
C at a t a n:
Operasi-operasi relasi juga merupakan kata-kunci yang tidak boleh digunakan
sebagai nama variable pada pembuatan deklarasi program PL/SQL.
Contoh pernyataan flow control dengan perintah IF dan ELSE :
IF harga > 200000000 THEN
Diskon := harga * 0.1;
END IF
IF harga > 200000000 THEN
Diskon := harga * 0.1;
ELSEIFHarga > 150000000 AND
Harga < 200000000 THEN
Diskon := harga * 0.75;
ELSE
DISKON := 0;
END IF;
Contoh flow control dengan perintah LOOP:LOOP
i =: i + 1;
INSERT INTO jumlah_qty(id, qtyot)
VALUES (i, qty);
EXIT WHEN i = 10;
END LOOP;
Contoh dengan numeric loop:FOR I IN 1 .. 10
LOOP
DBMS_OUTPUT.PUT_LINE(I);
44
END LOOP;
Program ini akan membuat loop sepuluh kali.
Komentar membantu menjelaskan tujuan suatu pernyataan. PL/SQL menyediakan 2
cara untuk memberikan komentar, yaitu :
1. Dengan meletakkan di dalam tanda /* dan */ seperti contoh :/* Pernyataan ini berguna untuk menguji apakah ada kolom yang
mempunyai nilai kosong */
2. Dengan memberikan symbol – di depan komentar sepeti contoh :-- Pernyataan ini berguna untuk menguji apakah ada kolom yang
mempunyai nilai kosong.
Pernyataan SQL
Pernyataan-pernyataan SQL dapat disisipkan di dalam suatu program PL/SQL. Dengan
menyisipkan pernyataan SQL tersebut, program PL/SQL dapat memproses data table
dengan mudah seperti contoh berikut :
IF temp := 0
THEN
INSERT
INTO
tabel_temp (nomer,
date) VALUES
(temp, sysdate);
END IF
Program ini akan mengisi table bernama “table_temp”, jika nilai temp = 0 dengan
menggunakan perintah INSERT.
Pernyataan Kursor
Jika mengeksekusikan suatu pernyataan SQL lewat PL/SQL, Oracle akan menciptakan
suatu ruang kerja pribadi yang dinamakan PGA. Di dlam ruang ini data yang
dikembalikan oleh pernyataan SQL akan disimpan. Nama Kursor adalah suatu pointer
(penunjuk) ke ruang tersebut. Sebelum dapat dipakai, kursor harus dideklarasikan dahulu
seperti contoh :
DECLARE CURSOR c_pembeli
45
IS SELECT *
FROM pembeli
Kursor “c_pembeli” ini akan merupakan pointer ke ruang kerja pribadi di mana hasil
pernyataan “SQL SELECT * FROM pembeli” disimpan. Semua nama kursor yang
dideklarasikan pada suatu program PL/SQL disebut kursor eksplisit. Karena ada pula
kursor yang dinamakan kursor implisit berupa setiap pernyataan SQL yang
dieksekusi oleh PL/SQL.
Pada pembuatan kursor eksplisit, kolom-kolom yang ingin dipanggil oleh perintah
SELECT dapat di tentukan seperti contoh:
DECLARE CURSOR c_pembeli IS
SELECT no_pembeli, nama, alamat
FROM pembeli;
Pada contoh ini hanya ada tiga kolom-kolom dari table pembeli yang akan dipanggil. Kursor
dapat dibuka (open), dipindahkan (fetch), atau ditutup.
Contoh pernyataan untuk membuka kursor “c_pembeli”:OPEN c_pembeli;
Contoh pernyataan untuk memindahkan kursor “c_pembeli”:
FETCH c_pembeli INTO catatan_pembeli;
Contoh pernyataan untuk menutup kursor “c_pembeli” :CLOSE c_pembeli;
D. Bagian Exception
Bagian perkecualian ini perlu dibuat jika pernyataan PL/SQL mengembalikan
hasil jika kesalahan (error) pada saat program dieksekusikan.
PL/SQL menyediakan sejumlah exception siap pakai sebagai berikut :
DUP_VAL_ON_IND
EX
INVALID_NUMBER
NO_DATA_FOUND
TOO_MANY_ROWS 46
VALUE_ERROR
Disamping itu exception dapat didefinisikan sendiri (pre-defined exception) yang dapat
dipanggil dengan perintah RAISE di dalam program PL/SQL. Sedangkan pre-defined
exception akan dipanggil secara otomatis jika terjadi kesalahan tanpa menggunakan
perintah RAISE.
Sintaks untuk bagian exception ini adalah:
EXCEPTION
WHEN nama_exceptional
THEN Pernyataan PL/SQL;
…WHEN nama_exception THEN Pernyataan PL/SQL;
END;
Contoh penggunaan bagian exception dengan pre-defined exception NO_DATA_FOUND :SQL>
DECLARE
2 produk_row kategori%ROWTYPE;
3
4 BEGIN
5
6 DBMS_OUTPUT. ENABLE;
7
8 SELECT * INTO produk_row
9 FROM kategori
10 WHERE
11 Nama_kategori = ‘Tidak Ada’ ;
12 END;
13 /
declare
*
ERROR at line 1:
ORA-01403 : no data found
ORA-06512 : at line 5
47
Pada contoh ini ketika perintah SELECT memasukkan data pada table “kategori”
dimana kolom “nama_kategori” = ‘Tidak Ada’, karena nama_kategori itu tidak akan
ditemukan dalam table “kategori”, maka pesan kesalahan “NO DATA FOUND” akan
ditampilkan.
Jika sekarang pre-defined “NO_DATA_FOUND” digunakan pada program
PL/SQL, maka PL/SQL tidak akan menampilkan pesan kesalahan pada lingkungan
SQL*PLUS, melainkan menampilkan pesan kesalahan sesuai dengan penempatan pre-
defined tersebut di dalam program PL/SQL.
E. Cara Mengakses Hasil PL/SQL
Oracle menyediakan dua cara untuk mengakses data-data yang dihasilkan oleh PL/SQL, yaitu:
Dengan menggunakan paket DBMS_OUTPUT
Paket DBMS_OUTPUT ini disediakan oleh oracle untuk digunakan dengan
SQL*PLUS. Paket ini terdiri atas sejumlah prosedur dan fungsi siap pakai yang
mengatur intput/output suatu data untuk digunakan oleh PL/SQL.
Dengan menggunakan Tabel PL/SQL
Contoh penggunaan table PL/SQL pada suatu program PL/SQL :
SQL> DECLARE2 TYPE tupe_no_pembeli IS TABLE OF3 Pembeli.no_pembeli%TYPE4 INDEX BY BINARY_INTEGER;567 no_pembeli_ind tipe_no_pembeli;8 ind BINARY_INTEGER := 0;9 akhir BINARY_INTEGER;10
11 BEGIN1213 DBMS_OUTPUT . ENABLE;1415 FOR catatan_no_pembeli IN (SELECT no_pembeli
48
16 FROM pembeli)17 LOOP18 ind :+ ind +1;19 no_pembeli_ind(ind):=catatan_no_pembeli.no_pembeli;20 END LOOP;2122 Akhir := ind;2324 FOR ind 1 .. akhir2526 LOOP27 Dbms_output.put_line (‘no_pembeli_ind(‘ || TO CHAR (ind)28 || ‘) = ‘ || no_pembeli_ind (ind));29 END LOOP;3031 END;
Jika program PL/SQL tersebut dieksekusikan, akan tampil hasil sebagai berikut :SQL > /
No_pembeli_ind(1) = 5001
No_pembeli_ind(1) = 5002
No_pembeli_ind(1) = 5003
No_pembeli_ind(1) = 5004
No_pembeli_ind(1) = 5005
Contoh diatas memperlihatkan cara menggunakan table PL/SQL yang hasilnya diakses
oleh program PL/SQL dan ditampilkan oleh paket DBMS_OUTPUT.
SQL Dinamik
Ada dua cara menggunakan SQL dinamik dalam PL/SQL yaitu SQL Dinamik native dan
paket DBMS_SQL dinamik native dapat diletakkan secara langsung didalam PL/SQL
seperti perintah create table log_eror yang dijalankan melalui melalui EXECUTA
IMMEDIATE. Selain statement DDL untuk mereferensi objek yang belum tersedia
pada saat kompilasi, sql dinamik juaga dapat emnggunakan statement control transaksi,
control session, control system, dan statement DML. SQL dinamik insert into log_eror
49
pada
contoh di atas dikerjakan dengan paket DBMS_SQL. Paket PL/SQL ini memiliki
beberapa prosedur seperti utnuk membuka, parsing, dan mengeksekusi cursor.
PL/SQL
Pl/sql mengombinasikan kemampuan manipulasi data sql dengan kemampuan bahasa
procedural, dengan kata lain pl/sql adalah sql ditambah procedural language. Pl/sql
merupakan bahasa berstruktur blog yang berupa fungsi, prosedur, dan blok
anonym. Suatublok biasanya ditujukan untuk menyelesaikan suatu tugas tertentu dan
suatu blok boleh memliki sub-blok (nested). Struktur blok pl/sql itu terdiri dari bagian
deklarasi, bagian kode program, dan bagian eksepsi untuk penanganan eror seperti
tampak di bawah ini:[DECLARE
Declarasi variable]
BEGIN
Kode program
[EXCEPTION
Penanganan error]
END;
Selain membuat fungsi dan prosedur, pl/sql juga digunakan untuk menghasilkan
untuk mengelompokkan fungsi dan prsedur, serta untuk pembuatan kode program dalam
trigger. Tentu saja defeloper akan sering memanfaatkan pl/sql untuk membuat modul
atau program baik yang dilakukan melalui SQL * Plus, Form Builder, Prosedural
Builder, maupun Oracle Portal.
Blok pl/sql anonym
Blok pl/sql anonym adalah blok pl/sql tanpa nama dan tidak menggunakan parameter.
Blok anonym tidak disimpan dalam database swhinggan tidak bisa direferensi oleh blok
pl/swql lain. Pada saat blok ini dijalankan pertama kali,menempatannya shared pool
harus didahului dengan proses parsing dan kompilasi. Proses reparsing dan frekompilasi
tidak dilakukan jika blok telas berada di shared pool. Apabila blok pl/sql itu telah
50
dikeluuarkan dari shared pool, eksekusi ulang terhadap blok pl/sql tadi harus melalui
tehapad parsing dan kompilasi. Ini desebabkan blok pl/sql tidak disimpan di database
meskipun blok itu bisa disimpan pada file system operasi.
Dalam pemrograman reguler, blok blok pl/sql anonim itu dapat diubah menjadi fungsi,
prosedur, atau paket sehingga blok pl/sql bisa direferensi melalui namanya dan
menghindari duplikasipembuatan kode program.
a. Parameter
Sub program dipanggil denhgan melewatkan nilai, variabel, atau ekspresi seseuai
parameter yuang ada. Variabel yang berada pada statement pemanggil sub program atau
yangh disebut parameter aktual akan dikirimkan ke parameter formmal, yaitu variabel
yang dideklarasikan pada fungsi atatu prosedur yang dipanggil. Parameter formal dan
aktual itu harus memiliki tipe data yang sama atau kompatibel.
Pemanggilan sub program untuk melewatkan parameter aktual dapat dilakukan dengan
cara notasi posisi parameter, notasi nama parameter, dan notasi kombinasi. Penetapan
notasi posisi melakukan pemanggiolan sub program berdasarkan posisi atau urutan
parameter formal.
b. Mode parameter
Parameter formal menerima nilai atau variabel dari parameter aktual. Subprogram
dapat menggunakan parameter formal sesuain mode parameter yang ditetapkan yaitu IN,
OUT dan IN OUT. Secara default setiap parameter formal menggunakan mode IN
yang berarti bahwa parameter itu hanya dapat dibaca atau direferensi dalam body
subprogram. Mode OUT berarti parameter formal hanya dapat ditulis atau diberikan
nilai, sedangkan mode IN OUT memungkinkan parameter formal untuk dibaca dan
ditulis. Jadi untuk dapat mengembal\ikan berbagai nilai maka subpreogram harus
menggunakan mode OUT ataub mode IN OUT, sedangkan mode IN bersifat seperti
konstanta yang tidakn dapat dimodifikasi dalam body proseduryang dipanggil.
c. Compiler Hint NOCOPY
Proses pengiriman parameter aktual dapat dilakukan dengan dua cara yaitu by reference
dan by value. Pengiriman parameter aktual secara by reference dilakukan dengan
melewatkan pointerv parameter aktual ke parameter formal sehinggan kedua parameter
mereferensi lokasi yang sama. Mekanisme itu berlanmhgsung pada parameter formal
51
dengan mode IN.
Secara default, pengiriman parameter aktual pada subprigram yang menggunakan mode
OUT dan IN OUT dilakukan secara by value yaitu nilai parameter aktual dicopykan
ke parameter formal. Setelah eksekusi subperogram diakhiri, nilai parameter formal
akan dikopiakn ke parameter aktual sehingga nilain parameter aktual berubah. Utnuk
mengubah pola pengiriman by value mengjadi by reference gunakan hint compiler
nocopy sehingga proses copi nilai data dapat dihindari. Penggunaan copy nilai data
antara kedua parameter itu dapat menurunkan kecepatan eksekusi dan meningkatkan
penggunaan memori terutama pada parameter yang menangani data berukuran besar seperti
record, collection, dan tipe objek.
Nocopy menuebabkan perubahan nilai pada parametr formal segera mempengaruhi nilai
parameter aktual. Ini berbeda dengan kondisi defaultnya dimana jika subprogram
berakhir secara tudak normal maka parameter formal tidak akan dicopy pada parameter
aktual. Sebagai suatu hingt comliper, nocopy melakukan pengiriman paremeter by
reference, sebaiknya pengiriman pewaremeter aktual dilakuakan secara by value.
Sub Program
Sub program adalah blok pl/sql dengan nama yang dapoat menggunakan parametter
serta bisa dipangguil oleh statement sql. Sub program terdiri dari prosedur untuk
mengerjakan suatu proses dan function yang mengembalikan suatau nilai. Sub program
itu disimpan dalam database sebagai objec schema dan disebut sebagai stored prosedure
dan stured function Compiler pl/sql akan membuat parse tree dan pseudo code dari stored
sub program itu sehingga ketika sub program dieksekusi pertama kali, engine pl/sql akan
menjalankan pseudo code dan membawanya ke shared pool. Sub program ini dapat
dijalankan ulanng tanpa perlou recompilasi karena code program dan bentuk compilasinya
disimpan pada database dan statusnya ada di data dictionary.pada blok pl/sql anonim,
setiap kali blok tu dijalankan maka blok akan diparsing, dikompilasi, dan ditempatkan
pada shared pool. Proses reparsing dan recompilasi tidak dilakukan jika blok telah
berada di shred pool. Namun jika bentuk terkompilasi blok terseebut tidak ada di shared
pool peroses parsing, kompilasi harus dilakukan ulang.
a. Fungsi
Fungsi adalah syb program yang mengembalikan suatu nilai. Fungsi terdiri dari 2 bagian
52
yaitu spesifikasi dan body . bagian spedifikan dimulai dari keyword function hingga
keyword return. Bagian body dimulai dari keyword IS atau AS hingga keyword AND
dan mencakup bagian deklarasi, kode [program dan ekseption.
b. Stored function
Stored function adalah fungsi yang disimpan dalam databasse seperti halnya tabel,
stoder function merupakan objek schema dan bisa direferensi ole statement sql.
Perubahan fungsi menjadi stored funcotion dilakukan dengan mmenyertakan klausa create
yuntuk menyimpan de database namun apabila fungsi itu sudah ada, sertakan klausa or
replaceuntuk modifikasinya.
c. Pepelined
Pepelined poada fungsi akan mengembalikan secara berulang hasil dari fungsi tabel yang
berupa tipe koleksi ( nested table atau varray).
d. Pragma restrict_references
Untuk dapat digunakan melalui statement sql, suatu stored function harus mematuhi
aturan purity untuk mengontrol side effect.
e. prosedur
adalah sub program yang umumnya digunakan untuk mengerjakan suatu proses.
Prosedur terdiri dari 2 bagian yaitu spesifikasi dan body.
f. Stored prosedure
Adalah prisedur yang disimpan pada database sebagai objek schema. Suatu prosedur
dapat diubah menjadi stored prosedure dengan menyertakan klausa cretae pada definisi
prosedur.
g. Overloading
Overloading memungkinkan prosedur dan fungsi menggunakan nama sama. Sub program
yang di overload harus menggunakan parameter formal yang berbeda seperti jumlah
paramter, urutan, dan tiprn datanyasub program atau metode tipe dapat di overloadanjika
berada dalam paket.
53
h. Definer dan invoker rigths
Agar user dapat ememb uat sub program pada schemanya, ia harus memiliki privileghe
sistem create prosedure, sedangkan pembuatan sub program pada schema user lain
dimungkinkan apabila user memiliki privilege sistem create any prosedure.
i. Status sub program
Sub program dalam database yang telah dikompilasi tanpa kesalahan akan bersetatus
valid Status itu disimpan pada dictionatry sehingga dapat dideteksi bilamana objek
berstatus valid atau invalid.
j. Melihat source sub program
Source sub program yang disimpan dalam database dapat dilihat melalui view
dictionary DBA_SOURCE.
Transaksi otonom
Adalah transaksi independen (autonomous) yang tidak berbagi pakai resources
dengan transaksi induknya oleh karena itu rollback pada transaksi induk tidak
mempengaruhi status transaksi otonom (TO) dan commit pada TO akan segera
tampak bagi transaksi lain, serta eksepsi yang terjadi pada transaksi otonom
meneyebabkan rollback pada transaction level bukan statement level. Transaksi otonom
itu tidak dapat melihat perubahan yang belum di commit dan tidak berbagi pakai
penguncian (Locking) dengan transaksi induk.
a. Menetapkan transaksi otonom
Suatu transaksi akan bersifak independen jika terdapat pragma atau compiler detective
autonomous_transaction. Pragma ini mengionstruksikan compiler pl/sql untuk menandai
suatu rutin yang independent.
b. Kontrol Transaksi otonom
Ketika rutin otonom dijalannkan, transaksi indik ditangguhkan (suspend) dan dilanjutkan
setelah TO berakhir. Perintah commit dan rollback mengakhiri TO untuk
kemudian melanjutkan statement berikutnya. Statement save point ditransaksi
pemanggil tidak berhubungan dengan save pointy di TO sehinggan rollback ke save
point hanhya dapat dilakukan pada konteks transaksi individual.
54
MODUL IXPROSEDUR DAN FUNGSI
Untuk membuat stored procedure/function pada database digunakan pernyataan CREATE
PROCEDURE atau CREATE FUNCTION.
PROCEDURE Sintak :
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body Keterangan :
_sp_name : Nama routine yang akan dibuat
_proc_parameter : Parameter stored procedue, terdiri dari :
_IN : parameter yang digunakan sebagai masukan.
_ OUT : parameter yang digunakan sebagai keluaran
_ INOUT : parameter yang digunakan sebagai masukan sekaligus keluaran.
_ routine_body : terdiri dari statemen prosedur SQL yang valid.
Agar lebih jelas, perhatikan contoh penggunaannya berikut ini.
_ Contoh 1:
mysql> delimiter //
mysql> create procedure pMhsIlkom(OUT x varchar(25))
-> begin
-> select nama into x from mahasiswa where kode_prodi='P01';
-> end
-> //
mysql> call pMhsIlkom(@Nama);
-> select @Nama;
55
-> //
Dari contoh diatas terlihat bahwa parameter “x” (sebagai OUT) digunakan untuk menampung
hasil dari perintah routine_body. Pernyataan “into x”, inilah yang mengakibatkan “x”
menyimpan informasi nama (sebagai kolom yang ter-select). Untuk menjalankan procedure
digunakan ststemen call. Pernyataan “call pMhsIlkom(@Nama)” menghasilkan informasi
yang kemudian disimpan pada parameter “@Nama”. Kemudian untuk menampilkan
informasi ke layar digunakan pernyataan “select @Nama”.
_ Contoh 2: mysql> delimiter //
mysql> create procedure pMhs(out x varchar(25), out y varchar(3), in zchar(3)) -> begin -> select nama,alamat into x,y from mahasiswa where kode_prodi=z; -> end -> //
mysql> call pMhs(@Nama,@Alamat,'P01');
mysql> select @Nama, @Alamat;
Dari contoh yang kedua ini terlihat bahwa parameter “z” (sebagai IN) digunakan sebagai
jalur untuk masukan routine dan parameter “x” dan “y” digunakan untuk menampung hasil
dari perintah routine_body. Pernyataan “into x, y”, inilah yang mengakibatkan “x” dan “y”
menyimpan informasi nama dan alamat (sebagai kolom yang ter-select).
Pernyataan “call pMhs(@Nama, @Alamat)” menghasilkan informasi yang kemudian
disimpan pada parameter @Nama dan @Alamat, sedangkan parameter “z” digunakan untuk
menampung string ‘P01’ yang kemudian digunakan untuk memproses routine_body .
56
Kemudian untuk menampilkan informasi ke layar digunakan pernyataan “select @Nama,
@Alamat”.
Jika diperhatikan pada contoh1 dan contoh2, dalam membuat routine selalu menggunakan
delimiter. Hal ini digunakan untuk mengubah pernyataan delimiter dari “;” ke “//” ketika
suatu procedure sedang didefinisikan. Sehingga sebelum delimiter ditutup, meskipun sudah
ditekan enter masih dianggap satu-kesatuan perintah. Jika menggunakan perintah delimiter,
maka untuk menutupnya digunakan karakter backslash (‘\’) karena karakter ini merupakan
karakter escape untuk MySQL.
FUNCTION
Secara default, routine (procedure/function) diasosiasikan dengan database yang sedang aktif.
Untuk dapat mengasosiasikan routine secara eksplisit dengan database yang lain, buat routine
dengan format: db_name.sp_name.
MySQL mengijinkan beberapa routine berisi statemen DDL, seperti CREATE dan DROP.
MySQL juga mengijinkan beberapa stored procedure (tetapi tidak stored function) berisi
statemen SQL transaction, seperti COMMIT. Stored function juga berisi beberapa statemen
baik yang secara eksplisit atau implisit commit atau rollback.
Sintak :
CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type
type: Any valid MySQL data type
characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
routine_body: Valid SQL procedure statement or statements
57
Keterangan :
_ sp_name: Nama routine yang akan dibuat
_ proc_parameter: Spesifikasi parameter sebagai IN, OUT, atau INOUT valid hanya untuk
PROCEDURE. (parameter FUNCTION selalu sebagai parameter IN)
_ returns: Klausa RETURNS dispesifikan hanya untuk suatu FUNCTION. Klausa ini
digunakan untuk mengembalikan tipe fungsi, dan routine_body harus berisi suatu statemen
nilai RETURN.
_ comment: Klausa COMMENT adalah suatu ekstensi MySQL, dan mungkin digunakan
untuk mendeskripsikan stored procedure. Informasi ini ditampilkan dengan statemen SHOW
CREATE PROCEDURE dan SHOW CREATE FUNCTION.
_ Contoh:
mysql> delimiter //
mysql> create function fcNamaMHS(x char(25)) returns char(40) -> return concat('Nama : ', x); -> //Query OK, 0 rows affected (0.00 sec)
mysql> select fcNamaMHS('Sholihun');
Dari contoh diatas terlihat bahwa parameter “x” diperlakukan sebagai IN karena sebagaimana
dijelaskan sebelumnya bahwa fungsi hanya bisa dilewatkan dengan parameter IN. Kemudian
untuk pengembalian nilainya, digunakan tipe data dengan kisaran nilai tertentu (dalam hal ini
char(40)) dengan diawali pernyataan returns.
Pernyataam “concat('Nama : ', x)” merupakan routine_body yang akan menghasilkan
gabungan string “Nama :” dengan nilai dari parameter “x” yang didapat ketika fungsi ini
dieksekusi. Perintah yang digunakan untuk mengeksekusi fungsi adalah “select
fcNamaMHS('Sholihun')”.
_ Menampilkan status fungsi tertentu:
mysql> show function status like 'fcNamaMHS'\G
58
Berisi tentang database bersangkutan, tipe fungsi, definer dan lain-lain.
Praktik!
1. Buatlah sebuah database dengan nama: dbperusahaan!2. Buatlah tabel karyawan dengan struktur seperti berikut:
3. Buatlah procedure tambah record pada tabel tersebut dan tambahkan 2 record
menggunakan procedure tsb!
P006 M. ALI SETIADI JL. TJ. SIAPI-API NO.11 J06 1500000
P007 ARMITA SARI JL. MERICA NO.35 J07 1000000
4. Tampilkan seluruh record pada tabel karyawan!
5. Buatlah procedure untuk mengedit record pada tabel tersebut dan editlah 2 record tadi
menggunakan procedure tsb!
P006 MERI ANDANI JL. SIMPANG 5 NO.70 J06 1500000
P007 SARITA MILI JL. PARAMESWARA NO. 89 J07 1000000
6. Tampilkan seluruh record pada tabel karyawan!
7. Buatlah procedure untuk menghapus record pada tabel tersebut dan hapuslah 2 record tsb
menggunakan procedure tsb!
8. Tampilkan seluruh record pada tabel karyawan!
59
MODUL XTRIGGER
Pernyataan CREATE TRIGGER digunakan untuk membuat trigger, termasuk aksi apa yang
dilakukan saat trigger diaktifkan. Trigger berisi program yang dihubungkan dengan suatu
tabel atau view yang secara otomatis melakukan suatu aksi ketika suatu baris di dalam tabel
atau view dikenai operasi INSERT, UPDATE atau DELETE.
Sintak :
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Keterangan :
_ [DEFINER = { user | CURRENT_USER }]: Definisi user yang sedang aktif, sifatnya
opsional.
_ trigger_name: Nama trigger.
_ trigger_time: waktu menjalankan trigger. Ini dapat berupa BEFORE atau AFTER.
_ BEFORE: Membuat trigger diaktifkan sebelum dihubungkan dengan suatu operasi.
_ AFTER: Membuat trigger diaktifkan setelah dihubungkan dengan suatu operasi.
_ trigger_event: berupa kejadian yang akan dijalankan trigger.
_ trigger_event dapat berupa salah satu dari berikut:
_ INSERT : trigger diaktifkan ketika sebuah record baru disisipkan ke dalam tabel.
Contoh: statemen INSERT, LOAD DATA, dan REPLACE.
_ UPDATE : trigger diaktifkan ketika sebuah record dimodifikasi.
Contoh: statemen UPDATE.
_ DELETE : trigger diaktifkan ketika sebuah record dihapus. Contoh: statemen
DELETE dan REPLACE.
Catatan : trigger_event tidak merepresentasikan statemen SQL yang diaktifkan trigger
sebagai suatu operasi tabel. Sebagai contoh, trigger BEFORE untuk INSERT akan diaktifkan
tidak hanya oleh statemen INSERT tetapi juga statemen LOAD DATA.
60
_ tbl_name: Nama tabel yang berasosiasi dengan trigger.
_ trigger_stmt: Statemen (tunggal atau jamak) yang akan dijalankan ketika trigger aktif.
Contoh yang akan dibahas adalah mencatat kejadian-kejadian yang terjadi beserta waktunya
pada tabel mahasiswa, dan catatan-catatan tadi disimpan dalam tabel yang lain, misal
log_mhs. Misalkan struktur tabel log_mhs adalah sebagai berikut.
mysql> describe log_mhs;
Contoh 1: mysql> create trigger ins_mhs after insert on mahasiswa -> for each row insert into log_mhs values('Tambah data',now());
mysql> insert into mahasiswa
values('00631','Hanif','Kalasan','P01');
mysql> select * from log_mhs;
Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa disisipkan
(insert), maka secara otomatis tabel log_mhs akan disisipkan satu record, yaitu kejadian
‘Tambah data’ dan waktu saat record pada tabel mahasiswa disisipkan.
_ Contoh 2 :
mysql> create trigger updt_mhs after update on mahasiswa -> for each row insert into log_mhs values('Ubah data',now());
mysql> update mahasiswa set nama='Moh. Riyan' where nim='00543';
mysql> select * from mahasiswa;
61
mysql> select * from log_mhs;
Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa diperbaharui
(update), maka secara otomatis tabel log_mhs akan disisipkan satu record, yaitu kejadian
‘Ubah data’ dan waktu saat record pada tabel mahasiswa diperbaharui.
_ Contoh 3 : mysql> create trigger del_mhs after delete on mahasiswa
-> for each row insert into log_mhs values('Hapus data',now());
mysql> delete from mahasiswa where nim='00631';
mysql> select * from log_mhs;
Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa dihapus
(delete), maka secara otomatis tabel log_mhs akan disisipkan satu record, yaitu kejadian
‘Hapus data’ dan waktu saat record pada tabel mahasiswa dihapus.
Dalam implementasinya untuk pekerjaan sehari-hari, pembuatan trigger dan tabel log,
digunakan untuk mencatat kejadian suatu tabel yang dianggap rawan serangan cracker.
Dengan struktur trigger yang baik sesuai kebutuhan, administrator dapat melakukan
62
pelacakan dan recovery data dengan cepat karena mengetahui record mana saja yang
“diserang”. Atau, dihubungkan dengan program aplikasi (user interface) agar mengaktifkan
alarm, jika terdapat operasi database pada waktu yang tidak seharusnya (misalkan malam
hari). _ Menampilkan daftar trigger yang telah dibuat: mysql> show triggers;
Keterangan (record pada kolom “statement” dan “sql_mode”): S1 : insert into log_mhs values('Tambah data',now())S2 : insert into log_mhs values('Ubah data',now())S3 : insert into log_mhs values('Hapus data',now())SQL1: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONSQL2: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONSQL3: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Karena hasil eksekusi perintah “show triggers” sangat panjang, tampilan di atas sengaja
diedit dengan tujuan agar mudah dipahami.
Praktik!
1. Dengan menggunakan database: dbperusahaan!
2. Buatlah trigger dengan nama: log_karyawan dengan struktur seperti berikut!
3. Tampilkan struktur log_karyawan!
4. Buatlah trigger tambah record pada tabel tersebut dan tambahkan 2 record berikut ini!
P006 M. ALI SETIADI JL. TJ. SIAPI-API NO.11 J06 1500000
P007 ARMITA SARI JL. MERICA NO.35 J07 1000000
5. Tampilkan seluruh record pada tabel karyawan!
6. Buatlah trigger untuk mengedit record pada tabel dan edit 2 record tadi seperti ini!63
P006 MERI ANDANI JL. SIMPANG 5 NO.70 J06 1500000
P007 SARITA MILI JL. PARAMESWARA NO. 89 J07 1000000
7. Tampilkan seluruh record pada tabel karyawan!
8. Buatlah trigger untuk menghapus record pada tabel tersebut dan hapus 2 record tadi!
9. Tampilkan seluruh record pada tabel karyawan!
10. Tampilkan record pada trigger log_karyawan!
11. Tampilkan seluruh trigger yang telah dibuat!
64