You can use the ALTER VIEW statement to modify the definition of a view. This topic describes how to modify the definition of a normal view.
Syntax
ALTER VIEW view_name [(column_name_list)] AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| select_stmt | A SELECT statement that defines a view. You can use this statement to query base tables or other views. |
| column_name_list | The column names in the view, which must be unique, like those in a base table. 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. |
Examples
Check the schema of the
vview.obclient> DESC v; +--------+---------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +--------+---------------+------+-----+---------+-------+ | ID | NUMBER(38) | YES | NULL | NULL | NULL | | NAME | VARCHAR2(256) | YES | NULL | NULL | NULL | | C_DATE | DATE | YES | NULL | NULL | NULL | | AGE | NUMBER(38) | YES | NULL | NULL | NULL | | SEX | VARCHAR2(256) | YES | NULL | NULL | NULL | +--------+---------------+------+-----+---------+-------+ 5 rows in setCheck the creation of the
vview.obclient> show create table v\G *************************** 1. row *************************** VIEW: V CREATE VIEW: CREATE VIEW "V" AS select "A"."ID1" AS "ID","A"."NAME" AS "NAME","B"."C_DATE" AS "C_DATE","B"."AGE" AS "AGE","A"."SEX" AS "SEX" from "SYS"."T1" "A","SYS"."T2" "B" where ("A"."ID1" = "B"."ID2") CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_bin 1 row in setView the structure of the
t3table.obclient> DESC t3; +--------+---------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +--------+---------------+------+-----+---------+-------+ | ID | NUMBER | YES | NULL | NULL | NULL | | NAME | VARCHAR2(128) | YES | NULL | NULL | NULL | | C_DATE | DATE | YES | NULL | NULL | NULL | | AGE | NUMBER(38) | YES | NULL | NULL | NULL | | SEX | VARCHAR2(128) | YES | NULL | NULL | NULL | +--------+---------------+------+-----+---------+-------+ 5 rows in setChange the definition of the
vview to that of thet3table.obclient> CREATE OR REPLACE FORCE VIEW v AS SELECT * FROM t3; Query OK, 0 rows affectedCheck the schema of the
vview again.obclient> DESC v; +--------+---------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +--------+---------------+------+-----+---------+-------+ | ID | NUMBER | YES | NULL | NULL | NULL | | NAME | VARCHAR2(128) | YES | NULL | NULL | NULL | | C_DATE | DATE | YES | NULL | NULL | NULL | | AGE | NUMBER(38) | YES | NULL | NULL | NULL | | SEX | VARCHAR2(128) | YES | NULL | NULL | NULL | +--------+---------------+------+-----+---------+-------+ 5 rows in set
