Views in Oracle with examples

In Oracle, a view is a virtual table based on the result of a SQL query. It does not store the data itself but provides a way to simplify complex queries, enhance security, and present data in a specific format. Here’s an overview of views in Oracle, including how to create, manage, and use them effectively, with examples.

Creating a View

Basic Syntax

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

Examples

1. Simple View

Create a view that shows a subset of columns from a table.

Assume you have a table employees with columns employee_id, first_name, last_name, department_id, and salary.

CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;

You can now query the view just like a table:

SELECT * FROM emp_view;

2. View with WHERE Clause

Create a view to show employees from a specific department.

CREATE VIEW hr_employees AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

Query the view:

SELECT * FROM hr_employees;

3. Join View

Create a view that joins multiple tables.

Assume you have two tables, employees and departments.

CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Query the view:

SELECT * FROM emp_dept_view;

4. View with Aggregation

Create a view that shows aggregated data.

CREATE VIEW dept_salary_view AS
SELECT department_id, AVG(salary) AS avg_salary, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Query the view:

SELECT * FROM dept_salary_view;

Managing Views

1. Updating a View

You can update the definition of a view using CREATE OR REPLACE VIEW.

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

2. Dropping a View

Drop a view when it is no longer needed.

DROP VIEW emp_view;

Using Views

1. Inserting Data into a View

If a view is simple (single table, no joins, no aggregations), you can insert data into it. Assume you have a view simple_emp_view:

CREATE VIEW simple_emp_view AS
SELECT employee_id, first_name, last_name
FROM employees;

Insert data into the view:

INSERT INTO simple_emp_view (employee_id, first_name, last_name) VALUES (101, 'John', 'Doe');

2. Updating Data in a View

You can update data through a simple view.

UPDATE simple_emp_view
SET last_name = 'Smith'
WHERE employee_id = 101;

3. Deleting Data from a View

You can delete data through a simple view.

DELETE FROM simple_emp_view
WHERE employee_id = 101;

Practical Use Cases

1. Security and Access Control

Views can restrict access to specific columns or rows.

CREATE VIEW employee_public_view AS
SELECT employee_id, first_name, last_name
FROM employees;

Grant access to the view without exposing sensitive columns like salary.

GRANT SELECT ON employee_public_view TO public_user;

2. Simplifying Complex Queries

Use views to encapsulate complex joins and calculations.

CREATE VIEW complex_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, 
       AVG(e.salary) OVER (PARTITION BY d.department_id) AS avg_dept_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Query the view without needing to write the complex SQL each time:

SELECT * FROM complex_view;

3. Data Abstraction

Abstract the underlying table structure from end-users.

CREATE VIEW project_data AS 
SELECT project_id, project_name, start_date, end_date 
FROM projects;

End-users query project_data without needing to know the exact table structure.

Considerations

• Performance: Views do not store data, so querying a view performs the underlying SQL each time. Complex views can impact performance.
• Updatability: Not all views are updatable. Views involving joins, aggregations, or certain functions may not support DML operations.
• Dependency Management: Changing the underlying table structure can break views. Use CREATE OR REPLACE VIEW to update views as needed.

Conclusion

Views in Oracle are powerful tools that provide a way to simplify SQL queries, enhance security, and present data in specific formats. By using views, you can abstract complexity, enforce security policies, and ensure consistent data access patterns across your applications.