Purpose
You can use this statement to create a view. By specifying the OR REPLACE clause, you can also replace an existing view.
In a database, views do not exist as tables, but are derived from the execution results of the SELECT statement specified in the CREATE VIEW statement. A view is derived each time the statement is used.
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 limited by the WITH READ ONLY clause.
A view must meet several criteria to be updatable. For example, a general rule is that an INSERT, UPDATE, or DELETE operation on a join view can modify only one base table at a time.
Syntax
CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt [WITH CHECK OPTION];
select_stmt:
SELECT column_name, [--string]
[, column_name, [--string] ...]
FROM from_list
[WHERE condition]
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Specifies to re-create a view that already exists by using a new definition. |
| [NO] FORCE | If you want to create a view regardless of whether the base tables of the view or the referenced object types exist, or the owner of the schema containing the view has privileges on them, specify FORCE. If you want to create a view only when the view has base tables and the owner of the schema containing the view has privileges on them, specify NOFORCE. The default value is NOFORCE. |
| view_name | The name of the view. |
| column_name_list | The column names in the view, which must be unique, like those in a base table. By default, the column names retrieved by the SELECT statement are used as the column names in the view. You can also use the optional column_name_list clause to define column names for a view. Separate multiple 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. The SELECT statement can directly reference the columns of a table. You can also use functions, constants, and operators in the statement to retrieve columns. |
| select_stmt | A SELECT statement that defines a view. You can use this statement to query base tables or other views. For more information about the SELECT statement, see SIMPLE SELECT. |
| –string | Optional. Specifies to add a comment to the view. The comments are stored in the view definition.
NoteYou can add |
| WITH CHECK OPTION | WITH CHECK OPTION ensures that the data inserted or updated to the view meets the conditions in the view definition.
NoticeIn the Oracle mode of OceanBase Database, you cannot manually set |
Examples
Retrieve the
col1andcol2columns of thetbl1table to create a view namedview1.obclient [SYS]> CREATE OR REPLACE FORCE VIEW view1(vcol1, vcol2) AS SELECT col1, col2 FROM tbl1;Create a view named
view2based on thecol1andcol2columns of thetbl2table.view2contains all data that meets thetbl2.col2 > 1condition.Create a table named
tbl2.obclient [SYS]> CREATE TABLE tbl2 (col1 INT, col2 INT);Create a view named
view2.obclient [SYS]> CREATE VIEW view2 AS SELECT * FROM tbl2 WHERE tbl2.col2 > 1 WITH CHECK OPTION;
Create a view named
view3based on thecol1andcol2columns of thetbl2table, and add comments toview3.obclient [SYS]> CREATE VIEW view3 AS SELECT col1, --col1 comment1 col2 --col1 comment2 FROM tbl2;Query comments on a view by using
SHOW CREATE VIEWorDBMS_METADATA.GET_DDL.Use
SHOW CREATE VIEWto query comments on a 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 setUse
DBMS_METADATA.GET_DDLto query comments on a view.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