1. SQL高级特性概述
Oracle SQL提供了许多高级特性，这些特性可以帮助我们更灵活、更高效地处理复杂的数据操作。本文将介绍一些常用的SQL高级特性，包括WITH子句、MERGE语句、PIVOT和UNPIVOT操作、正则表达式、层次查询以及BULK COLLECT和FORALL等。更多学习教程www.fgedu.net.cn
2. WITH子句（公共表表达式）
WITH子句允许我们定义临时结果集，这些结果集可以在后续的查询中引用，从而简化复杂的查询。
CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2)
);– 插入数据
INSERT INTO employees VALUES (100, ‘Steven’, ‘King’, 90, 24000);INSERT INTO employees VALUES (101, ‘Neena’, ‘Kochhar’, 90, 17000);INSERT INTO employees VALUES (102, ‘Lex’, ‘De Haan’, 90, 17000);INSERT INTO employees VALUES (103, ‘Alexander’, ‘Hunold’, 60, 9000);INSERT INTO employees VALUES (104, ‘Bruce’, ‘Ernst’, 60, 6000);INSERT INTO employees VALUES (105, ‘David’, ‘Austin’, 60, 4800);– 使用WITH子句
WITH dept_salary AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary, d.avg_salary
FROM employees e
JOIN dept_salary d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID SALARY AVG_SALARY
———– ——————– ————————- ————- ———- ———-
100 Steven King 90 24000 19333.3333
101 Neena Kochhar 90 17000 19333.3333
102 Lex De Haan 90 17000 19333.3333
103 Alexander Hunold 60 9000 6600
— 递归WITH子句
CREATE TABLE employees_hierarchy (
employee_id NUMBER(6),
manager_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25)
);INSERT INTO employees_hierarchy VALUES (100, NULL, ‘Steven’, ‘King’);INSERT INTO employees_hierarchy VALUES (101, 100, ‘Neena’, ‘Kochhar’);INSERT INTO employees_hierarchy VALUES (102, 100, ‘Lex’, ‘De Haan’);INSERT INTO employees_hierarchy VALUES (103, 102, ‘Alexander’, ‘Hunold’);INSERT INTO employees_hierarchy VALUES (104, 103, ‘Bruce’, ‘Ernst’);WITH manager_hierarchy (employee_id, first_name, last_name, level) AS (
SELECT employee_id, first_name, last_name, 1
FROM employees_hierarchy
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, mh.level + 1
FROM employees_hierarchy e
JOIN manager_hierarchy mh ON e.manager_id = mh.employee_id
)
SELECT * FROM manager_hierarchy;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME LEVEL
———– ——————– ————————- ———-
100 Steven King 1
101 Neena Kochhar 2
102 Lex De Haan 2
103 Alexander Hunold 3
104 Bruce Ernst 4
3. MERGE语句
MERGE语句是一种强大的DML操作，它可以根据条件执行插入、更新或删除操作，是一种高效的数据同步方法。
CREATE TABLE target_employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
salary NUMBER(8,2)
);CREATE TABLE source_employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
salary NUMBER(8,2)
);– 插入初始数据
INSERT INTO target_employees VALUES (100, ‘Steven’, ‘King’, 24000);INSERT INTO target_employees VALUES (101, ‘Neena’, ‘Kochhar’, 17000);INSERT INTO source_employees VALUES (100, ‘Steven’, ‘King’, 25000); — 更新
INSERT INTO source_employees VALUES (101, ‘Neena’, ‘Kochhar’, 17000); — 不变
INSERT INTO source_employees VALUES (102, ‘Lex’, ‘De Haan’, 17000); — 插入
— 使用MERGE语句
MERGE INTO target_employees t
USING source_employees s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
INSERT (t.employee_id, t.first_name, t.last_name, t.salary)
VALUES (s.employee_id, s.first_name, s.last_name, s.salary);3 rows merged.
— 查看结果
SELECT * FROM target_employees;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME SALARY
———– ——————– ————————- ———-
100 Steven King 25000
101 Neena Kochhar 17000
102 Lex De Haan 17000
4. PIVOT和UNPIVOT操作
PIVOT操作可以将行数据转换为列数据，UNPIVOT操作则可以将列数据转换为行数据。
CREATE TABLE sales_data (
sale_date DATE,
department VARCHAR2(20),
amount NUMBER(10,2)
);– 插入数据
INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), ‘Sales’, 10000);INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), ‘Marketing’, 5000);INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), ‘IT’, 8000);INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), ‘Sales’, 12000);INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), ‘Marketing’, 6000);INSERT INTO sales_data VALUES (TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), ‘IT’, 9000);– 使用PIVOT操作
SELECT *
FROM sales_data
PIVOT (
SUM(amount) FOR department IN (‘Sales’ AS fgsales, ‘Marketing’ AS marketing, ‘IT’ AS it)
)
ORDER BY sale_date;SALE_DATE SALES MARKETING IT
———- ———- ———- ———-
01-JAN-26 10000 5000 8000
02-JAN-26 12000 6000 9000
— 创建示例表用于UNPIVOT
CREATE TABLE monthly_sales (
year NUMBER(4),
january NUMBER(10,2),
february NUMBER(10,2),
march NUMBER(10,2)
);INSERT INTO monthly_sales VALUES (2026, 10000, 12000, 15000);– 使用UNPIVOT操作
SELECT *
FROM monthly_sales
UNPIVOT (
amount FOR month IN (january, february, march)
);YEAR MONTH AMOUNT
———- ———- ———-
2026 JANUARY 10000
2026 FEBRUARY 12000
2026 MARCH 15000
5. 正则表达式
Oracle SQL支持正则表达式，可以用于复杂的字符串匹配和处理。
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(last_name, ‘^K.*’);EMP_NAME EMP_EMP_EMP_LAST_NAME
——————– ————————-
Steven King
Neena Kochhar
— 使用REGEXP_SUBSTR函数
SELECT REGEXP_SUBSTR(‘Oracle 12c, MySQL 8.0, PostgreSQL 13’, ‘[A-Za-z0-9]+’) AS first_word
FROM dual;FIRST_WORD
——————–
Oracle
— 使用REGEXP_REPLACE函数
SELECT REGEXP_REPLACE(‘Oracle 12c, MySQL 8.0, PostgreSQL 13’, ‘[0-9]+’, ‘X’) AS replaced
FROM dual;REPLACED
—————————————-
Oracle Xc, MySQL X.0, PostgreSQL X
— 使用REGEXP_INSTR函数
SELECT REGEXP_INSTR(‘Oracle 12c, MySQL 8.0, PostgreSQL 13’, ‘[0-9]+’) AS position
FROM dual;POSITION
———-
8
6. 层次查询
层次查询用于查询具有层次结构的数据，如组织结构、物料清单等。
SELECT employee_id, manager_id, first_name, last_name,
LEVEL,
LPAD(‘ ‘, 2 * (LEVEL – 1)) || first_name || ‘ ‘ || last_name AS indent_name
FROM employees_hierarchy
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;EMPLOYEE_ID MANAGER_ID EMP_NAME EMP_EMP_EMP_LAST_NAME LEVEL INDENT_NAME
———– ———– ——————– ————————- ———- ——————————
100 Steven King 1 Steven King
101 100 Neena Kochhar 2 Neena Kochhar
102 100 Lex De Haan 2 Lex De Haan
103 102 Alexander Hunold 3 Alexander Hunold
104 103 Bruce Ernst 4 Bruce Ernst
— 使用NOCYCLE处理循环引用
INSERT INTO employees_hierarchy VALUES (105, 104, ‘David’, ‘Austin’);INSERT INTO employees_hierarchy VALUES (104, 105, ‘Bruce’, ‘Ernst’); — 创建循环引用
SELECT employee_id, manager_id, first_name, last_name,
LEVEL
FROM employees_hierarchy
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
START WITH manager_id IS NULL;EMPLOYEE_ID MANAGER_ID EMP_NAME EMP_EMP_EMP_LAST_NAME LEVEL
———– ———– ——————– ————————- ———-
100 Steven King 1
101 100 Neena Kochhar 2
102 100 Lex De Haan 2
103 102 Alexander Hunold 3
104 103 Bruce Ernst 4
105 104 David Austin 5
7. BULK COLLECT和FORALL
BULK COLLECT和FORALL是PL/SQL中的特性，可以提高批量数据处理的性能。
DECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE;emp_data emp_tab;BEGIN
SELECT * BULK COLLECT INTO emp_data
FROM employees
WHERE department_id = 90;FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee: ‘ || emp_data(i).first_name || ‘ ‘ || emp_data(i).last_name);END LOOP;END;/Employee: Steven King
Employee: Neena Kochhar
Employee: Lex De Haan
— 使用FORALL批量更新数据
DECLARE
TYPE emp_ids IS TABLE OF employees.employee_id%TYPE;TYPE emp_salaries IS TABLE OF employees.salary%TYPE;ids emp_ids;salaries emp_salaries;BEGIN
— 批量获取数据
SELECT employee_id, salary BULK COLLECT INTO ids, salaries
FROM employees
WHERE department_id = 60;– 批量更新数据
FORALL i IN 1..ids.COUNT
UPDATE employees
SET salary = salaries(i) * 1.1
WHERE employee_id = ids(i);COMMIT;END;/– 查看更新结果
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 60;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME SALARY
———– ——————– ————————- ———-
103 Alexander Hunold 9900
104 Bruce Ernst 6600
105 David Austin 5280
8. 最佳实践
1. 合理使用WITH子句，简化复杂的查询逻辑
2. 使用MERGE语句进行数据同步，提高效率
3. 利用PIVOT和UNPIVOT操作，灵活处理数据格式
4. 使用正则表达式进行复杂的字符串处理
5. 利用层次查询处理具有层次结构的数据
6. 使用BULK COLLECT和FORALL提高批量数据处理性能
7. 注意高级特性的性能影响，避免在大型表上使用过于复杂的操作
8. 定期分析查询性能，优化执行计划
9. 遵循SQL编码规范，保持代码的可读性
10. 测试高级特性的结果，确保逻辑正确
更多视频教程www.fgedu.net.cn
学习交流加群风哥微信: itpux-com
学习交流加群风哥QQ113257174
更多学习教程公众号风哥教程itpux_com
from oracle:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
