Modul Prak BDL - TA_1516_Genap

8/20/2019 Modul Prak BDL - TA_1516_Genap

1/53

PraktikumBasisdata Lanjut

Progdi Teknik InformatikaFTI UPN “Veteran” Jawa Timur

Semester Genap TA 2015/2016

Laboratorium Desain Dan Basis Data


2/53

i

Pengantar

Syukur alhamdulillah ke hadirat Allah SWT atas segala

limpahan Kekuatan-Nya sehingga dengan segala keterbatasan

waktu, tenaga dan pikiran yang dimiliki penulis, akhirnya modul

praktikum ini dapat terselesaikan dengan beberapa revisi dari edisi

sebelumnya.

Banyak tools yang bisa digunakan untuk membangun sebuah

sistem informasi mulai dari database hingga bahasa pemrograman

yang digunakan. Salah satunya Oracle, selain sebagai penyedia

aplikasi database Oracle juga menyediakan tool untuk membuat

aplikasi yang berbasis database khususnya Oracle. Tools yang

digunakan Oracle 10g.

Materi yang Anda dapatkan meliputi:

1. Review /SQL

2. Query Lanjutan

3. Pengenalan PL/SQL

4. Trigger

5. Index, View dan SequenceModul praktikum ini dibuat dengan tujuan untuk

mempermudah pelaksanaan praktikum. Dan lebih dari itu,

diharapkan mampu menjadi media akselerasi pemahaman serta

ketrampilan / skill praktikan sesuai dengan kompetensi yang

diharapkan.

Penulis sadar bahwa modul ini masih memiliki kekurangan.

Oleh karena itu saran dan masukan sangatlah diharapkan demi

semakin baiknya kualitas.

Penulis


3/53

ii

MATERI

MATERI 1 REVIEW SQL

1.1 Basic SQL Statement1.2 Sorting Data

1.3 Displaying Data From Multiple Table

1.4 Aggregate Data Using Group Function

1.5 Tugas Praktikum

MATERI 2 QUERY LANJUTAN

2.1 Single Row Subquery

2.2 Multiple Row Subquery2.3 Tugas Praktikum

MATERI 3 PENGENALAN PL/SQL

3.1 Keuntungan PL/SQL

3.2 Variabel

3.3 Operator

3.4 Control Structure

3.5 Memulai dan Membuat PL/SQL3.6 Tugas Praktikum

MATERI 4 TRIGGER

4.1 Manfaat dan Batasan Trigger

4.2 Struktur Dasar Trigger

4.3 Contoh Pembuatan Trigger

4.4 Mengkombinasikan Event Pada Trigger

4.5 Menggunakan Old dan New Qualifiers4.6 Klausa When Pada Trigger

4.7 Tugas Praktikum

MATERI 5 INDEX, VIEW DAN SEQUENCE

5.1 Master Detail

5.2 Tugas Praktikum


4/53

iii

DATABASE

Database yang akan kita gunakan adalah HR, struktur dan relasinya

bisa anda lihat :


5/53

iv


6/53

1

MATERI 1

REVIEW SQL

Structure Query Language (SQL) merupakan komponen

bahasa relational database system. SQL merupakan bahasa baku

(ANSI/SQL), non procedural, dan berorientasi himpunan (set-

oriented language). SQL dapat digunakan baik secara interaktif

atau ditempelkan (embedded) pada sebuah program aplikasi.

Beberapa komponen-Komponen SQL :

a. Data Definition Languange (DDL)

Digunakan untuk mendefinisikan data dengan menggunakan

perintah : Create, Drop, Alter.

b. Data Manipulation Language (DML)

Digunakan untuk memanipulasi data dengan menggunakan

perintah : Select, Insert, Update, Delete. Data Manipulation Language merupakan bagian terpadu bahasa

SQL. Perintah-perintahnya dapat dibuat secara interaktif atau

ditempelkan pada sebuah program aplikasi. Pemakai hanya

perlu menentukan 'APA' yang ia inginkan, DBMS menentukan

'BAGAIMANA' cara mendapatkannya.

c. Data Control Language (DCL)

Digunakan untuk mengontrol hak para pemakai data denganperintah : Grant, Revoke.

SELECT Data Retrieval

INSERT

UPDATE Data Manipulation Language (DML)

DELETE

MERGE


7/53

2

CREATE

ALTER

DROP Data Definition Language (DDL)

RENAME

TRUNCATE

COMMIT

ROLLBACK Transaction Control (untuk DML)

SAVEPOINT

GRANT Data Control Language (DCL)

REVOKE

1.1. Basic SQL Statement

Statement SELECT digunakan untuk mengambil record

dari sebuah tabel atau lebih. Record yang diambil dengan

SELECT dapat disaring dengan menggunakan kondisi yang

terdefinisi.

Statement SELECT mempunyai format sebagai berikut :

Menulis SQL Statement :

1. SQL statements tidak case sensitive

2. SQL statements dapat ditulis dalam satu atau banyak baris

3. Keyword harus dituliskan secara berurutan

4. Clause biasanya ditempatkan dalam satu baris

5. Indent digunakan untuk memudahkan pembacaan

SELECT [DISTINCT | ALL] * | coloumn1,coloumn2

FROM [table]

[WHERE condition]

[GROUP BY coloumn]

[HAVING condition]

[ORDER BY coloumn][ASC|DSC];


8/53

3

Concatenation Operator

Digunakan untuk menggabungkan kolom atau

character string (diapit single quotation marks) ke kolom lain.

Direpresentasikan dengan ” || “ . Hasil penggabungan ini berupa

ekspresi character. Contoh :

SELECT last_name || ‘ is a ‘ || job_id

AS “Employee Details”

FROM employees;

1.2. Sorting Data

Klausa [WHERE condition(s)]

Klausa [WHERE condition(s)] untuk membatasi row(s)

yang ditampilkan. Untuk tipe data character and date harus

ditempatkan dalam single quotation.

a. Character bersifat case sensitive, Date bersifat format

sensitive.

b. Default format untuk tipe Date: DD-MON-RR.

c.

Alias tidak dapat digunakan pada clause WHEREd. Kondisi pembanding:

Klausa [ORDER BY {coloumn, expr} [ASC|DESC]]


9/53

4

Default klausa ORDER BY adalah Ascending. Dapat

digunakan pada kolom alias. Dapat juga dilakukan SORT

berdasarkan kolom yang tidak ditampilkan dalam daftar SELECT.

Contoh :

SELECT employee_id, last_name, salary*12

annsal

FROM employees

ORDER BY annsal;

Dapat dilakukan pada multi kolom :

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

1.3. Displaying Data From Multiple Table

JOIN adalah menggabungkan beberapa tabel dangan

cara mengakses setiap tabel secara individu berdasarkan

kondisi yang diberikan, kemudian hasilnya digabungkan. Syarat

penggunaan JOIN adalah harus terdapat hubungan / keterkaitan

diantara tabel – tabel yang dijadikan sumber dari kolom – kolom

yang hendak di join dan ditampilkan. Keterkaitan diatara tabel

– tabel berupa kolom – kolom yang memiliki nilai dan tipe data

yang sama.

Join dimungkinkan dikarenakan oleh model relasional,

begitu juga sebaliknya, join dibutuhkan dikarenakan model

relational. Join sebetulnya secara umum terbagi dalam 3 jenis:

cartesian product, join condition dan outer join.

Operasi Join Deskripsi Contoh

Cartesian

Product

Menampilkan data

dari beberapa tabel

tanpa kondisi

tertentu

SELECT

NAMA_PEGAWAI,

NAMA_DEPARTEMEN

FROM PEGAWAI P,

DEPARTEMEN D


10/53

5

Join

Condition

Menampilkan data

dari beberapa tabel

dengan kondisitertentu

SELECT

NAMA_PEGAWAI,

NAMA_DEPARTEMEN

FROM PEGAWAI P,

DEPARTEMEN D

WHERE P.NO_DEP=

D.NO_DEPARTEMEN;

Outer Join

Menampilkan data

dari beberapa tabel

dimana kolom yang

diacu tidak

memiliki anggota

SELECT P.

NAMA_PEGAWAI,

M. NAMA_PEGAWAI

AS MANAJER

FROM PEGAWAI P,

PEGAWAI M

WHERE

P.NO_MANAJER =

M.NO_PEGAWAI(+);

Join digunakan ketika memerlukan data yang bersumber

pada lebih dari satu tabel. Untuk melakukan JOIN, perhatikan

kolom yang yang menjadi Primary Key dan Foreign Key dari

setiap tabel yang ingin di-JOIN-kan. Gunakan nama tabel untuk

mempertegas pengambilan kolom, terutama jika nama kolom yang

sama terdapat pada lebih dari satu tabel. Gunakan tabel alias

untuk menyederhanakan penulisan query dan meningkatkan unjuk

kerja.

SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id

FROM employees e, departments d

WHERE e.department_id = d.department_id;

SELECT e.last_name, d.department_name, l.city

FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id;

Sintaks Simple Join


11/53

6

1.4. Aggregate Data Using Group Function

Yaitu fungsi-fungsi yang hasilnya diambil dari proses tiap

baris pada tabel. Proses tersebut akan mengolah nilai sebuah field

atau lebih mulai baris pertama sampai seluruh baris.

Fungsi-fungsi tersebut yakni:

SELECT [column], group function (coloumn)

FROM [table]

[WHERE condition]

[GROUP BY coloumn]

[ORDER BY coloumn];

Sintaks


12/53

7

TUGAS PRAKTIKUM

Dengan menggunakan table yang ada pada user HR pada

ORACLE DATABASE:1. Buatlah query yang dapat membantu ANDA untuk melengkapi

tabel berikut ini:

2.

Tampilkan last name dan department number untuk

semua employees dalam department 20 sampai 50

dalam suatu urutan berdasarkan last name.

3.

Buatlah sebuah query untuk menampilkan last_name,

job_code, hire_date, dan employee number sebagaiurutan pertama! Berikan nama alias START DATE untuk

kolom hire_date! Berikut hasil tampilan query:

4.

Buatlah sebuah query untuk menampilkan job_id , secara

unik dari table employees!

5.

Buatlah LAPORAN seluruh pegawai dan job_id -nya.

Tampilkan last_name yang digabung dengan job_id -nya

(dipisahkan dengan koma dan spasi) dan nama kolomnyaEmployee and Title!

6.

Buatlah sebuah query yang menampilkan last_name dan

salary yang mendapatkan salary lebih dari $12,000!

7.

Buatlah sebuah query yang menampilkan last_name dan

department number yang employee number -nya sama

dengan 176!

8.

Buatlah sebuah query yang menampilkan last_name, job_id , dan start_date untuk pegawai yang nama


13/53

8

terakhirnya Matos dan Taylor, urutkan berdasarkan

start_date secara ascending!

9.

Tampilkan last_name (Employee) dan salary (Monthly

Salary) dari pegawai yang salary -nya diantara $5,000 dan$12,000 yang department ID-nya sama dengan 20 atau

50!

10. Buatlah sebuah query yang menampilkan last_name dan

start date untuk semua pegawai mulai bekerja pada

tahun 1994!

11. Buatlah sebuah query yang menampilkan last_name dan

job_id -nya untuk semua pegawai yang tidak memiliki

manager!

12. Pelanggan dari department HR menginginkan query yang

fleksibel dalam penulisannya. Mereka menginginkan

semacam laporan yang menampilkan last_name dan

salary untuk pegawai yang gajinya lebih dari jumlah yang

dimasukkan oleh user. Jika anda memasukkan $12000

maka akan menampilkan data sebagai berikut:

13.

Buatlah sebuah query dimana user dapat memasukkan

data sesuai manager_id dan menghasilkan employee_id,

last_name, salary, dan department untuk pegawai sesuai

managernya.

14. Buatlah sebuah query untuk menampilkan last_name dari

tabel pegawai untuk last_name yang huruf ketiganya a!

15. Perhatikan 2 query berikut, jelaskan perbedaan output

yang dihasilkan!a. SELECT AVG(commission_pct)

FROM employees;

b. SELECT AVG(NVL(commission_pct, 0))

FROM employees;


14/53

9

MATERI 2

QUERY LANJUT

Pada kondisi tertentu, terkadang beberapa query

membutuhkan nilai yang dihasilkannya dan digunakan sebagai

kondisi pembandingan (dalam klausa WHERE sebagai contoh).

Query tersebut dapat diformulasikan menggunakan nested query

(query bersarang). Query bersarang akan melibatkan query dan

subquery.Subquery merupakan statement SELECT yang

bersarang didalam klausa WHERE dari statement SELECT yang

lain. Meskipun jarang, tetapi subquery juga bisa terdapat pada

statement DML yang lain seperti INSERT, UPDATE ataupun

DELETE.

Secara umum, sintaks dari Subquery adalah :

SELECT [DISTINCT] select_listFROM table1, table_2 [,table_3 …]

WHERE {expression

{[NOT] IN | comparison operator} | [NOT] EXIST }

( SELECT [DISTINCT] subquery_select_list

FROM table_list

WHERE search_conditions

)

Yang dilakukan oleh subquery adalah menetapkan

kondisi pencarian pada klausa WHERE dalam beberapa cara:

a. Subquery (inner query) dijalankan sebelum Main Query

b. Output dari subquery digunakan untuk mengendalikan output

dari main query (outer query)

c. Menghasilkan list untuk klausa IN

Subquery

(Inner Query)


15/53

10

d. Mengembalikan sebuah nilai yang dapat digunakan oleh

operator

e. Mengembalikan nilai boolean (true atau false)

Petunjuk penggunaan Subquery yaitu :

a)

Letakkan subquery dalam tanda kurung

b) Letakkan subquery pada sisi kanan dari operator pembanding

c) Jangan tambahkan klausa Order By dalam subquery

d) Gunakan operator single-row untuk single-row subquery

e) Gunakan operator multiple-row untuk multiple-row subquery

Penggunaan Subquery

Tampilkan nama Akademia yang satu kota dengan Rindu ?

Generate Subquery :

SELECT nama

FROM akademia

WHERE kota = (SELECT kota

FROM akademia

WHERE nama = ‘Rindu’);

2.1. Single-Row Subquery

Pada Single – Row Subquery, hanya mengembalikan 1

nilai dan menggunakan operator pembanding :

Bandun


16/53

11

Contoh Penggunaan Single-Row Subquery

2.2. Multiple Row-Sub

Pada Multiple–Row Subquery, meghasilkan lebih dari 1

nilai dan menggunakan operator pembanding multiple-row :

Operator

=

>

>=


17/53

12

Contoh Penggunaan Multiple Row-Sub

TUGAS PRAKTIKUM

1. Buat query untuk menampilkan name dan hire date dari

setiap employee yang department-nya sama denganZlotkey, kecuali Zlotkey. output: 33 rows

2. Buat query untuk menampilkan employee number, last

name dan salary dari seluruh employee yang

mendapatkan salary diatas rata-rata. Tampilkan urut

berdasarkan salary.

output: 51 rows

3. Tampilkan last name, department number dan job ID

dari seluruh employee yang department location ID-nya

adalah 1700. output: 18 rows

4. Tampilkan last name dan salary dari setiap orang yang

mempunyai manager bernama King.

output: 14 rows


18/53

13

MATERI 3

PENGENALAN PL/SQL

PL/SQL, adalah bahasa prosedural yang berbentuk script

SQL dan sudah digolongkan ke dalam bahasa pemrograman generasi

4 (4GL - fourth-generation programming language). Beberapa fitur

yang diberikan antara lain ata encapsulation, overloading,

collection types, exception handling, dan information hiding.

Dengan menggunakan PL/SQL, Anda bisa menggunakan

statemen SQL untuk memanipulasi data di Oracle dan bisa

melakukan flow-of-control statemen dalam memproses data. Selain

itu, Anda bisa mendeklarasikan konstanta dan variabel, membuat

prosedur dan fungsi, sekaligus bisa untuk mengetahui kesalahan

(runtime errors) yang terjadi. Dengan demikian PL/SQL

mengabungkan manipulasi data yang didukung SQL, dengan

pemrosesan data menggunakan bahasa prosedural. Sebagai contoh

bisa Anda lihat pada contoh program dibawah ini :

DECLARE

qty_on_hand NUMBER(5);

BEGIN

SELECT quantity INTO qty_on_hand FROM inventory

WHERE product = 'TENNIS RACKET'

FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN -- check quantity

UPDATE inventory SET quantity = quantity - 1

WHERE product = 'TENNIS RACKET';INSERT INTO purchase_record

VALUES ('Tennis racket purchased', SYSDATE);

ELSE

INSERT INTO purchase_record

VALUES ('Out of tennis rackets', SYSDATE);

END IF;

COMMIT;

END;

Gambar 1. Contoh PL/SQL


19/53


20/53

15

Jika menggunakan PL/SQL, satu blok statemen akan dikirimkan

sekaligus pada waktu yang sama.

Keuntungan yang diperoleh dari sisi aplikasi adalah

apilkasi menjadi lebih ringan karena respon dari server cepat dan

statemen dikirimkan sekaligus pada waktu yang sama dan

penggunaan memori juga berkurang.

Untuk gambaran lebih jelas Anda bisa lihat gambar 3,

yang menunjukkan hubungan aplikasi dan database pada lingkungan

client-server.

Higher Productivity

PL/SQL bisa digunakan di semua aplikasi Oracle termasukOracle Forms dan Oracle Reports. Sehingga tidak perlu membuat

dengan sintak yang lain pada platform yang sama dan waktu

pengembangan pun bisa di hemat.

Full Portability

PL/SQL bisa berjalan dimana pun Oracle bisa dijalankan.

Dengan demikian Anda bisa menulis kode program dalam PL/SQL

dan bisa digunakan pada lingkungan yang berbeda.

Gambar 3. Boosts Performance


21/53

16

Tight Integration with SQL

PL/SQL dan SQL languages mempunyai hubungan sangat

dekat. PL/SQL mendukung semua tipe data SQL dan non value NULL

sehingga memungkinkan Anda memanipulasi data dengan lebih

mudah dan efisien.

Security

PL/SQL memungkinkan Anda untuk memberikan

pembatasan antara client dan server. Pembatasan tersebut bisa

diberikan lewat trigger yang ditulis dalam bentuk PL/SQL sehingga

bisa membatasi akses ke aplikasi dan database. Selain itu Anda

juga bisa membatasi user untuk hanya memanipulasi perintah

PL/SQL di dalam sebuah stored procedures yang bisa di jalankan

user tersebut dengan hak yang dia miliki.

Sebagai contoh, Anda memberikan akses kepada seorang

user untuk bisa mengakses prosedur update_table, tetapi user

tersebut tidak bisa mengakses tabel tersebut.

3.2. Variabel

Secara global tipe data pada PL/SQL dibedakan menjadi 6

(enam) golongan yaitu :

Numeric

Digunakan untuk mendefinisaikan variabel yang

bertipe numerik (angka), untuk mendefinisikannya Anda harus

menuliskan seperti ini :

Jumlah_barang number(5,2);

Angka 5 dan 2 yang ada pada bagian belakang

menunjukkan presisi (5) dan scale (2). Untuk scale bisa

digunakan dan bisa juga tidak.

Character

Digunakan untuk mendefinisikan variabel yang bertipe

karakter (huruf dan string lainnya), untuk mendefinisikan Anda

harus menuliskan seperti ini :

Nama_pelanggan varchar(30);


22/53

17

Angka 30 pada varchar menunjukkan panjang data

pada variabel tersebut.

Date

Jika Anda menggunakan tipe data tanggal maka cara

mendefinisikannya adalah :

Tanggal_lahir date;

Boolean

Tipe data ini hanya bernilai true dan false, untuk

mendefinisikan :

Status boolean;

Datatypes for specific columns

Tipe data ini digunakan untuk mendapatkan tipe data

yang digunakan pada sebuah kolom pada sebuah tabel.

Sehingga tipe variabel yang kita pesan otomatis sama denga

tipe kolom yang ada pada tabel. Cara mendefinisikannya :

Nomor_induk mahasiswa.nim%type;

User-defined datatypes

Dengan tipe data ini Anda bisa membuat tipe data

berdasarkan sekumpulan tipe data yang Anda buat sendiri

(record). Sebagai contoh :

Declare

type t_address is record (

name address.name%type,street address.street%type,

street_number address.street_number%type,

postcode address.postcode%type);

v_address t_address;

begin

select name,street, street_number, postcode into

v_address from address where rownum = 1;

end;


23/53

18

3.3. Operator

Seringkali dalam sebuah blok PL/SQL kita bertemu dengan

penggunaan operasi-operasi yang berkaitan dengan nilai, variabel,

dan konstanta. Ada 6 operator yang bisa digunakan seperti dibawah

ini :

Operator Logika

Operator ini bernilai TRUE dan FALSE saja dan

memiliki 3 operator logika yaitu NOT, AND, dan OR. Cara

penggunaannya tidak berbeda dengan bahasa pemrograman

yang lain.

Operator Aritmatika

Banyak kita gunakan dalam penyelesaian masalah

yang berkaitan dengan tipe data numerik, beberapa operator

bisa Anda lihat pada tabel dibawah ini :

Operator Keterangan Contoh

+ Penjumlahan 2 + 3

- Pengurangan 5 – 3

* Perkalian 5 * 2

/ Pembagian 10 / 3

MOD Sisa bagi 10 MOD 3

Berikut ini adalah contoh pemakaian operator

aritematika dalam blok PL/SQL.

Declare

X Integer;

Y Number;

Begin

X := 2 + 3;

Y := 5 * 5;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(X));

DBMS_OUTPUT.PUT_LINE(TO_CHAR(Y));

End;

Operator Relasional

Digunakan untuk menyatakan hubungan antara dua

buah nilai atau ekspresi yang akan menghasilkan nilai boolean


24/53

19

(TRUE atau FALSE). Yang termasuk dalam operasi relasional

bisa Anda lihat pada tabel berikut ini :

Operator Keterangan

< Lebih kecil

Lebih besar

>= Lebih besar atau sama dengan

Operator Persamaan

Digunakan untuk melakukan pengecekan terhadap dua

buah nilai pada dua bagian yang berbeda, kiri dan kanan.

Operator Keterangan

= Sama dengan

Tidak sama dengan

Contoh penggunaan dalam blok PL/SQL :

DeclareX Integer;

Y Number;

Begin

X := 2 + 3;

Y := 5 * 5;

If X 17 Then

DBMS_OUTPUT.PUT_LINE(TO_CHAR(X));

ElseDBMS_OUTPUT.PUT_LINE(TO_CHAR(Y));

End if

End;

Operator Penggabungan

Kadang kala Anda membutuhkan sebuah operator

untuk menggabungkan 2 (dua) atau lebih data menjadi satu

bagian. Dalam PL/SQL disediakan sebuah operatorpenggabungan (concatenation) yang berbentuk ||.


25/53

20

Declare

X number := 10;

Y varchar2(10) := “Operasi penggabungan ”;

BeginDBMS_OUTPUT.PUT_LINE(Y || TO_CHAR(X));

End;

Hasil dari PL/SQL tersebut adalah “Operasi Penggabungan

10”, jika yang digabungkan berbeda tipe data maka salah satu

harus di konversi tipe datanya. Pada contoh diatas variabel X yang

bertipe number di konversi menjadi karakter menggunakan fungsi

TO_CHAR().

3.4. Control Structures (Conditions and Iteration)

Sama dengan model bahasa pemrograman yang lain,

dalam PL/SQL juga mengenal statemen kondisi dan perulangan.

Untuk kondisi ada beberapa macam antara lain :

IF – THEN

Merupakan bentuk spaling sederhana dari statemen

IF, sintak penulisannya seperti berikut ini :

IF condition THEN

sequence_of_statements

END IF;

Contoh penerapan dalam PL/SQL bisa Anda lihat

seperti dibawah ini :

IF sales > quota THEN

compute_bonus(empid);

UPDATE payroll SET pay = pay + bonus

WHERE empno = emp_id;

END IF;


26/53

21

IF – THEN – ELSE

Merupakan pengembangan dari bentuk diatas dengan

penanganan untuk kondisi yang lainnya. Contoh penerapan

dalam PL/SQL :

IF trans_type = 'CR' THEN

UPDATE accounts SET balance = balance +

credit

WHERE ...

ELSE

UPDATE accounts SET balance = balance -

debit

WHERE ...END IF;

IF – THEN – ELSEIF

Kadang kala kita mempunyai kondisi yang banyak dan

memang harus dimasukkan. Hal tersebut tidak bisa diselesaikan

hanya dengan format IF-THEN atau IF-THEN-ELSE saja, ada

alternatif satu lagi yaitu IF-THEN-ELSIF.

Contoh penerapan dalam PL/SQL bisa Anda lihat dibawah ini :

BEGIN

...

IF sales > 50000 THEN

bonus := 1500;

ELSIF sales > 35000 THEN

bonus := 500;ELSE

bonus := 100;

END IF;

INSERT INTO payroll VALUES (emp_id, bonus,

...);

END;


27/53

22

Untuk perulangan ada beberapa macam yaitu :

LOOP

Merupakan bentuk sederhana dari perulangan yang

ada pada PL/SQL, cara penulisannya adalah :

LOOP


END LOOP;

Contoh penerapan dalam blok PL/SQL :

LOOP...

IF credit_rating < 3 THEN

...

EXIT; -- exit loop immediately

END IF;

END LOOP;

Statemen EXIT merupakan tanda bagi perulangan

untuk mengakhiri proses perulangan.

Selain itu ada bentuk lain dari EXIT yaitu EXIT – WHEN

seperti terlihat pada contoh di berikut ini :

...

LOOP

FETCH c1 INTO ...

EXIT WHEN c1%NOTFOUND;

--exit loop if condition is true...

END LOOP;

CLOSE c1;

FOR – LOOP

Jika Anda mempunyai sebuah proses perulangan yang

harus dijalankan secara sequence dan memiliki jumlah


28/53

23

perulangan yang pasti, Anda bisa menggunakan model

perulangan FOR – LOOP. Contoh penerapan dalam blok PL/SQL :

FOR i IN 1..3 LOOP -- memberi nilai 1,2,3 ke

i

DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));

-- dijalankan sebanyak 3 kali

END LOOP;

FOR i IN REVERSE 1..3 LOOP –-memberi nilai

3,2,1 to i

DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));

-- dijalankan sebanyak 3 kali

END LOOP;

Hasilnya akan berlawanan dari contoh sebelumnya,

yaitu 3, 2, 1. Atau jika Anda ingin menghasilkan sebuah

perulangan dengan sejumlah kelipatan, gunakan kata STEP

seperti contoh di berikut ini :

FOR J = 5 TO 15 STEP 5 --memberi nilai

5,10,15 ke J

DBMS_OUTPUT.PUT_LINE(TO_CHAR(J));

-- J mempunyai nilai 5,10,15

NEXT J

Contoh di atas menghasilkan 3 (tiga) buah angka yaitu5, 10, 15.

WHILE – LOOP

Merupakan perulangan yang akan dijalankan jika

kondisi yang ditemui benar, contoh dalam blok PL/SQL :

WHILE total


29/53

24

SELECT sal INTO salary FROM emp WHERE ...

total := total + salary;

END LOOP;

Pada contoh diatas proses perulangan dengan WHILE

akan dijalankan terus selama total lebih kecil atau sama

dengan 25000, jika melebihi 25000 maka perulangan tersebut

akan dihentikan.

Atau jika menggunakan operator logika, bisa Anda

lihat pada contoh berikut :

done := FALSE;WHILE NOT done LOOP


done := boolean_expression;

END LOOP;

3.5. Memulai dan Membuat PL/SQL

Memulai PL/SQL

Untuk memulai membuat PL/SQL Anda menggunakan tools

Oracle SQL*Plus yang sudah otomatis terinstal saat Anda menginstal

Oracle Form atau Oracle Database. Dan tentu saja Anda

memerlukan user dan password seperti yang telah dijelaskan pada

bab Pengantar Umum dibagian awal modul ini.


30/53

25

Masukkan user dan password yang telah Anda miliki, jika

Host String yang Anda masukkan tidak dikenali maka akan muncul

pesan kesalahan “ERROR : ORA-12154 : TNS : could not resolveservice name” seperti terlihat pada gambar dibawaH ini :

Untuk membuka

Oracle SQL*Plus, ikutilangkah berikut, klik

tombol [Start] kemudian

pilih [Program] cari

menu [Oracle for

Windows NT] nah di

dalam menu itu kamu

bisa menemukan SQL

Gambar 4. Pesan kesalahan host string tidak dikenali


31/53

26

Apabila user atau password Anda salah maka akan muncul

pesan kesalahan “ERROR : ORA-01017 : invalid username/password;

logon denied”. Jika Anda berhasil login maka akan muncul tampilan

seperti pada gambar berikut ini :

Membuat prosedur PL/SQL tanpa paramater

Setelah berhasil login untuk membuat sebuah PL/SQL

Anda harus mendeklarasikan sebuah prosedure dengan sintak :CREATE OR REPLACE PROCEDURE procedure-name IS

[deklarasi variabel]

BEGIN

statemen-PL/SQL

END;

Contoh penerapan dalam Oracle SQL*Plus ::

Gambar 5 Jika sukses login


32/53

27

U

ntuk melihat hasilnya dari prompt SQL ketikkan EXECUTE nama-

prosedure. Atau bisa Anda singkat menjadi EXEC nama-prosedure.

Jika saat menjalankan PL/SQL di SQL*Plus Anda tidak

mendapatkan hasil apa-apa di layar (dengan catatan tidak ada

kesalahan kode program) maka tuliskan sintak berikut ini:.

SQL>set setserveroutput on

Jika kode program Anda cukup panjang atau jika terjadi

kesalahan dan ingin memperbaikinya ketikkan “ED” pada prompt

SQL setelah itu akan muncul sebuah editor untuk mengedit sintakterakhir yang Anda tuliskan sepertipada gambar dibawah ini.

Gambar 6. Pembuatan prosedur dengan SQL*Plus

Gambar 7. Hasil eksekusi PL/SQL

Gambar 8 Editor untuk memperbaiki sintak


33/53

28

Untuk kembali ke PL/SQL tekan tombol [ALT+F] lanjutkan

dengan menekan tombol [X] jika muncul pertanyaan pilih [Yes].

Membuat prosedur PL/SQL dengan parameter

Semua contoh diatas adalah prosedur yang tidak

menggunakan parameter, dimana nilai yang diproses sudah

ditentukan dan berada di dalam prosedur tersebut. Ada juga

prosedur yang sifatnya dinamis dimana nilainya bisa kita ubah

sesuai dengan kondisi yang diinginkan. Agar bisa berfungsi seperti

itu prosedur tersebut harus menggunakan sebuah paramater

sebagai penerima inputan dari user.

Untuk cara pembuatan sama dengan prosedur yang tidak

menggunakan parameter. Contoh penerapannya bisa Anda lihat di

bawah ini :

CREATE OR REPLACE PROCEDURE tambah(pnim IN

VARCHAR2, pnama IN VARCHAR2) IS

BEGIN

Insert into Mahasiswa(nim, nama)

values(pnim, pnama);

END;

Untuk menjalankan prosedure tersebut, pada prompt SQL

tuliskan seperti di bawah ini :

SQL> exec tambah(’00.41010.0001’,’Superman’);

TUGAS PRAKTIKUM

1. Jika terdapat 3 buah variabel yaitu X (disi dgn nilai 13), Y (disi

dgn nilai 12), dan Z sebagai penampung hasil operasi-operasi

aritmatika antara X dan Y. Buatlah sebuah blok PL/SQL yang

dapat memberikan hasil sebagai berikut :

Hasil penjumlahan antara X dan Y = 25

Hasil pengurangan antara X dan Y = 1

Hasil perkalian antara X dan Y = 156


34/53

29

2. Buatlah sebuah blok PL/SQL untuk menentukan Nilai Huruf

(A,B,C,D) dari nilai ujian mahasiswa (masih berbentuk angka)

pada suatu perguruan tinggi di Surabaya. Ketentuannya

sebagai berikut :

3. Tampilkan urut angka 1-10. Dengan menggunakan :

a.

LOOPb. FOR – LOOP

c. WHILE – LOOP

4. Hitung hasil dari penjumlahan bilangan – bilangan yang

terdapat pada deret berikut dengan menggunakan blok

PL/SQL :

3 + 6 + 9 + 12 + 15 . . . + 30

5. Buatlah sebuah blok PL/SQL yang dapat menentukan sebuah

bilangan, apakah termasuk bilangan positif, negatif, atau nol.

Nilai Huruf Nilai Angka

A 80 – 100

B 65 – 79

C 45 – 65

D 0 – 44


35/53

30


36/53

31

MATERI 4

TRIGGER

Merupakan blok PL/SQL yang memiliki fungsi hampir sama

dengan procedure maupun function , hanya saja, jika

procedure atau function dijalankan secara eksplisit lewat

pemanggilan procedure atau function tersebut, sedangkan

untuk trigger, dijalankan secara implisit melalui perintah

insert, delete atau update.

4.1. Manfaat dan Batasan Trigger

Manfaat Trigger

Beberapa manfaat dalam penggunaan Trigger yaitu :

a. Mengatur integritas dari constraint yang kompleks yang

tidak mungkin ditangani oleh sintaks-sintaks pembuatan tabel.

b. Mencegah transaksi yang tidak valid

c.

Memperbaiki keamanan database dengan menyediakan

audit yang lebih kompleks mengenai informasi perubahan

database dan user siapa yang melakukan perubahan.

d. Secara otomatis memberi sinyal program lain untuk

melakukan sesuatu jika isi tabel diubah.

Batasan Trigger

Trigger mempunyai batasan, yaitu :a. Tidak dapat menggunakan perintah commit dan rollback,

selain itu juga tidak dapat memanggil procedure, function

atau package yang menggunakan perintah commit dan

rollback.

b. Tidak dapat diimplementasikan pada kolom pada suatu

tabel yang memilki constraint, jika pada akhirnya akan

menyebabkan pelanggaran constraint. Biasanya terjadi akibat

modifikasi pada primary key.


37/53

32

4.2. Struktur Dasar Trigger

CREATE [or replace] TRIGGER nama_trigger

(BEFORE|AFTER)(INSERT|UPDATE [OF nama_kolom,..]|DELETE)ON

nama_tabel

[FOR EACH ROW]

[WHEN (kondisi)]

DECLARE

Deklarasi tipe data

BEGINTrigger body

END;

Sintak penulisan dari trigger, berisi beberapa dari

komponen berikut :

1. Trigger timing

a. Untuk tabel : BEFORE, AFTER.

b. Untuk view : INSTEAD OF.

2. Trigger Event

INSERT, UPDATE atau DELETE.

3. Nama Tabel

Nama tabel atau view yang berhubungan dengan trigger.

4. Tipe Trigger

Baris atau Pernyataan (statement)

5. When

Penulisan kondisi pembatasan

6. Trigger Body

Bagian prosedur yang dituliskan pada trigger.

Nama trigger sebaiknya dengan jelas mencerminkan

table yang diaplikasikan, Perintah DML Trigger, status

before/after, dan apakah row level atau statement level.

Misalnya trigger BEFORE UPDATE dengan row level pada table

KARYAWAN dapat diberi nama bef_upd_row_karyawan.


38/53

33

Triggering statement adalah statement/perintah yang

menyebabkan trigger tereksekusi.

Komponen dalam Trigger :

Bagian Trigger Keterangan Nilai yg Mungkin

Trigger timing

Menunjukkan kapantrigger akandieksekusirelatif terhadapsuatu event.

BEFOREAFERINSTEAD OF

Trigger event

Jenis manipulasidata padatabel/view yangmenyebabkantrigger terpacu.

INSERTUPDATE

DELETECREATEALTERDROPSTARTUPSHUTDOWNLOGOFFLOGONSERVERERROR

SUSPEND

Triggerrestriction(optional)

Batasan yangmengizinkanpengeksekusiantrigger.

When (booleanvalue)

Trigger typeBerapa kali bodytriggerdieksekusi.

Row-levelSTATEMENT-level

Trigger bodyBerisi algoritmaaksi yang akandilakukan.

Blok PL/SQL

Trigger Timing

Trigger Timing adalah waktu kapan trigger diaktifkan.

Tiga macam trigger timing, adalah :

a. BEFORE : trigger dijalankan sebelum DML event pada tabel

b. AFTER : trigger dijalankan setelah DML event pada tabel

c.

INSTEAD OF : trigger dijalankan pada sebuah view.


39/53

34

Kita mungkin akan berhubungan dengan data lama

(old) dan data baru (new) yang terjadi dalam transaksi. Dalam

trigger, dikenal istilah alias atau referensi, yaitu sejenis variabel

yang menyimpan nilai dari suatu kolom dalam tabel. Alias

terbagi menjadi dua, yaitu:

1. Data Baru

Untuk mendapatkan data baru pada sebuah tabel di dalam

trigger body, dapat dilakukan dengan perintah sebagai berikut:

:new.nama_kolom

2. Data Lama

Untuk mendapatkan data lama pada sebuah tabel di dalam

trigger body, perintah yang digunakan:

:old.nama_kolom

Referensi nilai kolom di trigger:

insert (new.nama_kolom)

update (new.nama_kolom dan old.nama_kolom)

delete (old.kolom_name)

Trigger Event

Trigger Event, ada 3 kemungkinan : INSERT, UPDATE atau

DELETE. Pada saat trigger event UPDATE, kita dapat

memasukkan daftar kolom untuk mengidentifikasi kolom mana

yang berubah untuk mengaktifkan sebuah trigger (contoh: UPDATE

OF salary...). Jika tidak ditentukan, maka perubahannya akan

berlaku untuk semua kolom pada semua baris.

Trigger Type

Tipe dalam trigger ada 2, yaitu :

a. Statement

Statement-level trigger dieksekusi satu kali pada saat

transaksi, tanpa memperhatikan jumlah row yang terlibat.

Misalnya, jika terdapat suatu transaksi yang memasukkan

1000 baris ke dalam tabel, maka statement-level trigger

hanya akan dieksekusi sekali saja. Statement-level triggerberguna jika kode dalam trigger body tidak bergantung pada


40/53

35

baris yang terpengaruh oleh triggering statement. Secara

default trigger yang dibuat adalah statement-level trigger.

b. Row

Row-level trigger dieksekusi untuk setiap row yang

dimanipulasi pada suatu transaksi. Dengan kata lain, row-

level trigger mengerjakan trigger action satu kali untuk

setiap row yang dimanipulasi. Penerapan trigger ini

ditunjukkan oleh adanya klausa FOR EACH ROW. Row-level

trigger berguna jika kode dalam trigger body bergantung

pada setiap baris yang terpengaruh oleh triggering

statement.

Trigger Body

Trigger body mendefinisikan tindakan yang perlu

dikerjakan pada saat terjadinya event yang mengakibatkan

sebuah trigger menjadi aktif.

4.3. Contoh PembuatanTrigger

Contoh berikut ini akan mengaktifkan sebuah triggerpada saat sebuah baris tunggal dimanipulasi pada tabel :

Misal diberikan perintah DML untuk menyisipkan baris baru

(INSERT) ke dalam tabel sebagai berikut :

INSERT INTO departments (department_id,

department_name, location_id)

VALUES (400, 'CONSULTING', 2400);

Ilustrasi dari trigger timing untuk event tersebut adalah

sebagai berikut :

TRY IT!!


41/53

36

Jika DML statement berlaku untuk lebih dari satu

baris yang ada pada tabel (multiple row), semisal :

UPDATE employees

SET salary = salary * 1.1

WHERE department_id = 30;

Ilustrasi dari trigger timing untuk event tersebut adalah

sebagai berikut :

4.4. Mengkombinasikan Event Pada TriggerBeberapa event pada trigger bisa dikombinasikan

dalam sebuah trigger dengan menggunakan predikat kondisional

INSERTING, UPDATING dan DELETING. Berikut ini akan dibuat

trigger yang menggunakan predikat kondisional INSERTING,

UPDATING dan DELETING untuk membatasi manipulasi data

pada tabel EMPLOYEES hanya diperbolehkan pada setiap jam

kerja mulai hari Senin sampai Jum’at.

TRY IT!!


42/53

37

BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN

IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR

(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08'

AND '18')

THEN

IF DELETING THEN

RAISE_APPLICATION_ERROR (-20502,'You may

delete from

EMPLOYEES table only during business hours.');

ELSIF INSERTING THEN

RAISE_APPLICATION_ERROR (-20500,'You may

insert intoEMPLOYEES table only during business

hours.');

ELSIF UPDATING ('SALARY') THEN

RAISE_APPLICATION_ERROR (-20503,'You may update

SALARY only during business hours.');

ELSE

RAISE_APPLICATION_ERROR (-20504,'You may updateEMPLOYEES table only during normal hours.');

END IF;

END IF;

END;

4.5. Menggunakan Old dan New Qualifiers

Pada Row Trigger, nilai dari kolom sebelum dan sesudah

perubahan data dapat dirujuk dengan menggunakan OLD dan

NEW qualifier. OLD dan NEW hanya digunakan pada Row

Trigger. OLD dan NEW menggunakan prefiks (:) untuk

pernyataan dalam perintah SQL. Jika qualifier ini terlibat

dalam pembatasan kondisi pada klausa WHEN, maka tidak

digunakan prefiks (:).

Row triggers akan menurunkan unjuk kerja jika

banyak dilakukan update pada tabel yang cukup besar.


43/53


44/53

39

4.6. Penggunaan Klausa When Pada TriggerUntuk membatasi operasi trigger hanya pada baris

yang memenuhi kondisi tertentu, maka digunakan klausa

WHEN. Berikut ini akan dibuat trigger pada tabel EMPLOYEES

yang menghitung komisi yang diterima oleh seorang pegawai pada

saat sebuah baris ditambahkan ke dalam tabel EMPLOYEES,

atau pada saat dilakukan modifikasi pada gaji pegawai.

CREATE OR REPLACE TRIGGER derive_commission_pct

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;

/

Pada klausa WHEN, penggunaan OLD dan NEW qualifier

tidak dengan prefiks (:). Untuk menggunakan NEW qualifier,

gunakan BEFORE Row Trigger.

4.7. Perintah Umum

Status Trigger

Untuk mengubah status sebuah trigger dijalankan sintaks

berikut :

TRY IT!!


45/53

40

ALTER TRIGGER nama_trigger DISABLE | ENABLE;

DISABLE : untuk menonaktifkan trigger yang sudah dibuat

ENABLE : untuk mengaktifkan kembali trigger yang sudah di

DISABLE.

Untuk mengaktifkan atau menonaktifkan semua trigger

yang berlaku untuk sebuah tabel, digunakan perintah :

ALTER TABLE table_name DISABLE | ENABLE ALL

Untuk melakukan kompilasi ulang sebuah trigger,

digunakan perintah :

ALTER TRIGGER trigger_name COMPILE

Menghapus Trigger

Untuk menghapus trigger dari database, digunakan

perintah :

DROP TRIGGER trigger_name;

Semua trigger yang berlaku pada sebuah tabel akan

dihapus pada saat tabel tersebut dihapus dari database.

TUGAS PRAKTIKUM

1. Jelaskan maksud trigger dibawah ini:

create trigger ledger_after_ins_row

before insert on LEDGER_AUDIT

for each rowbegin

call INSERT_LEDGER_DUP(:new.Action_Date,

:new.Action, :new.Item,

:new.Quantity, :new.QuantityType, :new.Rate,

:new.Amount, :new.Person);

end;


46/53

41

MATERI 5

INDEX, VIEW DAN SEQUENCEData yang tersimpan dalam database semakin lama akan

semakin besar ukuran atau volumenya. Kalau tidak didukung

dengan kecepatan akses yang memadai maka akan semakin

menurun unjuk kerjanya. Ukuran unjuk kerja dalam hal ini

kecepatan akses data dipengaruhi oleh banyak faktor. Pada

modul 5 ini akan membahas tentang optimasi query serta

faktor-faktor lain yang berpengaruh terhadap optimalisasikecepatan akses data.

Optimalisasi Pada Perintah SQL

Desain aplikasi saja tidak cukup untuk meningkatkan

unjuk kerja harus didukung dengan optimasi dari perintah SQL

yang digunakan pada aplikasi tersebut. Dalam mendesain

database, seringkali lokasi fisik data tidak menjadi perhatian

penting. Karena hanya desain logik saja yang diperhatikan.Padahal untuk menampilkan hasil query dibutuhkan pencarian

yang melibatkan struktur fisik penyimpanan data. Inti dari

optimasi query adalah meminimalkan “jalur” pencarian untuk

menemukan data yang disimpan dalam lokasi fisik.

Index pada database digunakan untuk meningkatkan

kecepatan akses data. Pada saat query dijalankan, index

mencari data dan menentukan nilai ROWID yang membantu

menemukan lokasi data secara fisik di disk. Akan tetapipenggunaan index yang tidak tepat, tidak akan meningkatkan

unjuk kerja dalam hal ini kecepatan akses data.

5.1. Index

Index adalah objek schema yang berisi catatan dari

nilai-nilai yang muncul pada satu kolom atau kombinasi kolom

di index dari sebuah tabel. Index dibuat untuk mempercepat

pengaksesan data pada suatu tabel. Index ini dibuat


47/53

42

berdasarkan pada field – field dari sebuah tabel. Index bisa

dibuat secara otomatis untuk constraint primary key atau

unique key dan secara manual melalui CREATE INDEX statement.

Membuat Index

Memodifikasi Index

Mengubah Nama Index

CREATE INDEX nama_index ON

nama_tabel(nama_field1, nama_field2,….);

SQL>CREATE INDEX mahasiswa_idx ONmahasiswa(nim, nama, alamat );

ALTER INDEX nama_index

[INITRANS integer]

[MAXTRANS integer]

[STORAGE storage_clause]

SQL > ALTER INDEX mahasiswa_idx

INITRANS 10;

Sintaks :ALTER INDEXnama_index_lama RENAME TOnama_index_baru;

ALTER INDEX

mahasiswa_idx RENAME

TO mhs_idx;


48/53

43

Menghapus Index

Misal digunakan index yang melibatkan tiga buah

kolom yang mengurutkan kolom menurut kota, propinsi dan kode

pos dari tabel karyawan, sebagai berikut :

CREATE INDEX idx_kota_prop_kodepos

ON karyawan(kota, propinsi, kode_pos)

TABLESPACE INDX;

Lakukan query sebagai berikut :

SELECT * FROM karyawan WHERE propinsi=’Jawa

Barat’;

Pada saat melakukan query ini, index tidak akan

digunakan karena kolom pertama (kota) tidak digunakan dalam

klausa WHERE. Jika user sering melakukan query ini, maka

kolom index harus diurutkan menurut propinsi. Selain itu, proses

pencarian data akan lebih cepat jika data terletak pada block

tabel yang berdekatan daripada harus mencari di beberapa

datafile yang terletak pada block yang berbeda.

Misal pada perintah SQL berikut ini :

SELECT * FROM karyawan

WHERE id BETWEEN 1010 AND 2010;

Query ini akan melakukan “scan” terhadap sedikit data

block jika tabel karyawan diatas diurutkan berdasarkan kolom

id. Untuk mengurutkan berdasarkan kolom yang berbeda-beda

maka tabel disimpan dalam flat file, kemudian tabel diekspor

dan diurutkan sesuai kebutuhan.

Alternatif yang lain, bisa digunakan perintah untuk

membuat tabel lain yang memiliki urutan yang berbeda dari tabelasal, seperti perintah SQL berikut :

DROP INDEX nama_index;

Contoh :SQL > DROP INDEXmahasiswa_idx;


49/53

44

CREATE TABLE karyawan_urut

AS SELECT * FROM karyawan

ORDER BY id;

Pada SQL diatas, tabel karyawan_urut berisi data

yang sama dengan tabel karyawan hanya datanya terurut

berdasarkan kolom id.

5.2. View

View adalah sebuah virtual tabel yang dibangun dari satu

atau beberapa tabel yang sudah ada, baik berdasarkan kondisi

tertentu ataupun tidak. Secara fisik view tidak menyimpan

record seperti pada tabel, tetapi ia menyimpan data berupa

pointer yang menunjukkan ke record yang bersangkutan di

dalam tabel. Sumber data view dapat berasal dari table atau view

lain. Mirip dengan table, Anda dapat melakukan update, delete,

dan insert pada view sehingga perubahan itu akan

direfleksikan pada base tabelnya. Berbeda dengan tabel, view

tidak menyimpan data, view hanya menyimpan definisi query

pada data dictionary dan tidak memerlukan ruang penyimpanan

data. Penerapan view dapat diaplikasikan pada situasi berikut :

a. Membatasi akses sesuai otoritas user

b. Memudahkan pemahaman tehadap kolom penampung data

yang mungkin berbeda dengan definisi kolom pada table dasar

c. Menyederhanakan pandangan user terhadap data

d. Menangani data kompleks

e. Memudahkan penggunaan quey yang berulang karena

disimpan sebagai stored query

5.3. Materialized View (MV)

Materialized View (MV) merupakan objek schema yang

berisi hasil query. Tabel-tabel yang digunakan pada query dapat

berupa hasil, view atau MV lain yang disebut sebagai table

master (replikasi) atau table detail (data warehouse) dan

informasinya tersedia pada dictionary ALL_MVIEWS,

DBA_MVIEWS, dan UESR_MVIEWS.


50/53

45

Materialized view atau snapshotini digunakan pada

database terdistribusi untuk membuat aplikasi dengan

sinkronisasi data pada berbagai site maupun untuk data

warehouse yang mempersiapkan dan menyimpan data agregat. MV

meningkatkan kecepatan akses query melalui perkalkulasi join

dan operasi agregat sebelum menjalankan dan menyimpan

hasinya pada database. Ketika query terhaap MV dilakukan, query

optimizer akan mengetahui bilamana MV yang ada dapat

digunakan dan segera mengakses MV, bukan ke table detail

(query rewrite).

Membuat Materialized View

Privilege pembuatan MV haus di-grant secara langsung

jadi tidak melalui role. Untuk membuat MV pada Schema user

diperlukan priviledge system create materialized view dan

create table atau create any table serta previlege system

select any table. Sedangkan pembuatan MV pada schema user

lain memerlukan previledge system create any materialized view.

Untuk dapat membuat MV yang berisi summary jumlah

penduduk setiap kota dapat dibuat denagn cara berikut ini :

SQL> CREATE MATERIALIZED VIEW ivana.SNAP_KOTA

2 BUILD IMMEDIATE REFRESH FORCE

3 OF DEMAND AS

4 SELECT k.namakota, COUNT(p.nama)

jml_penduduk

5 FROM boy.kota k, boy.penduduk p

6 WHERE k.nokota=p.nokota

7 GROUP BY k.namakota;Materialize view created

Hasil :

SQL> SELECT * FROM ivana.Snap_kota;

NAMAKOTA JML_PENDUDUK

--------------------------------------------

Pontianak 700000

mempawak 1200000sintang 500000


51/53

46

5.4. Sequence

Sequence digunakan untuk membangkitkan serangkaian

nilai serial yang unik.

Membuat Sequence

INCREMENT BY berfungsi untuk mendefinisikan jumlah

incrementasi

setiap kali terjadi penyisipan record.

START WITH berfungsi untuk mendefisikan bilangan awal yang

dibangkitkan.

NOMAXVALUE tidak ada batas maximum bilangan sequence

yang

digenerate.

MAXVALUE mendefinisikan maximum bilangan sequence yang

digenerate.

CYCLE mendefinisikan bahwa jika telah bilangan sequencetelah

CREATE SEQUENCE nama_sequence

[INCREMENT BY integer]

[START WITH integer]

[MAXVALUE integer | NOMAXVALUE]

[MINVALUE integer | NOMINVALUE][CYCLE | NOCYCLE]

[CHACE | NOCHACE]SQL > CREATE SEQUENCE

seq_bulan

INCREMENT BY 1

START WITH 1

MAXVALUE 12


52/53

47

maximum, maka nilai akan diulang dari awal lagi

NOCYCLE tidak ada pengulangan bilangan bila telah sampai

nilai

maximum

CACHE bilangan sequence akan ditampung di buffer

NOCACHE bilangan sequence tidak akan ditampung di buffer.

Memodifikasi Sequence

Menghapus Sequence

ALTER SEQUENCE nama_sequence

[INCREMENT BY integer]

[START WITH integer]

[MAXVALUE integer | NOMAXVALUE]

[MINVALUE integer | NOMINVALUE]

[CYCLE | NOCYCLE]

[CHACE | NOCHACE]SQL > ALTER SEQUENCE seq_bulan

INCREMENT BY 2;

DROP SEQUENCE nama_sequence;


53/53

TUGAS PRAKTIKUM

1. Create view dengan nama DEPT50 yang berisi employee

number, employee last names and department number untuk

semua employee di department 50. Kolom-kolom view diberi

label sbb: EMPNO, EMPLOYEE, DEPNO

2. Tampilkan struktur dan isi dari DEPT50

3. Buatlah query untuk menampilkan 4 karyawan paling senior.

(Output: SENIOR, LAST_NAME, HIRE_DATE)

4. Cobalah drop view EMPLOYEE_VU dan DEPT50.

5. Create sebuah sequence yang akan digunakan pada kolom

primary key di tabel DEPT. Start 200, Max 1000, Increment 10.

Beri nama: DEPT_ID SEQ

6. Tuliskan SQL statement untuk melihat sequence_name,

min_value, max_value, increment_by dan last_number.

7. Tuliskan SQL statement untuk memasukkan 2 record ke dalam

tabel DEPT dengan menggunakan sequence DEPT_ID SEQ untuk

mengisikan department_id. (2 Department yang diinput

adalah: Education dan Administration)

8.

Pastikan kedua data sudah masuk dalam tabel DEPT9. Berapakah nilai department_id berikutnya?

10. Hapus sequence DEPT_ID SEQ

Modul Prak BDL - TA_1516_Genap

Documents