Purpose
This statement is used to modify the definition of a view.
Privilege requirements
To execute the ALTER VIEW statement, the current user must have the DROP and CREATE VIEW privileges. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.
Syntax
- Modify view definition
ALTER VIEW view_name { COMPILE | RECOMPILE };
column_name_list: column_name [, column_name ...]
column_name_list:
column_name [, column_name ...]
Recompile views
ALTER VIEW [IF EXISTS] view_name { COMPILE | RECOMPILE };
Parameters
Parameter |
Description |
|---|---|
| view_name | The name of the view. |
| column_name_list | Optional. The list of column names of the view. If you do not specify a column name list, the column names retrieved by the SELECT statement will be used as the view column names. The columns retrieved by the SELECT statement can be simple references to table columns, or expressions involving functions, constants, operators, and so on. The view column names have the following limitations:
|
| column_name | The name of a view column. |
| select_stmt | The query (SELECT) statement used to define the view. It specifies the definition of the view, which can select data from base tables or other views. For more information about the structure and options of the query statement, see SELECT statement. |
| COMPILE | A keyword that instructs the database to recompile the view. You can use this syntax to make the view effective again after the structure of the base table on which the view depends changes. |
| RECOMPILE | Has the same function as COMPILE. It instructs the database to recompile the view. The two keywords are interchangeable. |
Examples
Modify view definition
Modify the definition of view v1 to select data from test_tbl2.
Create the table
test_tbl1.CREATE TABLE test_tbl1 (col1 INT, col2 INT);Create view
v1based on tabletest_tbl1.CREATE VIEW v1 AS SELECT * FROM test_tbl1;Create the table
test_tbl2.CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);Modify the definition of view
v1to select data fromtest_tbl2.ALTER VIEW v1 AS SELECT * FROM test_tbl2;View the definition of view
v1again.SHOW CREATE VIEW v1;The return result is as follows:
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE VIEW `v1` AS select `db_test`.`test_tbl2`.`col1` AS `col1`,`db_test`.`test_tbl2`.`col2` AS `col2`,`db_test`.`test_tbl2`.`col3` AS `col3` from `db_test`.`test_tbl2` | utf8mb4 | utf8mb4_general_ci | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in setUse
COMPILEto recompile viewv1.ALTER VIEW v1 COMPILE;Use
RECOMPILEto recompile viewv1.ALTER VIEW v1 RECOMPILE;