单行函数

SELECT last_name,
       UPPER(last_name)                  AS upper_demo,
       LOWER(last_name)                  AS lower_demo,
       INITCAP('hello oracle')           AS initcap_demo,
       SUBSTR(last_name, 1, 3)           AS sub_demo,
       LENGTH(last_name)                 AS len_demo,
       INSTR(last_name, 'a')             AS pos_demo,
       LPAD(salary, 10, '*')             AS lpad_demo,
       TRIM('  spaces  ')                AS trim_demo,
       REPLACE(job_id, '_', '-')         AS replace_demo
FROM   employees
WHERE  employee_id <= 104;

SELECT ROUND(45.926, 2)   AS rounded,     -- 45.93
       TRUNC(45.926, 2)   AS truncated,   -- 45.92  直接截断，不进位
       MOD(1600, 300)     AS remainder,   -- 100
       CEIL(45.1)         AS ceiled,      -- 46
       FLOOR(45.9)        AS floored,     -- 45
       ABS(-10)           AS absolute
FROM   dual;

SELECT SYSDATE                            AS now,
       SYSDATE + 1                        AS tomorrow,
       SYSDATE + 1/24                     AS next_hour,      -- 1/24 天 = 1 小时
       MONTHS_BETWEEN(SYSDATE, DATE '2024-01-01') AS months_diff,
       ADD_MONTHS(SYSDATE, 3)             AS plus_3_months,
       NEXT_DAY(SYSDATE, 'MONDAY')         AS next_monday,   -- 须匹配 NLS_DATE_LANGUAGE
       LAST_DAY(SYSDATE)                  AS month_end,
       TRUNC(SYSDATE, 'MM')               AS month_start,    -- 截断到月初，报表常用
       TRUNC(SYSDATE, 'YYYY')             AS year_start,
       EXTRACT(YEAR FROM SYSDATE)         AS cur_year
FROM   dual;

NEXT_DAY 的第二个参数必须是「当前会话日期语言」下的星期名。默认 NLS_DATE_LANGUAGE=AMERICAN 时写 'MONDAY'；若要用 '星期一'，需先执行 ALTER SESSION SET NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE'，否则报 ORA-01846（not a valid day of the week）。

SELECT employee_id,
       last_name,
       hire_date,
       TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS years_of_service
FROM   employees
WHERE  hire_date < DATE '2005-01-01'
ORDER  BY years_of_service DESC;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')  AS formatted_date,
       TO_CHAR(1234567.891, '999,999,999.99')       AS formatted_num,
       TO_CHAR(1234.5, 'L99,999.00')                AS currency,
       TO_DATE('2024-03-15', 'YYYY-MM-DD')          AS parsed_date,
       TO_NUMBER('1,234.56', '9,999.99')            AS parsed_num
FROM   dual;

SELECT employee_id,
       salary,
       CASE
         WHEN salary < 5000  THEN '偏低'
         WHEN salary < 10000 THEN '中等'
         WHEN salary < 20000 THEN '较高'
         ELSE '很高'
       END AS salary_band,
       -- DECODE 是 Oracle 特有写法，只能做等值判断
       DECODE(department_id, 50, 'Shipping', 60, 'IT', 90, 'Executive', '其他') AS dept_label
FROM   employees
ORDER  BY salary DESC;