Oracle常用函数SQL大全

都放在这里了，方便你粘贴。

持续输出，请关注我哦~

--------------------------------------------------
-- Oracle 常用 SQL 函数大全（带注释）
-- 分类：字符串函数、数值函数、日期函数、聚合函数、条件函数、转换函数、其他函数
--------------------------------------------------


-- ======================
-- 1. 字符串函数（String Functions）
-- ======================

-- 1.1 CONCAT(str1, str2) - 字符串连接（等价于 ||）
-- 示例：将 'Hello' 和 'World' 拼接
SELECT CONCAT('Hello', 'World') AS result FROM dual;
-- 结果：HelloWorld

-- 1.2 SUBSTR(str, start_pos, [length]) - 截取子字符串
-- 示例：从第2个字符开始截取3个字符
SELECT SUBSTR('Oracle', 2, 3) AS result FROM dual;
-- 结果：rac

-- 1.3 LENGTH(str) - 返回字符串长度
SELECT LENGTH('Database') AS result FROM dual;
-- 结果：7

-- 1.4 UPPER(str) / LOWER(str) - 大小写转换
SELECT UPPER('hello'), LOWER('WORLD') FROM dual;
-- 结果：HELLO  world

-- 1.5 TRIM([LEADING|TRAILING|BOTH] [trim_char] FROM str) - 去除空格或指定字符
SELECT TRIM('  Oracle  ') AS trimmed, TRIM(BOTH 'x' FROM 'xxOraclexx') AS trimmed_x FROM dual;
-- 结果：Oracle  Oracle

-- 1.6 REPLACE(str, search_str, [replace_str]) - 替换字符串
SELECT REPLACE('Hello World', 'World', 'Oracle') AS result FROM dual;
-- 结果：Hello Oracle

-- 1.7 INSTR(str, substr, [start_pos], [occurrence]) - 查找子字符串位置
SELECT INSTR('Oracle Database', 'a', 1, 2) AS position FROM dual;
-- 结果：10（第2次出现 'a' 的位置）

-- 1.8 LPAD(str, length, [pad_char]) / RPAD(str, length, [pad_char]) - 填充字符串
SELECT LPAD('123', 5, '0') AS lpad_result, RPAD('123', 5, '*') AS rpad_result FROM dual;
-- 结果：00123  123**


-- ======================
-- 2. 数值函数（Numeric Functions）
-- ======================

-- 2.1 ROUND(num, [decimal_places]) - 四舍五入
SELECT ROUND(123.4567, 2) AS rounded FROM dual;
-- 结果：123.46

-- 2.2 TRUNC(num, [decimal_places]) - 截断小数
SELECT TRUNC(123.4567, 2) AS truncated FROM dual;
-- 结果：123.45

-- 2.3 MOD(num1, num2) - 取模（余数）
SELECT MOD(10, 3) AS remainder FROM dual;
-- 结果：1

-- 2.4 ABS(num) - 绝对值
SELECT ABS(-15) AS abs_value FROM dual;
-- 结果：15

-- 2.5 CEIL(num) / FLOOR(num) - 向上/向下取整
SELECT CEIL(3.2) AS ceil_result, FLOOR(3.8) AS floor_result FROM dual;
-- 结果：4  3

-- 2.6 POWER(num, exponent) - 幂运算
SELECT POWER(2, 3) AS power_result FROM dual;
-- 结果：8

-- 2.7 SQRT(num) - 平方根
SELECT SQRT(16) AS sqrt_result FROM dual;
-- 结果：4


-- ======================
-- 3. 日期函数（Date Functions）
-- ======================

-- 3.1 SYSDATE - 当前系统日期和时间
SELECT SYSDATE AS current_date FROM dual;

-- 3.2 ADD_MONTHS(date, num_months) - 日期加月份
SELECT ADD_MONTHS(TO_DATE('2023-01-15', 'YYYY-MM-DD'), 3) AS future_date FROM dual;
-- 结果：2023-04-15

-- 3.3 MONTHS_BETWEEN(date1, date2) - 计算月份差
SELECT MONTHS_BETWEEN(TO_DATE('2023-12-31', 'YYYY-MM-DD'), SYSDATE) AS months_diff FROM dual;

-- 3.4 LAST_DAY(date) - 返回月份的最后一天
SELECT LAST_DAY(TO_DATE('2023-02-15', 'YYYY-MM-DD')) AS last_day FROM dual;
-- 结果：2023-02-28

-- 3.5 NEXT_DAY(date, 'weekday') - 返回下一个指定星期几的日期
SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM dual;

-- 3.6 TO_CHAR(date, 'format') - 日期转字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM dual;
-- 结果：2023-10-05 14:30:45（示例）

-- 3.7 TO_DATE(str, 'format') - 字符串转日期
SELECT TO_DATE('20231005', 'YYYYMMDD') AS parsed_date FROM dual;


-- ======================
-- 4. 聚合函数（Aggregate Functions）
-- ======================

-- 4.1 COUNT(*) - 统计行数
SELECT COUNT(*) AS total_rows FROM employees;

-- 4.2 SUM(column) - 求和
SELECT SUM(salary) AS total_salary FROM employees;

-- 4.3 AVG(column) - 平均值
SELECT AVG(salary) AS avg_salary FROM employees;

-- 4.4 MAX(column) / MIN(column) - 最大值/最小值
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;

-- 4.5 GROUP BY - 分组聚合
SELECT department_id, COUNT(*) AS emp_count 
FROM employees 
GROUP BY department_id;


-- ======================
-- 5. 条件函数（Conditional Functions）
-- ======================

-- 5.1 CASE WHEN ... THEN ... ELSE ... END - 条件判断
SELECT employee_name, 
       CASE WHEN salary > 10000 THEN 'High' 
            WHEN salary > 5000 THEN 'Medium' 
            ELSE 'Low' 
       END AS salary_level 
FROM employees;

-- 5.2 DECODE(expr, search1, result1, [search2, result2, ...], default) - 简单条件判断
SELECT DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown') AS status_desc FROM orders;

-- 5.3 NVL(expr, default_value) / NVL2(expr, value_if_not_null, value_if_null) - 空值处理
SELECT NVL(commission_pct, 0) AS comm_pct FROM employees;
SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') AS comm_status FROM employees;


-- ======================
-- 6. 转换函数（Conversion Functions）
-- ======================

-- 6.1 TO_CHAR(num, 'format') - 数字转字符串
SELECT TO_CHAR(1234.56, '9,999.99') AS formatted_num FROM dual;
-- 结果：1,234.56

-- 6.2 TO_NUMBER(str, 'format') - 字符串转数字
SELECT TO_NUMBER('$1,234.56', '$9,999.99') AS parsed_num FROM dual;

-- 6.3 CAST(expr AS type) - 类型转换
SELECT CAST('123' AS NUMBER) AS num_value, 
       CAST(SYSDATE AS VARCHAR2(20)) AS date_str FROM dual;


-- ======================
-- 7. 其他函数（Miscellaneous Functions）
-- ======================

-- 7.1 COALESCE(expr1, expr2, ...) - 返回第一个非空值
SELECT COALESCE(NULL, NULL, 'Default', 'Other') AS result FROM dual;
-- 结果：Default

-- 7.2 GREATEST(expr1, expr2, ...) / LEAST(expr1, expr2, ...) - 返回最大/最小值
SELECT GREATEST(10, 20, 5) AS max_val, LEAST(10, 20, 5) AS min_val FROM dual;
-- 结果：20  5

-- 7.3 RANK() / DENSE_RANK() / ROW_NUMBER() - 排名函数
SELECT employee_name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- 7.4 LISTAGG(column, ',') WITHIN GROUP (ORDER BY column) - 字符串聚合
SELECT department_id, 
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS emp_list
FROM employees
GROUP BY department_id;