oracle--09常用函数

最新推荐文章于 2022-07-21 10:21:20 发布
最新推荐文章于 2022-07-21 10:21:20 发布
阅读量557 收藏
点赞数
分类专栏： oracle 文章标签： 数据库 操作系统 数据结构与算法
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhyp29/article/details/84804163
版权
Oracle内置函数日期函数字符串函数统计函数 
 

to_char();--转换为字符串
to_number();--转换为数值类型
to_date();--转换为日期类型
 
1、常用数学运算函数
abs(value) : 返回value的绝对值
ceil(value) ：返回大于或等于value的最小整数
cos(value) : 返回value的余弦值
floor(value) : 返回等于或小于value的最大整数
mod(value1,value2) : 返回value1除以value2的余数
power(value,e) : 返回value的e次幂
round(value,precision) : 保留precision位小数对value进行四舍五入
sign(value) : 如果value是正则返回1，是负则返回-1，否则返回0.
sqrt(value) : 返回value的平方根。
trunc(value,precision) : 是一个截取函数，如果precision为正，则往小数点右边截取precision位，为负则往左边截取precision位。
 
2、常用字符串函数
length(value) : 返回value的长度，value可以是字符串、数字或表达式
lower(value) : 把给定字符串value变为小写
upper(value) : 把给定字符串value变为大写
lpad(string,length[,padding]) : 在string左边填充padding，直到string的长度达到length，如果不指定padding，则默认填充空格
rpad(string,length[,padding]) : 在string右边填充padding，知道string的长度达到length，如果不指定padding，则默认填充空格
ltrim(string[,trimValue]) : 从左边删除trimValue，如果不指定trimValue则删除空格
rtrim(string[,trimValue]) : 从右边删除trimValue，如果不指定trimValue则删除空格
string1 || string2 : 连接两个字符串为一个字符串
initcap(string) : 将字符串的首字母大写
replace(String,src,dest) : 将string中的src用dest代替
substr(string,start[,count]) : 从start位置开始在string中截取count个字符，如果没有指定count，则截取到末尾
 
3、统计函数
avg([distinct] columnName) : 返回columnName中所有值的平均值，若使用distinct则返回所有不同的非空值的平均值
count([distinct] value) : 统计记录数，若使用distinct则只统计不同的非空数值。value可以是字段名也可以是表达式
max(columnName) : 返回columnName中的最大值
min(columnName) : 返回columnName中的最小值
sum(columnName) : 返回columnName中各值的总和
 
4、常用日期函数
add_months(date,number) : 返回date之后number个月的日期
last_day(date) : 返回date所在月份的最后一天
months_between(date1,date2) ：返回date2减去date1之后的月数
next_day(date,'day') : 返回date后的day所在的日期，其中day表示全称的星期，如Monday
round(date,'format') : 把date四舍五入到指定的format格式
to_char(date,'format') : 将日期date转换为format格式的字符串
to_date(string,'format') : 将字符串string按照format转换成日期形式
to_timestamp(string,'format')：将字符串string按照format转换成时间戳
trunc(date):将日期date的时分秒都设为0
 
另附几个数字跟日期互相转换的自定义函数：
Sql代码  
--create function 数字转日期  
create or replace function FN_NUMBER_TO_DATE(timeInMillis in number) return date is  
  Result date;  
begin  
  Result := to_date('1970-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') +  
                timeInMillis / (24 * 60 * 60 * 1000);  
  return(Result);  
end FN_NUMBER_TO_DATE;  
  
  
--create function 取到几天以前的日期  
create or replace function FN_DAYS_AGO(dest in date, days in number) return date is  
  Result date;  
  timeInMillis number;  
begin  
  timeInMillis := FN_DATE_TO_NUMBER(dest) - days*24*60*60*1000;  
  Result := FN_NUMBER_TO_DATE(timeInMillis);  
  return(Result);  
end FN_DAYS_AGO;  
  
  
--create function 日期转数字  
create or replace function FN_DATE_TO_NUMBER(inDate in date) return number is  
  Result number;  
begin  
  Result := (inDate-to_date('1970-01-01', 'yyyy-MM-dd')) * 24*60*60*1000;  
  return(Result);  
end FN_DATE_TO_NUMBER;  


SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
        A         A      ZERO     SPACE
--------- --------- --------- ---------
       65        97        48        32
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23'  高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
 INSTRING
---------
        9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME   LENGTH(NAME) ADDR             LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞            3 北京市海锭区                6   9999.99                    7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD  在列的右边粘贴字符
LPAD  在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM  删除左边出现的字符串
RTRIM  删除右边出现的字符串
SQL> select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string   希望被替换的字符或变量
s1       被替换的字符串
s2       要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING   剪掉前面的字符
TRAILING  剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
 ABS(100) ABS(-100)
--------- ---------
      100       100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
 ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
  ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
              4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
             -1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
 COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
   EXP(2)    EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
          2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
    LN(1)     LN(2) LN(2.7182818)
--------- --------- -------------
        0 .69314718     .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
 LOG(2,1)  LOG(2,4)
--------- ---------
        0         2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3)  MOD(3,3)  MOD(2,3)
--------- --------- ---------
        1         0         2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
       1024         27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
         56          -55          55          -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100)   SIGN(0)
--------- ---------- ---------
        1         -1         0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
           1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
  SIN(20)  SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
 SQRT(64)  SQRT(10)
--------- ---------
        8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
  TAN(20)   TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
 TANH(20)   TAN(20)
--------- ---------
        1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
   TRUNC1 TRUNC(124.16666,2)
--------- ------------------
      100             124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
          9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
 MON_BETW
---------
      -60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
  2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME             LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH                  HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID              ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL>  select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
     YEAR
---------
     1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
  2  0,'none',
  3  2,'insert',
  4  3,
  5  'select',
  6  6,'update',
  7  7,'delete',
  8  8,'drop',
  9  'other') cmd  from v$session where type!='background';
      SID   SERIAL# USERNAME                       CMD
--------- --------- ------------------------------ ------
        1         1                                none
        2         1                                none
        3         1                                none
        4         1                                none
        5         1                                none
        6         1                                none
        7      1275                                none
        8      1275                                none
        9        20 GAO                            select
       10        40 GAO                            none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME                    DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME                         USER_ID
------------------------------ ---------
GAO                                   25
58.USER
返回当前用户的名字
SQL> select user from  dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA  查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
                 152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
                 0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
                  1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
          6 SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
         3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
    2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
            5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
    1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
  1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
           1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
    1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       10         3      8750
       20         5     10875
       30         6      9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
   DEPTNO ENAME            SAL
--------- ---------- ---------
       10 KING            5000
       10 CLARK           2450
       10 MILLER          1300
       20 SCOTT           3000
       20 FORD            3000
       20 JONES           2975
       20 ADAMS           1100
       20 SMITH            800
       30 BLAKE           2850
       30 ALLEN           1600
       30 TURNER          1500
       30 WARD            1250

注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。

　　单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。

1、MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。
例如：SELECT MOD(24,5) FROM DUAL;

2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。
例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;

3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;

(二).字符型函数返回字符值(Character Functions Returning Character Values)
　　该类函数返回与输入类型相同的类型。
 返回的CHAR类型值长度不超过2000字节；
 返回的VCHAR2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。

 返回的CLOB类型值长度不超过4G；
对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。

1、LOWER(c) 将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如：SELECT LOWER('WhaT is tHis') FROM DUAL;

2、UPPER(c) 将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如：SELECT UPPER('WhaT is tHis') FROM DUAL;

3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：
 如果n<c1.length则从右到左截取指定长度返回；
 如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；
 如果n>c1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回；
例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
最后大家再猜一猜，如果n<0，结果会怎么样

4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；
例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;

5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。
看起来很复杂，理解起来很简单：
 如果没有指定任何参数则oracle去除c1头尾空格
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
 如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了leading参数则会去掉c1头部c2
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了trailing参数则会去掉c1尾部c2
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)
例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;

注意：c2长度=1

6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;

7、RTRIM(c1,c2)与上同，不过方向相反
例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;

8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。
例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;

9、SOUNDEX(c) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：
 保留字符串首字母，但删除a、e、h、i、o、w、y。
 将下表中的数字赋给相对应的字母：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：R
 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；
 只返回前4个字节，不够用0填充
例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;

10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度；
n2=截取的字符串长度，如果为空，默认截取到字符串结尾；
 如果n1=0 then n1=1
 如果n1>0，则oracle从左向右确认起始位置截取
例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;
 如果n1<0，则oracle从右向左数确认起始位置
例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;
 如果n1>c1.length则返回空
例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;
然后再请你猜猜，如果n2<1，会如何返回值呢

11、TRANSLATE(c1,c2,c3) 就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解，我们通过几具实例来说明：
例如：
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
结果都是空。来试试这个：
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再来看这个：
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了点呢？Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了？Replace是替换，而translate则像是过滤

(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型

1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
 c1:原字符串
 c2:要寻找的字符串
 n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0
 n2:第几个匹配项。大于0
例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;

2、LENGTH(c) 返回指定字符串的长度。如果
例如：SELECT LENGTH('A123中') FROM DUAL;
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么

(四).日期函数(Datetime Functions)
本类函数中，除months_between返回数值外，其它都将返回日期。

1、ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。
例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;

2、CURRENT_DATE 返回当前session所在时区的默认时间
例如：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;

3、SYSDATE 功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好，深入浅出)的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。
例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL;

4、LAST_DAY(d) 返回指定时间所在月的最后一天
例如：SELECT last_day(SYSDATE) FROM DUAL;

5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。
例如：三思用的中文nt，nls_language值为SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
两种方式都可以取到正确的返回，但是：
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
则会执行出错，提供你说周中的日无效，就是这个原因了。

6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0
例如：
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;

7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。
例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;

8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。
例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;

(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、TO_CHAR() 本函数又可以分三小类，分别是
 转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；
例如：SELECT TO_CHAR('AABBCC') FROM DUAL;

 转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；
例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

 转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；
例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。
例如：
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

为什么公元制的话，c的值必须不大于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句：
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;

3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;

(六).其它辅助函数(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。
毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：select decode('a2','a1','true1','a2','true2','default') from dual;

2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;

3、LEAST(n1,n2....n) 返回序列中的最小值
例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

4、NULLIF(c1,c2)
Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
例如：SELECT NVL(null, '12') FROM DUAL;

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3
例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;

7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如：
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);

insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');

select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;

8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数：
 CURRENT_SCHEMA:当前模式名；
 CURRENT_USER:当前用户；
 IP_ADDRESS:当前客户端IP地址；
 OS_USER:当前客户端操作系统用户；
等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents

例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。

单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。

1、ABS(n) 返回数字的绝对值
例如：SELECT ABS(-1000000.01) FROM DUAL;

2、COS(n) 返回n的余弦值
例如：SELECT COS(-2) FROM DUAL;

3、ACOS(n) 反余弦函数，n between -1 and 1，返回值between 0 and pi。
例如：SELECT ACOS(0.9) FROM DUAL;

4、BITAND(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下：
假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。
例如：SELECT BITAND(3,9) FROM DUAL;

5、CEIL(n) 返回大于或等于n的最小的整数值
例如：SELECT ceil(18.2) FROM DUAL;
考你一下，猜猜ceil(-18.2)的值会是什么呢

6、FLOOR(n) 返回小于等于n的最大整数值
例如：SELECT FLOOR(2.2) FROM DUAL;
再猜猜floor(-2.2)的值会是什么呢

7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制
例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;

8、SIN(n) 返回n的正玄值,n为弧度。
例如：SELECT SIN(10) FROM DUAL;

9、SINH(n) 返回n的双曲正玄值,n为弧度。
例如：SELECT SINH(10) FROM DUAL;

10、ASIN(n) 反正玄函数，n between -1 and 1，返回值between pi/2 and -pi/2。
例如：SELECT ASIN(0.8) FROM DUAL;

11、TAN(n) 返回n的正切值,n为弧度
例如：SELECT TAN(0.8) FROM DUAL;

12、TANH(n) 返回n的双曲正切值,n为弧度
例如：SELECT TANH(0.8) FROM DUAL;

13、ATAN(n) 反正切函数，n表示弧度，返回值between pi/2 and -pi/2。
例如：SELECT ATAN(-444444.9999999) FROM DUAL;

14、EXP(n) 返回e的n次幂，e = 2.71828183 ...
例如：SELECT EXP(3) FROM DUAL;

15、LN(n) 返回n的自然对数，n>0
例如：SELECT LN(0.9) FROM DUAL;

16、LOG(n1,n2) 返回以n1为底n2的对数，n1 >0 and not 1 ，n2>0
例如：SELECT LOG(1.1,2.2) FROM DUAL;

17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n必须为整数
例如：SELECT POWER(2.2,2.2) FROM DUAL;

18、SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.
例如：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;

19、SQRT(n) 返回n的平方根,n为弧度。n>=0
例如：SELECT SQRT(0.1) FROM DUAL;

(二).字符型函数返回字符值(Character Functions Returning Character Values)
　　该类函数返回与输入类型相同的类型。
 返回的CHAR类型值长度不超过2000字节；
 返回的VCHAR2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。

 返回的CLOB类型值长度不超过4G；
对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。

1、CHR(N[ USING NCHAR_CS]) 返回指定数值在当前字符集中对应的字符
例如：SELECT CHR(95) FROM DUAL;

2、CONCAT(c1,c2) 连接字符串，等同于||
例如：SELECT concat('aa','bb') FROM DUAL;

3、INITCAP(c) 将字符串中单词的第一个字母转换为大写，其它则转换为小写
例如：SELECT INITCAP('whaT is this') FROM DUAL;

4、NLS_INITCAP(c) 返回指定字符串，并将字符串中第一个字母变大写，其它字母变小写
例如：SELECT NLS_INITCAP('中华miNZHu') FROM DUAL;
它还具有一个参数：Nlsparam用来指定排序规则，可以忽略，默认状态该参数为当前session的排序规则。

(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、ASCII(c) 与chr函数的用途刚刚相反，本函数返回指定字符在当前字符集下对应的数值。
例如：SELECT ASCII('_') FROM DUAL;

(四).日期函数(Datetime Functions)
本类函数中，除months_between返回数值外，其它都将返回日期。
1、CURRENT_TIMESTAMP([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;

2、LOCALTIMESTAMP([n]) 与上同，返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;

3、SYSTIMESTAMP([n]) 与上同，返回当前数据库所在时区的日期和时间，n表示毫秒级的精度，>0 and <6
例如：SELECT SYSTIMESTAMP(4) FROM DUAL;

4、DBTIMEZONE 返回数据库的当前时区
例如：SELECT DBTIMEZONE FROM DUAL;

5、SESSIONTIMEZONE 返回当前session所在时区
例如：SELECT SESSIONTIMEZONE FROM DUAL;

6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列
例如：SELECT EXTRACT(year from sysdate) FROM DUAL;

7、TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。
例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。
例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

2、CAST(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效，比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型，此处就不作酹述。
例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;

3、CHARTOROWID(c) 将字符串转换为rowid类型
例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;

4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。
例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;

5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串
例如：SELECT TO_MULTI_BYTE('ABC abc　中华') FROM DUAL;

6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串
例如：SELECT TO_SINGLE_BYTE('ＡＢＣ　ａｂｃ中华') FROM DUAL;

(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值
例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;

2、DUMP(exp[,fmt[,start[,length]]])
dump是个功能非常强悍的函数，对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法，不对其功能做深入分析。

如上所示，dump拥有不少参数。其本质是以指定格式，返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式：8||10||16||17，分别表示8进制，10进制，16进制和单字符，默认为10进制。start参数表示开始位置，length表示以,分隔的字串数。
例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;

3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型，通常被用于insert和update等语句以初始化lob列，或者将其置为空。EMPTY表示LOB已经被初始化，只不过还没有用来存储数据。

4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。
例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;

5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。
例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;

6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度，n2是该值的字符集ID
例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;

7、SYS_EXTRACT_UTC(timestamp) 返回标准通用时间即格林威治时间。
例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;

8、SYS_TYPEID(object_type) 返回对象类型对应的id。
例如：这个这个，没有建立过自定义对象，咋做示例？

9、UID 返回一个唯一标识当前数据库用户的整数。
例如：SELECT UID FROM DUAL;

10、USER 返回当前session用户
例如：SELECT USER FROM DUAL;

11、USERENV(c) 该函数用来返回当前session的信息，据oracle文档的说明，userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用USERENV命名空间来获取相关信息，所以大家了解下就行了。
例如：SELECT USERENV('LANGUAGE') FROM DUAL;

12、VSIZE(c) 返回c的字节数。
例如：SELECT VSIZE('abc中华') FROM DUAL;

一、字符函数
字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

问题：将所有员工的名字按小写的方式显示
SQL> select lower(ename) from emp;
问题：将所有员工的名字按大写的方式显示。
SQL> select upper(ename) from emp;
问题：显示正好为5个字符的员工的姓名。
SQL> select * from emp where length(ename)=5;
问题：显示所有员工姓名的前三个字符。
SQL> select substr(ename, 1, 3) from emp;
问题：以首字母大写,后面小写的方式显示所有员工的姓名。
SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
问题：以首字母小写,后面大写的方式显示所有员工的姓名。
SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;
问题：显示所有员工的姓名，用“我是老虎”替换所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
问题：instr(char1,char2,[,n[,m]])用法
SQL> select instr('azhangsanbcd', 'zhangsan') from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 1) instring from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 2) instring from dual; --返回9
SQL> select instr('oracle traning', 'ra', 1, 3) instring from dual; --返回0，根据条件，由于ra只出现二次，第四个参数3，就是说第3次出现ra的位置，显然第3次是没有再出现了，所以结果返回0。注意空格也算一个字符
SQL> select instr('abc','d') from dual;  --返回0

               

二、数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round等
我们讲最常用的：
round(n,[m]) 该函数用于执行四舍五入，
如果省掉m，则四舍五入到整数。
如果m是正数，则四舍五入到小数点的m位后。
如果m是负数，则四舍五入到小数点的m位前。
eg、SELECT round(23.75123) FROM dual; --返回24
SELECT round(23.75123, -1) FROM dual; --返回20
SELECT round(27.75123, -1) FROM dual; --返回30
SELECT round(23.75123, -3) FROM dual; --返回0
SELECT round(23.75123, 1) FROM dual; --返回23.8
SELECT round(23.75123, 2) FROM dual; --返回23.75
SELECT round(23.75123, 3) FROM dual; --返回23.751
trunc(n,[m]) 该函数用于截取数字。
如果省掉m，就截去小数部分，
如果m是正数就截取到小数点的m位后，
如果m是负数，则截取到小数点的前m位。
eg、SELECT trunc(23.75123) FROM dual; --返回23
SELECT trunc(23.75123, -1) FROM dual; --返回20
SELECT trunc(27.75123, -1) FROM dual; --返回20
SELECT trunc(23.75123, -3) FROM dual; --返回0
SELECT trunc(23.75123, 1) FROM dual; --返回23.7
SELECT trunc(23.75123, 2) FROM dual; --返回23.75
SELECT trunc(23.75123, 3) FROM dual; --返回23.751
mod(m,n)取余函数
eg、select mod(10,2) from dual; --返回0
SELECT MOD(10,3) FROM dual; --返回1
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
eg、SELECT ceil(24.56) from dual; --返回25
SELECT floor(24.56) from dual; --返回24
abs(n) 返回数字n的绝对值
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果

                

三、日期函数
日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12”
(1)sysdate 返回系统时间
eg、SQL> select sysdate from dual;
(2)oracle add_months函数
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
eg、select add_months(sysdate,-6) from dual; --该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; --该查询的结果是当前时间半年后的时间
(3)last_day(d)：返回指定日期所在月份的最后一天
问题：查找已经入职8个月多的员工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
问题：显示满10年服务年限的员工的姓名和受雇日期。
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
问题：对于每个员工，显示其加入公司的天数。
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;
或者
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;
问题：找出各月倒数第3天受雇的所有员工。
SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
          
四、转换函数
转换函数用于将数据类型从一种转为另外一种。在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型
比如：
create table t1(id int);
insert into t1 values('10');--这样oracle会自动的将'10' -->10
create table t2 (id varchar2(10));
insert into t2 values(1); --这样oracle就会自动的将1 -->'1'；
我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。

to_char()函数
你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。
问题：日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;
问题：薪水是否可以显示指定的货币符号
SQL>
yy：两位数字的年份 2004-->04
yyyy：四位数字的年份 2004年
mm：两位数字的月份 8 月-->08
dd：两位数字的天 30 号-->30
hh24： 8点-->20
hh12：8点-->08
mi、ss-->显示分钟\秒
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
L：在数字前面加本地货币符号
C：在数字前面加国际货币符号
G：在指定位置显示组分隔符、
D：在指定位置显示小数点符号(.)

问题：显示薪水的时候，把本地货币单位加在前面
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;
问题：显示1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
问题：显示所有12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12;

          
to_date()函数
函数to_date用于将字符串转换成date类型的数据。
问题：能否按照中国人习惯的方式年—月—日添加日期。
eg、SELECT to_date('2012-02-18 09:25:30','yyyy-mm-dd hh24:mi:ss') FROM dual;

             

五、sys_context()系统函数
1)terminal：当前会话客户所对应的终端的标示符，如计算机名
2)language: 语言
3)db_name： 当前数据库名称
4)nls_date_format： 当前会话客户所对应的日期格式
5)session_user： 当前会话客户所对应的数据库用户名
6)current_schema： 当前会话客户所对应的默认方案名
7)host： 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？
select sys_context('USERENV','db_name') from dual;
注意：USERENV是固定的，不能改的，db_name可以换成其它,
eg、select sys_context('USERENV','language') from dual;
select sys_context('USERENV','current_schema') from dual;

1. ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3. CONCAT
连接两个字符串;
SQL> select concat(010-,88888888)||转23 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4. INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(smith) upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr(oracle traning,ra,1,2) instring from dual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower(AaBbCcDd)AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper(AaBbCcDd) upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad(gao,10,*),17,*)from dual;
LPAD(RPAD(GAO,1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim( gao qian jing , ), ) from dual;
LTRIM(RTRIM(
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(13088888888,3,8) from dual;
SUBSTR(
--------
08888888
12.REPLACE(string,s1,s2)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace(he love you,he,i) from dual;
REPLACE(H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values(weather);
SQL> insert into table1 values(wether);
SQL> insert into table1 values(gao);
SQL> select xm from table1 where soundex(xm)=soundex(weather);
XM
--------
weather
wether
• 14.TRIM(s from string)
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
• 31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,yyyy.mm.dd),to_char((sysdate)+1,yyyy.mm.dd) from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between(19-12月-1999,19-3月-1999) mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd)) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,this,that)
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,yyyy.mm.dd hh24:mi:ss) bj_time,to_char(new_time
2 (sysdate,PDT,GMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,day)
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,dd-mm-yyyy day) from dual;
TO_CHAR(SYSDATE,
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒
SQL> select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh,
2 to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert(strutz,we8hp,f7dec) "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,format)
SQL> select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;
TO_CHAR(SYSDATE,YY
-------------------
2004/05/09 21:14:41
• 48.TO_DATE(string,format)
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte(高) from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number(1999) year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename(lob_dir1,image1.gif));
52.CONVERT(x,desc,source)
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,none,
3 2,insert,
4 3,
5 select,
6 6,update,
7 7,delete,
8 8,drop,
9 other) cmd from v$session where type!=background;
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest(AA,AB,AC) from dual;
GR
--
AC
SQL> select greatest(啊,安,天) from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least(啊,安,天) from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv(isdba) from dual;
USEREN
------
FALSE
SQL> select userenv(isdba) from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv(sessionid) from dual;
USERENV(SESSIONID)
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv(entryid) from dual;
USERENV(ENTRYID)
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv(instance) from dual;
USERENV(INSTANCE)
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv(language) from dual;
USERENV(LANGUAGE)
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv(lang) from dual;
USERENV(LANG)
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv(terminal) from dual;
USERENV(TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
• 60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(zhu,5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
68. pl/sql中的case语句
select  (case  when  DUMMY='X'  then  0  else  1  end)  as  flag  from  dual;
case的第1种用法：
case col when 'a' then 1
when 'b' then 2
else 0 end
这种用法跟decode一样没什么区别
case的第2种用法：
case when score <60 then 'd'
when score >=60 and score <70 then 'c'
when score >=70 and score <80 then 'b'
else 'a' end
69.NVL(expr1, expr2)
NVL(expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
• Oracle分析函数参考手册
=============================================
作者: xsb([url]http://xsb.itpub.net)[/url]
发表于:2006.03.01 12:22
分类: DW&BI
出处：http://xsb.itpub.net/post/419/33028
---------------------------------------------------------------
    Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，
而聚合函数对于每个组只返回一行。
常用的分析函数如下所列:
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 ...)
    下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行
$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。
除本文内容外，你还可参考：
ROLLUP与CUBE [url]http://xsb.itpub.net/post/419/29159[/url]
分析函数使用例子介绍：[url]http://xsb.itpub.net/post/419/44634[/url]
本文如果未指明，缺省是在HR用户下运行例子。
开窗函数的的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
主要参考资料：《expert one-on-one》 Tom Kyte 《Oracle9i SQL Reference》第6章
ohwww 2007-3-12 09:19
70。AVG
功能描述：用于计算一个组和数据窗口内表达式的平均值。
SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000 15000
100 Raphaely 07-DEC-94 11000 11966.6667
100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96 13000 9633.33333
100 Weiss 18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333
71。CORR
功能描述：返回一对表达式的相关系数，它是如下的缩写：
COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))
从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度
上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数, 相关
系数给出了关联的强度，0表示不相关。
SAMPLE：下例返回1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）
SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) as CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number
ORDER BY t.calendar_month_number;
CALENDAR_MONTH_NUMBER CUM_CORR
--------------------- ----------
1
2 1
3 .994309382
4 .852040875
5 .846652204
6 .871250628
7 .910029803
8 .917556399
9 .920154356
10 .86720251
11 .844864765
12 .903542662
72。COVAR_POP
功能描述：返回一对表达式的总体协方差。
SAMPLE：下例CUM_COVP返回定价和最小产品价格的累积总体协方差
SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619
.
.
73。COVAR_SAMP
功能描述：返回一对表达式的样本协方差
SAMPLE：下例CUM_COVS返回定价和最小产品价格的累积样本协方差
SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619
• 74。COUNT
功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count
返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全
相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行
没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
SELECT last_name, salary, COUNT(*) OVER () AS cnt1,
COUNT(*) OVER (ORDER BY salary) AS cnt2,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
AND 150 FOLLOWING) AS cnt3 FROM employees;
LAST_NAME SALARY CNT1 CNT2 CNT3
------------------------- ---------- ---------- ---------- ----------
Olson 2100 107 1 3
Markle 2200 107 3 2
Philtanker 2200 107 3 2
Landry 2400 107 5 8
Gee 2400 107 5 8
Colmenares 2500 107 11 10
Patel 2500 107 11 10
.
.
75。CUME_DIST
功能描述：计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。例如，
在一个3行的组中，返回的累计分布值为1/3、2/3、3/3
SAMPLE：下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比
SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees WHERE job_id LIKE 'PU%';
JOB_ID LAST_NAME SALARY CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PU_CLERK Baida 2900 .8
PU_CLERK Khoo 3100 1
PU_MAN Raphaely 11000 1
76。DENSE_RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数
SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）
SELECT d.department_id , e.last_name, e.salary, DENSE_RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('60', '90');
DEPARTMENT_ID LAST_NAME SALARY DRANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 3
60 Hunold 9000 4
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 2
77。FIRST
功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
Ande 80 6400 6100 14000
Lee 80 6800 6100 14000
Tuvault 80 7000 6100 14000
Sewall 80 7000 6100 14000
Marvins 80 7200 6100 14000
Bates 80 7300 6100 14000
.
.
.
78。FIRST_VALUE
功能描述：返回组中数据窗口的第一个值。
SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字
SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares
79。LAG
功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD
SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- ---------- ---------- ----------
Khoo 18-5月 -95 3100 0
Tobias 24-7月 -97 2800 3100
Baida 24-12月-97 2900 2800
Himuro 15-11月-98 2600 2900
Colmenares 10-8月 -99 2500 2600
80。LAST
功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
Ande 80 6400 6100 14000
Lee 80 6800 6100 14000
Tuvault 80 7000 6100 14000
Sewall 80 7000 6100 14000
Marvins 80 7200 6100 14000
Bates 80 7300 6100 14000
.
81。LAST_VALUE
功能描述：返回组中数据窗口的最后一个值。
SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字
SELECT department_id, last_name, salary, LAST_VALUE(last_name)
OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal
FROM employees
WHERE department_id in(20,30);
DEPARTMENT_ID LAST_NAME SALARY HIGHEST_SAL
------------- ------------------------- ---------- ------------
20 Fay 6000 Fay
20 Hartstein 13000 Hartstein
30 Colmenares 2500 Colmenares
30 Himuro 2600 Himuro
30 Tobias 2800 Tobias
30 Baida 2900 Baida
30 Khoo 3100 Khoo
30 Raphaely 11000 Raphaely
82。LEAD
功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）
SAMPLE：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值
SELECT last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;
LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99
83。MAX
功能描述：在一个组中的数据窗口中查找表达式的最大值。
SAMPLE：下面例子中dept_max返回当前行所在部门的最大薪水值
SELECT department_id, last_name, salary,
MAX(salary) OVER (PARTITION BY department_id) AS dept_max
FROM employees WHERE department_id in (10,20,30);
DEPARTMENT_ID LAST_NAME SALARY DEPT_MAX
------------- ------------------------- ---------- ----------
10 Whalen 4400 4400
20 Hartstein 13000 13000
20 Fay 6000 13000
30 Raphaely 11000 11000
30 Khoo 3100 11000
30 Baida 2900 11000
30 Tobias 2800 11000
30 Himuro 2600 11000
30 Colmenares 2500 11000
84。MIN
功能描述：在一个组中的数据窗口中查找表达式的最小值。
SAMPLE：下面例子中dept_min返回当前行所在部门的最小薪水值
SELECT department_id, last_name, salary,
MIN(salary) OVER (PARTITION BY department_id) AS dept_min
FROM employees WHERE department_id in (10,20,30);
DEPARTMENT_ID LAST_NAME SALARY DEPT_MIN
------------- ------------------------- ---------- ----------
10 Whalen 4400 4400
20 Hartstein 13000 6000
20 Fay 6000 6000
30 Raphaely 11000 2500
30 Khoo 3100 2500
30 Baida 2900 2500
30 Tobias 2800 2500
30 Himuro 2600 2500
30 Colmenares 2500 2500
85。NTILE
功能描述：将一个组分为"表达式"的散列表示，例如，如果表达式=4，则给组中的每一行分配一个数（从1到4），如果组中有20行，则给前5行分配1，给下5行分配2等等。如果组的基数不能由表达式值平均分开，则对这些行进行分配时，组中就没有任何percentile的行数比其它percentile的行数超过一行，最低的percentile是那些拥有额外行的percentile。例如，若表达式=4，行数=21，则percentile=1的有5行，percentile=2的有5行等等。
SAMPLE：下例中把6行数据分为4份
SELECT last_name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees
WHERE department_id = 100;
LAST_NAME SALARY QUARTILE
------------------------- ---------- ----------
Greenberg 12000 1
Faviet 9000 1
Chen 8200 2
Urman 7800 2
Sciarra 7700 3
Popp 6900 4
86。PERCENT_RANK
功能描述：和CUME_DIST（累积分配）函数类似，对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组中所有的行数）。该函数总是返回0～1（包括1）之间的数。
SAMPLE：下例中如果Khoo的salary为2900，则pr值为0.6，因为RANK函数对于等值的返回序列值是一样的
SELECT department_id, last_name, salary,
PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary) AS pr
FROM employees
WHERE department_id < 50
ORDER BY department_id,salary;
DEPARTMENT_ID LAST_NAME SALARY PR
------------- ------------------------- ---------- ----------
10 Whalen 4400 0
20 Fay 6000 0
20 Hartstein 13000 1
30 Colmenares 2500 0
30 Himuro 2600 0.2
30 Tobias 2800 0.4
30 Baida 2900 0.6
30 Khoo 3100 0.8
30 Raphaely 11000 1
40 Mavris 6500 0
• 87。PERCENTILE_CONT
功能描述：返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数PERCENT_RANK，如果没有正好对应的数据值，就通过下面算法来得到值：
RN = 1+ (P*(N-1)) 其中P是输入的分布百分比值，N是组内的行数
CRN = CEIL(RN) FRN = FLOOR(RN)
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
注意：本函数与PERCENTILE_DISC的区别在找不到对应的分布值时返回的替代值的计算方法不同
SAMPLE：在下例中，对于部门60的Percentile_Cont值计算如下：
P=0.7 N=5 RN =1+ (P*(N-1)=1+(0.7*(5-1))=3.8 CRN = CEIL(3.8)=4
FRN = FLOOR(3.8)=3
（4 - 3.8）* 4800 + (3.8 - 3) * 6000 = 5760
SELECT last_name, salary, department_id,
PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY salary)
OVER (PARTITION BY department_id) "Percentile_Cont",
PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary) "Percent_Rank"
FROM employees WHERE department_id IN (30, 60);
LAST_NAME SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank
------------------------- ---------- ------------- --------------- ------------
Colmenares 2500 30 3000 0
Himuro 2600 30 3000 0.2
Tobias 2800 30 3000 0.4
Baida 2900 30 3000 0.6
Khoo 3100 30 3000 0.8
Raphaely 11000 30 3000 1
Lorentz 4200 60 5760 0
Austin 4800 60 5760 0.25
Pataballa 4800 60 5760 0.25
Ernst 6000 60 5760 0.75
Hunold 9000 60 5760 1
88。PERCENTILE_DISC
功能描述：返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数CUME_DIST，如果没有正好对应的数据值，就取大于该分布值的下一个值。
注意：本函数与PERCENTILE_CONT的区别在找不到对应的分布值时返回的替代值的计算方法不同
SAMPLE：下例中0.7的分布值在部门30中没有对应的Cume_Dist值，所以就取下一个分布值0.83333333所对应的SALARY来替代
SELECT last_name, salary, department_id,
PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary )
OVER (PARTITION BY department_id) "Percentile_Disc",
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) "Cume_Dist"
FROM employees
WHERE department_id in (30, 60);
LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist
------------------------- ---------- ------------- --------------- ----------
Colmenares 2500 30 3100 .166666667
Himuro 2600 30 3100 .333333333
Tobias 2800 30 3100 .5
Baida 2900 30 3100 .666666667
Khoo 3100 30 3100 .833333333
Raphaely 11000 30 3100 1
Lorentz 4200 60 6000 .2
Austin 4800 60 6000 .6
Pataballa 4800 60 6000 .6
Ernst 6000 60 6000 .8
Hunold 9000 60 6000 1
89。RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，
然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。
有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，
则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。
SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）
SELECT d.department_id , e.last_name, e.salary, RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('60', '90');
DEPARTMENT_ID LAST_NAME SALARY DRANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 3
90。RATIO_TO_REPORT
功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。
SAMPLE：下例计算每个员工的工资占该类员工总工资的百分比
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME SALARY RR
------------------------- ---------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
• 91。REGR_ (Linear Regression) Functions
功能描述：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用。
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)
（下面的例子都是在SH用户下完成的）
SAMPLE 1：下例计算1998年最后三个星期中两种产品（260和270）在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距
SELECT t.fiscal_month_number "Month", t.day_number_in_month "Day",
REGR_SLOPE(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE,
REGR_INTERCEPT(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_ICPT
FROM sales s, times t
WHERE s.time_id = t.time_id
AND s.prod_id IN (270, 260)
AND t.fiscal_year=1998
AND t.fiscal_week_number IN (50, 51, 52)
AND t.day_number_in_week IN (6,7)
ORDER BY t.fiscal_month_desc, t.day_number_in_month;
Month Day CUM_SLOPE CUM_ICPT
---------- ---------- ---------- ----------
12 12 -68 1872
12 12 -68 1872
12 13 -20.244898 1254.36735
12 13 -20.244898 1254.36735
12 19 -18.826087 1287
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 26 67.2658228 58.9712313
12 26 67.2658228 58.9712313
12 27 37.5245541 284.958221
12 27 37.5245541 284.958221
12 27 37.5245541 284.958221
SAMPLE 2：下例计算1998年4月每天的累积交易数量
SELECT UNIQUE t.day_number_in_month,
REGR_COUNT(s.amount_sold, s.quantity_sold)
OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month)
"Regr_Count"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 1998 AND t.fiscal_month_number = 4;
DAY_NUMBER_IN_MONTH Regr_Count
------------------- ----------
1 825
2 1650
3 2475
4 3300
26 21450
30 22200
SAMPLE 3：下例计算1998年每月销售量中已开发票数量和总数量的累积回归线决定系数
SELECT t.fiscal_month_number,
REGR_R2(SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.fiscal_month_number) "Regr_R2"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 1998
GROUP BY t.fiscal_month_number
ORDER BY t.fiscal_month_number;
FISCAL_MONTH_NUMBER Regr_R2
------------------- ----------
1
2 1
3 .927372984
4 .807019972
5 .932745567
6 .94682861
7 .965342011
8 .955768075
9 .959542618
10 .938618575
11 .880931415
12 .882769189
SAMPLE 4：下例计算1998年12月最后两周产品260的销售量中已开发票数量和总数量的累积平均值
SELECT t.day_number_in_month,
REGR_AVGY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
"Regr_AvgY",
REGR_AVGX(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
"Regr_AvgX"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND s.prod_id = 260
AND t.fiscal_month_desc = '1998-12'
AND t.fiscal_week_number IN (51, 52)
ORDER BY t.day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX
------------------- ---------- ----------
14 882 24.5
14 882 24.5
15 801 22.25
15 801 22.25
16 777.6 21.6
18 642.857143 17.8571429
18 642.857143 17.8571429
20 589.5 16.375
21 544 15.1111111
22 592.363636 16.4545455
22 592.363636 16.4545455
24 553.846154 15.3846154
24 553.846154 15.3846154
26 522 14.5
27 578.4 16.0666667
SAMPLE 5：下例计算产品260和270在1998年2月周末销售量中已开发票数量和总数量的累积REGR_SXY, REGR_SXX, and REGR_SYY统计值
SELECT t.day_number_in_month,
REGR_SXY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxy",
REGR_SYY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_syy",
REGR_SXX(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxx"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND prod_id IN (270, 260)
AND t.fiscal_month_desc = '1998-02'
AND t.day_number_in_week IN (6,7)
ORDER BY t.day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx
------------------- ---------- ---------- ----------
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
7 18870.4 2116198.4 258.4
8 18870.4 2116198.4 258.4
14 18870.4 2116198.4 258.4
15 18870.4 2116198.4 258.4
21 18870.4 2116198.4 258.4
22 18870.4 2116198.4 258.4
92。ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。
SAMPLE：下例返回每个员工再在每个部门中按员工号排序后的顺序号
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < 50;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
93。STDDEV
功能描述：计算当前行关于组的标准偏离。（Standard Deviation）
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积标准偏离
SELECT last_name, hire_date,salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;
LAST_NAME HIRE_DATE SALARY StdDev
------------------------- ---------- ---------- ----------
Raphaely 07-12月-94 11000 0
Khoo 18-5月 -95 3100 5586.14357
Tobias 24-7月 -97 2800 4650.0896
Baida 24-12月-97 2900 4035.26125
Himuro 15-11月-98 2600 3649.2465
Colmenares 10-8月 -99 2500 3362.58829
94。STDDEV_POP
功能描述：该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Population）
SAMPLE：下例返回部门20、30、60的薪水值的总体标准偏差
SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
20 Hartstein 13000 3500
20 Fay 6000 3500
30 Raphaely 11000 3069.6091
30 Khoo 3100 3069.6091
30 Baida 2900 3069.6091
30 Colmenares 2500 3069.6091
30 Himuro 2600 3069.6091
30 Tobias 2800 3069.6091
60 Hunold 9000 1722.32401
60 Ernst 6000 1722.32401
60 Austin 4800 1722.32401
60 Pataballa 4800 1722.32401
60 Lorentz 4200 1722.32401
95。STDDEV_SAMP
功能描述： 该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Sample）
SAMPLE：下例返回部门20、30、60的薪水值的样本标准偏差
SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
------------- ------------------------- ---------- ---------- ----------
20 Hartstein 17-2月 -96 13000
20 Fay 17-8月 -97 6000 4949.74747
30 Raphaely 07-12月-94 11000
30 Khoo 18-5月 -95 3100 5586.14357
30 Tobias 24-7月 -97 2800 4650.0896
30 Baida 24-12月-97 2900 4035.26125
30 Himuro 15-11月-98 2600 3649.2465
30 Colmenares 10-8月 -99 2500 3362.58829
60 Hunold 03-1月 -90 9000
60 Ernst 21-5月 -91 6000 2121.32034
60 Austin 25-6月 -97 4800 2163.33077
60 Pataballa 05-2月 -98 4800 1982.42276
60 Lorentz 07-2月 -99 4200 1925.61678
96。SUM
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
101 Whalen 4400 4400
101 Mavris 6500 10900
101 Baer 10000 20900
101 Greenberg 12000 44900
101 Higgins 12000 44900
103 Lorentz 4200 4200
103 Austin 4800 13800
103 Pataballa 4800 13800
103 Ernst 6000 19800
108 Popp 6900 6900
108 Sciarra 7700 14600
108 Urman 7800 22400
108 Chen 8200 30600
108 Faviet 9000 39600
97。VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量（本例在SH用户下运行）
SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 6.1321E+11 1.2264E+12
1998-03 4.7058E+11 7.0587E+11
1998-04 4.6929E+11 6.2572E+11
1998-05 1.5524E+12 1.9405E+12
1998-06 2.3711E+12 2.8453E+12
1998-07 3.7464E+12 4.3708E+12
1998-08 3.7852E+12 4.3260E+12
1998-09 3.5753E+12 4.0222E+12
1998-10 3.4343E+12 3.8159E+12
1998-11 3.4245E+12 3.7669E+12
1998-12 4.8937E+12 5.3386E+12
• 98。VAR_SAMP
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算：
(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量
SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 6.1321E+11 1.2264E+12
1998-03 4.7058E+11 7.0587E+11
1998-04 4.6929E+11 6.2572E+11
1998-05 1.5524E+12 1.9405E+12
1998-06 2.3711E+12 2.8453E+12
1998-07 3.7464E+12 4.3708E+12
1998-08 3.7852E+12 4.3260E+12
1998-09 3.5753E+12 4.0222E+12
1998-10 3.4343E+12 3.8159E+12
1998-11 3.4245E+12 3.7669E+12
1998-12 4.8937E+12 5.3386E+12
99。VARIANCE
功能描述：该函数返回表达式的变量，Oracle计算该变量如下：
如果表达式中行数为1，则返回0
如果表达式中行数大于1，则返回VAR_SAMP
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积变化
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 30;
LAST_NAME SALARY Variance
------------------------- ---------- ----------
Raphaely 11000 0
Khoo 3100 31205000
Tobias 2800 21623333.3
Baida 2900 16283333.3
Himuro 2600 13317000
Colmenares 2500 11307000
100。RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，
然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。
有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，
则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。
SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）
SELECT d.department_id , e.last_name, e.salary, RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('60', '90');
DEPARTMENT_ID LAST_NAME SALARY DRANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 3
101。RATIO_TO_REPORT
功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。
SAMPLE：下例计算每个员工的工资占该类员工总工资的百分比
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME SALARY RR
------------------------- ---------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
• 102。REGR_ (Linear Regression) Functions
功能描述：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用。
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)
（下面的例子都是在SH用户下完成的）
SAMPLE 1：下例计算1998年最后三个星期中两种产品（260和270）在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距
SELECT t.fiscal_month_number "Month", t.day_number_in_month "Day",
REGR_SLOPE(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE,
REGR_INTERCEPT(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_ICPT
FROM sales s, times t
WHERE s.time_id = t.time_id
AND s.prod_id IN (270, 260)
AND t.fiscal_year=1998
AND t.fiscal_week_number IN (50, 51, 52)
AND t.day_number_in_week IN (6,7)
ORDER BY t.fiscal_month_desc, t.day_number_in_month;
Month Day CUM_SLOPE CUM_ICPT
---------- ---------- ---------- ----------
12 12 -68 1872
12 12 -68 1872
12 13 -20.244898 1254.36735
12 13 -20.244898 1254.36735
12 19 -18.826087 1287
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 20 62.4561404 125.28655
12 26 67.2658228 58.9712313
12 26 67.2658228 58.9712313
12 27 37.5245541 284.958221
12 27 37.5245541 284.958221
12 27 37.5245541 284.958221
SAMPLE 2：下例计算1998年4月每天的累积交易数量
SELECT UNIQUE t.day_number_in_month,
REGR_COUNT(s.amount_sold, s.quantity_sold)
OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month)
"Regr_Count"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 1998 AND t.fiscal_month_number = 4;
DAY_NUMBER_IN_MONTH Regr_Count
------------------- ----------
1 825
2 1650
3 2475
4 3300
.
26 21450
30 22200
SAMPLE 3：下例计算1998年每月销售量中已开发票数量和总数量的累积回归线决定系数
SELECT t.fiscal_month_number,
REGR_R2(SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.fiscal_month_number) "Regr_R2"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 1998
GROUP BY t.fiscal_month_number
ORDER BY t.fiscal_month_number;
FISCAL_MONTH_NUMBER Regr_R2
------------------- ----------
1
2 1
3 .927372984
4 .807019972
5 .932745567
6 .94682861
7 .965342011
8 .955768075
9 .959542618
10 .938618575
11 .880931415
12 .882769189
SAMPLE 4：下例计算1998年12月最后两周产品260的销售量中已开发票数量和总数量的累积平均值
SELECT t.day_number_in_month,
REGR_AVGY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
"Regr_AvgY",
REGR_AVGX(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)
"Regr_AvgX"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND s.prod_id = 260
AND t.fiscal_month_desc = '1998-12'
AND t.fiscal_week_number IN (51, 52)
ORDER BY t.day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX
------------------- ---------- ----------
14 882 24.5
14 882 24.5
15 801 22.25
15 801 22.25
16 777.6 21.6
18 642.857143 17.8571429
18 642.857143 17.8571429
20 589.5 16.375
21 544 15.1111111
22 592.363636 16.4545455
22 592.363636 16.4545455
24 553.846154 15.3846154
24 553.846154 15.3846154
26 522 14.5
27 578.4 16.0666667
SAMPLE 5：下例计算产品260和270在1998年2月周末销售量中已开发票数量和总数量的累积REGR_SXY, REGR_SXX, and REGR_SYY统计值
SELECT t.day_number_in_month,
REGR_SXY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxy",
REGR_SYY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_syy",
REGR_SXX(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxx"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND prod_id IN (270, 260)
AND t.fiscal_month_desc = '1998-02'
AND t.day_number_in_week IN (6,7)
ORDER BY t.day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx
------------------- ---------- ---------- ----------
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
7 18870.4 2116198.4 258.4
8 18870.4 2116198.4 258.4
14 18870.4 2116198.4 258.4
15 18870.4 2116198.4 258.4
21 18870.4 2116198.4 258.4
22 18870.4 2116198.4 258.4
• 103。ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。
SAMPLE：下例返回每个员工再在每个部门中按员工号排序后的顺序号
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < 50;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
104。STDDEV
功能描述：计算当前行关于组的标准偏离。（Standard Deviation）
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积标准偏离
SELECT last_name, hire_date,salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;
LAST_NAME HIRE_DATE SALARY StdDev
------------------------- ---------- ---------- ----------
Raphaely 07-12月-94 11000 0
Khoo 18-5月 -95 3100 5586.14357
Tobias 24-7月 -97 2800 4650.0896
Baida 24-12月-97 2900 4035.26125
Himuro 15-11月-98 2600 3649.2465
Colmenares 10-8月 -99 2500 3362.58829
105。STDDEV_POP
功能描述：该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Population）
SAMPLE：下例返回部门20、30、60的薪水值的总体标准偏差
SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
20 Hartstein 13000 3500
20 Fay 6000 3500
30 Raphaely 11000 3069.6091
30 Khoo 3100 3069.6091
30 Baida 2900 3069.6091
30 Colmenares 2500 3069.6091
30 Himuro 2600 3069.6091
30 Tobias 2800 3069.6091
60 Hunold 9000 1722.32401
60 Ernst 6000 1722.32401
60 Austin 4800 1722.32401
60 Pataballa 4800 1722.32401
60 Lorentz 4200 1722.32401
106。STDDEV_SAMP
功能描述： 该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Sample）
SAMPLE：下例返回部门20、30、60的薪水值的样本标准偏差
SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
------------- ------------------------- ---------- ---------- ----------
20 Hartstein 17-2月 -96 13000
20 Fay 17-8月 -97 6000 4949.74747
30 Raphaely 07-12月-94 11000
30 Khoo 18-5月 -95 3100 5586.14357
30 Tobias 24-7月 -97 2800 4650.0896
30 Baida 24-12月-97 2900 4035.26125
30 Himuro 15-11月-98 2600 3649.2465
30 Colmenares 10-8月 -99 2500 3362.58829
60 Hunold 03-1月 -90 9000
60 Ernst 21-5月 -91 6000 2121.32034
60 Austin 25-6月 -97 4800 2163.33077
60 Pataballa 05-2月 -98 4800 1982.42276
60 Lorentz 07-2月 -99 4200 1925.61678
107。SUM
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
101 Whalen 4400 4400
101 Mavris 6500 10900
101 Baer 10000 20900
101 Greenberg 12000 44900
101 Higgins 12000 44900
103 Lorentz 4200 4200
103 Austin 4800 13800
103 Pataballa 4800 13800
103 Ernst 6000 19800
108 Popp 6900 6900
108 Sciarra 7700 14600
108 Urman 7800 22400
108 Chen 8200 30600
108 Faviet 9000 39600
108。VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量（本例在SH用户下运行）
SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 6.1321E+11 1.2264E+12
1998-03 4.7058E+11 7.0587E+11
1998-04 4.6929E+11 6.2572E+11
1998-05 1.5524E+12 1.9405E+12
1998-06 2.3711E+12 2.8453E+12
1998-07 3.7464E+12 4.3708E+12
1998-08 3.7852E+12 4.3260E+12
1998-09 3.5753E+12 4.0222E+12
1998-10 3.4343E+12 3.8159E+12
1998-11 3.4245E+12 3.7669E+12
1998-12 4.8937E+12 5.3386E+12
109。VAR_SAMP
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算：
(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量
SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc;
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 6.1321E+11 1.2264E+12
1998-03 4.7058E+11 7.0587E+11
1998-04 4.6929E+11 6.2572E+11
1998-05 1.5524E+12 1.9405E+12
1998-06 2.3711E+12 2.8453E+12
1998-07 3.7464E+12 4.3708E+12
1998-08 3.7852E+12 4.3260E+12
1998-09 3.5753E+12 4.0222E+12
1998-10 3.4343E+12 3.8159E+12
1998-11 3.4245E+12 3.7669E+12
1998-12 4.8937E+12 5.3386E+12
110。VARIANCE
功能描述：该函数返回表达式的变量，Oracle计算该变量如下：
如果表达式中行数为1，则返回0
如果表达式中行数大于1，则返回VAR_SAMP
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积变化
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 30;
LAST_NAME SALARY Variance
------------------------- ---------- ----------
Raphaely 11000 0
Khoo 3100 31205000
Tobias 2800 21623333.3
Baida 2900 16283333.3
Himuro 2600 13317000
Colmenares 2500 11307000
zhyp29
关注
专栏目录
oracle-常用函数.docx
05-02
Oracle 常用函数详解 Oracle 是一种关系型数据库管理系统，提供了许多实用的函数来处理字符串、数字和日期等数据类型。在实际应用中，了解这些函数的使用可以大大提高开发效率和数据处理能力。下面将详细介绍 ...
Oracle 函数大全
基于Oracle11G
07-08 6618
建议使用CTRL + F精确搜索 F.1字符函数——返回字符值 (chr,concat,initcap,lower,lpad/rpad,nls_initcap,nls_lower,nls_upper,regexp_replace,regexp_substr,replace, trim/ltrim/rtrim,soundex,substr,translate,upper)
Orcale中常遇到的日期函数
liu_shujuxiaobai的博客
07-16 1493
（1）add_month()函数 add_month()函数有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字（以月为单位）； 例如：select add_month(sysdate，3) from dual；表示的意思就是3个月以后的时间； select add_month（sysdate，-3）from dual；表示的意思就是3个月前的时间； （2）month_between()函数 month_between()函数有两个参数，两个参数都表示日期，用于计算两个日期之间有几个月； 例如：s
Oracle函数MONTHS_BETWEEN的作用以及java翻译
m0_61466807的博客
07-21 629
函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。如果日期1比日期2小，返回值为负数。---呵呵呵呵~~~那就是说只要两个月份的总天数存在差异，就会出现月份差先变大后变小的情况。这绝对是这个函数的逻辑问题！但是没办法，前辈们偷懒直接用函数计算的，导致现在必须遵循这种逻辑。场景应用计算两个时间相差的月份差。...
sin函数对照表_初中常用三角函数值对照表
weixin_33470084的博客
12-30 1万+
摘要： 初中常用三角函数值对照表为你介绍初中常用的三角函数有正弦函数、余弦函数和正切函数等等，接下来分享具体的三角函数值表，供参考。 常用三角函数值对照表 sin0=sin0°=0 cos0=cos0°=1 tan0=tan0°=0sin15=0.650； sin15°=0.259 cos15=-0.759；cos15°=0.966 tan15=-0.855；tan15°=0.268 sin30°...
[原创]Oracle 9i 分析函数参考手册
cp26520的博客
11-29 209
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。[@more@]下面例子中使用的表来自Oracle自带的HR用户下的表，如...
Oracle-Sql语句资料oracle+110个常用函数经典SQL语句大全.zip
03-03
Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全，可供学习参考。
Oracle-维护常用SQL语句.pdf
最新发布
06-19
### Oracle 维护常用SQL语句详解 #### 1. 查看表空间的名称及大小 ```sql select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name...
Oracle函数及常用sql
01-16
二、Oracle常用SQL查询 1. **选择查询（SELECT）**： - 基本查询：`SELECT column1, column2 FROM table;` - 条件查询：`WHERE`子句，如`WHERE column = value`。 - 排序查询：`ORDER BY`子句，如`ORDER BY ...
oracle函数大全
wangxiaohigh
08-08 292
ORACLE函数大全(本人收藏)SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL&gt;selectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;AAZEROSPACE------------------------------------659748322.CHR给出整数,返回对应的字符;SQL...
oracle9转换类型,Oracle9i 中的三个转换函数（Oracle9i 手册第10章）
weixin_30562025的博客
04-03 129
以下三种属于自动转换：1. to_char将Date或者Number类型的函数转换成字符串.2. to_date将Number或Char或Varchar类型的转换成date.3,to_number将char或varchar2类型转换成Number.一个Number类型的值不会自动的转换成Date类型。任何Number和Date类型的都可以转换成string，任何用于string类型的函数都可以用于...
oracle9语法函数创建,oracle中的创建过程，函数，包
weixin_39822423的博客
04-05 100
一、创建存储过程存储过程是在oracle中存取完成特定业务逻辑的代码块。存储过程是命名块，匿名块不存在数据库中，命名块会存储到数据库中，匿名块每次运行都需要提前编译，命名块一次存储，只会编译一次。命名块可以多次使用。创建存储过程的语法：create [or replace] procedure 存储过程的名称(参数名[in/out/inout] 参数类型，参数名...)]is/as变量声明部分be...
日期函数months_between的用法
热门推荐
Oracle
04-08 10万+
MONTHS_BETWEEN (date1, date2) 用于计算date1和date2之间有几个月。 如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数。 如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数。 如果date1和date2日期一样，那么MONTHS_BET...
Oracle常用数值型函数
睿思达DBA
02-03 558
Oracle常用数值型函数 1、求绝对值 格式： ABS(x) 举例： SQL> select ABS(-25.1) as RESULT from dual; RESULT ---------- 25.1 2、取整函数 格式： FLOOR(x) --返回小于或等于x的最大整数 CEIL(x) --返回大于或等于x的最小整数 举例： SQL> sel...
oracle数字转为字符串
书山有路，学海无涯
01-29 4万+
一。数字转为字符串  格式为9,0,$,l, .SQL> select ename,to_char(sal,'9999.000') salary from emp; ENAME SALARY ---------- -------------------- SMITH 808.000 ALLEN 1608.000 SQL> select ename,to_char(sal,'$00099
Oracle函数——数值函数
漓涂
05-07 3495
1、ABS(n)函数:返回数字的绝对值     select ABS(-7) FROM DUAL     结果：7 2、SIGN(x)函数：检测x的正负.如果x&lt;0返回－1.如果x=0返回0.如果x&gt;0返回1.     select SIGN(-7) FROM DUAL     结果：-1   3、CEIL(n)函数：返回大于或等于n的最小的整数值     select ...
oracle中的数值函数整理
曹海成的专栏
12-19 2433
对Oracle的数值函数多知道一些还是有好处的,做商业报表类的项目的时候会经常用到。 起码要知道oracle都给我们提供了哪些处理函数，不要到时候需要用的时候却不知道某个函数的存在而导致开发过程绕弯子。今天我就捧着一本《oracle database11g完全参考手册》将oracle提供给我们的数值函数从头捋了一遍。 主要分为三块介绍（单值函数、聚合函数、列表函数） 一、单值函数（比较简单，看
Oracle数据库常用函数详解
"Oracle数据库中的110个常用函数包括ASCII、CHR、CONCAT、INITCAP、INSTR、LENGTH、LOWER、UPPER等，这些函数在处理字符串和字符时非常实用。" 在Oracle数据库中，熟悉并掌握常用函数是进行高效SQL查询和数据操作的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值