单行函数

ゝ一世哀愁。 2022-05-27 01:36 270阅读 0赞

# 单行函数 #

## 概念 ##

单行函数分为：字符串函数、数值函数、日期函数、转换函数、通用函数。所有的单行函数可以在SQL语句的任意位置上出现。

## 语法结构 ##

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

## 字符串函数 ##

### 概念 ###

对字符串数据进行处理，在Oracle中对于此类函数定义有如下几种：UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、SUBSTR()。

### UPPER()、LOWER——大小写转换函数 ###

**1）语法格式**

*  UPPER()：转大写，字符串 UPPER(列 | 字符串)
 *  LOWER()：转小写，字符串 LOWER(列 | 字符串)

SELECT LOWER('Hello'), UPPER('Hello') FROM dual;

**dual表：**验证字符串函数时，必须保证编写的是完整的SQL语句，为了可以方便的使用函数验证，我们就会使用dual这张虚拟表。

**2）应用范例**

要求用户输入查询的雇员姓名，输出并所有信息：

SELECT * FROM EMP WHERE ENAME = UPPER('&inputname');

*  &inputname：替代变量，可以由键盘输入替换它的值。

### INITCAP()——首字母大写 ###

**1）语法格式**

SELECT INITCAP('helloWorld') FROM dual;

返回Helloworld。

**2)应用范例**

将所有雇员姓名首字母转为大写：

SELECT INITCAP(ENAME) FROM EMP;

### LENGTH()——计算字符串长度 ###

**1）语法格式**

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

**2）应用范例**

查询出所有雇员及其姓名长度：

SELECT ENAME, LENGTH(ENAME) LENGTH FROM EMP;

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

SELECT * FROM EMP WHERE LENGTH(ENAME) = 5;

### REPLACE()——字符串替换 ###

**1）语法格式**

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

**2）应用范例**

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

SELECT REPLACE(ENAME, UPPER('a'), '_') FROM EMP;

### SUBSTR()——字符串截取 ###

**1）语法格式**

字符串 SUBSTR(列 | 数据, 开始点)
    字符串 SUBSTR(列 | 数据, 开始点, 长度)

*  第一种：从指点开始点一直截取到结尾。
 *  第二种：截取指定范围的子字符串。
 *  SUBSTR()是从下标识从1开始的，即使设置的是0，也会从1开始。

**2）应用范例**

**a.截取雇员姓名的前三个字母：**

SELECT ENAME, SUBSTR(ENAME, 1, 3) FROM EMP;

**b.截取所有雇员姓名的后三个字母：**

*  方法一：传统方法

SELECT ENAME, SUBSTR(ENAME, LENGTH(ENAME) -2, 3) FROM EMP;

*  方法二：负数索引（只有Oracle支持负数索引）

SELECT ENAME, SUBSTR(ENAME, -3) FROM EMP;

SUBSTR()支持使用负数索引，这里的-3表示从倒数第三个位置开始截取。

## 数值函数 ##

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

### ROUND()——四舍五入 ###

**1）语法格式**

数字 ROUND(列 | 数字 [, 保留小数位])

*  如果不设置小数位表示不保留。

**2）应用范例**

SELECT ROUND(9404.6789), 9405，小数点四舍五入 ROUND(9404.6789, 2), 9404.68，保留两位小数 ROUND(9404.6789, -2), 9400，整位取整，四舍五入 ROUND(9484.6789, -2), 9500 ROUND(-502.123), -502，四舍五入 ROUND(-502,523) -503 FROM dual;

### TRUNC()——截取小数 ###

语法与ROUND相似，直接抹掉，不四舍五入

SELECT TRUNC(9404.6789), 9404，去掉小数位 TRUNC(9404.6789, 2), 9404.67，保留两位小数 TRUNC(9404.6789, -2), 9400，整位取整，不四舍五入 TRUNC(9484.6789, -2), 9400 TRUNC(-502.123), -502 TRUNC(-502,523) -502 FROM dual;

### MOD——求模（求余数） ###

**1）语法格式**

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

**2）应用范例**

SELECT MOD(10, 3) FROM dual;        结果=1

## 日期函数 ##

Oracle数据库中存在一个数据伪列SYSDATE和SYSTIMESTAMP（时间戳），以获得当前系统时间。

SELECT SYSDATE FROM dual;       --返回04-4月 -18
    SELECT SYSTIMESTAMP FROM dual;  --返回04-4月 -18 12.50.51.455000 下午 +8:00

### 日期的三种计算模式 ###

*  日期 + 数字 = 日期（若干天之后的日期）
 *  日期 - 数字 = 日期（若干天之前的日期）
 *  日期 - 日期 = 数字（两个日期间的天数）

SELECT SYSDATE+10 FROM dual;    --输出10天之后的日期

计算每位雇员到今天为止的雇佣天数：

SELECT ENAME, SYSDATE - HIREDATE FROM EMP;

### 日期处理函数 ###

利用日期处理函数可以表面闰年、闰月的问题，能够得到更准确的年或月结果。

#### MONTHS\_BETWEEN() ####

**1）语法格式**

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

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

**2）应用范例**

计算每一位雇员到今天的雇佣总月数：

SELECT ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

计算每一位雇员到今天的雇佣总年数：

SELECT ENAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) YEARS FROM EMP;

#### ADD\_MONTHS() ####

计算若干月之后的日期

**1）语法格式**

ADD_MONTHS(日期, 月数)

**2）应用范例**

计算4个月之后的日期：

SELECT ADD_MONTHS(SYSDATE, 4) FROM dual;

#### LAST\_DAY() ####

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

**1）语法格式**

日期 LAST_DAY(日期)

**2）应用范例**

计算当前日期所在月的最后一天：

SELECT LAST_DAY(SYSDATE) FROM DUAL;

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

SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2 = HIREDATE;

#### NEXT\_DAY() ####

计算下一个指定的星期。

**1）语法格式**

日期 NEXT_DAY(日期, 一周时间数)

**2）应用范例**

计算下一个周二的日期：

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

查询所有雇员的雇佣日期到今天共雇佣多少年，多少月，多少日

SELECT ENAME, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) YEARS, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIREDATE),12)) MONTHS, TRUNC(SYSDATE - ADD_MONTHS(HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE))) DAYS FROM EMP;

## 转换函数 ##

Oracle中共有三种数据类型：字符串、数字、日期。所以相应的，共有三种转换函数：**TO\_CHAR()**、TO\_DATE()、TO\_NUMBER()。

### TO\_CHAR()——转字符串 ###

**1）语法格式**

字符串 TO_CHAR(列|日期|数字, 转换格式)

*  日期转换为字符串：年（yyyy）、月（mm）、日（dd）、时（hh、hh24）、分（mi）、秒（ss）；
 *  数字转换为字符串：任意的以为数字（9）、货币（L，本地货币）。

**2）应用示例**

日期转字符串：

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh:mi:ss') FROM DUAL;

查询出在2月雇佣的所有雇员：

SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE, 'mm') = '02';
    SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE, 'mm') = 2;

**Oracle数据库中提供有数据类型自动转换功能**，所以第二条查询语句也正确。