Creates a view definition. A view is a logical structure that
refers to rows stored in other tables. Data in a view is not
physically stored in the database. You can include in a view
definition combinations of rows and columns from other tables and
view definitions in the schema. You define a view by specifying a
select expression, that:
o Names the criteria for selecting the tables, rows, and columns
for the view
o Specifies a set of columns from those tables
When the CREATE VIEW statement executes, SQL adds the view
definition to the physical database. If you declared the schema
with the PATHNAME argument, the definition is also stored in the
repository.
1 – Environment
You can use the CREATE VIEW statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CREATE VIEW --> <view-name> -------------------------+
+--------------------------------------------------+
++-----------------------------------+-------------+
+-> STORED NAME IS <stored-name> ---+ |
+----------------------------------------------------+
++------------------------>------------------------+-+
+-> ( -+-> <column-name> -+--------------+-+-> ) -+ |
| +----------------+ | | |
| ++-+------------------------+-+-+ | |
| | +-> sql-and-dtr-clause --+ | | |
| +-------------<--------------+ | |
+----------------- , <--------------+ |
+----------------------------------------------------+
+-> AS select-expr ---+------------------------+-->
+-> check-option-clause -+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +-------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> -------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> -+
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS literal ----+
+-> DATATRIEVE -+
check-option-clause =
WITH CHECK OPTION --+----------------------------------+-->
+-> CONSTRAINT <check-option-name> +
3 – Arguments
3.1 – check-option-clause
A constraint that places restrictions on update operations made
to a view. The check option clause ensures that any rows that are
inserted or updated in a view conform to the definition of the
view. Do not specify the WITH CHECK OPTION clause with views that
are read-only.
3.2 – column-name
A list of names for the columns of the view. If you omit column
names, SQL assigns the names from the columns in the source
tables in the select expression.
However, you must specify names for all the columns of the view
in the following cases:
o The select expression generates columns with duplicate names.
o The select expression uses statistical functions or arithmetic
expressions to create new columns that are not in the source
tables.
3.3 – CONSTRAINT
Specify a name for the WITH CHECK OPTION constraint. If you omit
the name, SQL creates a name. However, Oracle Rdb recommends that
you always name constraints. If you supply a name for the WITH
CHECK OPTION constraint, the name must be unique in the schema.
The name for the WITH CHECK OPTION constraint is used by the
INTEG_FAIL error message when an INSERT or UPDATE statement
violates the constraint.
3.4 – select-expr
A select expression that defines which columns and rows of the
specified tables SQL includes in the view. The select expression
for a nonmultischema database can name only tables in the same
schema as the view. A select expression for a multischema
database can name a table in any schema in the database; the
schema need not be in the same catalog as the view being created.
See the Select_Expressions HELP topic for more information on
select expressions.
3.5 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clauses. See the
DATATRIEVE HELP topic for more information on formatting clauses.
3.6 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a view created
in a multischema database. The stored name allows you to access
multischema definitions using interfaces, such as Oracle RMU,
the Oracle Rdb management utility, that do not recognize multiple
schemas in one database. You cannot specify a stored name for
a view in a database that does not allow multiple schemas. For
more details about stored names, see the User_Supplied_Names HELP
topic.
3.7 – view-name
Name of the view definition you want to create. When choosing a
name, follow these rules:
o Use a name that is unique among all view and table names in
the schema.
o Use any valid SQL name (see the User_Supplied_Names HELP topic
for more information).
4 – Examples
Example 1: Defining a view based on a single table
This example shows a view definition that uses three columns from
a single table, EMPLOYEES.
SQL> CREATE VIEW EMP_NAME
cont> AS SELECT
cont> FIRST_NAME,
cont> MIDDLE_INITIAL,
cont> LAST_NAME
cont> FROM EMPLOYEES;
SQL> --
SQL> -- Now display the rows from the view just created.
SQL> SELECT * FROM EMP_NAME;
FIRST_NAME MIDDLE_INITIAL LAST_NAME
Alvin A Toliver
Terry D Smith
.
.
.
Example 2: Defining a view that does not allow you to insert or
update rows that do not conform to the view's definition
This example shows a view definition using the WITH CHECK OPTION
clause.
SQL> CREATE VIEW ADMN_VIEW
cont> AS SELECT * FROM JOB_HISTORY
cont> WHERE DEPARTMENT_CODE = 'ADMN'
cont> WITH CHECK OPTION CONSTRAINT ADMN_VIEW_CONST;
SQL> -- You cannot insert a row that does not
SQL> -- conform to the view definition.
SQL> --
SQL> INSERT INTO ADMN_VIEW (DEPARTMENT_CODE) VALUES ('MBMN');
%RDB-E-INTEG-FAIL, violation of constraint ADMN_VIEW_CONST-
caused operation to fail
Example 3: Defining a view based on multiple tables
You can also define a view using more than one table.
SQL> CREATE VIEW CURRENT_SALARY
cont> AS SELECT
cont> E.LAST_NAME,
cont> E.FIRST_NAME,
cont> E.EMPLOYEE_ID,
cont> SH.SALARY_START,
cont> SH.SALARY_AMOUNT
cont> FROM
cont> SALARY_HISTORY SH, EMPLOYEES E
cont> WHERE
cont> SH.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND
cont> SH.SALARY_END IS NULL ;
This example defines a view from the EMPLOYEES and SALARY_HISTORY
tables. It uses the select expression to:
o Choose the columns derived from each table. Because no column
names are specified before the select expression, the columns
inherit the names from the source tables.
o Join the tables and limit the view to current salaries.
Example 4: Defining a view with local column names
SQL> CREATE VIEW EMP_JOB
cont> ( CURRENT_ID,
cont> CURRENT_NAME,
cont> CURRENT_JOB,
cont> SUPERVISOR )
cont> AS SELECT
cont> E.EMPLOYEE_ID,
cont> E.LAST_NAME,
cont> J.JOB_TITLE,
cont> JH.SUPERVISOR_ID
cont> FROM
cont> EMPLOYEES E,
cont> JOB_HISTORY JH,
cont> JOBS J
cont> WHERE
cont> E.EMPLOYEE_ID = JH.EMPLOYEE_ID
cont> AND
cont> JH.JOB_CODE = J.JOB_CODE
cont> AND
cont> JH.JOB_END IS NULL ;
This view definition:
o Specifies local names for the columns in the view.
o Joins the EMPLOYEES and JOB_HISTORY tables. This join links
rows in the EMPLOYEES table to rows in the JOB_HISTORY table.
o Joins the JOB_HISTORY and JOBS tables. This join lets the view
contain job titles instead of job codes.
o Uses the JH.JOB_END IS NULL expression. This clause specifies
that only the current JOB_HISTORY rows, where the JOB_END
column is null, should be included in the view.
The following query uses the view defined in the previous
example:
EXEC SQL
DECLARE X CURSOR FOR
SELECT CURRENT_ID, CURRENT_NAME, CURRENT_JOB, SUPERVISOR
FROM EMP_JOB
END-EXEC
EXEC SQL
OPEN X
END-EXEC
PERFORM WHILE SQLCODE NOT = 0
EXEC SQL
FETCH X
INTO :ID, :NAME, :JOB, :SUPER
END-EXEC
END PERFORM
EXEC SQL
CLOSE X
END-EXEC
Example 5: Defining a view with a calculated column
This example shows a view definition that derives a column
through a calculation based on a column in an base table.
SQL> CREATE VIEW SS_DEDUCTION
cont> ( IDENT,
cont> SALARY,
cont> SS_AMOUNT )
cont> AS SELECT
cont> E.EMPLOYEE_ID,
cont> SH.SALARY_AMOUNT,
cont> SH.SALARY_AMOUNT * 0.065
cont> FROM
cont> SALARY_HISTORY SH, EMPLOYEES E
cont> WHERE
cont> SH.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND
cont> SH.SALARY_END IS NULL ;
Each time the view column SS_AMOUNT is selected, it computes a
new value from the SALARY_AMOUNT column of the SALARY_HISTORY
table.
Example 6: Defining a view dependent on another view
This example creates a view, DEPENDENT_VIEW, that refers to
the CURRENT_JOB view in its definition to include current job
information for employees in the engineering department.
SQL> CREATE VIEW DEPENDENT_VIEW
cont> AS SELECT * FROM CURRENT_JOB
cont> WHERE DEPARTMENT_CODE = 'ENG';