I. CONSTRAINT TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • •
Memahami definisi Constraint Dapat Membuat Constraint Dapat Melakukan pemeliharaan Constraint
1.1 Definisi Constraint Constraint adalah batasan atau aturan yang ada pada table. Constraint menceg mencegah ah pengha penghapus pusan an data data dari dari suatu suatu table table yang yang mempun mempunyai yai ke keter terkai kaitan tan dengan dengan table table yang yang lain. lain. Misal Misal terdap terdapat at keterk keterkait aitan an antara antara table table depart departme ment nt dengan table pegawai. Dimana pada table pegawai menyimpan informasi kolom nomer department yang juga terdapat pada table department. Jika baris dengan nomer department '10' akan dihapus dari table department, sedangkan terdapat data pegawai yang bekerja pada department tersebut, maka dengan adanya constraint constraint antara dua table, penghapusa penghapusan n tersebut tersebut tidak bisa dilakukan. dilakukan. Oracle menyediakan beberapa tipe constraint berikut : • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK Sintak penulisan Constraint : CREATE TABLE
[schema.]table (column datatype [DEFAULT
expr] [column_constraint], ...
Contoh penulisan Constraint : CREATE TABLE
employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT
NULL, CONSTRAINT emp_emp_id_pk
1.2 Constraint NOT NULL Suatu kolom yang didefinisikan dengan constraint NOT NULL tidak boleh berisi berisi nilai nilai NULL. NULL. Kolom Kolom yang yang befung befungsi si sebaga sebagaii kunci kunci primer primer (prima (primary ry key) key) otomatis tidak boleh NULL.
Contoh penggunaan Constraint NOT NULL :
1.3 Constraint UNIQUE Constr Constrain aintt UNIQUE UNIQUE mende mendefin finisik isikan an suatu suatu kolom kolom menja menjadi di bersif bersifat at unik. unik. Ilustrasi penggunaan Constraint UNIQUE :
2
Perintah pembuatan tabel untuk ilustrasi diatas :
1.4 Constraint PRIMARY KEY Constraint PRIMARY KEY membentuk key yang unik untuk suatu table. Kolom Kolom yang yang didefi didefinis nisika ikan n sebaga sebagaii PRIMA PRIMARY RY KEY akan akan mengid mengident entifi ifikas kasii suatu suatu baris data menjadi unik. Contoh penggunaan Constraint PRIMARY KEY :
3
Ilustrasi penggunaan Constraint PRIMARY KEY :
1.5 Constraint FOREIGN KEY FOREIGN KEY constraint didefinisikan pada suatu kolom yang ada pada suatu tabel, dimana kolom tersebut juga dimiliki oleh table yang lain sebagai suatu PRIMARY KEY. Ilustrasi penggunaan Constraint FOREIGN KEY :
4
Perintah pembuatan tabel untuk ilustrasi diatas :
1.6 Keyword dari Constraint FOREIGN KEY Berikut ini keyword yang ada pada Constraint FOREIGN KEY :
•
FOREIG FORE IGN N KEY KEY anak REFERE REFERENCE NCES S
•
ON DELE DELETE TE CASC CASCAD ADE E
•
ON DELET DELETE E SET NULL NULL
•
: Mend Mendef efini inisi sika kan n ko kolo lom m yang yang ada ada pada pada tabe tabell : Mengid Mengident entifi ifikas kasii tabel tabel dan kolom kolom dalam dalam tabel tabel induk : Peng Pengha hapu pusa san n bari baris s depe depend nden entt dalam dalam tabe tabell anak pada saat sebuah baris pada tabel induk dihapus : Mengko Mengkonve nversi rsi depend dependent ent foreig foreign n key ke nilai nilai NULL.
1.7 Constraint CHECK Constraint Constraint CHECK digunakan digunakan untuk mendefinisik mendefinisikan an suatu suatu kondisi kondisi yang harus dipenuhi oleh tiap baris data dalam table. Contoh penggunaan :
1.8 Menambahkan suatu Constraint Untuk menambahkan suatu Constraint, digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
5
1.9 Menambahkan Constraint Misal ditambahkan constraint FOREIGN KEY pada tabel EMPLOYEES yang menu menunj njuk ukka kan n bahw bahwa a data data mana manage gerr haru harus s vali valid d atau atau bera berasa sall dari dari tabe tabell EMPLOYEES.
1.10 Menghapus Constraint Untuk Untuk mengh menghapu apus s suatu suatu Constr Constrain aint, t, juga juga diguna digunakan kan perint perintah ah ALTER ALTER TABLE. Cara penulisan : ALTER TABLE table DROP [CONSTRAINT constraint] type (column); Untuk menghapus constraint manager dari tabel EMPLOYEES :
Untu Untuk k meng mengha hapu pus s cons constr train aintt PRIM PRIMAR ARY Y KEY KEY pada pada tabe tabell depa depart rtme ment nts s dan dan menghapus asosiasi constraint FOREIGN KEY pada kolom EMPLOYEES.DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID
1.11 Men-Disable Constraint Untu Untuk k meno menona nakt ktif ifka kan n cons constr trai aint nt digu diguna naka kan n klau klausa sa DISA DISABL BLE. E. Pilih Pilihan an CASCADE digunakan untuk men-disable dependent integrity constraint.
1.12 Meng-Enable Constraint Untuk mengaktifkan kembali constraint, digunakan klausa ENABLE
6
7
1.13 Cascading Constraint Klausa CASCADE CONSTRAINTS digunakan dengan klausa DROP COLUMN. Klau Klausa sa CASC CASCAD ADE E CO CONS NSTR TRAI AINT NTS S meng mengha hapu pus s semu semua a refe refere rent ntia iall inte integr grit ity y constraints yang merefer ke primary dan unique key yang didefinisikan pada kolom yang dihapus.
1.14 Menampilkan Constraint Tabel USER_CONSTRAINT menyimpan informasi tentang constraint. Kolom tipe constraint (constraint_type) dapat berisi : C untuk constraint CHECK, R untuk referential integrity atau FOREIGN KEY, P untuk PRIMARY KEY, U untuk constraint UNIQUE.
1.15 Menampilkan Kolom yang Berasosiasi dengan suatu Constraint Berik Berikut ut ini untuk untuk menam menampilk pilkan an Kolom Kolom yang yang Beras Berasosi osiasi asi denga dengan n suatu suatu nama suatu Constraint yang ada pada view USER_CONS_COLUMN USER_CONS_COLUMNS S
8
1.16 Latihan 1. Tambahkan table-level PRIMARY KEY Constraint pada tabel EMP pada kolom ID. Constraint diberi nama : my_emp_id_pk 2. Buat PRIMARY KEY Constraint pada tabel DEPT dengan menggunakan kolom ID. Constraint diberi nama : my_dpet_id_pk 3. Tambah Tambahkan kan kolom DEPT_ DEPT_ID ID ke dalam dalam tabel tabel EMP. EMP. Tambah Tambahkan kan foreign foreign key refe refere renc nces es pada pada tabe tabell
EMP EMP yang yang mema memast stik ikan an bahw bahwa a
data data pega pegawa waii
dimasukkan berdasarkan nomer department yang ada pada tabel DEPT. 4. Tampilkan Tampilkan constraint-c constraint-constr onstraint aint yang telah ditambahka ditambahkan n dengan dengan melihat melihat pada view USER_CONSTRAINTS. USER_CONSTRAINTS. 5. Tampilk Tampilkan an nama nama object object dan tipeny tipenya a dari dari data data dictio dictionar nary y USER_O USER_OBJ BJECT ECTS S untuk tabel EMP dan DEPT. 6. Modifi Modifikas kasii tabel tabel EMP. EMP. Tambah Tambahkan kan ko kolom lom CO COMMI MMISSI SSION ON dengan dengan tipe tipe data data NUMBER NUMBER(2) (2).. Tambah Tambahkan kan Constr Constrain aintt untuk untuk memas memastik tikan an bahwa bahwa kolom kolom ini harus diisi dengan nilai > 0.
9
II. VIEW TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • Memahami definisi View • Dapat membuat View • Dapat Memanggil data melalui View • Merubah definisi View • Insert, Update, dan Delete data melalui View • Menghapus (drop) view 2.1 Definisi View View adalah lah salah lah satu objec ject database, yang secara logika merepresentasikan sub himpunan dari data yang berasal dari satu atau lebih table. Kegunaan dari view adalah : • Membatasi akses database • Membuat query kompleks secara mudah • Mengijinkan independensi data • Untuk Untuk menamp menampilk ilkan an view view (panda (pandanga ngan) n) data data yang yang berbed berbeda a dari dari data data yang yang sama. Ada Ada 2 (dua (dua)) tipe tipe view view,, yait yaitu u Simp Simple le View View dan dan Comp Comple lex x View View.. Beri Beriku kutt ini ini perbandingan antara Simple View dan Complex View :
2.2 Pembuatan View View View dapa dapatt dibu dibuat at deng dengan an peri perint ntah ah CREA CREATE TE VIEW VIEW.. Subqu Subquer ery y dapa dapatt dicantumkan dalam CREATE VIEW, tapi subquery yang digunakan tidak boleh berisi klausa ORDER BY. Sintak penulisan VIEW :
10
2.3 Memanggil Data dari View Buat uat view iew EMP EMPVU80 VU80 yang ang ber berisi isi detai etaill pegaw gawai yang ang bek bekerja erja di department 80.
Untuk menampilkan struktur dari view digunakan perintah DESCRIBE :
Perintah pembuatan View dapat dengan menggunakan kolom alias :
Untu Untuk k mema memang nggi gill data data dari dari view view,, digun digunak akan an peri perint ntah ah yang yang sama sama sepe sepert rtii memanggil data dari table.
11
2.4 Memodifikasi data View Untuk memodifikasi View digunakan klausa CREATE OR REPLACE VIEW.
2.5 Pembuatan Complex View Berikut ini akan dicontohkan pembuatan Complex View yang berisi fungsi group untuk menampilkan nilai yang berasal dari dua table.
2.6 Aturan untuk membentuk Operasi DML pada View Berikut ini aturan untuk membentuk operasi DML pada View : • Operasi DML dapat dibentuk pada Simple View • Baris data pada View tidak dapat dihapus, jika berisi : o Fungsi Group o Klausa GROUP BY o Keyword DISTINCT • Data pada View tidak bisa dimodifikasi jika berisi : o 3 Kondisi yang sudah disebutkan diatas o Kolom yang didefinisikan oleh suatu ekspresi o Kolom ROWNUM • Pada View tidak bisa ditambahkan data, jika ji ka : o View berisi 5 kondisi yang sudah disebutkan diatas o Terdapat kolom NOT NULL pada base table (table asal darimana view dibuat) yang tidak dipilih oleh View. 2.7 Menggunakan Klausa WITH CHECK OPTION Jika klausa WITH CHECK OPTION digunakan, maka tidak diperbolehka diperbolehkan n terjadi perubahan data pada kolom yang punya relasi ke table yang lain. Misal pada view EMPVU20 kolom deptno punya relasi ke kolom deptno pada table depa depart rtme ment nt,, maka maka peru peruba baha han n data data yang yang dilak dilakuk ukan an pada pada ko kolo lom m ini ini tida tidak k diperbolehkan. 12
2.8 Mengabaikan Operasi DML Klausa Klausa READ READ ONLY ONLY diguna digunakan kan jika jika kita kita ingin ingin menga mengabaik baikan an atau atau tidak tidak mengijinkan semua operasi DML yang dilakukan pada data.
2.9 Menghapus View View View dapa dapatt diha dihapu pus s deng dengan an menggu ngguna nak kan nama_view; Sintak penulisan untuk menghapus view :
perin erinta tah h
DROP
VIEW VIEW
Contoh penghapusan View :
2.10 Inline View Inline view adalah subquery dengan nama alias yang digunakan dalam SQL Statem Statement ent yang yang diguna digunakan kan untuk untuk membu membuat at view. view. Sebuah Sebuah inline inline view view bukan bukan merupakan object dari suatu schema. 2.11 Top-N Analysis Top N-queries adalah query untuk mendapatkan n buah nilai terbesar atau terkec terkecil il dari dari suatu suatu kolom kolom Semisa Semisall : produk produk apa saja saja yang yang penjua penjualan lannya nya 10 terbesar ?
13
2.12 Membentuk Top-N Analysis Sintak penulisan Top-N Analysis :
2.13 Contoh Top-N Analysis Untu Untuk k mena menamp mpil ilka kan n nama nama dan dan sala salary ry dari dari pega pegawa waii yang yang mem memilik ilikii penghasilan tiga besar ?
14
2.14 Latihan 1. Buat Buat view view EMP_VU EMP_VU yang berisi berisi nomer pegawai, pegawai, nama nama pegawa pegawai, i, nomer nomer department yang berasal dari table pegawai. Ubah judul kolom nama pegawai menjadi PEGAWAI. 2. Tampilkan view EMP_VU
3. Tampilkan nama view dan teks-nya dari data dictionary USER_VIEWS
4. Buat view dengan dengan nama DEPT20 DEPT20 yang berisi nomer, nomer, nama dan gaji dari pegawai yang bekerja di department 20. Beri judul kolom EMPLOYEE_ID, EMPLOYEE, EMPLOYEE, dan DEPARTMEN DEPARTMENT_ID T_ID.. Jangan Jangan perbolehka perbolehkan n pegawai pegawai untuk mendaftar kembali (atau mengisi datanya lagi) ke department yang lain melalui view. 5. Tampilkan struktur dari view DEPT20.
6. Buat view SALARY_VU SALARY_VU yang berisi nama pegawai, pegawai, nama department, department, gaji dan dan grad grade e dari dari gaji gaji untu untuk k semu semua a pega pegawa wai. i. Beri Beri judu judull PEGA PEGAWA WAI, I, DEPARTMENT, GAJI, JOB_TITLE. Tampilkan data pada SALARY_VU.
15
III. PL/SQL I TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • •
Mengenal blok PL/SQL beserta bagiannya Memahami variable dalam PL/SQL Dapat mendeklarasikan dan menggunakan variable pada PL/SQL
3.1 Struktur Blok PL/SQL PL/SQL merupakan block -structured language, dalam arti : program yang ditulis dengan PL/SQL dapat dibagi-bagi ke dalam blok logika. Blok dalam PL/SQL terdiri dari bagian-bagian, sebagai berikut : 1. Deklarasi 2. Bagian yang dapat dijalankan (executable) 3. Bagian penanganan kesalahan (exception handling) Bagian deklarasi dan penanganan kesalahan sifatnya optional (bisa ditulis, bisa tidak ditulis).Secara umum, struktur PL/SQL :
DECLARE merupakan bagian deklarasi. Antara BEGIN dan END merupakan bagian yang dapat dijalankan (executable). EXCEPTION merupakan bagian penanganan kesalahan.
16
3.2 Tipe -tipe Block Ada 3 (tiga) tipe block dalam dal am PL/SQL : · Anonymous Format umum :
· Procedure Format umum :
· Function Format umum :
3.3 Konstruksi Program PL/SQL dapat digunakan pada 2 (dua) lingkungan berikut : · Oracle Server (Database Server constructs) · Oracle Development Tools (Tools Construct) Berikut ini bagian-bagian PL/SQL yang dapat mengkonstruksi kedua lingkungan yang berbeda tersebut : · Tools Constructs o Anonymous blocks o Application procedures or functions o Application packages o Application triggers o Object types · Database Server Constructs o Anonymous blocks o Stored procedures or function o Stored packages o Database triggers o Object types 17
3.4 Penggunaan Variabel Variabel dalam PL/SQL dapat digunakan untuk : · Penyimpanan data sementara · Manipulasi nilai yang sudah tersimpan Varibel dapat digunakan untuk proses manipulasi nilai yang sudah tersimpan dalam database Oracle. · Reusability Variabel dapat digunakan kembali dengan melakukan referensi dari statement yang lain. · Memudahkan maintenance Pada penggunaan %TYPE dan %ROWTYPE, deklarasi variable dibuat berdasarkan definisi dari kolom database -> in the next session. 3.5 Penanganan Variabel dalam PL/SQL Variabel dalam PL/SQL dapat digunakan dalam berbagai cara berikut : · Deklarasi dan inisialisasi variable pada bagian deklarasi (DECLARE) · Menandai nila i baru pada bagian yang dapat dijalankan (executable). · Suatu nilai dapat dilewatkan melalui variable sebagai parameter dari suatu sub program · Hasil dari suatu proses dapat ditampilkan di tampilkan melalui variable output. 3.6 Tipe -tipe Variabel Tipe -tipe variable dalam PL/SQL : · Scalar · Composite · Reference · LOB (large object)
18
3.7 Deklarasi Variabel PL/SQL Deklarasi Variabel dalam PL/SQL, mempunyai sintak umum berikut :
Contoh deklarasi variable
3.8 Aturan Penamaan Variabel Variabel diberi nama sesuai dengan aturan, misal v_name merepresentasikan suatu variable, jadi nama dari variable selalu diawali dengan v diikut diikutii dengan dengan unders underscor core e (_) lalu nama nama variab variabeln elnya. ya. Sedang Sedangkan kan c_comm c_comm.. merepr mereprese esenta ntasik sikan an variab variable le ko konst nstant anta, a, jadi jadi nama nama variab variable le konsta konstanta nta selalu selalu diawali dengan c. Dua variable boleh memiliki nama yang sama, asalkan terletak pada blok yang berbeda. Nama variable (identifier) boleh sama dengan nama kolom dari suatu table. Misal pada contoh berikut :
3.9 Inisialisasi Variabel dan Keyword Sintak untuk inisialisasi variable : Identifier := expr; Contoh inisialisasi variable : v_hiredate := ’01-JAN-01’; Inisialisasi variable dapat juga dengan menggunakan keyword DEFAULT . Untuk contoh sebelumnya bisa ditulis, sebagai berikut : v_hiredate DEFAULT ’01-JAN-01’; Jika suatu variable harus berisi suatu nilai (tidak boleh kosong atau NULL), maka digunakan keyword NOT NULL. Contoh : v_city VARCHAR2(30) NOT NULL :=’Oxford’; 3.10 Tipe Data Scalar Variabel yang memiliki tipe data Scalar, memiliki nilai tunggal. Tipe data yang termasuk scalar, yaitu number, character, date, boolean.
19
Contoh deklarasi variable scalar :
3.11 Atribut %TYPE Atribut %TYPE digunakan untuk mendeklarasikan variable yang memiliki tipe data yang sama dengan definisi kolom database.
Contoh penggunaan :
3.12 Tipe Data Komposit dan LOB (Large Object) Tipe data Composite memiliki komponen internal yang dapat dimanipulasi secara secara individual. individual. Tipe data Composite dikenal dengan istilah istilah collection, collection, terdiri dari : TABLE, RECORD, NESTED TABLE, dan VARRAY. Sedangkan tipe data LOB (large objects) dapat digunakan untuk menyimpan data yang tidak terstruktur (misal : teks, grafik, image, video dan suara). LOB juga men-support random access terhadap data. Yang termasuk tipe data LOB : CLOB, BLOB, BFILE, NCLOB. 3.13 Variable Bind Bind Bind varia variable ble meru merupa paka kan n varia variabl ble e yang yang dide didekl klar aras asik ikan an dalam dalam host host environment. Variabel bind dapat digunakan untuk melewatkan suatu nilai yang dijalankan diluar blok PL/SQL. Cara deklarasi bind variable :
Untuk melakukan referensi terhadap bind variable digunakan tanda titik dua (:) Contoh penggunaan bind variable :
20
3.14 DBMS_OUTPUT.PUTLINE Bind variable selain ditampilkan dengan PRINT, juga dapat ditampilkan dalam blok PL/SQL , dengan menggunakan fungsi standart yang disediakan oleh Orac Or acle le yait yaitu u DBMS DBMS_O _OUT UTPU PUT. T.PU PUT_ T_LI LINE NE.. Untu Untuk k meng menggu guna naka kan n fung fungsi si ini, ini, SERVEROUTPUT harus diset ON.
3.15 Ringkasan · Blok dalam PL/SQL terdiri dari bagian-bagian, sebagai berikut : o Deklarasi o Bagian yang dapat dijalankan (executable) o Bagian penanganan kesalahan (exception handling) · Ada 3 (tiga) (tiga) tipe tipe block block dalam dalam PL/SQL PL/SQL,, yaitu yaitu Anonym Anonymous ous,, Proced Procedure ure dan Function · PL/SQL Identifier : o Didefinisikan pada bagian deklarasi o Merupakan tipe data scalar, composite, reference atau LOB o Dapat menggunakan struktur dari variable lain atau object database yang lain · Variabel Variabel yang dideklarasika dideklarasikan n pada lingkungan lingkungan eksternal eksternal semisal semisal SQL*PLUS SQL*PLUS disebut dengan host variable · DBMS_OUTPUT.PUT_LINE dapat digunakan untuk menampilkan data dalam blok PL/SQL
21
3.16 Latihan Soal 1. Evaluasi tiap-tiap deklarasi variable berikut ini. Mana yang legal dan mana yang illegal ! a. DECLARE v_id NUMBER(4); b. DECLARE v_x, v_y, v_z VARCHAR2(10); c. DECLARE v_birthdate DATE NOT NULL; d. DECLARE V_in_stock BOOLEAN := 1; 2. Pada tiap assignment berikut, yang manakah statement yang valid dan mana yang tidak valid,kemudian nyatakan tipe datanya ! a. v_days_to_go := v_due_date – SYSDATE; b. v_sender := USER || ‘: ‘ || TO_CHAR(v_dept_no); TO_CHAR(v_dept_no); c. v_sum := $100,000 + $250,000 d. v_flag := TRUE; e. v_n1 := v_n2 > (2 * v_n3); f. v_value := NULL; 3. Buat anonymous block untuk menampilkan kata ‘I love Oracle’, buat dengan dua cara. Cara pertama menggunakan bind variable dan perintah PRINT untuk menampilkannya. Cara kedua menggunakan DBMS_OUTPUT.PUT_LINE DBMS_OUTPUT.PUT_LINE 4. Buat kode PL/SQL PL/SQL yang mendeklarasik mendeklarasikan an dua variable variable dan masing-ma masing-masing sing nilainya berikut ini: Variabel Value V_CHAR ’42 adalah sebuah angka’ V_NUM dua karakter pertama dari variable V_CHAR yaitu 42
IV. PENULISAN STRUKTUR KONTROL IF-THEN-ELSE-CASE 22
TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • •
Mengidentifikasi penggunaan dan macam-macam tipe struktur kontrol Dapat membuat perintah IF Menggunakan ekspresi CASE
4.1 Mengontrol Alur Eksekusi PL/SQL Alur Alur logi logika ka dari dari stat statem emen en yang yang dija dijala lank nkan an dapa dapatt kita kita ubah ubah deng dengan an menggunakan perintah kondisional IF dan struktur kontrol perulangan Perintah IF macamnya: • IF-THEN-END IF • IF-THEN-ELSE-END IF • IF-THEN-ELSIF-END IF
Gamba 4.1 Ilustrasi Seleksi •
Menggunakan GOTO bagaimanapun tidak disarankan. Penggunaan GOTO digunakan berdasarkan penandaan atau pemberian label pada blok SQL seperti pada contoh berikut. Contoh BEGIN ... <> BEGIN UPDATE employees ... END update_row; ... GOTO update_row; ... END;
23
4.2 Cara Penulisan IF
4.3 Contoh Penggunaan IF 4.4 Penggunaan Simpel IF DECLARE sales NUMBER(8,2) := 10100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END IF; END;
4.5 Penggunaan IF-ELSE Bentuk kedua dari IF menambah kata kunci ELSE yang diikuti dengan sebuah tambahan tambahan alternatif. alternatif. Pernyataan Pernyataan pada ELSE dieksekusi dieksekusi hanya jika kondisinya kondisinya FALSE atau NULL. Pernyataan IF-THEN-ELSE memastikan jika sebuah rangkaian pernyataan atau rangkaian pernyataan lainnya di eksekusi. Pada contoh berikut, UPDATE pernyataan pertama dieksekusi ketika kondisinya TRUE, dan UPDATE pernyataan kedua dieksekusi ketika dalam kondisi FALSE atau NULL. DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END;
4.6 Penggunaan Nested IF 24
DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE IF sales > quota THEN bonus := 50; ELSE bonus := 0; END IF; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END;
4.7 Penggunaan IF-THEN-ELSEIF Terka Terkadan dang g kita kita ingin ingin memilih memilih antara antara bebera beberapa pa altern alternati atif. f. Anda Anda dapat dapat menggu menggunak nakan an kata kata kunci kunci ELSIF ELSIF (not (not ELSEI ELSEIF F or ELSE ELSE IF) untuk untuk memasu memasukka kkan n kondisi tambahan. Jika kondisi pertama adalah FALSE atau NULL, maka ELSIF akan akan mengu menguji ji kondis kondisii lainny lainnya. a. Sebua Sebuah h pernya pernyataa taan n IF bisa bisa memili memiliki ki bebera beberapa pa ESLIF; dengan ELSE akhir yang opsional. Konsisi di evaluasi satu demi satu dari atas ke bawah. Setiap ada kondisi TRUE, rangkaian asosiasi pernyataannya di eksekusi dan control melewati pernyataan berikutnya. Jika semua kondisi adalah FALSE atau NULL, urutan pada ELSE di eksekusi. DECLARE sales NUMBER(8,2) := 20000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END;
25
4.8 Extended IF-THEN DECLARE grade CHAR(1); BEGIN grade := 'B'; IF grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade = 'D' THEN DBMS_OUTPUT. PUT_LINE('Fair'); ELSIF grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END IF; ENd; /
4.9 Ekspresi CASE •
•
Eksp Ekspre resi si CASE CASE meng menguj ujii suat suatu u varia variabl ble e sele select ctor or dian dianta tara ra bebe bebera rapa pa alternative ekspresi Jika salah satu dari ekspresi memberikan nilai TRUE, maka perintah CASE akan mengembalikan hasilnya.
4.10 Cara Penulisan CASE
26
4.11 Penggunaan CASE DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END
4.12 Latihan 1.
Tampilkan
nama
department
dari
employ loyee_id
=
103
dengan
menggunakan IF-ELSEIF dengan ketentuan : DEPARTMENT_I D 10 20 30 40 50 60 70 80 90 100
DEPARTMENT_NA ME Administration Marketing Purchasing Hum Human Res Resourc ource es Shipping IT Publ ublic Relat elatio ions ns 80 Sales Executive Finance
2. Kerjakan kembali soal no 1 dengan menggunakan CASE!
27
V. Penulisan Struktur Kontrol LOOPING TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • •
Membuat dan mengidentifikasi macam-macam perintah perulangan Kontrol blok menggunakan nested loops dan label
5.1 Kontrol Iteratif: Perintah Perintah LOOP Perint Perintah ah Loop Loop (perula (perulanga ngan) n) menge mengerja rjakan kan kumpul kumpulan an perint perintah ah secara secara berulang. Ada tiga macam loop : Basic loop Perulangan tanpa kondisi FOR loop Perulangan Perulangan berdasarkan berdasarkan nilai variable variable counter counter (sudah (sudah diketahui diketahui berapa kali akan dikerjakan) WHILE loop Perulangan berdasarkan suatu kondisi Perintah EXIT digunakan untuk keluar dari Loop • •
•
5.2 Basic Loop Merupa Merupakan kan bentuk bentuk paling paling sederh sederhana ana pada pada Loop. Loop. Baris Baris di dalam dalam tubuh tubuh perulangan akan dikerjakan berulang kali sampai ditemukan perintah EXIT yang dapat menghentikan loop. Bentuk perintah EXIT pada basic Loop adalah EXIT WHEN kondisi; Jika kondisi yang disyaratkan terpenuhi, maka akan keluar dari perulangan. Struktur penulisan
28
Contoh penggunaan basic loop : DECLARE credit_rating NUMBER := 0; BEGIN LOOP credit_rating := credit_rating + 1; IF credit_rating > 3 THEN EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating)); IF credit_rating > 3 THEN RETURN; -- use RETURN not EXIT when o utside a LOOP END IF; DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating)); DECLARE credit_rating NUMBER := 0; BEGIN LOOP credit_rating := credit_rating + 1; EXIT WHEN credit_rating > 3; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating)); IF credit_rating > 3 THEN RETURN; -- use RETURN not EXIT when outside a L OOP END IF; DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
5.3 While Loop Gunaka Gunakan n perint perintah ah WHILE WHILE loop loop untuk untuk mengu mengulan lang g sekump sekumpula ulan n perint perintah ah selama kondisi yang ditentukan bernilai TRUE
29
Contoh :
5.4 For Loop Simpe Simpell for-lo for-loop op melak melakuka ukan n perula perulanga ngan n dengan dengan mengg mengguna unakan kan intege integerr sebagai range. Banyaknya perulangan diketahui sebelum memasuki perulangan. Tanda doble titik (. .) menandakan rentang terjadinya perulangan. Aturan penulisan For-Loop
Contoh : DECLARE p NUMBER := 0; BEGIN FOR k IN 1..500 LOOP -- calculate pi with 500 terms p := p + ( ( (-1) ** (k + 1) ) / ((2 * k) - 1) );
Secara Secara default default perulangan perulangan FOR yang yang dilaku dilakukan kan adalah adalah dari dari batas batas bawah bawah ke bata batas s atas atas,, namu namun n kita kita bisa bisa mengg enggun unka kan n RESERVE untuk untuk melak melakuka ukan n perulangan dari batas bawah ke batas atas.
30
Contoh : BEGIN FOR i IN REVERSE 1..3 LOOP -- assign the values 1,2,3 to i DBMS_OUTPUT.PUT_LINE (TO_CHAR(i)); END LOOP; END;
5.5 Nested Loops and Labels • • •
Loop dapat di-nested untuk beberapa level Gunakan level untuk membedakan blok dan loop Keluar dari outer loop dapat dilakukan dengan perintah EXIT yang merefer ke label.
Cara penulisan :
5.6 Petunjuk Umum Penggunaan Loop •
•
•
Gunakan Gunakan basic loop jika perintah dalam Loop harus dijalankan dijalankan sedikitnya sedikitnya sekali. Gunakan WHILE loop jika setiap kali perulangan ada kondisi yang harus dievaluasi nilainya pada setiap iterasi. Gunakan FOR loop jika jumlah iterasi i terasi diketahui
31
Contoh : DECLARE s PLS_INTEGER := 0; i PLS_INTEGER := 0; j PLS_INTEGER; BEGIN <> LOOP i := i + 1; := 0; <> LOOP j := j + 1; s := s + i * j; -- sum a bunch of products EXIT inner_loop WHEN (j > 5); EXIT outer_loop WHEN ((i * j) > 15); END LOOP inner_loop; END LOOP outer_loop; DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s)); END; /
5.7 Latihan 1. Buat sebuah table dengan nama DEPARTMENTS_NIM(salah satu nim dari kelompok) dengan struktur yang sama dengan table HR.DEPARTMENTS ! 2. Gunaka Gunakan n perula perulanga ngan n untuk untuk memas memasukk ukkan an data data dari dari HR.DEP HR.DEPART ARTMEN MENTS TS dengan department_id = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 ke dalam table yang sudah dibuat pada nomer 1! 3. Buat Buat table table dengan dengan nama nama EMP_D EMP_DETP ETP_NI _NIM M (sala (salah h satu satu nim dari ke kelom lompok pok)) dengan
nama-nama
kolom
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME! 4. Masukkan data pengawai dengan EMPLOYEE_ID = 100, 105, 110, 115, 120 ke dala dalam m tabl table e yang yang suda sudah h dibu dibuat at pada pada nome nomerr 4 deng dengan an meng menggu guna naka kan n WHILE
32
VI. CURSOR TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: Dapat membedakan sebuah implisit dan sebuah explisit cursor Memahami kebutuhan penggunaan cursor Menulis cursor dalam FOR LOOP
• • •
6.1 Definisi Cursor merupakan sebuah nama untuk sebuah area private yang specific dimana dimana inform informasi asi dari dari statem statement ent yang yang spesif spesifik ik dijaga dijaga.. PL/SQ PL/SQL L menggu menggunak nakan an explicit dan implicit cursor. •
•
Impl Implic icit it Curs Cursor or
Explicit Cursor
: didek didekla lara rasi sika kan n untu untuk k semu semua a DML DML dan dan PL/S PL/SQL QL SELE SELECT CT statement : dideklarasikan dan diberi nama oleh programmer
6.2 Mengontrol Explicit Cursor
DECLARE
OPEN
Membuat sebua s ebuah h nama dari area SQL
Mengidentifikasi Active Set
FETC H
Memasukkan baris ke dalam variabel
EMPTY?
Memeriksa ada atau tidaknya baris
C LOSE
Melepaskan Active Set
Mengembalikan ke FETCH jika baris ditemukan
6.3 Mendeklarasikan Explicit Cursor Syntax : CURSOR nama_cursor is • •
Jangan menambahkan klausa INTO dalam mendeklarasikan CURSOR Jika Jika baris baris yang yang dipros diproses es membut membutuhk uhkan an sebua sebuah h urutan urutan yang yang spesif spesifik, ik, gunakan klausa ORDER BY didalam BY didalam query
33
Contoh : DECLARE CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees WHERE salary >
Pada contoh diatas dideklarasikan sebuah cursor dengan nama c1 yang akan menyimpan data employee_id, job_id, salary untuk semua data pada table employees yang salarynya diatas 2000 6.4 Opening Cursor Syntax :
OPEN nama_cursor; Open cursor untuk menjalankan query dan mengidentifikasi active set
•
•
•
Jik Jika a quer query y tidak tidak menge mengemb mbal alik ikan an baris baris data data sama sama seka sekali, li, tida tidak k ada ada exception yang dikeluarkan Gunakan atribut cursor untuk mengetes hasil setelah sebuah FETCH
Contoh : DECLARE CURSOR c1 IS SELECT employee_id, last_name, job_id, alary FROM employees WHERE salary > 2000; BEGIN
6.5 Mengambil Data dari Cursor Syntax : FETCH nama_cursor into • • •
Mengambil nilai dari baris data ke dalam variable Membutuhkan jumlah yang sama dari variable Mencocokk Mencocokkan an masing-mas masing-masing ing variable variable yang bersesua bersesuaian ian sesuai sesuai dengan dengan posisinya
34
Contoh : DECLARE emp_id employees.job_id%TYPE; l_name employees.last_name%TYPE; id_job employees.job_id%TYPE; sal employees.salary%TYPE; CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary > 2000; BEGIN OPEN C1;
6.6 Close Cursor Syntax : CLOSE cursor_name; • • •
Tutup cursor setelah menyelesaikan memproses baris Buka kembali cursor, jika dibutuhkan Jangan mencoba untuk FETCH data dari cursor setelah cursor tersebut ditutup
6.7 Menampilkan Seluruh Data dari Cursor Menggunakan LOOP Sala Salah h satu satu cara cara untu untuk k mena menamp mpil ilka kan n semu semua a data data dari dari curs cursor or dapa dapatt menggunakan loop. Dengan loop akan secara otomatis membaca data mulai dari baris baris pertam pertama a sampai sampai dengan dengan bari bari terakh terakhir. ir. Untuk Untuk ke keluar luar dari dari loop loop gunaka gunakan n cursor_name%NOTFOUND. Contoh : DECLARE emp_id hr.employees.job_id%TYPE; l_name hr.employees.last_name%TYPE; id_job hr.employees.job_id%TYPE; sal hr.employees.salary%TYPE; CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM HR.employees WHERE salary > 2000; BEGIN OPEN C1; LOOP FETCH C1 INTO emp_id, l_name, id_job, sal; DBMS_OUTPUT.PUT_LINE(emp_id || ' ' || l_name || ' ' || id_job || ' ' || sal); EXIT WHEN C1%NOTFOUND; END LOOP; CLOSE C1; END;
6.8 Latihan 35
Dengan menggunakan Corsor, LOOP dan DBMS_OUTPUT tampilkan ! 1. Departmen Department_id, t_id, departmen department_nam t_name, e, city, state_prov state_province, ince, Country_na Country_name, me, region_name 2. Dapatkan jumlah pegawai berdasarkan berdasarkan job_title yang ditampilkan job_title, jml_emp. Semua job_title harus ditampilkan! 3. Employee_id, first_name, department_name, manager_id, manager_name
36
VII. STORED PROCEDURE TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • • •
Mendeskripsikan dan membuat stored procedure Membuat stored procedure dengan parameter Membedakan antara formal dan actual parameter Menggunakan parameter-passing modes yang berbeda
7.1 Definisi Procedure adalah sebuah tipe dari subprogram yang menjalankan sebuah action. Procedure bisa disimpan di dalam database sebagai sebuah objek dari skema. Procedure memberikan kemudahan dalam maintenance. 7.2 Membuat Stored Procedure •
•
•
Gunakan CREA kemu mudi dian an diik diikut utii deng dengan an nama nama,, CREATE TE PROC PROCED EDURE URE ke parameter yang dipilih, dan keyword IS atau AS Tambahkan OR REPLAC (optional nal)) untuk untuk meruba merubah h proced procedure ure yang yang REPLACE E (optio sudah ada Tulis sebuah blok PL/SQL yang berisi local variable, diawali dengan BEGIN dan diakhir dengan END (or END PROCEDURE_NAME) PROCEDURE_NAME)
Syntax :
37
7.3 Pembuatan Procedure
7.4 Definisi Parameter •
•
•
Parame Parameter ter didek dideklara larasik sikan an setel setelah ah nama nama subpro subprogra gram m didala didalam m PL/SQ PL/SQL L header Parameter melewatkan atau mengkomunikasikan data antara caller dan subprogram Para Parame mete terr digu diguna naka kan n sepe sepert rtii loca locall vari variab able le tapi tapi berg bergan antu tung ng pada pada parameter-passing mode : - IN parameter menyediakan nilai untuk subprogram supaya diproses - OUT parameter mengembalikan sebua nilai ke caller - IN OUT parame parameter ter menye menyediak diakan an sebuah sebuah nilai nilai masukk masukkan an dan bisa bisa mengembalikan nilai
7.5 Formal dan Aktual Parameter •
Forma Formall parame parameter ter : Lokal Lokal variab variabel el yang yang didekl dideklara arasik sikan an didala didalam m daftar daftar parameter dari sebuah subprogram Contoh:
CREATE PROCEDURE raise_sal(id NUMBER,sal NUMBER) IS BEGIN ... •
Aktual parameter : Nilai literal, variabel, atau ekspresi yang digunakan di dalam daftar parameter saat subprogram dipanggil Contoh :
emp_id := 100; raise_sal(emp_id,
38
7.6 Contoh Penggunaan IN parameter
7.7 Contoh Penggunaan OUT parameter
39
7.8 Contoh Penggunaan IN OUT parameter
7.9 Melihat OUT parameter dengan iSQL*Plus •
•
Menggunakan PL/SQL variable yang dicetak dengan memanggil procedure DBMS_OUTPUT.PUT_LINE
Menggunakan iSQL*Plus host host vari variab able le,, menj menjal alan anka kan n menggunakan host variable dan mencetak host variable.
QUERY_EMP
7.10 Menghapus Procedure Anda Anda dapat dapat mengh menghapu apus s proced procedure ure yang yang disimp disimpan an di dalam dalam databa database se dengan syntak sebagai berikut :
DROP PROCEDURE procedure_name Contoh :
DROP PROCEDURE
40
7.11 Latihan 1. Buat sebuah sebuah table dengan nama emp_nim(s emp_nim(salah alah satu nim dari kelompok) kelompok) dengan struktur yang sama dengan table employees! 2. Buat sebuah stored procedure dengan nama backup_emp_nim(salah satu nim dari dari ke kelo lomp mpok ok)) deng dengan an para parame mete terr inpu inputt job_ job_id id yang yang berf berfun ungs gsii untu untuk k mema memasu sukk kkan an data data dari dari tabl table e empl employ oyee ees s ke dala dalam m tabl table e emp_ emp_ni nim m berdasarkan input dari job_id! 3. Buat sebuah stored procedure dengan nama GET_EMP_NIM(salah satu nim dari kelompok) dengan parameter input department_id yang menampilkan result set yaitu EMPLOYEE_ID, FIRST_NAME, JOB_TITLE!
41
VIII. STORED FUNCTION TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • • • •
Mendiskripsikan kegunaan dari function Membuat stored function Memanggil function Menghapus function Membedakan antara procedure dan function
8.1 Definisi Function adalah blok PL/SQL yang bias memberikan nilai balik. Function bisa disimpan di dalam dalam database sebagai sebuah objek dari skema yang bisa dipan dipangg ggil il beru berulan lang. g. Func Functi tion on dipa dipang nggi gill seba sebaga gaii bagi bagian an dari dari ek eksp spre resi si atau atau digunakan untuk menyediakan nilai parameter. 8.2 Membuat Stored Procedure •
•
•
Gunakan CREATE FUNCTION kemudian diikuti dengan nama, parameter yang dipilih, tipe data nilai balik dari function dan keyword IS atau AS Tambahkan OR REPLAC REPLACE E (optio (optional nal)) untuk untuk meruba merubah h proced procedure ure yang yang sudah ada Tulis sebuah blok PL/SQL yang berisi local variable, diawali dengan BEGIN dan diakhir dengan END (or END FUNCTION_NAME) FUNCTION_NAME )
Syntax :
42
8.3 Pembangunan Function
8.4 Contoh Stored Function CREATE OR REPLACE FUNCTION get_sal (id employees.employe employees.employee_id%TYPE) e_id%TYPE) RETURN NUMBER IS sal employees.salary%TYPE := 0; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = id; RETURN sal; END get_sal;
Memanggil stored function sebagai sebuah ekspresi atau sebagai sebuah nilai parameter : EXECUTE dbms_output.put_line(get_sa dbms_output.put_line(get_sal(100)) l(100)) 8.5 Cara Menjalankan Stored Function 1. Memanggil sebagai bagian dari sebuah ekspresi PL/SQL - Menggunakan host-variable VARIABLE salary NUMBER EXECUTE :salary := get_sal(100)
43
- Menggunakan local-variable DECLARE sal employees.salary %type; BEGIN sal := get_sal(100); ... 2. Digunakan sebagai parameter untuk subprogram yang lain EXECUTE 3. Digunakan dalam sebuah pernyataan SQL SELECT job_id, get_sal(employee_id) 8.6 Keuntungan Penggunaan Stored Function •
•
•
Bisa memperpa memperpanjang njang SQL ketika ketika aktivitas aktivitas sangat komplek, komplek, sangat rumit, rumit, atau tidak mungkin hanya dengan SQL Bisa meningkatakan efisiensi ketika digunakan di dalam klausa WHERE untuk memfilter data Bisa memanipulasi nilai dari data
8.7 Function di Dalam Ekspresi SQL
44
8.8 Lokasi User Bisa Memanggil Stored Function • • •
• •
Pada daftar select atau klausa dari query Ekspresi kondisi dari kalusa WHERE dan HAVING Pada klausa CONNECT BY, START WITH, ORDER BY, dan GROUP BY dari sebuah query Klausa VALUE dari Pernyataan INSERT Klausa SET dari pernyataan UPDATE
8.9 Batasan – Batasan dalam Memanggil Stored Function 1. Supaya Supaya functi function on dapat dapat dipang dipanggil gil dalam dalam ekspre ekspresi si SQL harus harus memen memenuhi uhi syarat berikut : Tersimpan dalam database Hanya menerima parameter IN dengan tipe data SQL yang valid, bukan tipe PL-SQL yang spesifik Menge Mengemba mbalik likan an tipe tipe data data SQL yang yang valid, valid, bukan bukan tipe tipe PL-SQ PL-SQL L yang yang spesifik • •
•
2. Ketika memanggil function di dalam pernyataan SQL Parameter harus spesifik dengan bergantung pada notasi posisi Anda Anda harus harus mempun mempunya ya functi function on sendir sendirii atau atau mempun mempunya ya hak askse askses s untuk menjalankan function • •
8.10 Mengontrol Efek Samping Ketika Memanggil Function dari Ekspresi SQL • •
•
Pernyataan SELECT tidak boleh berisi pernyataan DML Pernyataan Pernyataan UPDATE atau DELETE di table T tidak bisa diquery atau berisi berisi DML pada table T yang sama. Pernyataan SQL tidak bisa mengakhiri transaksi (tidak bisa menjalankan operasi COMMIT atau ROLLBACK)
Contoh :
45
8.11 Menghapus Function Anda Anda bisa bisa meng mengha hapu pus s stor stored ed func functi tion on meng menggu guna naka kan n sint sintak ak seba sebaga gaii berikut : DROP FUNCTION Contoh : DROP FUNCTION get_sal;
8.12 Latihan 1. Buat Buat stor stored ed func functi tion on untu untuk k meng menghi hitu tung ng juml jumlah ah pega pegawa waii dala dalam m satu satu department dengan parameter in department_id, kemudian jalankan menggunakan host dan local variable! 2. Dari Dari soal soal nomer nomer 1, buat buat query query dengan dengan menjalank menjalankan an stored stored function function yang mengeluarkan data department_id, department_name, jumlah_pegawai! 3. Buat stored function untuk menaikkan gaji pegawai sebesar 25%, dengan input parameter salary, kemudian panggil dalam pernyataan UPDATE!
46
IX. TRIGGER TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • •
Mendeskripsikan dan membuat trigger Mengetahui kegunaan dari trigger Menghapus trigger
9.1 Definisi Trigger adalah sebuah blok PL/SQL atau sebuah blok stored procedure yang terhubung dengan tabel, view, skema, atau database. Trigger dijalankan secara eksp ek splis lisit it ka kapa panp npun un sebu sebuah ah even eventt (ins (inser ert, t, upda update te,, dele delete te atau atau yang yang lain) lain) dilakukan. 9.2 Membuat Trigger Sintak untuk membuat trigger sebagai berikut : CREATE [OR REPLACE] TRIGGER trigger-name {BEFORE | AFTER} {INSERT | DELETE | UPDATE | UPDATE OF columnlist} ON table-name [FOR EACH ROW] [WHEN (...)] [DECLARE ... ] BEGIN ... executable statements ...(PL/SQL statement) [EXCEPTION ... ]
Keterangan sintak : • OR REPLACE Buat trigger baru jika sudah ada. Dapat definisi dari trigger yg sudah ada. • Trigger‐name Nama dari trigger‐nya • BEFORE Mengin Mengindik dikasi asikan kan ORACL ORACLE E menjal menjalank ankan an statement • AFTER Mengin Mengindik dikasi asikan kan ORACLE ORACLE menjala menjalanka nkan n statement • INSERT Mengin Mengindik dikasi asikan kan ORACL ORACLE E menjala menjalanka nkan n statement INSERT row pada suatu tabel • DELETE Mengin Mengindik dikasi asikan kan ORACL ORACLE E menjala menjalanka nkan n statement DELETE row pada suatu table
digunakan juga untuk mengubah
trigge triggerr terseb tersebut ut sebel sebelum um suatu suatu trigge triggerr terseb tersebut ut setela setelah h suatu suatu trigge triggerr terseb tersebut ut ke ketik tika a adanya adanya trigge triggerr terseb tersebut ut ke ketik tika a adanya adanya
47
• UPDATE..OF Mengindikasikan ORACLE menjalankan trigger tsb ketika adanya statement UPDATE UPDATE pada pada ko kolom lom terte tertentu ntu dari dari tabel. tabel. Jika Jika OF‐nya OF‐nya dihilan dihilangka gkan n maka maka trigger dijalankan ketika adanya UPDATE pada setiap kolom. • FOR EACH ROW Trig Trigge gerr diakt diaktiv ivas asii oleh oleh peru peruba baha han n pada pada seti setiap ap tupl tuple e pada pada tabe tabell yang yang diaw diawas asii oleh oleh trig trigge ger. r. Seda Sedang ngka kan n jika jika ini ini tida tidak k dispe dispesi sifi fika kasi sika kan, n, maka maka diasumsik diasumsikan an statement statement trigger, trigger, dimana dimana trigger trigger diaktivasi diaktivasi setiap setiap adanya adanya statem statement ent INSER INSERT, T, DELETE DELETE atau atau UPDATE UPDATE yg dilaku dilakukan kan,, tanpa tanpa meliha melihatt adanya perubahan pada row atau tidak (bahkan akan diaktivasi walaupun row yg diawasi tidak berubah) • WHEN Adalah syarat trigger tersebut bisa dieksekusi • PL/SQL statement Merupakan body dari trigger yg menspesifikasikan apa saja yang dilakukan jika trigger tersebut diaktivasi 9.3 Penggunaan OLD dan NEW Old Old digun digunak akan an untu untuk k memp memper erol oleh eh nila nilaii lama lama dari dari sebu sebuah ah bari baris s yang yang mengalami perubahan sedangkan NEW digunakan untuk memperoleh nilai yang baru. Contoh : CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_emp(user_name, time_stamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id :OLD.employee_id,, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary); END;
9.4 Membatasi Sebuah Baris Trigger Kita dapat membatasi suatu trigger hanya boleh dijalankan jika sesuai dengan kondisi tertentu, salah satu caranya yaitu menggunakan WHEN. WHEN.
48
Contoh : BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job_id = 'SA_REP') BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct := :OLD.commission_pct+0.05; END IF; END;
9.5 Enable dan Disable Trigger Trigger yang sudah kita buat dapat kita enable atau disable sesuai dengan kebut ke butuhk uhkan an kita. kita. Beriku Berikutt sinta sintak k untuk untuk meng mengenable enable atau atau mendisable mendisable sebuah trigger: ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE; 9.6 Menghapus Trigger Untu Untuk k meng mengha hapu pus s trig trigge gerr dari dari data databa base se,, guna gunaka kan n pern pernya yata taan an DROP DROP TRIGGER : DROP TRIGGER trigger_name; Contoh : DROP TRIGGER
9.10 Oracle Merekomendasikan Trigger Untuk : • • • •
Tujuan audit (membuat audit log) Secara otomatis men‐generate nilai dari derived column Memaksakan constraint (business atau security) Membuat replikasi tabel untuk tujuan back‐up
49
9.11 Latihan 1. Buat sebuah sebuah tabel dengan struktur struktur yang sama dengan dengan tabel hr.employees hr.employees dengan mana emp_nim(salah satu dari nim kelompok)! 2. Buat tabel backup untuk nomer 1 dengan nama bkp_emp_nim(salah satu dari nim kelompok) dimana untuk setiap fieldnya mempunyai inisial new dan old, misal di tabel hr.employees terdapat field first_name maka di tabel yang anda buat aka nada field old_first_name dan new_first_name begitu jug juga a untu untuk k selu seluru ruh h fiel field d yang yang ada ada dan dan tamb tambah ahka kan n fiel field d wakt waktu, u, dan dan jenis_operasi (INSERT, UPDATE, DELETE)! 3. Buat trigger yang dipasang pada event after INSERT, UPDATE, DELETE pada table nomer 1 untuk melakukan proses INSERT pada table nomer 2 untuk proses UPDATE UPDATE dapatkan nilai OLD dan NEW!
50
X. OBJEK DATABASE YANG LAIN TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • • •
Dapat membuat Sequence Dapat melakukan modifikasi dan menghapus Sequence Dapat membuat Index dan melakukan pemeliharaan pada Index Dapat membuat private dan public Synonim 10.1 Definisi Apa itu sequence ?
• • •
Secara otomatis mengenerate bilangan secara unik Object yang bisa dipakai bersama Bias Biasan anya ya digu diguna naka kan n untu untuk k keper eperlu luan an peny penyed edia iaan an Sequence dibuat dengan perintah CREATE SEQUENCE.
PRIM PRIMAR ARY Y
KEY KEY
10.2 Sintak Statement CREATE SEQUENCE Berikut ini sintak umum Sequence : CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NONMAXVALUE}] [{MINVALUE n | NONMINVALUE}] [{CYCLE | NONCYCLE}] [{CACHE n | NOCACHE }]; 10.3
Pembuatan Sequence Buat sequence yang diberi nama DEPT_DEPTID_SEQ yang digunakan untuk mengisi primary key dari tabel DEPARTEMENTS, jangan menggunakan option CYCLE. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;
51
10.4 10.4 Konfir Konfirmas masii Sequen Sequence ce Nila Nilaii dari dari suat suatu u sequ sequen ence ce dapat dapat dilih dilihat at denga dengan n meng menggu guna naka kan n data data diction dictionary ary USER_SE USER_SEQUE QUENC NCES. ES. Kolom Kolom LAST_N LAST_NUMB UMBER ER pada data diction dictionary ary ters terseb ebut ut mena menamp mpil ilka kan n nila nilaii sequ sequen ence ce beri beriku kutn tnya ya jika jika NO NOCA CACH CHE E tida tidak k dispesifikasi. SELECT sequence_name, min_value, max_value, increment_by, last_number
10.5 NEXTVAL dan CURRVAL NEXT NEXTVA VAL L meng mengem emba bali lika kan n nila nilaii sequ sequen ence ce beri beriku kutn tnya ya,, seda sedang ngka kan n CURRVA CURRVAL L mengem mengembal balika ikan n nilai nilai sequenc sequence e saat saat ini. NEXTVAL NEXTVAL harus harus dipanggi dipanggill terlebih dahulu sebelum suatu nilai diisikan pada CURRVAL. 10.6 10.6
Penggu Penggunaa naan n Seque Sequence nce
Misal akan dimasukkan department baru yang bernama "Support" yang memiliki location ID 2500. INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL,'Support', 2500); Menampilkan nilai saat ini yang dimiliki oleh sequence DEPT_DEPTID_SEQ. SELECT DEPT_DEPTID_SEQ. CURRVAL
10.7 Memodifikasi Sequence Perintah ALTER SEQUENCE nama sequence bisa digunakan untuk memodifikasi sequence, misal merubah increment value, maximum value, pilihan cycle, atau cache. ALTER SEQUENCE dept_deptid_se q INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;
52
10.8 10.8 Petunju Petunjuk k untuk untuk Modifik Modifikasi asi Sequen Sequence ce Berikut ini petunjuk untuk melakukan modifikasi pada Sequence : •
• •
•
Harus menjadi owner dan telah memiliki privilege ALTER untuk suatu sequence. Hanya nilai sequence berikutnya yang berpengaruh Sequence harus dihapus dan dibuat ulang untuk memulai sequence dengan nilai yang berbeda. Beberapa validasi perlu dibuat pada sequence.
10.9 Menghapus Sequence Untuk menghapus sequence digunakan perintah : DROP SEQUENCE nama_sequence Sekali dihapus, sequence tidak bisa direferensi lagi. DROP SEQUENCE dept_deptid_seq; 10.10 Apa itu Index Apa itu index ? Index adalah skema object Diguna Digunakan kan oleh Oracle Oracle server server untuk untuk meningk meningkatk atkan an kecepat kecepatan an untuk untuk endapatkan endapatkan baris data yang diinginkan dengan menggunakan pointer Dapat mereduksi disk I./O dengan menggunakan metode pengaksesan pengaksesan untuk melokasikan data secara cepat. Independent Independent dari table yang diindeks • Digunaka Digunakan n dan dipelihar dipelihara a secara secara otomatis otomatis oleh oleh Oracle serve server. r. •
•
•
•
10.11 10.11 Bagaimana Index dibuat ? Bagaimana cara indeks dibuat : •
•
Secara Secara otomatis otomatis : Index Index yang yang unik dibuat secara secara otomat otomatis is pada saat mendefinisikan constraint constr aint PRIMARY KEY atau UNIQUE dalam definisi table. Secara Secara manual manual : User User dapat dapat membua membuatt index index non-un non-unik ik pada pada kolom kolom yang ada untuk menin meningk gkat atka kan n kece kecepa pata tan n akses.
Untuk membuat index pada satu atau lebih kolom, sintak penulisannya : CREATE INDEX ON
index
table {column[, ... column] ...};
53
10.12
Pembuatan Index CREATE INDEX emp_last_name_idx
10.13 Index perlu Dibuat ?
Kapan
Index perlu dibuat pada saat : • • • •
•
Kolom sering digunakan dalam klausa WHERE atau kondisi join Kolom berisi jangkauan nilai yang sangat luas Kolom berisi banyak sekali nilai NULL Dua atau lebih kolom sering digunakan bersama-sama dalam klausa WHERE atau kondisi join Table Table berukuran berukuran besar besar dan baris yang didapatka didapatkan n pada saat query query paling banyak diperkirakan kurang dari 2-4% dari baris yang ada.
10.1 10 .14 4 Kapan Kapan Index Index tidak tidak perlu perlu Dibuat Dibuat ? Index tidak perlu dibuat jika : •
Tabel
berukuran kecil •
Kolom tidak
terlalu sering digunakan sebagai kondisi dalam query Baris yang didapatkan pada saat query lebih dari 2-4% dari baris yang ada. Tabel sering di-update •
•
10.15 10.15 Melakuka Melakukan n pemeriksa pemeriksaan an terhadap terhadap keberadaa keberadaan n Index Untuk mengetahui keberadaan index, dapat dengan menggunakan data dictionary USER_INDEXES dan USER_IND_COLUMNS. USER_IND_COLUMNS.
54
10.16
Index Berbasis Fungsi
Index Index yang yang berbas berbasis is pada pada fungsi fungsi adalah adalah ekspre ekspresi si yang yang berbas berbasis is pada pada index. index. Ekspr Ekspres esii index index dibang dibangun un dari dari kolom kolom table, table, konsta konstanta nta,, fungsi fungsi SQL dan fungsi yang didefinisikan user.
10.17Menghapus Index Untuk menghapus index dari data dictionary digunakan perintah DROP INDEX. Perintah berikut menghapus index UPPER_LAST_NAME_IDX dari data dictionary.
Untuk menghapus sebuah index, anda harus menjadi owner atau pemilik dari index atau memiliki privilege DROP ANY INDEX. 10.18 Definisi Synonym Synonym Synonym dibuat dengan tujuan menyederhanakan akses ke object, dengan cara : • Merujuk ke table yang dimiliki oleh user lain l ain • Memperpendek nama object yang panjang. 10.19Pembuatan dan Penghapusan Penghapusan Synonym Sintak umum pembuatan synonym : CREATE [PUBLIC] SYNONYM synonym FOR object;
55
Misal dibuat nama pendek (sinonim) untuk view DEPT_SUM_VU CREATE SYNONYM d_sum FOR dept_sum_vu
Untuk menghapus synonym digunakan perintah DROP SYNONYM . Hanya DBA yang bisa menghapus public synonym. DROP SYNONYM d_sum
10.20 Latihan 1. Buat sequenc sequence e untuk digunakan digunakan sebagai sebagai PRIMARY PRIMARY KEY pada pada table DEPARTMENT. Sequence dimulai dari 60, dan mempunyai nilai maksimum 200.
Sequence di increment increment dengan dengan angka 10. Nama Nama
sequence yang dibuat adalah DEPT_ID_SEQ. 2. Tampilkan Tampilkan keberad keberadaan aan sequence sequence dari dictionary dictionary USER_SEQ USER_SEQUENC UENCES ES
3. Buat non-unique non-unique index pada kolom kolom FOREIGN FOREIGN KEY KEY yang ada pada table EMPLOYEE. 4. Tampilkan Tampilkan index index yang yang ada dalam data data dictionary dictionary untuk untuk table EMPLO EMPLOYEE YEE
56
