Oracle Views (Virtual Tables) 🪟
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).