Oracle 最常用功能函数经典汇总 (zz)

转载 2005年05月28日 10:50:00

来源:ChinaITLab 收集整理
2004-6-14 10:58:00
 
  * SQL Group Function
*
 s (num can be a column or ex

 pression)          

 
  (null values are ign
*
 ored, default between distin

 ct and all is all)      

 
  ********************
***************
 ****************************

 ****************

 
  AVG([distinct or all] num)   
 -- average value
 
  COUNT(distinct or all] num)  
 -- number of values
 
  MAX([distinct or all
 ] num)   -- maximum value
 
 
  MAX([distinct or all] num)   
 -- minimum value
 
  STDDEV([distinct or
 all] num)  -- standard devi
 ation
 
  SUM([distinct or all
 ] num)   -- sum of values
 
 
  VARIANCE([distinct o
 r all] num) -- variance of v
 alues
 
                                                                               
  ********************************
***********************
 ************************

 
  * Miscellaneaous Functions :  
*
                        

 
  ********************
***************
 ****************************

 ****************

 
  DECODE(expr, srch1,
 return1 [,srch2, return2...]
 , default]
 
      -- if no search matches t
 he expression then the default is returned,
 
      -- otherwise,
 the first search that match
 es will cause
 
      -- the corres
 ponding return value to be r
 eturned
 
  DUMP(column_name [,fmt [,start_p
 os [, length]]])
 
     -- returns an
column
 internal oracle format, used

 for getting info about a

 
     -- format options : 8 = oc
 tal, 10 = decimel, 16 = hex, 17 = characters
 
     -- return type
 codes : 1 = varchar2, 2 = n
 umber, 8 = long, 12 = date,
 
     --  23 = raw,
 24 = long raw, 69 = rowid,
 96 = char, 106 = mlslabel
 
  GREATEST(expr [,expr2 [, expr3...]]       
     -- returns the largest val
 ue of all expressions
 
  LEAST(expr [,expr2 [, expr3...]]             
     -- returns the
 smallest value of all expre
 ssions
 
  NVL(expr1 ,expr2                                             
     -- if expr1 is not null, i
 t is returned, otherwise expr2 is returned
 
  SQLCODE                                                               
     -- returns sql error code
query,
 of last error. Can not be used directly in

 
     -- value must
 be set to local variable fir
 st
 
  SQLERRM                                                               
     -- returns sql
in query,
 error message of last error

 . Can not be used directly

 
     -- value must be set to lo
 cal variable first
 
  UID                                                                       
     -- returns the user id of
 the user you are logged on as
 
     -- useful in s
 electing information from lo
 w level sys tables
 
  USER                                                                     
     -- returns the
 user name of the user you a
 re logged on as
 
  USERENV('option')                                           
     -- returns inf
 ormation about the user you
 are logged on as
 
     -- options : E
 NTRYID, SESSIONID, TERMINAL,
 LANGUAGE, LABEL, OSDBA
 
     --      (
 all options not available in
 all Oracle versions)
 
  VSIZE(expr)                                                       
     -- returns the number of b
 ytes used by the expression
 
     -- useful in s
 electing information about t
 able space requirements
 
                                                                               
  ********************
***************
 ****************************

 ****************

 
  * SQL Date Functions (dt represe
*
 nts oracle date and time)          

 
  * (functions return
*
 an oracle date unless otherw

 ise specified)        

 
  ********************************
***********************
 ************************

 
  ADD_MONTHS(dt, num)
    -- adds num months to
 dt (num can be negative)
 
  LAST_DAY(dt)    
    -- last day of month in
 month containing dt
 
  MONTHS_BETWEEN(dt1, dt2) -- retu
dt2
 rns fractional value of months between dt1,

 
  NEW_TIME(dt, tz1, tz
zone 2
 2)  -- dt = date in time zo

 ne 1, returns date in time

 
  NEXT_DAY(dt, str)    -- date
etc..)
 of first (str) after dt (str = 'Monday',

 
  SYSDATE         -- present system date 
  ROUND(dt [,fmt]     -- roun
 ds dt as specified by format fmt
 
  TRUNC(dt [,fmt]  
    -- truncates dt as spe
 cified by format fmt
 
                                                                               
  ********************************
***********************
 ************************

 
  * Number Functions :      
*
                        

 
  ********************************
***********************
 ************************

 
  ABS(num)       -- absolute
 value of num
 
  CEIL(num)      -- smallest integer > or = num 

  COS(num)       -- cosine(n
 um), num in radians
 
  COSH(num)     
  -- hyperbolic cosine(num)
 
 
  EXP(num)      
 -- e raised to the num powe
 r
 
  FLOOR(num)      -- largest
 integer < or = num
 
  LN(num)       -- natural
 logarithm of num
 
  LOG(num2, num1)   -- logarith
 m base num2 of num1
 
  MOD(num2, num1)   -- remainde
 r of num2 / num1
 
  POWER(num2, num1) 
  -- num2 raised to the num1
 power
 
  ROUND(num1 [,num2]  -- num1 rou
 nded to num2 decimel places (default 0)
 
  SIGN(num)      -- sign of
 num * 1, 0 if num = 0
 
  SIN(num)      
 -- sin(num), num in radians
 
 
  SINH(num)      -- hyperbolic sine(num) 
  SQRT(num)      -- square root of num     
  TAN(num)       -- tangent(
 num), num in radians
 
  TANH(num)     
  -- hyperbolic tangent(num)
 
 
  TRUNC(num1 [,num2]  -- truncate
 num1 to num2 decimel places (default 0)
 
                                                                               
  ********************************
***********************
 ************************

 
  * String Functions,
*
 String Result :      

                

 
  ********************************
***********************
 ************************

 
  (num)          -- ASCII
 character for num
 
  CHR(num)      
   -- ASCII character for n
 um
 
  CONCAT(str1, str2)   -- str1
 concatenated with str2 (same as str1||str2)
 
  INITCAP(str)    
   -- capitalize first lett
 er of each word in str
 
  LOWER(str)       -- str w
 ith all letters in lowercase
 
  LPAD(str1, num [,str2]) -- left
spaces)
 pad str1 to length num with str2 (default

 
  LTRIM(str [,set]) 
    -- remove set from left
 side of str (default spaces)
 
  NLS_INITCAP(str [,nl
 s_val]) -- same as initcap f
 or different languages
 
  NLS_LOWER(str [,nls_
 val])  -- same as lower for
 different languages
 
  REPLACE(str1, str2 [,str3]) -- r
 eplaces str2 with str3 in str1
 
                 --
 deletes str2 from str1 if str3 is omitted
 
  RPAD(str1, num [,str
(default spaces)
 2])   -- right pad str1 to

 length num with str2

 
  RTRIM(str [,set]) 
spaces)
      -- remove set from

 right side of str (default

 
  SOUNDEX(str)    
     -- phonetic represen
 tation of str
 
  SUBSTR(str, num2 [,n
 um1])  -- substring of str,
 starting with num2,
 
                 --
omitted)
 num1 characters (to end of str if num1 is

 
  SUBSTRB(str, num2 [,
bytes
 num1]) -- same as substr bu

 t num1, num2 expressed in

 
  TRANSLATE(str, set1,
 set2) -- replaces set1 in
 str with set2
 
                 --
truncated
 if set2 is longer than set1, it will be

 
  UPPER(str)     
     -- str with all lett
 ers in uppercase
 
                                                                               
  ********************
***************
 ****************************

 ****************

 
  * String Functions,
*
 Numeric Result :      

                

 
  ********************************
***********************
 ************************

 
                                                                               
  ASCII(str)           
  -- ASCII value of str
 
  INSTR(str1, str2 [,num1 [,num2]]
 ) -- position of num2th occurrence of
 
                  
   -- str2 in str1, starting at num1
 
                  
   -- (num1, num2 default to 1)
 
  INSTRB(str1, str2 [,num1 [num2]]

 ) -- same as instr, byte values for num1, num2

 
  LENGTH(str)    
         -- number of
 characters in str
 
  LENGTHB(str)          
  -- number of bytes in str
 
  NLSSORT(str [,nls_val])    
   -- nls_val byte value of str
 
                                                                               
  ********************************
***********************
 ************************

 
  * SQL Conversion Functions   
*
                        

 
  ********************************
***********************
 ************************

 
  CHARTOROWID(str)        
   -- converts str to ROWID
 
  CONVERT(str, chr_set2 [,chr_set1
 ]) -- converts str to chr_set2
 
            
character set
         -- chr_set1

 default is the datbase

 
  HEXTORAW(str)   
   -- converts hex string va
 lue to internal raw values
 
  RAWTOHEX(raw_val)   -- convert
 s raw hex value to hex string value
 
  ROWIDTOCHAR(rowid) 
  -- converts rowid to 18 ch
 aracter string format
 
  TO_CHAR(expr [,fmt])
fmt
  -- converts expr(date or n

 umber) to format specified by

 
  TO_DATE(str [,fmt])
   -- converts string to dat
 e
 
  TO_MULTI_BYTE(str)  -- convert
 s single byte string to multi byte string
 
  TO_NUMBER(str [,fmt]) -- convert
 s str to a number formatted by fmt
 
  TO_SINGLE_BYTE(str)
   -- converts multi byte st
 ring to single byte string
 
                                                                               
  ********************************
***********************
 ************************

 
  * SQL Date Formats 
*
               

                

 
  ********************
***************
 ****************************

 ****************

 
                                                                               
  BC, B.C.    BC indicator                             
  AD, A.D.    AD indicator                             
  CC, SCC     Cent
 ury Code (SCC includes space
 or - sign)
 
  YYYY, SYYYY   4 digit year (SY
 YYY includes space or - sign)
 
  IYYY      4 digit ISO year                         
  Y,YYY      4 digit year with comma         
  YYY, YY, or Y  last 3, 2, or 1
 digit of year
 
  YEAR, SYEAR   year spelled out
 (SYEAR includes space or - sign)
 
  RR       last 2 digits of
 year in prior or next century
 
  Q        quarter or year, 1 to 4             
  MM       month - from 01 to 12                 
  MONTH      month spelled out                     
  MON       month 3 letter abbreviation   
  RM       roman numeral for month             
  WW       week of year, 1 to 53                 
  IW       ISO week of year
 , 1 to 52 or 1 to 53
 
  W        week of month, 1
 to 5 (week 1 begins 1st day of the month)
 
  D        day of week, 1 to 7                     
  DD       day of month, 1 to 31                 
  DDD       day of year, 1 to 366               
  DAY       day of week spel
 led out, nine characters right padded
 
  DY       day abbreviation                           
  J        # of
 days since Jan 1, 4712 BC
 
 
  HH, HH12    hour of day, 1 to 12             
  HH24      hour of day, 0 to 23                 
  MI       minute of hour, 0 to 59             
  SS       second of minute, 0 to 59         
  SSSSS      seco
 nds past midnight, 0 to 8639
 9
 
  AM, A.M.    am indicator                             
  PM, P.M.    pm indicator                             
  any puctuation punc
 tuation between format items
 , as in 'DD/MM/YY'
 
  any text    text between format items   
  TH       conv
 erts 1 to '1st', 2 to '2nd',
 and so on
 
  SP       converts 1 to 'o
 ne', 2 to 'two', and so on
 
  SPTH      converts 1 to 'F
 IRST', 2 to 'SECOND', and so on
 
  FX       fill
 exact : uses exact pattern
 matching
 
  FM       fill mode : tog
 gles suppression of blanks in output
 
 

 

ORACLE常用函数功能演示

  • 2010年06月29日 23:59
  • 432KB
  • 下载

ORACLE常用函数功能演示

  • 2012年11月14日 11:35
  • 432KB
  • 下载

oracle SQL里常用的时间函数,经典推荐

常用日期型函数 1。Sysdate 当前日期和时间SQL> Select sysdate from dual; SYSDATE----------21-6月 -05 2。Last_day 本月最后一天...
  • zftang
  • zftang
  • 2011年03月23日 21:29
  • 838

ZZ 常用算法经典代码(C++版)

copy from renren.com 常用算法经典代码(C++版) 来源: 朱玉博Lyle.WPF的日志   一、快速排序 void qsort(int x,int y) ...

Oracle常用功能函数

oracle 常用功能函数汇总******************** ***************************************************************...

shell常用命令语法及功能zz

shell常用命令语法及功能zz   虽然在shell脚本中可以使用任意的unix命令,但是还是由一些相对更常用的命令。这些命令通常是用来进行文件和文字操作的。 常用命令语法及功能 (1...
  • brk1985
  • brk1985
  • 2014年03月04日 10:42
  • 681

MATLAB常用的基本数学函数 zz

一、MATLAB常用的基本数学函数   abs(x):纯量的绝对值或向量的长度   angle(z):复数z的相角(Phase angle)   sqrt(x):开平方   real(z):复...

zz:ORACLE to_char函数详解

转自:http://hi.baidu.com/djx_5211314/blog/item/4fec8713905df5826438db47.html The following are ...

oracle常用函数汇总大全

  • 2015年12月03日 15:17
  • 27KB
  • 下载

110个Oracle常用函数整理汇总

1. ASCII返回与指定的字符对应的十进制数;SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;A A...
  • zqpgood
  • zqpgood
  • 2011年03月10日 22:59
  • 384
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 最常用功能函数经典汇总 (zz)
举报原因:
原因补充:

(最多只允许输入30个字)