熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle 常用功能函數匯總

2013-11-13 15:59:02  來源: 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 srch return [srch return] 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 : = octal = decimel = hex = characters
      return type codes : = varchar = number = long = date
       = raw = long raw = rowid = char = mlslabel
  GREATEST(expr [expr [ expr]]
      returns the largest value of all expressions
  LEAST(expr [expr [ expr]]
      returns the smallest value of all expressions
  NVL(expr expr
      if expr is not null it is returned otherwise expr 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 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(dt dt) returns fractional value of months between dt dt
  NEW_TIME(dt tz tz)  dt = date in time zone returns date in time zone
  NEXT_DAY(dt str)     date of first (str) after dt (str = Monday etc)
  SYSDATE          present system date
  ROUND(dt [fmt]      rounds dt as specified by format fmt
  TRUNC(dt [fmt]      truncates dt as specified by format fmt
   
  *******************************************************************************
  * Number Functions :                             *
  *******************************************************************************
  ABS(num)       absolute value of num
  CEIL(num)       smallest integer > or = num
  COS(num)       cosine(num) num in radians
  COSH(num)       hyperbolic cosine(num)
  EXP(num)       e raised to the num power
  FLOOR(num)      largest integer < or = num
  LN(num)        natural logarithm of num
  LOG(num num)    logarithm base num of num
  MOD(num num)    remainder of num / num
  POWER(num num)   num raised to the num power
  ROUND(num [num]  num rounded to num decimel places (default )
  SIGN(num)       sign of num * if num =
  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(num [num]  truncate num to num decimel places (default )
   
  *******************************************************************************
  * String Functions String Result :                      *
  *******************************************************************************
  (num)          ASCII character for num
  CHR(num)         ASCII character for num
  CONCAT(str str)    str concatenated with str (same as str||str)
  INITCAP(str)       capitalize first letter of each word in str
  LOWER(str)        str with all letters in lowercase
  LPAD(str num [str]) left pad str to length num with str (default spaces)
  LTRIM(str [set])    remove set from left side of str (default spaces)
  NLS_INITCAP(str [nls_val]) same as initcap for different languages
  NLS_LOWER(str [nls_val])  same as lower for different languages
  REPLACE(str str [str]) replaces str with str in str
                 deletes str from str if str is omitted
  RPAD(str num [str])   right pad str to length num with str (default spaces)
  RTRIM(str [set])      remove set from right side of str (default spaces)
  SOUNDEX(str)         phonetic representation of str
  SUBSTR(str num [num])  substring of str starting with num
                 num characters (to end of str if num is omitted)
  SUBSTRB(str num [num])  same as substr but num num expressed in bytes
  TRANSLATE(str set set)  replaces set in str with set
                 if set is longer than set it will be truncated
  UPPER(str)          str with all letters in uppercase
   
  *******************************************************************************
  * String Functions Numeric Result :                     *
  *******************************************************************************
   
  ASCII(str)             ASCII value of str
  INSTR(str str [num [num]]) position of numth occurrence of
                    str in str starting at num
                    (num num default to )
  INSTRB(str str [num [num]]) same as instr byte values for num num
  LENGTH(str)            number of characters in str
  LENGTHB(str)            number of bytes in str
  NLSSORT(str [nls_val])      nls_val byte value of str
   
  **********************************
From:http://tw.wingwit.com/Article/program/Oracle/201311/17624.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.