Typical Summary of Oracle's most common functions and functions

[Author: shuipi resending from: csdn clicks: 81 updated on:]
Decrease font to increase font size

* SQL group Function
*
S (Num can be a column or ex

Expression)

(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
Column
Internal Oracle format, used

For getting info about

-- 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
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
The user you are logged on

-- Useful in S
Electing information from Lo
W level sys tables

User
-- Returns
User name of the user you
Re logged on

Userenv ('option ')
-- Returns INF
Ormation about the user you
Are logged on

-- 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
*
Default Oracle Date and Time)

* (Functions return
*
An Oracle date unless otherw

ISE specified)

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

Add_months (DT, num)
-- Adds num months
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
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 versions ages

Nls_lower (STR [, NLS _
Val]) -- same as lower
Different Ages

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

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

-- Str2 in str1, starting at num1

-- (Num1, num2 default to 1)

Limit B (str1, str2 [, num1 [num2]

) -- Same as instr, byte values for num1, num2

Length (STR)
-- Number
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

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 between des Space
Or-sign)

Yyyy, syyyy 4 digit year (SY
YYY primary des 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
Year in prior or next century

Q quarter or year, 1 to 4
Mm Month-from 01 to 12
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, one to 366
Day of week spel
Led out, nine characters right padded

Dy day abbreviation
J #
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-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 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