Update Data in the connection view of Oracle

The database view is an extension object of a table. Theoretically, data is updated using the DML Statement on the view, which will eventually be completed on the basic table. That is to say, you can modify the content of the base table through the view. However, it is often not that simple. If you want to execute the DML modification Statement on the connection view, you must strictly abide by some restrictions. Otherwise, the DML statement will not be executed successfully.

Assume that there are three tables in the personnel management system. One is the basic employee information table, which includes the employee ID (non-empty), employee name (non-empty), ID card number, and other fields; the second table is the Enterprise Department position table, which contains the job ID (not empty), job name (not empty), job description, and other fields. The third table is the corresponding table of employees and department positions, includes employee ID (not empty), job ID (not empty), description, and other fields.

The database administrator now provides a view to query employee numbers, employee names, and department information. Now, can the connection view be updated using DML statements? This depends on whether it meets certain conditions.

 

Condition 1: The Order by sorting statement cannot be found in the connection view.

If you want to change the department of an employee in the preceding view, consider whether the Order by sorting statement is used in the query statement that generates the view. If this sort statement is available, DML operations on the view will not be successful.

This is mainly because the Order by sorting statement changes the record's physical storage Order. If data is updated on the view, the physical location of the corresponding basic table cannot be found. Therefore, it will end with a failure.

Therefore, if you want to change the department of an employee in the preceding view, you cannot add Order by or other sorting statements to the SQL statement. Similarly, the SQL statement cannot contain Group by, connetc by, and other statements. If these statements are available, the database does not allow data update.

 

Condition 2: All not null columns in the base table must be in this view.

To update data in a view, all fields in the basic table that do not allow null must be in the current view. In fact, this is easy to understand. If each field cannot be blank and is not in the current view, this field is not assigned a value when a new record is added, therefore, it is rejected by the base table when it is saved.

In the preceding example, if the view created by the database administrator does not contain all non-empty fields. For example, the overall position number of the Enterprise Department position table is not in this view. Therefore, the database does not accept updates in this view. If you do need to update data in this view, you should consider displaying all non-empty fields on this view.

In addition, whether it is an update or delete statement, it cannot be changed if a non-empty column in the base table is not in this view. Someone may ask, if the user does not add a record, but only updates data. Does it also require that the view contain all non-empty fields? The answer is yes. Because the database system determines this condition before committing an update transaction.

 

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

In a view, the results of some columns may be defined by column expressions and do not exist in the basic table. We call these columns virtual columns. For example, in the preceding employee information table, there is no employee's date of birth information. In the view, we can use the ID card number to obtain the birth date of an employee. If you want to change the date of birth in the view, it is obviously not possible. Because this field does not exist in the basic table, the changes cannot be saved.

As long as a virtual Column exists in the view and any column in the view is defined by the column expression, sorry, the whole view cannot be changed. This control principle is similar to the above conditions.

It can be seen that when designing a database, you need to consider whether to change the table content based on The View. If you need to change the value, you must not use virtual columns in the view. Instead, you would rather add more fields in the table. Alternatively, you can use virtual columns in front-end applications instead of virtual columns in the database view.

 

Condition 4: The group function cannot be available.

As shown in the preceding table on the relationship between employees and positions, the user not only wants to know the existing employees in a position, but also wants to know the specific number of employees in a position. To achieve this, the database administrator needs to use the grouping function in the view to count the number of people in a certain position.

However, if the view contains this function, it cannot be updated. This is mandatory for Oracle databases.

In fact, this can also be avoided in some flexible ways. For example, you do not need to use grouping functions in the database view. In the Select statement at the front end, query the grouping function. Because the front-end needs to call the data in the database, you still need to query the view using the Select statement. Therefore, even if you do not group data in the original database view, you can still complete data grouping and statistics tasks in the foreground application. In this case, if you change the data in the view, you can not only update the content in the basic database table, but also timely feedback to the front-end application interface.

Grouping functions increase the burden on database queries. At the same time, DML operations cannot be performed on The View. Therefore, the database management personnel must negotiate with the front-end application developers to implement grouping statistics on data at the front-end, rather than at the back-end.

 

In addition to the preceding restrictions, if you need to perform DML operations on the view, the Select statement for creating the view cannot have set operators, subqueries, and so on. These are the basic conditions that must be met. Otherwise, DML operations on the view will end with failures.

However, it does not mean that after the preceding conditions are met, the view can smoothly update data, and it must still comply with certain rules. Among them, the most important thing is to save the table rules with key values.

If the key of a base table in the connection view still exists in its view and is still the primary key in the connection view, the base table is saved as the key value. When you insert, delete, or update a view, you can update only one key-value table in the view at a time.