This topic describes how to create, modify, and drop views. A view displays the result of a query. You can use views in most cases where you can use tables. If the data you frequently access is distributed across multiple tables, a view is the best choice.
Create a regular view
You can use the CREATE VIEW statement to create a regular view. After creation, you can execute DML statements on the view.
Syntax
The SQL syntax for creating a view is as follows:
create_view_stmt:
CREATE [OR REPLACE] VIEW view_name [(column_name_list)] AS select_stmt;
[WITH [CASCADED | LOCAL] CHECK OPTION];
column_name_list:
column_name [, column_name ...]
Fields are described as follows:
OR REPLACE: If a view with the specified name already exists, a new view is created based on the new definition.select_stmt: Defines theSELECTstatement of the view. You can obtain the information to be used in this statement from a base table or other views.column_name_list: Like a base table, a view must have unique column names. By default, the column names retrieved by theSELECTstatement are used as the column names in the view.You can also use the optional
column_name_listclause to define explicit names for view columns. Separate multiple column names with commas (,). The number of names incolumn_name_listmust be equal to the number of columns retrieved by theSELECTstatement.The
SELECTstatement can directly reference the columns of a table. You can also use functions, constants, and operators in the statement to retrieve columns.The
WITH CHECK OPTIONclause prevents the system from inserting or updating rows except for the rows that meet the query condition defined by theWHEREclause inselect_statement. When you create a view by using theWITH CHECK OPTIONclause, you can add theCASCADEDorLOCALkeyword to clarify the check scope.If the
LOCALkeyword is used, the system checks theWHEREclause of the view, then recursively checks the base views and applies the same rules.If the
CASCADEDkeyword is used, the system checks theWHEREclause of the view, then recursively checks the underlying views, addsWITH CASCADED CHECK OPTIONto these views without changing their definitions, and applies the same rules.If neither
CASCADEDnorLOCALis specified inWITH CHECK OPTION, theCASCADEDkeyword is used by default.If the
WITH CHECK OPTIONclause is not used when you create a view, the system does not check theWHEREclause of the view but recursively checks the underlying views and applies the same rules.Specifying the
WITH CHECK OPTIONclause is supported when the filter condition (where_clause) of a view contains a subquery.Notice
When you use the
WITH CHECK OPTIONclause in a filter condition, ensure that the following conditions are met:- The creation statement must not create a non-updatable view.
- Do not use
JOINoperations in the view definition.
Example
obclient> CREATE VIEW stock_item
AS
SELECT /*+ leading(s) use_merge(i) */
i_price, i_name, i_data, s_i_id, s_w_id, s_order_cnt, s_ytd, s_remote_cnt, s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
FROM stok s, item i
WHERE s.s_i_id = i.i_id;
Query OK, 0 rows affected
Modify a regular view
You can use the CREATE OR REPLACE VIEW statement to modify a regular view.
Modify the stock_item view.
obclient> CREATE OR REPLACE VIEW stock_item
AS
SELECT /*+ leading(s) use_merge(i) */
i_price, i_name, i_data, s_i_id, s_w_id, s_order_cnt, s_ytd, s_remote_cnt, s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
FROM stok s, item i
WHERE s.s_i_id = i.i_id;
Query OK, 0 rows affected (0.02 sec)
Drop a view
You can use the DROP VIEW statement to drop one or more views. Dropping a view does not drop the tables referenced by the view.
If the current view is referenced by other views, dropping the view will cause queries that depend on it to fail.
When dropping a view, ensure the current user has the DROP privilege on that view.
Syntax
The SQL syntax for dropping a view is as follows:
drop_view_stmt:
DROP VIEW [IF EXISTS] view_name_list;
view_name_list:
view_name [, view_name_list]
Fields are described as follows:
Using the
IF EXISTSkeyword prevents errors due to the view not existing.If
view_name_listcontains both existing and non-existent views, the execution may report an error, but the existing views are still dropped.
Example
Drop the V1 view.
obclient> DROP VIEW V1;