ORACLE-VIEWS

ORACLE-VIEWS-Learn about features of oracle views ,how to create view, and how to perform insert,update,delete operations on views with super simple explanation with examples.

ORACLE-VIEWS

A View is a select query that retrieves the data from one or more physical tables and stores in a logical table.

Points to Notice

1. View is a virtual table(logical table). Logical table does not take any storage space.
2. A view can be accessed with the help of select query.
3. View run the query definition each time they are accessed.
4. A view can be created from another view also

SQL Create View syntax

CREATE [OR REPLACE] VIEW [VIEW_NAME] AS
SELECT [column1,column2]
FROM [table_1,table_2]
WHERE [conditions]

Advantages of views

1. View hides complexity of join operations.
2. Row and column level security for user access of tables.
3. Views can be used to present aggregated data and hide detailed data.

Insert,Delete,Update on Views

• views can be inserted ,updated,deleted properly if view contains a single base table.
• If view contains multiple tables data , data may not insert, updata and delete correctly.

Base Table:

CREATE TABLE CUSTOMER_TBL(ID BIGINT,NAME VARCHAR(100));

Logica Table:

CREATE VIEW VW_CUSTOMER AS SELECT ID,NAME FROM CUSTOMER_TBL;

SQL query example Insert Data on Views.

INSERT INTO VW_CUSTOMER VALUES(1,'JHON');

To display the result use below query

SELECT * FROM VW_CUSTOMER;

Output

ID	NAME
1	JHON

SELECT* FROM CUSTOMER_TBL;

ID	NAME
1	JHON

SQL query example Update Data on Views.

UPDATE VW_CUSTOMER SET ID=3 WHERE ID=1;

To display the result use below query

SELECT * FROM VW_CUSTOMER ;

ID	NAME
3	JHON

SELECT * FROM CUSTOMER_TBL;

ID	NAME
3	JHON

SQL query example Delete Data on Views.

DELETE FROM VW_CUSTOMER WHERE ID=3;

To display the result use below query

SELECT * FROM VW_CUSTOMER;

Empty set (0.00 sec)

SELECT * FROM CUSTOMER_TBL;

Empty set (0.00 sec)

Notes

Check constraint can be used on views to limit the range of values on doing insertion on views.

SQL Create View statement with Check option

CREATE VIEW VW_CUSTOMER AS SELECT ID,NAME FROM CUSTOMER_TBL WHERE ID>3 WITH CHECK OPTION;

we will not be able to insert any row with ID < 3.  ID values with > 3 are allowed to insert as to match the view definition.

INSERT INTO VW_CUSTOMER VALUES(1,'JHON');

Output:

ERROR 1369 (HY000): CHECK OPTION failed ‘bookstore.vw_customer’

INSERT INTO VW_CUSTOMER VALUES(4,'JHON');

Output:

Query OK, 1 row affected (0.55 sec)

To display the result use below query

SELECT * FROM CUSTOMER_TBL;

ID	NAME
4	JHON

Points to Note
The View becomes invalid whenever the Physical table is altered.

SQL DROP View syntax

DROP VIEW [If Exists] [view_name]

SQL Query example to Drop view

DROP VIEW IF EXISTS VW_CUSTOMER;

IF EXISTS is optional – to avoid returning of error if view does not exist.