explain views in oracle with all type

VIEWS

After a table is created and populated with data, it may become necessary to prevent all users from accessing all columns of a table, for data security reasons. This would mean creating several tables having the appropriate number of columns and assigning specific users to each table, as required. This will answer data security requirements very well but will give rise to a great deal of redundant data being resident in tables, in the database.

To reduce redundant data to the minimum possible, Oracle allows the creation of an object called a View. A View is mapped, to a SELECT sentence. The table on which the view is based is described in the FROM clause of the SELECT statement. The SELECT clause consists of a sub-set of the columns of the table. Thus a View, which is mapped to a table, will in effect have a sub-set of the actual columns of the table from which it is built. This technique offers a simple, effective way of hiding columns of a table.

An interesting fact about a View is that it is stored only as a definition in Oracle's system catalog. When a reference is made to a View, its definition is scanned, the base table is opened and the View created on top of the base table. Hence, a view holds no data at all, until a specific call to the view is made. This reduces redundant data on the HDD to a very large extent. When a View is used to manipulate table data, the underlying base table will be completely invisible. This will give the level of data security required.

The Oracle engine treats a View just as though it was a base table. Hence a View can be queried exactly as though it was a base table. However, a query fired on a view will run slower than a query fired on a base table. This is because the View definition has to be retrieved from Oracle's system catalog, the base table has to be identified and opened in memory and then the View has to be constructed on top of the base table, suitably masking table columns. Only then will the query actually execute and return the active data set.

Some Views are used only for looking at table data. Other Views can be used to Insert, Update and Delete table data as well as View data. If a View is used to only look at table data and nothing else the View is called a Read-Only View. A View that is used to look at table data as well as Insert, Update and Delete table data is called an Updateable View.

The reasons why views are created are:

When Data security is required

When Data redundancy is to be kept to the minimum while maintaining data security

Learning how a View is:

Created

Used for only viewing and / or manipulating table data (i.e. a read-only or updateable view)

Destroyed

Creating View

Syntax:

CREATE VIEW AS

SELECT , FROM

WHERE = < Expression List>;

GROUP BY < Grouping Criteria > HAVING < Predicate >

Note : The ORDER BY clause cannot be used while creating a view.

Example 1 :

Create a view called Customers on the CUST_MSTR table.

CREATE VIEW vw_Customers AS SELECT * FROM CUST_MSTR;

Example 2 :

Create a view called Employees on the EMP_MSTR table.

CREATE VIEW vw_Employees AS SELECT NAME, DEPT, BASIC

FROM EMP_MSTR;

This creates a view by the name of vw_Employees based on the table EMP_MSTR.

Selecting A Data Set From A View

Once a view has been created, it can be queried exactly like a base table.

Syntax:

SELECT , FROM ;

Note : Instead of a table name in the FROM clause, a view name is used. The SELECT statement can have all the clauses like WHERE, ORDER BY etc.

Example 3:

SELECT NAME, DEPT FROM vw_Employees WHERE DEPT IN ('Marketing', 'Loans And Financing');

Updateable Views

Views can also be used for data manipulation (i.e. the user can perform the Insert, Update and Delete operations). Views on which data manipulation can be done are called Updateable Views. When updateable view name is given in an Insert, Update, or Delete SQL statement, modifications to data in view will be immediately passed to the underlying table.

For a view to be updateable, it should meet the following criteria:

Views defined from Single table.

If the user wants to INSERT records with the help of a view, then the PRIMARY KEY column(s) and all the NOT NULL columns must be included in the view.

The user can UPDATE, DELETE records with the help of a view even if the PRIMARY KEY column and NOT NULL column(s) are excluded from the view definition.

Example 4:

Table Name: NOMINEE_MSTR

Column Name	Data Type	Width	Attributes
NOMINEE_NO	VarChar2	10	Primary Key
ACCT_FD_NO	VarChar2	10	Not Null
NAME	VarChar2	75	Not Null
DOB	Date
RELATIONSHIP	VarChar2	25

CREATE VIEW vw_Nominees AS

SELECT NOMINEE_NO, ACCT_FD_NO, NAME FROM NOMINEE_MSTR;

When an INSERT operation is performed using the view:

INSERT INTO vw_Nominees VALUES(‘N123', 'SB234', 'Aaditya');

Oracle returns the following message:

1 row created

When a MODIFY operation is performed using the view:

UPDATE vw_Nominees SET NAME = 'Mihir' WHERE NAME='Aaditya';

Oracle returns the following message:

1 row updated.

When a DELETE operation is performed using the view

DELETE FROM vw_Nominees WHERE NAME = 'Mihir';

Oracle returns the following message:

1 row deleted.

A view can be created from more than one table. For the purpose of creating the View these tables will be linked by a join specified in the WHERE clause of the View definition.

The behavior of the View will vary for Insert, Update, Delete and Select table operations depending upon the following:

Whether the tables were created using a Referencing clause

Whether the tables were created without any Referencing clause and are actually standalone tables not related in any way

Views Defined From Multiple Tables (Which Have No Referencing Clause)

If a view is created from multiple tables, which were not created using a Referencing clause (i.e. No logical linkage exists between the tables), then though the PRIMARY Key Column(s) as well as the NOT NULL columns are included in the View definition the view's behavior will be as follows:

The INSERT, UPDATE or DELETE operation is not allowed. If attempted, Oracle displays the following error message:

For insert/modify:

ORA -01779: cannot modify a column, which maps to a non key-preserved table.

For delete:

ORA -01752: cannot delete from view without exactly one key-preserved table.

Views Defined From Multiple Tables (Which Have Been Created With A Referencing Clause)

If a view is created from multiple tables, which were created using a Referencing clause (i.e. a logical linkage exists between the tables), then though the PRIMARY Key Column(s) as well as the NOT NULL columns are included in the View definition, the view's behavior will be as follows:

An INSERT operation is not allowed

The DELETE or MODIFY operations do not affect the Master table

The view can be used to MODIFY the columns of the detail table included in the view.

If a DELETE operation is executed on the view, the corresponding records from the detail table will be deleted.

Example 5:

Table Name: BRANCH MSTR

Column Name	Data Type	Size	Attributes
BRANCH NO	VarChar2	10	Primary Key / First letter must be 'B'
NAME	VarChar2	25

Table Name: ADDR DTLS

Column Name	Data Type	Size	Attributes
ADDR NO	Number	6	Primary Key
CODE_NO	VarChar2	10	Foreign Key references BRANCH_NO of the BRANCH_MSTR table.
ADDR TYPE	VarChar2	1	Can hold the values: H for Head Office or B for Branch
ADDR1	VarChar2	50
ADDR2	VarChar2	50
CITY	VarChar2	25
STATE	VarChar2	25
PINCODE	VarChar2	6

Syntax for creating a Master/Detail View

CREATE VIEW vw_Branch AS

SELECT BRANCH_NO, NAME, ADDR_TYPE, ADDR1, ADDR2, CITY, STATE, PINCODE FROM BRANCH_MSTR, ADDR DTS

WHERE ADDR_DTLS.CODE_NO = BRANCH JV1STR.BRANCH_NO;

When an INSERT operation is performed using the view

INSERT INTO vw_Branch VALUES('B7', 'Dahisar', 'B', 'Vertex Plaza, Shop 4,', 'Western Express High, Dahisar (East),', 'Mumbai', 'Maharashtra', '400078');

Oracle returns the following error message:

ORA-01776: cannot modify more than one base table through a join view

When a MODIFY operation is performed using the view

UPDATE vw_Branch SET PINCODE = '400079' WHERE BRANCHING = 'B5';

Oracle returns the following message:

1   row updated.

When a DELETE operation is performed using the view

SQL> DELETE FROM vw_Branch WHERE BRANCH_NO = 'B5';

Oracle returns the following message:

1 row deleted.

Common Restrictions On Updateable Views

The following condition holds true irrespective of the view being created from a single table or multiple tables.

For the view to be updateable the view definition must not include:

Aggregate functions

DISTINCT, GROUP BY or HAVING clause

Sub-queries

Constants, Strings or Value Expressions like Sell_price * 1.05

UNION, INTERSECT or MINUS clause

If a view is defined from another view, the second view should be updateable

If the user tries to perform any of INSERT, UPDATE, DELETE operation, on a view, which is created from a non-updateable view Oracle returns the following error message

FOR INSERT/MODIFY/DELETE

ORA-01732:   data manipulation operation not legal on this view

Destroying A View

The DROP VIEW command is used to remove a view from the database.

Syntax:

DROP VIEW ;

Example 6:

Remove the view vw_Branch from the database.

DROP VIEW vw Branch;