ZT: Oracle 最常用功能函数经典汇总

转载 2004年07月05日 16:28:00
Oracle 最常用功能函数经典汇总
来源: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 devi
ation
  SUM([distinct or all
] num)   -- sum of values

  VARIANCE([distinct o
r all] num) -- variance of v
alues
                                                                               
  ********************************
***********************
************************

  * Miscellaneaous Functions :  
*
                       

  ********************
***************
****************************

****************

  DECODE(expr, srch1,
return1 [,srch2, return2...]
, default]
      -- if no search matches t
he expression then the default is returned,
      -- otherwise,
the first search that match
es will cause
      -- the corres
ponding return value to be r
eturned
  DUMP(column_name [,fmt [,start_p
os [, length]]])
     -- returns an
column
internal oracle format, used

for getting info about a

     -- format options : 8 = oc
tal, 10 = decimel, 16 = hex, 17 = characters
     -- return type
codes : 1 = varchar2, 2 = n
umber, 8 = long, 12 = date,
     --  23 = raw,
24 = long raw, 69 = rowid,
96 = char, 106 = mlslabel
  GREATEST(expr [,expr2 [, expr3...]]       
     -- returns the largest val
ue of all expressions
  LEAST(expr [,expr2 [, expr3...]]             
     -- returns the
smallest value of all expre
ssions
  NVL(expr1 ,expr2                                             
     -- if expr1 is not null, i
t 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 fir
st
  SQLERRM                                                               
     -- returns sql
in query,
error message of last error

. Can not be used directly

     -- value must be set to lo
cal variable first
  UID                                                                       
     -- returns the user id of
the user you are logged on as
     -- useful in s
electing information from lo
w level sys tables
  USER                                                                     
     -- returns the
user name of the user you a
re logged on as
  USERENV('option')                                           
     -- returns inf
ormation about the user you
are logged on as
     -- options : E
NTRYID, SESSIONID, TERMINAL,
LANGUAGE, LABEL, OSDBA
     --      (
all options not available in
all Oracle versions)
  VSIZE(expr)                                                       
     -- returns the number of b
ytes used by the expression
     -- useful in s
electing information about t
able space requirements
                                                                               
  ********************
***************
****************************

****************

  * SQL Date Functions (dt represe
*
nts oracle date and time)          

  * (functions return
*
an oracle date unless otherw

ise 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) -- retu
dt2
rns fractional value of months between dt1,

  NEW_TIME(dt, tz1, tz
zone 2
2)  -- dt = date in time zo

ne 1, returns date in time

  NEXT_DAY(dt, str)    -- date
etc..)
of first (str) after dt (str = 'Monday',

  SYSDATE         -- present system date
  ROUND(dt [,fmt]     -- roun
ds dt as specified by format fmt
  TRUNC(dt [,fmt]  
   -- truncates dt as spe
cified by format fmt
                                                                               
  ********************************
***********************
************************

  * Number Functions :      
*
                       

  ********************************
***********************
************************

  ABS(num)       -- absolute
value of num
  CEIL(num)      -- smallest integer > or = num

  COS(num)       -- cosine(n
um), num in radians
  COSH(num)     
 -- hyperbolic cosine(num)
  EXP(num)      
-- e raised to the num powe
r
  FLOOR(num)      -- largest
integer < or = num
  LN(num)       -- natural
logarithm of num
  LOG(num2, num1)   -- logarith
m base num2 of num1
  MOD(num2, num1)   -- remainde
r of num2 / num1
  POWER(num2, num1) 
 -- num2 raised to the num1
power
  ROUND(num1 [,num2]  -- num1 rou
nded 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 n
um
  CONCAT(str1, str2)   -- str1
concatenated with str2 (same as str1||str2)
  INITCAP(str)    
  -- capitalize first lett
er of each word in str
  LOWER(str)       -- str w
ith all letters in lowercase
  LPAD(str1, num [,str2]) -- left
spaces)
pad str1 to length num with str2 (default

  LTRIM(str [,set]) 
   -- remove set from left
side of str (default spaces)
  NLS_INITCAP(str [,nl
s_val]) -- same as initcap f
or different languages
  NLS_LOWER(str [,nls_
val])  -- same as lower for
different languages
  REPLACE(str1, str2 [,str3]) -- r
eplaces str2 with str3 in str1
                 --
deletes str2 from str1 if str3 is omitted
  RPAD(str1, num [,str
(default spaces)
2])   -- right pad str1 to

length num with str2

  RTRIM(str [,set]) 
spaces)
     -- remove set from

right side of str (default

  SOUNDEX(str)    
    -- phonetic represen
tation of str
  SUBSTR(str, num2 [,n
um1])  -- substring of str,
starting with num2,
                 --
omitted)
num1 characters (to end of str if num1 is

  SUBSTRB(str, num2 [,
bytes
num1]) -- same as substr bu

t num1, num2 expressed in

  TRANSLATE(str, set1,
set2) -- replaces set1 in
str with set2
                 --
truncated
if set2 is longer than set1, it will be

  UPPER(str)     
    -- str with all lett
ers 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
            
character set
        -- chr_set1

default is the datbase

  HEXTORAW(str)   
  -- converts hex string va
lue to internal raw values
  RAWTOHEX(raw_val)   -- convert
s raw hex value to hex string value
  ROWIDTOCHAR(rowid) 
 -- converts rowid to 18 ch
aracter string format
  TO_CHAR(expr [,fmt])
fmt
 -- converts expr(date or n

umber) to format specified by

  TO_DATE(str [,fmt])
  -- converts string to dat
e
  TO_MULTI_BYTE(str)  -- convert
s single byte string to multi byte string
  TO_NUMBER(str [,fmt]) -- convert
s str to a number formatted by fmt
  TO_SINGLE_BYTE(str)
  -- converts multi byte st
ring to single byte string
                                                                               
  ********************************
***********************
************************

  * SQL Date Formats 
*
              

               

  ********************
***************
****************************

****************

                                                                               
  BC, B.C.    BC indicator                             
  AD, A.D.    AD indicator                             
  CC, SCC     Cent
ury Code (SCC includes space
or - sign)
  YYYY, SYYYY   4 digit year (SY
YYY 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 spel
led 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      seco
nds past midnight, 0 to 8639
9
  AM, A.M.    am indicator                             
  PM, P.M.    pm indicator                             
  any puctuation punc
tuation between format items
, as in 'DD/MM/YY'
  any text    text between format items   
  TH       conv
erts 1 to '1st', 2 to '2nd',
and so on
  SP       converts 1 to 'o
ne', 2 to 'two', and so on
  SPTH      converts 1 to 'F
IRST', 2 to 'SECOND', and so on
  FX       fill
exact : uses exact pattern
matching
  FM       fill mode : tog
gles suppression of blanks in output

Oracle 最常用功能函数经典汇总

   * SQL Group Function* s (num can be a column or expression)             (null values are ign* ore...
  • vebasan
  • vebasan
  • 2009年11月26日 15:55
  • 388

Oracle 最常用功能函数经典汇总 (zz)

来源:ChinaITLab 收集整理 2004-6-14 10:58:00    * SQL Group Function*  s (num can be a column or ex pressio...
  • pwlazy
  • pwlazy
  • 2005年05月28日 10:50
  • 2149

oracle 常用功能函数汇总

******************************************************************************* * SQL Group Function...
  • yhb72
  • yhb72
  • 2005年04月30日 20:08
  • 608

oracle 常用功能函数汇总********************

******************************************************************************* * SQL Group Function...
  • Maybeyond
  • Maybeyond
  • 2008年03月17日 20:13
  • 296

oracle分组汇总统计函数grouping

前两天同事问一个oracle使用grouping完成一个统计报表的功能,这个函数帅呆了。开发分组报表直接一个SQL就搞定。 grouping(columnA)函数的意思:当前行如果是由rollup汇总...
  • damenggege123
  • damenggege123
  • 2014年08月24日 10:49
  • 22802

ORACLE-下级部门的汇总给上级部门

create table dept_money (unit_id varchar(10),grade varchar(1),money number(10,2)); create table dept...
  • S630730701
  • S630730701
  • 2018年01月02日 13:42
  • 122

20090901_ 利用Oracle内置分析函数进行高效统计汇总

 分析函数是Oracle从8.1.6开始引入的一个新的概念,为我们分析数据提供了一种简单高效的处理方式。在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在...
  • outget1
  • outget1
  • 2009年09月01日 16:20
  • 1742

html5中常用的一些功能

1,context.createRadialGradient这个方法的使用 http://imatlas.com/posts/canvas-createradialgradient/ 2,html5...
  • xieshuhi_2013
  • xieshuhi_2013
  • 2016年06月23日 21:41
  • 286

干货丨史上最好记的神经网络结构速记表(经典资源,值得收藏)

原文THE NEURAL NETWORK ZOO,出自Asimov人工智能研究所。 本文为雷锋网原创文章,如需转载请自行联系授权。 新的神经网络结构不断涌现,我们很难一一掌握。哪怕一开始只...
  • R1uNW1W
  • R1uNW1W
  • 2017年10月23日 00:00
  • 128

经典SQL语句汇总

1、  存在这样一个简单的表Test (ID,Name,Code),其中ID是主键PK,写一条SQL 语句实现 : 获得一个记录集,          该记录集包括Test中全部的字段,按记录的...
  • GeneralXCK
  • GeneralXCK
  • 2007年08月15日 14:30
  • 523
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ZT: Oracle 最常用功能函数经典汇总
举报原因:
原因补充:

(最多只允许输入30个字)