Description
This statement is used to create a view.
Limitations and considerations
In fact, a view does not exist as a table in the database. Instead, it is derived from the result of the
SELECTstatement specified in theCREATE VIEWstatement, and is derived each time it is used.If two or more tables or views are referenced in the
FROMclause, such a 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 restricted by theWITH READ ONLYclause.To ensure the updatable nature of a view, a series of specific conditions must be met. A general rule is that when performing
INSERT,UPDATE, orDELETEoperations on a connected view, the modifications can only affect a single base table.
Privilege requirements
To execute the CREATE VIEW statement, the current user must have the CREATE VIEW privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
CREATE [OR REPLACE] VIEW view_name[(column_name_list)]
AS select_stmt
[check_option];
column_name_list:
column_name [, column_name ...]
check_option:
WITH CHECK OPTION
| WITH CASCADED CHECK OPTION
| WITH LOCAL CHECK OPTION
Parameters
Parameters |
Description |
|---|---|
| OR REPLACE | Optional. If the view name to be created already exists, this option specifies to recreate the view with the new definition, replacing the existing view. |
| view_name | Specifies the view name. |
| column_name_list | Optional. Specifies the list of column names for the view. If the column name list is not specified, the column names retrieved by the SELECT statement will be used as the view column names. The columns retrieved by the SELECT statement can be simple references to table columns or expressions that use functions, constant values, operators, and so on. View column names have the following limitations:
|
| select_stmt | The query used to define the view (SELECT) statement. It defines the view, which can select data from base tables or other views. For details about the structure and options of the query statement, see SELECT. |
| check_option | Optional. Specifies the check scope of the view, which is used to restrict insert and update operations on the view. For more information about check scopes, see check_option. |
check_option
WITH CHECK OPTION: This is a syntax for creating a view that ensures the data inserted or updated satisfies the conditions defined by the view. In OceanBase AI Database, the WITH CHECK OPTION clause can also be used together with the LOCAL/CASCADED keywords to further specify the scope of data checking.
WITH CASCADED CHECK OPTION: checks the current view and recursively checks all referenced views.WITH LOCAL CHECK OPTION: Checks the current view. Whether to check the referenced views depends on their definitions.
When a view filter condition (where_clause) contains a subquery, you can specify the WITH CHECK OPTION clause.
Notice
When using the WITH CHECK OPTION clause in the filter condition:
- OceanBase AI Database must ensure that the creation statement is not for an "unupdatable view".
- The
JOINoperation is not supported in view definitions.
Examples
Create the test tables test_tbl1 and test_tbl2.
obclient> CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(50), col3 INT);
obclient> CREATE TABLE test_tbl2 (col1 INT, col2 VARCHAR(50));
Select columns
col1andcol2from tabletest_tbl1to create viewv1_t1.obclient> CREATE OR REPLACE VIEW v1_t1 AS SELECT col1, col2 FROM test_tbl1;Create the view
v2_t1usingWITH LOCAL CHECK OPTION.obclient> CREATE OR REPLACE VIEW v2_t1 AS SELECT * FROM test_tbl1 WHERE col1 > 0 WITH LOCAL CHECK OPTION;Create the view
v3_t1usingWITH CASCADED CHECK OPTION.obclient> CREATE OR REPLACE VIEW v3_t1 AS SELECT * FROM test_tbl1 WHERE col1 > 1 WITH CASCADED CHECK OPTION;Create the view
v4_t1usingWITH CHECK OPTION.obclient> CREATE OR REPLACE VIEW v4_t1 AS SELECT * FROM test_tbl1 WHERE col1 > 10 WITH CHECK OPTION;Specify the
WITH CHECK OPTIONclause after the filtering condition subquery.obclient> CREATE OR REPLACE VIEW v5_t1_t2 AS SELECT * FROM test_tbl1 t1 WHERE EXISTS (SELECT 1 FROM test_tbl2 t2 WHERE t2.col1 = t1.col1) WITH CHECK OPTION;