PL/SQL UPDATE VIEW

About Oracle Database management, the capability of updating data using views is the key element that gives data manipulation operations flexibility and efficiency. The PL/SQL, Oracle’s procedural extension to SQL, permits the execution of various complex data operations like view updating.

In this article, we will discuss the syntax and capabilities of PL/SQL UPDATE VIEW and provide a learning path to help developers adapt it to their data management needs.

PL/SQL UPDATE VIEW

View in Oracle Database is a logical table that can be regarded as a virtual table that joins data from one or more tables. Views provide an easy way to query and display data, but they are usually only good for something that is read-only when it is implemented. In PL/SQL, developers can change views which, in turn, can allow for an up-to-the-minute update of data presented through the views upgrading. The UPDATE VIEW statement in PL/SQL provides the medium for such updates by triggering important changes in the base tables when necessary.

Syntax:

The syntax for updating a view in PL/SQL is similar to updating a table:

UPDATE view_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

Here,

• view_name is the name of the view, this view.
• column1, column2, etc. are replaced with the columns to be modified.
• col1, col2, etc. are being substituted by values 1, 2, etc. for the corresponding columns.
• The optional WHERE clause dictates only specific conditions that satisfy the update operation will be performed. If neglected, every single row being presented by the view will be overwritten.

Example of PL/SQL UPDATE VIEW

-- Developing the 'employees' table

CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary INT
);

-- Inputting dummy data into the 'employees' table

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'Alice', 'Smith', 'Sales', 50000),
(2, 'Bob', 'Johnson', 'Marketing', 60000),
(3, 'Charlie', 'Brown', 'IT', 70000),
(4, 'David', 'Lee', 'Finance', 55000);

Output:

+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
|           1 | Alice      | Smith     | Sales      |  50000 |
|           2 | Bob        | Johnson   | Marketing  |  60000 |
|           3 | Charlie    | Brown     | IT         |  70000 |
|           4 | David      | Lee       | Finance    |  55000 |
+-------------+------------+-----------+------------+--------+

Let us Create a View

Now, let’s create a view named based on the employees table:

CREATE VIEW employee_view AS
SELECT * FROM employees;

Output:

+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
|           1 | Alice      | Smith     | Sales      |  50000 |
|           2 | Bob        | Johnson   | Marketing  |  60000 |
|           3 | Charlie    | Brown     | IT         |  70000 |
|           4 | David      | Lee       | Finance    |  55000 |
+-------------+------------+-----------+------------+--------+

This SQL code creates a view named ‘employee_view‘ that retrieves all columns and rows from the ‘employees‘ table, essentially providing a virtual representation of the ’employees’ table under the name ‘employee_view‘.

UPDATE VIEW

Next, we’ll update the employee_view view to increase the salary of employees by 10%:

UPDATE employee_view
SET salary = salary * 1.1;

Output:

| employee_id | first_name | last_name | department | salary        |
|-------------|------------|-----------|------------|---------------|
| 1           | Alice      | Smith     | Sales      | 55000.00000000001 |
| 2           | Bob        | Johnson   | Marketing  | 66000           |
| 3           | Charlie    | Brown     | IT         | 77000           |
| 4           | David      | Lee       | Finance    | 60500.00000000001 |

This SQL query updates the ‘salary‘ column in the ‘employee_view‘ table, increasing each salary by 10% (multiplied by 1.1).

DROP VIEW

Suppose we have the employee_view view created earlier. We can drop it using the following command:

DROP VIEW employee_view;

Output:

Upon executing the DROP VIEW command, the employee_view view will be removed from the database schema. Any subsequent attempts to query or manipulate data using this view will result in an error indicating that the view does not exist.

Conclusion

To sum up, the UPDATE VIEW statement in Oracle Database using PL/SQL allows the developers to dynamically edit data presented through views, which provides flexibility for the data manipulation tasks. By updating views, changes are sent to base tables resulting in keeping data consistent. As a result, this function helps to streamline the database management process and make developers’ jobs easier.