CREATE VIEW

Purpose

This statement is used to create a view.

Limitations and considerations

• A view is not stored as a table in the database. Instead, it is derived from the SELECT statement specified in the CREATE VIEW statement. Each time the view is used, it is derived anew.

• 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 restricted by the WITH READ ONLY clause.

• To ensure the updatable nature of a view, a series of specific conditions must be met. One universally applicable rule is that when performing INSERT, UPDATE, or DELETE operations on a join view, the operation must target only 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 Database privileges, see Privilege classification in MySQL-compatible mode.

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

check_option

WITH CHECK OPTION: A syntax for creating a view that ensures the data inserted or updated meets the view's definition conditions. In MySQL-compatible mode, the WITH CHECK OPTION clause can also be used with the LOCAL or CASCADED keywords to further specify the scope of data checks.

• 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 the definitions of those views.

OceanBase Database also supports specifying the WITH CHECK OPTION clause when a subquery is included in the view's filter condition (where_clause).

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));

• Create a view v1_t1 by selecting the col1 and col2 columns from table test_tbl1.

  obclient> CREATE OR REPLACE VIEW v1_t1
      AS SELECT col1, col2
         FROM test_tbl1;
  

• Create a view v2_t1 using the WITH LOCAL CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v2_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 0 WITH LOCAL CHECK OPTION;
  

• Create a view v3_t1 using the WITH CASCADED CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v3_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 1 WITH CASCADED CHECK OPTION;
  

• Create a view v4_t1 using the WITH CHECK OPTION clause.

  obclient> CREATE OR REPLACE VIEW v4_t1
      AS SELECT *
         FROM test_tbl1
         WHERE col1 > 10 WITH CHECK OPTION;
  

• Specify the WITH CHECK OPTION clause after the filter 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;
  

References

• Manage normal views
• ALTER VIEW
• DROP VIEW