View In Oracle

View In Oracle

Logical representation of subset of data is known as "View".

Types Of View::

1.Simple View         --->DML (INSERT,UPDATE,DELETE Possible)
2.Complex View --->DML (INSERT,UPDATE,DELETE Not Possible)

Some Important Points about View::

        1.The Simple view contain only one table.
2.The Complex View Contains More than one table.
3.If we insert anything to view then the Values are inserted into the base table also.
4.Primary & Not null Values can not Insert
5.Using Group Function,Group By,DISTINCT,Expression we can't Perform the DML Operations in view.

Advantages of View::

1.Restrict the data Access
2.Make Complex Query Easy
3.To Provide data independancy

1.Simple View::

Contains Only one table.

Syntax::

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

Example::

SQL> CREATE OR REPLACE VIEW Test_vw AS
  2  SELECT Name
  3  FROM TEST_LDR
  4  WHERE Name='xxx';

View created.

SQL> Select * from Test_vw;

NAME
-------------
xxx

Updating Simple View::

SQL> select * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxx                                  2000
yyy                                  3000

SQL> UPDATE Test_vw SET Name='xxxx' WHERE Name='xxx';

1 row updated.

SQL> select * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxxx                                 2000
yyy                                  3000

2.Complex View::

It Contains Multiple tables.

Syntax::

CREATE OR REPLACE VIEW view_name AS
  SELECT t1.column,t2.Column
  FROM tables t1,table t2
  [WHERE conditions];

Example::

SQL>CREATE OR REPLACE VIEW Test_Comp_vw AS
   SELECT e.Employee_Id,e.First_Name,d.Department_Name
   FROM Employees e,Departments d
   WHERE e.Department_Id=d.Department_Id AND d.Department_Id='10';

View created.

SQL> SELECT * FROM TEST_Comp_vw;

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_NAME
----------- -------------------- ------------------------------
200     Jennifer             Administration

With Read Only  & With Check Options::

Read Only in Simple View Example::

*We cant do any dml operations inside the View while using WITH READ ONLY option.

SQL> CREATE OR REPLACE VIEW Test_vw AS
  2  SELECT Name
  3  FROM Test_LDR
  4  WHERE Name='xxx'
WITH READ ONLY;

SQL> UPDATE Test_read_vw SET Name='xxx' WHERE Name='xxxx';
UPDATE Test_read_vw SET Name='xxx' WHERE Name='xxxx'
                        *
ERROR at line 1:
ORA-01733: virtual column not allowed here

With Check Option::

*While Using WITH CHECK OPTION we cant violate the Where Condition in View.

CREATE OR REPLACE VIEW Tst_Check_vw AS
SELECT Name
FROM TEST
WHERE Name='Vino'
WITH CHECK OPTION;

SQL> select * from Test;

ID                NAME
---------- ------------------------------
1                 Vino
2                 Mathi
3                 Xavi

SQL> CREATE OR REPLACE VIEW Tst_Check_vw AS
2  SELECT Name
3  FROM TEST
ORA-01402: view WITH CHECK OPTION where-clause violation
4  WHERE Name='Vino'
5  WITH CHECK OPTION;

View created.

SQL> UPDATE Tst_Check_vw SET Name='Kumar' Where Name='Vino';
UPDATE Tst_Check_vw SET Name='Kumar' Where Name='Vino'
*
ERROR at line 1:

DROP VIEW::

SQL>DROP VIEW VIEW_NAME;
View Dropped