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 [WHERE condition WITH {LOCAL | CASCADED} 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. |
| 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. In the MySQL mode of OceanBase Database, you can use the WITH CHECK OPTION clause together with the LOCAL/CASCADED keyword to further specify the data check scope. Differences between LOCAL and CASCADED:
|
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;
Use LOCAL CHECK OPTION to create a view named view1.
obclient> create table test99(a int);
Query OK, 0 rows affected (0.090 sec)
obclient> insert into test99 values(0), (1), (2), (3);
Query OK, 4 rows affected (0.046 sec)
Records: 4 Duplicates: 0 Warnings: 0
obclient> CREATE OR REPLACE VIEW view1 AS SELECT * FROM test99 WHERE a > 0 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.065 sec)
Use CASCADED CHECK OPTION to create a view named view2 based on view1.
obclient> CREATE OR REPLACE VIEW view2 AS SELECT * FROM view1 WHERE a > 1 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.073 sec)