Oracle CREATE VIEW Statement

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

Introduction to Oracle CREATE VIEW statement #

To create a new view in a database, you use the CREATE VIEW statement.

Here’s the basic syntax of the CREATE VIEW statement:

CREATE VIEW view_name
AS
    defining-query;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view after the CREATE VIEW keyword.
• Second, provide a defining query of the view.

For example, the following statement creates a view called years_of_services based on the employees table from the sample database:

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

Replacing a view #

If you have a view and want to replace its defining query, you can use the OR REPLACE option:

CREATE OR REPLACE VIEW view_name
AS
    defining-query;Code language: SQL (Structured Query Language) (sql)

The OR REPLACE option replaces the definition of the existing view.

It is handy if you have granted 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.

For example, you can add the hire_date column to the years_of_services view by using the OR REPLACE option and changing the defining query as follows:

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

Creating column aliases for views #

By default, the column names of a view are 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 columns.

For example, the following statement creates an employee_lists view:

CREATE VIEW employee_lists AS
SELECT
  first_name || ' ' || last_name
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

This statement causes an error:

00000 -  "must name this expression with a column alias"Code language: SQL (Structured Query Language) (sql)

To fix this, you can assign an alias to the expression in the defining query:

CREATE OR REPLACE VIEW employee_lists 
AS
SELECT
  first_name || ' ' || last_name full_name
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Alternatively, you can explicitly specify the column name for the view after the view name:

CREATE OR REPLACE VIEW employee_lists (full_name) 
AS
SELECT
  first_name || ' ' || last_name
FROM
  employees; Code language: SQL (Structured Query Language) (sql)

Creating a read-only view #

In Oracle, simple views are updatable. It means that you can modify data in the underlying tables through views. We’ll discuss updatable views in the updatable view tutorial.

To make the view not updatable, you can use the READONLY option:

CREATE OR REPLACE VIEW view_name
AS
    defining-query
WITH READ ONLY;Code language: SQL (Structured Query Language) (sql)

Consider the following customers table:

The following example creates a read-only view named customer_credits, which is based on the customers table:

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)

Summary #

• Use the Oracle CREATE VIEW statement to create new views in the database.
• Use the OR REPLACE option to redefine the query for the view.
• Use the WITH READ ONLY option to create a read-only view

Quiz #