PL/SQL CREATE VIEW

PL/SQL CREATE VIEW is a statement used to create a virtual table based on the result of a query. Views in PL/SQL allow users to access and manipulate data stored in one or more underlying tables as if it were a single table. In this article, We will learn about the PL/SQL CREATE VIEW by understanding the various examples with the output and so on.

CREATE VIEW in PL/SQL

PL/SQL 'CREATE VIEW' is a statement used to create a virtual table based on the result of a query. Views allow users to access and manipulate data stored in one or more underlying tables as if it were a single table. The 'CREATE VIEW' statement defines the view's name, the columns it will include and the query that retrieves the data. Views provide a way to simplify complex queries enhance data security and improve performance by storing frequently used query logic.

Syntax

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition];

Explanation:

• CREATE VIEW view_name initiates the creation of a view with a specified name.
• [OR REPLACE] is optional, If the view already exists, it will allow us to modify the existing view with a new description. Otherwise, it creates a new view.
• AS SELECT.... is defined as the SELECT query whose result set will content of the view.
• [WHERE CONDITION] is optional, it applies the condition to filtering the rows included in the view.

Setting Up The Environment

To understand about the PL/SQL CREATE VIEW we need a table on which we will perform various operations and queries. Here we will consider a table called products which shown below:

Output:

Example 1: Creating a View to Display Specific Columns From Table

Let's Create a view named `product_info` that includes `product_id`, `product_name`, and `price` from the `products` table. This view simplifies querying for specific product information.

CREATE OR REPLACE VIEW product_info AS
SELECT product_id, product_name, price
FROM products;
select * from product_info;

Output:

Explanation:

• The name of the view is product_info, it selects the columns such as product_id, product_name and price from the products table.
• When we querying the above view, only selected columns will be appear, other columns will be hidden.

Example 2: Creating a View with Data Filtered Based on Condition

Let's Create a view named expensive_products that includes `product_id`, `product_name`, and `price` from the `products` table where the `price` is greater than 100. This view provides a filtered list of products that are considered expensive.

CREATE OR REPLACE VIEW expensive_products AS
SELECT product_id, product_name, price
FROM products
WHERE price > 100;
select * from expensive_products;

Output:

Explanation:

• The name of the view is expensive_products. It is select the rows which is the price is greater than 100.
• When we querying the view, only products meeting the above condition will visible, making it is easy to focus the specific subset of the data.

Conclusion

In conclusion, views in PL/SQL are offer the powerful tool for the simplifying data access and data manipulation. By the abstracting the underlying the complexities of the database structures, views are provide the convenient way to the present subsets of data to the users, enhancing the data security, improving the performance and simplifying the development of application.