PL/SQL DROP VIEW

Views serve as an important tool for simplifying complex queries, enhancing data security, and streamlining database management. They provide a virtual layer that presents data from underlying tables in a structured format. However, views like other database objects need to be managed effectively to maintain data integrity and optimize performance

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

• PL/SQL DROP VIEW is a statement used to remove a view from the database.
  
• When we drop a view, the view definition is removed from the database schema and the view can no longer be accessed or used in queries.

Syntax for Drop a View in PL/SQL:

DROP VIEW view_name;

Explanation:

1. DROP VIEW statement is used to delete the view from the database. It is a Data Definition Language (DDL) statement.
2. view_name is specified as the name of the view that you want to be deleted. Once we execute the DROP VIEW statement along with the name of the view you want to remove the view, the database system will delete the view and its definition from the database.

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

Let’s Create a view named `products_list` that includes `product_id`, `product_name`, and `price` from the `products` table, and then select all data from this view.

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

Output:

Explanation:The above output shows the details of products_list view.

Let’s Create another view named `expensive_products` that includes `product_id`, `product_name`, and `price` from the `products` table where the `price` is greater than 1000, and then select all data from this view.

CREATE OR REPLACE VIEW expensive_products AS 
SELECT product_id, product_name, price
FROM 
products
WHERE price > 1000;

select * from expensive_products;

Output:

Explanation:The above output shows the items which price is more than 1000.

Let’s delete the view from the database with the help of DROP statement. Write the below statement to drop the view:

  DROP view products_list;
//try to access the dropped view
select * from product_info ;

Output:

ERROR at line 1:
ORA-00942: table or view does not exist

Explanation: The above output shows the view is not present in the database. So, the view is successfully dropped from the database.

Let’s delete the another view from the database with the help of DROP statement. Write the below statement to drop the view:

  DROP view expensive_products;
//try to access the dropped view
select * from expensive_products;

Output:

ERROR at line 1:
ORA-00942: table or view does not exist

Explanation:The above output shows the view is not present in the database. So, the view is successfully dropped from the database.

In conclusion, the PL/SQL DROP VIEW statement is a powerful tool for managing database views. It allows administrators to remove unnecessary views, keeping the database schema organized and optimized. Understanding how to effectively use the DROP VIEW statement is essential for maintaining a clean and efficient database environment.