oracle 常用功能函数汇总********************
*******************************************************************************
* SQL Group Functions (num can be a column or expression) *
(null values are ignored, default between distinct 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 deviation
SUM([distinct or all] num) -- sum of values
VARIANCE([distinct or all] num) -- variance of values
*******************************************************************************
* Miscellaneaous Functions : *
*******************************************************************************
DECODE(expr, srch1, return1 [,srch2, return2...], default]
-- if no search matches the expression then the default is returned,
-- otherwise, the first search that matches will cause
-- the corresponding return value to be returned
DUMP(column_name [,fmt [,start_pos [, length]]])
-- returns an internal oracle format, used for getting info about a column
-- format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters
-- return type codes : 1 = varchar2, 2 = number, 8 = long, 12 = date,
-- 23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel
GREATEST(expr [,expr2 [, expr3...]]
-- returns the largest value of all expressions
LEAST(expr [,expr2 [, expr3...]]
-- returns the smallest value of all expressions
NVL(expr1 ,expr2
-- if expr1 is not null, it is returned, otherwise expr2 is returned
-- returns sql error code of last error. Can not be used directly in query,
-- value must be set to local variable first
SQLERRM
-- returns sql error message of last error. Can not be used directly in query,
-- value must be set to local variable first
UID
-- returns the user id of the user you are logged on as
-- useful in selecting information from low level sys tables
USER
-- returns the user name of the user you are logged on as
USERENV('option')
-- returns information about the user you are logged on as
-- options : ENTRYID, SESSIONID, TERMINAL, LANGUAGE, LABEL, OSDBA
-- (all options not available in all Oracle versions)
VSIZE(expr)
-- returns the number of bytes used by the expression
-- useful in selecting information about table space requirements
*******************************************************************************
* SQL Date Functions (dt represents oracle date and time) *
* (functions return an oracle date unless otherwise 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) -- returns fractional value of months between dt1, dt2
NEW_TIME(dt, tz1, tz2) -- dt = date in time zone 1, returns date in time zone 2
NEXT_DAY(dt, str) -- date of first (str) after dt (str = 'Monday', etc..)
SYSDATE -- present system date
ROUND(dt [,fmt] -- rounds dt a