Summary: in this tutorial, you will learn how to use the Oracle
CREATE VIEW statement to create a new view in the database.
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]
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.
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
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
SELECTclause of the defining query.
- Explicitly specify column aliases for the view’s columns between the
The defining query is a
SELECT statement that defines the columns and rows of the view.
WITH READ ONLY
WITH READ ONLY clause prevents the underlying tables from changes through the view.
WITH CHECK OPTION
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.
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;
In this example, we did not define the column names for the view because the defining query uses column aliases for expressions such as
first_name || ' ' || last_name and
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 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;
The following query returns employees whose years of service are 15:
SELECT * FROM employee_yos WHERE yos = 15 ORDER BY full_name;
B) Creating a read-only view example
Consider the following
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;
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:
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;
In this tutorial, you have learned how to use the Oracle
CREATE VIEW statement to create new views in the database.