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.
Views are not stored as tables in the database. Instead, they are 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 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 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] 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
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Specifies that if the view to be created already exists, it will be recreated with the new definition. |
| [NO] FORCE | If you want to create the view without considering whether the view has base tables or referenced objects, or whether the owner of the schema where the view is located has the necessary privileges, specify FORCE. If you want to create the view only when the base tables exist and the owner of the schema where the view is located has the necessary privileges, specify NOFORCE. The default is NOFORCE. |
| view_name | The name of the view. |
| column_name_list | The view must have unique column names, just like base tables. By default, the column names retrieved by the SELECT statement will be used as the view column names. To define specific names for the view columns, use the optional column_name_list clause, separating the IDs with commas. The number of names in column_name_list must equal the number of columns retrieved by the SELECT statement. The columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions involving functions, constants, operators, etc. |
| select_stmt | A SELECT statement that defines the view. It can select data from base tables or other views. For more information about SELECT statements, see SIMPLE SELECT. |
| --string | Optional. Used to add comments to the view. The comments are stored in the view definition.
NoteYou can add comments to |
| view_with_opt | WITH READ ONLY is an option when creating or replacing a view, specifying that the view can only be used for reading data and not for insert, update, or delete operations. WITH CHECK OPTION is a syntax for creating a view, ensuring that inserted or updated data meets the view's definition conditions.
NoticeIn Oracle mode, |
Examples
Create a view named
view1by selecting thecol1andcol2columns from thetbl1table.-- Create the tbl1 table. obclient> CREATE TABLE tbl1 (col1 INT, col2 VARCHAR(50)); Query OK, 0 rows affected -- Insert data into the tbl1 table. 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 affectedCreate a view named
view2by selecting thecol1andcol2columns from thetbl2table. The viewview2contains all data that satisfies the conditiontbl2.col2 > 1.-- Create the tbl2 table. obclient> CREATE TABLE tbl2 (a INT, b INT); Query OK, 0 rows affected -- Insert data into the tbl2 table. 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
view3by selecting thecol1andcol2columns from thetbl2table, and add comments to the viewview3.obclient [SYS]> CREATE VIEW view3 AS SELECT col1, --col1 comment1 col2 --col1 comment2 FROM tbl2;Query the view comments by using
SHOW CREATE VIEWorDBMS_METADATA.GET_DDL.Query the view comments by using
SHOW CREATE VIEW.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 setQuery the view comments by using
DBMS_METADATA.GET_DDL.obclient [SYS]> SELECT DBMS_METADATA.GET_DDL('VIEW', 'VIEW3', 'SYS') FROM dual;The return result is as follows:
+--------------------------------------------------------------------------------------------------------------+ | DBMS_METADATA.GET_DDL('VIEW','VIEW3','SYS') | +--------------------------------------------------------------------------------------------------------------+ | CREATE VIEW "VIEW3" ("COL1", "COL2") AS SELECT col1, --col1 comment1 col2 --col1 comment2 FROM tbl2 | +--------------------------------------------------------------------------------------------------------------+ 1 row in set