Oracle 分析函数

1、分析函数

分析函数提供一系列比较高级的SQL功能。分析函数时建立在数据窗口（over在一定的数据库范 围进行数据分析），在一定的数据范围进行排序、汇总等。 查询当前员工所在部门的平均工资、最高工资和最低工资，即可通过分析函数来实现。

  1. SELECT e.last_name,
  2.  e.salary,
  3.  d.department_name,
  4.  AVG(e.salary) over(PARTITION BY d.department_name) department_avg_salary,
  5.  MAX(e.salary) over(PARTITION BY d.department_name) department_max_salary,
  6.  MIN(e.salary) over(PARTITION BY d.department_name) department_min_salary
  7.  FROM employees e, departments d
  8. WHERE 1 = 1
  9.  AND e.department_id = d.department_id;

将一个部门内的员工的工资从高到低进行排序，注意三种排序结果是不一样的

  1. SELECT d.department_name,
  2.  e.last_name,
  3.  e.salary,
  4.  rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1,
  5.  dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2,
  6.  row_number() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank3
  7.  FROM employees e, departments d
  8. WHERE 1 = 1
  9.  AND e.department_id = d.department_id;

2、闪回

Oracle数据库中提供一种机制可以用来查询之前一段时间内修改行原先的值，比如错误的提 交了修改结果，现在又想查看修改行原来的值，这时候就可以使用闪回。 需要注意通过闪回的时效是有限制的，具体能闪回到多久之前的数据和数据库的设置有关系，有兴趣可以继续研究。

  1. DELETE FROM departments
  2. WHERE department_name = 'Finance';
  3. COMMMIT;
  4. SELECT * FROM departments WHERE department_name = 'Finance';
  5. 此时不会查询到数据。
  6. 闪回到5分钟之前，再执行下查询试试有没有结果。
  7. SELECT * FROM departments AS OF TIMESTAMP SYSDATE-5/(24*60)
  8. WHERE department_name = 'Finance';

3、全局临时表

开发程序中也是经常使用的，在不同的回话之间可以屏蔽数据，可以有触发器， 检查约束，索引等。比如在程序的执行过程的当前回话中需要临时存放一些数据，这些数据是其 他回话无法访问的，此时全局临时表就是一个很好的方案。

  1. #基于会话
  2. CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT PRESERVE ROWS;
  3. 基于会话的临时表，在会话断开之前，或者通过一个delete或truncate物理地删除行之前，
  4. 这些行会一直存在于这个临时表中。只有我的会话能看到这些行，即使我已经提交，其他会话也无法看到我的行。
  5. #基于事务
  6. CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT DELETE ROWS;
  7. 基于事务的临时表，我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，
  8. 这些行就会消失，在临时表的自动清除过程中不存在开销。

4、物化视图

物化视图使用的也比较普遍，是一种特殊的物理表，有别于普通的数据视图。在一段时间内保存 固定的数据，提供自动刷新和手动刷新的机制。

  1. CREATE MATERIALIZED VIEW materialized_view_name
  2. BUILD [IMMEDIATE|DEFERRED] --1.创建方式
  3. REFRESH [COMPLETE|FAST|FORCE|NEVER] --2.物化视图刷新方式
  4. ON [COMMIT|DEMAND]         --3.刷新触发方式
  5. START WITH (start_date)    --4.开始时间
  6. NEXT (interval_date)       --5.间隔时间
  7. WITH [PRIMARY KEY|ROWID]   --6.默认 primary key
  8. ENABLE QUERY REWRITE       --7.是否启用查询重写
  9. AS                         --8.关键字
  10. SELECT STATEMENT;          --9.基表选取数据的select语句

5、VPD

VPD 全称Virtual Private Database，这个技术提供了对数据库信息的细粒度访问控制。 是数据库层面的一种非常成熟的数据访问控制技术，通过策略函数来实现的具体的控制。

  1. --1）创建策略函数 Function:
  2. CREATE OR REPLACE FUNCTION vpd_test(p_schema VARCHAR2, p_object VARCHAR2)
  3.  RETURN VARCHAR2 AS
  4. BEGIN
  5.  RETURN 'employee_id >= 100';
  6. END;
  7. --2）对数据库对象应用策略函数：
  8. BEGIN
  9.   dbms_rls.add_policy(object_schema => 'STUDENT1',
  10.   object_name => 'EMPLOYEES',
  11.     policy_name => 'VPD_TEST',
  12.     function_schema => 'STUDENT1',
  13.     policy_function => 'VPD_TEST');
  14. END;
  15. --3）通过查询数据字典，确认数据库对象上是否有策略函数：
  16.  SELECT * FROM dba_policies t WHERE t.object_name = 'EMPLOYEES';