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

T¹o Views Môc ®Ých Sau bµi häc nµy, b¹n cã thÓ: Miªu t¶ view T¹o view LÊy d÷ liÖu tõ view Thay ®æi ®Þnh nghÜa cña view Insert, update, vµ delete d÷ liÖu qua view. Xãa bá view     §èi t­îng cña CSDL Miªu t¶ §¬n vÞ c¬ së ®Ó l­u tr÷ d÷ liÖu; gåm hµng vµ cét. BiÓu diÔn tËp d÷ liÖu tõ mét hoÆc nhiÒu b¶ng mét c¸ch logic T¹o gi¸ trÞ cho primary key. C¶i thiÖn sù thùc hiÖn cña mét sè query Tªn kh¸c cho ®èi t­îng §èi t­îng 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 T¹i sao l¹i sö dông View? H¹n chÕ truy nhËp CSDL Lµm c¸c query phøc t¹p thµnh ®¬n gi¶n Cho phÐp d÷ liÖu ®éc lËp. BiÓu diÔn c¸ch nh×nh kh¸c nhau trªn cïng d÷ liÖu. View ®¬n gi¶n vµ phøc t¹p §Æc tÝnh View ®¬n gi¶n Phøc t¹p Sè b¶ng Mét One or more Chøa hµm? Kh«ng Yes Chøa nhãm d÷ liÖu Kh«ng Yes DML qua view Cã Tuú lóc T¹o View B¹n chÌn subquery gi÷a lÖnh CREATE VIEW. Subquery cã thÓ chøa SELECT phøc t¹p Subquery kh«ng chøa lÖnh ORDER BY. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] T¹o View T¹o view, EMPVU10. Dïng lÖnh DESCRIBE ®Ó miªu t¶ cÊu tróc 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 View T¹o view, sö dông alias cña cét trong subquery. Chän cét tõ view qua aliases SQL> CREATE VIEW salvu30 2 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, 3 sal SALARY 4 FROM emp 5 WHERE deptno = 30; View created. LÊy 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 vÊn View SQL*Plus SELECT * FROM empvu10; Thay ®æi View Thay ®æi EMPVU10 nhê lÖnh CREATE OR REPLACE. Aliases cña cét trong CREATE VIEW ®­îc liÖt kª cïng trËt tù nh­ cét trong 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 View phøc t¹p T¹o view phøc t¹p 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. Quy ®Þnh ®Ó thùc hiÖn ho¹t ®éng DML trªn view B¹n cã thÓ thùc hiÖn ho¹t ®éng DML trªn view ®¬n gi¶n. Kh«ng thÓ xo¸ hµng nÕu view cã chøa: C¸c hµm nhãm MÖnh ®Ò GROUP BY Tõ kho¸ DISTINCT Quy ®Þnh ®Ó thùc hiÖn ho¹t ®éng DML trªn view B¹n kh«ng thÓ thay ®æi d÷ liÖu trªn view nÕu: Cã ®iÒu kiÖn nh­ b¶n thuyÕt minh (slide) tr­íc Cét ®­îc x¸c ®Þnh nhê biÓu thøc Cét chøa ROWNUM B¹n kh«ng thÓ thªm d÷ liÖu nÕu: Cã ®iÒu kiÖn nh­ b¶n thuyÕt minh tr­íc ho¨c nh­ ë trªn Cã cét NOT NULL trong b¶ng c¬ së mµ view ch­a chän. Sö dông WITH CHECK OPTION §¶m b¶o lµ DML trªn view ë trªn mét miÒn víi mÖnh ®Ò WITH CHECK OPTION. Mäi thay ®æi vÒ deptno trªn view sÏ kh«ng ®­îc thùc hiÖn. 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. Kh«ng cho ho¹t ®éng DML trªn view. Sö dông WITH READ ONLY trong ®Þnh nghÜa cñ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. Kh«ng thùc hiÖn ®­îc ho¹t ®éng DM trªn view võa t¹o. Xo¸ bá View Xo¸ bá view kh«ng ¶nh h­ëng ®Õn b¶ng c¬ së. SQL> DROP VIEW empvu10; View dropped. DROP VIEW view; Tãm t¾t View cã ®­îc tõ d÷ liÖu cña b¶ng hoÆc view kh¸c. View cung cÊp: H¹n chÕ truy nhËp CSDL. Lµm ®¬n gi¶n query Cung cÊp sù ®éc lËp cña d÷ liÖu. Cho phÐp nhiÒu view trªn cïng d÷ liÖu. Cã thÓ xo¸ kh«ng cÇn xo¸ bá b¶ng c¬ së. Thùc hµnh T¹o view ®¬n gi¶n T¹o view phøc t¹p T¹o view víi with check option Thö thay ®æi d÷ liÖu trong view HiÖn view. Xo¸ view.