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.
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.
1
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 StartÆProgramÆOralce-OraHome81Æ Aplication DevelopmentÆSQL PLUS. Akan muncul dialog SQL plus sebagai berikut :
2
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 3
select * from user_free_space;
2.
Membuat tablespace SQL>Create tablespace nama-tablespace datafile ‘nama-datafile.dat’ size 1M autoextend 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 user01 Enter 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). 4
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
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.
6
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.
2.
Tabel krs, dengan field : nim varchar(5), kdmtk varchar(5), nilai char(1).
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 where 2 mahasiswa.nim=krs.nim and 3 krs.kdmtk=matakuliah.kdmtk and 4 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 where 2 matakuliah.kdmtk=krs.kdmtk
7
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.
8
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 mahasiswa 3 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
9
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_nim 2 foreign key (nim) references mahasiswa (nim) 3 on delete cascade;
10
SQL> alter table krs add constraint sk_kdmtk 2 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, matakuliah where mahasiswa.nim=krs.nim and matakuliah.kdmtk=krs.kdmtk group by mahasiswa.nama having count(sks)>2;
CATATAN : Untuk tugas mingguan akan diberikan pada saat praktikum.
11
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. 12
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. DECLARE a NUMBER := 3; BEGIN a := 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. 13
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; BEGIN SELECT 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 : DECLARE vnim varchar2(5) := 1; BEGIN select nama from mahasiswa where 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. 14
DECLARE vnim varchar2(5) := 1; vnama mahasiswa.nama%type; BEGIN select nama into vnama from mahasiswa where 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 : DECLARE a NUMBER; b NUMBER; BEGIN SELECT 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
15
END LOOP
Untuk menghentikan proses loop dipergunakan perintah EXIT dengan sintaks : EXIT [When kondisi];
Berikut ini contoh penggunaan LOOP : DECLARE i NUMBER := 1; BEGIN LOOP 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 LOOP urutan_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. DECLARE i NUMBER := 1; vhitung number; BEGIN WHILE 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 : 16
FOR penghitung IN [REVERSE] batas_bawah..batas_atas LOOP urutan_perintah; END LOOP;
Berikut ini contoh dari Numeric FOR Loops : Declare vhit number; BEGIN FOR 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 20000 DECLARE vnim varchar2(5) := 12345; vnama mahasiswa.nama%type; vkomen char(25); BEGIN select nama into vnama from mahasiswa where nim=vnim; exception when no_data_found then vkomen := 'Data tidak ada'; dbms_output.put_line(vkomen); END; /
17
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 DECLARE vnama matakuliah.nm_mtk%type; vsks matakuliah.sks%type; e_jml_sks exception; vkomentar char(25); BEGIN select nm_mtk, sks into vnama, vsks from matakuliah where kdmtk = 'mt01'; if vsks < 4 then RAISE e_jml_sks; end if; EXCEPTION when e_jml_sks then vkomentar := 'Bobot SKS dibawah 4'; dbms_output.put_line(vkomentar); end; /
CATATAN : Untuk tugas mingguan akan diberikan pada saat praktikum.
18
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 : BEGIN Tambah_Mahasiswa('11','Banu','Sleman'); END; /
19
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 BEGIN b := a * 4; INSERT INTO T1 VALUES(a, b); END; /
Jalankan script tersebut (procedure AddT1) dan cek dengan script berikut : DECLARE v number; BEGIN addt1(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 is vsks matakuliah.sks%type; Begin select count(sks) into vsks from krs, matakuliah where krs.nim = vnim and krs.kdmtk=matakuliah.kdmtk;
20
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.
21
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 TRIGUBAH BEFORE DELETE OR INSERT OR UPDATE ON MAHASISWA BEGIN insert into tlog values (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 field user, tanggal perubahan dan komentar. Ubah script trigger diatas menjadi script trigger berikut ini : CREATE OR REPLACE TRIGGER TRIGUBAH BEFORE DELETE OR INSERT OR UPDATE ON MAHASISWA BEGIN If deleting then insert into tlog values (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Data pada tabel dihapus'); elsif inserting then insert into tlog values (user, to_char(sysdate, 'Month DY, DD, HH24:MI'),'Data pada tabel ditambah'); elsif updating then insert into tlog values (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.
22
