Oracle 基礎函數與物件大全：字元/數字/日期/轉換/彙總/流程控制/DDL 範例

Oracle 基礎函數與物件大全：字元/數字/日期/轉換/彙總/流程控制/DDL 範例

整理 Oracle 開發高頻使用的內建函數、流程控制、DDL 範本與物件建立語法，作為桌邊速查手冊。

一、字元函數

-- 大小寫
LOWER('ABcDe')  -- abcde
UPPER('ABcDe')  -- ABCDE
INITCAP('aBcDe') -- Abcde

-- 截取/查找/填充/替換/長度/連接/去除
SUBSTR('ABcDe',2,2)      -- Bc
SUBSTR('ABcDe',-3,2)     -- cD
INSTR('ABcDefgdDh','D',3,2) -- 9 (第3字元起找第2個D)
LPAD('ABcDe',10,'D')     -- DDDDDABcDe
RPAD('ABcDe',10,'D')     -- ABcDeDDDDD
REPLACE('ABcDeEe','e',8) -- ABcD8E8
LENGTH('ABcDe')          -- 5
CONCAT('Hello','World')  -- HelloWorld
TRIM('H' FROM 'HelloWorld') -- elloWorld
TRIM(' ' FROM ' Hello ') -- Hello

二、數字函數

ROUND(245.1234,2)   -- 245.12
ROUND(245.1234,-2)  -- 200
TRUNC(245.1234,2)   -- 245.12 (直接截斷)
TRUNC(255.1234,-2)  -- 200
MOD(1600,300)       -- 100
FLOOR(3.7)          -- 3
CEIL(3.2)           -- 4

三、日期函數

SYSDATE                              -- 當前日期時間
MONTHS_BETWEEN(d1,d2)                -- 月份差
ADD_MONTHS(SYSDATE,2)                -- 加2個月
NEXT_DAY('15-4月-2012',3)            -- 給定日期後第3天
NEXT_DAY(SYSDATE,'星期一')           -- 下一個星期一
LAST_DAY(SYSDATE)                    -- 本月最後一天
ROUND(SYSDATE,'MONTH')               -- 月份四捨五入
TRUNC(SYSDATE,'MONTH')               -- 月首
TRUNC(SYSDATE,'DAY')                 -- 週首

四、轉換函數

TO_CHAR(123456.6543,'L99,999.00')   -- 本地貨幣格式
TO_CHAR(SYSDATE,'DAY DD-MON-YYYY')   -- 星期二 15-5月 -2012
TO_DATE('12-5-2012','DD-MM-YYYY')
TO_NUMBER('15,155,100.56','999,999,999.00')

五、通用函數

NVL(NULL,0)           -- 0
NVL2(NULL,0,1)        -- 1 (NULL->第三參數, 非NULL->第二參數)
NULLIF(1,1)           -- NULL (相等回NULL)
NULLIF(2,1)           -- 2
COALESCE(NULL,NULL,'不好','好') -- 不好 (第一個非NULL)

六、流程控制

-- DECODE
DECODE(job_id,'IT_PROG',1.1*sal,'ST_CLERK',1.15*sal,'SA_REP',1.2*sal,sal)

-- CASE
CASE job_id
  WHEN 'IT_PROG' THEN 1.1*sal
  WHEN 'ST_CLERK' THEN 1.15*sal
  ELSE sal END

七、彙總函數

AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE

八、DDL 範本

-- 建表含約束
CREATE TABLE employees(
  employee_id NUMBER(6) PRIMARY KEY,
  email VARCHAR2(25) UNIQUE,
  salary NUMBER(8,2) CHECK (salary > 0),
  department_id NUMBER(4) REFERENCES departments(department_id)
);

-- 修改表
ALTER TABLE dept80 ADD (job_id VARCHAR2(9));
ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));
ALTER TABLE dept80 DROP COLUMN job_id;

-- 設未用再刪 (大表優化)
ALTER TABLE emp SET UNUSED (department_id);
ALTER TABLE emp DROP UNUSED COLUMNS;

-- 視圖
CREATE OR REPLACE VIEW empvu20 AS
SELECT * FROM employees WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

-- 序列
CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;
SELECT dept_deptid_seq.NEXTVAL FROM dual;

-- 索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- 同義詞
CREATE SYNONYM emp FOR employees;
CREATE PUBLIC SYNONYM dept FOR departments;

九、事務控制

COMMIT; ROLLBACK;
SAVEPOINT sp1; ROLLBACK TO SAVEPOINT sp1;

十、MergE 語法

MERGE INTO copy_emp c USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT VALUES (...);