Oracle高级函数 #

-- 字符串长度
SELECT LENGTH('Oracle') FROM DUAL;           -- 6
SELECT LENGTH('甲骨文') FROM DUAL;            -- 3
SELECT LENGTHB('甲骨文') FROM DUAL;           -- 6（字节数）

-- 字符串转换
SELECT UPPER('oracle') FROM DUAL;            -- ORACLE
SELECT LOWER('ORACLE') FROM DUAL;            -- oracle
SELECT INITCAP('oracle database') FROM DUAL; -- Oracle Database

-- 字符串截取
SELECT SUBSTR('Oracle', 1, 3) FROM DUAL;     -- Ora
SELECT SUBSTR('Oracle', -3) FROM DUAL;       -- cle
SELECT SUBSTRB('甲骨文', 1, 2) FROM DUAL;    -- 甲（按字节）

-- 字符串查找
SELECT INSTR('Oracle', 'a') FROM DUAL;       -- 3
SELECT INSTR('Oracle', 'a', 1, 1) FROM DUAL; -- 3（从第1位开始，第1次出现）
SELECT INSTR('Oracle', 'a', -1) FROM DUAL;   -- 3（从后往前找）

-- 字符串替换
SELECT REPLACE('Oracle Database', 'Oracle', 'MySQL') FROM DUAL; -- MySQL Database
SELECT REPLACE('Oracle', 'a', 'A') FROM DUAL;                    -- OrAcle

-- 字符串填充
SELECT LPAD('Oracle', 10, '*') FROM DUAL;   -- ****Oracle
SELECT RPAD('Oracle', 10, '*') FROM DUAL;   -- Oracle****

-- 字符串修剪
SELECT TRIM('  Oracle  ') FROM DUAL;        -- Oracle
SELECT LTRIM('  Oracle  ') FROM DUAL;       -- Oracle  
SELECT RTRIM('  Oracle  ') FROM DUAL;       --   Oracle
SELECT TRIM('O' FROM 'Oracle') FROM DUAL;   -- racle

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World') FROM DUAL; -- Hello World
SELECT 'Hello' || ' ' || 'World' FROM DUAL;     -- Hello World

-- REGEXP_LIKE：正则匹配
SELECT * FROM employees
WHERE REGEXP_LIKE(email, '^[a-z]+@[a-z]+\.[a-z]+$');

-- REGEXP_SUBSTR：正则提取
SELECT REGEXP_SUBSTR('Oracle 12c Database', '[0-9]+') FROM DUAL; -- 12

-- REGEXP_INSTR：正则查找位置
SELECT REGEXP_INSTR('Oracle 12c', '[0-9]') FROM DUAL; -- 8

-- REGEXP_REPLACE：正则替换
SELECT REGEXP_REPLACE('Oracle 12c', '[0-9]+', '19c') FROM DUAL; -- Oracle 19c

-- REGEXP_COUNT：正则计数
SELECT REGEXP_COUNT('Oracle 12c 19c', '[0-9]+') FROM DUAL; -- 2

-- 复杂正则示例
-- 提取邮箱
SELECT REGEXP_SUBSTR(
    'Contact: john@example.com or jane@test.org',
    '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}'
) FROM DUAL;

-- 提取手机号
SELECT REGEXP_SUBSTR(
    'Phone: 138-1234-5678',
    '[0-9]{3}-[0-9]{4}-[0-9]{4}'
) FROM DUAL;

-- 当前日期时间
SELECT SYSDATE FROM DUAL;                    -- 当前日期
SELECT SYSTIMESTAMP FROM DUAL;               -- 当前时间戳
SELECT CURRENT_DATE FROM DUAL;               -- 会话日期
SELECT CURRENT_TIMESTAMP FROM DUAL;          -- 会话时间戳

-- 时区相关
SELECT DBTIMEZONE FROM DUAL;                 -- 数据库时区
SELECT SESSIONTIMEZONE FROM DUAL;            -- 会话时区
SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;  -- UTC时间

-- 日期加减
SELECT SYSDATE + 1 FROM DUAL;                -- 加1天
SELECT SYSDATE - 1 FROM DUAL;                -- 减1天
SELECT SYSDATE + 1/24 FROM DUAL;             -- 加1小时
SELECT SYSDATE + 1/1440 FROM DUAL;           -- 加1分钟
SELECT SYSDATE + 1/86400 FROM DUAL;          -- 加1秒

-- ADD_MONTHS函数
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;     -- 加1个月
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;    -- 减1个月

-- MONTHS_BETWEEN函数
SELECT MONTHS_BETWEEN(
    TO_DATE('2024-12-31', 'YYYY-MM-DD'),
    TO_DATE('2024-01-01', 'YYYY-MM-DD')
) FROM DUAL;  -- 11.9677...

-- LAST_DAY函数
SELECT LAST_DAY(SYSDATE) FROM DUAL;          -- 本月最后一天

-- NEXT_DAY函数
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL; -- 下一个周一
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;        -- 下一个周一（2=周一）

-- TRUNC：日期截取
SELECT TRUNC(SYSDATE) FROM DUAL;             -- 截取到天（去掉时间）
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;     -- 截取到年（年初）
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;       -- 截取到月（月初）
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;       -- 截取到天
SELECT TRUNC(SYSDATE, 'DAY') FROM DUAL;      -- 截取到周（周日）
SELECT TRUNC(SYSDATE, 'HH24') FROM DUAL;     -- 截取到小时
SELECT TRUNC(SYSDATE, 'MI') FROM DUAL;       -- 截取到分钟

-- ROUND：日期舍入
SELECT ROUND(SYSDATE) FROM DUAL;             -- 舍入到天
SELECT ROUND(SYSDATE, 'YYYY') FROM DUAL;     -- 舍入到年
SELECT ROUND(SYSDATE, 'MM') FROM DUAL;       -- 舍入到月

-- EXTRACT函数
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;    -- 年
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;   -- 月
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;     -- 日
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL;  -- 时
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL; -- 分
SELECT EXTRACT(SECOND FROM SYSTIMESTAMP) FROM DUAL; -- 秒

-- 日期相关函数
SELECT TO_CHAR(SYSDATE, 'YYYY') AS year FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MM') AS month FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD') AS day FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DAY') AS weekday FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'D') AS weekday_num FROM DUAL;  -- 1-7
SELECT TO_CHAR(SYSDATE, 'WW') AS week FROM DUAL;        -- 年中周数
SELECT TO_CHAR(SYSDATE, 'Q') AS quarter FROM DUAL;      -- 季度

-- 数字转字符串
SELECT TO_CHAR(12345.67, '999,999.99') FROM DUAL;    -- 12,345.67
SELECT TO_CHAR(12345.67, '$99,999.99') FROM DUAL;     -- $12,345.67
SELECT TO_CHAR(12345.67, 'L99,999.99') FROM DUAL;     -- ￥12,345.67
SELECT TO_CHAR(0.1234, '0.9999') FROM DUAL;           -- 0.1234
SELECT TO_CHAR(123, '00000') FROM DUAL;               -- 00123

-- 日期转字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DAY, DD MONTH YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS AM') FROM DUAL;

-- 常用日期格式
-- YYYY：四位年
-- YY：两位年
-- MM：两位月
-- MON：月份缩写
-- MONTH：月份全称
-- DD：日
-- DAY：星期
-- HH24：24小时制
-- HH12：12小时制
-- MI：分钟
-- SS：秒
-- AM/PM：上午/下午

-- 字符串转日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM DUAL;
SELECT TO_DATE('15-01-2024', 'DD-MM-YYYY') FROM DUAL;
SELECT TO_DATE('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('January 15, 2024', 'MONTH DD, YYYY') FROM DUAL;

-- 使用DATE字面量
SELECT DATE '2024-01-15' FROM DUAL;

-- 使用TIMESTAMP字面量
SELECT TIMESTAMP '2024-01-15 10:30:00' FROM DUAL;

-- 字符串转数字
SELECT TO_NUMBER('12345') FROM DUAL;
SELECT TO_NUMBER('12,345.67', '99,999.99') FROM DUAL;
SELECT TO_NUMBER('$12,345.67', '$99,999.99') FROM DUAL;

-- 使用CAST
SELECT CAST('12345' AS NUMBER) FROM DUAL;
SELECT CAST('123.45' AS NUMBER(10,2)) FROM DUAL;

-- CAST通用转换
SELECT CAST(123 AS VARCHAR2(10)) FROM DUAL;        -- 数字转字符串
SELECT CAST('123' AS NUMBER) FROM DUAL;            -- 字符串转数字
SELECT CAST(SYSDATE AS TIMESTAMP) FROM DUAL;       -- 日期转时间戳
SELECT CAST(SYSTIMESTAMP AS DATE) FROM DUAL;       -- 时间戳转日期

-- CAST与TO_CHAR比较
SELECT CAST(12345.67 AS VARCHAR2(20)) FROM DUAL;   -- 12345.67
SELECT TO_CHAR(12345.67, '99999.99') FROM DUAL;    -- 12345.67

-- NVL：NULL值替换
SELECT NVL(NULL, 0) FROM DUAL;                    -- 0
SELECT NVL(NULL, 'N/A') FROM DUAL;                -- N/A

-- 使用示例
SELECT 
    employee_id,
    first_name,
    salary,
    NVL(commission_pct, 0) AS commission,
    salary + salary * NVL(commission_pct, 0) AS total_income
FROM employees;

-- NVL2：根据是否为NULL返回不同值
-- NVL2(expr1, expr2, expr3)
-- 如果expr1不为NULL，返回expr2
-- 如果expr1为NULL，返回expr3

SELECT NVL2(NULL, 'NOT NULL', 'NULL') FROM DUAL;   -- NULL
SELECT NVL2('Value', 'NOT NULL', 'NULL') FROM DUAL; -- NOT NULL

-- 使用示例
SELECT 
    employee_id,
    first_name,
    NVL2(commission_pct, '有提成', '无提成') AS has_commission
FROM employees;

-- NULLIF：如果相等返回NULL
-- NULLIF(expr1, expr2)
-- 如果expr1 = expr2，返回NULL
-- 否则返回expr1

SELECT NULLIF(10, 10) FROM DUAL;                   -- NULL
SELECT NULLIF(10, 20) FROM DUAL;                   -- 10

-- 使用示例：避免除零错误
SELECT 
    employee_id,
    salary,
    commission_pct,
    NULLIF(commission_pct, 0) AS safe_commission
FROM employees;

-- COALESCE：返回第一个非NULL值
SELECT COALESCE(NULL, NULL, 'Value') FROM DUAL;    -- Value
SELECT COALESCE(NULL, 'Second', 'Third') FROM DUAL; -- Second

-- 使用示例
SELECT 
    employee_id,
    COALESCE(phone_number, mobile_number, email) AS contact
FROM employees;

-- 简单CASE表达式
SELECT 
    employee_id,
    first_name,
    department_id,
    CASE department_id
        WHEN 10 THEN 'IT'
        WHEN 20 THEN 'Sales'
        WHEN 30 THEN 'HR'
        ELSE 'Other'
    END AS department_name
FROM employees;

-- 搜索CASE表达式
SELECT 
    employee_id,
    first_name,
    salary,
    CASE 
        WHEN salary >= 10000 THEN 'High'
        WHEN salary >= 7000 THEN 'Medium'
        WHEN salary >= 5000 THEN 'Low'
        ELSE 'Very Low'
    END AS salary_level
FROM employees;

-- 在ORDER BY中使用CASE
SELECT employee_id, first_name, salary
FROM employees
ORDER BY CASE 
    WHEN department_id = 10 THEN 1
    WHEN department_id = 20 THEN 2
    ELSE 3
END;

-- DECODE：Oracle特有的条件函数
SELECT 
    employee_id,
    first_name,
    DECODE(department_id,
        10, 'IT',
        20, 'Sales',
        30, 'HR',
        'Other'
    ) AS department_name
FROM employees;

-- DECODE嵌套
SELECT 
    employee_id,
    first_name,
    DECODE(department_id,
        10, DECODE(job_id, 'IT_PROG', 'Programmer', 'IT Staff'),
        20, 'Sales',
        'Other'
    ) AS job_title
FROM employees;

-- DECODE实现范围判断
SELECT 
    employee_id,
    salary,
    DECODE(SIGN(salary - 10000),
        1, 'High',
        0, 'High',
        -1, DECODE(SIGN(salary - 5000),
            1, 'Medium',
            0, 'Medium',
            -1, 'Low'
        )
    ) AS salary_level
FROM employees;

-- LISTAGG：字符串聚合
SELECT 
    department_id,
    LISTAGG(first_name, ',') WITHIN GROUP (ORDER BY first_name) AS employees
FROM employees
GROUP BY department_id;

-- 结果
DEPARTMENT_ID EMPLOYEES
------------- -----------------
           10 Jane,John
           20 Alice,Bob
           30 Charlie

-- 处理超长字符串（12c R2+）
SELECT 
    department_id,
    LISTAGG(first_name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) 
        WITHIN GROUP (ORDER BY first_name) AS employees
FROM employees
GROUP BY department_id;

-- VARIANCE：方差
SELECT VARIANCE(salary) FROM employees;

-- STDDEV：标准差
SELECT STDDEV(salary) FROM employees;

-- MEDIAN：中位数
SELECT MEDIAN(salary) FROM employees;

-- PERCENTILE_CONT：百分位数
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

-- PERCENTILE_DISC：离散百分位数
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

-- PERCENT_RANK：百分比排名
SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;

-- 四舍五入
SELECT ROUND(123.456, 2) FROM DUAL;        -- 123.46
SELECT ROUND(123.456, -1) FROM DUAL;       -- 120

-- 截断
SELECT TRUNC(123.456, 2) FROM DUAL;        -- 123.45
SELECT TRUNC(123.456, -1) FROM DUAL;       -- 120

-- 取模
SELECT MOD(10, 3) FROM DUAL;               -- 1

-- 绝对值
SELECT ABS(-123) FROM DUAL;                -- 123

-- 幂运算
SELECT POWER(2, 10) FROM DUAL;             -- 1024

-- 平方根
SELECT SQRT(16) FROM DUAL;                 -- 4

-- 符号
SELECT SIGN(-10) FROM DUAL;                -- -1
SELECT SIGN(10) FROM DUAL;                 -- 1
SELECT SIGN(0) FROM DUAL;                  -- 0

-- 向上/向下取整
SELECT CEIL(123.1) FROM DUAL;              -- 124
SELECT FLOOR(123.9) FROM DUAL;             -- 123

-- LNNVL：条件取反（包含NULL）
SELECT * FROM employees
WHERE LNNVL(salary > 10000);
-- 返回salary <= 10000或salary为NULL的行

-- NANVL：处理NaN
SELECT NANVL(commission_pct, 0) FROM employees;

-- NULLIF与COALESCE组合
SELECT COALESCE(NULLIF(a, b), c) FROM table;

-- 用户信息
SELECT USER FROM DUAL;                     -- 当前用户
SELECT UID FROM DUAL;                      -- 用户ID
SELECT USERENV('SESSIONID') FROM DUAL;     -- 会话ID
SELECT USERENV('LANGUAGE') FROM DUAL;      -- 语言

-- 系统信息
SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL;

-- 唯一标识
SELECT SYS_GUID() FROM DUAL;               -- 生成唯一GUID
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;     -- 十六进制格式