PL/SQL Views – Oracle PL/SQL Tutorial

A PL/SQL view is a virtual table that contains data from one or more tables. PL/SQL Views are used to provide security or simplify complex queries. In this article we have a list of operations that can be performed on PL/SQL Views.

Creating a View

To create a view in Oracle database, you use the CREATE VIEW statement as follows:

CREATE VIEW view_name AS 
SELECT column1, column2,... 
FROM table_name 
WHERE condition;

For example, to create a view that displays all employees in the sales department, you use the following statement:

CREATE VIEW sales_employees AS 
SELECT first_name, last_name 
FROM employees 
WHERE department = 'Sales';

To display the view, you use the SELECT statement as follows:

SELECT * FROM view_name;

For example, to display all employees in the sales department, you use the following statement:

SELECT * FROM sales_employees;

Updating a View

To update data in a view, you use the UPDATE View statement. For example, to increase the salary of all employees in the sales department by 10%, you use the following statement:

UPDATE sales_employees SET salary = salary * 1.1;

Modifying a View

You can modify an existing view by using the CREATE OR REPLACE VIEW statement. This statement first drops the existing view and then creates it again.

For example, to modify the sales_employees view so that it displays all employees in the sales or marketing departments, you use the following statement:

CREATE OR REPLACE VIEW sales_employees AS 
SELECT first_name, last_name 
FROM employees 
WHERE department IN ('Sales', 'Marketing');

Renaming a View

To rename a view, you use the RENAME TO clause of the ALTER VIEW statement as follows:

ALTER VIEW old_view_name RENAME TO new_view_name;

Dropping a View

To drop a view, you use the DROP VIEW statement. For example, to drop the sales_employees view, you use the following statement:

DROP VIEW sales_employees;

Inserting Data into a View

You can insert data into a view if it meets the following conditions:
– The PL/SQL view must be created by using the WITH CHECK OPTION clause.
– All columns in the view must be from a single table or views based on that table.
– The view must not contain any virtual columns.
– The view must not contain any set operators (UNION or UNION ALL).

CREATE VIEW view_name AS 
SELECT * FROM table_name WHERE condition 
WITH CHECK OPTION; 
INSERT INTO view_name (c1, c2,...) VALUES (v1, v2,...);

Deleting Data from a View

You can delete data from a view if it meets the following conditions:
– The view must be created by using the WITH CHECK OPTION clause.
– All columns in the view must be from a single table or views based on that table.
– The view must not contain any virtual columns.

CREATE VIEW view_name AS 
SELECT * FROM table_name WHERE condition 
WITH CHECK OPTION; 
DELETE FROM view_name WHERE condition;

Querying Multiple Tables to Create a View

To query multiple tables to create a PL/SQL view, you use the JOIN or UNION operators. For example, to create a view that displays all employees and their departments, you use the following statement:

CREATE VIEW emp_dept AS 
SELECT e.first_name, e.last_name, d.department 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id;

Views with Aggregate Functions

You can create views that contain aggregate functions such as SUM or AVG if the view also contains a GROUP BY clause or the DISTINCT keyword. For example, the following view displays the average salary of all employees in each department:

CREATE VIEW dept_avg_salary AS 
SELECT AVG(salary) avg_sal, d.department 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id 
GROUP BY d.department;

Views with DISTINCT or ORDER BY

You can use the DISTINCT or ORDER BY clauses in views to remove duplicate rows or sort the rows in a certain order. For example, to create a view that displays all unique job titles, you use the following statement:

CREATE VIEW job_titles AS 
SELECT DISTINCT job_title 
FROM employees
ORDER BY job_title;

Views That Contain a Subquery in the SELECT

With Oracle PL/SQL you can create views that contain a subquery in the SELECT clause. For example, to create a view that displays all departments and the number of employees in each department, you use the following statement:

CREATE VIEW dept_emp AS 
SELECT d.department, 
(SELECT COUNT(*) FROM employees e 
WHERE e.department_id = d.department_id
) num_of_emp 
FROM departments d;

Views That Contain a Subquery in the WHERE

With Oracle PL/SQL you can create views that contain a subquery in the WHERE clause. For example, to create a view that displays all employees who work in the Sales or Marketing department, you use the following statement:

CREATE VIEW sales_employees AS 
SELECT first_name, last_name 
FROM employees WHERE department IN 
(SELECT department FROM departments WHERE location_id = 1700);