This section delves into Oracle 19c Views, explaining their purpose as virtual tables. Learn how to CREATE VIEW (simple and complex), ALTER VIEW, DROP VIEW, and utilize advanced features like WITH CHECK OPTION and FOR UPDATE views for efficient data management and security.

Views: Virtual Tables

Views in Oracle 19c are logical tables based on the result set of a query. They do not store data themselves but provide a dynamic window into the underlying base tables, simplifying complex queries and enhancing data security.

CREATE VIEW: Simple and Complex Views

The CREATE VIEW statement defines a new view. Simple views are based on a single table and allow DML operations, while complex views involve multiple tables, aggregations, or functions, typically restricting DML.

Example 1: Create Simple View

CREATE VIEW employees_v AS
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE department_id = 60; -- Creates a view showing employees from department 60

Explanation This code creates a simple view employees_v that displays selected columns for employees belonging to department 60 from the employees table.

Example 2: Create Complex View (Join)

CREATE VIEW employee_dept_v AS
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id; -- Creates a view joining employees and departments

Explanation This statement creates a complex view employee_dept_v by joining the employees and departments tables, showing employee names with their respective department names.

Example 3: Create Complex View (Aggregation)

CREATE VIEW department_salaries_v AS
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name; -- Creates a view showing total salary per department

Explanation This SQL creates a complex view department_salaries_v that aggregates the total salary for each department, demonstrating the use of GROUP BY and SUM.

Example 4: Create View with Aliases

CREATE VIEW current_employees AS
SELECT employee_id AS emp_id, first_name || ' ' || last_name AS full_name, hire_date
FROM employees
WHERE TRUNC(SYSDATE) - hire_date > 365; -- View of employees hired over a year ago with aliases

Explanation This view current_employees selects employee_id and concatenates first_name and last_name, using aliases for clarity and filtering by hire date.

Example 5: Create View with Read-Only Option

CREATE VIEW secure_employees_v AS
SELECT employee_id, first_name, last_name, email
FROM employees
WITH READ ONLY; -- Creates a view that cannot be modified via DML

Explanation This view secure_employees_v is created WITH READ ONLY, preventing any DML operations (inserts, updates, deletes) through the view itself, enhancing security.

ALTER VIEW, DROP VIEW

ALTER VIEW is used to recompile a view or add/remove constraints, while DROP VIEW removes a view from the database. Views do not store data, so dropping a view does not affect the underlying tables' data.

Example 1: Recompile View with ALTER VIEW

ALTER VIEW employees_v COMPILE; -- Recompiles the employees_v view

Explanation This command recompiles the employees_v view, which is useful if underlying table structures have changed and the view is now invalid.

Example 2: Drop a Simple View

DROP VIEW employees_v; -- Deletes the employees_v view

Explanation This SQL statement permanently removes the employees_v view from the database.

Example 3: Drop a Complex View

DROP VIEW department_salaries_v; -- Deletes the department_salaries_v view

Explanation This command drops the complex view department_salaries_v, removing its definition from the data dictionary.

Example 4: Drop View with CASCADE CONSTRAINTS (not applicable to views)

-- Views do not have constraints in the same way tables do,
-- so CASCADE CONSTRAINTS is not typically used with DROP VIEW.
-- DROP VIEW some_view CASCADE CONSTRAINTS; -- This syntax is invalid for views

Explanation The CASCADE CONSTRAINTS clause is used with DROP TABLE to drop dependent integrity constraints; it is not relevant for DROP VIEW as views do not enforce constraints.

Example 5: Verify View Status After Drop (Conceptual)

-- After dropping, querying the view will result in an error.
-- SELECT * FROM employees_v; -- This would return ORA-00942: table or view does not exist

Explanation Attempting to query a view after it has been dropped will result in an "ORA-00942: table or view does not exist" error, confirming its removal.

WITH CHECK OPTION

The WITH CHECK OPTION clause prevents DML operations through an updateable view that would create rows not selectable by the view. It ensures that any inserted or updated rows adhere to the view's WHERE clause conditions.

Example 1: Create View WITH CHECK OPTION

CREATE VIEW sales_department_employees AS
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE department_id = 80
WITH CHECK OPTION; -- Ensures any changes through this view maintain department_id = 80

Explanation This view sales_department_employees includes WITH CHECK OPTION to prevent users from inserting or updating employees to a department_id other than 80 via this view.

Example 2: Successful Insert with CHECK OPTION

INSERT INTO sales_department_employees (employee_id, first_name, salary, department_id)
VALUES (300, 'Alice', 7000, 80); -- This insert is allowed

Explanation This INSERT statement is successful because the department_id (80) matches the WHERE clause condition of the sales_department_employees view.

Example 3: Failed Insert with CHECK OPTION

INSERT INTO sales_department_employees (employee_id, first_name, salary, department_id)
VALUES (301, 'Bob', 6500, 90); -- This insert will fail with ORA-01402: view WITH CHECK OPTION where-clause violation

Explanation This INSERT fails because the department_id (90) violates the WITH CHECK OPTION clause of the view, which requires department_id to be 80.

Example 4: Successful Update with CHECK OPTION

UPDATE sales_department_employees
SET salary = 7500
WHERE employee_id = 300; -- This update is allowed

Explanation This UPDATE statement is successful because changing the salary does not violate the department_id = 80 condition.

Example 5: Failed Update with CHECK OPTION

UPDATE sales_department_employees
SET department_id = 50
WHERE employee_id = 300; -- This update will fail with ORA-01402: view WITH CHECK OPTION where-clause violation

Explanation This UPDATE fails because attempting to change department_id from 80 to 50 violates the WITH CHECK OPTION, as the row would no longer be selectable through the view.

FOR UPDATE Views

FOR UPDATE views are used in conjunction with select statements to explicitly lock rows for subsequent updates, preventing other transactions from modifying them. This is primarily for row-level locking during DML operations.

Example 1: Create View for Update (Implied)

CREATE VIEW updatable_employees_v AS
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 50; -- Simple views are often inherently updateable if DML is possible

Explanation This simple view is implicitly updatable because it selects directly from a single table without aggregations or complex joins.

Example 2: Select FOR UPDATE (on Base Table via View)

SELECT employee_id, first_name, salary
FROM updatable_employees_v
WHERE employee_id = 120
FOR UPDATE; -- Locks the row in the underlying 'employees' table via the view

Explanation This SELECT statement, executed through the updatable_employees_v view, acquires a row-level lock on employee_id = 120 in the employees base table.

Example 3: Update After FOR UPDATE

UPDATE updatable_employees_v
SET salary = 9000
WHERE employee_id = 120; -- This update will succeed on the locked row
COMMIT; -- Releases the lock

Explanation After SELECT ... FOR UPDATE, this UPDATE operation modifies the locked row, and the COMMIT statement releases the lock, making changes visible to other transactions.

Example 4: View Not Updateable (Aggregation)

-- This view is NOT updatable and cannot be used with FOR UPDATE clause directly for DML.
-- CREATE VIEW avg_salaries_v AS
-- SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
-- SELECT * FROM avg_salaries_v FOR UPDATE; -- Will raise ORA-01732: data manipulation operation not legal on this view

Explanation Views that involve aggregations (like AVG) are not inherently updatable because the database cannot uniquely map DML operations back to individual rows in the base table.

Example 5: Using FOR UPDATE OF Columns

SELECT employee_id, first_name, salary
FROM updatable_employees_v
WHERE employee_id = 120
FOR UPDATE OF salary; -- Locks the row, specifically for updates on the 'salary' column

Explanation This FOR UPDATE OF salary clause indicates intent to update only the salary column, though Oracle typically locks the entire row. This can be useful for clarity or with deferred locking.