PL/SQL Views
Abhirami T
VIEW
• VIEW is a virtual table that does not physically exist.
Rather, it is created by a query joining one or more tables.
• PL/SQL Views are used to provide security or simplify
complex queries
• We can create, update, and drop VIEWS
CREATE VIEW:
CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, [Link],
[Link]
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'Microsoft';
• This Oracle CREATE VIEW example would create a
virtual table based on the result set of the SELECT
statement. You can now query the Oracle VIEW as
follows:
• SELECT *
• FROM sup_orders;
Update VIEW
CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, [Link], [Link]
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'Apple';
This Oracle CREATE OR REPLACE VIEW example would update
the definition of the Oracle VIEW called sup_orders without
dropping it.
Drop VIEW
DROP VIEW sup_orders;
This Oracle DROP VIEW example would drop/delete the
Oracle VIEW called sup_orders.
Renaming a View
• ALTER VIEW old_view_name RENAME TO
new_view_name;
• ALTER VIEW sup_orders RENAME TO s_orders;
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, [Link]
FROM employees e
INNER JOIN departments d ON e.department_id =
d.department_id;
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.
CREATE VIEW job_titles AS
SELECT DISTINCT job_title
FROM employees
ORDER BY job_title;
