请注意，本文编写于 914 天前，最后修改于 910 天前，其中某些信息可能已经过时。
前言:
包含oracle常用函数用法的详解并提示例讲解，文章末尾有可以直接下载帮助文件！
1数值型函数
01.返回绝对值.abs()
ABS(x)
【功能】返回x的绝对值
【参数】x，数字型表达式
【返回】数字
【示例】
select abs(100),abs(-100) from dual;02.返回正负值.sign()
sign(x)
【功能】返回x的正负值
【参数】x，数字型表达式
【返回】数字，若为正值返回1，负值返回-1，0返回0
【示例】
select sign(100),sign(-100),sign(0) from dual;03.返回较大的最小整数.ceil()
ceil(x)
【功能】返回大于等于x的最小整数值
【参数】x，数字型表达式
【返回】数字
【示例】
select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;
返回4，5，004.返回较小的最大整数.floor()
floor(x)
【功能】返回小于等于x的最大整数值
【参数】x，数字型表达式
【返回】数字
【示例】
select floor(3.1),floor(2.8+1.3),floor(0) from dual;
返回4，5，005.返回x的y次幂.power(x,y)
power(x,y)
【功能】返回x的y次幂
【参数】x，y 数字型表达式
【返回】数字
【示例】
select power(2.5,2),power(1.5,0),power(20,-1) from dual;
返回：6.25,1,0.05
【相近】exp(y)
返回e的y次幂。（e为数学常量）
【关系】z=power(x,y),则y=1/log(z,x) (条件z,x>0)06.返回常量e的y次幂.exp(y)
exp(y)
【功能】返回e的y次幂（e为数学常量）
【参数】y，数字型表达式
【返回】数字
【示例】
select exp(3),exp(0),exp(-3) from dual;
返回：20.0855369,1 ,0.049787068
【相近】power(x,y)
返回e的y次幂。
【相反】ln(y)
返回e为底的自然对数。07.返回以x为底的y的对数.log(x,y)
log(x,y)
【功能】返回以x为底的y的对数
【参数】x，y,数字型表达式，
【条件】x,y都必须大于0
【返回】数字
【示例】
select power(4,2),log(16,2),1/log(16,4) from dual;
返回：16,0.25,2
select power(6.5,3),log(274.625,3),1/log(power(6.5,3),6.5) from dual;
返回： 274.625 ， 0.195642521 ， 3
【相近】ln(y)
返回e为底的y的对数。（e为数学常量）
【关系】z=power(x,y),则y=1/log(z,x) (条件z,x>0)08.返回以常量e为底的y的对数.ln(y)
ln(y)
【功能】返回以e为底的y的对数（e为数学常量）
【参数】y，数字型表达式 (条件y>0)
【返回】数字
【示例】
select exp(3),exp(-3),ln(20.0855369),ln(0.049787068) from dual;
返回：20.0855369 , 0.049787068 , 3 , -3
【相近】log(x,y)
返回以x为底的y的对数
【相反】exp(y)
返回e的y次幂09.返回x除以y的余数.mod(x,y)
mod(x,y)
【功能】返回x除以y的余数
【参数】x,y，数字型表达式
【返回】数字
【示例】
select mod(23,8),mod(24,8) from dual;
返回：7,010.返回四舍五入后的值.round()
round(x[,y])
【功能】返回四舍五入后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
【返回】数字
【示例】
select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;
返回： 5555.67 , 5600 , 5556
【相近】trunc(x[,y])
返回截取后的值，用法同round(x[,y]),只是不四舍五入11.返回截取后的值.trunc()
trunc(x[,y])
【功能】返回x按精度y截取后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【返回】数字
【示例】
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;
返回：5555.66 5500 5555
【相近】round(x[,y])
返回截取后的值，用法同trunc(x[,y]),只是要做四舍五入12.返回x的平方根.sqrt(x)
sqrt(x)
【功能】返回x的平方根
【参数】x数字型表达式
【返回】数字
【示例】
select sqrt(64),sqrt(10) from dual;
返回：8 , 3.1622776613.三角函数
SIN(x)
【功能】返回一个数字的正弦值
【示例】select sin(1.57079) from dual;
返回： 1
SIGH(x)
【功能】返回双曲正弦的值
【示例】select sin(20),sinh(20) from dual;
返回：0.91294525, 242582598
COS(x)
【功能】返回一个给定数字的余弦
【示例】select cos(-3.1415927) from dual;
返回： -1
COSH(x)
【功能】返回一个数字反余弦值
【示例】select cosh(20) from dual;
返回：242582598
TAN
【功能返回数字的正切值
【示例】select tan(20),tan(10) from dual;
返回：2.2371609 ,0.64836083
TANH
【功能返回数字n的双曲正切值
【示例】select tanh(20),tan(20) from dual;
返回：1 ,2.2371609
ASIN(x)
【功能】给出反正弦的值
【示例】select asin(0.5) from dual;
返回：0.52359878
ACOS(x)
【功能】给出反余弦的值
【示例】select acos(-1) from dual;
返回：3.1415927
ATAN(x)
【功能】返回一个数字的反正切值
【示例】 select atan(1) from dual;
返回：0.785398162字符型函数
01.返回字符的ASCII码.ASCII()
ASCII(x1)
【功能】：返回字符表达式最左端字符的ASCII 码值。
【参数】：x1，字符表达式
【返回】：数值型
【示例】
SQL> select ascii('A') A,ascii('a') a,ascii(' ') space,ascii('示') hz from dual;
A A SPACE hz
--------- --------- --------- ---------
65 97 32 51902
【说明】在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
如果最左端是汉字，只取汉字最左半边字符的ASCII 码
【互反函数】：chr()02.返回ASCII码为x的字符.CHR(x)
CHR(n1)
【功能】：将ASCII 码转换为字符。
【参数】：n1,为0 ~ 255，整数
【返回】：字符型
【示例】
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
【互反函数】：ASCII03.连接两个字符串.CONCAT()
CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1，c2 字符型表达式
【返回】字符型
同：c1||c2
【示例】
select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转2304.把每个单词首个字母变为大写.INITCAP()
INITCAP(c1)
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】c1字符型表达式
【返回】字符型
【示例】
SQL> select initcap('smith abc aBC') upp from dual;
UPP
-----
Smith Abc Abc05.将整个字符串转换为小写.LOWER()
LOWER(c1)
【功能】：将字符串全部转为小写
【参数】：c1，字符表达式
【返回】：字符型
【示例】
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
【同类】UPPER()将字符串全部转为大写。06.将整个字符串转换为大写.UPPER()
UPPER(c1)
【功能】将字符串全部转为大写
【参数】c1，字符表达式
【返回】字符型
【示例】
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
【同类】LOWER()将字符串全部转为小写07.把每个单词首个字母变为大写.NLS_INITCAP()
NLS_INITCAP(x[,y])
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】x字符型表达式
【参数】Nls_param可选，
查询数据级的NLS设置:select * from nls_database_parameters;
例如：
指定排序的方式(nls_sort=) 。
nls_sort=SCHINESE_RADICAL_M（部首、笔画）
nls_sort=SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））
【返回】字符型
【示例】
select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：Ab Cde, A C B D E
select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;08.把整个字符串转换为小写.NLS_LOWER()
NLS_LOWER(x[,y])
【功能】返回字符串并将字符串的变为小写;
【参数】x字符型表达式
【参数】Nls_param可选，指定排序的方式(nls_sort=) 。
SCHINESE_RADICAL_M（部首、笔画）
SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））
【返回】字符型
【示例】
select nls_LOWER('ab cde') "test",nls_LOWER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：ab cde,a c b d e09.将整个字符串转换为大写.NLS_UPPER()
NLS_UPPER(x[,y])
【功能】返回字符串并将字符串的转换为大写;
【参数】x字符型表达式
【参数】Nls_param可选，指定排序的方式(nls_sort=) 。
SCHINESE_RADICAL_M（部首、笔画）
SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））
【返回】字符型
【示例】
select NLS_UPPER('ab cde') "test",NLS_UPPER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：AB CDE,A C B D E10.字符串中搜索字符位置(全角算1字符).INSTR()
INSTR(C1,C2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 第J次出现的位置,默认为1
【返回】数值
【示例】select instr('oracle traning','ra',1,2) instring from dual;
返回：9
【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;
返回：3,511.字符串中搜索字符位置(全角算2字符).INSTRB()
INSTRB(C1,C2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
【说明】多字节符(汉字、全角符等)，按2个字符计算
【参数】
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 第J次出现的位置,默认为1
【返回】数值
【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;
返回：3,512.返回字符串的长度(全角算1字符).LENGTH()
LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;
length('高乾竞') length('北京市海锭区') length('北京TO_CHAR')
----------------- ---------------- ----------------------------
3 6 912.返回字符串的长度(全角算2字符).LENGTHB()
LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按2个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),lengthB('高乾竞') from dual;
length('高乾竞') lengthB('高乾竞')
----------------- ----------------
3 613.返回字符串的长度(其它).LENGTHC().LENGTH2().LENGTH4()
LENGTHC(c1).LENGTH2(c1).LENGTH4(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;
Oracle中的字符函数中，有一类函数是求字符长度的函数，length、lengthB、lengthC、length2、length4几个函数中比较常用的是length、lengthB。
他们的含义分别是：
Length函数返回字符的个数，使用定义是给定的字符集来计算字符的个数
LENGTHB给出该字符串的byte
LENGTHC使用纯Unicode
LENGTH2使用UCS2
LENGTH4使用UCS4
下面使一些例子：
Select length('你好') from dual; 2
Select lengthB('你好'),lengthC('你好'),length2('你好'), length4('你好') from dual;14.在左边添加字符.LPAD()
LPAD(c1,n[,c2])
【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止
【参数】C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符
如果如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符
【示例】
SQL> select lpad('gao',10,'*') from dual;
lpad('gao',10,'*')
-----------------
*******gao
不够字符则用*来填满
【相似】RPAD()在列的右边粘贴字符
【相反】LTRIM() 删除左边出现的字符串15.在右边添加字符.RPAD()
RPAD(c1,n[,c2])
【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止
【参数】C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符
如果如果c1长度小于n，c1和c2连接后大于n，则返回连接后的左边n个字符
如果如果c1长度小于n，c1和c2连接后小于n，则返回c1与多个重复c2连接(总长度>=n)后的左边n个字符
【示例】
SQL> select rpad('gao',10,'*a') from dual;
rpad('gao',10,'*a')
/-----------------
gao*a*a*a*
【相似】LPAD()在列的左边粘贴字符
【相反】RTRIM() 删除右边出现的字符串16.删除左边字符串.LTRIM()
LTRIM(c1,[,c2])
【功能】删除左边出现的字符串
【参数】C1 字符串
c2 追加字符串,默认为空格
【返回】字符型
【示例】
SQL> select LTRIM(' gao qian jing',' ') text from dual;
或：select LTRIM(' gao qian jing') text from dual;
text
-----------------
gao qian jing
【相似】RTRIM()删除右边出现的字符串
【相反】LPAD() 在列的左边粘贴字符``17.删除右边字符串.RTRIM()
RTRIM(c1,[,c2])
【功能】删除右边出现的字符串
【参数】C1 字符串
c2 追加字符串,默认为空格
【返回】字符型
【示例】
SQL> select RTRIM('gao qian jingXXXX','X') text from dual;
text
-----------------
gao qian jing
【相似】LTRIM()删除左边出现的字符串
【相反】RPAD() 在列的右边粘贴字符18.替换子字符串.REPLACE()
REPLACE(c1,c2[,c3])
【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
【参数】
c1 希望被替换的字符或变量
c2 被替换的字符串
c3 要替换的字符串，默认为空(即删除之意，不是空格)
【返回】字符型
【示例】
SQL> select replace('he love you','he','i') test from dual;
test
------------------------------
i love you19.字符串语音表示形式.SOUNDEX()
SOUNDEX(c1)
【功能】返回字符串参数的语音表示形式
【参数】c1,字符型
【返回】字符串
【说明】相对于比较一些读音相同，但是拼写不同的单词是非常有用的。
计算语音的算法：
1.保留字符串首字母，但删除a、e、h、i、o、w、y
2.将下表中的数字赋给相对应的字母
(1) 1：b、f、p、v
(2) 2：c、g、k、q、s、x、z
(3) 3：d、t
(4) 4：l
(5) 5：m、n
(6) 6：r
3. 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个
4.只返回前4个字节，不够用0填充
示例：
soundex('two'),soundex('too'),soundex('to')，他们的结果都是T000
soundex('cap'),soundex('cup')，他们的结果都是C100
soundex('house'),soundex('horse')，他们的结果都分别是H200，H62020.截取子字符串(全角算1字符).SUBSTR()
SUBSTR(c1,n1[,n2])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.
【返回】字符型
【示例】
SQL> select substr('13088888888',3,8) test from dual;
test
--------
0888888821.截取子字符串(全角算2字符).SUBSTRB()
SUBSTRB(c1,n1[,n2])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按2个字符计算
【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.
【返回】字符型,如果从多字符右边开始,则用空格表示。
【示例】
select substr('我手机13012345678',4,11),substrb('我手机13012345678',4,11),substrb('我手机13012345678',3,11) test from dual;
返回:13012345678, 机13012345,手机130123422.替换子字符.TRANSLATE()
TRANSLATE(c1,c2,c3)
【功能】将字符表达式值中，指定字符替换为新字符
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】
c1 希望被替换的字符或变量
c2 查询原始的字符集
c3 替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符
如果c3长度大于c2，则c3长出后面的字符无效
如果c3长度小于c2，则c2长出后面的字符均替换为空(删除)
如果c3长度为0，则返回空字符串。
如果c2里字符重复，按首次位置为替换依据
【返回】字符型
【示例】
select TRANSLATE('he love you','he','i'),
TRANSLATE('重庆的人','重庆的','上海男'),
TRANSLATE('重庆的人','重庆的重庆','北京男士们'),
TRANSLATE('重庆的人','重庆的重庆','1北京男士们'),
TRANSLATE('重庆的人','1重庆的重庆','北京男士们') from dual;
返回：i love you,上海男人,北京男人,1北京人,京男士人23.删除左边和右边字符串.TRIM()
TRIM(c1 from c2)
【功能】删除左边和右边出现的字符串
【参数】C2 删除前字符串
c1 删除字符串,默认为空格
【返回】字符型
【示例】
select TRIM('X' from 'XXXgao qian jingXXXX'),TRIM('X' from 'XXXgaoXXjingXXXX') text from dual;
返回：gao qian jing gaoXXjing
【相似】LTRIM()删除左边出现的字符串 RTRIM()删除右边出现的字符串3日期函数
01.返回系统当前日期.sysdate
sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期
【示例】select sysdate hz from dual;
返回：2008-11-502.返回指定月数后的日期.add_months()
add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期
【示例】select sysdate,add_months(sysdate,3) hz from dual;
返回：2008-11-5,2009-2-503.返回本月最后一天的日期.last_day()
last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。
【参数】：d1，日期型
【返回】：日期
【示例】select sysdate,last_day(sysdate) hz from dual;
返回：2008-11-5，2008-11-3004.返回2个日期间隔月数.months_between()
months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型
【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数
【示例】
select sysdate,
months_between(sysdate,to_date('2006-01-01','YYYY-MM-DD')),
months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD')) from dual;
返回：2008-11-5,34.16,-85.8405.返回时区的对应时间.NEW_TIME()
NEW_TIME(dt1,c1,c2)
【功能】：给出时间dt1在c1时区对应c2时区的日期和时间
【参数】：dt1，d2 日期型
【返回】：日期时间
【参数】：c1,c2对应的 时区及其简写
大西洋标准时间：AST或ADT
阿拉斯加_夏威夷时间：HST或HDT
英国夏令时：BST或BDT
美国山区时间：MST或MDT
美国中央时区：CST或CDT
新大陆标准时间：NST
美国东部时间：EST或EDT
太平洋标准时间：PST或PDT
格林威治标准时间：GMT
Yukou标准时间：YST或YDT
【示例】
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,
to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
返回：
BJ_TIME LOS_ANGLES
------------------- -------------------
2008.11.05 20:11:58 2008.11.06 03:11:58
【示例】
select sysdate bj_time,
new_time(sysdate,'PDT','GMT') los_angles from dual;
返回：
BJ_TIME LOS_ANGLES
------------------- -------------------
2008-11-05 20:11:58 2008-11-06 03:11:5806.四舍五入后的期间第一天.round()
round(d1[,c1])
【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d
最近月初日期：month或mon或mm或rm
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)
最近世纪初日期：cc或scc
【返回】：日期
【示例】
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期,
round(sysdate,'year') 最近年初日期 from dual;07.返回日期所在期间的第一天.trunc()
trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）
最近月初日期：month或mon或mm或rm
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)
最近世纪初日期：cc或scc
【返回】：日期
【示例】
select sysdate 当时日期,
trunc(sysdate) 今天日期,
trunc(sysdate,'day') 本周星期日,
trunc(sysdate,'month') 本月初,
trunc(sysdate,'q') 本季初日期,
trunc(sysdate,'year') 本年初日期 from dual;08.返回下周某一天的日期.NEXT_DAY()
next_day(d1[,c1])
【功能】：返回日期d1在下周，星期几(参数c1)的日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应:星期一，星期二，星期三……星期日
【返回】：日期
【示例】
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;09.提取时间日期中数据.extract()
extract(c1 from d1)
【功能】：日期/时间d1中，参数(c1)的值
【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)
【参数表】：c1对应的参数表详见示例
【返回】：字符
【示例】
select
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
from dual;
select extract (YEAR from date '2001-2-16' ) from dual;
select sysdate 当前日期,
extract(hour from timestamp timestamp sysdate) 小时,
extract(DAY from sysdate ) 日,
extract(MONTH from sysdate ) 月,
extract(YEAR from sysdate ) 年
from dual;10.返回会话中的日期和时间.localtimestamp
localtimestamp
【功能】：返回会话中的日期和时间
【参数】：没有参数，没有括号
【返回】：日期
【示例】select localtimestamp from dual;
返回：14-11月-08 12.35.37.453000 上午11.返回当前会话时区中的当前日期和时间.current_timestamp
current_timestamp
【功能】：以timestamp with time zone数据类型返回当前会话时区中的当前日期
【参数】：没有参数，没有括号
【返回】：日期
【示例】select current_timestamp from dual;
返回：14-11月-08 12.37.34.609000 上午 +08:0012.返回当前会话时区中的当前日期.current_date
dbtimezone
【功能】：返回时区
【参数】：没有参数，没有括号
【返回】：字符型
【示例】select dbtimezone from dual;13.返回数据库时区设置.dbtimezone
dbtimezone
【功能】：返回时区
【参数】：没有参数，没有括号
【返回】：字符型
【示例】select dbtimezone from dual;14.返回当前会话时区.SESSIONTIMEZONE
SESSIONTIMEZONE
【功能】：返回会话时区
【参数】：没有参数，没有括号
【返回】：字符型
【示例】select dbtimezone,SESSIONTIMEZONE from dual;
返回:+00:00 +08:0015.变动日期时间数值.INTERVAL
INTERVAL c1 set1
【功能】：变动日期时间数值
【参数】：c1为数字字符串或日期时间字符串，set1为日期参数
【参数表】：set1具体参照示例
【返回】：日期时间格式的数值,前面多个+号
以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟
【示例】
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY), --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;4转换函数
01.字符串转换为rowid值.chartorowid()
chartorowid(c1) 。。
【功能】转换varchar2类型为rowid值
【参数】c1,字符串，长度为18的字符串，字符串必须符合rowid格式
【返回】返回rowid值
【示例】
SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;
【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.02.rowid值转换字符串.ROWIDTOCHAR()
ROWIDTOCHAR(rowid) 。。
【功能】转换rowid值为varchar2类型
【参数】rowid,固定参数
【返回】返回长度为18的字符串
【示例】
SELECT ROWIDTOCHAR(rowid) FROM DUAL;
【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.03.字符串语言字符集转换.CONVERT()
CONVERT(c1,set1,set2)
【功能】将源字符串c1 从一个语言字符集set2转换到另一个目的set1字符集
【参数】c1,字符串，set1,set2为字符型参数
【返回】字符串
【示例】
select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
select convert(name,'us7ascii','zhs16cgb231280') "conversion" from dual;04.16进制转换为二进制.HEXTORAW()
HEXTORAW(c1)
【功能】将一个十六进制构成的字符串转换为二进制
【参数】c1,十六进制的字符串
【返回】字符串
【示例】
select HEXTORAW('A123') from dual;05.二进制转换为16进制.RAWTOHEX()
RAWTOHEX(c1)
【功能】将一个二进制构成的字符串转换为十六进制
【参数】c1,二进制的字符串
【返回】字符串
【示例】
select RAWTOHEX('A123') from dual;06.数字或日期转换为字符串.TO_CHAR()
TO_CHAR(x[[,c2],C3])
【功能】将日期或数据转换为char数据类型
【参数】
x是一个date或number数据类型。
c2为格式参数
c3为NLS设置参数
如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。
如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
【返回】varchar2字符型
【说明1】x为数据型时
【示例】
to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'
to_char(852,'xxxx') 返回' 354'
【说明2】x为日期型,c2可用参数
【示例】
to_char(sysdate,'d') 每周第几天
to_char(sysdate,'dd') 每月第几天
to_char(sysdate,'ddd') 每年第几天
to_char(sysdate,'ww') 每年第几周
to_char(sysdate,'mm') 每年第几月
to_char(sysdate,'q') 每年第几季
to_char(sysdate,'yyyy') 年
SQL> select to_char(sysdate,' PM yyyy-mm-dd hh24:mi:sssss AD year mon day ddd iw') FROM DUAL;
TO_CHAR(SYSDATE,'PMYYYY-MM-DDH
--------------------------------------------------------------------------------
上午 2008-03-27 09:58:35917 公元 two thousand eight 3月 星期四 087 13
SQL> SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF5') FROM DUAL;
TO_CHAR(SYSTIMESTAMP,'HH24:MI:
------------------------------
10:02:28.90000
SQL>SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL
TO_CHAR(SYSDATE,'DSDL')
-----------------------------------
2008-03-27 2008年3月27日 星期四
【示例】带C3示例
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
返回：monday07.字符串转换为日期型.TO_DATE()
TO_DATE(X[,c2[,c3]])
【功能】将字符串X转化为日期型
【参数】c2,c3,字符型，参照to_char()
【返回】字符串
如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x
【相反】 to_char(date[,c2[,c3]])
【示例】
select to_date('199912','yyyymm'),
to_date('2000.05.20','yyyy.mm.dd'),
(date '2008-12-31') XXdate,
to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp '2008-12-31 12:31:30') XXtimestamp
from dual;08.字符串转换为数字型.TO_NUMBER()
TO_NUMBER(X[[,c2],c3])
【功能】将字符串X转化为数字型
【参数】c2,c3,字符型，参照to_char()
【返回】数字串
【相反】 to_char(date[[,c2],c3])
【示例】
select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;
转换为16进制。
TO_CHAR(100,'XX')= 6409.半角转化为全角.TO_MULTI_BYTE()
TO_MULTI_BYTE(c1)
【功能】将字符串中的半角转化为全角
【参数】c1,字符型
【返回】字符串
【示例】
SQL> select to_multi_byte('高A') text from dual;
test
--
高Ａ10.全角转化为半角.to_single_byte()
to_single_byte(c1)
【功能】将字符串中的全角转化为半角
【参数】c1,字符型
【返回】字符串
【示例】
SQL> select to_multi_byte('高Ａ') text from dual;
test
----
高A11.字符集名称转为ID.nls_charset_id()
nls_charset_id(c1)
【功能】返回字符集名称参应id值
【参数】c1,字符型
【返回】数值型
sql> select nls_charset_id('zhs16gbk') from dual;
nls_charset_id('zhs16gbk')
--------------------------
85212.字符集ID转为名称.nls_charset_name()
nls_charset_name(n1)
【功能】返回字符集名称参应id值
【参数】n1,数值型
【返回】字符型
sql> select nls_charset_name(852) from dual;
nls_char
--------
zhs16gbk5聚组函数
01.统计平均值.AVG()
AVG([distinct|all]x)
【功能】统计数据表选中行x列的平均值。
【参数】all表示对所有的值求平均值,distinct只对不同的值求平均值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
执行统计：
select avg(distinct sal),avg(all sal),avg(sal) from table3;
结果： 3333.33 2592.59 2592.5902.统计合计值.SUM()
SUM([distinct|all]x)
【功能】统计数据表选中行x列的合计值。
【参数】all表示对所有的值求合计值,distinct只对不同的值求合计值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
执行统计：
select SUM(distinct sal),SUM(all sal),SUM(sal) from table3;
结果： 6666.66 7777.77 7777.7703.统计标准误差.STDDEV()
STDDEV([distinct|all]x)
【功能】统计数据表选中行x列的标准误差。
【参数】all表示对所有的值求标准误差,distinct只对不同的值求标准误差，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
执行统计：
select STDDEV(distinct sal),STDDEV(all sal),STDDEV(sal) from table3;
结果： 3142.69366257674 2565.99863039714 2565.9986303971404.统计方差.VARIANCE()
VARIANCE([distinct|all]x)
【功能】统计数据表选中行x列的方差。
【参数】all表示对所有的值求方差,distinct只对不同的值求方差，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，只能为数值型字段
【返回】数字值
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;
执行统计：
select VARIANCE(distinct sal),VARIANCE(all sal),VARIANCE(sal) from table3;
结果： 9876523.4568 6584348.9712 6584348.971205.统计查询所得的行数.count()
RAWTOHEX(c1)
【功能】将一个二进制构成的字符串转换为十六进制
【参数】c1,二进制的字符串
【返回】字符串
【示例】
select RAWTOHEX('A123') from dual;06.统计最大值.MAX()
MAX([distinct|all]x)
【功能】统计数据表选中行x列的最大值。
【参数】all表示对所有的值求最大值,distinct只对不同的值求最大值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，可为数字、字符或日期型字段
【返回】对应x字段类型
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;
执行统计：
select MAX(distinct sal),MAX(xm) from table3;
结果：5555.55 zhu07.统计最小值.MIN()
MIN([distinct|all]x)
【功能】统计数据表选中行x列的最大值。
【参数】all表示对所有的值求最大值,distinct只对不同的值求最大值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。
【参数】x，可为数字、字符或日期型字段
【返回】对应x字段类型
注：字符型字段，将忽略空值(NULL)
【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;
执行统计：
select MIN(distinct sal),MIN(xm),MIN(distinct xm),MIN(all xm) from table3;
结果：0 gao gao gao6分析函数
00.oracle分析函数
oracle分析函数--SQL*PLUS环境
一、总体介绍
12.1 分析函数如何工作
语法 FUNCTION_NAME(<参数>,…) OVER (<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC DESC> <NULLS FIRST NULLS LAST>> <WINDOWING子句>) PARTITION子句 ORDER BY子句 WINDOWING子句 缺省时相当于RANGE UNBOUNDED PRECEDING
1. 值域窗(RANGE WINDOW)
RANGE N PRECEDING 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 –/+ N)的所有行,因此与ORDER BY子句有关系。
2. 行窗(ROW WINDOW)
ROWS N PRECEDING 选定窗为当前行及之前N行。
还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING
函数 AVG(<distinct all> eXPr)
一组或选定窗中表达式的平均值 CORR(expr, expr) 即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关) ~ 1(正相关),0表示不相关
COUNT(<distinct> <*> <expr>) 计数
COVAR_POP(expr, expr) 总体协方差
COVAR_SAMP(expr, expr) 样本协方差
CUME_DIST 累积分布,即行在组中的相对位置,返回0 ~ 1
DENSE_RANK 行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数
FIRST_VALUE 一个组的第一个值
LAG(expr, <offset>, <default>) 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行)
LAST_VALUE 一个组的最后一个值
LEAD(expr, <offset>, <default>) 访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行)
MAX(expr) 最大值
MIN(expr) 最小值
NTILE(expr) 按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 ~ 4的值,而不能等分则多出的部分在值最小的那组
PERCENT_RANK 类似CUME_DIST,1/(行的序数 - 1)
RANK 相对序数,答应并列,并空出随后序号
RATIO_TO_REPORT(expr) 表达式值 / SUM(表达式值)
ROW_NUMBER 排序的组中行的偏移
STDDEV(expr) 标准差
STDDEV_POP(expr) 总体标准差
STDDEV_SAMP(expr) 样本标准差
SUM(expr) 合计
VAR_POP(expr) 总体方差
VAR_SAMP(expr) 样本方差
VARIANCE(expr) 方差
REGR_ xxxx(expr, expr) 线性回归函数
REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)
REGR_INTERCEPT：返回回归线的y截距，等于
AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
REGR_COUNT：返回用于填充回归线的非空数字对的数目
REGR_R2：返回回归线的决定系数，计算式为：
If VAR_POP(expr2) = 0 then return NULL
If VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1
If VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0 then
return POWER(CORR(expr1,expr),2)
REGR_AVGX：计算回归线的自变量(expr2)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr2)
REGR_AVGY：计算回归线的应变量(expr1)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr1)
REGR_SXX： 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
REGR_SYY： 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
REGR_SXY: 返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
首先：创建表及接入测试数据
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));
insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;
二、具体应用：
1、分组求和：
1）GROUP BY子句
--A、GROUPING SETS
select id,area,stu_type,sum(score) score
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;
/*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )
等效于
select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b
union all
select null, null, c, sum( d ) from t group by c
)
*/
--B、ROLLUP
select id,area,stu_type,sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;
/*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);
等效于
select * from (
select a, b, c, sum( d ) from t group by a, b, c
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/
--C、CUBE
select id,area,stu_type,sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;
/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)
等效于
select a, b, c, sum( d ) from t
group by grouping sets(
( a, b, c ),
( a, b ), ( a ), ( b, c ),
( b ), ( a, c ), ( c ),
() )
*/
--D、GROUPING
/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/
select decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;
二、OVER()函数的使用
1、统计名次——DENSE_RANK(),ROW_NUMBER()
1)允许并列名次、名次不间断，DENSE_RANK()，结果如122344456……
将score按ID分组排名：dense_rank() over(partition by id order by score desc)
将score不分组排名：dense_rank() over(order by score desc)
select id,area,score,
dense_rank() over(partition by id order by score desc) 分组id排序,
dense_rank() over(order by score desc) 不分组排序
from students order by id,area;
2)不允许并列名次、相同值名次不重复，ROW_NUMBER()，结果如123456……
将score按ID分组排名：row_number() over(partition by id order by score desc)
将score不分组排名：row_number() over(order by score desc)
select id,area,score,
row_number() over(partition by id order by score desc) 分组id排序,
row_number() over(order by score desc) 不分组排序
from students order by id,area;
3)允许并列名次、复制名次自动空缺，rank()，结果如12245558……
将score按ID分组排名：rank() over(partition by id order by score desc)
将score不分组排名：rank() over(order by score desc)
select id,area,score,
rank() over(partition by id order by score desc) 分组id排序,
rank() over(order by score desc) 不分组排序
from students order by id,area;
4)名次分析，cume_dist()——-最大排名/总个数
函数：cume_dist() over(order by id)
select id,area,score,
cume_dist() over(order by id) a, --按ID最大排名/总个数
cume_dist() over(partition by id order by score desc) b, --ID分组中，scroe最大排名值/本组总个数
row_number() over (order by id) 记录号
from students order by id,area;
5)利用cume_dist()，允许并列名次、复制名次自动空缺，取并列后较大名次，结果如22355778……
将score按ID分组排名：cume_dist() over(partition by id order by score desc)*sum(1) over(partition by id)
将score不分组排名：cume_dist() over(order by score desc)*sum(1) over()
select id,area,score,
sum(1) over() as 总数,
sum(1) over(partition by id) as 分组个数,
(cume_dist() over(partition by id order by score desc))*(sum(1) over(partition by id)) 分组id排序,
(cume_dist() over(order by score desc))*(sum(1) over()) 不分组排序
from students order by id,area
2、分组统计－－sum(),max(),avg(),RATIO_TO_REPORT()
select id,area,
sum(1) over() as 总记录数,
sum(1) over(partition by id) as 分组记录数,
sum(score) over() as 总计 ,
sum(score) over(partition by id) as 分组求和,
sum(score) over(order by id) as 分组连续求和,
sum(score) over(partition by id,area) as 分组ID和area求和,
sum(score) over(partition by id order by area) as 分组ID并连续按area求和,
max(score) over() as 最大值,
max(score) over(partition by id) as 分组最大值,
max(score) over(order by id) as 分组连续最大值,
max(score) over(partition by id,area) as 分组ID和area求最大值,
max(score) over(partition by id order by area) as 分组ID并连续按area求最大值,
avg(score) over() as 所有平均,
avg(score) over(partition by id) as 分组平均,
avg(score) over(order by id) as 分组连续平均,
avg(score) over(partition by id,area) as 分组ID和area平均,
avg(score) over(partition by id order by area) as 分组ID并连续按area平均,
RATIO_TO_REPORT(score) over() as "占所有%",
RATIO_TO_REPORT(score) over(partition by id) as "占分组%",
score from students;
3、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据
取前面记录的值：lag(score,n,x) over(order by id)
取后面记录的值：lead(score,n,x) over(order by id)
参数：n表示移动N条记录，X表示不存在时填充值，iD表示排序列
select id,lag(score,1,0) over(order by id) lg,score from students;
select id,lead(score,1,0) over(order by id) lg,score from students;
4、FIRST_VALUE()、LAST_VALUE()
取第起始1行值：first_value(score,n) over(order by id)
取第最后1行值：LAST_value(score,n) over(order by id)
select id,first_value(score) over(order by id) fv,score from students;
select id,last_value(score) over(order by id) fv,score from students;01.连续求和分析函数.sum(...) over(...)
sum(...) over ...
【功能】连续求和分析函数
【参数】具体参示例
【说明】Oracle分析函数
NC示例：
select bdcode,sum(1) over(order by bdcode) aa from bd_bdinfo
【示例】
1.原表信息： SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
20 SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30 ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /
DEPTNO ENAME SAL 连续求和 总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /
DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
10 CLARK 2450 2450 8750 28 2450 29025 8.44
KING 5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48
20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79
FORD 3000 4100 10875 27.59 12850 29025 10.34
JONES 2975 7075 10875 27.36 15825 29025 10.25
SCOTT 3000 10075 10875 27.59 18825 29025 10.34
SMITH 800 10875 10875 7.36 19625 29025 2.76
30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
JAMES 950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31
4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 BLAKE 2850 2850 2850
ALLEN 1600 4450 4450
TURNER 1500 5950 5950
WARD 1250 8450 8450
MARTIN 1250 8450 8450
JAMES 950 9400 9400
20 SCOTT 3000 6000 15400
FORD 3000 6000 15400
JONES 2975 8975 18375
ADAMS 1100 10075 19475
SMITH 800 10875 20275
10 KING 5000 5000 25275
CLARK 2450 7450 27725
MILLER 1300 8750 29025
6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 875002.排序值分析函数.RANK()和dense_rank()
RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数
【示例】
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
其语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
例子1：
有表Table内容如下
COL1 COL2
1 1
2 1
3 2
3 1
4 1
4 2
5 2
5 2
6 2
分析功能：列出Col2分组后根据Col1排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
结果如下：
COL1 COL2 Rank
1 1 1
2 1 2
3 1 3
4 1 4
3 2 1
4 2 2
5 2 3
5 2 3
6 2 5
例子2：
TABLE：A （科目，分数）
数学，80
语文，70
数学，90
数学，60
数学，100
语文，88
语文，65
语文，77
现在我想要的结果是：（即想要每门科目的前3名的分数）
数学，100
数学，90
数学，80
语文，88
语文，77
语文，70
那么语句就这么写：
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
where t.rk<=3;
例子3：
合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
结果如下：
Rank
4
dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过
例如：表
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
例如：当rank时为：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
而如果用dense_rank时为：
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 303.排序后顺序号分析函数.ROW_NUMBER()
ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
【说明】Oracle分析函数
主要功能：用于取前几名，或者最后几名等
【示例】
表内容如下：
name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test
我想有一个sql语句，搜索的结果是
A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test
实现:
select name,seqno,description
from(select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name) where id<=3;04.取上下行数据分析函数.lag()和lead()
lag()和lead()
【语法】
lag(EXPR,<OFFSET>,<DEFAULT>)
LEAD(EXPR,<OFFSET>,<DEFAULT>)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
lead （） 下一个值 lag（） 上一个值
【参数】
EXPR是从其他行返回的表达式
OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量
DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。
【说明】Oracle分析函数
【示例】
-- Create table
create table LEAD_TABLE
(
CASEID VARCHAR2(10),
STEPID VARCHAR2(10),
ACTIONDATE DATE
)
tablespace COLM_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd'));
commit;
结果如下：
Case1 Step1 2007-1-1 Step2 2007-1-2
Case1 Step2 2007-1-2 Step3 2007-1-3 Step1 2007-1-1
Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2
Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3
Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4
Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5
Case1 Step6 2007-1-7 Step4 2007-1-6
Case2 Step1 2007-2-1 Step2 2007-2-2
Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1
Case2 Step3 2007-2-3 Step2 2007-2-2
还可以进一步统计一下两者的相差天数
select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from (
select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,
lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,
lag(stepid) over (partition by caseid order by actiondate) prestepid,
lag(actiondate) over (partition by caseid order by actiondate) preactiondate
from lead_table)
结果如下：
Case1 Step1 2007-1-1 2007-1-2 1
Case1 Step2 2007-1-2 2007-1-3 1
Case1 Step3 2007-1-3 2007-1-4 1
Case1 Step4 2007-1-4 2007-1-5 1
Case1 Step5 2007-1-5 2007-1-6 1
Case1 Step4 2007-1-6 2007-1-7 1
Case1 Step6 2007-1-7
Case2 Step1 2007-2-1 2007-2-2 1
Case2 Step2 2007-2-2 2007-2-3 1
Case2 Step3 2007-2-3
每一条记录都能连接到上/下一行的内容
lead （） 下一个值 lag（） 上一个值
select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,
lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,
lag(stepid) over (partition by caseid order by actiondate) prestepid,
lag(actiondate) over (partition by caseid order by actiondate) preactiondate
from lead_table7其它函数
01.返回数据类型、字节长度和在内部的存储位置.DUMP()
DUMP(w[,x[,y[,z]]])
【功能】返回数据类型、字节长度和在内部的存储位置.
【参数】
w为各种类型的字符串（如字符型、数值型、日期型……）
x为返回位置用什么方式表达，可为:8,10,16或17，分别表示：8/10/16进制和字符型，默认为10。
y和z决定了内部参数位置
【返回】类型 <[长度]>，符号/指数位 [数字1，数字2，数字3，......，数字20]
如：Typ=2 Len=7: 60,89,67,45,23,11,102
SELECT DUMP('ABC',1016) FROM dual;
返回结果为：Typ=96 Len=3 CharacterSet=ZHS16GBK: 41,42,43
代码 数据类型
0 对应 VARCHAR2
1 对应 NUMBER
8 对应 LONG
12 对应 DATE
23 对应 RAW
24 对应 LONG RAW
69 对应 ROWID
96 对应 CHAR
106 对应 MSSLABEL
各位的含义如下:
1.类型: Number型,Type=2 (类型代码可以从Oracle的文档上查到)
2.长度:指存储的字节数
3.符号/指数位
在存储上,Oracle对正数和负数分别进行存储转换:
正数：加1存储(为了避免Null)
负数：被101减,如果总长度小于21个字节，最后加一个102(是为了排序的需要)
指数位换算:
正数：指数=符号/指数位 - 193 (最高位为1是代表正数)
负数：指数=62 - 第一字节
4.从<数字1>开始是有效的数据位
从<数字1>开始是最高有效位,所存储的数值计算方法为：
将下面计算的结果加起来：
每个<数字位>乘以100^(指数-N) (N是有效位数的顺序位，第一个有效位的N=0)
5、举例说明
SQL> select dump(123456.789) from dual;
返回：Typ=2 Len=6: 195,13,35,57,79,91
<指数>: 195 - 193 = 2
<数字1> 13 - 1 = 12 *100^(2-0) 120000
<数字2> 35 - 1 = 34 *100^(2-1) 3400
<数字3> 57 - 1 = 56 *100^(2-2) 56
<数字4> 79 - 1 = 78 *100^(2-3) .78
<数字5> 91 - 1 = 90 *100^(2-4) .009
123456.789
SQL> select dump(-123456.789) from dual;
返回：Typ=2 Len=7: 60,89,67,45,23,11,102
算法：
<指数> 62 - 60 = 2(最高位是0，代表为负数)
<数字1> 101 - 89 = 12 *100^(2-0) 120000
<数字2> 101 - 67 = 34 *100^(2-1) 3400
<数字3> 101 - 45 = 56 *100^(2-2) 56
<数字4> 101 - 23 = 78 *100^(2-3) .78
<数字5> 101 - 11 = 90 *100^(2-4) .009
123456.789(-)
现在再考虑一下为什么在最后加102是为了排序的需要，-123456.789在数据库中实际存储为
60,89,67,45,23,11
而-123456.78901在数据库中实际存储为
60,89,67,45,23,11,91
可见，如果不在最后加上102，在排序时会出现-123456.789<-123456.78901的情况。02.返回表达式列表中最大值.greatest()
greatest(exp1,exp2,exp3,……,expn)
【功能】返回表达式列表中值最大的一个。如果表达式类型不同，会隐含转换为第一个表达式类型。
【参数】exp1……n，各类型表达式
【返回】exp1类型
【示例】
SELECT greatest(10,32,'123','2006') FROM dual;
SELECT greatest('kdnf','dfd','a','206') FROM dual;03.返回表达式列表中最小值.least()
least(exp1,exp2,exp3,……,expn)
【功能】返回表达式列表中值最小的一个。如果表达式类型不同，会隐含转换为第一个表达式类型。
【参数】exp1……n，各类型表达式
【返回】exp1类型
【示例】
SELECT least(10,32,'123','2006') FROM dual;
SELECT least('kdnf','dfd','a','206') FROM dual;04.为空值赋值.nvl().nvl2()
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致
【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型05.返回当前会话对应的数据库用户名.user
user
【功能】返回当前会话对应的数据库用户名。
【参数】无
【返回】字符型06.返回当前会话所对应的用户id号.uid
uid
【功能】返回当前会话所对应的用户id号。
【参数】无
【返回】字符型07.返回当前会话上下文属性.userenv()
userenv(parameter)
【功能】返回当前会话上下文属性。
【参数】Parameter是参数，可以用以下参数代替：
Isdba:若用户具有dba权限，则返回true,否则返回false.
Language:返回当前会话对应的语言、地区和字符集。
LANG:返回当前环境的语言的缩写
Terminal:返回当前会话所在终端的操作系统标识符。
Sessionid:返回正在使用的审计会话号.
Client_info:返回用户会话信息，若没有则返回null.
【返回】根据参数不同则类型不同
【示例】
Select userenv('isdba'),userenv('Language'),userenv('Terminal'),userenv('Client_info') from dual;08.条件取值.decode()
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
·含义解释：
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
或：
when case 条件=值1 THEN
RETURN(翻译值1)
ElseCase 条件=值2 THEN
RETURN(翻译值2)
......
ElseCase 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END
【示例】
·使用方法：
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、表、视图结构转化
现有一个商品销售表sale，表结构为：
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据：
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);09.相等返回空.NULLIF()
【语法】NULLIF (expr1, expr2)
【功能】expr1和expr2相等返回NULL，不相等返回expr110.返回列表第一个不为空的表达式.COALESCE()
COALESCE(c1, c2, ...,cn)
【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
【返回】同参数类型
【说明】从Oracle 9i版开始，COALESCE函数在很多情况下就成为替代CASE语句的一条捷径
【示例】
select COALESCE(null,3*5,44) hz from dual; 返回15
select COALESCE(0,3*5,44) hz from dual; 返回0
select COALESCE(null,'','AAA') hz from dual; 返回AAA
select COALESCE('','AAA') hz from dual; 返回AAA11.返回当前行号.rownum
rownum
【功能】返回当前行号
【参数】无
【返回】数值型12.指定一个外部二进制文件.BFILENAME()
BFILENAME(dir,file)
【功能】函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
【参数】dir是一个directory类型的对象，file为一文件名。
insert into lobdemo(key,bfile_col) values (-1,biflename('utils','file1'));13.返回X的大小(字节)数.VSIZE(X)
VSIZE(X)
【功能】返回X的大小(字节)数
【参数】x
select vsize(user),user from dual;
返回：6 asdied
select length('adfad合理') "bytesLengthIs" from dual --7
select lengthb('adfad') "bytesLengthIs" from dual --5
select lengthb('adfad合理') "bytesLengthIs" from dual --9
select vsize('adfad合理') "bytesLengthIs" from dual --9
select lengthc('adfad合理')"bytesLengthIs" from dual --7
lengthb=vsize
lengthc=length14.条件取值.case when then end
case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1>
[when <表达式条件值2> then <满足条件时返回值2>
……
[else <不满足上述条件时返回值>]]
end
【功能】当：<表达式>＝<表达式条件值1……n> 时，返回对应 <满足条件时返回值1……n>
当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，
decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2> ……,<不满足上述条件时返回值>)
【参数】
<表达式> 默认为true (逻辑型)
<表达式条件值1……n> 类型要与<表达式>类型一致，
若<表达式>为字符型，则<表达式条件值1……n>也要为字符型
【注意点】
1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名
5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序
【示例】
建立环境：
create table xqb
(xqn number(1,0));
insert into xqb xqn values(1);
insert into xqb xqn values(2);
insert into xqb xqn values(3);
insert into xqb xqn values(4);
insert into xqb xqn values(5);
insert into xqb xqn values(6);
insert into xqb xqn values(7);
commit;
查询结果：
SELECT xqn,
CASE
WHEN xqn = 1 THEN '星期一'
WHEN xqn = 2 THEN '星期二'
WHEN xqn = 3 THEN '星期三'
else '星期三以后'
END 星期
FROM xqb
另类写法
SELECT xqn,
CASE xqn
WHEN 1 THEN '星期一'
WHEN 2 THEN '星期二'
WHEN 3 THEN '星期三'
else '星期三以后'
END 星期
FROM xqb
decode正确表达：
SELECT xqn,
decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期
FROM xqb
decode错误表达：
SELECT xqn,
decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后') 星期
FROM xqb
组合条件表达：
SELECT xqn,
CASE
WHEN xqn <= 1 THEN '星期一'
WHEN xqn <= 2 THEN '星期二' --条件同：not(xqn<=1) and xqn<=2
WHEN xqn <= 3 THEN '星期三' --条件同：not(xqn<=1 and xqn<=2) and xqn<=3
else '星期三以后'
END 星期
FROM xqb15.产生32位的随机数.sys_guid()
【语法】sys_guid()
【功能】生产32位的随机数，不过中间包括一些大写的英文字母。
【返回】长度为32位的字符串，包括0－9和大写A－F
【示例】
select sys_guid() from dual16.返回系统数据.SYS_CONTEXT()
【语法】SYS_CONTEXT(c1,c2)
【功能】返回系统c1对应的c2的值。可以使用在SQL/PLSQL中，但不可以用在并行查询或者RAC环境中
【参数】
c1，'USERENV'
c2,参数表，详见示例
【返回】字符串
【示例】
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual17.生成随机数值或者字符串dbms_random
Oracle dbms_random包的用法
from:http://space.myfarmer.cn/?action-viewthread-tid-17039
1.dbms_random.value方法
dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数，但value()是最常用的，value()的用法一般有两个种，第一
function value return number;
这种用法没有参数，会返回一个具有38位精度的数值，范围从0.0到1.0，但不包括1.0，如下示例：
SQL> set serverout on
SQL> begin
2 for i in 1..10 loop
3 dbms_output.put_line(round(dbms_random.value*100));
4 end loop;
5 end;
6 /
46
19
45
37
33
57
61
20
82
8
PL/SQL 过程已成功完成。
SQL>
第二种value带有两个参数，第一个指下限，第二个指上限，将会生成下限到上限之间的数字，但不包含上限，“学无止境”兄说的就是第二种，如下：
SQL> begin
2 for i in 1..10 loop
3 dbms_output.put_line(trunc(dbms_random.value(1,101)));
4 end loop;
5 end;
6 /
97
77
13
86
68
16
55
36
54
46
PL/SQL 过程已成功完成。
2. dbms_random.string 方法
某些用户管理程序可能需要为用户创建随机的密码。使用10G下的dbms_random.string 可以实现这样的功能。
例如：
SQL> select dbms_random.string('P',8 ) from dual ;
DBMS_RANDOM.STRING('P',8)
----
3q<M"yf[
第一个参数的含义：
■ 'u', 'U' - returning string in uppercase alpha characters
■ 'l', 'L' - returning string in lowercase alpha characters
■ 'a', 'A' - returning string in mixed case alpha characters
■ 'x', 'X' - returning string in uppercase alpha-numeric
characters
■ 'p', 'P' - returning string in any printable characters.
Otherwise the returning string is in uppercase alpha
characters.
P 表示 printable，即字符串由任意可打印字符构成
而第二个参数表示返回的字符串长度。
3. dbms_random.random 方法
random返回的是BINARY_INTEGER类型值,产生一个任意大小的随机数
与dbms_random.value 的区别举例:
Order By dbms_random.value;
这条语句功能是实现记录的随机排序
另外：
dbms_random.value 和
dbms_random.random 两者之间有什么区别？
1。Order By dbms_random.value ，为结果集的每一行计算一个随机数，dbms_random.value 是结果集的一个列（虽然这个列并不在select list 中），然后根据该列排序，得到的顺序自然就是随机的啦。
2。看看desc信息便知道vlue和random这两个函数的区别了，value返回的是number类型，并且返回的值介于1和0之间，而random返回的是BINARY_INTEGER类型（以二进制形式存储的数字，据说运算的效率高于number但我没测试过，但取值范围肯定小于number，具体限制得查资料了）
如果你要实现随机排序，还是用value函数吧
4. dbms_random.normal方法
NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。
5. dbms_random.send方法
用于生成一个随机数种子,设置种子的目的是可以重复生成随机数，用于调试。否则每次不同，难以调度。18.取得Internet中的主机名和IP地址
Oracle包utl_inaddr
作用:用于取得局域网或Internet环境中的主机名和IP地址.
1、utl_inaddr.get_host_address 环境中IP地址
如果查询失败，则提示系统错误
查询www.qq.com的IP地址
select UTL_INADDR.get_host_address('www.qq.com') from dual;
查询本机IP地址
select UTL_INADDR.get_host_address() from dual;
查询局域网内yuechu的IP地址
select UTL_INADDR.get_host_address('yuechu') from dual;
2、UTL_INADDR.get_host_name返回环境中主机名
返回本机主机名
select UTL_INADDR.get_host_name() from dual;
返回局域网内指定IP地址的主机名
select UTL_INADDR.get_host_name('192.168.0.156') from dual;
返回intrenet中指定IP地址的网址
select UTL_INADDR.get_host_name('219.153.50.84') from dual;简洁版补充
一、数学函数
abs(x) 返回x的绝对值
bin(x) 返回x的二进制（oct返回八进制，hex返回十六进制）
ceiling(x) 返回大于x的最小整数值
exp(x) 返回值e（自然对数的底）的x次方
floor(x) 返回小于x的最大整数值
greatest(x1,x2,...,xn)返回集合中最大的值
least(x1,x2,...,xn) 返回集合中最小的值
ln(x) 返回x的自然对数
log(x,y)返回x的以y为底的对数
mod(x,y) 返回x/y的模（余数）
pi()返回pi的值（圆周率）
rand()返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
round(x,y)返回参数x的四舍五入的有y位小数的值
sign(x) 返回代表数字x的符号的值
sqrt(x) 返回一个数的平方根
truncate(x,y) 返回数字x截短为y位小数的结果
二、聚合函数(常用于group by从句的select查询中)
avg(col)返回指定列的平均值
count(col)返回指定列中非null值的个数
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
ascii(char)返回字符的ascii码值
bit_length(str)返回字符串的比特长度
concat(s1,s2...,sn)将s1,s2...,sn连接成字符串
concat_ws(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
find_in_set(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果
left(str,x)返回字符串str中最左边的x个字符
length(s)返回字符串str中的字符数
ltrim(str) 从字符串str中切掉开头的空格
position(substr,str) 返回子串substr在字符串str中第一次出现的位置
quote(str) 用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)返回字符串str重复x次的结果
reverse(str) 返回颠倒字符串str的结果
right(str,x) 返回字符串str中最右边的x个字符
rtrim(str) 返回字符串str尾部的空格
strcmp(s1,s2)比较字符串s1和s2
trim(str)去除字符串首部和尾部的所有空格
ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果
四、日期和时间函数
curdate()或current_date() 返回当前的日期
curtime()或current_time() 返回当前的时间
date_add(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) 返回date是一个月的第几天(1~31)
dayofyear(date) 返回date是一年的第几天(1~366)
dayname(date) 返回date的星期名，如：select dayname(current_date);
from_unixtime(ts,fmt) 根据指定的fmt格式，格式化unix时间戳ts
hour(time) 返回time的小时值(0~23)
minute(time) 返回time的分钟值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名，如：select monthname(current_date);
now() 返回当前的日期和时间
quarter(date) 返回date在一年中的季度(1~4)，如select quarter(current_date);
week(date) 返回日期date为一年中第几周(0~53)
year(date) 返回日期date的年份(1000~9999)
一些示例：
获取当前系统时间：select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回两个日期值之间的差值(月数)：select period_diff(200302,199802);
在mysql中计算年龄：
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
这样，如果brithday是未来的年月日的话，计算结果为0。
下面的sql语句计算员工的绝对年龄，即当birthday是未来的日期时，将得到负值。
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') <date_format(birthday, '00-%m-%d')) as age from employee
五、加密函数
aes_encrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用aes_encrypt的结果是一个二进制字符串，以blob类型存储
aes_decrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
decode(str,key) 使用key作为密钥解密加密字符串str
encrypt(str,salt) 使用unixcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
encode(str,key) 使用key作为密钥加密字符串str，调用encode()的结果是一个二进制字符串，它以blob类型存储
md5() 计算字符串str的md5校验和
password(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法。
sha() 计算字符串str的安全散列算法(sha)校验和
示例：
select encrypt('root','salt');
select encode('xufeng','key');
select decode(encode('xufeng','key'),'key');#加解密放在一起
select aes_encrypt('root','key');
select aes_decrypt(aes_encrypt('root','key'),'key');
select md5('123456');
select sha('123456');
六、控制流函数
mysql有4个函数是用来进行条件操作的，这些函数可以实现sql的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。
mysql控制流函数：
case when[test1] then [result1]...else [default] end如果testn是真，则返回resultn，否则返回default
case [test] when[val1] then [result]...else [default]end 如果test和valn相等，则返回resultn，否则返回default
if(test,t,f) 如果test是真，返回t；否则返回f
ifnull(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
nullif(arg1,arg2) 如果arg1=arg2返回null；否则返回arg1
这些函数的第一个是ifnull()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是null，函数就会向调用者返回第一个参数；如果是null,将返回第二个参数。
如：select ifnull(1,2), ifnull(null,10),ifnull(4*null,'false');
nullif()函数将会检验提供的两个参数是否相等，如果相等，则返回null，如果不相等，就返回第一个参数。
如：select nullif(1,1),nullif('a','b'),nullif(2+3,4+1);
和许多脚本语言提供的if()函数一样，mysql的if()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，if()将会返回第二个参数，如果为假，if()将会返回第三个参数。
如：selectif(1<10,2,3),if(56>100,'true','false');
if()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，mysql提供了case函数，它和php及perl语言的switch-case条件例程一样。
case函数的格式有些复杂，通常如下所示：
case [expression to be evaluated]
when [val 1] then [result 1]
when [val 2] then [result 2]
when [val 3] then [result 3]
......
when [val n] then [result n]
else [default result]
end
这里，第一个参数是要被判断的值或表达式，接下来的是一系列的when-then块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的when-then块将以else块结束，当end结束了所有外部的case块时，如果前面的每一个块都不匹配就会返回else块指定的默认结果。如果没有指定else块，而且所有的when-then比较都不是真，mysql将会返回null。
case函数还有另外一种句法，有时使用起来非常方便，如下：
case
when [conditional test 1] then [result 1]
when [conditional test 2] then [result 2]
else [default result]
end
这种条件下，返回的结果取决于相应的条件测试是否为真。
示例：
mysql>select case 'green'
when 'red' then 'stop'
when 'green' then 'go' end;
select case 9 when 1 then 'a' when 2 then 'b' else 'n/a' end;
select case when (2+2)=4 then 'ok' when(2+2)<>4 then 'not ok' end asstatus;
select name,if((isactive = 1),'已激活','未激活') as result fromuserlogininfo;
select fname,lname,(math+sci+lit) as total,
case when (math+sci+lit) < 50 then 'd'
when (math+sci+lit) between 50 and 150 then 'c'
when (math+sci+lit) between 151 and 250 then 'b'
else 'a' end
as grade from marks;
select if(encrypt('sue','ts')=upass,'allow','deny') as loginresultfrom users where uname = 'sue';#一个登陆验证
七、格式化函数
date_format(date,fmt) 依照字符串fmt格式化日期date值
format(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
inet_aton(ip) 返回ip地址的数字表示
inet_ntoa(num) 返回数字所代表的ip地址
time_format(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是format()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
select format(34234.34323432,3);
select date_format(now(),'%w,%d %m %y %r');
select date_format(now(),'%y-%m-%d');
select date_format(19990330,'%y-%m-%d');
select date_format(now(),'%h:%i %p');
select inet_aton('10.122.89.47');
select inet_ntoa(175790383);
八、类型转化函数
为了进行数据类型转化，mysql提供了cast()函数，它可以把一个值转化为指定的数据类型。类型有：binary,char,date,time,datetime,signed,unsigned
示例：
select cast(now() as signed integer),curdate()+0;
select 'f'=binary 'f','f'=cast('f' as binary);
九、系统信息函数
database() 返回当前数据库名
benchmark(count,expr) 将表达式expr重复运行count次
connection_id() 返回当前客户的连接id
found_rows() 返回最后一个select查询进行检索的总行数
user()或system_user() 返回当前登陆用户名
version() 返回mysql服务器的版本
示例：
select database(),version(),user();
selectbenchmark(9999999,log(rand()*pi()));#该例中,mysql计算log(rand()*pi())表达式9999999次。函数帮助文件下载
链接: https://pan.baidu.com/s/1wd8cNnDzpA3EpVk8TKb2HA
此处内容需要评论回复后（审核通过）方可阅读。
