Purpose
You can use this statement to create a view. By specifying the OR REPLACE clause, you can also replace an 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] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt [WHERE condition WITH CHECK OPTION];
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. |
| [NO] FORCE | If you want to create a 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 a 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. |
| 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. |
| WHERE condition WITH CHECK OPTION | WITH CHECK OPTION ensures that the data inserted or updated to the view meets the conditions in the view definition.
NoticeIn the Oracle mode of OceanBase Database, you cannot manually set |
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
Create a view named view2 based on the a and b columns of the tbl2 table. view2 contains all data that meets the tbl2.b > 1 condition.
obclient> create table tbl2 (a int, b int);
Query OK, 0 rows affected
obclient> insert into tbl2 (a,b) values (23,2),(76,9),(456,1);
Query OK, 3 rows affected (0.046 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient> create view view2 as select * from tbl2 where tbl2.b > 1 with check option;
Query OK, 0 rows affected (0.064 sec)