ORACLE内置函数使用

1、数学函数

ABS(X):返回X的绝对值

CEIL(X):返回大于等于X的最小整数，即向上取整

FLOOR(X):返回小于等于X的最大整数，即向下取整

SQRT(X):返回X的平方根

POWER(X,Y):返回X的Y次幂

EXP(X):返回e的X次幂

LN(X):返回X的对数

LOG(X,Y):返回以X为底Y的对数

MOD(X,Y):返回X/Y的余数

SIGN(X):返回X的符号，正数返回1,0返回0，负数返回-1

ROUND(X[,Y]):对X四舍五入，Y为可选参数，表示四舍五入到小数点后第几位，若Y为负值，则表示四舍五入到小数点前第几位

TRUNC(X[,Y]):截取X，Y为可选参数，表示截取到小数点后第几位，若Y为负值，则表示截取到小数点前第几位

2、字符串函数

REPLACE(string,from_str,to_str)：字符串替换函数，字符串级别的替换，
把from_str整个替换成to_str
例：select replace('ABCDCBA','BCD','H') from dual;
输出：AHCBA
将字符串 ABCDCBA中的 BCD 整个替换成 H,所以结果就是AHCBA

TRANSLATE(string,from_str,to_str)：字符串替换函数，字符级别的替换，把from_str跟to_str字符一一对应替换，
如果from_str比to_str长，超出的字符将从string中去掉，因为to_str中没有对应可替换的字符，to_str不能为空，
translate中的任何参数为NULL，结果都是NULL
例：select translate('ABCDCBA','BCD','H') from dual;
输出：AHHA
将字符串 ABCDCBA 中的 B 替换成 H，CD 从字符转中去掉，因为没有可替换的字符，所以结果是AHHA

INSTR(string1,string2[,start_position[,nth_appearence]])：字符串查找函数，查找string2在string1中出现的位置。
string2从string1中检索，从string1的第start_position（位置）位开始检索，检索第nth_appearence（几次）出现的位置,
start_position和nth_appearence是可选参数
例1：select instr('ABCDCBA','C') from dual;
输出：3
在字符串 ABCDCBA 中检索 C 第一次出现是第3个位置

例2：select instr('ABCDCBA','C',4) from dual;
输出：5
在字符串 ABCDCBA 中从第4位开始检索 C，第一次出现的位置是第5位，所以输出5

例2：select instr('ABCDCBA','C',1,2) from dual;
输出：5
在字符串 ABCDCBA 中从第1位开始检索 C，检索第2次出现C的位置，是第5位

PS:instr(字段名，‘字符串’)>0 效果类似与 like
例：
select * from emp where emp_name like '%张%';
select * from emp where instr(emp_name,'张')>0;

CONCAT(char1,char2):字符串连接函数，连接两个字符串，与||效果相同
例：select concat('my name ','is y') from dual;
输出：my name is y

TRIM()
1、TRIM(expr1)：去除指定字符前后空格 
2、TRIM(LEADING|TRAILING|BOTH expr1 from expr2)：从指定字符串expr2的的前面|后面|前后去掉字符expr1，
只能去除单个字符
例1：select trim(leading 'A' from 'ABCDCBA') from dual; 
输出：BCDCBA
从字符串ABCDCBA前面去掉A

例2：select trim(trailing 'A' from 'ABCDCBA') from dual;
输出：ABCDCB
从字符串ABCDCBA后面去掉A

例3：select trim(both 'A' from 'ABCDCBA') from dual;
输出：BCDCB
从字符串ABCDCBA前后分别去掉A

LTRIM()
1、LTRIM(expr1)：去除指定字符前面空格 
2、LTRIM(expr1,expr2):从字符串expr1前面去除与expr2匹配的字符，可以去除多个字符
例：select ltrim('ABCDCBA','ACB') from dual;
输出：DCBA
从字符串ABCDCBA前面去掉了ABCS三个字符

RTRIM()
1、RTRIM(expr1)：去除指定字符后面空格 
2、RTRIM(expr1,expr2)：从字符串expr1后面去除与expr2匹配的字符，可以去除多个字符
例：select rtrim('ABCDCBA','ACB') from dual;
输出：ABCD
从字符串ABCDCBA前面去掉了ABC三个字符

LPAD(expr1, n, expr2)：用指定字符expr2从左边开始填充字符串expr1，
参数expr1不存在的话默认用空格填充，直到expr1字符串长度满足n为止
例：select lpad('ABC',7,'H') from dual;
输出：HHHHABC

RPAD(expr1, n, expr2)：用指定字符expr2从右边开始填充字符串expr1，
参数expr1不存在的话默认用空格填充，直到expr1字符串长度满足n为止
例：select rpad('ABC',7,'H') from dual;
输出：ABCHHHH

LENGTH():返回字符串长度
例：select length('ABCDCBA') from dual;
输出：7

SUBSTR(string,start_position,substring_length):字符串截取函数,从字符串string的start_position位置开始截取字符串，
截取的字符串长度是substring_length。开始位置start_position可为正数也可为负数，
为正数时，从前面开始数开始位置，为负数时从后面开始数开始位置
例1：select substr('ABCDEF',3,2) from dual;
输出：CD
从字符串ABCDEF的第三位（C）开始截取2个字符串，所以是CD

例2：select substr('ABCDEF',-3,2) from dual;
输出：DE
从字符串ABCDEF的倒数第三位（D）开始截取2个字符串，所以是DE

例3：select substr('ABCDEF',3) from dual;
输出：CDEF
从字符串ABCDEF的第三位（C）开始截取字符串，因为没有字符串长度参数，所以默认后面的都截取

例3：select substr('ABCDEF',-3) from dual;
输出：DEF
从字符串ABCDEF的倒数第三位（D）开始截取字符串，因为没有字符串长度参数，所以默认后面的都截取

UPPER(string):字母大小写转换函数，将参数全部转换成大写字母
LOWER(string):字母大小写转换函数，将参数全部转换成小写字母
INITCAP(tring):字母大小写转换函数，将所有单词首字母转换成大写字母

3、日期函数

MONTHS_BETWEEN(date1,date2)；计算date1与date2之间有几个月，date1-date2

ADD_MONTHS(date,n):若n为正整数，则返回日期date的n月后的日期；若n为负整数，则返回日期date的n月前的日期

NEXT_DAY(date,'char'):返回date之后的下一周的指定天的日期，char的值可能是一个表示一天的书或者是一个字符串，如1,'星期一'等

LAST_DAY(date):返回date本月最后一天

ROUND(date[,'fmt']):返回格式化模式fmt四舍五入到指定单位的date,若fmt省略，则四舍五入到最近的日期。
fmt 可以是'YEAR','MONTH','DAY','HH24','MI'
SELECT ROUND(SYSDATE,'YEAR') FROM dual; --四舍五入到最近年的第一天
SELECT ROUND(SYSDATE,'MONTH') FROM dual; --四舍五入到最近月的第一天
SELECT ROUND(SYSDATE,'DAY') FROM dual; --四舍五入到最近周的第一天（星期天为一周第一天）
SELECT ROUND(SYSDATE,'HH24') FROM dual; --四舍五入到最近整点小时
SELECT ROUND(SYSDATE,'MI') FROM dual; --四舍五入到最近整点分钟

TRUNC(date[,'fmt']):返回格式化模式fmt截取到指定单位的date,若fmt省略，则截取到最近的日期。
SELECT TRUNC(SYSDATE,'YEAR') FROM dual; --本年的第一天
SELECT TRUNC(SYSDATE,'MONTH') FROM dual; --本月的第一天
SELECT TRUNC(SYSDATE,'DAY') FROM dual; --本周的第一天（星期天为一周第一天）
SELECT TRUNC(SYSDATE,'HH24') FROM dual; --当前时间整点小时
SELECT TRUNC(SYSDATE,'MI') FROM dual; --当前时间整点分钟

4、类型转换函数

TO_CHAR(number[,fmt[,nlsparam]]):将数值型参数转换成字符型数据,fmt是要转成字符的格式，nlsparam是使用的语言类型

TO_CHAR(date[,fmt[,nlsparam]]):将日期型数据转换成字符型数据,fmt是要转成字符的格式，nlsparam是使用的语言类型

TO_DATE(char[,fmt[,nlsparam]])：将字符型数据转换成日期型数据

TO_TIMESTAMP(date):将日期型数据转换成时间戳类型

TO_NUMBER(string[,fmt[,nlsparam]]):将字符串转换成数值型数据

CAST(expression AS date_type)：数据类型转换函数，将源值expression转换成date_type类型
（1）转换列和值
（2）转换集合

5、其他函数

NVL(expr1,expr2):转换NULL值为一个实际的值，expr1为null时，返回expr2

NVL2(expr1,expr2,expr3):expr1不为null时，返回expr2；expr1为null时，返回expr3。
expr1可以是任何数据类型，expr2和expr3可以是除LONG之外任何类型，若expr2和expr3的数据类型不同，
则oracle在比较它们之前将转换expr3为expr2的数据类型，除非expr3是一个NULL常数

NULLIF(expr1,expr2):比较expr1和expr2两个表达式，若相等返回NULL值，
若不相等返回expr1，expr1不能为NULL值

COALESCE(expr1,expr2,...exprn):从多个表达式中返回第一个非空表达式

DECODE(expr1,search1,result1,search2,result2,...,searchn,resultn,default):
比较表达式expr1和每一个查找表达式search,若表达式值与查找值相等，则返回结果值，
若默认值缺省，当没有查找值与表达式相等时，返回NULL值