但行好事
莫论前程❤

Oracle系列教程–函数详解

Oracle之中有一点比较麻烦,即使要验证字符串,也必须编写完整的SQL语句,所以在Oracle数据库之中为了用户的查询方便,故专门提供了一个“dual”的虚拟表

通用函数(核心)

通用函数主要有两个:NVL()、DECODE(),这两个函数算是 Oracle自己的特色函数了;

1、NVL()函数,处理null

  • 要求查询出每个雇员的全部年薪  
SQL> SELECT ename,sal,comm,(sal+comm)*12 年薪 FROM emp ;

img

  • 由上可知,有雇员的年薪变为null了,而造成这种问题的关键是在于comm字段上为null,那么要想解决这个问题,就必须做一种处理:将null变为0,而这个就是NVL()函数作用。  
SELECT ename,sal,comm,(sal+comm)*12,NVL(comm,0) FROM emp ;

img

  • 看来,年薪还有为0的,下面再来看看!  
SQL> SELECT ename,sal,comm,(sal+NVL(comm,0))*12 ?êD? FROM emp ;

img

2、DECODE()函数:多数值判断

DECODE()函数非常类似于程序中的if…else语句,唯一不同的是DECODE()函数判断的是数值,而不是逻辑条件了。

decode 功能类似 switch…case…

例如:要求显示全部雇员的职位,但是这些职位要求替换成中文显示:

* CLERK:办事员;

* SALESMAN:销售;

* MANAGER:经理;

* ANALYST:分析员;

* PRESIDENT:总裁;

这种判断肯定是逐行判断,故此时必须采用DECODE()函数,而此函数语法如下:

DECODE(数值|列,判断1,显示值1,判断2,显示值2,判断3,显示值3,...)

实现显示的操作功能  

SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职务 FROM emp ;

DECODE()函数是整个Oracle之中最具有特点的函数,必须掌握!!!**

转换函数(核心)

现在已经接触到了Oracle数据库之中的三种数据:数字(NUMBER)、字符串(VACHAR2)、日期(DATE),转换函数的主要功能是完成这几种数据间的相互转换的操作,一共有三种转换函数:

  • TO_CHAR(字符串|列,格式字符串):将日期或者是数字变为字符串显示;
  • TO_DATE(字符串,格式字符串):将字符串变为DATE数据显示;
  • TO_NUMBER(字符串):将字符串变为数字显示;

    1、TO_CHAR()函数

在之前查询过当前的系统日期时间:

SELECT SYSDATE FROM dual ;

这个时候是按照“日-月-年”的格式显示,很明显此格式不符合于正常的思路,正常是“年-月-日”,

  • TO_CHAR()函数,但是使用此函数的话需要一些格式字符串:年(yyyy),月(mm),日(dd)
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year,TO_CHAR(SYSDATE,'mm') month,TO_CHAR(SYSDATE,'dd') day FROM dual ;
  • 但是这个时候的显示数据之中可以发现会存在前导0,如果要想消除这个0的话,可以加入一个“fm”。  
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM dual ;
  • 正常人都加0,故这个标记知道就行了,可是在Oracle之中,DATE里面是包含了时间的,但是之前的代码没有显示出时间,要想显示时间,则需要增加标记:
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh:mi:ss')day FROM dual ; //12时制
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual ;
  • 一定要注意:使用TO_CHAR()函数之后,所有的内容都是字符串,不再是之前的DATE型数据,TO_CHAR()函数也可以用数字的格式化上,此时每一个“9”表示一位数字的概念,而不是数字9概念。
SELECT TO_CHAR(11157191115719,'999,999,999,999,999') FROM dual ;
SELECT TO_CHAR(11157191115719,'L999,999,999,999,999') FROM dual ;

其中的字母“L”,表示的是“Locale”的含义,即:当前的所在语言环境下的货币符号。

2、TO_DATE()函数

此函数的主要功能是将一个字符串变为DATE型数据

col TO_DATE('1988-8-8','yyyy-mm-dd') format a50 ;
SELECT TO_DATE('1988-8-8','yyyy-mm-dd') FROM dual ;

一般此函数在更新数据时,使用比较多!

3、TO_NUMBER()函数:基本不用!

  • TO_NUMBER()函数一看就知道是将字符串变为数字的:  
SELECT TO_NUMBER('1') + TO_NUMBER('2') + TO_NUMBER('3') FROM dual ;

但是在Oracle之中是很智能的,故以上功能不使用TO_NUMBER()函数也可以完成。

SELECT '1' + '2' + '3' FROM dual ;

重点:

* TO_NUMBER()函数,基本已经不考虑了;

* TO_CHAR()函数,是重点;

* TO_DATE()函数,是次重点。

字符函数:

字符函数的功能主要是进行字符串数据的操作,下面给出几个字符函数:

  • UPPER(字符串|列):将输入的字符串变为大写返回;
SELECT UPPER('hello') FROM dual ;
  • LOWER(字符串|列):将输入的字符串变为小写返回;
SELECT LOWER('ename') FROM emp ;
  • INITCAP(字符串|列):开头首字母大写;
SELECT INITCAP(ename) FROM emp ;
  • LENGTH(字符串|列):求出字符串的长度;
SELECT ename,LENGTH(ename) FROM emp ;
  • REPLACE(字符串|列):进行替换;
# 要求查询出雇员姓名长度正好是5的信息;
SELECT ename,LENGTH(ename) FROM emp WHERE LENGTH(ename)=5 ;

# 使用字母“_”替换姓名中所有字母“A”的信息;
col REPLACE(ename,'A','_') FORMAT A50 ;
SELECT REPLACE(ename,'A','_') FROM emp ;
  • SUBSTR(字符串|列,开始点[结束点]):字符串截取:

字符串截取操作有两种语法:

|-语法一:SUBSTPR(字符串|列,开始点),表示从开始点一直截以到结尾;

|-语法二:SUBSTPR(字符串|列,开始点,结束点),表示从开始点一直截以到结束点,截取部分内容;

# 语法一:SUBSTPR(字符串|列,开始点),表示从开始点一直截以到结尾;
SELECT ename,SUBSTR(ename,3) FROM emp ; //从第3个字符开始一直到结尾!

# 语法二:SUBSTPR(字符串|列,开始点,结束点),表示从开始点一直截以到结束点,截取部分内容;

SELECT ename,SUBSTR(ename,0,3) FROM emp ; //截取前3个字符!
SELECT ename,SUBSTR(ename,1,3) FROM emp ; //截取前3个字符!

要求截取每个雇员姓名的后三个字母;

  • 正常思路:通过长度-2确定开始点
SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;
  • 新思路:设置负数,表示从后指定截取位置
SELECT ename,SUBSTR(ename,-3) FROM emp ;

数字函数

数字函数一共有3个:

  • ROUND(数字|列[,保留小数的位数]):四舍五入的操作;
# 验证ROUND()函数  
SELECT ROUND(903.5) FROM dual ;

SELECT ROUND(903.5),ROUND(-903.53567) FROM dual ;
SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1) FROM dual ;
SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1),ROUND(903.53567,2) FROM dual ;//保留2位
  • TRUNC(数字|列[,保留小数的位数]):舍弃指定位置的内容;
# 验证TRUNC()函数
SELECT TRUNC(903.5),TRUNC(-903.53567),TRUNC(903.53567,-1),TRUNC(903.53567,2) FROM dual ;
# 验证MOD()函数    
SELECT MOD(10,3) FROM dual ;
  • MOD(数字1,数字2):取模,取余数;
SELECT MOD(10,3) FROM dual ;

日期函数

如果现在要想进行日期的操作,则首先有一个必须要解决的问题,就是如何取得当前的日期, 这个当前日期可使用“SYSDATE”取得,代码如下:

SELECT SYSDATE FROM dual ;
--> 28-8月 -13

除了以上的当前日期之外,在日期中也可以进行若干计算:

* 日期 + 数字 = 日期,表示若干天之后的日期;

SELECT SYSDATE+3,SYSDATE+300 FROM dual ;

* 日期 – 数字 = 日期,表示若干天的日期;

SELECT SYSDATE-3,SYSDATE-300 FROM dual ;

* 日期 – 日期 = 数字,表示的是两个日期的天数,但是肯定是大日期 – 小日期;

范例14:求出每个雇员到今天为止的雇佣天数;

SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;

注:而且很多编程语言之中,也都会提出一种概念,日期可以通过数字表示出来!

除了以上三个公式之外,也提供了以下四个操作函数:

* LAST_DAY(日期):求出指定日期的最后一天;

范例15:求出本月的最后一天日期

SELECT LAST_DAY(SYSDATE) FROM dual ;

* NEXT_DAY(日期,星期数):求出下一个指定星期X的日期;

范例16:求出下一个周一

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual ;

* ADD_MONTHS(日期,数字):求出若干月之后的日期;

范例17:求出4个月后是何时

SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;

* MONTHS_BETWEEN(日期1,日期2):求出两个日期之间所经历的月份;

范例18:求出每个雇员到今天为止的雇佣月份;

SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ;
SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ;

在所有的开发之中,如果是日期的操作,建议使用以上函数,因为这些函数可以避免闰年的问题。

赞(1) 打赏
未经允许不得转载:刘鹏博客 » Oracle系列教程–函数详解
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏