1 / 7

VIEWS

VIEWS. A view is like a window through which one can view or change information in the table. Although view does not contain any data of their own . They look like tables with some restrictions. Since views does not occupy any memory space they are called ‘ Virtual Tables ’.

ivo
Download Presentation

VIEWS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VIEWS • A view is like a window through which one can view or change information in the table. • Although view does not contain any data of their own . They look like tables with some restrictions. • Since views does not occupy any memory space they are called ‘Virtual Tables’. The two main reasons for using views are • Security • Convenience Syntax: CREATE [OR REPLACE ] VIEW [ ALIAS [,ALIAS]…] AS QUERY [WITH CHECK OPTION [CONSTRAINT CONSTRAINT_NAME ]]

  2. OR REPLACE : Recreate the views if it already exits • ALIAS : View name • WITH CHECK OPTION: restricts inserts and updates performed through the view Consider the following EMP table SQL> SELECT * from EMP; ENO ENAME DEPT POST BASIC --------- ---------- --------------- --------------- --------- 111 kumar sales manager 16000 222 umar purchase account 7000 333 vino sales clerk 5000 444 raj marketing manager 15000 555 muniya purchase manager 15000 666 omna sales asst.manager 12000

  3. Ex: 1 One might define a view of EMP table called EMPVIEW, which contains employee no, name, department, post and basic only the records in post as manager. SQL> create view EMPVIEW as select * from EMP where post = 'manager'; Output of EMPVIEW SQL> select * from EMPVIEW ; ENO ENAME DEPT POST BASIC --------- ---------- --------------- --------------- --------- 111 kumar sales manager 16000 444 raj marketing manager 15000 555 muniya purchase manager 15000

  4. Ex:2 One might define a view of EMP table called EMPVIEW, which contains employee no, name, department, post and basic only the records in post as clerk. SQL> create view EMPVIEW as select * from EMP where post = ‘clerk'; Output Error : name is already used by an existing object But I want to create a view as the same name EMPVIEW, Solution is SQL> create or replace view EMPVIEW as select * from EMP where post = 'clerk'; Output of EMPVIEW SQL> select * from EMPVIEW; ENO ENAME DEPT POST BASIC --------- ---------- --------------- --------------- --------- 333 vino sales clerk 5000

  5. Now SQL> insert into EMPVIEW values(1,'jeya','purchase','clerk',10000); 1 row created. SQL> insert into EMPVIEW values(2,'selvi','purhase','manager',10000); 1 row created. After that Output of EMPVIEW SQL> select * from EMPVIEW;

  6. ENO ENAME DEPT POST BASIC --------- ---------- --------------- --------------- --------- 333 vino sales clerk 5000 1 jeya purchase clerk 10000 Output of EMP SQL> select * from EMP; ENO ENAME DEPT POST BASIC --------- ---------- --------------- --------------- --------- 111 kumar sales manager 15000 222 umar purchase account 7000 333 vino sales clerk 5000 444 raj marketing manager 15000 555 muniya purchase manager 15000 666 omna sales asst.manager 12000 1 jeya purchase clerk 10000 2 selvi purhase manager 10000

  7. Ex: 3 SQL> create or replace view EMPVIEW as select * from EMP where post = 'clerk‘ WITH CHECK OPTION CONSTRAINT post; Output of EMPVIEW SQL> select * from EMPVIEW;

More Related