oracle.pl

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 k lik Next.

Upload: chen-yuan-mei

Post on 17-Jul-2015

109 views

Category:

Documents


0 download

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.