oracle-31-View DML Operations

I. DML operations on a view

1. Create a View V_person

Create or replace noforceview V_person

As Select * from the person where ID between 1003 and 1007;

2. Insert operation into view V_person

Insert into V_person (id,name) values(1010, ' J ')

"Note" The View is DML when operating, only the simple view can be executed DML operation; Complex views are not supported DML operation. Because V_person does not use with check option, the rows retrieved by the subquery can be added and censored.

Example 1 : Practice inserting data on a view (without with CHECK option) .

Solution: The current user under the view V_stu data

The base table for view V_stu is the student table, the table's current data:

Insert new data into view:

The query view found one more line:

Query base table found one more row:

Therefore, the view is DML The operation can achieve the effect of modifying the base table data.

Example 2 : Practice inserting data into a view (with CHECK option) .

Solution: Create a view and overwrite the existing view with the WITH checkoption:

Then insert the following data:

The cause of the error is ' man ' does not satisfy the where condition in the view, the view has with check option is to validate the new data , if the Where condition is satisfied, Insert is allowed, otherwise it is not allowed.

Similarly, the following error is caused because the view requires read-only when it is created, that is, with Read only:

Example 3: Practice two Gee tables to generate a view and verify that new data can be inserted in the view.

Solution: The current SC table:

Current Course Table:

Create VIEW v_2 based on SC tables and course tables, note the SQL command:

To insert new data into the view:

The reason for the error is that the view was created based on two tables, so the new data could not be inserted.

Note

The following three scenarios do not work on the view DML Operation:

1. A view with withcheck option , if the insert data does not meet the Where condition, the DML operation cannot be done, as in example 2 ;

2. A view with withread only, as example 2;

3. based on two or more base table views, such as the STUDENT,COURSE,SC table to generate a view, then this view cannot do DML operations such as example 3 .

oracle-31-View DML Operations