개요

설명

뷰(가상 테이블)는 물리적으로 존재하지 않는 가상의 테이블이며, 기존의 테이블이나 뷰에 대한 질의문을 이용하여 뷰를 생성할 수 있다. VIEWVCLASS는 동의어로 사용된다.

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 ]

예제

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'