CREATE VIEW|V4.3.1| docs|Distributed Database

CREATE VIEW

Last Updated：2025-12-04 08:27:57 Updated

Description

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 is not a table that is stored in a database. Instead, a view is derived from the SELECT statement in the CREATE VIEW statement each time it is used.

A view derived from two or more tables or views is called a join view (JV). A join view that allows DML operations is called an updatable join view (UJV) or modifiable join view (MJV) and involves two or more base tables or views. The UJV or MJV is not limited by the WITH READ ONLY clause.

To be updatable, a view must meet certain requirements. For example, you can insert data, update data, or delete data from only one base table at a time for a join view.

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

Syntax

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

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

column_name_list:
    column_name [, column_name ...]

view_with_opt:  
      WITH READ ONLY
    | WITH CHECK OPTION
    | empty

Parameter explanation

Parameter	Description
OR REPLACE	Specifies to recreate the view with the new definition if the view already exists.
[NO] FORCE	Specifies to create the view without considering whether the base table or referenced objects exist, or whether the owner of the schema where the view is to be created has the privileges. If the base table exists and the owner of the schema where the view is to be created has the privileges, specify `NOFORCE`. `NOFORCE` is the default setting.
view_name	The name of the view.
column_name_list	The view must have unique column names, just like the base table. By default, the column names retrieved by the `SELECT` statement are used as the column names of the view. To explicitly define the column names of the view, you can use the optional `column_name_list` clause. Separate the column names with commas. The number of column names in `column_name_list` must be equal to the number of columns retrieved by the `SELECT` statement. Columns can be simply referenced or be expressions that contain functions, constant values, and operators.
select_stmt	A `SELECT` statement that defines the view. This statement can select data from a base table or another view. For more information, see SIMPLE SELECT.
--string	An optional parameter to comment on the view. The comment is stored in the view definition. Note You can place the `--string` parameter at any position in the `select_stmt`.
view_with_opt	You can add the `WITH READ ONLY` option when creating or replacing a view to specify that the view is used only for reading data and cannot be used for inserting, updating, or deleting data. You can add the `WITH CHECK OPTION` option when creating a view to ensure that the inserted or updated data meets the definition of the view. Note The `WITH CHECK OPTION` syntax in Oracle mode does not support the `LOCAL` or `CASCADED` parameter. The default value is `CASCADED`.

Examples

Create a view named view1 by selecting the col1 and col2 columns from the tbl1 table.

-- Create a table named `tbl1`.
obclient> CREATE TABLE tbl1 (col1 INT, col2 VARCHAR(50));
Query OK, 0 rows affected

-- Insert data into `tbl1`.
obclient> INSERT INTO tbl1 (col1, col2) VALUES (1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.046 sec)
Records: 3  Duplicates: 0  Warnings: 0

obclient> CREATE OR REPLACE FORCE VIEW view1(vcol1, vcol2) AS SELECT col1, col2 FROM tbl1;
Query OK, 0 rows affected

Create a view named view2 by selecting the col1 and col2 columns from the tbl2 table. The view2 contains only the data that satisfies the tbl2.col2 > 1 condition.

-- Create a table named `tbl2`.
obclient> CREATE TABLE tbl2 (a INT, b INT);
Query OK, 0 rows affected

-- Insert data into `tbl2`.
obclient> INSERT INTO tbl2 (a, b) VALUES (23, 2), (76, 9), (456, 1);
Query OK, 3 rows affected (0.046 sec)

obclient> CREATE VIEW view2 AS SELECT * FROM tbl2 WHERE tbl2.b > 1 WITH CHECK OPTION;
Query OK, 0 rows affected (0.064 sec)

Create a view named view3 by selecting the col1 and col2 columns from the tbl2 table, and add a comment to the view3.

obclient [SYS]> CREATE VIEW view3
  AS SELECT col1, --col1 comment1
    col2 --col1 comment2
    FROM tbl2;

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

Query the comments of a view by using the SHOW CREATE VIEW statement.

obclient [SYS]> SHOW CREATE VIEW view3;

The return result is as follows:

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

Query the comments of a view by using the DBMS_METADATA.GET_DDL statement.

obclient [SYS]> SELECT DBMS_METADATA.GET_DDL('VIEW', 'VIEW3', 'SYS') FROM dual;