Description
This statement is used to create a view.
Limitations and considerations
If the
OR REPLACEclause is specified, the statement can replace an existing view.A view does not actually exist as a table in the database. Instead, a view is derived as the result of the
SELECTstatement specified in theCREATE VIEWstatement and re-derived each time it is used.If the
FROMclause references two or more tables or views, the view is a join view. A join view that supports DML operations is called an updatable join view or a modifiable join view. It involves two or more base tables or views and allowsINSERT,UPDATE, andDELETEoperations.To make a view updatable, it must meet certain requirements. For example, you can perform an
INSERT,UPDATE, orDELETEoperation on 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} | FORCE] VIEW view_name [(column_name_list)] [TABLE_ID COMP_EQ INTNUM] AS view_subquery view_with_opt
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} | FORCE | Specifies to create the view without considering whether the base table or referenced object type exists or whether the owner of the schema where the view is to be created has the privileges to create the view. If the FORCE option is specified and the base table does not exist or the owner of the schema where the view is to be created does not have the privileges to create the view, the system will create a dependent view. If the NOFORCE option is specified and the base table does not exist or the owner of the schema where the view is to be created does not have the privileges to create the view, the system will return an error. The NOFORCE option is the default. |
| view_name | The name of the view. |
| select_stmt | A SELECT statement that defines the view. The statement can select data from base tables or other views. |
| 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 column names of the view. To specify explicit column names for the view, you can use the optional column_name_list clause. The column names are separated by commas. The number of column names in column_name_list must be the same as the number of columns retrieved by the SELECT statement. The columns retrieved by the SELECT statement can be simple references to the table columns or expressions that contain functions, constant values, and operators. |
| view_with_opt | WITH READ ONLY is an option for creating or replacing a view. It specifies that the view can be used only for reading data and cannot be used for inserting, updating, or deleting data.WITH CHECK OPTION is a syntax for creating a view. It ensures that the data inserted or updated into the view meets the definition of the view.
NoticeIn Oracle mode, the |
Examples
Create a view named view1 by using the col1 and col2 columns from the tbl1 table.
-- Create the tbl1 table.
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 using the a and b columns from the tbl2 table. The view2 table contains only the data that meets the tbl2.b > 1 condition.
-- Create the tbl2 table.
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 using the a and b columns from the tbl2 table, and add a comment to the view3 table.
obclient> CREATE VIEW view3 AS SELECT a, --test.a
-> b --test.b
-> from tbl2;
Query OK, 0 rows affected
You can execute the show create view or dbms_metadata.get_ddl statement to query the comment on a view.
obclient> SHOW CREATE VIEW view3;
+-------+------------------------------------------------------------------------------+----------------------+----------------------+
| VIEW | CREATE VIEW | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+-------+------------------------------------------------------------------------------+----------------------+----------------------+
| VIEW3 | CREATE VIEW "VIEW3" ("A", "B") AS select a, -- test.a
b -- test.b
from tbl2 | utf8mb4 | utf8mb4_bin |
+-------+------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.003 sec)
obclient> SELECT dbms_metadata.get_ddl('VIEW', 'VIEW3', 'SYS') FROM dual;
+------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('VIEW','VIEW3','SYS') |
+------------------------------------------------------------------------------+
| CREATE VIEW "VIEW3" ("A", "B") AS select a, -- test.a
b -- test.b
from tbl2 |
+------------------------------------------------------------------------------+
1 row in set (0.162 sec)
