<< CREATE VIEW statement | Temporary tables are useful when >>

DECLARE GLOBAL TEMPORARY TABLE statement

<< CREATE VIEW statement | Temporary tables are useful when >>

Derby Reference Manual

user2

attempts to create a view called

user2.v2

that references table

user1.t1

and function

user1.f_abs()

, then

user2

must have the SELECT privilege on table

user1.t1

and the EXECUTE privilege on function

user1.f_abs()

The privilege to grant the SELECT privilege cannot be revoked. If a required privilege
on one of the underlying objects that the view references is revoked, then the view is
dropped.

Syntax

CREATE VIEW

view-Name

[ (

Simple-column-Name

] * ) ]

Query

[

ORDER BY clause

]

[

result offset clause

]

[

fetch first clause

]

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.

Examples

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.

DECLARE GLOBAL TEMPORARY TABLE statement

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for
the current connection.