Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 6 of 10
subquery: See "SELECT and Subqueries".
To define a view, 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.
For information on various types of views and their uses, see Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Administrator's Guide.
For information on modifying a view, see "ALTER VIEW". For information on removing a view from the database, see "DROP VIEW".
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.
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. For information on these privileges, see "SELECT and Subqueries", "INSERT", "UPDATE", and "DELETE". 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 Release 7.3 to provide partitioning capabilities for applications requiring them. Partition views are supported in Oracle8i so that you can upgrade applications from Release 7.3 without any modification. In most cases, subsequent to migration to Oracle8i you will want to migrate partition views into partitions (see Oracle8i Administrator's Guide).
With Oracle8i, 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:
|
|
re-creates 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. |
|
|
|
|
|
Note: If any materialized views are dependent on view, those materialized views will be marked |
|
|
creates 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 |
|
|
creates 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. |
|
schema |
is the schema to contain the view. If you omit schema, Oracle creates the view in your own schema. |
|
view |
is the name of the view or the object view.
Restriction: If a view has |
|
alias |
specifies 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 schema objects in the section, "Referring to Schema Objects and Parts". 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. |
|
|
Restriction: You cannot specify an alias when creating an object view. |
|
|
explicitly creates 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 See Also: "CREATE TYPE" for more information about creating objects. |
|
|
specifies 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.
If you try to dereference or pin a primary key |
|
|
Note: The 8.0 syntax |
|
|
If the object view is defined on an object table or an object view, you can omit this clause or specify |
|
|
specifies that the intrinsic object identifier of the underlying object table or object view will be used to uniquely identify each row. |
|
attribute |
is an attribute of the object type from which the object identifier for the object view is to be created. |
|
|
identifies columns and rows of the table(s) that the view is based on. The subquery's select list 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 |
|
|
Restrictions:
The preceding restrictions apply to materialized views as well. |
|
|
|
|
|
|
|
|
See Also:
|
|
with_clause |
restricts the subquery in one of the following ways: |
|
|
|
specifies that no delete, inserts, or updates can be performed through the view. |
|
|
specifies that inserts and updates performed through the view must result in rows that the view query can select. The |
|
|
assigns the name of the |
The following statement creates a view of the EMP
table named DEPT20
. The view shows the employees in Department 20 and their annual salary:
CREATE VIEW dept20 AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 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 an updatable view named CLERKS
of all clerks in the EMP
table. Only the employees' IDs, names, and department numbers are visible in this view and only these columns can be updated in rows identified as clerks:
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH CHECK OPTION CONSTRAINT wco;
Because of the CHECK
OPTION
, you cannot subsequently insert a new row into CLERK
if the new employee is not a clerk.
A join view is one whose view query 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 ed AS SELECT e.empno, e.ename, d.deptno, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno View created. SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'ED'; COLUMN_NAME UPD --------------- --- ENAME YES DEPTNO NO EMPNO YES LOC NO INSERT INTO ed (ENAME, EMPNO) values ('BROWN', 1234);
In the above example, there is a unique index on the DEPTNO
column of the DEPT
table. You can insert, update or delete a row from the EMP
base table, because all the columns in the view mapping to the EMP
table are marked as updatable and because the primary key of EMP
is included in the view.
See Also:
Oracle8i Application Developer's Guide - Fundamentals for more information on updating join views. |
The following statement creates a read-only view named CLERKS
of all clerks in the EMP
table. Only the employees' IDs, names, department numbers, and jobs are visible in this view:
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH READ ONLY;
The following example creates object view EMP_OBJECT_VIEW
of EMPLOYEE_TYPE
:
CREATE TYPE employee_type AS OBJECT ( empno NUMBER(4), ename VARCHAR2(20), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2) ); CREATE OR REPLACE VIEW emp_object_view OF employee_type WITH OBJECT IDENTIFIER (empno) AS SELECT empno, ename, job, mgr, hiredate, sal, comm FROM emp;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|