oracle学习笔记(第三章:常用函数)

Avg(num):求平均值
Count(*) :求总数
Sum(num):汇总
Max(x):求最大值，x  :数值型，字符型
Min(x):求最小值，x  :数值型，字符型
Sign(num):正负值，正值返回 1 ，负值返回 -1，0 返回 0
例如：select sign(-100) from dual;查询结果为 -1
Decode(表达式，value1,result1,value2,result,..default)
例如：select decode(sex,’0’,’女’,’1’,’男’,’未知’) from empm;
借助sign()和decode()函数可以比较两个数的大小。例如，比较两个数，返回较大的值：select decode(sign(x-y),-1,y,1,x,0,0) from tab1;

随机函数 dbms_random
关于这些函数及DBMS_RANDOM包的文件都包含在SQLPlus中：
select text   from all_source
where name = 'DBMS_RANDOM'
and type = 'PACKAGE' order by line;
   ◆ TYPE      num_array
   ◆ PROCEDURE terminate
   ◆ PROCEDURE seed
   ◆ PROCEDURE initialize
   ◆ FUNCTION random
   ◆ FUNCTION value RETURN NUMBER;
   ◆ FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
   ◆ FUNCTION normal RETURN NUMBER;
   ◆ FUNCTION string (opt char, len NUMBER) RETURN VARCHAR2;
   VALUE函数的第一种形式返回一个大于或等于0且小于1的随机数；第二种形式返回一个大于或等于LOW，
   小于HIGH的随机数。下面是其用法的一个示例：
SQL> select dbms_random.value, dbms_random.value(55,100) from dual;
VALUE            DBMS_RANDOM.VALUE(55,100)
--------------------------------------------
0.434982180314862       73.9457320892544

NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有
68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。
最后，是STRING函数。它返回一个长度达60个字符的随机字符串。参数OPT可以是列表2显示的值中的任何一
个单个字符。
e.g:dbms_random.string('a',8)
第一个参数的含义：
'u', 'U' - 返回全是大写的字符串
'l', 'L' - 返回全是小写的字符串
'a', 'A' - 返回大小写结合的字符串
'x', 'X' - 返回全是大写和数字的字符串
'p', 'P' - 返回键盘上出现字符的随机组合
第二个参数表示返回的字符串长度。
select dbms_random.value()  num from dual   -- num[0,1)
select dbms_random.value(10,100)  num from dual  -- num[p1,p2)
SELECT dbms_random.STRING('a',8)FROM dual --大小写混合,长度为 8
SELECT dbms_random.STRING('u',8)FROM dual --全是大写,长度为 8
SELECT dbms_random.STRING('l',8)FROM dual --全是小写,长度为 8
SELECT dbms_random.STRING('x',8)FROM dual --返回全是大写和数字的字符串,长度为 8
SELECT dbms_random.STRING('p',8)FROM dual --返回键盘上出现字符的随机组合,长度为 8
SELECT dbms_random.NORMAL FROM dual;
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函数吧

sys_guid():自动产生一个 序列
例如：select sys_guid() from dual;
upper(c) ：转换成大写;
lower(c)：转换成小写
initcap(str)：将字符串中的每个单词的首字母转换成大写,其余的转换成小写
concat()：连接字符串，等价于 || 操作符
substr(str,start,len)：截取字符串
Lpad(str,len,str2)：在str左边加字符串str,长度为len
例如：select lpad('xx',6,'y') from dual;结果是：yyyyxx
Rpad(str,len,str)：在str右边加字符串str,长度为len
Trim(str)：将字符串str的左右两边的空格去掉
Trim([ leading | trailing | both ] ‘str1’ from ‘str2’)：将字符串str2的前面=边|后边|左右两边的字符str1去掉,默认是both左右两边,如果没有str2则默认是去掉空格
例如：select trim('x' from 'xaaax') from dual;结果为:aaa，select trim(leading 'x' from 'xaaax') from dual;结果为：aaax，select trim(leading  from  ‘      aaax') from dual;结果为:aaax前面的空格去掉了
Ltrim(),Rtrim():表示去掉左边的和右边的字符。
Length(),lengthb():求字符串的长度,其中lengthb()是一字节为单位
例如：Select length(‘汉字’) from dual;结果为2,select lengthb(‘汉字’) from dual;结果为：4
Translate(str1,str2,str3)：将字符串str1中的字符串替换为str3,等长度的替换
例如：select translate('abcdeabc','ab','XY') from dual;将abdceabc中的ab替换为XY，注意是等长度的替换，换种写法：select translate('abcdeabc','ab','XYZ’) from dual;意义相同，结果也一样，都是XYcdeXYc不等长度的替换用replace()函数
Replace()：将字符串str1中的字符串替换为str3,不等长度的替换
例如：select translate('abcdeabc','ab','XYZ’) from dual;结果为XYZcdeXYZ
Instr(str1,str2,[start] [nth])：在字符串str1中查找str2从start开始找，第nth次出现的位置，如果start，nth省略，表示从第一个位置开始找，第一次出现的位置
例如：select instr('abcdefabcdabcd','a',1,2) from dual;结果为：7
Round(n1,n2)：四舍五入法,n2表示保留几位小数，如果省略了n2表示四舍五入保留整数
Trunc(n1,n2)：表示无条件的舍弃，n2表示保留几位小数，如果省略了n2表示保留整数
Round(),Trunc() 函数还可以用在日期类型的数据上，
round()函数将该天的时间设置为0点﹐如果该时间是下午之后﹐设置为次日的0点
trunc()函数与round()函数类似﹐只是它将任何时间（包括午夜过后1秒）都设置为0点，这样两日期相减得到整天数
floor(n) 返回小于或等于 n 的 最大整数
ceil(n) 返回大于或等于 n 的 最小整数
mod(1600,300) 求余数
add_months(date,n):增加n 个月
months_between(date1,date2):两个日期相差几个月
next_day(date,n):n表示 1—7，表示周日至周六，函数返回下一个周几的日期
例如：select next_day(sysdate,1) from dual;下一个周一是多少号
Last_day(date)：本月最后一天的日期
Chr（c）:转换成ASCII值
Ascii(c)：将ASCII值转化成CHAR字符
greatest('str1','str2','str3'....)：比较一组值的大小,返回最大值,不同于max
Nvl(x,y)：对空值替换,如果x是null,则返回y,否则返回x，x,y数据类型要一致
Nvl2(x,y,z) ：对空值替换,如果x是null,则返回y，否则返回z，x,y数据类型要一致，x,z数据类型可以不一致
STDDEV()：标准差
VARIANCE() ：方差
Nullif(表达式1﹐表达式2) Oracle 9i新增函数，意义比较两个表达式如果相等则返回空值（null）﹐不相等返回 表达式1
Coalesce(表达式1﹐表达式2﹐表达式3...表达式n):Oracle 9i新增函数，功能﹕返回第一个不为空的表达式值
例如：select  coalesce(addr,tel1,tel2) from empm;
case 表达式 ﹕Case 表达式 when value1 then valuea when value2 then valueb when value3 then valuec… else valuez;功能与coalesce()函数完全相同
例如：select distinct case emp_nm
when 'lyj' then 'lyjdb'
when 'Lucy' then 'ABC'
when 'Jake' then 'Jake.Lea'
else emp_nm
END AS "姓名"
from empm;
to_char(),to_number(),to_date()：数据类型转换函数
to_char():将日期转换成字符，显示格式如下：
年：YYYY , YYY , YY , Y , YEAR , YYYYBC , YYYYB.C.，例如：select to_char(sysdate,’YEAR’) FROM DUAL;其中YYYYBC , YYYYB.C.表示公元，SELECT to_char(SYSDATE,'YYYYB.C.') FROM dual
月：MM，MONTH，fmMM 例如：SELECT to_char(SYSDATE,'fmMM') FROM dual;
日：DDD，DD，D，DAY，DY (DDD、DD可加入fm删除多余空间)例如：SELECT to_char(SYSDATE,'fmDDDDDDDD') FROM dual;
时：hh ，hh12 ，hh24
分：mi
秒：ss
AM,PM 上午，下午
例如：SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd')||to_char(SYSDATE,'AM')||to_char(SYSDATE,'HH12:MI:SS') FROM dual
关于时间的处理：
SELECT SYSDATE  FROM DUAL  取当前系统时间
Select trunc(sysdate) from dual 取当前日期
Select trunc(sysdate,’MM’) from dual 取当前月的第一天
Select trunc(sysdate,’YYYY’) from dual取当年的元旦
Select to_char(sysdate,’ss’) from dual取当前时间秒部分
Select to_char(sysdate,’mi’) from dual取当前时间分钟部分
Select to_char(sysdate,’HH24’) from dual取当前时间秒小时部分
Select to_char(sysdate,’DD’) from dual取当前时间日期部分
Select to_char(sysdate,’MM’) from dual取当前时间月部分
Select to_char(sysdate,’YYYY’) from dual取当前时间年部分
Select to_char(sysdate,’w’) from dual取当前时间是一个月中的第几周(从1日开始算)
Select to_char(sysdate,’ww’) from dual取当前时间是一年中的第几周（从1.1开始算）
Select to_char(sysdate,’iw’) from dual取当前时间是一年中的第几周（按实际日历的）
Select to_char(sysdate,’d’) from dual取当前时间是一周的第几天，从星期天开始，周六结束
Select to_char(sysdate,'day') from dual　取当前日是星期几，和数据库设置的字符集有关，会输出’Tuesday’
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天
Select Add_months(sysdate,12) from dual 取一年后的今天
Select sysdate-(sysdate-100) from dual 取两个日期之间的天数
Select (sysdate-(sysdate-100))*1440 from dual 取两个日期之间的分钟数
Select (sysdate-(sysdate-100))*1440*60 from dual 取两个日期之间的秒数
Select months_between(sysdate,sysdate-100) from dual 取两个日期间隔的月份
Select last_day(sysdate) from dual 取当前月的最后天
Select next_day(sysdate,’1’) from dual 取当前日之后第一个星期天，里面的’1’表示取星期日，如果今天正好是星期日，则会显示下一个星期日

Regexp_substr()：正则 表达式oracle 10g 函数
例如：SELECT regexp_substr('123-456-7890','-[^-]+-') "regexp_substr" FROM dual;
rollup(),rand(),cube() , 分析函数 ，这三个函数在 9i 以后的版本中可以使用:
cube(col1,col2…):例子如下，同过以下几个例子，可以看出cube()函数的用途
SELECT sex,dept_no,round(AVG(age),2)  FROM empm
GROUP BY sex,dept_no ORDER BY sex,dept_no ;-- 通过 sex , dept_no 统计平均年龄

SELECT sex,dept_no,round(AVG(age),2)  FROM empm
GROUP BY cube(sex),dept_no ORDER BY sex,dept_no ;-- 先通过 sex 统计平均年龄，再通过 dept_no 统计平均年龄

SELECT sex,dept_no,round(AVG(age),2)  FROM empm
GROUP BY cube(sex,dept_no) ORDER BY sex,dept_no ; -- 分别通过 sex,dept_no统计平均年龄

SELECT sex,round(AVG(age),2) FROM empm
GROUP BY sex ORDER BY sex;-- 通过 sex 统计平均年龄

SELECT dept_no,round(AVG(age),2) FROM empm
GROUP BY dept_no ORDER BY dept_no;-- 通过 dept_no 统计平均年龄

从上面的结果中我们很容易发现,每个统计资料所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0，例如：
SELECT decode(grouping(sex),1,'sex') sex,decode(grouping(dept_no),1,'dept') dept,round(AVG(age),2)  FROM empm
GROUP BY cube(sex,dept_no) ORDER BY sex,dept_no ;

SELECT sex,dept_no,round(AVG(age),2)  FROM empm
GROUP BY cube(sex,dept_no) ORDER BY sex,dept_no ;
Cube()函数与rollup() 函数的区别在于: rollup()函数只根据第一个参数汇总,而 cube() 可以根据所有参数汇总,都出现在group by 语句之后;两者用法相同

rank() , dense_rank() , row_number(),percent_rank()
三者的区别在于:rank()如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,
而dense_rank()则不会,常用该函数进行名次统计,差别更大的是,row_number()哪怕是两个数据完全相同,排名也会不一样,
这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处. percent_rank() 排序后,出现的位置所占百分之几
例子：
Rank():
SELECT emp_no,age, rank() over(ORDER BY age DESC) FROM empm;
SELECT sex,dept_no,round(avg(age)), rank() over(ORDER BY round(avg(age)) DESC) FROM empm
GROUP BY sex,dept_no;
Dense_rank():
SELECT emp_no,age, dense_rank() over(ORDER BY age DESC) FROM empm ;
SELECT sex,dept_no,round(avg(age)), dense_rank() over(ORDER BY round(avg(age)) DESC) FROM empm
GROUP BY sex,dept_no;
Row（）:
SELECT emp_no,age, row_number() over(ORDER BY age DESC) FROM empm ;
SELECT sex,dept_no,round(avg(age)), row_number() over(ORDER BY round(avg(age)) DESC) FROM empm
GROUP BY sex,dept_no;
Percent_rank():
SELECT emp_no,age, percent_rank() over(ORDER BY age DESC) FROM empm ;
SELECT sex,dept_no,round(avg(age)), percent_rank() over(ORDER BY round(avg(age)) DESC) FROM empm
GROUP BY sex,dept_no;

Lag()和Lead()函数介绍:
Lag(col,n,value):表示col列的前n笔资料，如果没有，则用value显示
SELECT emp_no,dept_no,age,lag(age,2,0) over(PARTITION BY dept_no ORDER BY emp_no) lag_age FROM empm;--前2笔资料
Lead(col,n,value): 表示col列的后n笔资料，如果没有，则用value显示
SELECT emp_no,dept_no,age,lead(age,2,0) over(PARTITION BY dept_no ORDER BY emp_no) lag_age FROM empm;--后2笔资料

Ratio_to_report():函数，每行总数的百分比，格式为：Ratio_to_report(expr) OVER (query_partition_clause)
SELECT sex,dept_no,ratio_to_report(AVG(age)) over(PARTITION BY sex) FROM empm GROUP BY dept_no,sex ORDER BY sex,dept_no;


常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

按性别统计最大年龄和最小年龄
select distinct sex,max(age) over(partition by sex) max_age,min(age) over(partition by sex) min_age from empm;

NLSSORT()，用来进行语言排序
拼音 :
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 :
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_STROKE_M')
部首 :
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_RADICAL_M')

获得IP：
select sys_context('userenv','ip_address') from dual;
select utl_inaddr.get_host_address from dual;