Alright, let's break down everything you need to know about views in Oracle—from what they are, why you'd want to use them, to exactly how to create and work with them.

First off, a view is a virtual table—it doesn't store data physically like a regular table. Instead, it's defined by a SQL query that pulls data from one or more base tables (or even other views). Every time you query a view, Oracle runs that underlying query to return the latest results (with the exception of materialized views, which we'll cover later).

Think of it as a saved shortcut to a complex query, or a filtered/transformed lens into your base data.

Views aren't just a convenience—they solve real-world problems:

• Simplify complex queries: If you regularly run a multi-table join with aggregations and filters, wrap that logic into a view. Now you can just SELECT * FROM my_view instead of rewriting the whole SQL every time.
• Boost data security: Restrict users to only the data they need. For example, create a view that hides sensitive columns like salary or social_security_number, or filters rows to only show a user's own department data.
• Ensure data consistency: Multiple applications or teams can use the same view, guaranteeing everyone works with the same logical dataset. No more discrepancies from different people writing slightly different SQL.
• Abstract underlying schema changes: If you rename a column or restructure base tables, you can update the view's query to match, and upstream applications won't need to change a single line of code.

Let's dive into the syntax and practical examples.

Basic CREATE VIEW Syntax #

Here's the standard structure for creating a view:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(column1, column2, ...)]
AS
SELECT your_query_here
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY];

Let's break down each optional clause:

• OR REPLACE: The most handy clause—if the view already exists, this replaces it instead of requiring you to drop it first. Saves a ton of time.
• FORCE: Creates the view even if the base tables don't exist or you don't have permissions on them (it'll only work once the base tables are available and you have access). NOFORCE is the default—requires base tables to exist and you have SELECT access to them.
• (column1, column2, ...): Optional, but recommended if your SELECT uses expressions (like concatenation or aggregations) that would result in messy default column names.
• WITH CHECK OPTION: Ensures any INSERT/UPDATE operations through the view follow the view's WHERE clause. For example, if your view only shows employees in department 10, you can't update a row to move them to department 20 via this view.
• WITH READ ONLY: Makes the view query-only—no INSERT/UPDATE/DELETE allowed. Perfect for reporting or shared datasets you don't want modified.

Practical Examples of Different View Types #

1. Simple View (Single Table, No Aggregations) #

Let's create a view that shows only essential employee details, hiding sensitive or irrelevant columns:

CREATE OR REPLACE VIEW emp_public_info
AS
SELECT 
  first_name || ' ' || last_name AS full_name,
  job_id,
  hire_date
FROM employees
WHERE hire_date >= '01-JAN-2020';

Now you can query this view just like a table: SELECT * FROM emp_public_info;

2. Complex View (Multi-Table Join + Aggregations) #

For a department salary summary that joins employees and departments, with aggregated metrics:

CREATE OR REPLACE VIEW dept_salary_stats (dept_name, avg_salary, total_employees)
AS
SELECT 
  d.department_name,
  ROUND(AVG(e.salary), 2) AS avg_salary,
  COUNT(e.employee_id) AS total_employees
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

We explicitly named the view columns here because the aggregations would otherwise have clunky default names like AVG(e.salary).

3. View WITH CHECK OPTION #

Create a view that only lets users interact with employees in department 10, and enforces that rule for updates:

CREATE OR REPLACE VIEW emp_dept10
AS
SELECT * FROM employees
WHERE department_id = 10
WITH CHECK OPTION CONSTRAINT emp_dept10_check_rule;

Try running UPDATE emp_dept10 SET department_id = 20 WHERE employee_id = 100;—Oracle will throw an error, because it violates the CHECK OPTION constraint.

4. Read-Only View #

Lock down a view so no one can modify the underlying data:

CREATE OR REPLACE VIEW dept_readonly
AS
SELECT department_id, department_name, location_id
FROM departments
WITH READ ONLY;

Any attempt to INSERT, UPDATE, or DELETE on this view will fail immediately.

5. Materialized View (Physical Stored Data) #

Unlike regular views, materialized views store the query results physically on disk. They're great for slow-changing data that's queried often (like reports).

CREATE MATERIALIZED VIEW mv_dept_salary_summary
BUILD IMMEDIATE -- Generate data right away
REFRESH COMPLETE ON DEMAND -- Fully refresh data when we manually trigger it
AS
SELECT 
  d.department_name,
  SUM(e.salary) AS total_salary,
  COUNT(e.employee_id) AS emp_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

To refresh the data (get the latest from base tables), run:

REFRESH MATERIALIZED VIEW mv_dept_salary_summary;

• Permissions: To create a view, you need the CREATE VIEW system privilege, plus SELECT access on all base tables. For OR REPLACE, you'll need CREATE ANY VIEW or ownership of the existing view.
• Performance: Regular views don't improve query performance—they just simplify the SQL. Make sure the underlying SELECT query is optimized (proper indexes, etc.). Materialized views are faster for queries but require planning around refresh frequency to avoid stale data.
• Updatability: Not all views can be updated. Views with aggregations, GROUP BY, DISTINCT, or multi-table joins are typically non-updatable. Simple single-table views (without those clauses) are updatable unless you add WITH READ ONLY.

内容的提问来源于stack exchange，提问作者Archeofuturist