oracle常用函数

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
SQLCODE
      -- 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 us