oracle.pl
TRANSCRIPT
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 1/23
BAB X
ORACLE
MODUL I
SQL *PLUS
SQL *PLUS dipergunakan untuk mengakses Oracle Database Server. Untuk
mengakses server dibuat sebuah service yang mengarahkan client ke server
terlebih dahulu ( Database Alias). Tahapan untuk membuat service tersebut
sebagai berikut :
1. Pada dialog diatas diberi service name TPRAKTIKUM. Klik next
2. Pilih protocol yang menghubungkan antara client dengan server. Pada
kesempatan ini untuk menghunbungkan client ke server mempergunakan
protocol TCP/IP. Berikutnya klik Next.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 2/23
1
3. Pada field Host Name masukkan nama host name atau alamat IP address
server ORACLE, misalnya nt5. Klik Next
4. Pada menu field Database SID, pada layar System IDentifier, masukkan
nama database SID, misalnya lab4. Berikunya klik Next .
5. Untuk mencoba service name yang sudah dibuat, perlu dilakukan testing.
Klik tombol Test Service, dan akan menuju layar testing.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 3/23
2
6. Pada layar Conection Test masukkan username dan password. Silahkan
masukkan Username scott dan Password tiger, klik tombol test, maka akan
ditampilkan respon server. Jika sukses akan ada komentar ….successful..
jika gagal akan muncul komentar bahwa service yang dibuat gagal.
Setelah Service Name dibuat, kini masuk kedalam SQL *PLUS untuk mengakses
database server.Buka SQL *PLUS dari menu StartProgramOralce-OraHome81
Aplication DevelopmentSQL PLUS.
Akan muncul dialog SQL plus sebagai berikut :
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 4/23
3
Masukkan pada User Name system, password manager dan Host String dengan
service name yang sudah dibuat tadi (TPRAKTIKUM).
Jika Login berhasil maka akan muncul Oracle SQL *PLUS sebagai berikut :
TABLESPACE
Untuk menyimpan obyek-obyek database dibutuhkan suatu ruang memori yang
disebut TABLESPACE. Untuk mengakses tablespace dibutuhkan sebuah account
user. Berikut ini perintah SQL untuk membuat tablespace, user dan perintah untuk
manipulasi tabel.
1. Melihat tablespace
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 5/23
4
select * from user_free_space;
2. Membuat tablespace
SQL>Create tablespace nama-tablespace datafile ‘nama-datafile.dat’ size 1Mautoextend on next 500K maxsize 2M;
3. Mengubah ukuran tablespace
SQL>alter database datafile ‘nama-datafile.dat’ resize 2M;
4. Menambah ukuran tablespace dengan cara menambahkan data file baru
SQL>alter tablespace dataku add datafile ‘nama-datafile.dat’ size 2M;
5. Menghapus table space
SQL>drop tablespace nama-tablespace including contents;
Setelah Tablespace siap, maka langkah selanjutnya membuat account baru.
Account ini akan anda pergunakan selama praktikum. Account yang sudah ada
ketika instalasi selesai adalah SYS, SYSTEM dan SCOTT. Untuk membuat
account baru kita mempergunakan user SYS dan SYSTEM. Pergunakan
tablespace yang tadi telah anda buat untuk menyimpan data anda selama
praktikum.
1. Membuat user baru dan dengan tablespace yang dipakai misalnya dtpraktek.
SQL>create user nama_user identified by password-user default
tablespace dtpraktek;
Parameter nama_user adalah nama login, dan password-user adalah
password user yang dipilih. Misalnya dipilih untuk nama_user user01.
2. Berikan hak akses user pada database. Misalnya hak akses grant dan
resource.
SQL> grant connect to user01;SQL> grant resource to user01;
3. Langkah berikutnya, lakukan sambungan dengan server dengan
mempergunakan user_name yang sudah dibuat tadi.
SQL> connect user01Enter password: ******Connected.
4. Saat ini sudah tersambung dengan server dengan mempergunakan user01.
Buat tabel mahasiswa yang memiliki struktur nim varchar(5), nama
varchar(25), alamat varcahr(30).
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 6/23
5
SQL> create table mahasiswa (nim varchar(5), nama varchar(25), alamat
varchar(30));
5. Untuk melihat tabel yang ada dalam tablespace mempergunakan perintah :
SQL> select * from tab;
6. Untuk melihat struktur tabel dapat dilakukan dengan mempergunakan
perintah :
SQL>describe mahasiswa;
7. Perintah alter berguna untuk melakukan perubahan tabel diantaranya
merubah tipe field, menambah field baru dan merubah nama tabel.
Merubah tipe field tabel :
SQL>alter table mahasiswa modify (nim number(5));
Menambah field tabel :
SQL>alter table mahasiswa add (usia number(2));
Mengganti nama tabel :
SQL>alter table mahasiswa rename to mhs;
8. Untuk menambahkan data kedalam tabel, pergunakan perintah insert
SQL>insert into mahasiswa values(1,’Sumanto’,’Sleman’);
9. Data yang sudah disimpan dalam tabel dapat diedit dengan
memperguanakan perintah update.
SQL>update mahasiswa set nama = ‘Sumanto’ where nim=1;
10. Untuk keperluan penghapusan data, pergunakan perintah :
SQL>delete from mahasiswa where nim=1;
CONSTRAINT
Constraint (konstrain) adalah aturan yang diberikan pada suatu tabel supaya data
yang dimasukkan lebih terjamin validitasnya.
1. Memberi konstrain pada saat membuat tabel :
SQL>create table mahasiswa(nim char(5) constraint pnim primary key, nama
char(20) constraint nnama not null, alamat char(30));
2. Memberi konstrain pada tabel yang sudah ada :
SQL>alter table mahasiswa add constraint pnim primary key (nim);SQL>alter table mahasiswa modify (nama char(20) constraint nnama not null);
3. Menghapus konstrain :
SQL>alter table mahasiswa drop constraint nnama;
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 7/23
6
ORDER BY
Klausa ORDER BY digunakan untuk mengurutkan data yang di-query. Klausa
ORDER BY diikuti dengan nama kolom atau kolom alias kemudian diikuti klausa
jenis urutan yang kita inginkan. Jenis urutan bisa ASC (ascending) atau DESC
(descending). Sebagai ilustrasi, tampilkan semua data dengan cara diurutkan
ascending berdasarkan nama mahasiswa.
SQL> select * from mahasiswa order by nama;SQL> select * from mahasiswa order by nama desc;
WHERE
Klausa LIKE WHERE digunakan untuk membatasi query menurut kriteria
tertentu. Kriteria ini didefinisikan menggunakan bentuk perbandingan. Sebagai
ilustrasi, tampilkan data mahasiswa yang berasal dari Jogjakarta.
SQL> select * from mahasiswa where alamat = ‘Jogjakarta’;
LIKE
Klausa LIKE digunakan untuk membatasi query menurut nilai tertentu. Nilai ini
merupakan sebagian dari nilai suatu field. Sebagai ilustrasi, akan ditampilkan data
mahasiswa yang memiliki nama yang nama akhirnya mengandung huruf “ni”.
SQL> select * from mahasiswa where nama like ‘%ni’;
TUGAS MINGGU INI
1. Tampilkan data mahasiswa yang tersimpan dalam tabel mahasiswa.
2. Tampilkan data mahasiswa yang berusia 20 tahun.
3. Tampilkan data mahasiswa yang berasal dari Jogjakarta.
4. Tampilkan data mahasiswa yang namanya mengandung nilai “ni”
dibelakang (disesuaikan dengan yang telah dimasukkan) dan berasal dari
Jakarta.5. Tampilkan jumlah mahasiswa berdasarkan alamat.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 8/23
7
MODUL II
SQL *PLUS LANJUTAN
Pada pertemuan minggu kedua, akan dibahas tentang bagaimana
menampilkan data dari beberapa tabel yang terhubung oleh suatu kunci. Buat
sambungan ke server ORACLE dengan SQL *PLUS seperti minggu lalu, dan
ikuti latihan berikut :
1. Buat tabel matakuliah dan tabel krs dengan tipe field :
Tabel matakuliah terdiri dari field : kdmtk varchar(5), nm_mtk
varchar(20), sks number, dan field kdmtk tidak boleh null.
Tabel krs, dengan field : nim varchar(5), kdmtk varchar(5), nilai char(1).
2. Isi tabel matakuliah dan krs, dengan nomor mahasiswa sesuai dengan nomor
mahasiswa pada tabel krs sesuai dengan yang ada di tabel mahasiswa dan
kdmtk pada tabel krs sama dengan kdmtk pada tabel matakuliah. Kosongi
field nilai pada tabel krs.
3. Tampilkan nama mahasiswa yang mengambil matakuliah yang memiliki
kode matakuliah ‘mt01’;
SQL> select nama from mahasiswa, krs where mahasiswa.nim=krs.nim and
krs.kdmtk='mt01';4. Tampilkan nama matakuliah dan sks dari matakuliah yang diambil
mahasiswa yang memiliki nomor mahasiswa ‘1’
SQL> select nm_mtk, sks from matakuliah, krs where matakuliah.kdmtk=krs.kdmtk
and krs.nim='1';
5. Tampilkan nama mahasiswa yang mengambil matakuliah ‘Bahasa C’
SQL> select nama from mahasiswa, matakuliah, krs where2 mahasiswa.nim=krs.nim and3 krs.kdmtk=matakuliah.kdmtk and4 matakuliah.nm_mtk='Bahasa C';
GROUP BY
Klausa GROUP BY digunakan untuk mengelompokkan data hasil perintah group
function. Ilustrasi pemakaian GROUP BY :
SQL> select matakuliah.nm_mtk, count(krs.kdmtk) from matakuliah, krs where2 matakuliah.kdmtk=krs.kdmtk
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 9/23
8
3 group by matakuliah.nm_mtk;
HAVING
Klausa HAVING dipergunakan untuk membatasi hasil query yang dihasilkan dari
klausa GROUP BY.
SQL> select nama, count(sks) from mahasiswa, krs, matakuliah
2 where mahasiswa.nim=krs.nim and
3 matakuliah.kdmtk=krs.kdmtk
4 group by mahasiswa.nama
5 having count(sks)>2;
SUBQUERY
Subquery adalah query dalam query. Dalam menuliskan perintah select, kita dapat
mendefinisikan nilai kondisional dengan menggunakan perintah SELECT lagi.
Artinya ada perintah SELECT dalam perintah SELECT. Untuk ilustrasi,
tambahkan data mahasiswa yang nomor mahasiswanya (nim) tidak ada di tabel
krs, dan berikutnya coba perintah berikut :
SQL> select nama from mahasiswa where nim in
2 (select nim from krs group by nim);
TUGAS MINGGU INI
1. Tampilkan nama matakuliah dan sks terbesar.
2. Tampilkan nama matakuliah dan sks terkecil.
3. Tampilkan nama mahasiswa dan jumlah sks terbanyak yang diambil
mahasiswa.
4. Tampilkan nama mahasiswa dan jumlah sks paling sedikit yang diambil
mahasiswa.
5. Tampilkan nama matakuliah dan jumlah mahasiswa yang mengambil
matakuliah paling banyak.
6. Tampilkan nama matakuliah dan jumlah mahasiswa yang mengambil
matakuliah paling sedikit.
Catatan : Nomor 1, 3 dan 5, pergunakan fungsi MAX dan nomor 2, 4 dan 6,
pergunakan fungsi MIN.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 10/23
9
MODUL III
VIEW, CONSTRAINT LANJUT
dan SQL DUMP
VIEW
View merupakan objek database seperti tabel namun secara fisik tidak memiliki
data. Isi data view merupakan hasil query dari sebuah atau beberapa tabel. View
disebut juga virtual tabel karena strukturnya seperti tabel tapi tidak memiliki data.
1. Untuk membuat view dengan perintah CREATE VIEW seperti contoh
berikut :
SQL> create view mhs_jogja as (2 select * from mahasiswa3 where alamat like 'J%');
View created.
2. Untuk melihat view yang telah dibuat, diberikan perintah untuk melihat
view sebagaimana perintah untuk melihat data dalam tabel.
SQL> select * from mhs_joigja;
3. Struktur field view dapat dilihat dengan mempergunakan perintah :
SQL> describe mhs_jogja;
4. Data yang ditampilkan dalam view dapat diupdate, dengan catatan tidak
terdapat constraint yang menolak perubahan data. Sebagai contoh, untuk
merubah data dalam view dipergunakan perintah :
SQL> update mhs_jogja
2 set nama = 'Abdullah'
3 where nim = '1';
1 row updated.
5. Jalankan perintah berikut untuk melihat perubahan yang terjadi akibat perintah diatas.
SQL> select * from mhs_jogja;
6. Untuk kepentingan merubah definisi view dapat dipergunakan perintah
berikut :
SQL> create or replace view mhs_jogja as
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 11/23
10
2 (select * from mahasiswa);
7. View dapat dihapus dengan perintah DROP
SQL> drop view jml_sks;
Untuk latihan buatlah view untuk tugas minggu kemarin nomor 1,3,5 atau 2,4,6.
Simpan tugas ini sebagai salah satu tugas minggu ini.
CONSTRAINT LANJUT
Pada minggu pertama sudah dibahas sekilas tentang konstraint. Kali ini akan
dibahas lebih lanjut tentang constraint. Ada lima jenis constraint yaitu PRIMARY
KEY, UNIQUE, FOREIGN KEY, CHECK dan NOT NULL. Pada tablespace
sudah terdapat tiga tabel yaitu tabel mahasiswa, tabel matakuliah dan tabel krs.
Ketiga tabel tersebut mempunyai relasi sebagai berikut :
Tabel KRS mempunyai ketergantungan pada tabel mahasiswa dan tabel
matakuliah. Sehingga field NIM yang ada di tabel KRS harus terdapat juga pada
tabel mahasiswa dan field KDMTK pada tabel KRS juga harus terdapat pada tabel
matakuliah. Sebelum ditambahkan constraint pada tabel krs, cobalah untuk
menambahkan data pada krs, dimana nim yang dimasukkan tidak terdapat pada
tabel mahasiswa dan kdmtk juga tidak terdapat pada tabel matakuliah.
Untuk menambahkan Integrity Constraint, tambahkan constraint pada field nim di
tabel mahsiswa (jika belum ada) dan field kdmtk pada tabel matakuliah (jika
belum ada). Untuk menambahkan constraint pada tabel-tabel tersebut, pergunakan
perintah :
SQL> alter table krs add constraint sk_nim2 foreign key (nim) references mahasiswa (nim)3 on delete cascade;
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 12/23
11
SQL> alter table krs add constraint sk_kdmtk2 foreign key (kdmtk) references matakuliah (kdmtk)3 on delete cascade;
Setelah ditambahkan constraint pada tabel krs, cobalah untuk menambah data
yang nim-nya tidak terdapat pada tabel mahasiswa dan kdmtk-nya tidak terdapat
pada tabel matakuliah. Apa yang terjadi? Tambahkan komentar anda pada tugas
minggu ini.
SQL DUMP
Pemakaian bahasa SQL dengan mengetikkan tiap baris pada SQL *PLUS akan
menimbulkan kesulitan jika perintah yang diberikan cukup panjang. Perintah SQL
dapat di buat dalam file teks dengan mempergunakan editor apapun (notepad, edit,
wordpad dan lain sebagainya). Pemakaian SQL pada file eksternal ini sama
dengan SQL DUMP yang terdapat pada MySQL. Untuk pemakaian SQL DUMP,
ikuti instruksi berikut :
1. Buat direktori script pada c:\oracle untuk menyimpan script SQL yang
anda buat pada praktikum minggu ini dan minggu depan.
2. Buka notepad dan ketikkan perintah SQL yang anda kehendaki dan simpan
file dengan akhiran *.sql (script1.sql) sebagai contoh :
SQL> select table_name from user_tables;
3. Dari prompt SQL *PLUS ketikkan perintah untuk mengeksekusi file
tersebut dengan disertakan path directori.
SQL> @c:\oracle\script\script1.sql
4. Buat script baru, dan isikan perintah SQL berikut :
select nama, count(sks) from mahasiswa, krs, matakuliahwhere mahasiswa.nim=krs.nim and
matakuliah.kdmtk=krs.kdmtkgroup by mahasiswa.namahaving count(sks)>2;
CATATAN : Untuk tugas mingguan akan diberikan pada saat praktikum.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 13/23
12
MODUL IV
PL/SQL
PL/SQL singkatan dari Procedural Language/Structured Query Language.
PL/SQL menambahkan SQL dengan bahasa-bahasa prosedural. Dengan SQL,
pekerjaan manipulasi data lebih mudah, namun akan lebih maksimal jika
ditambahkan dengan bahasa prosedural. Sebagai contoh, pemanfaatan fungsi
control IF-THEN-ELSE atau looping. Hal ini tidak bisa dilakukan dengan
mempergunakan SQL murni. Untuk hal inilah dibutuhkan PL/SQL, bahasa
pemrograman yang menggabungkan fleksibilitas SQL (bahasa tingkat empat atau
4GL) dengan bahasa prosedural (bahasa tingkat tiga atau 3GL). Bentuk umum
dari suatu PL/SQL adalah :
DECLARE
<declaration section>
BEGIN
<executable section>
exception
<exception handling>
end;
/
Dalam PL/SQL blok penanganan komunikasi dengan database dilakukan dengan
variabel. Variabel adalah suatu alokasi memori untuk menyimpan suatu nilai.
Nilai ini dapat diambil dari database atau dimasukkan ke database. Berikut ini
contoh deklarasi variabel dalam PL/SQL.
Declare
vnama varchar2(12);
vnim number(4) := 3452;
Pada contoh diatas dideklarasikan dua buah variabel yaitu vnama dan vnim
dengan tipe data varchar2 dan number. Variabel vnama tidak memiliki nilai
default dan variabel vnim memiliki nilai default 3452.
Jenis data yang dapat dipergunakan dalam PL/SQL adalah : Number, Char,
Varchar2, Date, Long, Decimal (Real), Integer (Int) dan Boolean.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 14/23
13
Selain dengan cara mendefinisikan tipe data variabel, juga ada cara lain untuk
mendefinisikan tipe data dengan mempergunakan %TYPE. Cara ini memberikan
tipe data variabel dengan tipe field pada tabel. Berikut ini contoh definisi tipe data
dengan mempergunakan %TYPE.
Declare
vnama mahasiswa.nama%type;
vnim mahasiswa.nim%type := 3452;
Struktur suatu tabel dapat diambil dan disimpan dalam suatu variabel array. Untuk
memperoleh struktur tabel, dipergunakan perintah %ROWTYPE.
Declare
vnama mahasiswa.nama%type;
ArMtk mtk%ROWTYPE;
Pada contoh diatas, struktur tabel mtk diambil dengan %ROWTYPE dan
diletakkan dalam array ArMtk.
Untuk lebih jelas tentang PL/SQL, silahkan anda ikuti latihan berikut ini.
1. Buatlah script berikut ini dalam file *.sql pada direktory script seperti
pertemuan terdahulu.
DECLAREa NUMBER := 3;
BEGINa := a + 1;
END;/
Jalankan script ini dari promt sql *plus. Maka pada layar sql *plus akan
dihasilkan keluaran PL/SQL procedure successfully completed. Untuk
menampilkan hasil perhitungan diatas tambahkan perintah
dbms_output.put_line(a);dibawah perintah a := a + 1. Simpan script tersebut
dan jalankan kembali dari prompt SQL *PLUS. Selain itu juga tambahkan
perintah set serveroutput on size 20000 diawal script (diatas declare).
Catatan : perintah dbms_output.put_line(nama_field); dipergunakan untuk
menampilkan isi suatu field dan variabel pada PL/SQL.
Pergunakan perintah ini untuk menampilkan isi field dan
variabel.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 15/23
14
2. Dengan mempergunakan SQL DUMP, buatlah tabel T1 dengan menyalin
script berikut :
CREATE TABLE T1(e INTEGER,f INTEGER
);
DELETE FROM T1;INSERT INTO T1 VALUES(1, 3);INSERT INTO T1 VALUES(2, 4);
Buat script PL/SQL berikut ini, dan berikan komentar dari hasil eksekusi
script ini pada laporan mingguan.
DECLARE
a NUMBER;b NUMBER;
BEGINSELECT e,f INTO a,b FROM T1 WHERE e>1;INSERT INTO T1 VALUES(b,a);
END;/
3. Perintah SQL untuk menampilkan isi tabel tidak dapat dilakukan secara
langsung. Perintah SQL yang dimaksud adalah perintah select [nama_field]
from [nama_tabel]. Untuk mengambil isi tabel dilakukan dengan meletakkan
isi field yang dikehendaki kedalam variabel memori dengan perintah select
[nama_field] into [variabel_memori] from [nama_tabel]. Lebih jelas tentang
hal ini silahkan anda coba script berikut :
DECLAREvnim varchar2(5) := 1;
BEGINselect namafrom mahasiswawhere nim=vnim;
END;/
Bila script diatas dieksekusi maka akan dianggap ada kesalahan, padahal
secara logika sudah benar. Edit script diatas sehingga sama dengan script
berikut, dan berikan komentar anda tentang hal ini pada laporan mingguan.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 16/23
15
DECLAREvnim varchar2(5) := 1;vnama mahasiswa.nama%type;
BEGINselect nama into vnamafrom mahasiswawhere nim=vnim;
END;/
PERINTAH KONDISIONAL
PL/SQL mempunyai perintah kondisional IF-THEN-ELSE. Sintaksnya adalah
sebagai berikut :
IF ekspresi_boolean1 THEN urutan_perintah1;
[ELSIF ekspresi_boolean2 THEN urutan_perintah2;]
…[ELSE
urutan_perintah3;END IF];
Lebih jelas mengenai pemakaian If adalah sebagai berikut :
DECLAREa NUMBER;b NUMBER;
BEGINSELECT e,f INTO a,b FROM T1 WHERE e>1;IF b=1 THEN
INSERT INTO T1 VALUES(b,a);ELSE
INSERT INTO T1 VALUES(b+10,a+10);END IF;
END;/
LOOP
PL/SQL mendukung pengeksekusian pertah berulang menggunakan loop. Jenis
loop yang terdapat dalam PL/SQL adalah :
1. Simple Loops
Sintaks simple loop adalah :
LOOP
Urutan_perintah
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 17/23
16
END LOOP
Untuk menghentikan proses loop dipergunakan perintah EXIT dengan
sintaks :EXIT [When kondisi];
Berikut ini contoh penggunaan LOOP :
DECLAREi NUMBER := 1;
BEGINLOOP
INSERT INTO T1 VALUES(i,i);i := i+1;EXIT WHEN i>100;
END LOOP;
END;/
2. While Loops
Sintaks dari While Loop adalah :
WHILE kondisi LOOPurutan_perintah
END LOOP;
Jika kondisi bernilai TRUE, urutan_perintah akan terus dieksekusi. Dalam
loop ini perintah EXIT dapat digunakan jika dibutuhkan. Berikut ini contoh
penggunaan while loop.
DECLAREi NUMBER := 1;vhitung number;
BEGINWHILE i <= 20 LOOP
vhitung := i ** 2;INSERT INTO T1 VALUES(i, vhitung);vhitung := 0;i := i+1;
END LOOP;
END;/
3. Numeric FOR Loops
Sintaks dari Numeric FOR Loops adalah :
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 18/23
17
FOR penghitung IN [REVERSE] batas_bawah..batas_atas LOOPurutan_perintah;
END LOOP;
Berikut ini contoh dari Numeric FOR Loops :
Declarevhit number;
BEGINFOR vhitung IN 1..20 LOOP
vhit := vhitung ** 3;Insert into t1 values (vhitung, vhit);
END LOOP;END;/
Menangani ERROR
Penanganan error dalam PL/SQL lebih mudah dengan adanya bagian
EXCEPTION dalam blok PL/SQL. Tidak seperti pemrograman dalam bahasa
pemrograman lain seperti C yang harus menangkap error dengan mempergunakan
perintah if . EXCEPTION dibagi dalam dua jenis yaitu :
1. Predefined Exception
Merupakan kondisi exception yang telah didefinisikan dalam oracle,
diantaranya adalah : NO_DATA_FOUND, DUP_VAL_ON_INDEX,
TOO_MANY_ROWS, INVALID_NUMBER, ZERO_DEVIDE dan
PROGRAM_ERROR. Berikut ini contoh predefined exception.
set serveroutput on size 20000DECLARE
vnim varchar2(5) := 12345;vnama mahasiswa.nama%type;vkomen char(25);
BEGINselect nama into vnamafrom mahasiswawhere nim=vnim;
exceptionwhen no_data_found then
vkomen := 'Data tidak ada';dbms_output.put_line(vkomen);
END;/
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 19/23
18
2. User-Defined Exception
User-Defined Exception merupakan penanganan error yang didefinisikan
oleh programmer dalam suatu script. Variabel exception dideklarasikan dan
kemudian dalam program yang dapat dieksekusi, kondisi exception
ditangkap dengan mempergunakan perintah if dan menyerahkannya ke
variabel exception dengan perintah RAISE. Berikut ini contoh user-defined
exception.
set serveroutput on size 20000
DECLAREvnama matakuliah.nm_mtk%type;vsks matakuliah.sks%type;
e_jml_sks exception;vkomentar char(25);
BEGINselect nm_mtk, sks into vnama, vsks from matakuliahwhere kdmtk = 'mt01';
if vsks < 4 thenRAISE e_jml_sks;
end if;
EXCEPTIONwhen e_jml_sks then
vkomentar := 'Bobot SKS dibawah 4';
dbms_output.put_line(vkomentar);end;/
CATATAN : Untuk tugas mingguan akan diberikan pada saat praktikum.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 20/23
19
MODUL V
PROCEDURE, FUNCTION DAN TRIGGER
Procedure, function dan Trigger termasuk named PL/SQL block. Artinya blok
PL/SQL ini memiliki nama. Seperti layaknya Procedure dan Function pada bahasa
pemrograman yang sudah dipelajari (Visual basic, Delphi, Bahasa C dan lainnya).
Procedure
Procedure merupakan blok yang memiliki nama. Pemanggilan procedure
dilakukan melalui blok PL/SQL lain. Penggunaan procedure merupakan fasilitas
yang paling sering digunakan dalam membuat kontrol program di database
Oracle. Procedure memiliki struktur yang mudah dipahami, terletak dalam
database, dan mudah dipanggil untuk dieksekusi. Seperti dalam PL/SQL yang
telah dipelajari pada nagian lalu, hanya saja untuk bagian DECLARE diganti
dengan CREATE PROCEDURE. Cobalah prosedur berikut ini.
CREATE OR REPLACE PROCEDURE Tambah_Mahasiswa(
vnim mahasiswa.nim%type,
vnama mahasiswa.nama%type,
valamat mahasiswa.alamat%type) as
BEGIN
INSERT INTO mahasiswa (nim, nama, alamat)
VALUES(vnim, vnama,valamat);
END Tambah_Mahasiswa;
/
Procedure diatas berfungsi untuk menambah data pada tabel mahasiswa. Nama
prosedur adalah Tambah_Mahasiswa. Untuk penambahan data dapat dilakukan
dengan mempergunakan pemanggilan procedure diikuti parameter. Sehingga tidak
mempergunakan perintah Insert into …. Penambahan data dengan
mempergunakan procedure tersebut dapat dilakukan dengan perintah berikut :
BEGINTambah_Mahasiswa('11','Banu','Sleman');
END;/
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 21/23
20
Perintah tersebut akan memanggil procedure Tambah_Mahasiswa dengan
membawa parameter 11, Banu, Sleman. Penambahan data mahasiswa dilakukan
dengan perintah Insert into …. yang terdapat pada procedure Tambah_Mahasiswa.
Berikut ini script procedure yang agak berbeda dengan script diatas, dimana salah
satu variabel yang dimasukkan diproses terlebih dahulu (variabel b).
CREATE OR REPLACE PROCEDURE AddT1(a NUMBER, b OUT NUMBER) AS
BEGINb := a * 4;INSERT INTO T1 VALUES(a, b);
END;/
Jalankan script tersebut (procedure AddT1) dan cek dengan script berikut :
DECLAREv number;
BEGINaddt1(10,v);
END;/
Script diatas menambahkan data kedalam tabel T1 dengan nilai field pertama 10
dan nilai field kedua (v) dari hasil perhitungan dalam procedure.
FUNCTION
Function merupakan PL/SQL blok yang mirip dengan procedure. Perbedaannya
adalah function mengembalikan suatu nilai dan dipanggil melalui suatu ekspresi.
Untuk membuat function, diawali dengan CREATE OR REPLACE FUNCTION.
Sama seperti membuat procedure, kalimat OR REPLACE digunakan untuk
menimpa function yang sudah ada jika namanya sama. Berikut ini contoh
function.
CREATE OR REPLACE FUNCTION HitSks(vnim krs.nim%type)
RETURN matakuliah.sks%type isvsks matakuliah.sks%type;
Beginselect count(sks) into vsks from krs, matakuliahwhere krs.nim = vnim and krs.kdmtk=matakuliah.kdmtk;
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 22/23
21
RETURN vsks;END HitSks;/
Dari prompt SQL *PLUS eksekusi script function diatas. Fungsi HitSks dapat
dipergunakan dengan pemanggilan fungsi dengan perintah berikut ini pada prompt
SQL *PLUS :
SQL> select nama, hitsks(nim) from mahasiswa where hitsks(nim)>2;
Pemanggilan fungsi dapat dilakukan di dalam suatu blok procedure. Semua nilai
yang dikembalikan oleh function tersebut dapat dipergunakan untuk input suatu
proses dalam suatu ekspresi didalam procedure. Justru kenyataanya, function
banyak dipergunakan didalam blok procedure.
TRIGGER
Trigger merupakan PL/SQL blok yang mirip dengan procedure. Jika procedure
dijalankan secara eksplisit lewat pemanggilan procedure, trigger dijalankan secara
implisit lewat ‘pemicu’. Pemicu yang dimaksud adalah Data Manipulation
Language (DML) yang terdiri atas perintah INSERT, UPDATE atau DELETE
dari suatu tabel database. Trigger bermanfaat untuk :
• Mengatur integrity constraint yang kompleks yang tidak mungkin ditangani
oleh sintaks-sintaks pembuatan tabel.
• Audit database dengan cara memasukkan informasi perubahan dan siapa yang
mengubah saat ada user yang mengubah data.
• Secara otomatis memberi sinyal program lain untuk melakukan sesuatu jika isi
tabel diubah.
Trigger dapat didefinisikan untuk operasi INSERT, UPDATE dan DELETE.
Trigger ini dapat dipicu sebelum (BEFORE) atau sesudah (AFTER) operasi
berjalan. Trigger juga berlaku untuk tingkat baris (FOR EACH ROW) yang
berlaku untuk setiap baris tabel saat dimanipulasi, atau tingkat seluruh tabel yang
dijalankan untuk seluruh tabel saat trigger itu dijalankan.
5/14/2018 ORACLE.PL - slidepdf.com
http://slidepdf.com/reader/full/oraclepl 23/23
22
Buatlah tabel dengan nama tlog dengan field user varchar(25), tanggal
varchar(30), komentar varchar(35).
Ketikkan script trigger berikut ini dan jalankan di prompt SQL *PLUS.
CREATE OR REPLACE TRIGGER TRIGUBAHBEFORE DELETE OR INSERT OR UPDATE ON MAHASISWA
BEGIN
insert into tlogvalues (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Tabel telah diubah');
END;/
Trigger tersebut bernama TRIGUBAH, yang berguna untuk melakukan
dokumentasi jika tabel mahasiswa diubah. Identitas perubah disimpan dalam fielduser, tanggal perubahan dan komentar. Ubah script trigger diatas menjadi script
trigger berikut ini :
CREATE OR REPLACE TRIGGER TRIGUBAHBEFORE DELETE OR INSERT OR UPDATE ON MAHASISWA
BEGIN
If deleting theninsert into tlogvalues (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Data pada tabel
dihapus');
elsif inserting theninsert into tlogvalues (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Data pada tabel
ditambah');elsif updating then
insert into tlogvalues (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Data pada tabel
diubah');end if;
END;/
Jalankan script trigger tersebut dan cobalah untuk menambah, mengupdate dan
menghapus data pada tabel mahasiswa. Lihat isi tabel tlog. Tambahkan komentar
anda pada laporan tugas mingguan.
CATATAN : Untuk tugas mingguan akan diberikan pada saat praktikum.