Database Views

 

A view does not physically exist in the database. It is derived from other database tables. When the data in its source table is updated , the view reflects the updates as well.

If structure of a view’s source table is altered, or if a view’s source table is dropped, then the view becomes invalid and can no longer be used until it is dropped and re-created or replaced.

 

Creating and Manipulating Simple Views

CREATE VIEW view_name AS sql_query; 
The view name must follow the Oracle naming standards.
The view name can not already exist in the user’s database schema.

You must have sufficient object privileges are related with the view to manage the records.

CREATE OR REPLACE VIEW view_name AS Sql_query;

 

 

CREATE VIEW faculty_view as

SELECT f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank

FROM faculty

 

SELECT f_last, f_first, bldg_code, room

FROM faculty_view, location

WHERE faculty_view.loc_id = location.loc_id;

 

Creating and Manipulating Complex Views

 

Complex views, unlike simple views, cannot be used to directly manage data in the underlying database tables.

 

CREATE VIEW inventory_view AS

SELECT item_desc, item_size, color, price, qoh, price*qoh AS value

FROM inventory, item

WHERE item.item_id = inventory.item_id

ORDER BY item_desc;

 

Dropping Views

 

DROP VIEW view_name;

 

Using Data Dictionary Views to Retrieve Information about the Views

 

Dictionary views: All_views

                               User_views

 

You can only see information about views for which you have been granted object privileges.

SELECT view_name

FROM ALL_VIEWS

WHERE owner = ‘SYSTEM’;