Oracle常用函数

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);