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).
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.
Contact Us