Purpose
You can use this statement to create a view. If the OR REPLACE clause is specified, this statement can replace the existing view.
In a database, views do not exist as tables, but are derived from the execution results of the SELECT statement specified in the CREATE VIEW statement. A view is derived each time the statement is used.
If the FROM clause references two or more tables or views, the view is called a join view. An updatable join view, also known as a modifiable join view, involves two or more base tables or views and allows DML operations. Updatable views are not limited by the WITH READ ONLY clause.
A view must meet several criteria to be updatable. For example, a general rule is that an INSERT, UPDATE, or DELETE operation on a join view can modify only one base table at a time.
Syntax
create_view_stmt:
CREATE [OR REPLACE] VIEW view_name [(column_name_list)] AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
Parameter |
Description |
|---|---|
| OR REPLACE | Specifies to re-create a view that already exists by using a new definition. |
| view_name | The name of the view. |
| select_stmt | A SELECT statement that defines a view. You can use this statement to query base tables or other views. |
| column_name_list | The column names in the view, which must be unique, like those in a base table. By default, the column names retrieved by the SELECT statement are used as the column names in the view. You can also use the optional column_name_list clause to define column names for a view. Separate multiple column names with commas (,). The number of column names in column_name_list must be equal to the number of columns retrieved by the SELECT statement. The SELECT statement can directly reference the columns of a table. You can also use functions, constants, and operators in the statement to retrieve columns. |
Examples
Select columns c1 and c2 from table t to create view v.
obclient> CREATE OR REPLACE VIEW v(vc1, vc2) AS SELECT c1, c2 FROM t;