Manage standard views

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 standard view

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

The SQL syntax for creating a view in Oracle-compatible mode 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

The parameters are described as follows:

• OR REPLACE: specifies to re-create a view that already exists by using a new definition.

• [NO] FORCE: If you do not want to check whether the base tables exist or whether referenced object types exist, or whether the owner of the schema of the view has required privileges when creating the view, you must specify FORCE.

  If you have confirmed that the base tables exist and the owner of the schema of the view has required privileges, you can specify NO FORCE. The default value is NO FORCE.

• column_name_list: the unique column names used in the view. Each base table must also have unique column names. 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: the SELECT statement that defines the view. The information in this statement can be retrieved from a base table or another view.

  Note

  • In Oracle-compatible mode of OceanBase Database, you can use the --string option to add comments to a view when you create it. In this case, the comments are stored in the view definition.
    • You can add comments to select_stmt at any position.

  • WITH READ ONLY: an option that you can specify when you create or replace a view. This option specifies that the view can be used only for reading data and cannot be used for DML operations (such as INSERT, UPDATE, and DELETE). In other words, you cannot insert, update, or delete data through the view.

  • WITH CHECK OPTION: a syntax for creating a view. This syntax ensures that the data that you insert or update through the view meets the definition conditions of the view. In other words, you can insert or update data through the view, but the data must meet the definition conditions of the view.

    Note

    In Oracle-compatible mode, the WITH CHECK OPTION syntax does not support the LOCAL or CASCADED options. By default, the CASCADED option is used.

    OceanBase Database also supports the WITH CHECK OPTION option when the filtering condition clause (where_clause) contains subqueries.

    Notice

    When you use the `WITH CHECK OPTION` option in the filtering condition, the view definition cannot contain a JOIN operation.

  Here are some examples:

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

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

  • Create a view named view4 based on the col1 and col2 columns of the tbl2 table, and add comments to view4.

    1. Create a table named tbl2.

       obclient [SYS]> CREATE TABLE tbl2 (col1 INT, col2 INT);
       

    2. Create a view named view4.

       obclient [SYS]> CREATE VIEW view4
         AS SELECT col1, --col1 comment1
           col2 --col1 comment2
           FROM tbl2;
       

  Modify a standard view

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

  Example: 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 execute the DROP VIEW statement to drop one or more views at a time. When a view is dropped, tables referenced by the view are not dropped.

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

  Before you drop a view, make sure that you have the DROP privilege on the view.

  The SQL syntax for dropping a view in Oracle-compatible mode is as follows:

  obclient>DROP VIEW view_name;
  

  Example: Drop the v1 view.

  obclient> DROP VIEW v1;
  

  References

  • CREATE VIEW

  • DROP VIEW