Oracle 最常用功能函数经典汇总 (zz)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 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 column internal oracle format, used
for getting info about a
-- format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters -- return typecodes : 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 thesmallest value of all expressions NVL(expr1 ,expr2 -- if expr1 is not null, it 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 first SQLERRM -- returns sqlin query, error message of last error
. Can not be used directly
-- 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 theuser 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 inall Oracle versions)