CREATE VIEW statement

Views are virtual tables formed by a query. A view is a dictionary object that you can use until you drop it.

Views are not updatable.

If a qualified view name is specified, the schema name cannot begin with SYS.

Syntax

CREATE VIEW view-Name
    [ ( Simple-column-Name [, Simple-column-Name] * ) ]
AS Query

A view definition can contain an optional view column list to explicitly name the columns in the view. If there is no column list, the view inherits the column names from the underlying query. All columns in a view must be uniquely named.

CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF)
	AS SELECT COMM + BONUS, COMM - BONUS
	FROM SAMP.EMPLOYEE;

CREATE VIEW SAMP.VEMP_RES (RESUME)
	AS VALUES 'Delores M. Quintana', 'Heather A. Nicholls', 'Bruce Adamson';

CREATE VIEW SAMP.PROJ_COMBO 
	(PROJNO, PRENDATE, PRSTAFF, MAJPROJ) 
	AS SELECT PROJNO, PRENDATE, PRSTAFF, MAJPROJ
	FROM SAMP.PROJECT UNION ALL 
SELECT PROJNO, EMSTDATE, EMPTIME, EMPNO 
	FROM SAMP.EMP_ACT 
	WHERE EMPNO IS NOT NULL;

Statement dependency system

View definitions are dependent on the tables and views referenced within the view definition. DML (data manipulation language) statements that contain view references depend on those views, as well as the objects in the view definitions that the views are dependent on. Statements that reference the view depend on indexes the view uses; which index a view uses can change from statement to statement based on how the query is optimized. For example, given:

CREATE TABLE T1 (C1 DOUBLE PRECISION);

CREATE FUNCTION SIN (DATA DOUBLE) 
	RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.sin'
	LANGUAGE JAVA PARAMETER STYLE JAVA;

CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T1;

the following SELECT:

SELECT * FROM V1

is dependent on view V1, table T1, and external scalar function SIN.