Oracle 数据库提供了丰富的内置函数，可以用于数据查询、转换、计算和聚合等操作。以下是 Oracle 函数的分类和主要用法：
UPPER/LOWER/INITCAP: 转换大小写
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('oracle database') FROM dual;
CONCAT/||: 字符串连接
SELECT CONCAT('Hello', 'World'), 'Hello' || ' ' || 'World' FROM dual;
SUBSTR: 截取子串
SELECT SUBSTR('Oracle Database', 8, 8) FROM dual; -- 返回 'Database'
LENGTH/LENGTHB: 字符串长度
SELECT LENGTH('Oracle'), LENGTHB('甲骨文') FROM dual;
INSTR: 查找子串位置
SELECT INSTR('Oracle Database', 'ase') FROM dual; -- 返回 13
LPAD/RPAD: 填充字符串
SELECT LPAD('500', 6, '0'), RPAD('500', 6, '*') FROM dual;
TRIM/LTRIM/RTRIM: 去除空格或指定字符
SELECT TRIM(' Oracle '), LTRIM('xxxOracle', 'x') FROM dual;
REPLACE: 替换字符串
SELECT REPLACE('Oracle Database', 'Database', 'DB') FROM dual;
ROUND: 四舍五入
SELECT ROUND(45.926, 2), ROUND(45.926, -1) FROM dual;
TRUNC: 截断数字
SELECT TRUNC(45.926, 2), TRUNC(45.926, -1) FROM dual;
MOD: 取模
SELECT MOD(10, 3) FROM dual; -- 返回 1
CEIL/FLOOR: 向上/向下取整
SELECT CEIL(3.14), FLOOR(3.14) FROM dual;
SYSDATE: 当前系统日期和时间
SELECT SYSDATE FROM dual;
ADD_MONTHS: 增加月份
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
MONTHS_BETWEEN: 月份差
SELECT MONTHS_BETWEEN('01-JAN-2023', '01-MAR-2023') FROM dual;
LAST_DAY: 月份最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;
NEXT_DAY: 下一个指定星期几的日期
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
EXTRACT: 提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
TO_CHAR: 转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(1234.56, 'L9,999.99') FROM dual; -- 本地货币格式
TO_NUMBER: 转换为数字
SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;
TO_DATE: 转换为日期
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') FROM dual;
NVL/NVL2: 空值处理
SELECT NVL(commission_pct, 0), NVL2(commission_pct, '有佣金', '无佣金') FROM employees;
COALESCE: 返回第一个非空表达式
SELECT COALESCE(commission_pct, salary, 0) FROM employees;
DECODE: 简单条件判断
SELECT DECODE(department_id, 10, '财务部', 20, '研发部', '其他部门') FROM employees;
CASE: 条件表达式
SELECT CASE WHEN salary > 10000 THEN '高薪'
WHEN salary > 5000 THEN '中等'
ELSE '低薪' END AS salary_level
FROM employees;
AVG: 平均值
SELECT AVG(salary) FROM employees;
COUNT: 计数
SELECT COUNT(*), COUNT(commission_pct) FROM employees;
MAX/MIN: 最大/最小值
SELECT MAX(salary), MIN(hire_date) FROM employees;
SUM: 求和
SELECT SUM(salary) FROM employees WHERE department_id = 50;
LISTAGG: 列转行(字符串聚合)
SELECT department_id, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name)
FROM employees GROUP BY department_id;
ROW_NUMBER: 行号
SELECT employee_id, last_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
RANK/DENSE_RANK: 排名
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
LEAD/LAG: 访问前后行数据
SELECT employee_id, last_name, hire_date,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS prev_hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire_date
FROM employees;
FIRST_VALUE/LAST_VALUE: 访问窗口首尾值
SELECT employee_id, last_name, salary, department_id,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS lowest_sal
FROM employees;
PIVOT/UNPIVOT: 行列转换
-- 行转列
SELECT * FROM (
SELECT department_id, job_id, salary FROM employees
) PIVOT (
AVG(salary) FOR job_id IN ('IT_PROG' AS IT, 'SA_REP' AS Sales)
);
-- 列转行
SELECT * FROM pivot_table
UNPIVOT (
salary FOR job_id IN (IT AS 'IT_PROG', Sales AS 'SA_REP')
);
JSON函数(Oracle 12c及以上)
SELECT JSON_OBJECT('id' VALUE employee_id, 'name' VALUE last_name) FROM employees;
SELECT JSON_VALUE('{"id":100, "name":"King"}', '$.name') FROM dual;
创建自定义函数示例：
CREATE OR REPLACE FUNCTION get_annual_salary(
p_monthly_sal NUMBER,
p_commission NUMBER DEFAULT 0
) RETURN NUMBER IS
v_annual_sal NUMBER;
BEGIN
v_annual_sal := (p_monthly_sal + NVL(p_commission, 0)) * 12;
RETURN v_annual_sal;
END get_annual_salary;
/
-- 使用自定义函数
SELECT last_name, salary, get_annual_salary(salary, commission_pct) AS annual_sal
FROM employees;
以上是Oracle函数的主要用法，实际应用中可以根据具体需求选择合适的函数组合使用。