Manage 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.

Syntax

The syntax for creating a view in MySQL mode 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 ...]

The parameters in the syntax are described as follows:

• OR REPLACE: specifies to re-create a view that already exists by using a new 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.

• 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.

• WITH CHECK OPTION clause: prevents the system from inserting or updating rows that do not meet the query condition defined by the WHERE clause in select_statement. When you create a view by using the WITH CHECK OPTION clause, you can add the CASCADED or LOCAL keyword to clarify the check scope.

  • If the LOCAL keyword is used, the system checks the WHERE clause of a view, recurses the check to the base view, and then applies identical rules.

  • If the CASCADED keyword is used, the system checks the WHERE clause of a view, recurses the check to underlying views, adds WITH CASCADED CHECK OPTION to these views without changing their definitions, and then applies identical rules.

  • If neither the CASCADED nor the LOCAL keyword is used in WITH CHECK OPTION, the CASCADED keyword takes effect by default.

  • If the WITH CHECK OPTION clause is not used when you create a view, the system does not check the WHERE clause of this view but recurses the check to underlying views and then applies identical rules.

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 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.

Syntax

The syntax for dropping a view in MySQL mode is as follows:

drop_view_stmt:
    DROP VIEW [IF EXISTS] view_name_list;

view_name_list:
    view_name [, view_name_list]

The parameters in the syntax are described as follows:

• The IF EXISTS keyword prevents the error that is returned because the view to be dropped does not exist.

• If view_name_list contains views that do not exist, an error may be returned during execution but the specified views that exist are dropped.

Example

Drop the V1 view.

obclient> DROP VIEW V1;

Check a view

You can use the CHECK TABLE statement to check whether a view exists in the database or whether objects referenced in a view are valid.

For more information about the CHECK TABLE statement, see CHECK TABLE.

Here is an example:

1. Assume that the v_test_tbl1 view does not exist. Execute the following statement and view the return result:

   obclient [test]> CHECK TABLE v_test_tbl1;
   

   The return result is as follows:

   +------------------+-------+----------+----------------------------------------+
   | Table            | Op    | Msg_type | Msg_text                               |
   +------------------+-------+----------+----------------------------------------+
   | test.v_test_tbl1 | check | Error    | Table 'test.v_test_tbl1' doesn't exist |
   | test.v_test_tbl1 | check | status   | Operation failed                       |
   +------------------+-------+----------+----------------------------------------+
   2 rows in set
   

2. Create a table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT, col2 VARCHAR(18));
   

3. Create a view named v_test_tbl1.

   CREATE OR REPLACE VIEW v_test_tbl1 AS SELECT * FROM test_tbl1;
   

4. Execute the following statement and view the return result:

   obclient [test]> CHECK TABLE v_test_tbl1;
   

   The return result is as follows:

   +------------------+-------+----------+----------+
   | Table            | Op    | Msg_type | Msg_text |
   +------------------+-------+----------+----------+
   | test.v_test_tbl1 | check | status   | OK       |
   +------------------+-------+----------+----------+
   1 row in set
   

5. Drop the test_tbl1 table.

   obclient [test]> DROP TABLE test_tbl1;
   

6. Execute the following statement and view the return result:

   obclient [test]> CHECK TABLE v_test_tbl1;
   

   The return result is as follows:

   +------------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------+
   | Table            | Op    | Msg_type | Msg_text                                                                                                                           |
   +------------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------+
   | test.v_test_tbl1 | check | Error    | View 'test.v_test_tbl1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
   | test.v_test_tbl1 | check | error    | Corrupt                                                                                                                            |
   +------------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------+
   2 rows in set
   

References

• CREATE VIEW

• DROP VIEW