单行函数

单行函数

什么是函数？
任何东西，只要它能接收输入，对输入进行输出并产生输出，它就可以被称之为函数。

单行函数简介
单行函数的格式：函数名[(参数1,参数2,参数3,...)]
其中的参数可以为： 用户定义的常量、变量、列名、表达式
单行函数支队表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。单行函数可以接受一个或者多个参数，其产生的输出结果的数据类型可能与参数的数据类型不同。
单行函数可用于如下的子句中：SELECT、WHERE、ORDER BY，而且单行函数可以嵌套
单行函数包括：字符型、数字型、日期型、转换型、一般型函数

单行字符型函数
注意：SQL中的索引位置是从1开始的
常用的字符型函数包括：
LOWER、UPPER、INITCAP、CONCAT、SUBSTR、LENGTH、INSTR、TRIM、REPLACE

LOWER(列名|表达式)：该函数是把字符转换成小写
SELECT LOWER('ABC') FROM dual; -- abc

UPPER(列名|表达式)：该函数是把字符转换成大写
SELECT UPPER('abc') FROM dual; -- ABC

INITCAP(列名|表达式)：该函数是把每个字的头一个字符转换成大写，其余的转换成小写
SELECT INITCAP('hi , this tom ASS') FROM dual; -- Hi , This Tom Ass

CONCAT(列名|表达式，列名|表达式)：该函数是把头一个字符串和第二个字符串连接成一个字符串。
SELECT CONCAT('hi','my') FROM dual; -- himy

SUBSTR(列名|表达式，m，[n])：该函数是返回指定的字符串。该字符串是从第m个字符开始，其长度为n
SELECT SUBSTR('ABCDEFG',3) FROM dual; --CDEFG 省略n，表示从第m个字符开始截取到最后
SELECT SUBSTR('ABCDEFG',3,3) FROM dual; --CDE

LENGTH(列名，表达式)：该函数是返回列中或表达式中字符串的长度
SELECT LENGTH('ABCDEFG') FROM dual; -- 7

INSTR(列名|表达式，‘字符串’，[m],[n])：该函数是返回所给字符串的数字位置位置，m表示从第m个字符开始搜索，n表示所给字符串出现的次数，它们的默认值都为1。

SELECT INSTR('ABCDEFG','L') FROM dual; --0搜索不到就返回0
SELECT INSTR('ABCDEFG','A') FROM dual; -- 1
SELECT INSTR('ABCDEFG','A',2) FROM dual; --0
SELECT INSTR('ABCDEFAG','A',1,2) FROM dual; --7

TRIM([leading|trailing|both]要去掉的字符 FROM 源字符串)：该函数能够从“源字符串”中的头（leading）部，或者尾（trailing）部，或者头尾都（both）去掉“要去掉的字符”；如果没有指定指定leading或者trailing，TRIM函数默认按照both处理（该函数是8i开始引入，前面版本是两个函数LTRIM和RTRIM）
SELECT TRIM('?' FROM '?ABDDSAGSA?') FROM dual; --ABDDSAGSA
SELECT TRIM(leading '?' FROM '?ABDDSAGSA?') FROM dual; --ABDDSAGSA?
SELECT TRIM(trailing '?' FROM '?ABDDSAGSA?') FROM dual; --?ABDDSAGSA
REPLACE(正文表达式，要搜寻的字符串，替换字符串)：该函数是在“正文表达式”中查找“要搜寻的字符串”，如果找到了就用“替换字符串”替换
SELECT REPLACE(INITCAP(REPLACE('guo_da_xia','_',' ')),' ','') FROM dual; --GuoDaXia

 

 

数字型函数
常用的数字型函数包括：
ROUND、TRUNC、MOD

ROUND(列名|表达式，n)：该函数将列名或表达式所表示的数值四舍五入到小数点后的n位
select ROUND(0.765,2) FROM dual; --0.77

TRUNC(列名|表达式，n)：该函数列名或表达式所表示的数值截取到小数点后的n位
select TRUNC(0.765,2) FROM dual; --0.76

MOD(m,n):该函数将m除以n并去余数
select MOD(6,5) FROM dual; --1

日期型数据的处理
oracle的日期型数据的内部存储格式为：世纪,年，月，日，时，分，秒。不论您的输入格式如何，oracle永远按照它的内部存储格式来存储日期型数据。oracle9i日期型数据输入和输出的默认格式为DD-MON-RR，之前的版本为DD-MON-YY

系统日期函数SYSDATE
SELECT SYSDATE FROM dual; -- 2016/12/23 16:20:03 显示的日期的格式和当前的数据库设置有关
可以把一个日期型数据和一个数字相加减，其结果仍然为日期型。注意：基数是天。
SELECT SYSDATE-10 FROM dual; -- 2016/12/13 16:20:03
SELECT SYSDATE-10*365 FROM dual; -- 2006/12/13 16:20:03
SELECT SYSDATE+1/24 FROM dual; -- 2016/12/13 17:20:03

日期函数
注意：只有符合默认日期表示形式的字符串才可以被直接当成日期。我这里是这个格式：15-7月-03。或者使用TO_DATE
常用的日期型函数包括：
MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY

MONTHS_BETWEEN(日期1，日期2)：该函数是返回日期1和日期2之间的月数。如果日期1大于日期2，其返回的月数为正，如果日期1小于日期2，其返回的月数为负
SELECT MONTHS_BETWEEN('15-7月-03','16-7月-03') FROM dual; ---0.032258064516129

ADD_MONTHS(日期，n)：该函数是把n个月加到日期上
SELECT ADD_MONTHS('15-7月-01',8) FROM dual; --2002/3/15

NEXT_DAY(日期，字符串)：该函数是返回下一个由字符串（星期几）指定的日期
SELECT NEXT_DAY('23-12月-16',3) FROM dual; -- 2016/12/27 星期从0开始，3表示星期二

ROUND和TRUNC函数用于日期型数据
除了可以把ROUND和TRUNC函数用于数字型数据外，还可以把ROUND和TRUNC（截取）函数用于日期型数据
ROUND,借助TO_DATE函数
SELECT ROUND(TO_DATE('28-5月-01'),'MONTH') FROM dual; -- 2001/6/1
SELECT ROUND(TO_DATE('28-7月-01'),'YEAR') FROM dual; --2002/1/1
TRUNC,借助TO_DATE函数
SELECT TRUNC(TO_DATE('28-7月-06'),'MONTH') FROM dual; --2006/7/1

不同数据类型之间的隐含转换
赋值语句中数据类型之间的隐含转换（Oracle自动转换）：
将边长字符型（VARCHAR2）或长字符型（CHAR）转换成数字型（NUMBER）
将边长字符型（VARCHAR2）或定长字符型（CHAR）转换成日期型（DATE）
将数字型（NUMBER）转变成长字符型（VARCHAR2）
将日期型（DATE）转换成长字符型（VARCHAR2）
表达式中（oracle自动转换）：
将变长字符型（VARCHAR2）或定长字符型（CHAR）转换成数字型（NUMBER）
将边长字符型（VARCHAR2）或定长字符型（CHAR）转换成日期型（DATE）
在将字符类型的数据转换成数字时，要保证字符型数据为有效的数。在将字符类型的数据转换为日期型时，要保证字符型数据为有效地日期，否则转换不能成功
数字型（NUMBER）的数据与日期型（DATE）的数据之间不能直接进行转换。必须将其中的一种数据类型先转换成字符型，之后再将字符型转换成其中的另一种数据类型。

注意：尽管oracle提供了数据类型之间的隐含转换方法，但是应该避免使用这种方式。

不同数据类型之间的显式转换
数据类型之间的显式转换（使用oracle的转换函数）：TO_CHAR、TO_NUMBER、TO_DATE
TO_CHAR(日期，'fmt'):该函数的这种格式将日期变成长字符串，其中fmt为日期模式
SELECT TO_CHAR(SYSDATE,'DD-MM-YY') FROM dual; --23-12-16
日期参数格式以及含义说明：
D 一周中第几天 --6
DAY 天的名字，星期几 --星期五
DD 月的第几天 -23
DDD 年中的第几天 --358
DY 天的简写名，星期几 --星期五
IW ISO标准年中的第几周 --51
IYYY ISO标准的四位年份 --2016
YYY,YY,Y 年份的最后三位，两位，一位
HH 小时，按12小时计，舍掉后面的分秒
HH24 小时，按24小时计
MI 分
SS 秒
MON 月份的简写
W 该月的第几个星期
WW 年中的第几个星期

interval关键字：表示间隔的意思
SELECT SYSDATE - INTERVAL '7' hour FROM dual; --2016/12/23 14:25:14 当前时间减去7小时的事件
可以灵活使用各中模式，可以和固定字符结合使用：
SELECT TO_CHAR(SYSDATE,'DD"日" MONTH YEAR') FROM dual; --23日 12月 TWENTY SIXTEEN

TO_CHAR(数字，‘fmt’)：该函数的这种格式吧数字型数据转换成变长字符串。其中fmt为数字模式。
常用的数字模式包括：
9：一位数字
0：显示前导零
$: 显示美元号
L: 显示本地货币号
.: 显示小数点
,: 显示前位符
MI: 在数的右边显示减号
PR: 把负数用尖括号括起来
SELECT TO_CHAR(9876,'999999') FROM dual; -- 9876（默认使用空格左填充不足的位数）
SELECT TO_CHAR(10078,'$99,999.00') FROM dual; -- $10,078.00
SELECT TO_CHAR(107558,'$99,999.00') FROM dual; --###### 给出的实际数据超过模式的最大值，就会出现全部##

TO_NUMBER(字符串，['fmt'])：该函数把字符串转换成数字
TO_DATE(字符串,['fmt'])：该函数把字符串转换成日期型数据

两千年问题：
由于早期计算机硬件非常昂贵，程序员们在编程时为了节省内存资源就用两位数来表示年份，如：73表示1973.这样过了1999年，到了新世纪就会出现日期混乱的问题，不知道93指的是1993还是2093.
oracle引入RR算法解决两千年问题：
RR算法：
1、如果当前年份的最后两位数（即不包括世纪）为0~49，并且指定的年份的而最后两位数也为0~49，则返回的日期在本世纪
2、如果当前年份的最后两位数（即不包括世纪）为0~49，且指定的年份为50~99，则返回的日期为上一世纪
3、如果当前年份的最后两位数（即不包括世纪）为50~99，且指定的年份的最后两位数为0~49，则返回的日期为下一世纪
4、如果当前年份的最后两位数（即不包括世纪）为50~99，且指定的年份的最后两位数也为50~99，则返回的日期在本世纪

SELECT 'ASAD' FROM dual WHERE SYSDATE BETWEEN '30-12月-2016' AND '12-1月-2017' -- 空
SELECT 'ASAD' FROM dual WHERE SYSDATE BETWEEN '22-12月-2016' AND '12-1月-2017' --ASAD
注意：现在最好年份都使用四位