Purpose
You can use this statement to create a view. By specifying the OR REPLACE clause, you can also replace an existing view.
Views do not exist as tables in the database. A view is derived from the results of the SELECT statement specified in the CREATE VIEW statement. A view is derived each time the statement is executed.
If the FROM clause references two or more tables or views, this 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. An updatable view is not subject to 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] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Indicates recreating a view with a new definition if the view already exists. |
| [NO] FORCE | If you want to create the view regardless of whether the base tables of the view or the referenced object types exist, or the owner of the schema containing the view has privileges on them, specify FORCE. If you want to create the view only when the view has base tables and the owner of the schema containing the view has privileges on them, specify NOFORCE. The default value is NOFORCE. |
| view_name | The name of the view to be created. |
| select_stmt | A SELECT statement that defines the view. You can use this statement to retrieve columns from base tables or other views. |
| column_name_list | The list of column names in the view. Like a base table, the view must have unique column names. 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
Retrieve the col1 and col2 columns of the tbl1 table to create a view named view1.
obclient> CREATE OR REPLACE FORCE VIEW view1(vcol1, vcol2) AS SELECT col1, col2 FROM tbl1;
Query OK, 0 rows affected