全面掌握Oracle数据库查询语言从基础语法到高级优化技巧解决企业复杂数据查询与分析提升工作效率的实用指南

威震华夏关云长

1. Oracle数据库查询语言概述

Oracle数据库作为企业级数据库管理系统的领导者，其查询语言SQL（Structured Query Language）是数据管理与分析的核心工具。掌握Oracle SQL不仅可以高效地检索和操作数据，还能通过高级技巧解决复杂的业务问题，显著提升工作效率。

Oracle SQL在标准SQL的基础上进行了扩展，提供了丰富的功能，包括强大的分析函数、层次查询、高级聚合操作等。这些功能使Oracle SQL成为处理企业级复杂数据查询与分析的理想选择。

2. Oracle SQL基础语法

2.1 基本SELECT语句

SELECT语句是SQL中最基本也是最常用的语句，用于从数据库中检索数据。

2. -- 基本语法
3. SELECT column1, column2, ...
4. FROM table_name
5. [WHERE condition]
6. [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...];
8. -- 示例：查询员工表中的员工ID、姓名和薪资
9. SELECT employee_id, first_name, last_name, salary
10. FROM employees
11. WHERE department_id = 10
12. ORDER BY salary DESC;

复制代码

2.2 数据过滤与条件查询

WHERE子句用于过滤记录，只返回满足特定条件的记录。

2. -- 使用比较运算符
3. SELECT * FROM employees
4. WHERE salary > 5000;
6. -- 使用逻辑运算符
7. SELECT * FROM employees
8. WHERE department_id = 10 OR department_id = 20;
10. -- 使用BETWEEN运算符
11. SELECT * FROM employees
12. WHERE salary BETWEEN 3000 AND 6000;
14. -- 使用IN运算符
15. SELECT * FROM employees
16. WHERE department_id IN (10, 20, 30);
18. -- 使用LIKE运算符进行模糊匹配
19. SELECT * FROM employees
20. WHERE last_name LIKE 'S%';
22. -- 使用IS NULL检查空值
23. SELECT * FROM employees
24. WHERE commission_pct IS NULL;

复制代码

2.3 聚合函数与分组

聚合函数对一组值执行计算并返回单个值，常与GROUP BY子句一起使用。

2. -- 常用聚合函数
3. SELECT COUNT(*) AS total_employees,
4.        AVG(salary) AS avg_salary,
5.        MAX(salary) AS max_salary,
6.        MIN(salary) AS min_salary,
7.        SUM(salary) AS total_salary
8. FROM employees;
10. -- 使用GROUP BY进行分组
11. SELECT department_id,
12.        COUNT(*) AS employee_count,
13.        AVG(salary) AS avg_salary
14. FROM employees
15. GROUP BY department_id;
17. -- 使用HAVING过滤分组结果
18. SELECT department_id,
19.        COUNT(*) AS employee_count,
20.        AVG(salary) AS avg_salary
21. FROM employees
22. GROUP BY department_id
23. HAVING COUNT(*) > 5;

复制代码

2.4 多表连接查询

连接查询用于从多个表中检索相关数据。

2. -- 内连接（INNER JOIN）
3. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
4. FROM employees e
5. INNER JOIN departments d ON e.department_id = d.department_id;
7. -- 左连接（LEFT JOIN）
8. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
9. FROM employees e
10. LEFT JOIN departments d ON e.department_id = d.department_id;
12. -- 右连接（RIGHT JOIN）
13. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
14. FROM employees e
15. RIGHT JOIN departments d ON e.department_id = d.department_id;
17. -- 全连接（FULL JOIN）
18. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
19. FROM employees e
20. FULL JOIN departments d ON e.department_id = d.department_id;
22. -- 自连接
23. SELECT e1.employee_id, e1.first_name, e1.last_name,
24.        e2.first_name AS manager_name, e2.last_name AS manager_last_name
25. FROM employees e1
26. LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

复制代码

3. 中级查询技术

3.1 子查询

子查询是嵌套在其他SQL语句中的SELECT语句，可以用于WHERE子句、FROM子句或SELECT列表中。

2. -- 在WHERE子句中使用子查询
3. SELECT employee_id, first_name, last_name, salary
4. FROM employees
5. WHERE salary > (SELECT AVG(salary) FROM employees);
7. -- 在FROM子句中使用子查询（内联视图）
8. SELECT d.department_name, avg_sal.avg_salary
9. FROM departments d
10. JOIN (SELECT department_id, AVG(salary) AS avg_salary
11.       FROM employees
12.       GROUP BY department_id) avg_sal
13. ON d.department_id = avg_sal.department_id;
15. -- 在SELECT列表中使用子查询
16. SELECT e.employee_id, e.first_name, e.last_name,
17.        (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name
18. FROM employees e;
20. -- 使用EXISTS和NOT EXISTS
21. SELECT d.department_id, d.department_name
22. FROM departments d
23. WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);
25. SELECT d.department_id, d.department_name
26. FROM departments d
27. WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

复制代码

3.2 集合操作

Oracle提供了三种主要的集合操作：UNION、INTERSECT和MINUS。

2. -- UNION：合并两个查询结果，去除重复行
3. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
4. UNION
5. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;
7. -- UNION ALL：合并两个查询结果，保留所有行，包括重复行
8. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
9. UNION ALL
10. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;
12. -- INTERSECT：返回两个查询结果的交集
13. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
14. INTERSECT
15. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;
17. -- MINUS：返回在第一个查询结果中但不在第二个查询结果中的行
18. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
19. MINUS
20. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;

复制代码

3.3 层次查询

层次查询用于处理具有层次结构的数据，如组织结构、产品分类等。

2. -- 基本层次查询语法
3. SELECT [LEVEL], column, expr...
4. FROM table_name
5. [WHERE condition]
6. START WITH condition
7. CONNECT BY [NOCYCLE] condition;
9. -- 示例：查询员工组织结构
10. SELECT LEVEL,
11.        LPAD(' ', 2*(LEVEL-1)) || employee_id AS emp_id,
12.        first_name || ' ' || last_name AS employee_name,
13.        manager_id
14. FROM employees
15. START WITH manager_id IS NULL
16. CONNECT BY PRIOR employee_id = manager_id;
18. -- 使用CONNECT_BY_ROOT查找根节点
19. SELECT CONNECT_BY_ROOT employee_id AS root_manager_id,
20.        employee_id, first_name, last_name
21. FROM employees
22. WHERE LEVEL > 1
23. START WITH manager_id IS NULL
24. CONNECT BY PRIOR employee_id = manager_id;
26. -- 使用CONNECT_BY_ISLEAF识别叶子节点
27. SELECT employee_id, first_name, last_name,
28.        CONNECT_BY_ISLEAF AS is_leaf
29. FROM employees
30. START WITH manager_id IS NULL
31. CONNECT BY PRIOR employee_id = manager_id;

复制代码

3.4 分析函数（Analytic Functions）

分析函数是Oracle SQL的强大功能，允许在结果集上执行复杂计算，同时保持原始行的完整性。

2. -- ROW_NUMBER()：为结果集中的每一行分配一个唯一的序号
3. SELECT employee_id, first_name, last_name, department_id, salary,
4.        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
5. FROM employees;
7. -- RANK()和DENSE_RANK()：为结果集中的行分配排名
8. SELECT employee_id, first_name, last_name, department_id, salary,
9.        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
10.        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
11. FROM employees;
13. -- LEAD()和LAG()：访问结果集中后续行或前导行的数据
14. SELECT employee_id, first_name, last_name, salary,
15.        LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
16.        LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
17. FROM employees;
19. -- 窗口函数：计算移动平均、累计总和等
20. SELECT employee_id, first_name, last_name, hire_date, salary,
21.        SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary,
22.        AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
23. FROM employees;

复制代码

4. 高级查询技巧

4.1 高级连接技术

2. -- 使用USING子句简化连接条件
3. SELECT employee_id, first_name, last_name, department_name
4. FROM employees
5. JOIN departments USING (department_id);
7. -- 自然连接（NATURAL JOIN）：自动匹配两个表中具有相同名称和数据类型的列
8. SELECT employee_id, first_name, last_name, department_name
9. FROM employees
10. NATURAL JOIN departments;
12. -- 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积
13. SELECT e.first_name, e.last_name, d.department_name
14. FROM employees e
15. CROSS JOIN departments d;
17. -- 分区外连接（Partitioned Outer Join）：处理稀疏数据
18. SELECT d.department_id, d.department_name, t.time_id, t.sales_amount
19. FROM departments d
20. LEFT OUTER JOIN sales t PARTITION BY (d.department_id)
21. ON d.department_id = t.department_id
22. ORDER BY d.department_id, t.time_id;

复制代码

4.2 递归子查询

Oracle 11g引入了递归子查询，使用WITH子句实现层次查询。

2. -- 使用WITH子句实现递归查询
3. WITH emp_hierarchy (employee_id, first_name, last_name, manager_id, level, path) AS (
4.   -- 基础查询
5.   SELECT employee_id, first_name, last_name, manager_id, 1 AS level,
6.          first_name || ' ' || last_name AS path
7.   FROM employees
8.   WHERE manager_id IS NULL
9.   
10.   UNION ALL
11.   
12.   -- 递归查询
13.   SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, h.level + 1,
14.          h.path || ' -> ' || e.first_name || ' ' || e.last_name
15.   FROM employees e
16.   JOIN emp_hierarchy h ON e.manager_id = h.employee_id
17. )
18. SELECT employee_id, first_name, last_name, LPAD(' ', 2*(level-1)) || path AS org_path
19. FROM emp_hierarchy
20. ORDER BY level, path;

复制代码

4.3 PIVOT和UNPIVOT操作

PIVOT和UNPIVOT是Oracle 11g引入的功能，用于数据透视和逆透视操作。

2. -- PIVOT：将行转换为列
3. SELECT *
4. FROM (
5.   SELECT department_id, job_id, salary
6.   FROM employees
7. )
8. PIVOT (
9.   AVG(salary) AS avg_salary
10.   FOR job_id IN ('AC_ACCOUNT' AS AC_ACCOUNT,
11.                  'AC_MGR' AS AC_MGR,
12.                  'AD_ASST' AS AD_ASST,
13.                  'AD_PRES' AS AD_PRES,
14.                  'AD_VP' AS AD_VP,
15.                  'FI_ACCOUNT' AS FI_ACCOUNT)
16. )
17. ORDER BY department_id;
19. -- UNPIVOT：将列转换为行
20. WITH dept_salaries AS (
21.   SELECT department_id,
22.          SUM(CASE WHEN job_id = 'AC_ACCOUNT' THEN salary ELSE 0 END) AS AC_ACCOUNT,
23.          SUM(CASE WHEN job_id = 'AC_MGR' THEN salary ELSE 0 END) AS AC_MGR,
24.          SUM(CASE WHEN job_id = 'AD_ASST' THEN salary ELSE 0 END) AS AD_ASST,
25.          SUM(CASE WHEN job_id = 'AD_PRES' THEN salary ELSE 0 END) AS AD_PRES,
26.          SUM(CASE WHEN job_id = 'AD_VP' THEN salary ELSE 0 END) AS AD_VP,
27.          SUM(CASE WHEN job_id = 'FI_ACCOUNT' THEN salary ELSE 0 END) AS FI_ACCOUNT
28.   FROM employees
29.   GROUP BY department_id
30. )
31. SELECT department_id, job_id, total_salary
32. FROM dept_salaries
33. UNPIVOT (
34.   total_salary FOR job_id IN (AC_ACCOUNT, AC_MGR, AD_ASST, AD_PRES, AD_VP, FI_ACCOUNT)
35. )
36. ORDER BY department_id, job_id;

复制代码

4.4 模式匹配（MATCH_RECOGNIZE）

Oracle 12c引入了MATCH_RECOGNIZE子句，用于在有序数据中识别模式。

2. -- 示例：识别股票价格连续3天上涨的模式
3. SELECT *
4. FROM stock_prices
5. MATCH_RECOGNIZE (
6.   PARTITION BY symbol
7.   ORDER BY trade_date
8.   MEASURES
9.     FIRST(trade_date) AS start_date,
10.     LAST(trade_date) AS end_date,
11.     COUNT(*) AS days_up
12.   ONE ROW PER MATCH
13.   AFTER MATCH SKIP PAST LAST ROW
14.   PATTERN (STRICT_UP+)
15.   DEFINE
16.     STRICT_UP AS price > PREV(price)
17. );

复制代码

5. 查询优化方法

5.1 执行计划分析

执行计划是Oracle优化器为SQL语句生成的操作步骤，分析执行计划是优化查询的关键。

2. -- 使用EXPLAIN PLAN生成执行计划
3. EXPLAIN PLAN FOR
4. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
5. FROM employees e
6. JOIN departments d ON e.department_id = d.department_id
7. WHERE e.salary > 5000;
9. -- 查看执行计划
10. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
12. -- 使用AUTOTRACE查看执行计划和统计信息
13. SET AUTOTRACE ON EXPLAIN STATISTICS
14. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
15. FROM employees e
16. JOIN departments d ON e.department_id = d.department_id
17. WHERE e.salary > 5000;
18. SET AUTOTRACE OFF

复制代码

5.2 索引优化

索引是提高查询性能的重要工具，合理使用索引可以显著减少查询时间。

2. -- 创建B树索引
3. CREATE INDEX idx_emp_salary ON employees(salary);
5. -- 创建复合索引
6. CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary);
8. -- 创建函数索引
9. CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
11. -- 创建位图索引（适用于低基数列）
12. CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
14. -- 监控索引使用情况
15. ALTER INDEX idx_emp_salary MONITORING USAGE;
16. SELECT * FROM V$OBJECT_USAGE;
17. ALTER INDEX idx_emp_salary NOMONITORING USAGE;
19. -- 重建索引以提高性能
20. ALTER INDEX idx_emp_salary REBUILD;

复制代码

5.3 查询重写技巧

查询重写是通过改变SQL语句的结构而不改变其语义来提高性能的方法。

2. -- 使用EXISTS替代IN
3. -- 原始查询
4. SELECT * FROM employees
5. WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
7. -- 重写后的查询
8. SELECT * FROM employees e
9. WHERE EXISTS (SELECT 1 FROM departments d
10.               WHERE d.department_id = e.department_id AND d.location_id = 1700);
12. -- 使用JOIN替代子查询
13. -- 原始查询
14. SELECT e.employee_id, e.first_name, e.last_name,
15.        (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name
16. FROM employees e;
18. -- 重写后的查询
19. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
20. FROM employees e
21. JOIN departments d ON e.department_id = d.department_id;
23. -- 使用UNION ALL替代UNION（如果确定没有重复行）
24. -- 原始查询
25. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
26. UNION
27. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;
29. -- 重写后的查询（如果确定两个结果集没有重复行）
30. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
31. UNION ALL
32. SELECT employee_id, first_name, last_name FROM employees WHERE salary > 5000;

复制代码

5.4 统计信息收集

统计信息是Oracle优化器做出正确决策的基础，定期收集统计信息对查询性能至关重要。

2. -- 收集表统计信息
3. EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
5. -- 收集索引统计信息
6. EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_SALARY');
8. -- 收集模式统计信息
9. EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
11. -- 收集数据库统计信息
12. EXEC DBMS_STATS.GATHER_DATABASE_STATS;
14. -- 查看表的统计信息
15. SELECT table_name, num_rows, blocks, avg_row_len
16. FROM user_tables
17. WHERE table_name = 'EMPLOYEES';
19. -- 查看列的统计信息
20. SELECT table_name, column_name, num_distinct, density, num_nulls
21. FROM user_tab_columns
22. WHERE table_name = 'EMPLOYEES';

复制代码

6. 实际企业应用案例

6.1 销售数据分析

2. -- 月度销售趋势分析
3. SELECT
4.   TO_CHAR(order_date, 'YYYY-MM') AS month,
5.   COUNT(*) AS order_count,
6.   SUM(order_total) AS total_sales,
7.   AVG(order_total) AS avg_order_value,
8.   SUM(CASE WHEN status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_orders,
9.   SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_orders
10. FROM orders
11. WHERE order_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11) AND TRUNC(SYSDATE, 'MM')
12. GROUP BY TO_CHAR(order_date, 'YYYY-MM')
13. ORDER BY month;
15. -- 产品类别销售排名
16. SELECT
17.   p.category_id,
18.   c.category_name,
19.   COUNT(DISTINCT o.order_id) AS order_count,
20.   SUM(oi.quantity * oi.unit_price) AS total_sales,
21.   RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS sales_rank
22. FROM order_items oi
23. JOIN products p ON oi.product_id = p.product_id
24. JOIN categories c ON p.category_id = c.category_id
25. JOIN orders o ON oi.order_id = o.order_id
26. WHERE o.order_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11) AND TRUNC(SYSDATE, 'MM')
27. GROUP BY p.category_id, c.category_name
28. ORDER BY total_sales DESC;
30. -- 客户购买行为分析
31. SELECT
32.   customer_id,
33.   COUNT(DISTINCT order_id) AS order_count,
34.   SUM(order_total) AS total_spent,
35.   AVG(order_total) AS avg_order_value,
36.   MIN(order_date) AS first_order_date,
37.   MAX(order_date) AS last_order_date,
38.   MAX(order_date) - MIN(order_date) AS customer_lifetime_days,
39.   CASE
40.     WHEN COUNT(DISTINCT order_id) > 10 THEN 'High Frequency'
41.     WHEN COUNT(DISTINCT order_id) > 5 THEN 'Medium Frequency'
42.     ELSE 'Low Frequency'
43.   END AS purchase_frequency,
44.   CASE
45.     WHEN SUM(order_total) > 10000 THEN 'High Value'
46.     WHEN SUM(order_total) > 5000 THEN 'Medium Value'
47.     ELSE 'Low Value'
48.   END AS customer_value
49. FROM orders
50. GROUP BY customer_id
51. HAVING COUNT(DISTINCT order_id) > 1
52. ORDER BY total_spent DESC;

复制代码

6.2 库存管理分析

2. -- 库存周转率分析
3. SELECT
4.   p.product_id,
5.   p.product_name,
6.   c.category_name,
7.   i.quantity_on_hand,
8.   i.reorder_level,
9.   CASE
10.     WHEN i.quantity_on_hand <= i.reorder_level THEN 'Reorder Required'
11.     ELSE 'Sufficient Stock'
12.   END AS stock_status,
13.   COALESCE(s.total_sold, 0) AS total_sold,
14.   CASE
15.     WHEN i.quantity_on_hand > 0 THEN (COALESCE(s.total_sold, 0) / i.quantity_on_hand)
16.     ELSE NULL
17.   END AS turnover_ratio,
18.   p.list_price,
19.   i.quantity_on_hand * p.list_price AS inventory_value
20. FROM products p
21. JOIN categories c ON p.category_id = c.category_id
22. JOIN inventories i ON p.product_id = i.product_id
23. LEFT JOIN (
24.   SELECT product_id, SUM(quantity) AS total_sold
25.   FROM order_items
26.   JOIN orders ON order_items.order_id = orders.order_id
27.   WHERE orders.order_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11) AND TRUNC(SYSDATE, 'MM')
28.   GROUP BY product_id
29. ) s ON p.product_id = s.product_id
30. ORDER BY turnover_ratio DESC NULLS LAST;
32. -- 库存老化分析
33. WITH product_lifecycle AS (
34.   SELECT
35.     p.product_id,
36.     p.product_name,
37.     i.warehouse_id,
38.     i.quantity_on_hand,
39.     i.last_stock_date,
40.     TRUNC(SYSDATE) - i.last_stock_date AS days_in_stock,
41.     CASE
42.       WHEN TRUNC(SYSDATE) - i.last_stock_date > 365 THEN 'Obsolete'
43.       WHEN TRUNC(SYSDATE) - i.last_stock_date > 180 THEN 'Slow Moving'
44.       WHEN TRUNC(SYSDATE) - i.last_stock_date > 90 THEN 'Aging'
45.       ELSE 'Current'
46.     END AS stock_status,
47.     c.category_name
48.   FROM products p
49.   JOIN inventories i ON p.product_id = i.product_id
50.   JOIN categories c ON p.category_id = c.category_id
51. )
52. SELECT
53.   category_name,
54.   stock_status,
55.   COUNT(*) AS product_count,
56.   SUM(quantity_on_hand) AS total_quantity,
57.   AVG(days_in_stock) AS avg_days_in_stock
58. FROM product_lifecycle
59. GROUP BY category_name, stock_status
60. ORDER BY category_name,
61.   CASE stock_status
62.     WHEN 'Obsolete' THEN 1
63.     WHEN 'Slow Moving' THEN 2
64.     WHEN 'Aging' THEN 3
65.     ELSE 4
66.   END;

复制代码

6.3 人力资源分析

2. -- 员工流失率分析
3. SELECT
4.   TO_CHAR(hire_date, 'YYYY') AS hire_year,
5.   COUNT(*) AS hired_count,
6.   SUM(CASE WHEN termination_date IS NOT NULL THEN 1 ELSE 0 END) AS terminated_count,
7.   ROUND(SUM(CASE WHEN termination_date IS NOT NULL THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS turnover_rate,
8.   ROUND(AVG(CASE WHEN termination_date IS NOT NULL
9.            THEN termination_date - hire_date
10.            ELSE NULL END), 0) AS avg_tenure_days
11. FROM employees
12. GROUP BY TO_CHAR(hire_date, 'YYYY')
13. ORDER BY hire_year;
15. -- 部门人力成本分析
16. SELECT
17.   d.department_id,
18.   d.department_name,
19.   COUNT(e.employee_id) AS employee_count,
20.   SUM(e.salary) AS total_salary_cost,
21.   SUM(e.salary * 12) AS annual_salary_cost,
22.   AVG(e.salary) AS avg_salary,
23.   SUM(CASE WHEN e.commission_pct IS NOT NULL THEN e.salary * e.commission_pct ELSE 0 END) AS total_commission,
24.   SUM(e.salary * 12 + CASE WHEN e.commission_pct IS NOT NULL THEN e.salary * 12 * e.commission_pct ELSE 0 END) AS total_annual_cost,
25.   SUM(CASE WHEN e.hire_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12) AND TRUNC(SYSDATE, 'YYYY')
26.            THEN 1 ELSE 0 END) AS hires_last_year,
27.   SUM(CASE WHEN e.termination_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12) AND TRUNC(SYSDATE, 'YYYY')
28.            THEN 1 ELSE 0 END) AS terminations_last_year
29. FROM departments d
30. LEFT JOIN employees e ON d.department_id = e.department_id
31. GROUP BY d.department_id, d.department_name
32. ORDER BY total_annual_cost DESC;
34. -- 薪资分布分析
35. SELECT
36.   CASE
37.     WHEN salary < 3000 THEN 'Under 3K'
38.     WHEN salary BETWEEN 3000 AND 5000 THEN '3K - 5K'
39.     WHEN salary BETWEEN 5001 AND 8000 THEN '5K - 8K'
40.     WHEN salary BETWEEN 8001 AND 12000 THEN '8K - 12K'
41.     WHEN salary > 12000 THEN 'Over 12K'
42.   END AS salary_range,
43.   COUNT(*) AS employee_count,
44.   ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS percentage,
45.   d.department_name
46. FROM employees e
47. JOIN departments d ON e.department_id = d.department_id
48. GROUP BY
49.   CASE
50.     WHEN salary < 3000 THEN 'Under 3K'
51.     WHEN salary BETWEEN 3000 AND 5000 THEN '3K - 5K'
52.     WHEN salary BETWEEN 5001 AND 8000 THEN '5K - 8K'
53.     WHEN salary BETWEEN 8001 AND 12000 THEN '8K - 12K'
54.     WHEN salary > 12000 THEN 'Over 12K'
55.   END,
56.   d.department_name
57. ORDER BY
58.   CASE
59.     WHEN salary < 3000 THEN 1
60.     WHEN salary BETWEEN 3000 AND 5000 THEN 2
61.     WHEN salary BETWEEN 5001 AND 8000 THEN 3
62.     WHEN salary BETWEEN 8001 AND 12000 THEN 4
63.     WHEN salary > 12000 THEN 5
64.   END,
65.   d.department_name;

复制代码

7. 性能调优技巧

7.1 SQL提示（Hints）的使用

SQL提示是指导Oracle优化器选择特定执行路径的指令。

2. -- 使用INDEX提示强制使用特定索引
3. SELECT /*+ INDEX(e idx_emp_department_id) */
4.        e.employee_id, e.first_name, e.last_name, d.department_name
5. FROM employees e
6. JOIN departments d ON e.department_id = d.department_id
7. WHERE e.department_id = 10;
9. -- 使用FIRST_ROWS提示优化快速返回前几行结果
10. SELECT /*+ FIRST_ROWS(10) */
11.        employee_id, first_name, last_name, salary
12. FROM employees
13. WHERE department_id = 10
14. ORDER BY salary DESC;
16. -- 使用ALL_ROWS提示优化整体查询性能
17. SELECT /*+ ALL_ROWS */
18.        e.employee_id, e.first_name, e.last_name, d.department_name
19. FROM employees e
20. JOIN departments d ON e.department_id = d.department_id
21. WHERE e.salary > 5000;
23. -- 使用LEADING提示指定连接顺序
24. SELECT /*+ LEADING(e d) */
25.        e.employee_id, e.first_name, e.last_name, d.department_name
26. FROM employees e
27. JOIN departments d ON e.department_id = d.department_id
28. WHERE e.salary > 5000;
30. -- 使用USE_HASH提示指定哈希连接
31. SELECT /*+ USE_HASH(e d) */
32.        e.employee_id, e.first_name, e.last_name, d.department_name
33. FROM employees e
34. JOIN departments d ON e.department_id = d.department_id
35. WHERE e.salary > 5000;

复制代码

7.2 查询执行计划优化

2. -- 使用SQL Trace分析查询性能
3. ALTER SESSION SET SQL_TRACE = TRUE;
4. -- 执行查询
5. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
6. FROM employees e
7. JOIN departments d ON e.department_id = d.department_id
8. WHERE e.salary > 5000;
9. ALTER SESSION SET SQL_TRACE = FALSE;
11. -- 使用TKPROF格式化跟踪文件
12. -- 在操作系统命令行执行
13. -- tkprof trace_file.trc output_file.txt explain=username/password
15. -- 使用DBMS_XPLAN显示详细执行计划
16. EXPLAIN PLAN FOR
17. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
18. FROM employees e
19. JOIN departments d ON e.department_id = d.department_id
20. WHERE e.salary > 5000;
22. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL +OUTLINE'));
24. -- 使用10053事件查看优化器决策过程
25. ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
26. -- 执行查询
27. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
28. FROM employees e
29. JOIN departments d ON e.department_id = d.department_id
30. WHERE e.salary > 5000;
31. ALTER SESSION SET EVENTS '10053 trace name context off';

复制代码

7.3 SQL性能分析工具

2. -- 使用SQL Tuning Advisor
3. DECLARE
4.   l_task_name VARCHAR2(30);
5.   l_sql_text CLOB;
6. BEGIN
7.   l_sql_text := 'SELECT e.employee_id, e.first_name, e.last_name, d.department_name
8.                  FROM employees e
9.                  JOIN departments d ON e.department_id = d.department_id
10.                  WHERE e.salary > 5000';
11.   
12.   l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
13.     sql_text => l_sql_text,
14.     user_name => 'HR',
15.     scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
16.     time_limit => 60,
17.     task_name => 'sql_tuning_task',
18.     description => 'Tuning task for a query');
19.   
20.   DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
21. END;
22. /
24. -- 查看SQL Tuning Advisor的结果
25. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recommendations FROM DUAL;
27. -- 使用SQL Access Advisor
28. DECLARE
29.   l_task_name VARCHAR2(30);
30. BEGIN
31.   l_task_name := DBMS_ADVISOR.QUICK_TUNE(
32.     advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
33.     task_name => 'sql_access_task',
34.     attr1 => 'SELECT e.employee_id, e.first_name, e.last_name, d.department_name
35.               FROM employees e
36.               JOIN departments d ON e.department_id = d.department_id
37.               WHERE e.salary > 5000');
38.   
39.   DBMS_ADVISOR.EXECUTE_TASK(task_name => 'sql_access_task');
40. END;
41. /
43. -- 查看SQL Access Advisor的结果
44. SELECT DBMS_ADVISOR.GET_TASK_REPORT('sql_access_task') AS recommendations FROM DUAL;

复制代码

7.4 批量操作优化

2. -- 使用BULK COLLECT进行批量获取
3. DECLARE
4.   CURSOR emp_cursor IS
5.     SELECT employee_id, first_name, last_name, salary
6.     FROM employees
7.     WHERE department_id = 10;
8.   
9.   TYPE emp_array_type IS TABLE OF emp_cursor%ROWTYPE;
10.   emp_array emp_array_type;
11. BEGIN
12.   OPEN emp_cursor;
13.   LOOP
14.     FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT 100;
15.     EXIT WHEN emp_array.COUNT = 0;
16.    
17.     -- 处理批量数据
18.     FOR i IN 1..emp_array.COUNT LOOP
19.       DBMS_OUTPUT.PUT_LINE(emp_array(i).first_name || ' ' || emp_array(i).last_name ||
20.                            ': ' || emp_array(i).salary);
21.     END LOOP;
22.   END LOOP;
23.   CLOSE emp_cursor;
24. END;
25. /
27. -- 使用FORALL进行批量DML操作
28. CREATE TABLE emp_temp AS SELECT * FROM employees WHERE 1=0;
30. DECLARE
31.   CURSOR emp_cursor IS
32.     SELECT employee_id, first_name, last_name, salary, department_id
33.     FROM employees
34.     WHERE department_id = 10;
35.   
36.   TYPE emp_array_type IS TABLE OF emp_cursor%ROWTYPE;
37.   emp_array emp_array_type;
38. BEGIN
39.   OPEN emp_cursor;
40.   FETCH emp_cursor BULK COLLECT INTO emp_array;
41.   CLOSE emp_cursor;
42.   
43.   FORALL i IN 1..emp_array.COUNT
44.     INSERT INTO emp_temp VALUES emp_array(i);
45.   
46.   COMMIT;
47.   DBMS_OUTPUT.PUT_LINE('Inserted ' || emp_array.COUNT || ' records.');
48. END;
49. /
51. -- 使用MERGE语句进行高效的条件插入/更新
52. MERGE INTO employees_target t
53. USING (
54.   SELECT employee_id, first_name, last_name, salary, department_id
55.   FROM employees_source
56.   WHERE department_id = 10
57. ) s
58. ON (t.employee_id = s.employee_id)
59. WHEN MATCHED THEN
60.   UPDATE SET
61.     t.first_name = s.first_name,
62.     t.last_name = s.last_name,
63.     t.salary = s.salary,
64.     t.department_id = s.department_id
65. WHEN NOT MATCHED THEN
66.   INSERT (employee_id, first_name, last_name, salary, department_id)
67.   VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

复制代码

8. 结论与最佳实践

Oracle数据库查询语言是企业数据管理的核心工具，从基础语法到高级优化技巧，掌握这些技能可以显著提升工作效率和解决复杂数据查询与分析问题的能力。

8.1 Oracle SQL最佳实践

1. 编写清晰可读的SQL语句：使用适当的缩进、注释和命名约定，使SQL代码易于理解和维护。
2. 合理使用索引：为经常用于WHERE子句、JOIN条件和ORDER BY子句的列创建索引，但避免过度索引。
3. 定期收集统计信息：确保数据库优化器有最新的统计信息以做出最佳执行计划决策。
4. *避免使用SELECT **：只查询实际需要的列，减少数据传输量。
5. 使用绑定变量：减少硬解析，提高SQL执行效率。
6. 合理使用分区：对于大型表，考虑使用分区技术提高查询性能。
7. 定期监控和优化：使用Oracle提供的工具定期监控SQL性能，识别并优化低效查询。

编写清晰可读的SQL语句：使用适当的缩进、注释和命名约定，使SQL代码易于理解和维护。

合理使用索引：为经常用于WHERE子句、JOIN条件和ORDER BY子句的列创建索引，但避免过度索引。

定期收集统计信息：确保数据库优化器有最新的统计信息以做出最佳执行计划决策。

*避免使用SELECT **：只查询实际需要的列，减少数据传输量。

使用绑定变量：减少硬解析，提高SQL执行效率。

合理使用分区：对于大型表，考虑使用分区技术提高查询性能。

定期监控和优化：使用Oracle提供的工具定期监控SQL性能，识别并优化低效查询。

8.2 持续学习与提升

Oracle数据库技术不断发展，新版本中引入了许多新功能和优化技术。作为Oracle SQL专业人士，应持续学习新技术，如：

• Oracle 19c/21c中的新SQL功能
• 机器学习在SQL中的应用
• 多租户架构下的SQL优化
• 云环境中的Oracle SQL性能调优

通过不断学习和实践，可以将Oracle SQL技能提升到更高水平，为企业数据管理与分析提供更强大的支持。

总之，掌握Oracle数据库查询语言从基础语法到高级优化技巧，不仅可以解决企业复杂数据查询与分析问题，还能显著提升工作效率，为企业创造更大的价值。