Materi Basis Data Lanjut

5/6/2018

Ma te r i Ba sis Da ta La njut - slide pdf.c om

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 mencegah penghapusan data dari suatu table yang mempunyai keterkaitan dengan table yang lain. Misal terdapat keterkaitan antara table department dengan table pegawai. Dimana pada table pegawai menyimpan informasi kolom nomer department '10' yangakan jugadihapus terdapatdari pada table department. Jika baristerdapat dengan nomer department table department, sedangkan data pegawai yang bekerja pada department tersebut, maka dengan adanya constraint antara dua table, penghapusan tersebut tidak bisa 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 nilai NULL. Kolom yang befungsi sebagai kunci primer (primary key) otomatis tidak boleh NULL.

http://slide pdf.c om/re a de r/full/ma te r i-ba sis-da ta -la njut

1/56

5/6/2018


Contoh penggunaan Constraint NOT NULL :

1.3 Constraint UNIQUE Constraint UNIQUE mendefinisikan suatu kolom menjadi bersifat unik. Ilustrasi penggunaan Constraint UNIQUE :

2


2/56

5/6/2018


Perintah pembuatan tabel untuk ilustrasi diatas :

1.4 Constraint PRIMARY KEY Constraint PRIMARY KEY membentuk key yang unik untuk suatu table. Kolom yang didefinisikan sebagai PRIMARY KEY akan mengidentifikasi suatu baris data menjadi unik. Contoh penggunaan Constraint PRIMARY KEY :

3


3/56

5/6/2018


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


4/56

5/6/2018


Perintah pembuatan tabel untuk ilustrasi diatas :

1.6 Keyword dari Constraint FOREIGN KEY Berikut ini keyword yang ada pada Constraint FOREIGN KEY :

•

FOREIGN KEY anak REFERENCES

•

ON DELETE CASCADE

: Mengidentifikasi tabel dan kolom dalam tabel induk : Penghapusan baris dependent dalam tabel anak pada saat sebuah baris pada tabel induk dihapus

•

ON DELETE SET NULL

: Mengkonversi dependent foreign key ke nilai NULL.

•

: Mendefinisikan kolom yang ada pada tabel

1.7 Constraint CHECK Constraint CHECK digunakan untuk mendefinisikan suatu 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


5/56

5/6/2018


1.9 Menambahkan Constraint Misal ditambahkan constraint FOREIGN KEY pada tabel EMPLOYEES yang menunjukkan bahwa data manager harus valid atau berasal dari tabel EMPLOYEES.

1.10 Menghapus Constraint Untuk menghapus suatu Constraint, juga digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table DROP [CONSTRAINT constraint] type (column); Untuk menghapus constraint manager dari tabel EMPLOYEES :

Untuk menghapus constraint PRIMARY KEY pada tabel departments dan menghapus asosiasi constraint FOREIGN KEY pada kolom EMPLOYEES.DEPARTMENT_ID

1.11 Men-Disable Constraint Untuk menonaktifkan constraint digunakan klausa DISABLE. Pilihan CASCADE digunakan untuk men-disable dependent integrity constraint.

1.12 Meng-Enable Constraint Untuk mengaktifkan kembali constraint, digunakan klausa ENABLE

6


6/56

5/6/2018


7


7/56

5/6/2018


1.13 Cascading Constraint Klausa CASCADE CONSTRAINTS digunakan dengan klausa DROP COLUMN. Klausa CASCADE CONSTRAINTS menghapus semua referential integrity 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 Berikut ini untuk menampilkan Kolom yang Berasosiasi dengan suatu nama suatu Constraint yang ada pada view USER_CONS_COLUMNS

8


8/56

5/6/2018


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. Tambahkan kolom DEPT_ID ke dalam tabel EMP. Tambahkan foreign key references pada tabel

EMP

yang memastikan bahwa

data pegawai

dimasukkan berdasarkan nomer department yang ada pada tabel DEPT. 4. Tampilkan constraint-constraint yang telah ditambahkan dengan melihat pada view USER_CONSTRAINTS. 5. Tampilkan nama object dan tipenya dari data dictionary USER_OBJECTS untuk tabel EMP dan DEPT. 6. Modifikasi tabel EMP. Tambahkan kolom COMMISSION dengan tipe data NUMBER(2). Tambahkan Constraint untuk memastikan bahwa kolom ini harus diisi dengan nilai > 0.

9


9/56

5/6/2018


II. VIEW TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • definisi View • Memahami 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 salah satu object 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 menampilkan view (pandangan) data yang berbeda dari data yang sama. Ada 2 (dua) tipe view, yaitu Simple View dan Complex View. Berikut ini perbandingan antara Simple View dan Complex View :

2.2 Pembuatan View View dapat dibuat dengan perintah CREATE VIEW. Subquery dapat dicantumkan dalam CREATE VIEW, tapi subquery yang digunakan tidak boleh berisi klausa ORDER BY. Sintak penulisan VIEW :

10


10/56

5/6/2018


2.3 Memanggil Data dari View Buat view EMPVU80 yang berisi detail pegawai yang bekerja di department 80.

Untuk menampilkan struktur dari view digunakan perintah DESCRIBE :

Perintah pembuatan View dapat dengan menggunakan kolom alias :

Untuk memanggil data dari view, digunakan perintah yang sama seperti memanggil data dari table.

11


11/56

5/6/2018


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 : 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 diperbolehkan 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 department, maka perubahan data yang dilakukan pada kolom ini tidak diperbolehkan. 12


12/56

5/6/2018


2.8 Mengabaikan Operasi DML Klausa READ ONLY digunakan jika kita ingin mengabaikan atau tidak mengijinkan semua operasi DML yang dilakukan pada data.

2.9 Menghapus View View dapat dihapus dengan menggunakan nama_view; Sintak penulisan untuk menghapus view :

perintah

DROP

VIEW

Contoh penghapusan View :

2.10 Inline View Inline view adalah subquery dengan nama alias yang digunakan dalam SQL Statement yang digunakan untuk membuat view. Sebuah inline view bukan merupakan object dari suatu schema. 2.11 Top-N Analysis Top N-queries adalah query untuk mendapatkan n buah nilai terbesar atau terkecil dari suatu kolom Semisal : produk apa saja yang penjualannya 10 terbesar ?

13


13/56

5/6/2018


2.12 Membentuk Top-N Analysis Sintak penulisan Top-N Analysis :

2.13 Contoh Top-N Analysis Untuk menampilkan nama dan salary dari pegawai yang memiliki penghasilan tiga besar ?

14


14/56

5/6/2018


2.14 Latihan 1. Buat view EMP_VU yang berisi nomer pegawai, nama pegawai, 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 nama DEPT20 yang berisi nomer, nama dan gaji dari pegawai yang bekerja di department 20. Beri judul kolom EMPLOYEE_ID, EMPLOYEE, dan DEPARTMENT_ID. Jangan perbolehkan 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 yang berisi nama pegawai, nama department, gaji dan grade dari gaji untuk semua pegawai. Beri judul PEGAWAI, DEPARTMENT, GAJI, JOB_TITLE. Tampilkan data pada SALARY_VU.

15


15/56

5/6/2018


III. PL/SQL I TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • •

Mengenal PL/SQL beserta bagiannya Memahamiblok 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


16/56

5/6/2018


3.2 Tipe -tipe Block Ada 3 (tiga) tipe block dalam 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


17/56

5/6/2018


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 melalui variable output. 3.6 Tipe -tipe Variabel Tipe -tipe variable dalam PL/SQL : · Scalar · Composite · Reference · LOB (large object)

18


18/56

5/6/2018


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 diikuti dengan underscore (_) lalu nama Sedangkan c_comm. merepresentasikan variable konstanta, jadi variabelnya. nama variable konstanta 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


19/56

5/6/2018


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 individual. Tipe data Composite dikenal dengan istilah 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 variable merupakan variable yang dideklarasikan dalam 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


20/56

5/6/2018


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 Oracle yaitu DBMS_OUTPUT.PUT_LINE. Untuk menggunakan fungsi 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) tipe block dalam PL/SQL, yaitu Anonymous, Procedure 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 yang dideklarasikan pada lingkungan eksternal semisal SQL*PLUS dengan host variable · disebut DBMS_OUTPUT.PUT_LINE dapat digunakan untuk menampilkan data dalam blok PL/SQL

21


21/56

5/6/2018


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); 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 4. Buat kode PL/SQL yang mendeklarasikan dua variable dan masing-masing 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


22/56

5/6/2018


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 logika dari statemen yang dijalankan dapat kita ubah dengan 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


23/56

5/6/2018


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 alternatif. Pernyataan pada ELSE dieksekusi hanya jika 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


24/56

5/6/2018


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 Terkadang kita ingin memilih antara beberapa alternatif. Anda dapat menggunakan kata kunci ELSIF (not ELSEIF or ELSE IF) untuk memasukkan kondisi tambahan. Jika kondisi pertama adalah FALSE atau NULL, maka ELSIF akan menguji kondisi lainnya. Sebuah pernyataan IF bisa memiliki beberapa 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; employees SET salary = UPDATE salary + bonus WHERE employee_id = emp_id; END;

25


25/56

5/6/2018


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 •

•

Ekspresi CASE menguji suatu variable selector diantara beberapa alternative ekspresi Jika salah satu dari ekspresi memberikan nilai TRUE, maka perintah CASE

akan mengembalikan hasilnya. 4.10 Cara Penulisan CASE

26


26/56

5/6/2018


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

employee_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 Human Resources Shipping IT Public Relations 80 Sales Executive Finance

2. Kerjakan kembali soal no 1 dengan menggunakan CASE!

27


27/56

5/6/2018


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 LOOP Perintah Loop (perulangan) mengerjakan kumpulan perintah secara berulang. Ada tiga macam loop : Basic loop Perulangan tanpa kondisi FOR loop Perulangan berdasarkan nilai variable counter (sudah diketahui berapa kali akan dikerjakan) WHILE loop Perulangan berdasarkan suatu kondisi Perintah EXIT digunakan untuk keluar dari Loop • •

•

5.2 Basic Loop Merupakan bentuk paling sederhana pada Loop. Baris di dalam 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


28/56

5/6/2018


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 outside 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 LOOP END IF; DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));

5.3 While Loop Gunakan perintah WHILE loop untuk mengulang sekumpulan perintah selama kondisi yang ditentukan bernilai TRUE

29


29/56

5/6/2018


Contoh :

5.4 For Loop Simpel for-loop melakukan perulangan dengan menggunakan integer 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 default perulangan FOR yang dilakukan adalah dari batas bawah ke batas atas, namun kita bisa menggunkan RESERVE untuk melakukan perulangan dari batas bawah ke batas atas.

30


30/56

5/6/2018


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 basic loop jika perintah dalam Loop harus dijalankan sedikitnya sekali. Gunakan WHILE loop jika setiap kali perulangan ada kondisi yang harus dievaluasi nilainya pada setiap iterasi. Gunakan FOR loop jika jumlah iterasi diketahui

31


31/56

5/6/2018


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. Gunakan perulangan untuk memasukkan data dari HR.DEPARTMENTS dengan department_id = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 ke dalam table yang sudah dibuat pada nomer 1! 3. Buat table dengan nama EMP_DETP_NIM (salah satu nim dari kelompok) dengan

nama-nama

kolom

EMPLOYEE_ID,

FIRST_NAME,

DEPARTMENT_NAME! 4. Masukkan data pengawai dengan EMPLOYEE_ID = 100, 105, 110, 115, 120 ke dalam table yang sudah dibuat pada nomer 4 dengan menggunakan WHILE

32


32/56

5/6/2018


VI. CURSOR TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: •

Dapat membedakan sebuah implisitcursor dan sebuah explisit cursor Memahami kebutuhan penggunaan Menulis cursor dalam FOR LOOP

• •

6.1 Definisi Cursor merupakan sebuah nama untuk sebuah area private yang specific dimana informasi dari statement yang spesifik dijaga. PL/SQL menggunakan explicit dan implicit cursor. •

•

Implicit Cursor

Explicit Cursor

: dideklarasikan untuk semua DML dan PL/SQL SELECT statement : dideklarasikan dan diberi nama oleh programmer

6.2 Mengontrol Explicit Cursor

DECLARE

OPEN

Membuat sebuah nama dari area SQL

Mengidentifikasi Active Set

FETCH

Memasukkan baris ke dalam variabel

EMPTY?

Memeriksa ada atau tidaknya baris

CLOSE

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 baris yang diproses membutuhkan sebuah urutan yang spesifik, gunakan klausa ORDER BY didalam query

33


33/56

5/6/2018


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

•

•

•

Jika query tidak mengembalikan baris data sama sekali, tidak 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 Mencocokkan masing-masing variable yang bersesuaian sesuai dengan posisinya

34


34/56

5/6/2018


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 Salah satu cara untuk menampilkan semua data dari cursor dapat menggunakan loop. Dengan loop akan secara otomatis membaca data mulai dari baris pertama sampai dengan bari terakhir. Untuk keluar dari loop gunakan 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


35/56

5/6/2018


Dengan menggunakan Corsor, LOOP dan DBMS_OUTPUT tampilkan ! 1. Department_id, department_name, city, state_province, Country_name, region_name 2. Dapatkan jumlah pegawai 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


36/56

5/6/2018


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 CREATE PROCEDURE kemudian diikuti dengan nama, parameter yang dipilih, dan keyword IS atau AS Tambahkan OR REPLACE (optional) untuk merubah procedure yang sudah ada Tulis sebuah blok PL/SQL yang berisi local variable, diawali dengan BEGIN dan diakhir dengan END (or END PROCEDURE_NAME)

Syntax :

37


37/56

5/6/2018


7.3 Pembuatan Procedure

7.4 Definisi Parameter •

•

•

Parameter dideklarasikan setelah nama subprogram didalam PL/SQL header Parameter melewatkan atau mengkomunikasikan data antara caller dan subprogram Parameter digunakan seperti local variable tapi bergantung pada parameter-passing mode : - IN parameter menyediakan nilai untuk subprogram supaya diproses - OUT parameter mengembalikan sebua nilai ke caller - IN OUT parameter menyediakan sebuah nilai masukkan dan bisa mengembalikan nilai

7.5 Formal dan Aktual Parameter •

Formal parameter : Lokal variabel yang dideklarasikan didalam daftar parameter dari sebuah subprogram Contoh:

CREATE PROCEDURE raise_sal(id NUMBER,sal

•

NUMBER) BEGIN ... IS 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


38/56

5/6/2018


7.6 Contoh Penggunaan IN parameter

7.7 Contoh Penggunaan OUT parameter

39


39/56

5/6/2018


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 variable, menjalankan menggunakan host variable dan mencetak host variable.

QUERY_EMP

7.10 Menghapus Procedure Anda dapat menghapus procedure yang disimpan di dalam database dengan syntak sebagai berikut :

DROP PROCEDURE procedure_name Contoh :

DROP PROCEDURE

40


40/56

5/6/2018


7.11 Latihan 1. Buat sebuah table dengan nama emp_nim(salah satu nim dari kelompok) dengan struktur yang sama dengan table employees! 2. Buat sebuah stored procedure dengan nama backup_emp_nim(salah satu nim dari kelompok) dengan parameter input job_id yang berfungsi untuk memasukkan data dari table

employees

ke dalam table

emp_nim

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


41/56

5/6/2018


VIII. STORED FUNCTION TUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: • • • • •

Mendiskripsikan Membuat stored kegunaan function dari 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 dipanggil berulang. Function dipanggil sebagai bagian dari ekspresi 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 REPLACE (optional) untuk merubah procedure yang sudah ada Tulis sebuah blok PL/SQL yang berisi local variable, diawali dengan BEGIN dan diakhir dengan END (or END FUNCTION_NAME)

Syntax :

42


42/56

5/6/2018


8.3 Pembangunan Function

8.4 Contoh Stored Function CREATE OR REPLACE FUNCTION get_sal (id employees.employee_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_sal(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


43/56

5/6/2018


- 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 memperpanjang SQL ketika aktivitas sangat komplek, sangat 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


44/56

5/6/2018


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 function dapat dipanggil dalam ekspresi SQL harus memenuhi syarat berikut : Tersimpan dalam database Hanya menerima parameter IN dengan tipe data SQL yang valid, bukan tipe PL-SQL yang spesifik • •

•

Mengembalikan tipe data SQL yang valid, bukan tipe PL-SQL yang spesifik

2. Ketika memanggil function di dalam pernyataan SQL Parameter harus spesifik dengan bergantung pada notasi posisi Anda harus mempunya function sendiri atau mempunya hak askses untuk menjalankan function • •

8.10 Mengontrol Efek Samping Ketika Memanggil Function dari Ekspresi SQL • •

•

Pernyataan SELECT tidak boleh berisi pernyataan DML Pernyataan UPDATE atau DELETE di table T tidak bisa diquery atau berisi DML pada table T yang sama. Pernyataan SQL tidak bisa mengakhiri transaksi (tidak bisa menjalankan operasi COMMIT atau ROLLBACK)

Contoh :

45


45/56

5/6/2018


8.11 Menghapus Function Anda bisa menghapus stored function menggunakan sintak sebagai berikut : DROP FUNCTION Contoh : DROP FUNCTION get_sal;

8.12 Latihan 1. Buat stored function untuk menghitung jumlah pegawai dalam satu department dengan parameter in department_id, kemudian jalankan menggunakan host dan local variable! 2. Dari soal nomer 1, buat query dengan menjalankan stored 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


46/56

5/6/2018


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 eksplisit kapanpun sebuah event (insert, update, delete atau yang 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 digunakan juga untuk mengubah definisi dari trigger yg sudah ada. • Trigger‐name Nama dari trigger‐nya • BEFORE Mengindikasikan ORACLE menjalankan trigger tersebut sebelum suatu statement • AFTER Mengindikasikan ORACLE menjalankan trigger tersebut setelah suatu statement • INSERT Mengindikasikan ORACLE menjalankan trigger tersebut ketika adanya statement INSERT row pada suatu tabel • DELETE Mengindikasikan ORACLE menjalankan trigger tersebut ketika adanya statement DELETE row pada suatu table

47


47/56

5/6/2018


• UPDATE..OF Mengindikasikan ORACLE menjalankan trigger tsb ketika adanya statement UPDATE pada kolom tertentu dari tabel. Jika OF‐nya dihilangkan maka trigger dijalankan ketika adanya UPDATE pada setiap kolom. • FOR EACH ROW diawasi Trigger diaktivasi olehSedangkan perubahan jika padaini setiap pada tabel maka yang oleh trigger. tidak tuple dispesifikasikan, diasumsikan statement trigger, dimana trigger diaktivasi setiap adanya statement INSERT, DELETE atau UPDATE yg dilakukan, tanpa melihat 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 digunakan untuk memperoleh nilai lama dari sebuah baris 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.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.

48


48/56

5/6/2018


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 kebutuhkan kita. Berikut sintak untuk mengenable atau mendisable sebuah trigger: ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE; 9.6 Menghapus Trigger Untuk menghapus trigger dari database, gunakan pernyataan 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


49/56

5/6/2018


9.11 Latihan 1. Buat sebuah tabel dengan struktur yang sama dengan tabel 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 juga untuk seluruh field yang ada dan tambahkan field waktu, 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 dapatkan nilai OLD dan NEW!

50


50/56

5/6/2018


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 Biasanya digunakan untuk keperluan penyediaan

PRIMARY

KEY

Sequence dibuat dengan perintah CREATE SEQUENCE. 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


51/56

5/6/2018


10.4 Konfirmasi Sequence Nilai dari suatu sequence dapat dilihat dengan menggunakan data dictionary USER_SEQUENCES. Kolom LAST_NUMBER pada data dictionary tersebut menampilkan nilai sequence berikutnya jika NOCACHE tidak dispesifikasi. SELECT sequence_name, min_value, max_value, increment_by, last_number

10.5 NEXTVAL dan CURRVAL NEXTVAL mengembalikan nilai sequence berikutnya, sedangkan CURRVAL mengembalikan nilai sequence saat ini. NEXTVAL harus dipanggil terlebih dahulu sebelum suatu nilai diisikan pada CURRVAL. 10.6

Penggunaan Sequence

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 Perintahsequence, ALTER SEQUENCE nama sequence bisa digunakan memodifikasi misal merubah increment value, maximumuntuk value, pilihan cycle, atau cache. ALTER SEQUENCE dept_deptid_se q INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;

52


52/56

5/6/2018


10.8 Petunjuk untuk Modifikasi Sequence 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 Digunakan oleh Oracle server untuk meningkatkan kecepatan untuk endapatkan baris data yang diinginkan dengan menggunakan pointer

•

Dapat disk I./O dengan menggunakan metode pengaksesan untuk mereduksi melokasikan data secara cepat. Independent dari table yang diindeks • Digunakan dan dipelihara secara otomatis oleh Oracle server. •

10.11 Bagaimana Index dibuat ? Bagaimana cara indeks dibuat : •

•

Secara otomatis : Index yang unik dibuat secara otomatis pada saat mendefinisikan constraint PRIMARY KEY atau UNIQUE dalam definisi table. Secara manual : User membuat index non-unik pada kolom dapat yang ada untuk meningkatkan kecepatan akses.

Untuk membuat index pada satu atau lebih kolom, sintak penulisannya : CREATE INDEX ON

index

table {column[, ... column] ...};

53


53/56

5/6/2018


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 berukuran besar dan baris yang didapatkan pada saat query paling banyak diperkirakan kurang dari 2-4% dari baris yang ada.

10.14 Kapan Index tidak perlu 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 Melakukan pemeriksaan terhadap keberadaan Index Untuk mengetahui keberadaan index, dapat dengan menggunakan data dictionary USER_INDEXES dan USER_IND_COLUMNS.

54


54/56

5/6/2018


10.16

Index Berbasis Fungsi

Index yang berbasis pada fungsi adalah ekspresi yang berbasis pada index. Ekspresi index dibangun dari kolom table, konstanta, 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 dibuat dengan tujuan menyederhanakan akses ke object, dengan cara : • Merujuk ke table yang dimiliki oleh user lain • Memperpendek nama object yang panjang. 10.19Pembuatan dan Penghapusan Synonym Sintak umum pembuatan synonym : CREATE [PUBLIC] SYNONYM synonym FOR object;

55


55/56

5/6/2018


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 sequence untuk digunakan sebagai PRIMARY KEY pada table DEPARTMENT. Sequence dimulai dari 60, dan mempunyai nilai maksimum 200.

Sequence di increment dengan angka 10. Nama

sequence yang dibuat adalah DEPT_ID_SEQ. 2. Tampilkan keberadaan sequence dari dictionary USER_SEQUENCES

3. Buat non-unique index pada kolom FOREIGN KEY yang ada pada table EMPLOYEE. 4. Tampilkan index yang ada dalam data dictionary untuk table EMPLOYEE

56


56/56

Materi Basis Data Lanjut

Recommend Documents