数据库查询高级特性与视图详解

# 数据库查询高级特性与视图详解 ## 1. 高级查询特性 ### 1.1 排序与记录筛选 在查询时，可指定排序标准和记录筛选规则。例如，要求按 MSAL 值降序排序，跳过前 5 条记录，且若结果集的最后一条记录与不在结果集中的其他记录 MSAL 值相同，则将这些记录也包含进来。若查询使用了特定的 ORDER BY 子句，只有当 Oracle DBMS 找到具有相同 MSAL 和 NAME 值组合的其他记录时，才会返回超出指定 5 条限制的额外记录。 ### 1.2 闪回特性 #### 1.2.1 闪回查询准备 闪回特性是 Oracle 对 SQL 语言的特定扩展。在进行闪回查询实验前，需先创建 EMPLOYEES 表的临时副本，并更改 NLS_TIMESTAMP_FORMAT 参数以影响时间戳值在屏幕上的显示方式。以下是具体操作步骤： ```sql SQL> create table e as select * from employees; Table created. SQL> alter session set nls_timestamp_format='DD-MON-YYYY HH24:MI:SS.FF3'; Session altered. SQL> select localtimestamp as table_created from dual; TABLE_CREATED ------------------------------------------------------ 01-OCT-2004 10:53:42.746 SQL> update e set msal = msal + 10; 14 rows updated. SQL> commit; Commit complete. SQL> select localtimestamp as after_update_1 from dual; AFTER_UPDATE_1 ------------------------------------------------------- 01-OCT-2004 10:54:26.138 SQL> update e set msal = msal - 20 where deptno = 10; 3 rows updated. SQL> commit; Commit complete. SQL> select localtimestamp as after_update_2 from dual; AFTER_UPDATE_2 ------------------------------------------------------- 01-OCT-2004 10:54:42.602 SQL> delete from e where deptno <= 20; 8 rows deleted. SQL> commit; Commit complete. SQL> select localtimestamp as now from dual; NOW ------------------------------------------------------- 01-OCT-2004 10:55:25.623 ``` 操作流程如下： ```mermaid graph LR A[创建临时表 e] --> B[更改时间戳格式] B --> C[记录创建时间] C --> D[更新 msal 值] D --> E[提交更新] E --> F[记录更新 1 后时间] F --> G[按条件更新 msal 值] G --> H[提交更新] H --> I[记录更新 2 后时间] I --> J[删除符合条件的记录] J --> K[提交删除操作] K --> L[记录当前时间] ``` #### 1.2.2 AS OF 子句闪回查询 通过 AS OF 子句可实现闪回查询，查看过去某个时间点的数据。以下是具体示例： ```sql -- 评估当前情况 select empno, ename, deptno, msal from e; EMPNO ENAME DEPTNO MSAL -------- -------- -------- -------- 7499 ALLEN 30 1610 7521 WARD 30 1260 7654 MARTIN 30 1260 7698 BLAKE 30 2860 7844 TURNER 30 1510 7900 JONES 30 810 -- 查询过去某个时间点的数据 select empno, ename, deptno, msal from e AS OF TIMESTAMP to_timestamp('01-OCT-2004 10:53:47.000'); EMPNO ENAME DEPTNO MSAL -------- -------- -------- -------- 7369 SMITH 20 800 7499 ALLEN 30 1600 7521 WARD 30 1250 7566 JONES 20 2975 7654 MARTIN 30 1250 7698 BLAKE 30 2850 7782 CLARK 10 2450 7788 SCOTT 20 3000 7839 KING 10 5000 7844 TURNER 30 1500 7876 ADAMS 20 1100 7900 JONES 30 800 7902 FORD 20 3000 7934 MILLER 10 1300 -- 查询时间点过早的情况 select empno, ename, deptno, msal from e AS OF TIMESTAMP to_timestamp('01-OCT-2004 10:53:42.000'); * ERROR at line 2: ORA-01466: unable to read data - table definition has changed ``` #### 1.2.3 VERSIONS BETWEEN 操作符 使用 VERSIONS BETWEEN 操作符可获取行的完整历史记录。示例如下： ```sql break on empno select empno, msal , versions_starttime , versions_endtime from e versions between timestamp minvalue and maxvalue where deptno = 10 order by empno, versions_starttime nulls first; EMPNO MSAL VERSIONS_STARTTIME VERSIONS_ENDTIME -------- -------- ------------------------- ------------------- ```