---

前言

Oracle数据库作为全球领先的关系型数据库管理系统（RDBMS），自1977年诞生以来，始终是企业级数据管理的核心。本文将从基础理论出发，结合可视化图表和代码实践，带你系统掌握Oracle的核心技能。

---

一、Oracle的起源

1. 时代背景（1970年代）：
   • 数据库技术的荒漠：企业主要使用层次型数据库（如IMS）和网状数据库（如CODASYL），数据结构僵化，查询复杂。
   • 理论突破：1970年IBM研究员Edgar F. Codd发表论文《A Relational Model of Data for Large Shared Data Banks》，提出关系型数据库理论（二维表+集合操作）。
2. 历代重点版本

读到Codd的论文后，Ellison团队基于IBM System R项目（首个实现SQL的数据库）的公开论文，开发了首个商用SQL数据库。
里程碑事件：

• 1977年：公司成立，原名SDL（Software Development Laboratories）。
• 1979年：发布Oracle V2（故意跳过V1避免用户顾虑）。
• 1983年：发布Oracle V3，首个实现ACID事务的商用数据库，解决数据一致性，进入金融/军工领域。
• 1988年：发布Oracle V6，引入PL/SQL、行级锁、集群，支持高并发，成为企业级解决方案。在那年还有一个大事件，Oracle V6因急于上市存在严重Bug，客户集体诉讼，公司濒临破产。
• 1992年：发布Oracle 7，优化器升级、分布式事务、存储过程，市场份额超IBM DB2和Sybase。
• 2013年：发布Oracle 12c，首创多租户架构。
• 2018年：发布自治数据库（Autonomous Database），AI自动优化/打补丁。
• 2020年：融合机器学习，全面转向云原生架构。

为何叫“Oracle”？
源自Ellison参与过的CIA项目代号 Oracle（意为“神谕”），象征其能解答数据问题。

为什么Oracle能快速占领市场？
首先在设计上具有前瞻性，在1980年代时，代码就支持跨平台了，并且在1997年，Oracle 8首次支持对象关系模型（存储图像/视频）。

附录：Oracle年表

二、重点语法

2.1 SQL的本质：声明式编程范式

核心理念：告诉数据库"要什么"，而不是"如何获取"。

-- 对比过程式语言（如Java）  
for (Employee e : employees) {  // 遍历所有员工  if (e.salary > 10000) {     // 检查条件  results.add(e);         // 收集结果  }  
}  -- SQL的声明式写法  
SELECT * FROM employees WHERE salary > 10000;  -- 直接描述结果

这样的优势在于：执行优化器自动选择最优路径，屏蔽底层存储细节。

2.2 Oracle的SQL语法

类型	全称	功能	Oracle关键命令
DDL	Data Definition Language	定义数据结构	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	操作数据记录	INSERT, UPDATE, DELETE, MERGE
DQL	Data Query Language	查询数据	SELECT（含扩展子句）
DCL	Data Control Language	权限与事务控制	GRANT, REVOKE, COMMIT, ROLLBACK

DQL核心：SELECT语句解剖学

完整语法结构：

SELECT [DISTINCT] 列1, 列2, 聚合函数()   
FROM 表1   
[JOIN 表2 ON 条件]  
WHERE 行级过滤  
GROUP BY 分组列  
HAVING 组级过滤  
ORDER BY 排序列 [ASC|DESC]  
[OFFSET n ROWS FETCH NEXT m ROWS ONLY]; -- Oracle 12c+分页  

关键子句执行顺序（优化器实际处理流程）：

Oracle特色子句：

• WITH子句（CTE）：也称为公共表表达式，它允许在查询中定义临时命名结果集，提高SQL的可读性和性能‌。

WITH dept_stats AS (  SELECT department_id, AVG(salary) avg_sal  FROM employees GROUP BY department_id  
)  
SELECT * FROM dept_stats WHERE avg_sal > 10000;  

• MODEL子句：主要功能是‌在SQL中实现跨行引用和电子表格式计算‌，它通过将查询结果视为多维数组，使开发者能够像操作电子表格单元格一样引用和计算数据‌

各关键部分说明：
PARTITION BY‌：将数据分组，类似于GROUP BY，不同分区间的数据相互独立‌。
‌DIMENSION BY‌：定义模型的维度，相当于多维数组的索引‌。
‌MEASURES‌：定义度量列，即模型中要处理或计算的列‌。
‌RULES‌：定义计算规则，支持复杂表达式和跨行引用‌。

场景一：销售预测分析

SELECT prd_type_id, year, month, sales_amount 
FROM all_sales 
WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id=21
MODELPARTITION BY (prd_type_id)DIMENSION BY (month, year)MEASURES (amount sales_amount)RULES (sales_amount[1,2004] = sales_amount[1,2003],sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],sales_amount[3,2004] = ROUND(sales_amount[3,2003]*1.25,2))
ORDER BY prd_type_id, year, month;

此查询中：
按产品类型(prd_type_id)分区计算，以月份和年份为维度。
预测规则：2004年1月销量=2003年1月销量；2月销量=2003年2月+3月销量；3月销量=2003年3月销量的1.25倍‌。

场景二：库存计算模型
计算每周库存的经典电子表格模型：

SELECT product, country, year, week, inventory, sale, receipts
FROM sales_fact
WHERE country='Australia' AND product='Xtend Memory'
MODELPARTITION BY (product, country)DIMENSION BY (year, week)MEASURES (0 inventory, sale, receipts)RULES AUTOMATIC ORDER (inventory[year, week] = NVL(inventory[cv(year), cv(week)-1], 0)- sale[cv(year), cv(week)]+ receipts[cv(year), cv(week)])
ORDER BY product, country, year, week;

此模型实现了库存计算公式：本周库存=上周库存-本周销售+本周进货‌。

关键字：
AUTOMATIC ORDER：数据库自动分析规则之间的依赖关系，根据逻辑依赖顺序确定执行顺序。系统会识别规则间的相互引用关系，确保被依赖的规则先执行‌。
SEQUENTIAL ORDER：严格按规则书写顺序执行，不考虑规则间的依赖关系。开发者需要自行确保规则的执行顺序正确‌。

DML进阶：Oracle的增强实现

批量操作优化

-- 批量插入（比单行INSERT快10倍）  
INSERT ALL  INTO employees VALUES (101, 'Alice', 9000)  INTO employees VALUES (102, 'Bob', 8500)  
SELECT * FROM DUAL;  -- 批量更新（关联更新）  
UPDATE (  SELECT e.salary, s.new_sal  FROM employees e  JOIN salary_updates s ON e.emp_id = s.emp_id  
) SET salary = new_sal;  

MERGE

Oracle MERGE语句(又称"upsert"操作)是一种强大的DML语句，允许在单条SQL中根据条件执行更新或插入操作。
MERGE语句的核心优势在于：

• 将UPDATE和INSERT操作合并为单个原子操作
• 只需一次全表扫描即可完成工作，效率高于分别执行INSERT和UPDATE
• 支持复杂的条件逻辑和批量数据处理
• 减少应用程序代码量和数据库往返次数‌

语法结构：

MERGE [INTO] [schema.]target_table [t_alias]
USING { [schema.]source_table | view | subquery } [s_alias]
ON (join_condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...][WHERE condition] [DELETE WHERE condition]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...])VALUES (value1 [, value2 ...])[WHERE condition]

‌关键子句说明‌：
‌INTO‌：指定目标表，数据将被更新或插入到此表
‌USING‌：指定数据源，可以是表、视图或子查询
‌ON‌：定义匹配条件，决定是执行UPDATE还是INSERT
‌WHEN MATCHED‌：当源数据与目标表匹配时执行UPDATE
‌WHEN NOT MATCHED‌：当源数据在目标表中无匹配时执行INSERT‌

场景一：数据同步

-- 将每日订单数据同步到月汇总表
MERGE INTO monthly_orders mo
USING (SELECT product_id, SUM(quantity) total_qty, SUM(amount) total_amtFROM daily_ordersWHERE order_date BETWEEN TRUNC(SYSDATE, 'MONTH') AND SYSDATEGROUP BY product_id
) do
ON (mo.product_id = do.product_id AND mo.month = TO_CHAR(SYSDATE, 'YYYY-MM'))
WHEN MATCHED THENUPDATE SET mo.quantity = do.total_qty,mo.amount = do.total_amt,mo.last_updated = SYSDATE
WHEN NOT MATCHED THENINSERT (product_id, month, quantity, amount, last_updated)VALUES (do.product_id, TO_CHAR(SYSDATE, 'YYYY-MM'), do.total_qty, do.total_amt, SYSDATE)

Oracle专属SQL特性

行号与分页

-- 传统ROWNUM分页（效率低）  
SELECT * FROM (  SELECT t.*, ROWNUM rn  FROM (SELECT * FROM employees ORDER BY hire_date) t  WHERE ROWNUM <= 20  
) WHERE rn > 10;  -- 现代分页（Oracle 12c+）  
SELECT * FROM employees  
ORDER BY hire_date  
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;  

参数说明‌：

• OFFSET N ROWS：跳过前N行
• FETCH NEXT M ROWS ONLY：获取接下来的M行‌

正则表达式支持

-- 提取邮箱用户名  
SELECT REGEXP_SUBSTR(email, '^([^@]+)@') AS user_name  
FROM contacts;  -- 验证电话号码格式  
SELECT phone FROM suppliers  
WHERE REGEXP_LIKE(phone, '^\(\d{3}\) \d{3}-\d{4}$');  

时间

Oracle时间数据类型：

Oracle提供了丰富的时间数据类型，满足不同精度和时区需求：

• DATE类型‌：
  • 存储日期和时间，精确到秒
  • 固定存储7字节
  • 格式：世纪、年、月、日、小时、分钟、秒
  • 默认显示格式为"DD-MON-YY"‌
• TIMESTAMP类型‌：
  • DATE的扩展，可存储小数秒(默认6位)
  • 不存储时区信息
  • 语法：TIMESTAMP[(scale)]，scale范围0-9‌
• TIMESTAMP WITH TIME ZONE‌：
  • 存储日期、时间和时区信息
  • 数据在不同时区查看时保持不变
  • 语法：TIMESTAMP[(scale)] WITH TIME ZONE‌
• TIMESTAMP WITH LOCAL TIME ZONE‌：
  • 将输入时间转换为数据库服务器时区存储
  • 查询时再转换为客户端时区显示
  • 不存储原始时区信息‌
• INTERVAL类型‌：
  • INTERVAL YEAR TO MONTH：存储年份和月份间隔
  • INTERVAL DAY TO SECOND：存储天、小时、分钟、秒间隔‌

核心日期时间函数：

1. 获取当前时间
   • SYSDATE：返回数据库服务器当前日期和时间(DATE类型)
   • CURRENT_DATE：返回当前会话时区的日期
   • SYSTIMESTAMP：返回带有时区信息的当前时间(TIMESTAMP WITH TIME ZONE)
   • CURRENT_TIMESTAMP：返回带有时区信息的当前会话时间‌
2. 日期计算函数
   • ADD_MONTHS(date, n)：日期加减月份
   • MONTHS_BETWEEN(date1, date2)：计算两个日期之间的月数差
   • LAST_DAY(date)：返回当月最后一天
   • NEXT_DAY(date, day)：返回下一个指定星期几的日期
   • ROUND(date[, format])：日期四舍五入
   • TRUNC(date[, format])：日期截断‌
3. 时区处理函数
   • DBTIMEZONE：返回数据库时区
   • SESSIONTIMEZONE：返回当前会话时区
   • TZ_OFFSET(timezone)：返回指定时区的偏移量
   • NEW_TIME(date, zone1, zone2)：时区转换‌
4. 提取函数
   • EXTRACT(field FROM datetime)：提取日期部分(年、月、日等)
   • TO_CHAR(date, format)：日期转字符串
   • TO_DATE(string, format)：字符串转日期
   • TO_TIMESTAMP(string, format)：字符串转TIMESTAMP‌

-- 精确日期查询
SELECT * FROM orders WHERE order_date = TO_DATE('2023-01-01', 'YYYY-MM-DD');-- 日期范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN TO_DATE('2023-01-01','YYYY-MM-DD') 
AND TO_DATE('2023-01-31','YYYY-MM-DD');-- 大于/小于比较
SELECT * FROM employees 
WHERE hire_date > TO_DATE('2023-01-01','YYYY-MM-DD');-- 使用TRUNC函数忽略时间部分
SELECT * FROM log_table 
WHERE TRUNC(log_time) = TO_DATE('2023-01-01','YYYY-MM-DD');-- 使用TO_CHAR格式化比较
SELECT * FROM log_table 
WHERE TO_CHAR(log_time, 'YYYY-MM-DD') = '2023-01-01';-- 获取服务器当前时间
SELECT SYSDATE FROM dual;-- 获取带时区的当前时间戳
SELECT SYSTIMESTAMP FROM dual;-- 获取会话当前日期
SELECT CURRENT_DATE FROM dual;-- 获取会话当前时间戳
SELECT CURRENT_TIMESTAMP FROM dual;-- 加减月份
SELECT ADD_MONTHS(SYSDATE, 3) AS three_months_later FROM dual;-- 计算两个日期之间的月数差
SELECT MONTHS_BETWEEN(TO_DATE('2023-12-31','YYYY-MM-DD'), SYSDATE) FROM dual;-- 获取当月最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;-- 获取下一个周一的日期
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;-- 使用EXTRACT提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year,EXTRACT(MONTH FROM SYSDATE) AS current_month,EXTRACT(DAY FROM SYSDATE) AS current_day
FROM dual;-- 日期格式化输出
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS datetime1,TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') AS datetime2,TO_CHAR(SYSDATE, 'DAY') AS weekday
FROM dual;-- 查看数据库和会话时区
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;-- 设置会话时区
ALTER SESSION SET TIME_ZONE = '+08:00';-- UTC时间转换为北京时间
SELECT FROM_TZ(CAST(TO_TIMESTAMP('2024-05-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Shanghai' AS beijing_time
FROM dual;-- 创建带时区的时间戳列
CREATE TABLE global_events (event_id NUMBER,event_name VARCHAR2(100),event_time TIMESTAMP WITH TIME ZONE
);-- 插入带时区的时间数据
INSERT INTO global_events VALUES (1, 'Product Launch', TO_TIMESTAMP_TZ('2024-01-15 09:00:00 America/New_York', 'YYYY-MM-DD HH24:MI:SS TZR')
);-- 查询并转换为本地时区显示
SELECT event_name,TO_CHAR(event_time AT TIME ZONE SESSIONTIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS local_time
FROM global_events;-- 查询最近30天数据
SELECT * FROM sales 
WHERE sale_date >= SYSDATE - 30;-- 查询本季度数据
SELECT * FROM sales 
WHERE sale_date BETWEEN TRUNC(SYSDATE, 'Q') 
AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 2));-- 查询上周数据
SELECT * FROM log 
WHERE log_time BETWEEN TRUNC(SYSDATE, 'IW')-7 
AND TRUNC(SYSDATE, 'IW')-1;-- 计算两个日期之间的工作日数
SELECT (TRUNC(TO_DATE('2023-12-31','YYYY-MM-DD'), 'D') - TRUNC(SYSDATE, 'D'))/7*5 +LEAST(TO_DATE('2023-12-31','YYYY-MM-DD') - TRUNC(TO_DATE('2023-12-31','YYYY-MM-DD'), 'D') + 1, 5) +GREATEST(TRUNC(SYSDATE, 'D') - SYSDATE + 6, 0) - 1 AS workdays
FROM dual;

SQL优化

1. 索引使用原则：
   • WHERE/JOIN条件列建索引
   • 避免对索引列使用函数

-- 错误：索引失效  
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';  -- 正确：函数索引  
CREATE INDEX emp_upper_name ON employees(UPPER(name));  

2. 集合思维代替循环：

-- 低效：逐行更新  
BEGIN  FOR rec IN (SELECT * FROM temp_updates) LOOP  UPDATE employees SET salary = rec.new_sal  WHERE emp_id = rec.emp_id;  END LOOP;  
END;  -- 高效：单语句更新  
MERGE INTO employees USING temp_updates ...  

3. 执行计划分析：

EXPLAIN PLAN FOR  
SELECT e.name, d.department_name  
FROM employees e  
JOIN departments d ON e.dept_id = d.dept_id;  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  

关键指标：
COST：优化器预估代价
ROWS：返回行数估计
ACCESS PATH：索引扫描(INDEX) vs 全表扫描(FULL)

SQL与PL/SQL的分工协作

两种语法的基本概念与定位差异：
SQL：
是一种标准化的数据库查询和操作语言，广泛应用于各类关系型数据库管理系统，主要目的是查询、更新、插入和删除数据，同时也包括创建和修改表、视图、索引等数据库对象，作为一种声明式语言，使用者只需表达想要完成的任务，而不必关心具体的执行细节‌。
‌PL/SQL：
是Oracle公司为其数据库产品开发的一种过程化编程语言，它是SQL的扩展版本，允许开发者将SQL命令与其他编程元素(如变量、常量、条件语句、循环、异常处理等)结合在一起，可以形成模块化的、可重复使用的代码单元，如存储过程、函数和触发器等‌。

场景	工具选择	示例
简单数据操作	纯SQL	UPDATE table SET col=val WHERE…
复杂业务逻辑	PL/SQL	存储过程/函数/触发器
混合处理	SQL嵌入PL/SQL	在PL/SQL中执行动态SQL

动态SQL示例：

DECLARE  sql_stmt VARCHAR2(200);  v_dept_id NUMBER := 60;  
BEGIN  sql_stmt := 'SELECT * FROM employees WHERE department_id = :dept';  EXECUTE IMMEDIATE sql_stmt USING v_dept_id;  
END;  

三、高级查询技巧

3.1 多表连接（JOIN）

核心连接类型全景图：

Oracle性能优化实践：

1. 驱动表选择原则：
   • 小表驱动大表（小表在FROM，大表在JOIN）
   • 高筛选率表优先

-- 优化前（大表驱动）
SELECT * FROM large_table l 
JOIN small_table s ON l.id = s.id;-- 优化后（小表驱动）
SELECT * FROM small_table s 
JOIN large_table l ON s.id = l.id;

2. 哈希连接 vs 嵌套循环：

哈希连接(Hash Join)工作原理：
哈希连接是一种基于哈希算法的连接方式，主要分为两个阶段：
‌构建阶段‌： 选择较小的表(内部表)作为构建表，对其连接列应用哈希函数构建内存中的哈希表‌。
‌探查阶段‌： 扫描较大的表(外部表)，对每行连接列计算哈希值，在哈希表中查找匹配项‌。
这种连接方式特别适合大数据集的等值连接操作，理论上只需扫描两表各一次即可完成连接‌。

嵌套循环(Nested Loops)工作原理：
嵌套循环采用双重循环结构：
‌外层循环‌： 遍历驱动表(通常选择较小的表)的每一行‌。
‌内层循环‌： 对于驱动表的每一行，遍历被驱动表查找匹配行‌。
这种连接方式在小数据集场景下响应迅速，尤其当被驱动表连接列有高效索引时性能更佳‌。

/*+ USE_HASH(employees departments) */ -- 强制哈希连接（适合大数据）
SELECT e.name, d.dname 
FROM employees e 
JOIN departments d ON e.dept_id = d.dept_id;/*+ LEADING(small) USE_NL(large) */ -- 嵌套循环（小数据集）

4. 分区智能连接

-- 分区表创建
CREATE TABLE sales PARTITION BY RANGE (sale_date) (PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),PARTITION p2024 VALUES LESS THAN (MAXVALUE)
);-- 自动分区连接
SELECT * FROM sales s 
JOIN products p ON s.product_id = p.id 
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 只扫描p2023分区

3.2 子查询

子查询类型性能对比：

类型	执行方式	适用场景
标量子查询	外层每行执行一次	SELECT列表中的计算
行内视图	物化为临时表	复杂中间结果
关联子查询	嵌套循环	需要引用外层数据的过滤
EXISTS子查询	找到即停止（半连接）	存在性检查

实战改写技巧：

-- 原始：低效的关联子查询
SELECT employee_id, salary
FROM employees outer
WHERE salary > (SELECT AVG(salary) FROM employees inner WHERE inner.department_id = outer.department_id
);-- 优化：CTE+JOIN
WITH dept_avg AS (SELECT department_id, AVG(salary) avg_salFROM employeesGROUP BY department_id
)
SELECT e.employee_id, e.salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

Oracle特殊子查询优化

反连接（Anti-Join）优化：

-- NOT IN 自动转为反连接
SELECT * FROM orders 
WHERE customer_id NOT IN (SELECT customer_id FROM vip_customers
);-- 执行计划：HASH JOIN ANTI

WITH子句物化提示：
物化机制‌：
CTE在部分数据库中会被物化为临时结果集，后续引用直接使用缓存‌。

WITH /*+ MATERIALIZE */ reg_sales AS (SELECT region, SUM(sales) total FROM orders GROUP BY region
)
SELECT * FROM reg_sales WHERE total > 1000000;

集合运算

集合操作性能基准测试（百万级数据）：

操作	执行时间	临时空间	推荐场景
UNION	13.3s	1.5GB	需要去重的结果合并
UNION ALL	0.7s	0GB	快速合并（首选）
INTERSECT	10.7s	855MB	找共同项
MINUS	9.2s	720MB	排除特定数据

高级集合技巧：
多维度联合：

-- 按时间维度合并报表
SELECT 'Q1' period, product, sales FROM q1_sales
UNION ALL
SELECT 'Q2' period, product, sales FROM q2_sales
UNION ALL
SELECT 'Q3' period, product, sales FROM q3_sales

层次化聚合：

-- 使用GROUPING SETS替代UNION聚合
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id),  -- 部门+职位组合(department_id),           -- 仅部门()                         -- 总计
);

高级透视：Oracle分析函数

核心概念：
Oracle分析函数是一类特殊的SQL函数，用于在查询结果的"窗口"内执行计算(如排名、累计求和、移动平均等)，不会聚合结果行，而是为每一行返回一个计算结果。它们通常与OVER()子句结合使用，是处理复杂分析需求(如分组排名、累计统计等)的高效工具‌

基本语法结构：
所有分析函数都遵循一个核心的OVER()子句结构，它定义了窗口：

function_name([arguments]) OVER ([PARTITION BY partition_expression,...][ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|NULLS LAST],...][windowing_clause]
)

• PARTITION BY‌：分区子句。将数据集逻辑上分割成多个独立的组(分区)，窗口函数在每个分区内部独立计算。若省略，整个结果集被视为单个分区‌。
• ORDER BY‌：排序子句。定义分区内各行的处理顺序。对于排名和位置函数，此子句至关重要‌。
• windowing_clause‌：窗口范围子句。更精确地定义计算窗口的边界(例如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示当前行、前一行和后一行)。如果省略(但有ORDER BY)，默认通常是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW‌。

五大核心函数族：

主要分析函数：

• ROW_NUMBER()‌：为窗口内的每一行分配一个从1开始的唯一且连续的排名。即使行具有相同的值，排名也不会重复‌。
• RANK()‌：计算排序后的排名，相同值排名相同，排名之间可能有间隔‌。
• ‌DENSE_RANK()‌：计算排序后的排名，相同值排名相同，但排名之间没有间隔‌。
• NTILE(n)‌：将结果集分成指定数量的组，并为每一行分配组编号‌。
• FIRST_VALUE()/LAST_VALUE()‌：返回窗口内第一行/最后一行的值‌。
• LAG()/LEAD()‌：访问当前行之前/之后指定偏移量的行‌。

场景一：滞后和领先分析
使用LAG和LEAD函数可以比较一个时间点的值与之前和之后的值，对于跟踪股票价格的动态变化尤其重要‌。

SELECT time_id, TO_CHAR(SUM(amount_sold), '99,999,999') AS Daily_Sales,TO_CHAR(LAG(SUM(amount_sold),1) OVER (ORDER BY time_id), '99,999,999') AS Lag_Indicator,TO_CHAR(LEAD(SUM(amount_sold),1) OVER (ORDER BY time_id), '99,999,999') AS Lead_Indicator
FROM sales
WHERE time_id BETWEEN TO_DATE('01-JAN-2025') AND TO_DATE('15-JAN-2025')
GROUP BY time_id;

场景二：移动平均计算
分析函数可以轻松实现各种时间窗口的移动平均计算，这是金融分析中的常见需求‌。

SELECT stock_code, trade_date, closing_price,AVG(closing_price) OVER(PARTITION BY stock_code ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5,AVG(closing_price) OVER(PARTITION BY stock_code ORDER BY trade_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS ma20
FROM stock_daily
WHERE stock_code = '600000';

模式匹配：MATCH_RECOGNIZE（Oracle 12c+）

核心概念：
MATCH_RECOGNIZE是Oracle 12c引入的强大模式匹配功能，主要用于在数据流中识别复杂的事件模式，特别适合时间序列分析、异常检测等场景‌。
基本语法框架：

SELECT [column_list]
FROM table_name
MATCH_RECOGNIZE ([PARTITION BY partition_columns][ORDER BY order_columns][MEASURES measure_expressions][ONE ROW PER MATCH | ALL ROWS PER MATCH][AFTER MATCH SKIP TO next_pattern][PATTERN (pattern_expression)][DEFINE define_expressions]
)

关键子句详解：

• PARTITION BY‌：将数据逻辑分区，模式匹配在每个分区内独立进行‌。
• ORDER BY‌：定义分区内行的处理顺序，对模式匹配至关重要‌。
• MEASURES‌：定义输出列，可访问模式变量和匹配信息‌。
• PATTERN‌：使用正则表达式语法定义要查找的模式序列‌。
• DEFINE‌：为模式变量指定条件，决定哪些行属于哪个变量‌。
• AFTER MATCH SKIP‌：控制匹配后从何处开始下一次匹配‌。

场景：‌股票价格V形走势识别‌

SELECT *
FROM stock_prices
MATCH_RECOGNIZE (PARTITION BY symbolORDER BY trade_dateMEASURES STRT.trade_date AS start_date,DOWN.trade_date AS bottom_date,UP.trade_date AS end_date,MATCH_NUMBER() AS match_numONE ROW PER MATCHAFTER MATCH SKIP TO LAST UPPATTERN (STRT DOWN+ UP+)DEFINEDOWN AS DOWN.price < PREV(DOWN.price),UP AS UP.price > PREV(UP.price)
)

此查询识别股票价格先下跌后回升的V形走势，输出每个V形模式的起止日期‌。

• 数据分区与排序：
  • PARTITION BY symbol：按股票代码分组，确保每只股票的模式识别独立进行‌。
  • ORDER BY trade_date：按交易日期排序，保证时间序列的正确性‌。
• 模式定义(PATTERN子句)：
  • PATTERN (STRT DOWN+ UP+) 定义了要查找的价格模式：
  • STRT：模式起始点(未定义条件，匹配任意行)。
  • DOWN+：一个或多个连续下跌日。
  • UP+：一个或多个连续上涨日。

这种模式对应金融技术分析中的"V形底"形态，特点是价格快速下跌后迅速回升，形成V字形的走势‌。

• 条件定义(DEFINE子句)：
  • DOWN AS DOWN.price < PREV(DOWN.price)：定义下跌日为价格低于前一交易日。
  • UP AS UP.price > PREV(UP.price)：定义上涨日为价格高于前一交易日。
    这些条件确保价格走势符合严格的下降和上升趋势‌。
• 输出指标(MEASURES子句)：
  • STRT.trade_date AS start_date：模式开始日期。
  • DOWN.trade_date AS bottom_date：底部日期(最后一个下跌日)。
  • UP.trade_date AS end_date：模式结束日期(最后一个上涨日)。
  • MATCH_NUMBER() AS match_num：匹配序号。

这些输出指标完整描述每个V形反转的关键时间点‌。

• 匹配后行为：AFTER MATCH SKIP TO LAST UP表示在找到匹配后，从最后一个UP行开始下一次匹配，避免重叠模式并确保连续识别不同的V形反转‌。

查询执行流程：

1. 按股票代码分组，每组内按交易日期排序
2. 从起始行开始，寻找符合DOWN定义(价格连续下跌)的行序列
3. 在下跌序列后，寻找符合UP定义(价格连续上涨)的行序列
4. 当找到完整的STRT→DOWN+→UP+模式时，输出该模式的起止日期和底部日期
5. 从最后一个UP行开始，继续搜索下一个模式
6. 为每个成功匹配的模式分配一个递增的匹配编号

3.3 性能调优

1. 执行计划分析：

EXPLAIN PLAN FOR
SELECT /*+ YOUR_HINTS_HERE */ ...;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 实时监控：

-- 查看运行中SQL性能
SELECT sql_id, elapsed_time/1000000 sec, cpu_time, executions
FROM v$sql
WHERE sql_text LIKE '%employees%';

核心组件：
数据来源 - v $ sql视图
v $ sql是Oracle的关键动态性能视图，记录共享SQL区中所有SQL语句的执行统计信息，包含以下重要字段：
sql_id：SQL语句的唯一标识符。
sql_text：SQL语句的前1000个字符。
elapsed_time：SQL执行总耗时(微秒)。
cpu_time：纯CPU处理时间(微秒)。
executions：SQL语句的执行次数。

关键指标评价标准：
‌单次执行时间(elapsed_time/executions)‌：
<0.1秒：优秀
0.1-1秒：可接受
1-5秒：需要关注
5秒：严重性能问题
‌CPU利用率(cpu_time/elapsed_time)‌：
高比率(>80%)：CPU密集型操作
低比率(<50%)：存在等待瓶颈
执行频率：
高频执行SQL对系统影响更大，即使单次耗时短

3. 资源控制：

-- 创建资源计划
BEGINDBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => 'HR_QUERY_PLAN',comment => 'Limit HR report resources');
END;

四、事务控制

4.1 事务的本质：ACID四重保障

Oracle通过以下机制实现事务的原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)：

特性	Oracle实现机制	技术细节
原子性	Undo Segments（撤销段）	修改前的数据镜像保存在UNDO表空间
一致性	约束+触发器	事务执行中检查约束，违反则回滚
隔离性	多版本并发控制(MVCC)	基于SCN（系统变更号）的快照读
持久性	Redo Log（重做日志）	先写日志后修改数据，确保故障可恢复

关键概念：
SCN（System Change Number）：Oracle的全局时间戳，每次提交递增
快照过旧（Snapshot Too Old）：UNDO空间不足导致的经典错误

4.2 事务控制基础命令

1. 事务生命周期管理

-- 开启隐式事务（Oracle默认自动开始）  
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';  -- 显式提交  
COMMIT;  -- 确认所有修改，释放锁  -- 显式回滚  
ROLLBACK; -- 撤销所有未提交修改  -- 保存点控制  
SAVEPOINT before_transfer;  -- 创建还原点  
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';  
ROLLBACK TO before_transfer; -- 回滚到指定保存点  

2. 事务状态检测

-- 查看当前事务状态  
SELECT xid, status FROM v$transaction;  /* 输出示例：  XID              STATUS  
---------------    ---------  0A000B00C        ACTIVE  
*/  

4.3 Oracle隔离级别

Oracle默认采用读已提交(READ COMMITTED)，支持更高级别：

隔离级别	脏读	不可重复读	幻读	Oracle实现
READ COMMITTED	不可能	可能	可能	查询SCN时刻的快照
SERIALIZABLE	不可能	不可能	不可能	事务开始时SCN冻结
READ ONLY	不可能	不可能	不可能	强制只读模式

设置隔离级别：

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;  

MVCC工作流程：

---

总结

通过本文，我们掌握了Oracle的核心理论及DDL/DML操作、高级查询技巧。下一步建议：

• 在Oracle Live SQL（免费在线环境）练习代码。
• 探索索引优化、执行计划调优。

---

附录：学习资源：

• Oracle官方文档