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

来源：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