SQL with Oracle , View pada Tabel

Masih dalam pembahasan DDL dalam SQL, dalam mendefinisikan data ada hal lain yang dapat kita lakukan yaitu membuat suatu indeks dan view untuk mempermudah dalam pegambilan data berdasarkan query tertentu.

View

Kita dapat membuat view sebagai tabel virtual, yang dapat menbungkus beberapa query atau suatu query yang kompleks. Ketika kita membuat view yang mengeset suatu data dari tabel maka ketika data dari tabel yang bersangkutan berubah, maka ketika itu pula yang dapat kita lihat data memalui view-pun berubah. Perlu diingat bahwa membuat suatu view bukan berarti membuat sebuah tabel baru secara fisik, melainkan secara virtual.

Sebenarnya kegunaan view ini akan terasa jika kita hendak memanggil dengan berulang kali suatu statement SQL yang sama.

CREATE VIEW [OR REPLACE] nama_view [(kolom1, kolom2, ...)] AS

SELECT kolom_yang_dimaksud

FROM nama_tabel

WHERE kondisi

GROUP BY kolom1,kolom2,…

HAVING kondisi_Aggregate

ORDER BY kolom_tertentu

Baris yang utama adalah baris pertama, karena disana terdapat definisi atas nama view yang dibuat. Selanjutnya ia diikuti pernyataan seleksi secara umum. Seleksi yang disebutkan diatas tidak terbatas pada sintaks diatas saja, namun dapat pula digunakan subquery maupun query literal.

Untuk mempermudah pemahaman, kita coba untuk memulainya dari studi kasus sederhana, terlebih dahulu kita membuat beberapa tabel dengan struktur tabel-ERD sebagai berikut:

Lalu kita isikan beberapa data sebagaimana tabel berikut:

Tabel perusahaan

id	nama	almat	kota	negara
1	Phone Company	111 1st Street	Atlanta	GA
2	Power Company	222 2nd Street	Jacksonville	FL
3	Record Club	333 3rd Avenue	Los Angeles	CA
4	Software Company	444 4th Drive	San Francisco	CA
5	Cable TV Company	555 5th Drive	Austin	TX
6	Joe’s Car Palace	1000 Govt. Blvd	Miami	FL
7	S.C. Student Loan	25 College Blvd	Columbia	SC
8	Florida Water Company	1883 Hwy 87	Navarre	FL
9	U-O-Us Insurance Company	295 Beltline Hwy	Macon	GA
10	Debtor’s Credit Card	115 2nd Avenue	Newark	NJ

Tabel akun_bank

id_akun	type	neraca	bank
1	Checking	500	First Federal
2	Money Market	1200	First Investor’s
3	Checking	90	Credit Union
4	Savings	400	First Federal
5	Checking	2500	Second Mutual
6	Business	4500	Fidelity

Tabel rekening

id	id_perusahaan	jumlah	id_akun
1	2	75	1
2	1	125	1
3	3	25	2
4	4	250	1
5	7	200	3
6	6	350	5
7	5	35	6
8	8	20	1
9	9	125	5
10	10	35	4

Untuk pertama kali, kita akan membuat sebuah view yang sederhana. Kita akan menampilkan DEBTS sebagai view yang memuat nama perusahaan, jumlah debts dan id_akun.

CREATE VIEW debts AS

SELECT p.nama, r.jumlah, r.id_akun

FROM perusahaan p, rekening r

WHERE r.id_perusahaan = p.id;

select * from debts;

Selanjutnya membuat view untuk menampilakan kartu kredit debitor.

CREATE VIEW creditcard_debts AS

SELECT * FROM DEBTS

WHERE id_akun = 4;

SELECT * FROM creditcard_debts;

Begitu seterusnya untuk view yang lain, yaitu :

CREATE VIEW OR REPLACE info_perusahaan (nama, negara) AS

SELECT nama, negara FROM perusahaan;

SELECT * FROM info_perusahaan;

Membuat view dengan merubah tampilan nama kolom.

CREATE VIEW OR REPLACE amplop (nama, alamat) AS

SELECT nama, alamat||’ ‘||kota||’, ‘||negara

FROM perusahaan;

SELECT * FROM amplop;

Membuat view untuk mengetahui keterangan rekening tiap perusahaan

CREATE VIEW OR REPLACE rekening_due (nama, jumlah, neraca_akun, bank) AS

SELECT perusahaan.nama, rekening.jumlah, akun_bank.neraca, akun_bank.bank

FROM perusahaan, rekening, akun_bank

WHERE rekening.id_akun = akun_bank.id_akun

AND rekening.id_perusahaan = perusahaan.id;

SELECT * FROM rekening_due

WHERE neraca_akun > 500;

Membuat view untuk mengetahui jumlah rekening dan total terjumlah perusahaan per negara

CREATE VIEW OR REPLACE rek_negara (negara, jumlah_rekening, total_jumlah) AS

SELECT DISTINCT perusahaan.negara, COUNT(rekening.id_perusahaan), SUM(rekening.jumlah)

FROM rekening, perusahaan

GROUP BY perusahaan.negara;

SELECT * FROM rek_negara;

Membuat view untuk melihat perubahan akhir dari total baru setelah di kali 1.10

CREATE VIEW OR REPLACE pembayaran_akhir (nama, total_baru, tipe_akun) AS

SELECT perusahaan.nama, rekening.jumlah * 1.10, akun_bank.tipe

FROM perusahaan, rekening, akun_bank

WHERE perusahaan.id = rekening.id_perusahaan AND rekening.id_akun = akun_bank.id_akun;

SELECT * FROM pembayaran_akhir;

Sekarang kita telah mempelajari bagaimana memanfaatkan view terhadap satu atau lebih tabel fisik yang ada dalam database. Selain menggunakan statement SELECT, dalam memanfaatkan VIEW kita juga dapat memanipulasi data yang ada pada tabel fisik, perubahan data merupakan perubahan permanent dalam tabel fisik yang sebenarnya. Dalam menipulasi data kita dapat menggunakan statement yang ada pada DML seperti biasa, yaitu UPDATE, INSERT, dan DELETE.

Meng-update data jumlah dengan jumlah * 1.10 pada pembayaran akhir2 dan menambah 10 poin untuk jumlah > 100.

SELECT * FROM rekening;

CREATE VIEW OR REPLACE pembayaran_akhir2 AS

SELECT * FROM rekening;

UPDATE pembayaran_akhir2

SET jumlah = jumlah * 1.10;

UPDATE pembayaran_akhir2

SET jumlah = jumlah + 10

WHERE jumlah > 100;

SELECT * FROM pembayaran_akhir2;

Menambahkan data pada tabel perusahaan, akun_bank dan rekening melalui VIEW

CREATE VIEW OR REPLACE tambah_perusahaan AS

SELECT * FROM perusahaan;

INSERT INTO tambah_perusahaan

VALUES (11, ‘Valuaded Credit Card’, ‘235 1nd Avenue’, ‘Newark’, ‘NJ’);

SELECT * FROM tambah_perusahaan;

SELECT * FROM perusahaan;

CREATE VIEW OR REPLACE tambah_akun_bank AS

SELECT * FROM akun_bank;

INSERT INTO tambah_akun_bank

VALUES (7, ‘Savings’, 500, ‘Fidelity’);

SELECT * FROM tambah_akun_bank;

CREATE VIEW OR REPLACE tambah_rekening AS

SELECT * FROM rekening;

INSERT INTO tambah_rekening

VALUES (11, 11, 235, 7);

SELECT * FROM tambah_rekening;

Menghapus data pada tabel perusahaan, akun_bank dan rekening melalui VIEW

DELETE FROM tambah_perusahaan WHERE id = 11;

DELETE FROM tambah_akun_bank WHERE id_akun = 7;

DELETE FROM tambah_rekening WHERE id = 11;

SELECT * FROM tambah_perusahaan;

SELECT * FROM tambah_akun_bank;

SELECT * FROM tambah_rekening;

Tidak selamanya view akan digunakan dalam membantu memudahkan query. Untuk itu perlu melakukan penghapusan view jika memang sudah tidak dibutuhkan. Menghapus view tidak jauh berbeda dengan menghapus tabel, yaitu menggunakan drop, dengan sintaks berikut:

DROP VIEW namaview

Untuk mencobanya kita delete view untuk melihat kartu kredit milik debitor’s:

DROP VIEW creditcard_debts