View (study notes)

ViewA view is a table that is exported from one or several entity tables (or views). Unlike the entity table, the view itself is a virtual table that does not contain any real data. Only the definition of the view is stored in the database, not the data that corresponds to the view, which is still stored in the original entity table. So the data in the entity table changes, and the data that is queried from the view changes. In this sense, a view is like a window through which you can see the data that you are interested in in the database and its changes. Advantages of the view:

• The view simplifies the operation of the user;
• Views enable users to view the same data in multiple ways;
• The view provides a certain degree of logical independence to reconstruct the database;
• The view can provide security protection for confidential data;
• The appropriate use of the view can be more clearly expressed query;

CREATE VIEW Syntax

CREATE [Force | Noforce] [OR REPLACE] View name [(alias 1, alias 2,...)]

As

Sub-query;

Syntax Parameters: The main parameters in the Create view are explained as follows:Force : indicates that the table to create the view does not exist or can create a view; noforce:(default) indicates that the table to create the view must exist, otherwise it cannot be created; OR Replace: represents the replacement of the view, creates a new if the view is created without it, and replaces it if the view already exists.  

Permissions to create views for authorization

GRANT CREATE VIEW  to tests;

Simple view:

example One, create a view with a base salary greater than 2000

CREATE VIEW V_empview  as SELECT *  from WHERE sal>;

Whether the query view was created successfully

SELECT *  from WHERE Tabtype='VIEW';

Query V_empview View

SELECT *  from V_empview;

Query the specific information of the view

SELECT view_name,text_length,text from User_views;

example Two, create a view that contains only 20 door employee information

CREATE OR REPLACE VIEW V_EMP20  as SELECT *  from WHERE deptno=;

-- whether the query was created successfully SELECT *  from WHERE Tabtype='VIEW'; -- Query myemponly_dept20 View SELECT *  from v_myemponly_dept20;

to perform DML operations under a view

Example Three,

Create a view with 20-door employee information and add a piece of information

CREATE OR REPLACE VIEW V_EMP20  as SELECT  from WHERE deptno=;

-- Query V_myview View SELECT *  from V_emp20;

--add a piece of data to the V_EMP20 viewINSERT  intoV_EMP20 (Empno,ename,job,sal,deptno)VALUES(8888,'JAVA','Clerk', +, -);COMMIT;--Query V_myview ViewSELECT *  fromV_emp20;--Results Increase Success

example four : Modifying a view

 update  v_emp20 set  Ename=   " oracle  , Job

example Five, deleting data from the V_EMP20 view

DELETE  from WHERE empno=8888; COMMIT ; -- Query V_myview View SELECT *  from V_emp20;

Complex views

A mapped view that contains multiple tables

example Six, showing details for each department

CREATE OR REPLACE VIEWV_myview asSELECTD.deptno,d.dname,d.loc,COUNT(E.empno)COUNT, NVL (ROUND(AVG(SAL),2),0)AVG, NVL (SUM(SAL),0)SUM, NVL (MAX(SAL),0)MAX, NVL (MIN(SAL),0)MIN fromEMP e,dept DWHEREE.deptno (+)=D.deptnoGROUP  byD.deptno,d.dname,d.loc--Query V_myview ViewSELECT *  fromV_myview;

example seven aliases for the columns of a query in a view

CREATE OR REPLACE VIEWV_myview (Department number, department name, department position, number of people, average salary, total wage, maximum wage, minimum wage) asSELECTD.deptno,d.dname,d.loc,COUNT(E.empno)COUNT, NVL (ROUND(AVG(SAL),2),0)AVG, NVL (SUM(SAL),0)SUM, NVL (MAX(SAL),0)MAX, NVL (MIN(SAL),0)MIN fromEMP e,dept DWHEREE.deptno (+)=D.deptnoGROUP  byD.deptno,d.dname,d.loc--Query V_myview ViewSELECT *  fromV_myview;

For simple views, you can perform insert,update and delete operations directly
However, for complex views, direct execution of insert,update and delete operations is not allowed. The direct execution of DML operations is not allowed when the view meets any of the following conditions. The details are as follows:
1) with Set operator (Union,union All,intersect,minus);
2) having a grouping function (min,max,sum,avg,count, etc.);
3) Having group By,connect by or start with clauses;
4) with distinct keywords;
5) with connection query;
In order to perform DML operations on a complex view with the above conditions, the requisition trigger is required to complete

The WITH CHECK OPTION clause----Ensure that the creation condition of the view is not changed

Sometimes you need to use some where clauses to limit the conditions when creating a view,
However, by default, when the view is created, it is possible to modify the contents of the field used in the WHERE clause through the view.
At this point, the WITH CHECK option clause is required to ensure that the creation condition of the view is not updated.
Grammar:

 with CHECK OPTION clause CREATE [Force| Noforce][OR REPLACE]VIEW[(alias 1, alias 2,...) ] as        [withCHECK OPTION [CONSTRAINT constraint name ]        ];

example Eight, create a view that contains only 20 people, add a constraint, and the department number is not subject to change

CREATE OR REPLACE VIEWV_EMP20 asSELECT *  fromEmpWHEREDeptno= - with CHECK OPTION CONSTRAINTV_emp20_ck;---constraint After the optional do not write can also--Query V_myview ViewSELECT *  fromV_emp20;--If you update the data now and change the department number to something else, you will get an error and indicate that you have violated the Where conditionUPDATEV_emp20SETDeptno= + WHEREEmpno=7369;

With READ only clause--make all fields in the view non-updatable

Grammar:

 with READOnly clauseCREATE [Force | Noforce] [OR REPLACE] VIEWView Name[(alias 1, alias 2,...)]         asSub-query[With CHECK OPTION [CONSTRAINT constraint name] ]    [With READ only];

example Eight, create a view that contains only 20 people, add constraints to read-only

CREATE OR REPLACE VIEWV_EMP20 asSELECT *  fromEmpWHEREDeptno= - with READ  only;--set to read-only property--Query V_myview ViewSELECT *  fromV_emp20;--If you update the data now, you will be presented with an error and prompt for the Where conditionUPDATEV_emp20SETEname='TTTT', comm=NULL WHEREEmpno=7369;

Delete a view

DROP View Name

example Nine, deleting a v_myview view

DROP VIEW V_myview;

-- querying the User_views data dictionary SELECT view_name,text_length,text from User_views;

View (study notes)