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

Syntax

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

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

column_name_list:
    column_name [, column_name ...]

view_check_option:
    WITH [LOCAL | CASCADED] CHECK OPTION

Field description:

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

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

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

• The WITH CHECK OPTION clause prevents the system from inserting or updating rows other than those for which the WHERE clause in select_stmt is true. When you create a view with the WITH CHECK OPTION clause, you can add the CASCADED or LOCAL keyword to specify the scope of the check.

  • If you specify the LOCAL keyword, the system checks the WHERE clause of the view and recursively checks the base views, applying the same rules.

  • If you specify the CASCADED keyword, the system checks the WHERE clause of the view and recursively checks the underlying views, adding WITH CASCADED CHECK OPTION to them (the definitions of the underlying views remain unchanged), and applying the same rules.

  • If you do not specify the CASCADED or LOCAL keyword in WITH CHECK OPTION, the CASCADED keyword is used by default.

  • If you do not use the WITH CHECK OPTION clause, the system does not check the WHERE clause of the view when creating the view. The system then recursively checks the underlying views and applies the same rules.

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

    • In MySQL-compatible mode, make sure the CREATE statement does not define a non-updatable view.
    • The view definition does not support the JOIN operation.

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;

Modify a standard view

You can use the CREATE OR REPLACE VIEW statement to modify a standard 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;

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

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

Syntax

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

DROP VIEW [IF EXISTS] view_name_list;

view_name_list:
    view_name [, view_name_list]

Field description:

• The IF EXISTS keyword prevents an error from being returned when the view does not exist.

• If view_name_list contains both existing and non-existing views, an error may be returned, but the existing views are still dropped.

Example

Drop the V1 view.

obclient> DROP VIEW V1;

Check a view

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

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

Example:

1. When the view v_test_tbl1 does not exist, check the return information for v_test_tbl1.

   obclient> CHECK TABLE v_test_tbl1;
   

   The query 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 the table test_tbl1.

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

3. Create the view v_test_tbl1.

   obclient> CREATE OR REPLACE VIEW v_test_tbl1
       AS SELECT *
          FROM test_tbl1;
   

4. Check the view v_test_tbl1 again and review the return information.

   obclient> CHECK TABLE v_test_tbl1;
   

   The query result is as follows:

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

5. Drop the table test_tbl1.

   obclient> DROP TABLE test_tbl1;
   

6. Check the view v_test_tbl1 again and review the return information.

   obclient> CHECK TABLE v_test_tbl1;
   

   The query 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