A view is a virtual table that does not exist physically. You can create a view by using an existing table or a query. VIEW and VCLASS are used interchangeably.
Use CREATE VIEW statement to create a view.
CREATE [OR REPLACE] {VIEW | VCLASS} <view_name>
[ <subclass_definition> ]
[ ( <view_column_def_comma_list> ) ]
[ CLASS ATTRIBUTE
( <column_definition_comma_list> ) ]
[ METHOD <method_definition_comma_list> ]
[ FILE <method_file_comma_list> ]
[ INHERIT <resolution_comma_list> ]
[ AS <select_statement> ]
[ WITH CHECK OPTION ]
<view_column_definition> ::= <column_definition> | <column_name>
<column_definition> :
column_name column_type [ <default_or_shared> ] [ <column_constraint_list>]
<default_or_shared> :
{SHARED [ <value_specification> ] | DEFAULT <value_specification> } |
AUTO_INCREMENT [ (seed, increment) ]
<column_constraint> :
NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY REFERENCES...
<subclass_definition> :
{ UNDER | AS SUBCLASS OF } table_name_comma_list
<method_definition> :
[ CLASS ] method_name
[ ( [ argument_type_comma_list ] ) ]
[ result_type ]
[ FUNCTION function_name ]
<resolution> :
[ CLASS ] { column_name | method_name } OF superclass_name
[ AS alias ]
AS select_statement : A valid SELECT statement must be specified. A view is created based on this.
WITH CHECK OPTION : If this option is specified, the update or insert operation is possible only when the condition specified in the WHERE clause of the select_statement is satisfied. Therefore, this option is used to disallow the update of a virtual table that violates the condition.
CREATE TABLE a_tbl(
id INT NOT NULL,
phone VARCHAR(10));
INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);
--creating a new view based on AS select_statement from a_tbl
CREATE VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL WITH CHECK OPTION;
SELECT * FROM b_view;
id phone
===================================
1 '111-1111'
2 '222-2222'
3 '333-3333'
--WITH CHECK OPTION doesn’t allow to update column value which violates WHERE clause
UPDATE b_view SET phone=NULL;
In line 1, column 72,
ERROR: Check option exception on view b_view.
--creating view which name is as same as existing view name
CREATE OR REPLACE VIEW b_view AS SELECT * FROM a_tbl ORDER BY id DESC;
--the existing view has been replaced as a new view by OR REPLACE keyword
SELECT * FROM b_view;
id phone
===================================
5 NULL
4 NULL
3 '333-3333'
2 '222-2222'
1 '111-1111'