Oracle Create View

In relational databases, a view refers to a virtual table used to store the result of a SELECT statement for later use. Essentially, the primary use of a view is to keep the result of a SELECT statement allowing for data re-use or as a subquery.

Views are fundamental as they allow you to store complex queries in the database, which can be referenced and used in other parts of your application. Doing so can lead to faster app performance and code efficiency.

This tutorial will teach you how to use the Oracle CREATE VIEW statement to create an Oracle view.

Oracle Create View Syntax

The following shows the syntax for the CREATE VIEW command in Oracle:

1. We start with the CREATE keyword that allows us to tell the database engine that we are creating a view. It is good to use the REPLACE option to modify an existing view or prevent the database engine if a view with a similar name exists in the database.
2. The NO FORCE clause tells the database engine not to force the view creation. This means the view will not be created if any errors are encountered during the creation. This can help prevent storing incorrect information from an incorrect query.
3. Next, we set the schema name where the view will be stored and the name of your view. Leaving the schema name blank will create the view in the current schema.
4. The (alias [ inline_constraint [ inline_constraint ]… ] | out_of_line_constraint [, alias [ inline_constraint [ inline_constraint ]… ] | out_of_line_constraint ]…) block determines the columns and constraints that should be defined for the view.
5. Finally, the section, AS subquery [subquery_restriction_clause] specifies the SELECT statement that defines the view.

Oracle Create View Example

Suppose we have a table containing employee information as shown below:

select FIRST_NAME, LAST_NAME, EMAIL, SALARY, COMMISSION_PCT from EMPLOYEES emp;

Result:

Suppose we want to query the table for all the employees whose commission_pct is not null and store that information in a view.

We can create a query as shown below:

In this case, we use the CREATE OR REPLACE VIEW statement to create a new view called employee_pct.

Next, we use the select statement to specify which columns we wish to include in the view. We also calculate the commission for each employee by multiplying their COMMISSION_PCT by their SALARY.

The FROM clause allows us to specify from which table the view is based on.

Since we do not wish to include NULL values in the view, we use the WHERE clause and the IS NOT NULL operator to exclude all NULL values from the result set.

This means that only employees who have a non-null COMMISSION_PCT will be included in the view.

Once we have created the view, we can use it in subsequent queries like a normal table.

For example, we can select the records from the view using a select statement to get a list of employees and their commissions:

This would return a list of employees and their commissions, as defined in the view.

Conclusion

To sum up, we can use the CREATE VIEW statement in Oracle to create a new view that is a virtual table based on the result set of a SELECT statement.

This allows you to store complex queries in the database and use them in other parts of your app.