FahmidasClassroom

Learn by easy steps

You Are Here

P3
Database

Create View in Oracle

no Comments

View is a virtual table that is created from an existing database table. The way of creating, updating, and deleting view has been shown in this tutorial.

Syntax:

CREATE [OR REPLACE] VIEW view_name AS
SELECT field1, field2, ..., field3
FROM tables
[WHERE conditions];
[WITH READ ONLY]
[WITH CHECK OPTION]

A new view can be created or an existing view can be replaced by a new view by using CREATE or REPLACE statement. The SELECT query of the view can retrieve data from one or more tables with or without the WHERE clause. The ‘WITH READ ONLY’ feature is used in view to create non-updatable view. The ‘WITH CHECK OPTION’ feature is used to prevent the view from updating those fields are not included in the view.

Some examples of creating view in oracle have been shown below.

Example-1: Create a view by using where clause

The following SQL statement will create a simple view from two tables.

CREATE VIEW "SYSTEM"."STD_VIEW" AS 
SELECT STD_BASIC.ID, STD_BASIC.NAME, STD_BASIC.BATCH, STD_DEPT.DEPARTMENT
FROM STD_BASIC, STD_DEPT
WHERE STD_BASIC.ID = STD_DEPT.STD_ID;

The following output will appear if the view is created successfully.

P1

The view will contain the following output.

P2

Example-2: Create a view by using inner join

The following SQL statement will create an updatable view from two tables by using inner join.

CREATE or REPLACE VIEW "SYSTEM"."STD_VIEW2" AS 
SELECT STD_BASIC.NAME, STD_BASIC.BATCH, STD_BASIC.SEMESTER, STD_DEPT.DEPARTMENT
FROM STD_BASIC
INNER JOIN STD_DEPT
ON STD_BASIC.ID = STD_DEPT.STD_ID;

The following output will appear if the view is created successfully.

P3

The view will contain the following output.

P4

Example-3: Create a read-only view

The following SQL statement will create a non-updatable view of a table.

CREATE VIEW SYSTEM."STD_VIEW3" AS 
SELECT STD_BASIC.ID, STD_BASIC.NAME, STD_BASIC.BATCH, STD_BASIC.SEMESTER 
FROM STD_BASIC 
WITH READ ONLY;

The following output will appear if the view is created successfully.

P6

Example-4: Drop a view

The following SQL statement will drop the existing view.

DROP VIEW STD_VIEW2;

The following output will appear if the view is dropped successfully.

P5

Exercise:

Create two tables named manufacturer (id*, name, address, email) and products (id*, name, price, manufacturer_id). Here, the manufacturer_id is a foreign key of the products table. Create an updatable view that will show the product name, product price, and manufacturer name from two tables.