CREATE VIEW|V4.2.5| docs|Distributed Database

CREATE VIEW

Last Updated：2025-12-04 07:10:03 Updated

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 stored as a table in the database. 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 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. For example, 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 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

Parameter	Description
OR REPLACE	Optional. If specified, the view will be created with the new definition if the view already exists.
{NO FORCE} \| FORCE	Optional. Controls whether the view creation is enforced. `NO FORCE`: The default value. The view will be created only if the base table exists and the owner of the schema where the view is located has the necessary permissions. `FORCE`: The view will be created regardless of whether the base table or referenced object type exists, and whether the owner of the schema where the view is located has the necessary permissions.
view_name	The name of the view.
column_name_list	Optional. The list of column names for the view. If not specified, the column names retrieved by the `SELECT` statement will be used as the view column names. The columns retrieved by the `SELECT` statement can be simple references to table columns or expressions involving functions, constants, operators, etc. The view column names have the following limitations: Each column name must be unique and cannot be repeated. The number of names in `column_name_list` must equal the number of columns retrieved by the `SELECT` statement.
select_stmt	The `SELECT` statement used to define the view. It specifies the view's definition, which can be queried from the base table or other views. For specific structures and options of the query statement, see SELECT statement.
view_check_option	Optional. Specifies additional properties of the view. For more information, see view_check_option.

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 cannot be used for DML operations (INSERT, UPDATE, DELETE). That is, data cannot be inserted, updated, or deleted 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. That is, DML operations are allowed through the view, but the data after the operation must meet the view's definition conditions.

Note

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

Example

Create the test tables tbl1 and tbl2.

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

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

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

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

Create the view v2_t2 by selecting the col1 and col2 columns from the tbl2 table, and include all data that satisfies the condition tbl2.col2 > 1 in the view.
```
obclient> CREATE VIEW v2_t2
    AS SELECT *
       FROM tbl2
       WHERE tbl2.col2 > 1 WITH CHECK OPTION;
```

Create the view v3_t3 by selecting the col1 and col2 columns from the tbl2 table, and add comments to the view v3_t3.

Create the view v3_t3.

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

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

Query the comments of the view 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 comments of the view by using DBMS_METADATA.GET_DDL.

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