Oracle常用函数
| * 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 |
| MIN([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 str1str2) |
| 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) |
| IYYY4 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) |
| RRlast 2 digits of |
year in prior or next century |
| Q quarter or year, 1 to 4 |
| MMmonth - from 01 to 12 |
| MONTH month spelled out |
| MON month 3 letter abbreviation |
| RMroman numeral for month |
| WWweek of year, 1 to 53 |
| IWISO 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 |
| DDday of month, 1 to 31 |
| DDD day of year, 1 to 366 |
| DAY day of week spel |
led out, nine characters right padded |
| DYday abbreviation |
| J # of |
days since Jan 1, 4712 BC |
| HH, HH12hour of day, 1 to 12 |
| HH24hour of day, 0 to 23 |
| MIminute of hour, 0 to 59 |
| SSsecond 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 puctuationpunc |
tuation between format items |
, as in 'DD/MM/YY' |
| any texttext between format items |
| THconv |
erts 1 to '1st', 2 to '2nd', |
and so on |
| SPconverts 1 to 'o |
ne', 2 to 'two', and so on |
| SPTHconverts 1 to 'F |
IRST', 2 to 'SECOND', and so on |
| FXfill |
exact : uses exact pattern |
matching |
| FMfill mode: tog |
gles suppression of blanks in output |
