Oracle常用函数大全：高效开发必备指南

Oracle常用函数大全：高效开发必备指南

一、字符串处理函数

1. 拼接与连接

-- || 管道拼接
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- CONCAT 连接两个字符串
SELECT CONCAT('Hello', 'World') FROM dual; -- 结果: HelloWorld

-- LISTAGG 多行合并（Oracle 11g+）
SELECT dept_id, LISTAGG(emp_name, ',') WITHIN GROUP (ORDER BY emp_id) 
FROM employees GROUP BY dept_id;

2. 大小写转换

SELECT LOWER('Oracle') FROM dual; -- oracle
SELECT UPPER('sql') FROM dual;    -- SQL

3. 子串与查找

-- INSTR 查找子串位置
SELECT INSTR('Hello World', 'World', 1) FROM dual; -- 7

-- REGEXP_SUBSTR 正则提取
SELECT REGEXP_SUBSTR('2023-07-15', '\d{4}') FROM dual; -- 2023

-- LENGTH 字符串长度
SELECT LENGTH('Oracle') FROM dual; -- 6

二、条件判断函数

1. CASE表达式

SELECT employee_id,
  CASE 
    WHEN salary > 10000 THEN 'High'
    WHEN salary > 5000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

2. DECODE函数

SELECT DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown') 
FROM user_status;

3. 空值处理

-- NVL 空值替换
SELECT NVL(commission_pct, 0) FROM employees;

-- NVL2 非空判断
SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') FROM employees;

-- COALESCE 返回第一个非空值
SELECT COALESCE(middle_name, first_name, 'N/A') FROM persons;

三、聚合与窗口函数

1. 基础聚合

SELECT MAX(salary), MIN(salary), AVG(salary) FROM employees;

-- DISTINCT 去重
SELECT DISTINCT department_id FROM employees;

2. 窗口函数

-- ROW_NUMBER 分组排序
SELECT emp_name, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;

-- RANK 排名（处理并列）
SELECT emp_name, RANK() OVER (ORDER BY sales DESC) FROM sales_data;

四、树形结构查询

-- 层级查询（START WITH...CONNECT BY）
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name; -- SIBLINGS保持层级顺序

五、特殊数据类型处理

1. LOB大对象处理

-- CLOB类型包含值查询
SELECT * FROM documents 
WHERE DBMS_LOB.INSTR(doc_content, '紧急通知', 1, 1) > 0;

-- CLOB比较
SELECT * FROM contracts 
WHERE DBMS_LOB.COMPARE(contract_a, contract_b) = 0;

2. GUID生成

SELECT RAWTOHEX(SYS_GUID()) FROM dual; -- 生成32位UUID

六、高级数据处理

1. MERGE语句（UPSERT操作）

MERGE INTO employees e
USING new_employees n
ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = n.salary
WHEN NOT MATCHED THEN
  INSERT (emp_id, name) VALUES (n.emp_id, n.name);

2. 临时表使用

-- 创建事务级临时表
CREATE GLOBAL TEMPORARY TABLE temp_sales (
  product_id NUMBER,
  quantity NUMBER
) ON COMMIT DELETE ROWS;

-- 会话结束自动清空数据

七、日期与数字处理

-- 当前时间戳
SELECT CURRENT_TIMESTAMP FROM dual; -- 2023-07-15 14:30:45

-- 四舍五入
SELECT ROUND(123.456, 2) FROM dual; -- 123.46
SELECT ROUND(SYSDATE, 'MM') FROM dual; -- 当月第一天

-- 日期截断
SELECT TRUNC(SYSDATE, 'YYYY') FROM dual; -- 当年第一天

八、跨数据库操作

-- 通过DBLink跨库查询
SELECT * FROM employees@remote_db;

-- DBLink创建示例
CREATE DATABASE LINK remote_db 
CONNECT TO remote_user IDENTIFIED BY password 
USING 'remote_tns';

九、性能优化技巧

-- EXISTS vs IN 选择
-- 适合EXISTS的情况（外表小，内表大）
SELECT * FROM departments d 
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

-- 适合IN的情况（内表小）
SELECT * FROM employees 
WHERE dept_id IN (SELECT dept_id FROM small_departments);

-- NULLS LAST排序优化
SELECT * FROM employees 
ORDER BY commission_pct DESC NULLS LAST;

十、系统级操作

-- 清空表（DDL操作）
TRUNCATE TABLE temp_data; -- 比DELETE更快且不产生undo

-- 虚表DUAL使用
SELECT SYSDATE, USER FROM dual;

附录：函数速查表

性能提示：

• 对LOB字段的查询应创建函数索引
• 树形查询中CONNECT_BY_ISLEAF判断叶节点
• 临时表适合中间结果集暂存
• MERGE语句比单独UPDATE+INSERT更高效

掌握这些Oracle函数和技巧，可大幅提升数据库开发效率，解决90%的日常数据处理需求！