简述oracle函数的功能,Oracle-最常用功能函数经典汇总-(zz)

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)