Học Oracle cơ bản (Bài 12)

T¹o View Néi dung M« t¶ c¸c view T¹o view Truy xuÊt d÷ liÖu tõ view Thay ®æi ®Þnh nghÜa c¸c view Insert, update vµ delete data th«ng qua view Xo¸ view     C¸c ®èi t­îng trong database Description Basic unit of storage; composed of rows and columns Logically represents subsets of data from one or more tables Generates primary key values Improves the performance of some queries Alternative name for an object Object Table View  Sequence Index Synonym View lµ g×? EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ----- --------- ----- ----- ------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 EMP Table V× sao l¹i sö dông view? Giíi h¹n d÷ liÖu truy xuÊt T¹o ra c¸c query phøc t¹p dÔ dµng h¬n Cho phÐp d÷ liÖu ®éc lËp Tr×nh bµy c¸ch nh×n kh¸c nhau cña cïng mét d÷ liÖu View ®¬n gi¶n vµ view phøc t¹p Feature Simple Views Complex Views Number of tables One One or more Contain functions No Yes Contain groups of data No Yes DML through view Yes Not always T¹o mét View Cã thÓ nhóng subquery vµo trong c©u lÖnh CREATE VIEW. Subquery cã thÓ chøa c©u lÖnh SELECT phøc t¹p. Subquery kh«ng ®­îc chøa mÖnh ®Ò ORDER BY . CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] T¹o mét View T¹o view EMPVU10, chøa th«ng tin chi tiÕt cña c¸c employees trong department 10. M« t¶ cÊu tróc cña view SQL> DESCRIBE empvu10 SQL> CREATE VIEW empvu10 2 AS SELECT empno, ename, job 3 FROM emp 4 WHERE deptno = 10; View created. T¹o mét View T¹o mét view b»ng c¸ch sö dông bÝ danh cét trong subquery Lùa chän c¸c cét tõ view nµy b»ng bÝ danh cña c¸c cét SQL> CREATE VIEW salvu30 2 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, 3 sal SALARY 4 FROM emp 5 WHERE deptno = 30; View created. Truy xuÊt d÷ liÖu tõ View EMPLOYEE_NUMBER NAME SALARY --------------- ---------- --------- 7698 BLAKE 2850 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 7521 WARD 1250 6 rows selected. SQL> SELECT * 2 FROM salvu30; Truy xuÊt d÷ liÖu tõ View SQL*Plus SELECT * FROM empvu10; Söa ch÷a mét View Ta cã thÓ söa EMPVU10 view b»ng c¸c sö dông mÖnh ®Ò CREATE OR REPLACE VIEW . C¸c bÝ danh cét trong mÖnh ®Ò CREATE VIEW liÖt kª theo trËt tù nh­ trong c©u lÖnh subquery. SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10; View created. T¹o mét View phøc t¹p T¹o mét view phøc t¹p mµ chøa c¸c hµm lÊy d÷ liÖu tõ hai b¶ng. SQL> CREATE VIEW dept_sum_vu 2 (name, minsal, maxsal, avgsal) 3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal), 4 AVG(e.sal) 5 FROM emp e, dept d 6 WHERE e.deptno = d.deptno 7 GROUP BY d.dname; View created. Nguyªn t¾c thùc hiÖn lÖnh DML trªn View Cã thÓ ch¹y c©u lÖnh DML trªn mét view. Kh«ng thÓ xo¸ mét hµng nÕu view chøa: Hµm nhãm Mét mÖnh ®Ò GROUP BY Tõ kho¸ DISTINCT Nguyªn t¾c thùc hiÖn lÖnh DML trªn View Kh«ng thÓ söa ch÷a d÷ liÖu trªn mét view nÕu: C¸c diÒu kiÖn trªn slide tr­íc C¸c cét ®Þnh nghÜa bëi biÓu thøc To¸n tö gi¶ ROWNUM B¹n kh«ng thÓ thªm d÷ liÖu nÕu: C¸c view chøa ®iÒu kiÖn nh­ trong slide tr­íc Cã cét NOT NULL trong b¶ng gèc mµ kh«ng ®­îc select bëi view Sö dông mÖnh ®Ò WITH CHECK OPTION Cã thÓ ®¶m b¶o r»ng DML trªn view ë trong domain cña view b»ng c¸ch sö dông mÖnh ®ÒWITH CHECK OPTION . BÊt cø thay ®æi department number trong view sÏ kh«ng ®­îc v× nã phô thuéc vµo WITH CHECK OPTION constraint. SQL> CREATE OR REPLACE VIEW empvu20 2 AS SELECT * 3 FROM emp 4 WHERE deptno = 20 5 WITH CHECK OPTION CONSTRAINT empvu20_ck; View created. C¸c thao t¸c bÞ cÊm thùc hiÖn trªn VIEW B¹n cã thÓ ®¶m b¶o r»ng kh«ng cã to¸n h¹ng nµo b»ng c¸ch thªm vµo mÖnh ®Ò WITH READ ONLY vµo ®Þnh nghÜa view SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10 6 WITH READ ONLY; View created. BÊt cø viÖc thùc thi c¸c c©u lÖnh DML trªn c¸c hµng view sÏ bÞ cÊm Xo¸ mét View Xo¸ mét viªw kh«ng lµm mÊt d÷ liÖu trªn c¸c b¶ng base SQL> DROP VIEW empvu10; View dropped. DROP VIEW view; Tæng kÕt Mét view ®­îc dÉn xuÊt tõ nhiÒu b¶ng hay nhiÒu view. View cung cÊp ­u ®iÓm sau: Giãi h¹n d÷ liÖu truy xuÊt Lµm ®¬n gi¶n ho¸ c¸c query Cung cÊp d÷ liÖu ®éc lËp Cho phÐp nhiÒu view cña cïng mét d÷ liÖu Cã thÓ xo¸ mµ kh«ng xo¸ d÷ liÖu