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 two or more tables or views are referenced in the FROM clause, 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 operations can only modify 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 types in MySQL 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 inserted or updated data meets the view's definition conditions. In MySQL mode, the WITH CHECK OPTION clause can also be used with the LOCAL/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.

Examples

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 INT, col2 INT);
   

2. Create a view named v1 based on the test_tbl1 table.

   CREATE VIEW v1
       AS SELECT * FROM test_tbl1 WHERE col2 < 2
       WITH CHECK OPTION;
   

3. Create a view named v2 based on the v1 view using the LOCAL CHECK OPTION syntax.

   CREATE VIEW v2
       AS SELECT * FROM v1 WHERE col2 > 0
       WITH LOCAL CHECK OPTION;
   

4. Create a view named v3 based on the v1 view using the CASCADED CHECK OPTION syntax.

   CREATE VIEW v3
       AS SELECT * FROM v1 WHERE col2 > 0
       WITH CASCADED CHECK OPTION;
   

References

• Manage normal views
• ALTER VIEW
• DROP VIEW