CREATE VIEW

Purpose

This statement is used to create a view.

Limitations and considerations

If the OR REPLACE clause is specified, the statement can replace an existing view.

A view is not actually 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.

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.

To be updatable, a view must meet several conditions. One common rule is that for a join view, an INSERT, UPDATE, or DELETE operation can modify only one base table at a time.

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 Oracle mode.

Syntax

CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [(column_name_list)]
    AS select_stmt
    [view_check_option];

select_stmt:
    SELECT column_name, [--string]
        [, column_name, [--string] ...]
    FROM from_list
    [WHERE condition]

column_name_list:
    column_name [, column_name ...]

view_check_option:
    WITH READ ONLY
    | WITH CHECK OPTION

Parameters

view_check_option

• WITH READ ONLY: An option used when creating or replacing a view. It specifies that the view can only be used for reading data and cannot be used for DML operations (INSERT, UPDATE, DELETE). This means that insertions, updates, and deletions through the view are prohibited.

• WITH CHECK OPTION: A syntax used when creating a view. It ensures that the data inserted or updated meets the conditions defined by the view. In other words, it allows DML operations through the view, but the data after the operation must meet the conditions defined by the view.

  Note

  In Oracle mode, the WITH CHECK OPTION syntax does not support specifying LOCAL or CASCADED. The default is CASCADED.

  If the 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, the view definition does not support the JOIN operation.

Examples

Create test tables tbl1 and tbl2.

obclient> CREATE TABLE tbl1 (col1 INT, col2 VARCHAR(50));

obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);

• Select columns col1 and col2 from table tbl1 to create view v1_t1.

  obclient> CREATE OR REPLACE FORCE VIEW v1_t1(vcol1, vcol2)
      AS SELECT col1, col2
         FROM tbl1;
  

• Create view v2_t2 based on columns col1 and col2 of table tbl2, which contains all data that satisfies tbl2.col2 > 1.

  obclient> CREATE VIEW v2_t2
      AS SELECT *
         FROM tbl2
         WHERE tbl2.col2 > 1 WITH CHECK OPTION;
  

• Create view v3_t3 based on columns col1 and col2 of table tbl2, and add comments to view v3_t3.

  1. Create view v3_t3.

     obclient> CREATE VIEW v3_t3
         AS SELECT 
             col1,   --test.col1
             col2    --test.col2
         FROM tbl2;
     

  2. Query the view comments by using SHOW CREATE VIEW or DBMS_METADATA.GET_DDL.

     • Query the view comments by using SHOW CREATE VIEW.

       obclient> SHOW CREATE VIEW v3_t3;
       

       The returned result is as follows:

       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       | VIEW  | CREATE VIEW                                                                                                                              | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       | V3_T3 | CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
                   col1,   --test.col1
                   col2    --test.col2
               FROM tbl2 | utf8mb4              | utf8mb4_bin          |
       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       1 row in set
       

     • Query the view comments by using DBMS_METADATA.GET_DDL.

       obclient> SELECT DBMS_METADATA.GET_DDL('VIEW', 'V3_T3', 'SYS') FROM DUAL;
       

       The returned result is as follows:

       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       | VIEW  | CREATE VIEW                                                                                                                              | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       | V3_T3 | CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
                   col1,   --test.col1
                   col2    --test.col2
               FROM tbl2 | utf8mb4              | utf8mb4_bin          |
       +-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
       1 row in set (0.028 sec)
       
       obclient(SYS@oracle001)[SYS]> SELECT DBMS_METADATA.GET_DDL('VIEW', 'V3_T3', 'SYS') FROM DUAL;
       +------------------------------------------------------------------------------------------------------------------------------------------+
       | DBMS_METADATA.GET_DDL('VIEW','V3_T3','SYS')                                                                                              |
       +------------------------------------------------------------------------------------------------------------------------------------------+
       | CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
                   col1,   --test.col1
                   col2    --test.col2
               FROM tbl2 |
       +------------------------------------------------------------------------------------------------------------------------------------------+
       1 row in set
       

• Specify the WITH CHECK OPTION clause after the subquery in the filter condition.

  obclient> CREATE OR REPLACE VIEW v4_t1
      AS SELECT *
      FROM tbl1 t1
      WHERE EXISTS (SELECT 1 FROM tbl2 t2 WHERE t2.col1 = t1.col1) WITH CHECK OPTION;
  

References

• Manage normal views
• CREATE VIEW
• SIMPLE SELECT
• SHOW
• GET_DDL