Oracle：单行函数

在Oracle文档

中，我们可以查看Oracle资料，在Books下的sql中，有所有的sql。关于sql要看sql reference。

函数分为单行函数，多行函数，与我们数学上的定义差不多，要有0个或多个输入，1个或多个输出，可以没有输入，但是必须要有输出。

什么是单行函数？它只对一行进行变换，产生一个输出，比如求字符串的长度，滤空函数等。

如果对多行进行变换长生一个输出，就是多行函数。比如AVG求平均值函数，它求多行的平均值。

 

六种单行函数：字符，通用，转换，数值，日期，条件表达式。

字符函数：

--字符函数lower小写，upper大写，initcap首字母大写。

Select lower(‘Hello World’) 转小写,upper(‘HelloWorld’) 转大写,initcap(‘hello world’) 首字母大写 from dual;

--求子串：substr(a,b)，从a中，第b位开始取

Select substr(‘Hello World’,3) from dual;

--求子串：substr(a,b,c)，从a中，第b位开始取，取c位,

Select substr(‘Hello World’,3,4) from dual;

--求字符数length,求字节数 lengthb

Select length(‘Hello World’) 字符,lengthb(‘HelloWorld’) 字节 from dual;

--instr(a,b)在a字符串中查找b，如果找到就返回下标，下标从1开始，没找到就返回0

Select instr(‘Hello World’,’ll’) from dual;

--lpad 左填充， rpad右填充，在abcd的左边填充*，组成10为的字符串，

Selectlpad(‘abcd’,10,’*’) 左,rpad(‘abcd’,10,’*’) 右 from dual;

--trim去掉前后指定的字符，可以是空格也可以不是空格。去掉字符串HelloWorldH前后的H

Select trim(‘H’ from ‘Hello WorldH’) fromdual;

--replace 替换，使用*替换Hello World中的l。

Select replace(‘Hello World’,’l’,’*’) fromdual;

 

数值函数：

--四舍五入round

Select round(45.926,2) 一,round(45.926,1)二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;

结果：

--截断，

Select trunc(45.926,2) 一, trunc(45.926,1) 二, trunc (45.926,0) 三, trunc (45.926,-1) 四, trunc (45.926,-2) 五 from dual;

结果：

--求余MOD，求1600除以300的余数，结果是100

Select MOD(1600,300) from dual;

 

 

日期函数：

MySQL中表示一个日期：date，时间：datetime，Oracle中，只有date，没有datetime。

--查询系统时间：无法显示具体时间，因为系统格式中，没有定义具体时间的格式。

Select sysdate from dual;

--将日期转成字符串，可以指定格式。将系统时间按照格式：’yyyy-mm-ddhh24:mi:ss’转化成字符串。

Select to_char(sysdate,’yyyy-mm-ddhh24:mi:ss’) from dual;

结果：

--还有一个日期格式是timestamp时间戳，精度更高，可以精确到毫秒。Oracle将一秒钟分成了一百万份。

Select to_char(systimestamp,’yyyy-mm-ddhh24:mi:ss:ff’) from dual;

结果：

/*

除了日期表示外，还可以通过毫秒表示，表示的方式是：从1970年1月1日以来表示的一个毫秒数。

可以对日期型的数据进行数学运算。

在日期上加上或减去一个正整数结果仍为日期。

两个日期相减返回日期之间相差的天数。

可以用数字除24来向日期中加上或减去小时。

*/

-- 昨天，今天，明天

Select(sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

--计算员工的工龄：天星期月年

Select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365年 from emp;

/*

日期不可以直接加日期。

每个月的天数是不一样的，那如何算准员工入职的月数呢？通过函数来完成。

MONTHS BETWEEN函数，返回两个日期相差的月数，这个月数一定是一个准确的值。

ADD_MONTHS，向指定日期中加上若干月数。

NEXT_DAY，指定日期的下一个日期。

LAST_DAY，本月的最后一天

ROUND，日期四舍五入

TRUNC，日期截断

*/

Selectename,hiredate,MONTHS_WETWEEN(sysdate,hiredate) 月数 from emp;

--30个月后

Select add_months(sysdate,30) from dual;

Select LAST_DAY(sysdate) from dual;

--NEXT_DAY，从今天开始算，下一个星期一，

Select next_day(sysdate,’星期一’) fromdual;

--如果今天是星期一，那么下一个星期二是哪天，是这个星期的星期二。

Select next_day(sysdate,’星期二’) fromdual;

--round,trunk ，假设sysdate=’25-JUL-95’。

--按月四舍五入：结果01-JUL-95

Select round(sysdate,’MONTH’) from dual;

--按年四舍五入：结果01-JAN-96

Select round(sysdate,’YEAR’) from dual;

--按月截断：结果01-JUL-95

Select trunc(sysdate,’MONTH’) from dual;

--按年截断：结果01-JAN-95

Select trunk(sysdate,’YEAR’) from dual;

 

转换函数：指的是在不同的数据类型之间进行转换。例如将字符串转换成数字。

分为隐式转换和显示转换。隐式转换指的是Oracle会自动完成转换，不用用户手动转换，比如我们通过日期查询的时候，查询语句中的日期明显是字符串，但是却可以匹配数据库中date格式的字段，说明Oracle帮我们完成了自动转换，四种隐式转换：字符串or字符à数字，字符串or字符à日期，数字à字符串，日期à字符串；显示转换是需要用户手动转换的。

隐式转换的前提：被转换对象是可以转换的。例如abc不可以转为100。

/*

To_char(date,’format_model’)

格式：必须包含在单引号中而且大小写敏感。

可以包含任意的有效的日期格式。

日期之间用逗号隔开。

*/

日期格式元素：

其中格式的大小写不敏感。

--今天是星期一

Select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss”今天是”day’)from dual;

To_char函数中常用的几种格式：

--查询员工的薪水：按照格式：货币符号(本地区)两位小数千位符，货币是从系统环境中取出来的。

Select to_char(sal,’L9,999.99’) from emp;

 

 

通用函数：适用于任何数据类型,NVL2后面的2，以及varchar,varchar2后面的2，有2是没有2的增强。

NVL(expr1,expr2)：虑空

NVL2(expr1,expr2,expr3)：

NULLIF(expr1,expr2)

COALESCE(expr1,expr2,…,exprn)

 

--nvl(a,b,c):当a=null时，返回c，否则返回b。例如，查询年收入

Select sal*12+nvl2(comm,comm,0) from emp;

--NULLIF(a,b):当a=b时，返回null，否则返回a

Select nullif(‘abcd’,’abcd’) from dual;

--COALESCE(a,b,c,…,n):从左到右找到第一个不为null的值。

Select comm,sal,COALESCE(comm,sal) fromemp;

 

条件表达式函数：

在SQL语句使用IF-THEN-ELSE逻辑

两种方法：

CASE表达式：SQL99的语法，类似Basic，比较繁琐。

DECODE函数：Oracle自己的语法，类似Java，比较简洁。

Case:::::

--根据职位涨工资：总裁1000经历800其他400

select ename,job,sal 涨前,

         casejob when ‘PRESIDENT’ then sal+1000

                            when‘MANAGER’ then sal+800

                            elsesal+400

         end涨后

from emp;

 

DECODE:::::

select ename,job,sal 涨前,

         decode(job,’PRESIDENT’,sal+1000,

‘MANAGER’,sal+800,

sal+400) 涨后

from emp;