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