Home > SQL, Technical > Views in ORACLE SQL

Views in ORACLE SQL

August 3, 2012

View: its a SELECT statement with a name for a table.kind of virtual table…allows you to access a part of original table.

1.CREATE VIEW

============

Syntax: CREATE OR REPLACE VIEW  view_name AS SELECT STATEMENT

Example: CREATE OR REPLACE VIEW KARU_ORDER_VIEW AS SELECT * FROM ORDERS_KARU WHERE ORDER_ID=1;

you can check the view structure using below command

DESC KARU_ORDER_VIEW;

2.INSERT INTO VIEW

=================

Note 1: If the view is omitted ‘not null’ fields of real tables, then we cannot insert a record using view but we can update/delete

Note 2:If the view has a distinct, group function or hierarchical queries then we cannot insert a row

Note 3: From clause references more than one table ..i mean joins then we cannot insert a row

INSERT INTO KARU_ORDER_VIEW(ORDER_ID,ORDER_NAME,ORDER_DATE,QUANTITY)

values(6,’CUPS’,sysdate,60);

3.UPDATE VIEW

=============

Unless the data conflict the constraints of the real table, we can update the view

UPDATE

KARU_ORDER_VIEW

SET

ORDER_NAME=’SPOONS’

WHERE ORDER_ID=6;

4.DELETING IN VIEW

==================

DELETE  FROM KARU_ORDER_VIEW WHERE ORDER_ID=6;

5.DROP VIEW

============

Syntax: DROP VIEW View_Name;

Example: DROP VIEW KARU_ORDER_VIEW;

 

Advertisements
Categories: SQL, Technical
%d bloggers like this: