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 uppercase 

  
* String Functions, Numeric Result :                                        
 * 


  
ASCII(str)                        -- ASCII value of str

INSTR(str1, str2 [,num1 [,num2]]) -- position of num2th occurrence of
 
                                 -- str2 in str1, starting at num1
       
                           -- (num1, num2 default to 1)
INSTRB(str1, str2
[,num1 [num2]]) -- same as instr, byte values for num1, num2
LENGTH(str)    
                  -- number of characters in str
LENGTHB(str)             
        -- number of bytes in str
NLSSORT(str [,nls_val])           --
nls_val byte value of str 

  
* SQL Conversion Functions                                                 
  *


CHARTOROWID(str)                   -- converts str to ROWID
CONVERT(str,
chr_set2 [,chr_set1]) -- converts str to chr_set2
                         
          -- chr_set1 default is the datbase character set
HEXTORAW(str)    
    -- converts hex string value to internal raw values
RAWTOHEX(raw_val) 
   -- converts raw hex value to hex string value
ROWIDTOCHAR(rowid)    --
converts rowid to 18 character string format
TO_CHAR(expr [,fmt])  --
converts expr(date or number) to format specified by fmt
TO_DATE(str
[,fmt])   -- converts string to date
TO_MULTI_BYTE(str)    -- converts
single byte string to multi byte string
TO_NUMBER(str [,fmt]) -- converts
str to a number formatted by fmt
TO_SINGLE_BYTE(str)   -- converts multi
byte string to single byte string 

  

* SQL Date Formats                                                          
 * 

  
BC, B.C.        BC indicator
AD, A.D.        AD indicator
CC,
SCC         Century Code (SCC includes space or - sign)
YYYY, SYYYY     4
digit year (SYYYY includes space or - sign)
IYYY            4 digit ISO year

Y,YYY           4 digit year with comma
YYY, YY, or Y   last 3, 2, or 1
digit of year
YEAR, SYEAR     year spelled out (SYEAR includes space or -
sign)
RR              last 2 digits of year in prior or next century
Q 
             quarter or year, 1 to 4
MM              month - from 01 to 12

MONTH           month spelled out
MON             month 3 letter
abbreviation
RM              roman numeral for month
WW          
   week of year, 1 to 53
IW              ISO week of year, 1 to 52 or 1 to
53
W               week of month, 1 to 5 (week 1 begins 1st day of the
month)
D               day of week, 1 to 7
DD              day of month,
1 to 31
DDD             day of year, 1 to 366
DAY             day of
week spelled out, nine characters right padded
DY              day
abbreviation
J               # of days since Jan 1, 4712 BC
HH, HH12    
   hour of day, 1 to 12
HH24            hour of day, 0 to 23
MI       
      minute of hour, 0 to 59
SS              second of minute, 0 to 59

SSSSS           seconds past midnight, 0 to 86399
AM, A.M.        am
indicator
PM, P.M.        pm indicator
any puctuation  punctuation
between format items, as in ''''DD/MM/YY''''
any text        text between
format items
TH              converts 1 to ''''1st'''', 2 to ''''2nd'''',
and so on
SP              converts 1 to ''''one'''', 2 to ''''two'''', and
so on
SPTH            converts 1 to ''''FIRST'''', 2 to ''''SECOND'''', and
so on
FX              fill exact : uses exact pattern matching
FM       
      fill mode  : toggles suppression of blanks in output 
  

转载于:https://www.cnblogs.com/you-zi/articles/4535895.html