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 mode is as follows:

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

column_name_list:
    column_name [, column_name ...]

The parameters are described as follows:

• OR REPLACE: If the name of the view to be created already exists, a new view is created based on the specified definition.

• select_stmt: defines the SELECT statement of the view. You can obtain the information to be used in this statement from a base table or other views.

  Note

  • When you create a view in the Oracle mode of OceanBase Database, you can use the --string option to add a comment on the view. The comment is stored in the view definition.
  • You can add the --string option at any position in select_stmt.

• [NO] FORCE: If you are not sure whether the view to be created involves base tables or referenced object types, or whether the owner of the schema of the view has required privileges, you must specify FORCE.

  If base tables are involved, 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 clause column_name_list 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.

Here is an example:

• Retrieve the col1 and col2 columns of the tbl1 table to create a view named 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 a comment on the view4 view.

  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 you drop a view that is referenced by another view, queries on the view that references the dropped view will 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 mode is as follows:

obclient>DROP VIEW view_name;

Example: Drop the v1 view.

obclient> DROP VIEW v1;

References

• CREATE VIEW

• DROP VIEW