Manage views

This topic describes how to create, modify, and drop views.

A view displays the query results of tables. You can use a view in most places where you can use a table. When the data you frequently access is distributed across multiple tables, using a view is the best approach.

Create a standard view

You can use the CREATE VIEW statement to create a standard view. After the view is created, you can execute DML statements on the view.

In Oracle-compatible mode, the SQL syntax for creating a view is as follows:

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

column_name_list:
    column_name [, column_name ...]

view_check_option:
    WITH READ ONLY
    | WITH CHECK OPTION

Syntax elements:

• OR REPLACE: specifies that if a view with the specified name already exists, the system recreates the view with the new definition.

• [NO] FORCE: if you do not want to consider whether the base table for the view exists, whether referenced object types exist, or whether the owner of the schema where the view is located has the required privileges when creating the view, specify FORCE.

  If you have confirmed that the base table exists and the owner of the schema where the view is located has the required privileges, specify NO FORCE. The default is NO FORCE.

• column_name_list: like a base table, a view must have unique column names that cannot be duplicated. By default, the column names retrieved by the SELECT statement are used as the view column names.

  If you want to define explicit names for view columns, you can use the optional column_name_list clause, with identifiers separated by 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, or expressions that use functions, constant values, operators, and so on.

• select_stmt: specifies the SELECT statement that defines the view. The information in the statement can be selected from base tables or other views.

• WITH READ ONLY: an option when creating or replacing a view. It specifies that the view can be used only to read data, not for DML operations (INSERT, UPDATE, or DELETE). In other words, you cannot insert, update, or delete data through the view.

• WITH CHECK OPTION: a syntax option for creating views that ensures inserted or updated data satisfies the view definition conditions. DML operations are allowed through the view, but the resulting data must satisfy the view definition conditions.

  Note

  In Oracle-compatible mode, the WITH CHECK OPTION syntax does not support specifying LOCAL or CASCADED. The default is CASCADED.

  For V4.2.5, starting from V4.2.5 BP7, you can specify the WITH CHECK OPTION clause when the view filter condition (where_clause) contains a subquery.

  Notice

  When you use the WITH CHECK OPTION clause in a filter condition, the view definition does not support the JOIN operation.

Example:

Select the col1 and col2 columns from table tbl1 to create the view view1.

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

Modify a standard view

You can use the CREATE OR REPLACE VIEW statement to modify a standard view.

Example:

Modify the view stock_item.

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;

Drop a view

You can use the DROP VIEW statement to drop one or more views. Dropping a view does not delete the tables referenced by the view.

If the current view is referenced by other views, dropping the current view will cause queries on the other views that depend on it to fail.

Before you drop a view, make sure that the current user has the DROP privilege on the view.

In Oracle-compatible mode, the SQL syntax for dropping a view is as follows:

DROP VIEW view_name;

Example:

Drop the view v1.

obclient> DROP VIEW v1;

References

• CREATE VIEW

• DROP VIEW