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 AI Database privileges, see Permission classification.
Syntax
ALTER VIEW view_name [(column_name_list)]
AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
Parameter |
Description |
|---|---|
| view_name | Specifies the view name. |
| column_name_list | Optional. Specifies the list of column names for the view. If the column name list is not specified, 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 that use functions, constant values, operators, and so on. View column names have the following limitations:
|
| column_name | The column name of the view. |
| select_stmt | The query used to define the view (SELECT) statement. It defines the view, which can select data from base tables or other views. For details about the structure and options of the query statement, see SELECT. |
Examples
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 a view named
v1based on the 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 set