单行函数

在数据库里面为了方便用户的开发，往往会会提供一系列的支持函数，利用这些函数可以针对于数据进行处理。如：在根据姓名查询的时候，如果说姓名本身是大写字母，而查询是小写字母，此时将不会有任何的结果返回，所以考虑到此类情况，往往会在数据保存或查询的时候对数据进行一些处理，而这些处理每一个数据库都有自己的函数库，利用函数可以实现特定功能。

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

返回值 函数名称（列 | 数据）

​ 而根据函数的特点，单行函数可以分为以下几种：字符串函数、数值函数、日期函数、转换函数、通用函数（所有的单行函数可以在SQL语句的任意位置上出现）

1.字符串函数

字符串函数可以针对于字符串数据进行处理，在Oracle之中对于此类函数定义有如下几种：

UPPER() 、 LOWER()、 INITCAP() 、REPLACE()、LENGTH()、SUBSTR()。

1.大小写转换函数

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

• 转小写函数：字符串 LOWER（列 | 字符串）

  如果想在Oracle之中验证字符串函数，那么必须要保证编写的完整的SQL语句。为了可以方便的验证函数，往往会使用虚拟表：dual表

几乎所有的数据库里面都会提供这两个函数，使用场景：现在要求由用户自己输入一个雇员姓名，而后进行雇员信息的查找。

用户在进行数据输入的时候几乎不回去考虑大小写，为了保证数据可以正常查询出来，往往需要对输入数据做一个处理，由于在数据表之中所有的数据都是大写操作，那么就可以在接收完数据之后将其全部转换为大写字母

在一些要求严格的操作环境下，对于不区分大小写操作的时候基本有两种做法：

• 在数据保存的时候将所有的数据统一变为大写或小写字母，这样在查询的时候直接对特定数据进行处理

• 在数据保存的时候依然将所有的数据按照原始的方式保存，而后在查询的时候将每一个数据中的字母变成大写形式进行查询

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

2、首字母大写

• 语法：字符串 INITCAP(列 | 数据)

  

所有的函数后面除了可以跟数据，都可以跟列（将每一个雇员的姓名首字母大写）

3、计算字符串的长度

• 语法：数字 LENGTH （列 | 字符串数据）

  范例：查询出每个雇员姓名及雇员姓名的长度

  

范例：查询雇员姓名长度为5的所有信息

4、字符串替换

可以使用指定的内容替换掉原始字符串的内容

• 语法：字符串 REPLACE (列 | 数据，查找内容，新的内容)

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

  

利用REPLACE()函数可以取消掉字符串中的全部空格数据。

范例：消除空格数据

5.字符串截取

• 语法一：字符串 SUBSTR （列 | 数据 ，开始点），从指定的开始点一直截取到结尾；

• 语法二：字符串 SUBSTR （列 | 数据，开始点 ，长度），截取指定范围的字符串。

  范例：字符串截取操作（从指定位置到结尾）

  

范例：字符串截取操作（指定范围截取）

索引下标从1开始，即使你设置的是0，也会按照1来处理

范例：截取emp表中ename的后三个字母（方法一）

范例：截取emp表中ename的后三个字母（方法二）只有Oracle才支持负数的索引设计，其他的任何语言都没有。

2、数值函数

数值函数是针对数字进行处理的，有三个主要函数：ROUND()、TRUNC()、MOD()

1、四舍五入函数

• 语法： 数字 ROUND(列 | 数字 [，保留小数位] )， 不设置表示不保留

  

2、截取小数位，所有小数位都不进位

• 语法： 数字 TRUNC ( 列 | 数字 [，小数位])。

  

3、求模（求余数）

• 语法： 数字 MOD（列1 | 数字1， 列2 | 数字2）

  

3、日期函数（Oracle自己特色）

日期函数主要是处理日期，整个处理过程中有一个关键问题，如何获取当前时间？Oracle中提供有一个数据伪列，这个列不存在表中，可以像表的列一样进行数据查询：SYSDATE、SYSTIMESTAMP （emp中没有SYSDATE、SYSTIMESTAMP列）

日期时间提供有以下三种计算模式：

• 日期 + 数字 = 日期 （若干天之后的日期）
• 日期 - 数字 = 日期（若干天之前的日期）

• 日期 - 日期 =天数 （两个日期之间的天数）

通过天数很难得到一个准确的年或月，为了可以进行精确计算，在Oracle里面提供有日期处理函数，可以屏蔽掉闰年或闰月的问题。

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

• 语法： 数字 MONTHS_BUTWEEN(日期1 ，日期 2)。

  

知道月的数据，就可以准确计算年了

2、增加若干月之后的日期（避免闰年、闰月）

• 语法： 日期 ADD_MONTHS(日期 ，月数)

  

3、计算指定日期所在月的最后一天

• 语法： 日期 LAST_DAY( 日期)。

范例： 查询出在雇佣所在月倒数第二天被雇佣的信息

4、计算下一个指定星期的日期

语法： 日期 LAST_DAY(日期，‘星期×’)

范例：查询出雇员的编号、姓名、雇佣日期，雇佣年数、月数、天数

select empno,ename,hiredate,trunc(months_between(sysdate,hiredate)/12)years,trunc(mod(months_between(sysdate,hiredat),12)) months,trunc(sysdate-add_months(months_between(sysdate,hiredate)))from emp;

4、转换函数

Oracle数据类型： 字符串、数字、日期，转换函数就是将字符串与日期、数字进行转换。

三种转换函数： TO_CHAR()、 TO_DATE()、 TO_NUMBER().

1、转字符串函数

数字或者日期可以转换为字符串

• 语法：字符串 TO_CHAR( 列 | 日期 | 数字 ，转换格式)
• 主要有两类转换格式：

|-日期转换字符串 ： 年（yyyy） 月（mm） 日（dd） 时（hh、hh24） 分（mi） 秒（ss）

|-数字转换为字符串 ： 任意一位数字（9）、货币（L,本地货币）

日期要想改变格式，最终的数据类型就是字符串，但是这样的转换会破坏程序的一致性。

TO_CHAR()的好处，它可以实现年、月、日的拆分。

范例：查询出emp表中员工雇佣的年份

范例：查询出所有10月雇佣的员工

Oracle提供有数据类型自动转换

数字转换是对数字可读性的一些格式化的操作

2、转日期函数

如果字符串按照“日-月-年”格式编写，可以自动转换为日期类型，但是也可以将指定类型的字符串转换为日期类型

• 语法 日期 TO_DATE(字符串， 转换格式)

  年（yyyy） 月（mm） 日（dd） 时（hh、hh24） 分（mi） 秒（ss）

  

  3、转数字函数（由于Oracle有数据类型自动转换，基本很少用）

  语法：数字 TO_NUMBER(列 | 字符串)

  

5、通用函数（Oracle特色）

在Oracle中提供两个简单的数据处理函数：NVL()、DECODE()

1、处理null

范例：计算出每一个雇员的年薪 （基本工资+佣金）

上面的查询操作，出现了年薪为0的情况，不难发现是comm为null所导致，因为null在进行数值运算时恒为null

如果发现内容为null，在进行数学运算时，应该使用0来替代，可以使用NVL（）函数来解决此类问题

语法： NVL(列 | null ，为空的默认值) 如果列上的内容不为null，则使用列上的内容，如果为null，则使用默认值

2、多数值判断

根据不同的结果在输出的时候进行一个数据的转换。

语法： DECODE (列 ，匹配内容1，显示内容1，匹配内容2，显示内容2…[,默认值])

范例：将位置的英文描述转换为中文