Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » ViewRSS Feeds

VIEWS

Posted By: Bingham Fischer     Category: Oracle     Views: 2490

This article explains about views, creating view, remaining column in view, updating views and conditions for updateable views.

VIEWS

- view is a table whose contents are taken from other tables through the execution of a query.
- kind of tables which we have seen uptil now are called permanent base tables.
- views contain no data of their own, hence they are called virtual tables or viewed tables.
- View is a query that is executed when the view is used in a SQL statement.

CREATE VIEW  

you can define views using CREATE VIEW statement.

Syntax of Create view 

CREATE VIEW <viewname> AS 
(SELECT <col1>,<col2> FROM <tablename> WHERE <condition>)

CREATE VIEW ahmdstaff
AS (SELECT * from Salespeople WHERE city='ahmd');

- The query in the view is known as UNDERLYING QUERY, and the tables it uses are called underlying tables.
- Through views, you can give people access to some but not all the information in a table.(SELECTIVE ACCESS TO YOUR DATABASE)
- E.g. if you wanted your salespeople to be able to look at Salespeople table,but not each others commission,

Salespeople(SNUM,SNAME,CITY,COMM)

CREATE VIEW Salesown AS (SELECT snum,sname,city FROM Salespeople);

RENAMING COLUMNS IN VIEWS

Use AS argument to rename output columns.

CREATE VIEW ahmdstaff
AS (SELECT snum AS SalesPerson_Num from Salespeople WHERE city='ahmd');

Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)                                    

UPDATING VIEWS 

- updations to the views are passed on to the underlying tables.

UPDATE salesown SET city='mumbai' WHERE sname='nayan';

DROP VIEW ahmdstaff;

CONDITIONS FOR UPDATEABLE VIEWS

i)views must be defined from single table.
ii)To insert records from view,then P.K. columns and all NOT NULL columns must be included in the view.

INSERT INTO salesown VALUES (26,'ramesh','ahmd');

UPDATE salesown SET city='ahmd' WHERE snum=25;

 

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on VIEWS Or get search suggestion and latest updates.

Bingham Fischer
Bingham Fischer author of VIEWS is from Frankfurt, Germany.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!