How To Create View In Oracle

Summary: in this tutorial, you will learn how to use the Oracle CREATE VIEW statement to create a new view in the database.

Oracle `CREATE VIEW` syntax

To create a new view in a database, you use the following Oracle CREATE VIEW statement:

                                          CREATE                [OR                REPLACE]                VIEW                view_name [(column_aliases)]                AS                defining-query                [WITH                READ                ONLY] [WITH                CHECK                OPTION]                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

`OR REPLACE`

The OR REPLACE option replaces the definition of existing view. It is handy if you have granted various privileges on the view. Because when you use the DROP VIEW and CREATE VIEW to change the view's definition, Oracle removes the view privileges, which may not be what you want. To avoid this, you can use the CREATE OR REPLACE clause that preserves the view privileges.

`FORCE`

Usually, you create a new view based on existing tables. However, sometimes, you may want to create a view based on the tables that you will create later or the tables that you don't have sufficient privileges to access at the time of creating the view. In these cases, you can use the FORCE option.

`column-aliases`

Typically, the column names of a view derived from the select list of the defining query. However, the column names of the defining query may contain functions or expressions that you cannot use for the view definition.

To solve this problem, you have two options:

Use column aliases that adhere to the naming rules in the SELECT clause of the defining query.
Explicitly specify column aliases for the view's columns between the CREATE VIEW and AS clauses.

`AS defining-query`

The defining query is a SELECT statement that defines the columns and rows of the view.

`WITH READ ONLY`

The WITH READ ONLY clause prevents the underlying tables from changes through the view.

`WITH CHECK OPTION`

The WITH CHECK OPTION clause protects the view from any changes to the underlying table that would produce rows which are not included in the defining query.

Oracle `CREATE VIEW` examples

Let's look at some examples of creating new views based on the tables in the sample database.

A) Creating a view example

See the following employees table from the sample database.

The following statement creates a view named employee_yos based on the employees table. The view shows the employee id, name and years of service:

                                          CREATE                VIEW                employee_yos                AS                SELECT                employee_id,     first_name ||                ' '                || last_name full_name,                FLOOR( months_between(                CURRENT_DATE, hire_date )/                12                ) yos                FROM                employees;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

In this example, we did not define the column names for the view because the defining query uses column aliases for expressions such as full_name for first_name || ' ' || last_name and yos for FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 ).

If you don't want to use column aliases in the query, you must define them in the CREATE VIEWclause:

                                          CREATE                VIEW                employee_yos (employee_id, full_name, yos)                AS                SELECT                employee_id,     first_name ||                ' '                || last_name,                FLOOR( months_between(                CURRENT_DATE, hire_date )/                12                )                FROM                employees;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

The following query returns employees whose years of service are 15:

                                          SELECT                *                FROM                employee_yos                WHERE                yos =                15                ORDER                BY                full_name;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

B) Creating a read-only view example

Consider the following customers table:

The following example creates a read-only view named customer_credits, which is based on the customers table. The view contains three columns: customer id, customer name and credit limit:

                                          CREATE                OR                REPLACE                VIEW                customer_credits(         customer_id,                name,         credit     )                AS                SELECT                customer_id,                name,         credit_limit                FROM                customers                WITH                READ                ONLY;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

C) Creating a Join view example

A join view is a view whose defining query contains a join, e.g., inner join or left join. The following statement creates a view named backlogs whose the defining query includes join clauses that join three tables: orders, order_items, and products.

orders, order_items, and products tables

CREATE OR REPLACE VIEW backlogs AS SELECT product_name, EXTRACT( YEAR FROM order_date ) YEAR, SUM( quantity * unit_price ) amount FROM orders INNER JOIN order_items USING(order_id) INNER JOIN products USING(product_id) WHERE status = 'Pending' GROUP BY EXTRACT( YEAR FROM order_date ), product_name;

Code language: SQL (Structured Query Language) ( sql )

In this tutorial, you have learned how to use the Oracle CREATE VIEW statement to create new views in the database.

Was this tutorial helpful?