Shulou(Shulou.com)06/01 Report--

This article is to share with you about the Oracle view can be DML operation conditions, the editor thinks it is very practical, so share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

Condition 1: there can be no sorting statements such as Order by, Group by, connetc by and so on in the connection view.

Condition 2: all NOT NULL columns in the underlying table must be in this view.

Condition 3: the column to be updated is not a virtual column.

Condition 4: cannot have a grouping function.

In addition to the above restrictions, if you need to perform DML operations on a view, there can be no set operators, subqueries, and so on in the Select statement that creates the view. These are some of the basic conditions that must be met, all of which are indispensable. Otherwise, the DML operation on the view will fail.

However, it does not mean that after meeting the above conditions, the view can update the data without hindrance, it must still comply with certain rules. Among them, the most important thing is that the key value saves the table rules.

If the key of an underlying table in the join view still exists in his view and is still the primary key in the join view, the underlying table saves the table for the key value. On the connected view, when inserting, deleting, updating, and other operations on the view, only one key value saving table in the view can be updated at a time.

With check option will be checked by WITH CHECK OPTION if data rows that violate the scope of view selection are inserted

DML actions on the view:

Principles that should be followed in DML operations:

1. Simple view can perform DML operation

two。 Data rows cannot be deleted when the view contains GROUP functions, GROUP BY clauses, and DISTINCT keywords

3. The base table data can be modified or inserted through the view when the view does not:

a. The view contains GROUP functions, GROUP BY clauses, and DISTINCT keywords

b. Columns defined using expressions

C.ROWNUM pseudo column.

d. Other columns in the base table that are not selected in the view are defined as non-empty and have no default values.

WITH CHECK OPTION clause

INSERTS and UPDATES operations performed through a view cannot create data rows that cannot be retrieved by that view because it performs integrity constraints and data validity checks on inserted or modified data rows. (that is to say, when executing INSERTS and UPDATES, the WHERE condition needs not only the restrictions of INSERT and UPDATE itself, but also the WHERE condition when the view is created. )

These are the conditions under which the Oracle view can operate DML. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.