뷰(가상 테이블)는 물리적으로 존재하지 않는 가상의 테이블이며, 기존의 테이블이나 뷰에 대한 질의문을 이용하여 뷰를 생성할 수 있다. VIEW와 VCLASS는 동의어로 사용된다.
CREATE 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 : 유효한 SELECT 문이 명시되어야 한다. 이를 기반으로 뷰가 생성된다.
WITH CHECK OPTION : 이 옵션이 명시되면 select_statement 내 WHERE 절에 명시된 조건식을 만족하는 경우에만 업데이트 또는 삽입이 가능하다. 조건식을 위반하는 가상 테이블에 대한 갱신을 허용하지 않기 위해서 사용한다.
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'