Oracle Views (Virtual Tables) 🪟¶

Prerequisites: SELECT Statement, Joins

Mentor's Note: A View is like a "Saved Bookmark" of a query. It doesn't store data itself; it's just a window into other tables. Use it to make complex queries look simple! 💡

🌟 The Scenario: The Security Guard 🛡️¶

Imagine a table with all Employee info, including Home Addresses and Private Salaries. - The Problem: You want the Receptionist to see names, but NOT the salaries. - The Solution: You create a View that only includes the "Name" column. - The Result: The Receptionist thinks they are looking at a table, but they only see what you allow!

💻 1. Creating a Basic View¶

CREATE VIEW staff_list AS
SELECT first_name, last_name, job_id
FROM employees;

-- Query it just like a table!
SELECT * FROM staff_list;

💻 2. Updatable Views ✏️¶

Can you UPDATE a view? - Yes, if it's a simple view pointing to a single table. - No, if it contains GROUP BY, DISTINCT, or certain types of Joins.

💻 3. Inline Views (Nested Queries)¶

A view that exists only inside your query.

SELECT * FROM (
    SELECT id, salary FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 5;

💻 4. WITH CHECK OPTION 🚦¶

Prevents users from inserting data through a view that wouldn't actually be visible in that view.

CREATE VIEW it_staff AS
SELECT * FROM employees WHERE department_id = 60
WITH CHECK OPTION;
-- If you try to insert an HR employee here, Oracle will reject it!

🏗️ Architect's Note: Performance & Logic 🛡️¶

Views are excellent for abstraction, but be careful! - The Architect's Warning: "View on a View on a View" is a recipe for disaster. This creates "Query Spaghetti" which makes it impossible for the optimizer to find a fast plan. - Tip: Keep your view hierarchy shallow (Max 2 levels deep).