Oracle视图 #

视图类型
├── 简单视图
│   └── 基于单个表，无函数
├── 复杂视图
│   └── 多表连接，包含函数
├── 可更新视图
│   └── 允许DML操作
└── 只读视图
    └── 不允许DML操作

-- 创建视图基本语法
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias1, alias2, ...)]
AS
SELECT statement
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY];

-- 创建简单视图
CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10;

-- 使用视图
SELECT * FROM emp_view;

-- 创建带别名的视图
CREATE OR REPLACE VIEW emp_view (
    emp_id, emp_name, emp_salary
) AS
SELECT employee_id, first_name || ' ' || last_name, salary
FROM employees
WHERE department_id = 10;

-- 创建多表连接视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

-- 创建包含聚合函数的视图
CREATE OR REPLACE VIEW dept_stats_view AS
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

-- 可更新视图条件：
-- 1. 不包含聚合函数
-- 2. 不包含DISTINCT
-- 3. 不包含GROUP BY
-- 4. 不包含集合运算
-- 5. 不包含ROWNUM伪列

-- 创建可更新视图
CREATE OR REPLACE VIEW emp_update_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10;

-- 通过视图插入
INSERT INTO emp_update_view VALUES (999, 'Test', 'User', 5000);

-- 通过视图更新
UPDATE emp_update_view SET salary = 6000 WHERE employee_id = 999;

-- 通过视图删除
DELETE FROM emp_update_view WHERE employee_id = 999;

-- WITH CHECK OPTION：确保DML操作后的数据仍在视图中
CREATE OR REPLACE VIEW emp_dept_10_view AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

-- 允许：更新后仍在视图中
UPDATE emp_dept_10_view SET salary = 6000 WHERE employee_id = 100;

-- 不允许：更新后不在视图中
UPDATE emp_dept_10_view SET department_id = 20 WHERE employee_id = 100;
-- ORA-01402: view WITH CHECK OPTION where-clause violation

-- 创建只读视图
CREATE OR REPLACE VIEW emp_readonly_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10
WITH READ ONLY;

-- 不允许DML操作
-- INSERT INTO emp_readonly_view VALUES (999, 'Test', 'User', 5000);
-- ORA-42399: cannot perform a DML operation on a read-only view

-- 查看视图定义
SELECT view_name, text FROM user_views;

-- 查看视图详细信息
SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';

-- 查看视图列
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMP_VIEW';

-- 使用DESC命令
DESC emp_view;

-- 使用CREATE OR REPLACE修改视图
CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = 10;

-- 删除视图
DROP VIEW emp_view;

-- 删除不存在的视图（避免错误）
DROP VIEW IF EXISTS emp_view;

-- 内联视图：FROM子句中的子查询
SELECT e.employee_id, e.first_name, d.department_name
FROM (
    SELECT employee_id, first_name, department_id
    FROM employees
    WHERE salary > 10000
) e
JOIN departments d ON e.department_id = d.department_id;

-- 使用内联视图进行分页
SELECT *
FROM (
    SELECT e.*, ROWNUM rn
    FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) e
    WHERE ROWNUM <= 10
)
WHERE rn > 5;

-- 推荐：使用v_或view_前缀
CREATE OR REPLACE VIEW v_employee_details AS ...
CREATE OR REPLACE VIEW view_dept_stats AS ...

-- 1. 简化复杂查询
CREATE OR REPLACE VIEW v_emp_full_info AS
SELECT e.*, d.department_name, l.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id;

-- 2. 数据安全
CREATE OR REPLACE VIEW v_emp_public AS
SELECT employee_id, first_name, last_name
FROM employees;  -- 隐藏敏感列如salary

-- 3. 业务逻辑封装
CREATE OR REPLACE VIEW v_active_employees AS
SELECT * FROM employees
WHERE status = 'ACTIVE' AND hire_date <= SYSDATE;