ORACLE函数大全

 ORACLE函数大全 
   PLSQL中提供了两个内置的统计函数VARIANCE和STDDEV，分别对应于方差和标准差，他们的算法如下：
VARIANCE：方差，数列中各项和平均值的差平方后求和，然后除以数列个数减一，得到的即为方差。
STDDEV：标准差，就是上面算出来的方差的开平方根

  
  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-','888 88888')||'转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   出现的zifu位置,默认为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 
  Top
10 楼dinya2003（OK）回复于 2004-11-08 18:24:01 得分 0

 
  
  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.USERENV 
  返回当前用户环境的信息,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