CREATE VIEW

Purpose

This statement is used to create a view.

Limitations and considerations

If the OR REPLACE clause is specified, the existing view will be replaced.

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.

If the FROM clause references two or more tables or views, the view is referred to as 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 be updatable, a view must meet several conditions. One common rule is that for a join view, an INSERT, UPDATE, or DELETE operation can only modify 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 privileges in OceanBase Database, see Privilege classification in Oracle-compatible 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 for creating or replacing a view. It specifies that the view can only be used for reading data and not for DML operations (INSERT, UPDATE, DELETE). This means that you cannot insert, update, or delete data through the view.

• WITH CHECK OPTION: A syntax for creating a view. It ensures that the data inserted or updated meets the view's definition conditions. This means that you can perform DML operations through the view, but the data must meet the view's definition conditions.

  Note

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

  OceanBase Database also supports specifying the WITH CHECK OPTION clause when the view filter condition (where_clause) contains a subquery.

  Notice

  When using the WITH CHECK OPTION clause in the filter condition, the view definition does not support the JOIN operation.

Examples

Create the test tables tbl1 and tbl2.

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

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

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

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

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

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

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

  1. Create the 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 query 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 query 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