Oracle database objects are divided into five kinds: tables,
Men's Air Max 360, views, sequences, indexes and synonyms.
view is based on one or more tables or views of the logical form itself does not contain data, through which the data inside the table can be queried and modified. View based on table as base table.
view is stored in the data dictionary of a select statement. Data can be extracted at creating a logical view on the accumulation or portfolio.
view of the advantages:
1. access to the database, because you can selectively view the selection as portion of the database.
2. consumer through a simple query to get results from a complex query.
3. to preserve the independence of the data, attempting to retrieve data from multiple tables.
4. as the same data can produce different view.
view is divided into a simple outlook and complicated view:
simply view merely access to data from a unattached table
; complex view from multiple tables
simple view does not contain functions and data sets
complex view namely contains
simple view of DML operation can not
complex view.
view creation:
; CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
[(alias [, alias ]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint
[WITH READ ONLY]
OR REPLACE: whether trying to create already exists,
Women's Air Max Ltd, ORACLE automatically rebuild the view;
FORCE : Regardless of the subsistence of the base table will automatically create the ORACLE view;
NOFORCE: only ORACLE base tables exist only to create the view:
alias : To view the definition of aliases generated columns;
subquery: a complete SELECT expression, the statement can be defined in an alias;
WITH CHECK OPTION:
insert or modify rows of data view definition must satisfy the constraints;
WITH READ ONLY:
; can not view whichever of the DML operation.
For example:
CREATE OR REPLACE VIEW dept_sum_vw
(name, minsal, maxsal, avgsal)
AS SELECT d.dname,
Air Max 2, min (e.sal), max (e.sal), avg (e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
view of the defining principles:
1 View of complex SELECT query grammar can be accustom,
Air Max 90 Boot, including the articulation / group queries and subqueries;
2. In the absence of WITH CHECK OPTION and READ ONLY in the circumstance, the query can not use
ORDER BY clause;
3. If there is no name for the CHECK OPTION constraint, the system will automatically whom named, in the form of SYS_Cn;
4.OR REPLACE adoption does no delete the aboriginal view will alteration its definition and reconstruct, alternatively re-granting object
permission.
view query:
view is successfully created, you can retrieve data from the view, and from this table to retrieve the same data.
can likewise interrogate and view all the information specified in rows and columns.
such as:
retrieve data:
; SQL> SELECT * FROM dept_sum_vw;
query view definition:
; SELECT view_name, text from user_views;
which exhibits the contents of text view defined by the SELECT statement can be won by DESC USER_VIEWS
information.
modify the view:
through OR REPLACE to re-create the same view.
DML operations aboard a view:
DML operation should emulate the principle:
; 1 simple view can perform DML operations;
2. in the view contains a GROUP functions, GROUP BY clause, DISTINCT opener words can not
deleted rows;
; 3 In view of these circumstances does not appear to modify the base table through view data or insert data:
a. view contains a GROUP functions,
Women's Air Max 2009, GROUP BY proviso, DISTINCT keyword;
b. use expressions to define col;
c.ROWNUM pseudo column.
d. base table not in view, choose the additional columns defined as non-empty and not default amount.
view can be used to maintain the integrity of the database,
Men's Air Max 2003, merely a finite role.
view through the implementation of referential integrity constraints in the database-level implementation constraints.
WITH CHECK OPTION clause is limited to:
through the implementation of INSERTS and UPDATES view operation can not build the view can not retake the data row ,
because it will insert or adjust rows of data integrity constraints and fulfil data validation checks.
For example:
CREATE OR REPLACE VIEW vw_emp20
AS SELECT * FROM emp
WHERE deptno = 20
; WITH CHECK OPTION constraint vw_emp20_ck;
view has been created.
results:
SELECT empno, ename, job FROM vw_emp20;
; EMPNO ENAME ; JOB
--------------------- -------------- -------------
7369 SMITH CLERK
7566 JONES MANAGER
7902 FORD ANALYST
modified:
UPDATE vw_emp20
; SET deptno = 20
; WHERE empno = 7902;
will produce an error:
UPDATE vw_emp20
*
ERROR in the 1st line:
ORA-01402: view WITH CHECK OPTION violation
view WHERE clause apt delete:
; DROP VIEW VIEW_NAME statement to drop views.
delete the bottom view definition does not affect the file in the chart.
only view the owner and permissions of the user with DROP VIEW to delete view