CREATE VIEW

Purpose

This statement is used to create a view.

Limitations and considerations

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

• A view does not actually exist 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 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 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 mode.

Syntax

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

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 prevents inserting, updating, or deleting data through the view.

• WITH CHECK OPTION: A syntax option for creating a view. It ensures that any data inserted or updated through the view meets the view's definition conditions. This allows DML operations through the view, but the data must satisfy the view's definition conditions after the operation.

  Note

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

  Starting from V4.2.5 BP7, views with filtering conditions (where_clause) that include subqueries support the WITH CHECK OPTION clause.

  Notice

  When using the WITH CHECK OPTION clause in filtering conditions, the JOIN operation is not supported in the view definition.

Examples

Create test tables tbl1 and tbl2.

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

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

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

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

• Create a view v2_t2 based on the col1 and col2 columns from table tbl2, containing all data that meets the condition tbl2.col2 > 1.

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

• Create a view v3_t3 based on the col1 and col2 columns from 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 using SHOW CREATE VIEW or DBMS_METADATA.GET_DDL.

     • Query the view comments 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 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 filtering condition subquery.

  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 views
• DROP VIEW