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 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 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(num2, num1)      -- logarithm base num2 of num1
MOD(num2, num1)      -- remainder of num2 / num1
POWER(num2, num1)    -- num2 raised to the num1 power
ROUND(num1 [,num2]   -- num1 rounded to num2 decimel places (default 0)
SIGN(num)            -- sign of num * 1, 0 if num = 0
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(num1 [,num2]   -- truncate num1 to num2 decimel places (default 0)
  
*******************************************************************************
* String Functions, String Result :                                           *
*******************************************************************************
(num)                   -- ASCII character for num
CHR(num)                -- ASCII character for num
CONCAT(str1, str2)      -- str1 concatenated with str2 (same as str1||str2)
INITCAP(str)            -- capitalize first letter of each word in str
LOWER(str)              -- str with all letters in lowercase
LPAD(str1, num [,str2]) -- left pad str1 to length num with str2 (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(str1, str2 [,str3]) -- replaces str2 with str3 in str1
                             -- deletes str2 from str1 if str3 is omitted
RPAD(str1, num [,str2])     -- right pad str1 to length num with str2 (default spaces)
RTRIM(str [,set])           -- remove set from right side of str (default spaces)
SOUNDEX(str)                -- phonetic representation of str
SUBSTR(str, num2 [,num1])   -- substring of str, starting with num2,
                             -- num1 characters (to end of str if num1 is omitted)
SUBSTRB(str, num2 [,num1])  -- same as substr but num1, num2 expressed in bytes
TRANSLATE(str, set1, set2)  -- replaces set1 in str with set2
                             -- if set2 is longer than set1, it will be truncated
UPPER(str)                  -- str with all letters in

[1] [2]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18