Typical Summary of Oracle's most common functions and functions

Source: Internet
Author: User
Tags sql error sql error code

[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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.