Oracle 最常用功能函數經典匯總

［ 作者：shuipipi 轉貼自：csdn 點擊數：81 更新時間：2005-11-17 ］
減小字型 增大字型

* 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