oracle常用函数及关键字
lower 转小写、
upper 转大写、
initcap 每个单词首字母大写、
concat 连接两个字符串、
substr 截取一段字符串,从..到..、
instr 返回指定字符所在的下标位置、
length 返回字符串长度、
lpad 左填充、
rpad 右填充、
trim 去除指定位置的字符(头和尾)、
replace 将...替换为...
mod 求余、
trunc 截断、
round 四舍五入、
SYSDATE、months_between、add_months、next_day、last_day、round、trunc、extract
to_char、to_date、to_number
nvl、nvl2、nullif、coalesce、
case、decode
字符函数:
函数 | 说明 | 例子 | 结果 | |
---|---|---|---|---|
转小写 | LOWER(列名|string) | 将 ”列名或string“ 转为小写 | LOWER(‘Hello Oracle’) | hello oracle |
转大写 | UPPER(列名|string) | 将 ”列名或string“ 转为大写 | UPPER('Hello Oracle') | HELLO ORACLE |
首字母大写 | INITCAP(列名|string) | 将 ”列名或string“ 每个单词的首字母大写 | INITCAP('hello ORACLE') | Hello Oracle |
字符串连接 | CONCAT(x,y) | 连接两个值,相当于 || ,可以是列名,也可以是字符串 | CONCAT(CONCAT(ENAME,'的工作是'),JOB) | ENAME的工作是JOB |
截取字符串 | SUBSTR(x,start[,length]) | 返回第一个参数x中,从start位置开始,截取length个长度字符,start为负,从后向前数向右取length个长度 start:正数是从左到右,负数是从右到左。 | SUBSTR('ABCDEF',2,4) | BCDE |
SUBSTR('ABCDEF',2) | BCDEF | |||
SUBSTR('ABCDEF',4,-1) | NULL | |||
select substr('abcdef', -4,-1) from dual; | NULL | |||
从倒数第四个开始,向右数2个 | SUBSTR('ABCDEF',-4,2) | CD | ||
SUBSTR('ABCDEF',2,0) | NULL | |||
SUBSTR('ABCDEF',-10,2) | NULL | |||
字符串长度 | LENGTH(列名|string) | 返回括号内的字符长度 | LENGTH('oracle') | 6 |
获取字符下标 | INSTR(s1, s2, start_position,n2) | 返回要截取的字符串s2在源字符串s1中的位置下标,从start_position开始,第n2次出现的位置 | INSTR('ABCDE1234','12') | 6 |
INSTR('ABCDE1234','EF') | 0 | |||
INSTR('ABCDEBC1234BC','BC') | 2 | |||
从第三个数向右数,第一次出现BC的位置 | INSTR('ABCDEBC1234BC','BC',3) | 6 | ||
从第三个数向右数,第2次出现BC的位置 | INSTR('ABCDEBC1234BC','BC',3,2) | 12 | ||
负号代表方向,从右边向左数7个位置,然后从当前位置在向左数第1次出现BC的位置 | INSTR('ABCDEBC1234BC','BC',-7,1) | 6 | ||
负号代表方向,从右边向左数7个位置,然后从当前位置在向左数第1次出现EBC的位置 | INSTR('ABCDEBC1234BC','EBC',-7,1) | 5 | ||
INSTR('ABCDEBC1234BC','BC',-20,3) | 0 | |||
INSTR('ABCABCDABCD','BC',-3,1) | 9 | |||
INSTR('ABCABCDABCD','BC',-2,1) | 9 | |||
INSTR('ABCABCDABCD','BC',-1,3) | 2 | |||
左填充 | LPAD(s1,n1,s2) | 返回s1被s2从左面填充到n1长度后的字符串 | LPAD('500',6, '*' ) | ***500 |
LPAD('500',6) | 500 | |||
LPAD('500',2) | 50 | |||
右填充 | RPAD(s1,n1,s2) | 返回s1被s2从右面填充到n1长度后的字符串 | RPAD('500',6, '*' ) | 500*** |
RPAD('500',6) | 500 | |||
RPAD('500',2) | 50 | |||
去除头尾字符串 | TRIM(s1 FROM s2) | 把s2的两边截去s1字符串,缺省截去空格。 | TRIM(' Tech ') | Tech |
去掉Tech两头的空格 | TRIM(' ' FROM ' Tech ') | Tech | ||
LEADING去掉头部的 | TRIM(LEADING '0' FROM '00123') | 123 | ||
TRAILING尾去掉1 | TRIM(TRAILING '1' FROM 'Tech1') | Tech | ||
BOTH首尾都去掉 | TRIM(BOTH '1' FROM '123Tech111') | 23Tech | ||
不写表示去除首尾1 | TRIM('1' FROM '123Tech111') | 23Tech | ||
替换 | REPLACE(s1,s2,s3) | 把s1中的s2用s3替换 | REPLACE('abc',‘b’,‘d’) | adc |
REPLACE('abc',‘m’,‘d’) | abc | |||
REPLACE('abc',‘b’) | ac | |||
数值函数:
函数 | 说明 | 例子 | 结果 | |
---|---|---|---|---|
四舍五入,大约 | ROUND(列|表达式,n) | 将列或表达式的数值四舍五入到小数点后第n位 | ROUND(45.926,2) | 45.93 |
ROUND(45.926) | 46 | |||
-1看小数点左边个位 | ROUND(55.926,-1) | 60 | ||
-2看小数点左边十位 | ROUND(255.926,-2) | 300 | ||
截断,截去 | TRUNC(列|表达式,n) | 将列或表达式的数值截取到小数点后第n位 | TRUNC(45.926,2) | 45.92 |
TRUNC(45.926) | 45 | |||
TRUNC(55.926,-1) | 50 | |||
TRUNC(285.926,-2) | 200 | |||
取余 | MOD(x,y) | 返回 x 除以 y 后的余数 | MOD(5.2,3) | 2.2 |
MOD(5.6,3) | 2.6 | |||
MOD(5.0,3) | 2 | |||
MOD(5,3) | 2 | |||
向上取整 | CEIL(X) | X向上取整 | CEIL(1.9) | 2 |
CEIL(1.0) | 1 | |||
CEIL(1.01) | 2 | |||
向下取整 | FLOOR(X) | X向下取整 | FLOOR(1.9) | 1 |
FLOOR(1.0) | 1 | |||
FLOOR(1.01) | 1 |
日期函数:
函数 | 说明 | 例子 | 结果 | |
---|---|---|---|---|
系统当前日期和时间 | SYSDATE | 返回系统日期 | ||
计算两个日期相差的月数 | MONTHS_BETWEEN(x,y) | 返回日期 x 和日期 y 相差的月数 | MONTHS_BETWEEN(SYSDATE,TO_DATE('1985-12-31','YYYY-MM-DD')) | 398.450511499403 |
当前日期加多少月后的日期 | ADD_MONTHS(x,y) | 返回当前日期+y个月后的日期 | ADD_MONTHS(SYSDATE,3) 当时的SYSDATE为:2019/3-13 23:18:10 | 2019/6/13 23:18:10 |
x日期后第一周指定day的日期 | NEXT_DAY(x,day) | 返回 x 日期之后下一个星期的什么日期 | NEXT_DAY(SYSDATE,'星期一') day 格式可以为: 'Monday' 星期一 'Tuesday' 星期二 'wednesday' 星期三 'Thursday' 星期四 'Friday' 星期五 'Saturday' 星期六 'Sunday' 星期日
| 当前日期的下一个星期一是多少号 |
所在月份的最后一天是多少号 | LAST_DAY( x ) | 返回x所在月份的最后一天 | LAST_DAY(TO_DATE('2006-02-02','YYYY-MM-DD')) | 2006/2/28 |
日期四舍五入 | ROUND(日期,格式) | 按月四舍五入 | ROUND(TO_DATE('2017-10-17','YYYY-MM-DD'),'YY') 2017-10-17四舍五入的年份 -- 月份大于6年份进1 | 2018/1/1 |
按天四舍五入 | ROUND(TO_DATE('2017-10-17','YYYY-MM-DD'),'MM') 2017-10-17四舍五入的月份 -- 天数大于15月份进1 | 2017/11/1 | ||
按小时四舍五入 | ROUND(TO_DATE('2017-10-17 15:30:07','YYYY-MM-DD HH24:MI:SS'),'DD') 2017-10-17四舍五入的日期天数 (四舍五入到几号)--时间大于12点天数进1 | 2017/10/18 | ||
日期截断 | TRUNC(日期,格式) | 截断年 | TRUNC(TO_DATE('2017-10-17','YYYY-MM-DD'),'YY') | 2017/1/1 |
截断月 | TRUNC(TO_DATE('2017-10-17','YYYY-MM-DD'),'MM') | 2017/10/1 | ||
截断日 | TRUNC(TO_DATE('2017-10-17 18:33:07','YYYY-MM-DD HH24:MI:SS') | 2017/10/17 | ||
返回日期特定部分 | EXTRACT(格式 FROM 日期) | 年份 | EXTRACT(YEAR FROM SYSDATE) | 2019 |
格式为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中YEAR、MONTH、DAY可以为DATE类型匹配,也可以与TIMESTAMP类型匹配;但是HOUR、MINUTE、SECOND必须与TIMESTAMP类 | 月份 | EXTRACT(MONTH FROM SYSDATE) | 3 | |
几号 | EXTRACT(DAY FROM SYSDATE) | 14 | ||
显示转换函数:
函数 | 说明 | 例子 | 结果 | |
---|---|---|---|---|
日期格式转换 | TO_CHAR(日期,‘格式’) | 把日期转为特定格式 YYYY完整的年份数字 | TO_CHAR(SYSDATE,'YYYY') | 2019 |
格式可以为:YYYY、MM/YY、HH24:MI:SS AM、YYYY-MM-DD | TO_CHAR(SYSDATE,'YY') | 19 | ||
YEAR年份的英文表示 | TO_CHAR(SYSDATE,'YEAR') | TWENTY NINETEEN | ||
月 MM用两位数字来表示 | TO_CHAR(SYSDATE,'MM') | 03 | ||
MONTH月份的全名 | TO_CHAR(SYSDATE,'MONTH') | 3月 | ||
日
| TO_CHAR(SYSDATE,'DD') | 14 | ||
星期 DAY星期几 | TO_CHAR(SYSDATE,'DAY') | 星期四 | ||
时 | TO_CHAR(SYSDATE,'HH24') | 17 | ||
分 | TO_CHAR(SYSDATE,'MI') | 28 | ||
秒 | TO_CHAR(SYSDATE,'SS') | 22 | ||
TO_CHAR(HIREDATE,'YYYY-MM-DD') | 1981-02-20 | |||
数值格式转换 | TO_CHAR(NUMBER,‘fmt’) 9:一位数字 0:显示前导零 $:显示美元符号 L:显示本地货币符号 .:显示小数点 ,:显示千位符 | 把number转化为fmt格式的 | TO_CHAR(SAL,'L9,999') | |
SELECT TO_CHAR('1600.00','L9,999') | ¥1,600 | |||
SELECT TO_CHAR('600.00','L9,999') | ¥600 | |||
转为数值 | TO_NUMBER(char[,'fmt']) | 将char转为fmt格式 | TO_NUMBER('0012')+2 FROM | 14 |
TO_NUMBER('$1273.281','$9999.999') | 1273.281 | |||
将字符串转为日期 | TO_DATE(char[,‘fmt’]) | TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS'), | 2015/3/18 13:13:13 |
通用函数:
函数 | 说明 | 例子 | 结果 | |
---|---|---|---|---|
对NULL的处理 | NVL(expr1,expr2) | 如果expr1不是NULL,返回expr1,否则返回expr2 | NVL(COMM,0) | |
NVL2(expr1,expr2,expr3) | 如果expr1不是null,返回expr2,否则返回expr3 | NVL2(COMM,COMM,0) | ||
NULLIF(expr1,expr2) | 如果两个表达式相等,返回null,否则,返回第一个表达式 | NULLIF('abc','abc') | ||
NULLIF('abc','ab') | abc | |||
COALESCE(表达式1,表达式2,...表达式n) | 返回第一个不为null的值 | COALESCE(NULL,NULL,NULL,'ABC',NULL,'HELLO') | ABC | |
COALESCE(COMM,0) | 0 | |||
条件处理函数2种格式
| CASE
| 类似于java的 if else | CASE WHEN COMM=300 THEN '低' WHEN COMM=500 THEN '中' WHEN COMM=1400 THEN '高' ELSE '无' END | |
CASE 条件表达式 WHEN 表达式 THEN 结果 WHEN 表达式 THEN 结果 ELSE 结果 END | CASE COMM WHEN 300 THEN '低' WHEN 500 THEN '中' WHEN 1400 THEN '高' ELSE '无' END | |||
DECODE(字段|表达式,条件1,结果1,条件2,结果2,...,缺省值) | DECODE(DEPTNO,10,'销售部',20,'技术部',30,'管理部','无') |