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.

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.