三、单行函数
--什么是单行函数?
单行函数:function_name [(arg1, arg2,...)]
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
说明:这里会尽量给出每个函数的运行结果,以防止没说明白,可以对照命令和结果理解清楚
1.字符函数
(1)大小写控制函数(lower, upper, initcap)
SQL> select lower('HeLLO World')转小写,upper('HeLLO World')转大写,initcap('hello world') 首字母大写 from dual;
-->结果:
大小写控制函数(2)字符控制函数(CONCAT,SUBSTR,LENGTH/LENGTHB,INSTR,LPAD|RPAD,TRIM,REPLACE)
--concat(a,b),拼接字符串a和b,在前面讲连接符“||”的时候说过了
SQL> select concat('Hello', ' World') from dual;
--substr(a,b,c) 从a中,第b位开始取,取c位
SQL> select substr('hello world',3,4) from dual;
-->结果:
从第3位开始截取4位(最后有个空值)--substr(a,b) 从a中,第b位开始取,取右边的所有字符(默认指定了第三个参数到最后)
SQL> select substr('hello world',3) from dual;
-->结果:
从第3位开始取右边的所有字符--length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符数,lengthb('Hello World') 字节数 from dual;
-->结果:(英文的话,是一样的)
英文,字符数=字节数SQL> select length('中国') 字符数,lengthb('中国') 字节数 from dual;
-->结果:(中文的话,字节说大于字符数,至于大多少,要看编码)
中文, 字符数<字节数--instr: 在母串中,查找子串;如果找到,返回下标(从1开始);否则返回0
SQL> select instr('Hello World','ll') from dual;
-->结果:
注意,只是返回起始下标(从1开始),没有返回0--lpad 左填充 rpad 右填充
SQL> select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;
-->结果:
左填充,右填充--trim 去掉前后指定的字符(注意语法,参数里要用from)
SQL> select trim('H' from 'Hello WorldH') from dual;
-->结果:
注意,trim参数里要用from--replace 替换
SQL> select replace('Hello World','l','*') from dual;
-->结果:(注意第二个参数,如果是lo,则结果是“Hel* World”)
replace 替换字符2.数字函数
--ROUND:四舍五入(不仅可以对数字,也可以对日期,后面会说)
SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三, ROUND(45.926, -1) 四,ROUND(45.926, -2) 五 from dual;
-->结果:
ROUND,可以自己琢磨参数,正负的含义--TRUNC:截断(不仅可以对数字,也可以对日期,后面会说)
SQL> select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,
TRUNC(45.926, -1) 四,TRUNC(45.926, -2) 五 from dual;
-->结果:
TRUNC,可以自己琢磨参数,正负的含义--MOD:求余
SQL> select MOD(1600, 300) from dual;
-->结果:100
3.日期函数
--先来查一下系统的时间
SQL> select sysdate from dual;
-->结果:(这个格式之前说过了,也说了怎么改,参见一,基本查询)
格式:DD-MON-RR注:日期的数学运算
1.在日期上加上或减去一个数字结果仍为日期。
2.两个日期相减返回日期之间相差的天数。
3.可以用数字除24来向日期中加上或减去小时。
--昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
-->结果:(运行日期:2018-08-04)
对日期进行加减数值注:日期的加法,只能是数字,你加上个另一个日期是个啥子?
减法可以是两个日期相减,返回天数
--计算员工的工龄: 天 星期 月 年
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月, (sysdate-hiredate)/365 年 from emp;
-->结果:(这只是算个大概,运行日期:2018-08-04)
计算员工的工龄,大概计算--MONTHS_BETWEEN :计算两个日期之间的月份数,会自动考虑(28/29,30/31)
SQL> select ename,hiredate,(sysdate-hiredate)/30 方式一,MONTHS_BETWEEN(sysdate,hiredate) 方式二 from emp;
-->结果:(工龄:月份数,运行日期:2018-08-04)
MONTHS_BETWEEN ,大值在前返回正数,小值在前返回负数--ADD_MONTHS:计算指定多少个月前(负数)/后(正数)是什么日期
SQL> select ADD_MONTHS(sysdate,-119) "119个月前", ADD_MONTHS(sysdate,119) "119个月后" from dual;
-->结果:(运行日期:2018-08-04)
ADD_MONTHS,负值向前计算,正值向后计算--LAST_DAY:当前月的最后一天
SQL> select LAST_DAY(sysdate) from dual;
-->结果:
LAST_DAY,当前月的最后一天--next_day:返回指定标识的下一个日期
SQL> select next_day(sysdate,'星期日') from dual;
-->结果:(运行日期:2018-08-04)
next_day,指定标识的下一个日期注:
1.这里的第二个参数,中文状态只能是:星期一~星期日(不能是周一,礼拜一等)。英文状态,只能是monday~sunday
2.SQL> select * from v$nls_parameters可以查询当前Oracle数据库的参数(之前说过了):
NLS_LANGUAGE --> SIMPLIFIED CHINESE 表示简体中文
--对日期进行四舍五入
SQL> select round(sysdate,'month'), round(sysdate,'year') from dual;
-->结果:(运行日期:2018-08-04)
round,也可以对日期使用说明:
现在是2018-08-04,月未过半,故对month四舍五入是2018-08-01,而年已过半,故对year四舍五入是2019-01-01。
--对日期进行截断
SQL> select trunc(sysdate,'month'), trunc(sysdate,'year') from dual;
-->结果:(运行日期:2018-08-04)
trunc,也可以对日期使用4.转换函数
--to_char:将字段,转换为指定格式的字符串,不限于日期
--格式化日期,变成字符串
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-->结果:
to_char,可以格式化日期--能不能直接将日期格式化为:2018-08-04 23:50:10 今天是 星期六,可以的
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
-->结果:
to_char,在格式化字符的时候,可以添加其他字符说明:
在格式化日期的时候,可以使用双引号向日期中添加字符
DD "of" MONTH --> 12 of OCTOBER
注:
1.格式:
1)必须包含在单引号中而且大小写敏感。
2)可以包含任意的有效的日期格式。
3)日期之间用逗号隔开。
2.日期格式的元素
常用的日期格式化元素--格式化数字,变成字符串
--查询员工的薪水: 货币代码 两位小数 千位符
SQL> select to_char(sal,'L9,999.99') from emp;
-->结果:
to_char,也可以格式化数值注:
在TO_CHAR 函数中经常对数值使用的几种格式:
常用的数值格式化元素--TO_NUMBER(char[, 'format_model']):将字符串转化为数值
SQL> select to_number('10000') from dual;
-->结果:10000
SQL> select to_number('$7,356.87', '$9,999.99') from dual;
-->结果:( '$9,999.99' --> '$99,999.99'也可以,自己琢磨)
把格式匹配正确即可--TO_DATE(char[, 'format_model']):将字符转换成日期
SQL> select to_date('20180805 00:09:53', 'yyyymmdd hh24:mi:ss') from dual;
-->结果:
需要正确匹配日期格式SQL> select to_date('05-8月-18') from dual;
-->结果:
省略,日期format的情况说明:
如果想省略to_date的第二个参数,传入的字符串必须和Oracle数据库默认的格式(DD-MON-RR)一致
补充:关于隐式转换(不推荐,尽量显示转换)
使用上面的函数进行的转换都是显示转换,还有一种转换是隐式转换
要想完成隐式转换,则传入的值必须符合格式要求。
举例:
SQL> select * from emp where deptno='10';
-->结果:(Oracle会自动将’10‘转化为,数字10,因为deptno的类型是number)
隐式转换,‘10’-->105.通用函数(适用于任何数据类型,同时也适用于空值)
--滤空函数nvl,nvl2
--nvl(a,b)当a=null时,返回b; 否则返回自身
--nvl2(a,b,c) 当a=null时,返回c; 否则返回b
SQL> select ename, sal*12+nvl2(comm,comm,0) from emp;
-->结果:
使用滤空函数nvl2如果不加滤空函数会怎样?
SQL> select ename, sal*12+comm from emp;
-->结果:(这显然不是我们想要的,回顾我们说的null的第一个说明,包含null的表达式都是空值)
未使用滤空函数说明:
nvl2是nvl的增强,在Oracle中后面带2的都是不带2的增强,常见的还有如数据类型varchar2和varchar
--nullif(a,b) 当a=b时,返回null,否则返回a
SQL> select nullif('abc','abc') from dual;
-->结果:
nullif(a,b) 当a=b时,返回nullSQL> select nullif('abc','adbc') from dual;
-->结果:
nullif(a,b) 当a!=b时,返回a--COALESCE (expr1, expr2, ..., exprn): 返回第一个不为null的值
SQL> select comm,sal,COALESCE(comm,sal) from emp;
-->结果:
COALESCE (expr1, expr2, ..., exprn): 返回第一个不为null的值注:
1.COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
2.如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。
3.即:找第一个不为空的值。
--CASE 表达式(条件表达式,适用所有对SQL99标准实现的数据库):即IF-THEN-ELSE 逻辑
--报表:涨后的工资(总裁1000 经理800 其他400)
SQL> select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后薪水
from emp;
-->结果:(SQL的书写规范,和其他语言一样,都很重要)
使用case-when-else-end实现涨薪注:
case表达式语法:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
--decode函数:Oracle提供的一个简化的case-when-else-end实现,只在Oracle中能用
--报表:涨后的工资(总裁1000 经理800 其他400)
SQL> select ename,job,sal 涨前薪水, decode(job,'PRESIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后薪水
from emp;
-->结果:和case-when-else-end逻辑实现的一样
注:
decode函数的参数说明:
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
网友评论