SQL高级语法与函数详解（Oracle/PostgreSQL/MySQL）

一、窗口函数（Window Functions）

窗口函数用于对结果集的子集（窗口）进行计算，保留原始行的同时生成聚合或排序结果。

1. 核心语法对比

2. 常用窗口函数及用途

示例：部门内薪资累计和

-- 三库通用
SELECT
  emp_id,
  dept_id,
  salary,
  SUM(salary) OVER (PARTITIONBY dept_id ORDERBY emp_id) AS cumulative_sum
FROM employees;

差异总结：

二、递归查询（Recursive CTE）

递归查询用于处理树形或层级数据（如组织结构、分类树）。

1. 语法对比

示例：查询员工层级关系

-- Oracle
WITH org_tree AS (
SELECT emp_id, name, 1AS level FROM employees WHERE manager_id ISNULL
UNIONALL
SELECT e.emp_id, e.name, ot.level +1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.emp_id
)
SELECT*FROM org_tree;

-- PostgreSQL/MySQL（需添加 RECURSIVE 关键字）
WITHRECURSIVE org_tree AS (
SELECT emp_id, name, 1AS level FROM employees WHERE manager_id ISNULL
UNIONALL
SELECT e.emp_id, e.name, ot.level +1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.emp_id
)
SELECT*FROM org_tree;

差异总结：

三、存储过程（Stored Procedure）

存储过程用于封装复杂业务逻辑，支持事务控制与异常处理。

1. 语法对比

示例：插入员工并返回 ID

-- Oracle（需创建序列）
CREATE SEQUENCE employees_seq STARTWITH100;
CREATEOR REPLACE PROCEDURE add_employee (
  p_name IN VARCHAR2,
  p_dept_id IN NUMBER,
  p_emp_id OUT NUMBER
) AS
BEGIN
INSERTINTO employees (emp_id, name, dept_id)
VALUES (employees_seq.NEXTVAL, p_name, p_dept_id)
  RETURNING emp_id INTO p_emp_id;
END;
/

-- PostgreSQL（使用 SERIAL 自增）
CREATEOR REPLACE FUNCTION add_employee(
  p_name VARCHAR,
  p_dept_id INT,
OUT p_emp_id INT
) LANGUAGE plpgsql AS $$
BEGIN
INSERTINTO employees (name, dept_id)
VALUES (p_name, p_dept_id)
  RETURNING emp_id INTO p_emp_id;
END;
$$;

-- MySQL（使用 AUTO_INCREMENT）
DELIMITER $$
CREATEPROCEDURE add_employee(
IN p_name VARCHAR(50),
IN p_dept_id INT,
OUT p_emp_id INT
)
BEGIN
INSERTINTO employees (name, dept_id) VALUES (p_name, p_dept_id);
SET p_emp_id = LAST_INSERT_ID();
END$$
DELIMITER ;

差异总结：

四、执行计划分析（Execution Plan）

执行计划用于分析 SQL 语句的查询路径与性能瓶颈。

1. 查看方法对比

示例：分析索引使用情况

-- Oracle
EXPLAIN PLAN FORSELECT*FROM employees WHERE dept_id =10;
SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT*FROM employees WHERE dept_id =10;

-- MySQL
EXPLAIN FORMAT=JSON SELECT*FROM employees WHERE dept_id =10;

优化建议：

• 若发现全表扫描（FULL TABLE SCAN或Seq Scan），可为dept_id字段添加索引。
• PostgreSQL 的ANALYZE选项会实际执行 SQL 并统计时间，适合精确分析。

五、扩展功能实战

1. Oracle Data Guard（高可用灾备）

-- 主库启用归档模式
ALTER DATABASE ARCHIVELOG;
-- 配置备库同步
ALTERSYSTEMSET LOG_ARCHIVE_DEST_2='SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';

作用：主库数据实时同步到备库，支持故障自动切换。

2. PostgreSQL PostGIS（地理数据处理）

-- 计算两个坐标距离
SELECT ST_Distance(
  ST_GeogFromText('POINT(116.4074 39.9042)'),
  ST_GeogFromText('POINT(121.4737 31.2304)')
) AS distance;  -- 单位：米

优势：支持复杂空间计算（如相交判断、缓冲区分析）。

3. MySQL 分区表（性能优化）

-- 按时间分区
CREATETABLE sales (
  id INT AUTO_INCREMENT,
  sale_date DATE,
  amount DECIMAL(10,2),
PRIMARY KEY (id, sale_date)
) PARTITIONBYRANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022)
);

适用场景：时间序列数据（如日志、交易记录），支持快速删除旧分区。

六、核心差异总结

以上关于SQL高级语法与函数详解（Oracle/PostgreSQL/MySQL）的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。