Oracle数据库从入门到精通 单行函数问题

视频课程:李兴华 Oracle从入门到精通视频课程

学习者:阳光罗诺

视频来源:51CTO学院

Oracle数据库从入门到精通单行函数

 

在数据库中,为了方便用户的数据开发,往往会提供一系列的支持函数,利用这些函数可以针对于数据处理。

 

例如:在进行根据姓名查询的时候,如果说姓名本身是大写字母,而查询的是小写字母,此时就不会由任何的数据结果返回。所以针对于此类情况,往往数据保存的时候或者是查询的时候对数据进行一些处理,而这些处理每一个数据库都有自己本身的函数库,利用函数可以实现特定的功能。

 

在Oracle中,对于函数的基本使用结构如下:

1     返回值 函数名称(列|数据)

而根据函数的特点,单行函数可以分为以下几种:字符串函数、数值函数、日期函数、转换函数以及通用函数。

 

一、字符串函数

 

字符串函数可以针对于字符串数据进行处理,在Oracle之中对于此类函数定义有如下变化:UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、SUBSTR()。

 

1.大小写转换函数

      转大写函数:字符串 UPPER(列|字符串)

      转小写函数:字符串 LOWER(列|字符串)

 

如果要在Oracle数据库中验证字符串函数,那么就必须保证编写的是完整的SQL语句。所以为了可以方便地进行函数验证,往往会使用一张虚拟表:dual表

 

范例:验证函数

 

语法格式:

1 SELECT LOWER('SOLer He'),UPPER('SOLer He') FROM  dual;

几乎所有的数据库里面都会提供这两个函数。

 

如果说现在要求用户自己输入一个雇员姓名,而后进行雇员信息的查找。

 

语法格式:

1 SELECT * FROM emp WHERE ename='&inputename';

结果如下:

用户在进行数据输入的时候几乎不会去考虑大小写,所以为了保证数据可以正常的查询出来,往往需要对输入数据进行处理。由于在数据表中所有的数据都是大写操作,那么就可以接收完输入数据之后将会自动变为大写字母。

 

范例:改善输入操作。

语法格式:

1 SELECT * FROM emp WHERE ename=UPPER('&inputename');

所以在一些要求严格的操作环境下,对于不区分大小写的操作的时候,基本上就会有两种做法:

 

  1. 在数据保存的时候将所有的数据统一变为大写或者是小写,这样子在查询的时候就可以直接利用特定的函数进行处理。
  2. 在数据保存的时候依然是按照原始的方式进行保存,而后在查询的时候将每一个数据中的字母变为大写形式进行处理。

在所有不区分大小写的操作的项目之中,保存数据时就必须对数据进行提前的处理。

 

 

2.首字母大写

语法格式:

1 字符串 INITCAP(列 | 数据)

 

范例:观察首字母大写

 

代码格式:

1 SELECT INITCAP ('HeLLoWorld') FROM dual;

查询输出结果:

 

除了首字母大写之外,其他的都是小写。

 

范例:将每一个雇员的姓名首字母变为大写。

 

代码结构:

1 SELECT INITCAP (ename) FROM emp;

查询结果:

 

3.计算字符串长度

语法:

1 数字 LENGTH (列 | 字符串数据)

范例:查询出每个雇员姓名以及雇员姓名的长度。

 

代码格式:

1 SELECT ename,LENGTH(ename) FROM emp;

查询结果:

 

那么所有的单行函数可以在SQL语句的任意位置上出现。

 

范例:查询雇员姓名长度为5的全部雇员信息。

 

         分析:需要针对于所选的数据行进行筛选,那么就一定要在WHERE子句之中进行。

 

代码格式:

1 SELECT * FROM emp WHERE LENGTH(ename)=5;

结果如图:

 

4.字符串的替换操作

可以使用指定的内容替换原始字符串中的数据。

 

语法格式:

1 字符串  REPLACE (列 | 数据,要查找的内容,新的内容)

范例:将所有雇员姓名之中的字母替换为“_”。

 

代码示例:

1 SELECT REPLACE (ename,'A','_') FROM emp;

查询结果:

 

实际上可以利用REPLACE()函数可以消除字符串中的全部空格数据。

 

范例:消除空格数据。

 

代码示例:

1 SELECT REPLACE('Hello World This is my oracle',' ','') FROM dual;

查询结果:

 

 

5.字符串截取

         语法一:字符串 SUBSTR(列 | 数据,开始点),从指定的开始点一直截取到结尾

         语法二:字符串 SUBSTR(列 | 数据,开始点,长度):截取指定范围的子字符串。

 

范例:子字符串截取操作。

 

代码示例: 

1 SELECT SUBSTR('helloworldnihao',11) FROM dual;

查询截图:

此种方式就是从指定位置截取到结尾。

 

范例:截取部分内容

代码示例:

1 SELECT SUBSTR('helloworldnihao',6,5) FROM dual;

结果:

 

但是对于SUBSTR()函数千万要记住一点,它的下标是从1开始的,也就是在进行截取的时候,字符串从1开始作为索引下标,但是即使设置的值是0,也是按照1来处理。

例如:代码示例:

1 SELECT SUBSTR('helloworldnihao',0,5) FROM dual;
2 
3 SELECT SUBSTR('helloworldnihao',1,5) FROM dual;

结果:

 

 

范例:要求截取每一位雇员姓名的前三位字符

 

代码示例:

1 SELECT  ename,SUBSTR(ename,1,3) FROM emp;

结果如下:

 

 

范例:要求截取姓名的后三个字母。此范例可以采用两种做法:

        

         第一种(传统做法):如果进行截取,那么首先一定要确认出截取的开始点,所以对于开始点,由于每一个姓名的长度都是不一样的,所以开始点也是不一样的。所以采用最好的办法就是进行计算。就可以使用LENGTH来计算长度。

 

代码示例:

1 SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;

查询结果:

 

 

SUBSTR()的支持,可以设置负数索引。

代码示例:

1 SELECT ename,SUBSTR(ename,-3) FROM emp;

实际上只有Oracle数据库才会支持这种负数的索引设计,其他的任何语言都是不支持。

 

面试题:请问Oracle中的SUBSTR()函数截取字符串的索引是从1开始还是从0开始?

 

答案:Oracle数据库中的字符串的索引都是从1开始的,即使设置的值是0,也会将其自动变为1开始执行。

 

 

数值函数

数值函数主要是针对于数字进行处理的,有三个主要的函数:ROUND()、TRUNC()、MOD()。

 

1.四舍五入操作

语法:

1 数字 ROUND(列| 数字,[保留小数位])如果不设置小数位就表示不保留。

 

范例:测试四舍五入

代码示例及解析:

 1 SELECT
 2 
 3          ROUND(78915.678932654),           78916,小数点之后的内容直接进行四舍五入
 4 
 5          ROUND(78915.678932654,2),       78915068       保留两位小数
 6 
 7          ROUND(78915.678932654,-2),      78900,把不足5的数字全部取消了。
 8 
 9          ROUND(78985.678932654,-2),      79000,如果超过了5则进行进位
10 
11          ROUND(-15.32)                                 -15
12 
13 FROM dual;

结果如下:

 

2.截取小数,所有的小数都不进位。

语法:

1 数字 TRUNC(列 | 数字[,小数位])

代码示例:

 1 SELECT
 2 
 3          TRUNC(78915.678932654),                    78916
 4 
 5          TRUNC(78915.678932654,2),                          78915.68
 6 
 7          TRUNC(78915.678932654,-2),               78900
 8 
 9          TRUNC(78985.678932654,-2),               79000
10 
11          TRUNC(-15.32)                                           -15
12 
13 FROM dual;
14 
15  

结果如下:

 

3.求模(求余数)

语法:

1 数字 MOD(列1 | 数字1,列2 | 数字2)

范例:求模操作

代码示例:

1 SELECT MOD(10,3) FROM dual;

 

 

日期函数(Oracle自己的特色)

日期处理函数主要是进行日期处理,但是整个日期处理过程中会存在一个关键词的问题。如何可以取得当前的日期时间。在Oracle中会提供一个数据伪列。指的是一个列,但是不存在于表中,可是却可以像列一样进行数据的查询。那么这个伪列就是SYSDATE。

 

代码示例:

1 SELECT ename, hiredate, SYSDATE FROM emp;

如果只是单纯地想要取得日期,可以使用简单一些,直接使用dual虚拟表就可以。

代码示例:

1 SELECT SYSDATE FROM dual;

 

如果是用具体地时间戳,那么就可以直接加SYSTIMESTAMP。

代码示例:

1 SELECT SYSDATE,SYSTIMESTAMP FROM dual;

 

实际上对于日期时间提供有三种计算模式:

                  

1 日期 + 数字 = 日期(若干天后地日期)
2 
3 日期 – 数字 = 日期(若干之前地日期)
4 
5 日期 – 日期 = 数字 (两个日期间的天数)
6 
7                      

 

测试:若干天后的日期

 

代码示例1:

1 SELECT SYSDATE+10 FROM dual;

 

代码示例2:

1 SELECT SYSDATE+10, FROM dual;

在进行日期与数字的计算之中,得到的结果都是比较容易理解的。

 

范例:计算每一位雇员到今天为止的雇佣天数。

代码示例:

1 SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

 

通过以上的分析发现,如果现在只是依靠天数实际上是很难得到一个准确的年或者是月,所以为了可以精确的进行计算,在Oracle里面才提供有日期处理函数,利用这些函数可以避免掉那些闰年或者是闰月的问题。

 

计算两个日期间所经历的月数总和。

语法:

1 数字 MONTHS_BETWEEN(日期1,日期2)

 

范例:计算每一位雇员到今天为止的雇佣总月数。

代码示例:

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

实际上,现在已经存在有月的数据了,那么就表示可以准确计算年。

 

范例:计算每一个雇员到今天为止所雇佣的年限。

代码示例:

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

 

增加若干月之后的日期。

语法:

1 日期 ADD_MONTHS(日期,月数)

 

范例:测试ADD_MONTHS()函数。

 

代码示例:

1 SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

 

利用这种方式增加的月可以避免闰年、闰月这两个问题。

 

范例:计算所有还差1年满34年雇佣日期的全部雇员。

代码示例:

1 SELECT * FROM emp WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12)=34;

 

  1. 计算指定日期所在月的最后一天。

      语法:

1              日期 LAST_DAY(日期)      

 

 

范例1:计算当前日期所在月的最后一天。

代码示例:

1 SELECT LAST_DAY(SYSDATE) FROM dual;

 

范例2:查询出所有在雇佣所在月倒数第二天被雇佣的雇员信息

         每一个雇员的雇佣日期都是不一样的,所以在每一个雇佣日期所在月的倒数第二天也不一样的。

         ·首先应该知道每一个雇员雇佣月的最后一天,而后利用“日期 + 数字 = 日期”,计算倒数第二天。

 

代码示例:

1 SELECT ename,hiredate, LAST_DAY(hiredate),LAST_DAY(hiredate)-2
2 
3 FROM emp
4 
5 WHERE LAST_DAY(hiredate)-2=hiredate;

 

  1. 计算下一个指定的日期

    语法:日期 next_day(日期,一周时间数)

 

范例:计算下一个周二

代码示例:

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

【了解】综合分析:要求查询雇员的编号、姓名、雇佣日期,以及每一位雇员到今天为止所雇佣的年数、月数、天数。

         假设现在的日期是:2018-07-06

 

现在WARD他的雇佣日期为:“1981-02-22”,所以它到今天为止的雇佣日期已经被雇佣了:35年、0月,15天。

对于该查询而言,由于日期的跨度较长。所以要想准确的计算出结果。

 

代码示例:

 

1 SELECT empno,aname,hiredate
2 
3          TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year
4 
5 FROM emp;

 

第二步:计算月

在进行计算年的时候就包含余数,余数实际上就是除以12的结果,余数就是月数。利用MOD()函数求出余数。

 

加上TRUNC之后的结果如下:

 

第三步:计算天数

         计算天数的操作只有一个公式:“日期1 – 日期2 = 数字(天数)”,现在就出现了日期的问题上:

                  日期1:一定是当前日期,肯定是使用SYSDATE伪例。

                  日期:实际上可以使用MONTHS_BETWEEN()函数求出两个日期之间的月数。

 

观察可以发现,天数里面计算结果也会有小数点,所以我们加上TRUNC之后,就会只有整数形式的结果。

 

 

转换函数(重点)

就目前而言,在Oracle中的三种数据类型:字符串、数字、日期。所以所谓的转换函数的实现字符串与日期、数字之间的转换。

转换函数一共提供有三种:TO_CHAR()、TO_DATE()、TO_NUMBER()。针对于转换函数而言,重点的是TO_CHAR()。

 

1.转字符串函数,数字或者是日期可以转换为字符串。
         语法:字符串 TO_CHAR(列 | 日期 | 数字,转换格式)

         对于转换格式而言,主要有两类格式:

–        日期转换为字符串:年(yyyy)、月(mm)、日(hh、hh24)、分(mi)、秒(ss)。

–        数字转换为字符串:任意一位数字(9)、货币(L,本地货币)。

 

范例:格式化日期。

代码格式:

1 SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-dd hh24-mi-ss')FROM dual;

 

在这里提供了思想:日期要想该改变日期,最终的数据类型就是字符串。如果要这样子转换实际上会破坏程序的一致性。

 

实际上现在可以进一步探索TO_CAHR()好处,它可以实现年月份、月、日的拆分。

 

范例:查询出,每个雇员的编号、姓名、雇佣年份。

代码示例:

1 SELECT * empno,ename,TO_CHAR(hiredate,'yyyy') year FROM emp;

 

范例:查询出所有在2月雇佣的雇员信息。

代码示例:

 

 

Oracle中实际上提供有数据类型的自动转换,如果发现比较的类型不统一,在一定的范围内是可以转换的。

 

TO_CHAR()函数除了可以进行日期的转换之外,也是支持数字转换的。所谓的数字转换往往是针对于数字的可读性进行一些格式化的操作。

 

范例:转换数字

代码示例:

1 SELECT TO_CHAR(8899,'L999,999,999,999') FROM dual;

 

2.转日期函数

如果说现在某一个字符按照“日 – 月 – 年”的格式去编写。那么可以自动转换为日期类型,但是也可以依靠TO_DATE()函数来完成。

 

语法格式:

1 TO_DATE(字符串,转换格式)

–        年(yyyy)、月(mm)、日(hh、hh24)、分(mi)、秒(ss)。

 

范例:实现字符串转换为日期。

代码示例:

1 SELECT TO_DATE('1995-05-02','yyyy-mm-dd')  FROM dual;

3.转数字函数

         可以将字符串(由字符串所组成),变为数字。

 

         语法格式:

1 数字 TO_NUMBER(字符串)。

 

范例:验证转数字函数

代码示例:

1 SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;

 

通用函数(oracle自己的特色)

在Oracle中提供了两个简单的数据处理函数:NVL()、DECODE()。并且随着版本的提升,此两个函数也衍生出了许多的子函数。

 

1.处理null

计算出每一个雇员的年薪,包括基本工资和佣金。

 

代码示例:

1 SELECT empno,ename,job,(sal+comm)*12 income FROM emp;

现在发现,所有没有佣金的雇员,现在进行年收入计算的时候,最终的计算结果都是Null ,因为null在进行任何数学计算的时候,结果永远都是null。而实际上在计算之中,如果发现内容为null,如果是数字则应该使用0来替代,那么就需要利用我们的NVL()函数来解决此类问题。

         语法格式:

1 NVL(列 | null,为空的默认值)如果在列上的内容不是null则使用列的数据,如果为null,则使用默认值。

         代码示例:

1 SELECT empno,ename,job,sal,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;

 

2.多数值判断

         所谓的多数值判断,指的是根据不同的结果可以在输出的时候进行严格数据的转换,假设每一个雇员都有自己的职位。职位现在使用的是英文描述,决定使用更换为中文描述。

代码示例:

1 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售','暂无此信息') FROM emp;
2 
3  
4 
5 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售') FROM emp;

 

 

 

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐