PL/SQL VIEW

In PL/SQL, views are virtual tables created by stored queries, providing a dynamic view of data from one or more tables. Unlike physical tables, views do not store data themselves but act as convenient, encapsulated queries.

In this article, We will learn about the VIEW in PL/SQL in detail by understanding the CREATE VIEW, UPDATE VIEW and DROP VIEW with the examples in detail.

• A PL/SQL view is a virtual table produced by a stored query. Unlike the real tables that hold the data, views never save data by themselves.
• Views exactly create the dynamic view of data that comes from one or multiple tables.
• A view behaves like a saved query, which enables easy access to data as it encapsulates all complex join, aggregations, and filters in one object.

Let’s consider one table called employee which we will use as an example for understanding the view in a better manner.

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

INSERT INTO employee (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Smith', 'Sales', 50000),
(2, 'Alice', 'Johnson', 'Marketing', 55000),
(3, 'Michael', 'Williams', 'IT', 60000),
(4, 'Emily', 'Brown', 'Human Resources', 48000),
(5, 'David', 'Jones', 'Finance', 62000),
(6, 'Sarah', 'Martinez', 'Operations', 53000);

Output

employee_id | first_name | last_name | department      | salary
------------|------------|-----------|-----------------|-------
1           | John       | Smith     | Sales           | 50000
2           | Alice      | Johnson   | Marketing       | 55000
3           | Michael    | Williams  | IT              | 60000
4           | Emily      | Brown     | Human Resources | 48000
5           | David      | Jones     | Finance         | 62000
6           | Sarah      | Martinez  | Operations      | 53000

In PL/SQL, creating a view is achieved using the CREATE VIEW statement, which allows us to define a virtual table based on the results of a query. Let’s understands with an example using our “employee” table:

CREATE VIEW employee_view AS 
SELECT employee_id, first_name, last_name, department 
FROM 
employee;

Output:

employee_id | first_name | last_name | department
------------|------------|-----------|-----------------
1           | John       | Smith     | Sales
2           | Alice      | Johnson   | Marketing
3           | Michael    | Williams  | IT
4           | Emily      | Brown     | Human Resources
5           | David      | Jones     | Finance
6           | Sarah      | Martinez  | Operations

Explanation: In this example, we create a view named “employee_view” that selects specific columns from the “employee” table. This view will provide a simplified view of employee data, containing only the employee ID, first name, last name, and department.

Generally, the PL/SQL views are read-only, but it is still possible to update data through a view under some conditions. Let’s understand this with an example:

Let’s take an example, where we need to update the department of employee 1 from “Sales” to “Customer Service,” using the “employee_view.”

UPDATE employee_view
SET department = 'Customer Service'
WHERE employee_id = 1;

Explanation: In PL/SQL, we cannot directly update the data of an already created view because views are typically read-only representations of data. However, we can modify the underlying query of the view to include updated data.

Finally, if we no longer need a view, we can remove it from the database using the DROP VIEW statement. Let’s understand this with an example:

DROP VIEW employee_view;

Output:

Error : no such table: employee_view

Explanation: The DROP VIEW statement deletes the specified view from the database schema. If we are trying to get the view after performing the above query then we get the error as shown in the output. Also, It is irreversible therefore we should use it with care.

Using PL/SQL views simplify the access to the data and improve security and code reusability. Through the knowledge of how to create, update, and drop views, developers will be able to use these features to simplify the database operations and enhance the system efficiency. Whether it is utilized for the reason of data reporting, security enforcement or data integration, the view is a key function in database management and application development.