Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE TYPE to
DROP ROLLBACK SEGMENT, 5 of 26
Use the CREATE
VIEW
statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.
You can also create an object view or a relational view that supports LOB and object datatypes (object types, REFs, nested table, or varray types) on top of the existing view mechanism. An object view is a view of a user-defined type, where each row contains objects, each object with a unique object identifier.
See Also:
|
To create a view in your own schema, you must have CREATE
VIEW
system privilege. To create a view in another user's schema, you must have CREATE
ANY
VIEW
system privilege.
To create a subview, you must have UNDER
ANY
VIEW
system privilege or the UNDER
object privilege on the superview.
The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.
To use the basic constructor method of an object type when creating an object view, one of the following must be true:
EXECUTE
ANY
TYPE
system privileges.
EXECUTE
object privilege on that object type.
Partition views were introduced in Oracle Release 7.3 to provide partitioning capabilities for applications requiring them. Partition views are supported in Oracle9i so that you can upgrade applications from Release 7.3 without any modification. In most cases, subsequent to migration to Oracle9i you will want to migrate partition views into partitions.
In Oracle9i, you can use the CREATE
TABLE
statement to create partitioned tables easily. Partitioned tables offer the same advantages as partition views, while also addressing their shortcomings. Oracle recommends that you use partitioned tables rather than partition views in most operational environments.
See Also:
|
create_view::=
create_view
table_or_view_constraint and column_constraint: See constraint_clause.
object_view_clause::=
object_view_clause
subquery_restriction_clause::=
subquery_restriction_clause
Specify OR
REPLACE
to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.
INSTEAD
OF
triggers defined in the view are dropped when a view is re-created.
If any materialized views are dependent on view, those materialized views will be marked UNUSABLE
and will require a full refresh to restore them to a usable state. Invalid materialized views cannot be used by query rewrite and cannot be refreshed until they are recompiled.
See Also:
|
Specify FORCE
if you want to create the view regardless of whether the view's base tables or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT
, INSERT
, UPDATE
, or DELETE
statements can be issued against the view.
If the view definition contains any constraints, CREATE
VIEW
... FORCE
will fail if the base table does not exist or the referenced object type does not exist. CREATE
VIEW
... FORCE
will also fail if the view definition references a constraint that does not exist.
Specify NOFORCE
if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.
Specify the schema to contain the view. If you omit schema, Oracle creates the view in your own schema.
Specify the name of the view or the object view.
Restriction: If a view has INSTEAD
OF
triggers, any views created on it must have INSTEAD
OF
triggers, even if the views are inherently updatable.
Specify names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming Oracle schema objects. Aliases must be unique within the view.
If you omit the aliases, Oracle derives them from the columns or column aliases in the view's query. For this reason, you must use aliases if the view's query contains expressions rather than only column names. Also, you must specify aliases if the view definition includes constraints.
Restriction: You cannot specify an alias when creating an object view.
You can specify constraints on views and object views. You define the constraint at the view level using the table_or_view_constraint
clause. You define the constraint at the column or attribute level using the column_constraint
clause after the appropriate alias.
View constraints (at the view and at the column or attribute level) are declarative only. That is, Oracle does not enforce them. However, operations on views are subject to the integrity constraints defined on the underlying base tables, so you can enforce constraints on views through constraints on base tables.
View constraints are a subset of table constraints and are subject to a number of restrictions.
The object_view_clause lets you define a view on an object type.
Use this clause to explicitly create an object view of type type_name. The columns of an object view correspond to the top-level attributes of type type_name. Each row will contain an object instance and each instance will be associated with an object identifier (OID) as specified in the WITH
OBJECT
IDENTIFIER
clause. If you omit schema, Oracle creates the object view in your own schema.
Use the WITH
OBJECT
IDENTIFIER
clause to specify a top-level (root) object view. This clause lets you specify the attributes of the object type that will be used as a key to identify each row in the object view. In most cases these attributes correspond to the primary key columns of the base table. You must ensure that the attribute list is unique and identifies exactly one row in the view.
Restrictions:
If the object view is defined on an object table or an object view, you can omit this clause or specify DEFAULT
.
Specify DEFAULT
if you want Oracle to use the intrinsic object identifier of the underlying object table or object view to uniquely identify each row.
For attribute, specify an attribute of the object type from which Oracle should create the object identifier for the object view.
Use the UNDER
clause to specify a subview based on an object superview.
To learn whether a view is a superview or a subview, query the SUPERVIEW_NAME
column of the USER_
, ALL_
, or DBA_VIEWS
data dictionary views.
Restrictions:
See Also:
Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The select list of the subquery can contain up to 1000 expressions.
If you create views that refer to remote tables and views, the database links you specify must have been created using the CONNECT
TO
clause of the CREATE
DATABASE
LINK
statement, and you must qualify them with schema name in the view subquery.
Restrictions on the view subquery:
CURRVAL
or NEXTVAL
pseudocolumns.
ROWID
, ROWNUM
, or LEVEL
pseudocolumns, those columns must have aliases in the view subquery.
CREATE
OR
REPLACE
VIEW
statement.
SAMPLE
clause.
The preceding restrictions apply to materialized views as well.
Notes on Creating Updatable Views:
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD
OF
trigger on any view to make it updatable.
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS
data dictionary view. (The information displayed by this view is meaningful only for inherently updatable views.)
UPDATE
statement that refers to any of these pseudocolumns or expressions.
INSERT
statement, the view must not be created WITH
CHECK
OPTION
, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table in one for which every primary key or unique key value in the base table is also unique in the join view.
UPDATE
statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH
CHECK
OPTION
, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE
.
DELETE
statement, the join can have one and only one key-preserved table. That table can appear more than once in the join, unless the view was created WITH
CHECK
OPTION
.
See Also:
INSTEAD
OF
trigger on a view that is not inherently updatable
Use the subquery_restriction_clause to restrict the subquery in one of the following ways:
Specify WITH
READ
ONLY
if you want no deletes, inserts, or updates to be performed through the view.
Specify WITH
CHECK
OPTION
to guarantee that inserts and updates performed through the view will result in rows that the view subquery can select. The CHECK
OPTION
cannot make this guarantee if:
INSERT
, UPDATE
, or DELETE
operations are performed using INSTEAD
OF
triggers.
Specify the name of the CHECK
OPTION
constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of the form SYS_C
n, where n is an integer that makes the constraint name unique within the database.
The following statement creates a view of the sample table employees named emp_view
. The view shows the employees in department 20 and their annual salary:
CREATE VIEW emp_view AS SELECT last_name, salary*12 annual_salary FROM employees WHERE department_id = 20;
The view declaration need not define a name for the column based on the expression sal*12
, because the subquery uses a column alias (annual_salary
) for this expression.
The following statement creates a restricted view of the sample table hr.employees
and defines a unique constraint on the email
view column and a primary key constraint for the view on the emp_id
view column:
CREATE VIEW emp_sal (emp_id, last_name, email UNIQUE RELY DISABLE NOVALIDATE, CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE) AS SELECT employee_id, last_name, email FROM employees;
The following statement creates an updatable view named clerk
of all sales and purchasing clerks in the employees
table. Only the employees' IDs, last names, department numbers, and jobs are visible in this view, and these columns can be updated only in rows where the employee is a king of clerk:
CREATE VIEW clerk AS SELECT employee_id, last_name, department_id, job_id FROM employees WHERE job_id = 'PU_CLERK' or job_id = 'SH_CLERK' or job_id = 'ST_CLERK';
This view lets you change the job_id
of a purchasing clerk to purchasing manager (PU_MAN
):
UPDATE clerk SET job_id = 'PU_MAN' WHERE employee_id = 118;
The next example creates the same view WITH
CHECK
OPTION
. You cannot subsequently insert a new row into clerk
if the new employee is not a clerk. You can update an employee's job_id
from one type of clerk to another type of clerk, but the update above would fail, because the view cannot access employees with non-clerk job_id.
CREATE VIEW clerk AS SELECT employee_id, last_name, department_id, job_id FROM employees WHERE job_id = 'PU_CLERK' or job_id = 'SH_CLERK' or job_id = 'ST_CLERK' WITH CHECK OPTION;
A join view is one whose view subquery contains a join. If at least one column in the join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS
to see whether the columns in a join view are updatable. For example:
CREATE VIEW locations_view AS SELECT d.department_id, d.department_name, l.location_id, l.city FROM departments d, locations l WHERE d.location_id = l.location_id; SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'LOCATIONS_VIEW'; COLUMN_NAME UPD ------------------------------ --- DEPARTMENT_ID YES DEPARTMENT_NAME YES LOCATION_ID NO CITY NO
In the above example, the primary key index on the location_id
column of the locations
table is not unique in the locations_view
view. Therefore, locations
is not a key-preserved table and columns from that base table are not updatable.
INSERT INTO locations_view VALUES (999, 'Entertainment', 87, 'Roma'); INSERT INTO locations_view VALUES * ERROR at line 1: ORA-01776: cannot modify more than one base table through a join view
You can insert, update, or delete a row from the departments
base table, because all the columns in the view mapping to the departments
table are marked as updatable and because the primary key of departments
is retained in the view.
INSERT INTO locations_view (department_id, department_name) VALUES (999, 'Entertainment'); 1 row created.
See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information on updating join views |
The following statement creates a read-only view named customer
of the oe.customers
table. Only the customers' last names, language, and credit limit are visible in this view:
CREATE VIEW customer (name, language, credit) AS SELECT cust_last_name, nls_language, credit_limit FROM customers WITH READ ONLY;
The following example shows the creation of the type inventory_typ
in the oc
schema, and the oc_inventories
view that is based on that type:
CREATE TYPE inventory_typ AS OBJECT ( product_id number(6) , warehouse warehouse_typ , quantity_on_hand number(8) ) ; CREATE OR REPLACE VIEW oc_inventories OF inventory_typ WITH OBJECT IDENTIFIER (product_id) AS SELECT i.product_id, warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id), i.quantity_on_hand FROM inventories i, warehouses w WHERE i.warehouse_id=w.warehouse_id;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|