| 函數 | 語法 | 示例 | 結果 |
|---|---|---|---|
| ASCII | ASCII('A') | SELECT ASCII('A') FROM dual; | 65 |
| CHR | CHR(65) | SELECT CHR(65) FROM dual; | A |
| CONCAT | CONCAT('010-','88888888') | SELECT CONCAT('010-','88888888') FROM dual; | 010-88888888 |
| INITCAP | INITCAP('smith') | SELECT INITCAP('smith') FROM dual; | Smith |
| INSTR | INSTR('oracle traning','ra',1,2) | SELECT INSTR('oracle traning','ra',1,2) FROM dual; | 9 |
| LENGTH | LENGTH('高乾競') | SELECT LENGTH('高乾競') FROM dual; | 3 |
| LOWER | LOWER('AaBb') | SELECT LOWER('AaBb') FROM dual; | aabb |
| UPPER | UPPER('AaBb') | SELECT UPPER('AaBb') FROM dual; | AABB |
| LPAD | LPAD('gao',10,'*') | SELECT LPAD('gao',10,'*') FROM dual; | *******gao |
| RPAD | RPAD('gao',10,'*') | SELECT RPAD('gao',10,'*') FROM dual; | gao******* |
| LTRIM | LTRIM(' gao ',' ') | SELECT LTRIM(' gao ',' ') FROM dual; | gao |
| RTRIM | RTRIM(' gao ',' ') | SELECT RTRIM(' gao ',' ') FROM dual; | gao |
| TRIM | TRIM(' gao ') | SELECT TRIM(' gao ') FROM dual; | gao |
| SUBSTR | SUBSTR('13088888888',3,8) | SELECT SUBSTR('13088888888',3,8) FROM dual; | 08888888 |
| REPLACE | REPLACE('he love you','he','i') | SELECT REPLACE('he love you','he','i') FROM dual; | i love you |
| SOUNDEX | SOUNDEX('weather') | SELECT * FROM t WHERE SOUNDEX(col)=SOUNDEX('weather'); | 匹配 weather/wether |
| TRANSLATE | TRANSLATE('fumble','uf','ar') | SELECT TRANSLATE('fumble','uf','ar') FROM dual; | ramble |
| 函數 | 示例 | 結果 |
|---|---|---|
| ABS | ABS(-100) | 100 |
| ACOS | ACOS(-1) | 3.1415927 |
| ASIN | ASIN(0.5) | .52359878 |
| ATAN | ATAN(1) | .78539816 |
| CEIL | CEIL(3.14) | 4 |
| COS | COS(-3.1415927) | -1 |
| COSH | COSH(20) | 242582598 |
| EXP | EXP(1) | 2.7182818 |
| FLOOR | FLOOR(2345.67) | 2345 |
| LN | LN(2.7182818) | .99999999 |
| LOG | LOG(2,4) | 2 |
| MOD | MOD(10,3) | 1 |
| POWER | POWER(2,10) | 1024 |
| ROUND | ROUND(55.5), ROUND(12345,-2) | 56, 12300 |
| SIGN | SIGN(-100) | -1 |
| SIN | SIN(1.57079) | 1 |
| SINH | SINH(20) | 242582598 |
| SQRT | SQRT(64) | 8 |
| TAN | TAN(20) | 2.2371609 |
| TANH | TANH(20) | 1 |
| TRUNC | TRUNC(124.1666,-2) | 100 |
| 函數 | 示例 | 結果 |
|---|---|---|
| SYSDATE | SYSDATE | 2024-05-09 14:30:00 |
| ADD_MONTHS | ADD_MONTHS(TO_DATE('199912','YYYYMM'),2) | 200002 |
| LAST_DAY | LAST_DAY(SYSDATE) | 2024-05-31 |
| MONTHS_BETWEEN | MONTHS_BETWEEN('19-DEC-1999','19-MAR-1999') | 9 |
| NEW_TIME | NEW_TIME(SYSDATE,'PDT','GMT') | 時區轉換 |
| NEXT_DAY | NEXT_DAY('18-MAY-2001','FRIDAY') | 25-MAY-2001 |
| ROUND | ROUND(SYSDATE,'HH') | 整點 |
| TRUNC | TRUNC(SYSDATE,'MI') | 整分 |
| 函數 | 用途 | 示例 |
|---|---|---|
| TO_CHAR | 日期/數字 → 字符 | TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') |
| TO_DATE | 字符 → 日期 | TO_DATE('2024-05-09','YYYY-MM-DD') |
| TO_NUMBER | 字符 → 數字 | TO_NUMBER('1999') |
| CHARTOROWID | 字符 → ROWID | CHARTOROWID('AAAA...') |
| ROWIDTOCHAR | ROWID → 字符 | ROWIDTOCHAR(ROWID) |
| HEXTORAW | 十六進制 → RAW | HEXTORAW('FF') |
| RAWTOHEX | RAW → 十六進制 | RAWTOHEX(HEXTORAW('FF')) |
| CONVERT | 字符集轉換 | CONVERT('str','US7ASCII','WE8ISO8859P1') |
| TO_MULTI_BYTE | 單字節 → 多字節 | TO_MULTI_BYTE('A') |
| TO_SINGLE_BYTE | 多字節 → 單字節 | TO_SINGLE_BYTE('全') |
| 函數 | 說明 | 示例 |
|---|---|---|
| DECODE | IF-THEN-ELSE | DECODE(cmd,2,'INSERT',3,'SELECT','OTHER') |
| DUMP | 內部表示調試 | DUMP('WORLD',1017,8,5) → Typ=1 Len=5... |
| EMPTY_BLOB/CLOB | LOB 初始化 | INSERT INTO t VALUES (EMPTY_BLOB()); |
| GREATEST | 最大值 | GREATEST('AA','AB','AC') → 'AC' |
| LEAST | 最小值 | LEAST('啊','安','天') → '啊' |
| UID | 當前用戶 ID | SELECT UID FROM dual; |
| USER | 當前用戶名 | SELECT USER FROM dual; |
| USERENV | 會話環境信息 | USERENV('SESSIONID'), USERENV('LANGUAGE') |
| VSIZE | 內部字節數 | VSIZE('ABC') → 3 |
| BFILENAME | 外部文件定位器 | BFILENAME('LOB_DIR','image.gif') |
| 函數 | 語法 | 示例 |
|---|---|---|
| AVG | AVG([DISTINCT|ALL] expr) | AVG(DISTINCT sal) → 忽略重複薪資 |
| COUNT | COUNT({*|DISTINCT|ALL} expr) | COUNT(*) → 含 NULL 行數 |
| MAX | MAX([DISTINCT|ALL] expr) | MAX(sal) |
| MIN | MIN([DISTINCT|ALL] expr) | MIN(sal) |
| SUM | SUM([DISTINCT|ALL] expr) | SUM(sal) |
| STDDEV | STDDEV([DISTINCT|ALL] expr) | 標準差 |
| VARIANCE | VARIANCE([DISTINCT|ALL] expr) | 方差 |
-- 基礎分組
SELECT deptno, COUNT(*), SUM(sal) FROM scott.emp GROUP BY deptno;
-- HAVING 過濾分組
SELECT deptno, COUNT(*), SUM(sal) FROM scott.emp
GROUP BY deptno HAVING COUNT(*) >= 5;
-- ORDER BY 排序分組結果
SELECT deptno, COUNT(*) cnt FROM scott.emp
GROUP BY deptno ORDER BY cnt DESC;
