Oracle常用函数

* 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

MIN([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 str1str2)

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)

IYYY4 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)

RRlast 2 digits of

year in prior or next century

Q quarter or year, 1 to 4             

MMmonth - from 01 to 12                 

MONTH month spelled out                     

MON month 3 letter abbreviation   

RMroman numeral for month             

WWweek of year, 1 to 53                 

IWISO 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                     

DDday of month, 1 to 31                 

DDD day of year, 1 to 366               

DAY day of week spel

led out, nine characters right padded

DYday abbreviation                           

J # of

days since Jan 1, 4712 BC

HH, HH12hour of day, 1 to 12             

HH24hour of day, 0 to 23                 

MIminute of hour, 0 to 59             

SSsecond 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 puctuationpunc

tuation between format items

, as in 'DD/MM/YY'

any texttext between format items   

THconv

erts 1 to '1st', 2 to '2nd',

and so on

SPconverts 1 to 'o

ne', 2 to 'two', and so on

SPTHconverts 1 to 'F

IRST', 2 to 'SECOND', and so on

FXfill

exact : uses exact pattern

matching

FMfill mode: tog

gles suppression of blanks in output