Oracle中的函数（详细！！！）

---

前言

此篇文章是我在B站学习时所做的笔记，主要对 函数的基本概念和分类和常见的单行函数，sql语句部分为亲自动手演示过的，方便复习用。此篇文章仅供学习参考。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、SQL中的函数

1. 函数是用于执行数据处理和复杂运算的。通过对一组数据进行一系列的运算，得到一个最终的需要输出的结果。函数通常都会有一个或多个输入，被称作函数的参数在函数的内部对输入的参数进行逻辑计算，最终会得到一个或多个值，但是只有一个值作为函数的计算结果返回给客户
2. 函数可以出现在sql语句的各个位置，最常见的是出现在查询列表中

两种SQL函数

1. 根据函数对多行输入数据的处理方式，函数分为单行函数和多行函数。
2. 单行函数：每行作为一个输入参数，经过函数的计算得到每行的计算结果单行函数执行的操作影响表中每一行的显示效果
3. 多行函数多多行输入值进行计算，得到多行对应的一个结果
   

--单行函数:每次取一条记录,作为函数的参数,得到这条记录对应的单个结果
SELECT NAME,LENGTH(NAME)
FROM A_CHENS;
--多行函数:一次性把多条记录当作参数输入给函数,得到多条记录对应的当个结果
SELECT  MAX(CHENGJI)
FROM A_CJJ_4;

单行函数

• 单行函数对单行输入参数进行计算，在每行中取得的值作为函数的输入参数，在计算后将结果返回给用户。每行都会得到相应的计算结果
• 单行函数可以对数据进行复杂的计算，也可以进行数据类型的转换，还可以对特殊的数据进行处理。
• 另外，单行函数还可以嵌套。一个函数可以作为另一个函数的输入参数

单行函数的分类

1. 字符型函数

字符函数对文本串进行操作

字符型函数	解释
LOWER（）	将整个字符串转换为小写
UPPER（）	将整个字符串转换为大写
INITCAP（）	把每个单词收个字母变成大写
CONCAT（）	连接两个字符串
SUBSTR（）	截取子字符串（全角算2字符）
LENGTH（）	返回字符串的长度（全角算1字符）
INSTR（）	字符串中搜索字符位置（全角算1字符）
LPAD（）	在左边添加字符
RPAD（）	在右边添加字符
TRIM（）	删除左边和右边字符串
REPLACE（）	替换子字符串

表A_CHENS 数据：

SELECT *
FROM A_CHENS;

LOWER函数

SELECT *
FROM A_CHENS
WHERE LOWER(XMPY)='chenyi';

UPPER函数

SELECT *
FROM A_CHENS
WHERE XMPY=UPPER('chener');

INITCAP函数

SELECT PASSWORD,XMPY
FROM A_CHENS
WHERE INITCAP(PASSWORD)='Chensi';

CONCAT函数

SELECT NAME||XMPY,CONCAT(NAME,XMPY)
FROM A_CHENS;

SUBSTR函数

SELECT XMPY,SUBSTR(XMPY,1,2)
FROM A_CHENS;

INSTR函数

SELECT XMPY,INSTR(XMPY,'U')
FROM A_CHENS;

LPAD|RPAD函数

SELECT PASSWORD,LPAD(PASSWORD,10,'$'),rPaD(PASSWORD,10,'*')
FROM A_CHENS;

REPLACE函数

SELECT PASSWORD,REPLACE(PASSWORD,'a','A')
FROM A_CHENS;

---

2. 数字函数

数字函数是对数字进行操作的函数，这类函数比较少，也比较简单。
只有三种：

• 对数字四舍五入的ROUND函数；
• 对数字进行截取的TRUNC函数；
• 求模或求余的MOD函数

ROUND函数

SELECT ROUND(45.943,2) "小数点后两位",
       ROUND(45.943,0) "个位",
       ROUND(45.942,-1) "十位"
    FROM SYS.dual;

TRUNC函数

SELECT TRUNC(45.943,2）"小教点后两位"，
TRUNC(45.343, 0)"个位",
TRUNC(45.942,-1)"十位"
FROM sys.DUAL;

查看A_CJJ_4表数据：

SELECT* FROM A_CJJ_4;

MOD函数

SELECT CHENGJI,PINGSHIFEN,MOD(CHENGJI+PINGSHIFEN,2)
FROM A_CJJ_4
WHERE PINGSHIFEN=80;

---

3. 使用日期

• 日期函数对日期数据进行处理，得到新的日期或者数字
• Oracle以内部数字格式存储日期：世纪，年，月，日，小时，分钟，秒
• 缺省的日期格式是 DD-MON-YY
• Oracle内置的SYSDATE函函数，是返回日期和时间的函数

查看系统时间

--查看系统时间
SELECT SYSDATE FROM SYS.DUAL

根据时间查询信息

SELECT *
FROM A_CHENS
WHERE BIRTHDAY='20-7月-2001';

日期的运算

• 日期类型的数据可以使用算术运算符进行计算
• 从日期中加或减一个数值，以得当一个新的日期结果值
• 两个日期相减以便得到他们相差多少天
• 注意：日期不能加另一个日期，目期也不支持乘除运算

SELECT NAME,SYSDATE,BIRTHDAY,ROUND((SYSDATE-BIRTHDAY)/365,0) 生活年数
FROM A_CHENS;

日期函数

函数	描述
MONTHS_BETWEEN	两日期相差多少月，返回两个日期间隔月数
ADD_MONTHS	在一个日期上加或减若干月到新的日期，返回指定月数后的日期
NEXT_DAY	取得从当前日期开始遇到的第一指定星期几的日期，返回下周某一天的日期
LAST_DAY	计算指定日期所在月份的最后一天的日期，返回本月最后一天的日期
ROUND	取得按年或月四舍五入得到的新日期，四舍五入后的期间第一天
TRUNC	取得按年或月截取得到的新日期，返回日期所在期间的第一天

MONTHS_BETWEEN函数

SELECT NAME,SYSDATE,BIRTHDAY,(SYSDATE-BIRTHDAY)/30 普通计算,months_between(SYSDATE,BIRTHDAY)精确计算
FROM A_CHENS;

ADD_MONTHS函数

SELECT NAME,SYSDATE,BIRTHDAY,BIRTHDAY+90 粗略的三个月,add_months(BIRTHDAY,3)精确日期
FROM A_CHENS;

NEXT_DAY函数

select sysdate 当时日期,
       next_day(sysdate,'星期一')下周星期一,
       next_day(sysdate,'星期二')下周星期二,
       next_day(sysdate,'星期三')下周星期三,
       next_day(sysdate,'星期四')下周星期四,
       next_day(sysdate,'星期五')下周星期五,
       next_day(sysdate,'星期六')下周星期六,
       next_day(sysdate,'星期日')下周星期日
 from dual;

LAST_DAY函数

SELECT NAME,BIRTHDAY,LAST_DAY(BIRTHDAY)
FROM A_CHENS;

ROUND函数

select sysdate 当时日期,
       ROUND(sysdate)最近0点日期,
       ROUND(sysdate,'day')最近星期日,
       ROUND(sysdate,'month')最近月初,
       ROUND(sysdate,'q')最近季出日期,
       ROUND(sysdate,'year')最近年初日期
 from dual;

---

4. 转换函数

在执行数据操作的时候，如果两个类型不同的数据进行计算，很可能会出错，所以要使用数据类型转换函数

注：尽管可以使用隐式数据类型转换，但是还是建议执行显式的数据类型转换，以确保SQL语句的可靠性。

数据类型转换

日期格式基础

1、在转换日期为字符串的时候，日期中的年，月，日，时，分，秒都需要相应的格式控制符来控制转换的显示格式

格式	解释
YYYY,YYY,YY	分别代表4位，3位，2位的数字年份
YEAR	年的拼写
MM	数字月
MONTH	月份的全拼名称
MON	月份的缩写
DD	数字日
DAY	星期的全拼
DY	星期的缩写

2、与日期控制符相对应，时间也需要相应的格式控制符来描述。
否则默认是不显示时间的。即使包含了时间，日期格式控制符也比较简单

格式控制符	描述
AM	表示上午或下午
HH24,HH12	24小时制或12小时制
Ml	分钟
SS	秒钟
SP	数字的拼写
TH	数字的序数词
“特殊字符”	在日期时间中加入特殊的字符

3、
时间格式作为日期的一部分

HH24:MI:SS AM	15:45:32 PM

用双引号括起来以加字符串

DD “of” MONTH	12 of OCTOBER

数值拼写

ddspth	fourteenth

--隐式转换
SELECT *
FROM A_CHENS
WHERE SAL='22';

---

TO_CHAR函数操作日期

格式

• 必须用单引号括起来，并且是大小写敏感
• 可包含任何有效的日期格式

案例一：TO_CHAR把日期转换为字符

SELECT NAME,BIRTHDAY ,TO_CHAR(BIRTHDAY,'YYYY-MM-DD')
FROM A_CHENS
WHERE TO_CHAR(BIRTHDAY, 'YYYY-MM-DD' )= '2004-01-01';

SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM DAY')
FROM SYS.DUAL;

案例二：TO_CHAR把数值转换为字符

使用TO_CHAR函数将数字作为字符显示

--TO_CHAR,把数值转换为字符
--$有就显示格式，没有就不显示
--L有就显示该格式，没有强制显示该格式
SELECT SAL , To_CHAR (SAL, '$999,999.00' ) , To_CHAR(SAL,'L000,000.00')
FROM A_CHENS
WHERE NAME='陈一';

TO_NUMBER和TO_DATE函数

案例一：使用TO_NUMBER函数将字符转换为数字，字符的格式和模板的模式必须要一致

--TO_NUMBER把字符型的数据转换为数值,字符的格式和模板的模式必须要一致
SELECT TO_NUMBER('$800.00','$999,999.00')
FROM SYS.DUAL

案例二：使用TO_DATE函数将字符转换为日期

--TO_DATE把字符型的数据转为日期型的数据
SELECT *
FROM A_CHENS
WHERE BIRTHDAY=TO_DATE('2004-1-1','YYYY-MM-DD');

---

如若侵权，请及时与我联系。
此篇文章仅供学习参考，禁止转载！