[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