Aim:
To create an updatable view and read only view on different tables.
PROCEDURE
* A view created using already existing table.
* If there is any updation or insertion is performed in existing table,the view table is
updated and inserted.
UPDATABLE VIEW
* We can view the table and make any manipulations on the view.
* Any change made to the view will be reflected both in the original table and the view.
Syntax:
Create view <new table name>(column1…..column n)as select (column1…..,column n) from <existing table name>;
VIEW WITH READ ONLY
* We can only view the table, no alternation is performed.
* If we want to insert or update a record in case of view with read only,error message will be displayed.
Syntax:
Create view <new table name>(column1…..column n)as select (column1…..,column n) from <existing table name> with read only;
UPDATABLE VIEW EXAMPLE:
SQL> create table emp1(eno number(10),ename varchar(15),esal number,eadd varchar2(10))
Table created.
SQL> desc emp1;
Name Null? Type
———————————— ——– ————–
ENO NUMBER(10)
ENAME VARCHAR2(15)
ESAL NUMBER(5)
EADD VARCHAR2(10)
SQL> create view emp2 as select eno,ename,esal,eadd from emp1;
View created.
SQL> delete from emp1 where ename=’kannan’;
1 row deleted.
SQL> select * from emp1;
ENO ENAME ESAL EADD
—— ——– ——- ——–
1 arthi 2000 chennai
3 raju 9000 madurai
7 suda 8000 cuddalore
SQL> select * from emp2;
ENO ENAME ESAL EADD
—— ——— ——- ——-
1 arthi 2000 chennai
3 raju 9000 madurai
7 suda 8000 cuddalore
SQL> update emp2 set eadd=’karnataka’ where ename=’arthi’;
1 row updated.
SQL> select * from emp1;
ENO ENAME ESAL EADD
—— ———- —— ——-
1 arthi 2000 karnataka
3 raju 9000 madurai
7 suda 8000 cuddalore
SQL> select * from emp2;
ENO ENAME ESAL EADD
—— ——– —— ——-
1 arthi 2000 karnataka
3 raju 9000 madurai
7 suda 8000 cuddalore
READ ONLY VIEW EXAMPLE:
SQL> create view empv1 as select eno,ename from emp1 with read only;
View created.
SQL> insert into empv1 values(2,’arthi’);
insert into empv1 values(2,’arthi’)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> create view empv2 as select eno,eadd from emp2;
View created.
SQL> select * from empv2;
ENO EADD
—— ———
1 karnataka
3 madurai
7 cuddalore
SQL> select * from emp2;
ENO ENAME ESAL EADD
—— ———– ——– ———-
1 arthi 2000 karnataka
3 raju 9000 madurai
7 suda 8000 cuddalore
SQL> select * from emp1;
ENO ENAME ESAL EADD
—— ———— ——– ———-
1 arthi 2000 karnataka
3 raju 9000 madurai
7 suda 8000 cuddalore
SQL> insert into empv2 values(15,’arthg’);
1 row created.
SQL> select * from empv2;
ENO EADD
——- ———-
1 karnataka
3 madurai
7 cuddalore
15 arthg
SQL> create view empv3 as select eno from empv1 with read only;
View created.
SQL> select * from empv3;
ENO
——–
1
3
7
15
SQL> insert into empv3 values(34);
insert into empv3 values(34)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
RESULT
Thus the views were created for various tables and output was verified.
