Detailed View Usage

View

The OCP question uses the conclusion:

When you create a view, you must alias a column that uses a variety of functions, or an expression of an operation, and fails if the alias view is not created.

The column names that define the view can be omitted from the column names in the select definition, and if the column names that define the view are not omitted, the number of columns is consistent with the number of columns in the select definition.

One, what is the view:
In Oracle, the view is called a logical table. The logical word still shows that the view is not a real table. Because there is no data in the view. All the possessions of the view, the SELECT statement when you create the view.


Second, create the view:

1. The main method of creating a view is:

CREATE [OR REPLACE] [force| Noforce] View View
[(alias[, alias] ...)]
As subquery
[With CHECK OPTION [CONSTRAINT CONSTRAINT]]
[With READ only [CONSTRAINT CONSTRAINT]];

2, for example, the following statement creates a view:

hr@ocm> CREATE VIEW empvu30
2 as SELECT employee_id, last_name, salary
3 From Employees
4 WHERE department_id = 30;

View created.

The name of the view is empvu30. Well, what's the use of it? Quite simply, if you later want to view the employee information for the DEPARTMENT_ID (department number) for 30, you do not have to use "select employee_id, last_name, salary from employees WHERE departmen t_id = 30 ", direct select View can:

Hr@ocm> select * from Empvu30;


employee_id last_name                     SALARY
--- -------------------------------------------
        145 Russell         &NB Sp              14000
        146       & nbsp               13500
        147 Errazuriz                      12000
        148 Cambrault                      11000
        149 Zlotkey   & nbsp                    10500

Tucker 10000

This is simpler than the DEPARTMENT_ID = 30 you enter each time. We can create a view of the more complex query operations that are commonly used like the above. This allows you to see the results each time you query the view, which is more convenient than entering complex query statements.

3. To create the meaning of [OR Replace] in the view command, replace has the meaning of substitution. Its primary role is to modify the definition of the view, which is to modify the SQL statements within the view. For example, the above empvu30, I want to add a column first_name for empvu30, the command is as follows:

hr@ocm> CREATE OR REPLACE VIEW empvu30
2 as SELECT employee_id, First_name,last_name, salary
3 From Employees
4 WHERE department_id = 30;


View created.

4. When you create a view, you must alias a column that uses a variety of functions, or an expression of an operation, such as if no alias view creation fails, for example:

hr@ocm> CREATE OR REPLACE VIEW empvu30
2 as SELECT department_id,count (*)
3 From Employees
4 GROUP by department_id;
As SELECT department_id,count (*)
*
ERROR at line 2:
Ora-00998:must name This expression with a column alias

According to Oracle's error, give COUNT (*) an individual name of count, for example:

hr@ocm> CREATE OR REPLACE VIEW empvu30
2 as SELECT department_id,count (*) Count
3 From Employees
4 GROUP by department_id;


View created.

View the structure of the view EMPVU30 with DESC

Hr@ocm> desc EMPVU30
Name Null? Type
-----------------------------

DEPARTMENT_ID Number (4)

COUNT number

If you do not specify an alias, Oracle will not be able to determine what its column name is in the view. Therefore, when you create a view, you must alias it for this complex expression.

5, when using select query a view, and the query table is one of the same, we can use the view as a table to query. In the SELECT statement, all applicable to the table attribute are the same applicability view. For example we can grow conditions:

Hr@ocm> SELECT * from empvu30 where department_id=30;


department_id COUNT
------------- ----------
30 6


Displays the line with the department number (department_id) equal to 30 in the empvu30 view. When using this command, we can use empvu30 as a table, which has 1 rows, two columns, and the column names are department_id and count respectively. The source of the view data, called the "base table" of the view. The base table for a view can have multiple. If I create a view based on the connection command, the data in this view may come from more than one table, so that it has multiple base tables.

When the data in the base table changes, the view changes as well. For example, in the Employees table I add a new employee to the department number (DEPARTMENT_ID) that is equal to 30:

Hr@ocm> INSERT INTO employees values (888, ' Joe ', ' Guo ', ' Joe@oracle.com ', 1388888888,sysdate, ' ad_pres ', 50000,null, 200,30);


1 row created.


Hr@ocm> commit;


Commit complete.

After adding a line, I'll show you the view again:

Hr@ocm> SELECT * from empvu30 where department_id=30;


department_id COUNT
------------- ----------

30 7

The department number 30th is now 7 people. That is, what happens to the base table does not require any action, and the view can be immediately reflected.
This is the view, we do not go into the in-depth discussion of it, only from the look, can not see what it is different from the table. In select, you can't see whether you select a table or a view. In fact, most of the time we are in the select View, but we all think in the Select table.

We can also use a view as the base table, and then create a view, such as the above example, I want to display the results of the department number and number and Departmets table equivalent to display the department name:

Hr@ocm> Select A.department_id,department_name,count from empvu30 a,departments b where a.department_id=b. department_id and a.department_id=30;


department_id Department_name COUNT
------------- ------------------------------ ----------

7 Purchasing

The command to connect is still a little bit longer, and I'll create a view based on this command:

hr@ocm> CREATE OR REPLACE VIEW v_emp30 as
2 Select A.department_id,department_name,count
3 from empvu30 a,departments b
4 where a.department_id=b.department_id and a.department_id=30;

View created.

The name of the view is V_EMP30, the name is changed according to need, if I feel this complex, can be replaced by simple, mainly I think when I see this name, I can know that this view involves two tables employees and Departmetns.

OK, so here's a look at this view, and the output will be the same as the one above that joins the SELECT statement:

Hr@ocm> SELECT * from V_EMP30;


department_id Department_name COUNT
------------- ------------------------------ ----------
7 Purchasing


In this way, I would like to see the total number of unit 30th. Just look at this view directly.
The base table for this view has departmetns tables and empvu30 views, and empvu30 base tables are employees tables. That is, a view can serve as a base table for other views. Again, in the select operation, we can use the view as a table, we simply temporarily called the view as a visual chart. We can assume that the data from the chart is based on the base table. Well, since the view is a table, I can create other views based on this table, of course, there is no problem.


Third, the characteristics of the view:
1. Simplifies query operation, a view may represent a string of very complex query operations.
We can define the data that is often used in a combination of multiple tables, or a specific result set as a view, so that it acts as a modular data. We can use this data directly to query the view, rather than write a long SQL statement, so it is also easy to maintain the role.
2, limited data access.
For example: For different users, we only provide part of the data to him. In this way, we can qualify the result set in the view and then return that view to him. In this way, no matter how the user defines the query criteria for the view, he cannot query the data that we do not want to provide to him.
3, the same data different display.
Presentation of report data
4, to provide specific data.
The view allows the user or program developer to see only the data they need without exposing all the information and fields in the table, which enhances the security of the data.


Iv. Classification of views
Oracle divides views into two categories, simple views and complex views, as follows:

Characteristics	Simple view	Complex view
Number of tables	One	Multiple
Whether to include functions	Whether	Is
Whether to group	Whether	Is

The empvu30 in our example above is a simple view, and V_EMP30 is definitely a complex view.
Typically, we can use DML commands on simple views, but complex views cannot use DML commands, only queries. Since 9i, though, the view created for a multiple-table connection is also a complex view, but it can also be DML-operated.
Let's take a look at the situation where the view is DML.


V. Views and DML

The view is just an SQL statement, and Oracle merges the query statement with the SQL statements in the view when querying the view. In the case of a DML operation, Oracle blends the SQL statement of the view with the DML command, for example, we update the view empvu30:

hr@ocm> CREATE VIEW empvu30
2 as SELECT employee_id, last_name, salary
3 From Employees
4 WHERE department_id = 30;


View created.


hr@ocm> update empvu30 set salary=salary+100;


7 rows updated.


Hr@ocm> commit;


Commit complete.


This statement is executed in much the same way as the query above, where Oracle first empvu30 the view to its SQL:
Update (SELECT employee_id, last_name, salary from employees WHERE department_id =) Set salary=salary+100;
For such DML statements, should not be unfamiliar, in the seventh lecture "manipulating data" we have said. That is, when the view is updated, Oracle converts it to its base table update. Because there is no data in the view, the data is in the base table, the view is updated, and the base table is eventually updated.
When DML is made on the view, Oracle converts your DML to DML on the base table. If the conversion operation Oracle cannot complete, the view's DML will fail. For example:

I'm experimenting with a view that contains group by, groups functions. The Create SQL statement for the view is as follows:

hr@ocm> CREATE OR REPLACE VIEW V_emp_depart
2 as SELECT department_id,count (*) Count
3 From Employees
4 GROUP by department_id;


View created.


hr@ocm> Update V_emp_depart set count=8 where department_id=60;
Update V_emp_depart set count=8 where department_id=60
*
ERROR at line 1:

Ora-01732:data manipulation operation not legal on this view

In the case of a view that has a multiple row in the base table, changing a row in the view, Oracle cannot determine the rows in the base table for that row, and no DML operations can be made by such a view. Multiple rows are merged, and only groups, group functions, and distinct rows are selected. Distinct is to cancel a duplicate row, in fact, to combine multiple duplicate rows into one, a row in the view, and possibly several rows in the base table. In addition to this, other types of views can have at least some kind of DML operation. For example, you cannot insert rows into a view when a non-empty column is not in view. But can be updated or deleted.
This is why. Think about it. Actually very simple, if the table has a, B, c three columns, where C column has non-null constraints. View a contains both columns A and B of the table. Inserting rows into a view actually inserts rows into the table, but only assigns values to columns A and B of the table, because column C is not included in the view, so inserting rows into the view does not assign values to table C. and column C is non-empty. This can cause errors that violate the non-null constraint.
However, it is possible to update and delete this view.
Through this, we'll wake up and make a further refinement of what I said above: "Consider whether Oracle can convert this DML to a base table DML when we view it as DML." If not, the DML operation on the view will fail. To add, "also consider whether the DML of the view will violate the base table's constraints when it is converted to the base table's DML, and the DML of the view will also fail if there is a violation."
What kind of a view can be a DML, is very flexible, we have the general rules on the line, the general rule is what we have just said: "In our views as DML, consider whether Oracle can convert this DML to the base table of DML." If not, the DML operation on the view will fail. Also consider whether the DML of the view will violate the base table's constraints if it is converted to DML on the base table, and the DML of the view will also fail if there is a violation.
Finally, there are two of hard rules in Oracle, and if these two points are included in the view, they can be limited when they are DML. The so-called hard rules, that is, Oracle's provisions, do not need a reason. These two-point mandatory provisions are:

1, when the view contains RowNum, the view can not be any DML.

Gyj@ocm> Create or Replace view V_t3_rn as select RowNum rn,id,name,salary from T3;


View created.


Gyj@ocm> select * from V_t3_rn;


RN ID NAME SALARY
---------- ---------- ---------- ----------
1 1 gyj1 5000
2 1 Gyj11 5000
3 2 GYJ2 6000
4 2 GYJ22 6000
5 3 Gyj3 7000

(1) Update operation, error:

gyj@ocm> Update v_t3_rn set rn=10 where rn=1;
Update V_t3_rn set rn=10 where rn=1
*
ERROR at line 1:
Ora-01732:data manipulation operation not legal on this view


(2) Delete operation, error:

Gyj@ocm> Delete from V_t3_rn where rn=1;
Delete from V_t3_rn where rn=1
*
ERROR at line 1:
Ora-01732:data manipulation operation not legal on this view


(3) Insert operation, error

gyj@ocm> INSERT into V_T3_RN values (10,10, ' gyj10 ', 9000);
INSERT into V_T3_RN values (10,10, ' gyj10 ', 9000)
*
ERROR at line 1:
Ora-01732:data manipulation operation not legal on this view

This is rownum, it's like a column, you can show it, but it's not really a column. Its value is when you output the table data, temporarily added. The first row that is output from the table, its rownum value is 1, and then each output row from the table, the RowNum value plus 1.
2, when the view contains an expression of an operation, the column for an expression of an operation cannot be updated. You cannot insert a new row, but you can delete a row, for example:

Create a view as follows:

Gyj@ocm> select * from T2;


ID Age NAME
---------- ---------- --------------------
1 Joe


Create a View v_t2
Gyj@ocm> CREATE View v_t2 as Select id,age+10 age from T2;


View created.


You can delete rows as follows:


Gyj@ocm> Delete from V_t2;


1 row deleted.


After deletion, the data in the base table is gone:
Gyj@ocm> select * from T2;


No rows selected

gyj@ocm> rollback;


Rollback complete.


Cannot insert new row:


gyj@ocm> INSERT into V_T2 values (2,28);
INSERT into V_T2 values (2,28)
*
ERROR at line 1:

Ora-01733:virtual Column not allowed

Cannot update a table with an operational expression, also known as a virtual column

However, you can update other columns that do not contain an expression of an operation:

gyj@ocm> Update v_t2 set id=2 where id=1;


1 row updated.


gyj@ocm> rollback;


Rollback complete.


3. read-only view

Some views do not want users to change the rows in the base table through it, they can be built into read-only view: The commands are as follows:

Gyj@ocm> Create or Replace view v_t2 as select Id,age,name from T2 with Read only;


View created.

At the end of the creation of the view command, with the read only, the view is created as a read-only view. In this way, no DML operation on the view can succeed:

(1) Update operation, error:

gyj@ocm> Update v_t2 set age=age+1 where id=1;
Update v_t2 set age=age+1 where id=1
*
ERROR at line 1:
Ora-42399:cannot perform a DML operation on a read-only view

(2) Insert operation, error

gyj@ocm> INSERT into V_T2 values (2,28, ' Tom ');
INSERT into V_T2 values (2,28, ' Tom ')
*
ERROR at line 1:
Ora-42399:cannot perform a DML operation on a read-only view

(3) Delete exercise, error

Gyj@ocm> Delete from V_t2;
Delete from V_t2
*
ERROR at line 1:
Ora-42399:cannot perform a DML operation on a read-only view




4, with CHECK OPTION
Remember this option, in the seventh chapter has been described. If there is a condition in the view, this option ensures that you can only DML on the view, within the conditions of the views.
For example, I created the following view:
Gyj@ocm> Create or Replace view V_T3 as select ID, name,salary from T3 where salary>=7000 with CHECK option;


View created.

The condition in the view is salary>=7000, so I insert a row to salary less than 7000, and I will report an error:

gyj@ocm> INSERT into V_T3 values (5, ' gyj5 ', 1000);
INSERT into V_T3 values (5, ' gyj5 ', 1000)
*
ERROR at line 1:
Ora-01402:view with CHECK OPTION where-clause violation

Insert a row greater than 7000 to insert successfully:

gyj@ocm> INSERT into V_T3 values (5, ' gyj5 ', 7500);

1 row created.

Other updates, delete I will not try again, as long as the results of DML meet salary greater than the 7000,DML can be normal.


VI. Delete View
No view can be deleted, the command is very simple:


gyj@ocm> drop View v_t3;


View dropped.


Gyj@ocm> select * from V_t3;
SELECT * FROM V_t3
*
ERROR at line 1:
Ora-00942:table or view does not exist


Gyj@ocm> select * from T3;


ID NAME SALARY
---------- ---------- ----------
1 Gyj1 5000
1 GYJ11 5000
2 GYJ2 6000
2 GYJ22 6000
3 Gyj3 7000
3 Gyj33 7000
4 Gyj4 8000
4 GYJ44 8000
5 Gyj5 7500


9 rows selected.


The view was deleted, but the corresponding base table for the view is still in.

The drop command in Oracle is specifically used to delete various objects. The drop table is the deletion of tables. Drop view is to delete the view, and we will use other delete object commands later.

Vii. Disadvantages of the view
Although views can bring us convenience, it does not mean that we can abuse it. Because the view is actually an SQL statement, its results are dynamically generated each time it is invoked. If the definition of view is unreasonable, it will inevitably bring about the loss of performance.
Here are a few things we should be aware of when creating a view:
1, Operation view will be slower than direct operation of the underlying table, so we try to avoid creating views on large tables.
2, try not to create a nested view, is to use the view in the view. This allows you to repeatedly access the underlying table repeatedly, resulting in performance loss when querying.
3, as far as possible in the view only to return the required information, try not to use in the view of the table do not need to access.
4, in large tables or complex defined views, you can use stored procedures instead.
5, frequently used views, you can use the indexed view instead.