Menciptakan View

View adalah tabel logika berbasis pada tabel atau view lain. View tidak memiliki data, tetapi hanya merupakan “jendela” terhadap data pada tabel. Tabel yang merupakan basis dari view disebut based table. Kita dapat mengartikan view sebagai query yang disimpan di database.

14.1. Keuntungan View

Penggunaan view memiliki beberapa keuntungan antara lain:

• Membatasi akses ke basis data karena view dapat menampilkan porsi tertentu basis data.
• Mengijinkan pengguna menciptakan query sederhana untuk menampilkan hasil dari suatu query yang rumit. Sebagai contoh, view memungkinkan pengguna untuk mendapatkan informasi dari beberapa tabel tanpa harus mengetahui perintah join.
• Menyediakan kemandirian data bagi pengguna dan program aplikasi. Sebuah view dapat digunakan untuk menampilkan data dari beberapa tabel.
• Menampilkan data dalam perspektif yang berbeda.

14.2. Menciptakan View

View dapat diciptakan dengan melekatkan subquery menggunakan perintah CREATE VIEW. Kita harus memiliki privilege CREATE VIEW untuk dapat menciptakan view dan area penyimpanan dimana objek tersebut diciptakan. Kita akan membahas masalah privilege pada bab Mengontrol Hak Akses.

Sintaks

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view
[(alias[, alias] . . .)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];

dimana:

• OR REPLACE
  • memodifikasi view yang telah ada.
• FORCE
  • menciptakan view, baik terdapat base table ataupun tidak.
• NOFORCE
  • menciptakan view hanya jika terdapat base table.
• view
  • nama view yang akan diciptakan.
• alias
  • menentukan nama lain kolom dalam query.
• subquery
  • perintah SELECT lengkap.
• WITH CHECK OPTION
  • menentukan baris data yang boleh di-insert atau di-update.
• constraint
  • nama yang diberikan untuk constraint CHECK OPTION.
• WITH READ ONLY
  • tidak mengijinkan operasi DML dalam view ini.

Aturan

• Query dalam view dapat mengandung perintah SELECT kompleks, termasuk join, group dan subquery.
• Query dalam view tidak dapat mengandung ORDER BY.
• Jika nama constraint tidak disebutkan, system memberikan nama default berformat SYS_Cn.
• Pilihan OR REPLACE dapat digunakan untuk merubah definisi view tanpa menghapus dan menciptakannya ulang.

Perbandingan View Sederhana dan Kompleks

• View Sederhana
  • Jumlah table hanya satu
  • Mengandung fungsi
  • Tidak mengandung grup data (DISTINCT atau group function)
  • Tidak terdapat DML di dalam View
• View Kompleks
  • Jumlah table lebih dari satu
  • Tidak mengandung fungsi
  • Mengandung grup data (DISTINCT atau group function)
  • Terdapat DML di dalam View

Contoh:

Menciptakan view yang terdiri dari kolom id, last_name, dan title untuk karyawan yang bekerja pada department 45.

CREATE VIEW empvu45
AS SELECT id, last_name, title
FROM employee
WHERE dept_id = 45;

Menampilkan view empvu45.

SELECT *
FROM empvu45;

Menciptakan view yang terdiri dari kolom first_name dengan alias FIRST, last_name dengan alias LAST, dan salary dengan alias MONTHLY_SALARY untuk karyawan yang bekerja pada departemen 42.

CREATE OR REPLACE VIEW salvu41
AS SELECT id ID, first_name FIRST, last_name LAST,
salary MONTHLY_SALARY
FROM employee
WHERE dept_id=41;

Berikut ini adalah cara alternatif untuk mendefinisikan alias

CREATE OR REPLACE VIEW salvu41 (id, first, last, monthly_salary)
AS SELECT id, first_name FIRST, last_name LAST,
salary MONTHLY_SALARY
FROM employee
WHERE dept_id=41;

Menciptakan view yang berisi nama departemen, gaji minimum, gaji maksimum, dan gaji rata-rata berdasarkan departemen.

CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS SELECT d.name, min(e.salary), max(e.salary), avg(e.salary)
FROM employee e, departement d
WHERE e.dept_id = d.id
GROUP BY d.name;

View yang mengandung semua column dari tabel S_EMP dengan department id 41, dan menambahkan klausa WITH CHECK OPTION.

CREATE OR REPLACE VIEW empvu41
AS SELECT *
FROM S_EMP
WHERE dept_id = 41
WITH CHECK OPTION CONSTRAINT empvu41_ck;

Memodifikasi view EMPVU45. Tidak mengijinkan operasi DML dalam view tersebut.

CREATE OR REPLACE VIEW empvu45 (id_number, employee, job)
AS SELECT id, last_name, title
FROM s_emp
WHERE dept_id = 45
WITH READ ONLY;

14.3. Melakukan Operasi DML Terhadap View

Kita dapat melakukan operasi-operasi DML terhadap data melalui view yang disediakan oleh operasi-operasi yang mengikuti aturan-aturan berikut ini:
Kita dapat menghapus data dari view kecuali ia mengandung salah satu dari hal-hal berikut ini:

• Group functions
• Klausa GROUP BY
• Perintah DISTINCT

Kita dapat memodifikasi data didalam view kecuali ia mengandung salah satu dari hal-hal diatas dan salah satu dari hal-hal berikut ini:

• Kolom-kolom yang didefinisikan dengan ekspresi, sebagai contoh, SALARY*12
• Pseudocolumn ROWNUM

Kita dapat menambahkan data melalui view kecuali ia mengandung salah satu dari hal-hal diatas dan terdapat column NOT NULL didalam table asalnya yang tidak dipilih didalam view. Seluruh nilai-nilai yang diperlukan harus ada dalam view. Harap diingat bahwa kita menambahkan data secara langsung kedalam table yang mendasarinya melalui view.

Kita dapat memastikan bahwa ketika kita menambahkan atau memodifikasi data didalam view sederhana, data yang ditambahkan atau dimodifikasi tersebut dapat dilihat melalui view.

Contoh:

Menciptakan view yang mengandung seluruh column dari table EMPLOYEE untuk department 45. Tambahkan dengan klausa WITH CHECK OPTION.

CREATE OR REPLACE VIEW empvu41
AS SELECT *
FROM employee
WHERE dept_id = 41
WITH CHECK OPTION CONSTRAINT empvu41_ck;

Berusaha mengubah department number untuk employee 16 menjadi department 42 melalui view.

UPDATE empvu41
SET dept_id = 42
WHERE id = 16;

Catatan:

Tidak ada data yang terupdate karena jika department diubah menjadi 42, view tidak akan dapat lagi melihat employee tersebut. Oleh sebab itu, dengan klausa WITH CHECK OPTION, view hanya dapat melihat employee yang berada pada department 41, dan tidak mengijinkan department number untuk employee tersebut untuk diubah melalui view.

Kita dapat menjamin bahwa tidak ada operasi-operasi DML yang muncul pada view kita dengan menciptakannya menggunakan opsi WITH READ ONLY.

Contoh:

Memodifikasi view EMPVU45. Tidak mengijinkan operasi-operasi DML terhadap view tersebut. Cobalah untuk menghapus data melalui view tersebut.

CREATE OR REPLACE VIEW empvu45
(id_number, employee, job)
AS SELECT id, last_name, title
FROM employee
WHERE dept_id = 45
WITH READ ONLY;

DELETE FROM empvu45
WHERE id_number = 10;

14.4. Mengkonfirmasi View dan Strukturnya

Setelah view diciptakan maka informasi view tersebut akan tersimpan dalam data dictionary (kamus data) Oracle, sama seperti objek-objek lainnya. Kita dapat melihatnya menggunakan tabel USER_OBJECTS atau USER_VIEWS.

Contoh:

Menampilkan struktur tabel USER_VIEWS.

DESCRIBE user_views

Menampilkan nama-nama view telah diciptakan.

SELECT * FROM user_views;

14.5. Menghapus View

Gunakan perintah DROP VIEW untuk menghapus view. Perintah ini menghapus definisi view dari database. Menghapus view tidak akan mempengaruhi base table dari view tersebut. Menghapus view akan mempengaruhi view atau aplikasi lain yang menggunakan view yang telah dihapus tersebut. Perintah ini hanya dapat dilakukan oleh pemilik view atau user yang memiliki system privilege DROP ANY VIEW.

Sintaks

DROP VIEW view

Contoh:

Menghapus view EMPVU45.

DROP VIEW empvu45;

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.

For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Get Free Oracle Magazine

14.6. Latihan

1. Buatlah sebuah view yang berisi kode, nama belakang dan jabatan pegawai, khusus untuk pegawai yang bekerja pada departemen dengan kode 45. Beri nama view tersebut dengan EMPVU45

CREATE VIEW empvu45
AS SELECT id, last_name, title
FROM employee
WHERE dept_id = 45;

Tampilkan view yang telah Anda buat tersebut.

SELECT *
FROM empvu45;

2. Buatlah sebuah view yang berisi kode pegawai, nama depan pegawai dengan alias FIRST, nama belakang pegawai dengan alias LAST, dan gaji bulanan pegawai dengan alias MONTHLY_SALARY, khusus untuk pegawai yang bekerja pada departemen dengan kode 41. Beri nama view tersebut dengan SALVU41.

CREATE VIEW salvu41
AS SELECT id, first_name FIRST, last_name LAST,
salary MONTHLY_SALARY
FROM employee
WHERE dept_id = 41;

Tampilkan view yang telah Anda buat tersebut.

SELECT *
FROM salvu41;

3. Modifikasilah view EMPVU45 yang telah Anda buat. Rubahlah agar kode pegawai memiliki judul ID_NUMBER, nama belakang pegawai memiliki judul EMPLOYEE, sedangkan jabatan pegawai memiliki judul JOB.

CREATE OR REPLACE VIEW empvu45 (id_number, employee, job)
AS SELECT id, last_name, title
FROM employee
WHERE dept_id = 45;

4. Buatlah sebuah view yang berisi nama departemen, gaji minimum, gaji maksimum dan rata-rata gaji tiap-tiap departemen tersebut. Beri nama dengan DEPT_SUM_VU.

CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS SELECT d.name, min(e.salary), max(e.salary), avg(e.salary)
FROM employee e, department d
WHERE e.dept_id = d.id
GROUP BY d.name;

Tampilkan struktur dari view DEPT_SUM_VU tersebut.

DESC dept_sum_vu;

Tampilkan isi dari view DEPT_SUM_VU tersebut.

SELECT *
FROM dept_sum_vu;

5. Buatlah sebuah view yang berisi semua column table EMPLOYEE khusus untuk pegawai yang bekerja pada departemen dengan kode 41. Tambahkan klausa WITH CHECK OPTION pada view tersebut.

CREATE VIEW empvu41
AS SELECT *
FROM employee
WHERE dept_id = 41
WITH CHECK OPTION CONSTRAINT empvu41_ck;

6. Modifikasilah view EMPVU41 dengan mengganti kode departemen menjadi 42 untuk pegawai dengan kode 16.

UPDATE empvu41
SET dept_id = 42
WHERE id = 16;

7. Modifikasilah view EMPVU45 sehingga tidak memperbolehkan operasi DML terhadap view tersebut.

CREATE OR REPLACE VIEW empvu45 (id_number, employee, job)
AS SELECT id, last_name, title
FROM employee
WHERE dept_id = 45
WITH READ ONLY;

8. Hapuslah isi dari view EMPVU45 khusus untuk pegawai dengan kode 10. Apa yang terjadi ?

DELETE empvu45
WHERE id_number = 10;

9. Tampilkan struktur dari table data dictionary USER_VIEWS.

DESCRIBE user_views;

10. Tampilkan isi dari table data dictionary USER_VIEWS.

SELECT *
FROM user_views;

11. Hapuslah view EMPVU45.

DROP VIEW empvu45;

12. Buatlah view dengan nama EMP_VU yang berisi kode, nama belakang pegawai dan kode departemen tempat pegawai tersebut bekerja. Ubahlah judul dari nama belakang pegawai menjadi EMPLOYEE.

CREATE VIEW emp_vu (id, employee, dept_id)
AS SELECT id, last_name, dept_id
FROM employee;

Tampilkan isi dari view EMP_VU.

SELECT *
FROM emp_vu;

13. Buatlah script untuk menampilkan definisi dari view tersebut. Nama view diisikan lewat keyboard. Simpan script tersebut dengan nama VIEW_SCRIPT1.SQL. Eksekusi script tersebut untuk melihat definisi dari view EMP_VU.

SET ECHO OFF
ACCEPT view_name PROMPT ‘Please insert the view name : ‘
DESCRIBE &view_name

14. Ubahlah kode departemen Smith menjadi 37 pada view EMP_VU.

UPDATE emp_vu
SET dept_id = 37
WHERE employee = ‘Smith’;

Tampilkan perubahan tersebut.

SELECT *
FROM emp_vu;

15. Buatlah view dengan nama MNS_VU yang berisi kode pegawai, nama lengkap pegawai dan nama departemen tempat pegawai tersebut bekerja, khusus untuk pegawai yang bekerja pada departemen Finance dan Sales.

CREATE VIEW mns_vu (id, employee, department)
AS SELECT e.id, e.first_name | | ‘ ‘ | | e.last_name, d.name
FROM employee e, department d
WHERE e.dept_id = d.id AND d.name IN(’Finance’,'Sales’);

Tampilkan struktur dan isi dari view MNS_VU.

DESCRIBE mns_vu;

16. Tampilkan definisi dari view MNS_VU dengan mengeksekusi script VIEW_SCRIPT1.SQL yang telah Anda buat.

@VIEW_SCRIPT1;

17. Tampilkan department name dan jumlah karyawan tiap-tiap departemen pada MNS_VU tersebut.

SELECT department, COUNT(*)
FROM mns_vu
GROUP BY department;

18. Modifikasi view EMP_VU sehingga hanya berisi pegawai yang bekerja pada departemen dengan kode 37. Tambahkan klause WITH CHECK OPTION sehingga kode departemen tidak dapat dimodifikasi.

CREATE OR REPLACE VIEW emp_vu (id, employee, dept_id)
AS SELECT id, last_name, dept_id
FROM employee
WHERE dept_id = 37;

Tampilkan isi dari view EMP_VU.

SELECT *
FROM emp_vu;

19. Modifikasilah kode departemen Smith menjadi 54 pada view EMP_VU tersebut. Apa yang terjadi ?

UPDATE emp_vu
SET dept_id = 54
WHERE employee = ‘Smith’;

20. Modifikasilah view MNS_VU sehingga hanya dapat ditampilkan antara 1:00 P.M. dan 4:00 P.M.

CREATE OR REPLACE VIEW mns_vu (id, employee, department)
AS SELECT e.id, e.first_name | | ‘ ‘ | | e.last_name, d.name
FROM employee e, department d
WHERE e.dept_id = d.id AND d.name IN(’Finance’,'Sales’)
AND ( TO_CHAR(sysdate,’fmHH:SS PM’)  BETWEEN ‘1:00 PM’ AND ‘4:00 PM’ );

Silahkan melanjutkan membaca pembahasan Menciptakan Index

Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).