Oracle常用函数
函数 | 说明 | 示例 |
---|---|---|
ABS | ABS(num) 返回绝对值 | SELECT ABS(-12) FROM dual; – 12 |
MOD | MOD(num1, num2) 返回余数 注意:返回值的正负仅由第一个参数决定 | SELECT MOD(10, 3) FROM DUAL; – 1 SELECT MOD(10, -3) FROM DUAL; – 1 SELECT MOD(-10, 3) FROM DUAL; – -1 SELECT MOD(-10, -3) FROM DUAL; – -1 |
REMAINDER | REMAINDER(n2, n1) 取余,用法同MOD | |
CEIL | CEIL(num) 返回大于或等于的最小整数 | SELECT CEIL(15.2) FROM DUAL; – 16 |
FLOOR | FLOOR(num) 返回小于或等于的最大整数 | SELECT FLOOR(15.8) FROM DUAL; – 15 |
ROUND (number) | ROUND(n [, integer ]) 四舍五入 | SELECT ROUND(14.15, 1) FROM DUAL; – 14.2 SELECT ROUND(14.15, -1) FROM DUAL; – 10 SELECT ROUND(14.15) FROM DUAL; – 14 |
TRUNC (number) | TRUNC(n1 [, n2 ]) 数字截取 | SELECT TRUNC(16.88) FROM DUAL; – 16 SELECT TRUNC(16.88, 1) FROM DUAL; – 16.8 SELECT TRUNC(16.88, -1) FROM DUAL; – 10 |
POWER | POWER(num1, num2) 返回num1的num2次方 | SELECT POWER(3, 2) FROM DUAL; – 9 |
SQRT | SQRT(n) 返回n的平方根 | SELECT SQRT(9) FROM DUAL; – 3 |
LOWER | LOWER(char) 转小写 | |
UPPER | UPPER(char) 转大写 | |
CONCAT | CONCAT(char1, char2) 拼接字符串 | |
SUBSTR | 字符串截取 | SELECT SUBSTR(‘ABCDEFGHIJK’, 0, 1) FROM DUAL; – A SELECT SUBSTR(‘ABCDEFGHIJK’, 1, 1) FROM DUAL; – A SELECT SUBSTR(‘ABCDEFGHIJK’, 1, 0) FROM DUAL; – null SELECT SUBSTR(‘ABCDEFGHIJK’, 1, -1) FROM DUAL; – null SELECT SUBSTR(‘ABCDEFGHIJK’, 2, 1) FROM DUAL; – B SELECT SUBSTR(‘ABCDEFGHIJK’, 2, 0) FROM DUAL; – null |
REPLACE | REPLACE(char, search_string [,replacement_string ]) | SELECT REPLACE(‘JACK and JUE’,‘J’,‘BL’) FROM DUAL; – BLACK and BLUE |
TRIM | TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source ) | |
LTRIM | LTRIM(char [, set ]) 左修剪 | |
RTRIM | LTRIM(char [, set ]) 右修剪 | |
LPAD | LPAD(expr1, n [, expr2 ]) 左填充 expr2省略时填充空字符串,expr1的长度大于n时,将截取expr1 | SELECT LPAD(‘123456’, 2) FROM DUAL; – 12 SELECT LPAD(‘123456’, 10, ‘E’) FROM DUAL; – EEEE123456 |
RPAD | LPAD(expr1, n [, expr2 ]) 右填充 | |
INSTR | instr(str,subStr,pos),从第pos位开始查找str中subStr首次出现的位置 | |
LENGTH | 字符串长度 | |
SYSDATE | 系统时间 | |
LAST_DAY | 月份最后一天 LAST_DAY(date) | |
NEXT_DAY | 下一个指定日期,星期日=1,星期一=2,依此类推 NEXT_DAY(date, char) | SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL; |
ADD_MONTHS | ADD_MONTHS(date, integer) 添加月份 | |
MONTHS_BETWEEN | 两日期月份差 MONTHS_BETWEEN(date1, date2) | |
EXTRACT (datetime) | 提取日期时间 EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR } FROM { expr } ) | SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; |
ROUND (date) | 四舍五入 ROUND(date [, fmt ]) | |
TRUNC (date) | 截取日期 | SELECT trunc(sysdate, ‘yyyy’) FROM dual; – 返回当年第一天 SELECT trunc(sysdate, ‘mm’) FROM dual; – 返回当月第一天 SELECT trunc(sysdate, ‘dd’) FROM dual; – 返回当前年月日 SELECT trunc(sysdate, ‘hh’) FROM dual; – 返回当前日期截取到小时,分秒补0 SELECT trunc(sysdate, ‘mi’) FROM dual; – 返回当前日期截取到分,秒补0 |
TO_CHAR (datetime) | 日期转字符串 | to_char(sysdate,‘yyyy-MM-dd’) to_char(sysdate,‘yyyy-MM-dd HH:mm:ss’) |
TO_DATE | 转日期 | to_date(‘2005-10-02’,‘yyyy-MM-dd’) to_date(‘2005-10-02,13:25:59’,‘yyyy-MM-dd HH24:mi:ss’) |
CAST | 类型转换 CAST({ expr | MULTISET (subquery) } AS type_name [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, ‘nlsparam’ ] ]) | select cast(1212345.61 as varchar2(15)) FROM dual; |
BIN_TO_NUM | 二进制转十进制 BIN_TO_NUM(expr [, expr ]… ) | |
DECODE | DECODE(expr, search, result [, search, result ]… [, default ]) | SELECT DECODE(3,3,1) FROM DUAL; – 1 SELECT DECODE(3,2,1) FROM DUAL; – NULL SELECT DECODE(3,3,1,0) FROM DUAL; – 1 SELECT DECODE(3,2,1,0) FROM DUAL; – 0 |
NVL | NVL(expr1, expr2) | SELECT NVL(‘S’, ‘R’) FROM DUAL; – S SELECT NVL(NULL, ‘R’) FROM DUAL; – R |
NVL2 | NVL2(expr1, expr2, expr3) | SELECT NVL2(‘S’, ‘R1’, ‘R2’) FROM DUAL; – R1 SELECT NVL2(NULL, ‘R1’, ‘R2’) FROM DUAL; – R2 |
COALESCE | 至少两个参数,返回第一个不为null的expr COALESCE(expr [, expr ]…) | SELECT COALESCE(NULL, ‘SS’, NULL, ‘DD’) FROM DUAL; – SS |
NULLIF | NULLIF(expr1, expr2) expr1=expr2返回null,否则返回expr1 | SELECT NULLIF(3, 3) FROM DUAL; – NULL SELECT NULLIF(3, 5) FROM DUAL; – 3 |
LISTAGG | 拼接字符串 LISTAGG (measure_column [, ‘delimiter’]) WITHIN GROUP (ORDER BY order_by_clause) [OVER ( query_partition_clause)] | SELECT age,LISTAGG(name, ‘;’) WITHIN GROUP (ORDER BY age) OVER(PARTITION BY AGE) FROM temp_dep; |
WM_CONCAT | 拼接字符串 | SELECT WM_CONCAT(NAME) FROM temp_dep ORDER BY age; SELECT depid,WM_CONCAT(NAME) FROM temp_dep GROUP BY depId ORDER BY depid; |
MERGE INTO | MERGE [ hint ] INTO [ schema. ] { table | view } [ t_alias ] USING { [ schema. ] { table | view } | subquery } [ t_alias ] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error_logging_clause ] ; | MERGE INTO people_target pt USING people_source ps ON (pt.person_id = ps.person_id) WHEN matched THEN UPDATE SET pt.first_name = ps.first_name, pt.last_name = ps.last_name, pt.title = ps.title WHEN NOT matched THEN INSERT (pt.person_id, pt.first_name, pt.last_name, pt.title) VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title); |