Oracle function Daquan 5 [resend]

60. AVG (distinct | all)
All indicates the average value for all values, while distinct only calculates the average value for different values.
Sqlwks> Create Table table3 (XM varchar (, Sal number (7,2 ));
The statement has been processed.
Sqlwks> insert into table3 values ('gao', 1111.11 );
Sqlwks> insert into table3 values ('gao', 1111.11 );
Sqlwks> insert into table3 values ('zhu', 5555.55 );
Sqlwks> commit;

SQL> select AVG (distinct Sal) from Gao. table3;

AVG (distinctsal)
----------------
3333.33

SQL> select AVG (all Sal) from Gao. table3;

AVG (allsal)
-----------
2592.59

61. Max (distinct | all)
For maximum value, "all" indicates the maximum value for all values, and "distinct" indicates the maximum value for different values. For the same value, only one time is used.
SQL> select max (distinct Sal) from Scott. EMP;

Max (distinctsal)
----------------
5000

62. Min (distinct | all)
Minimum value. "All" indicates minimum value for all values, and "distinct" indicates minimum value for different values. The same value is used only once.
SQL> select Min (all Sal) from Gao. table3;

Min (allsal)
-----------
1111.11

63. stddev (distinct | all)
Evaluate standard deviation. "All" indicates evaluate standard deviation for all values, and "distinct" indicates evaluate standard deviation for different values only.
SQL> select stddev (SAL) from Scott. EMP;

Stddev (SAL)
-----------
1182.5032

SQL> select stddev (distinct Sal) from Scott. EMP;

Stddev (distinctsal)
-------------------
1229.951

 

64. Variance (distinct | all)
Covariance

SQL> select variance (SAL) from Scott. EMP;

Variance (SAL)
-------------
1398313.9

65. Group
It is mainly used to calculate the number of a group.
SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno;

Deptno count (*) sum (SAL)
---------------------------
10 3 8750
20 5 10875
30 6 9400

 

66. Having
Grouping statistics plus restrictions
SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno having count (*)> = 5;

Deptno count (*) sum (SAL)
---------------------------
20 5 10875
30 6 9400
SQL> select deptno, count (*), sum (SAL) from Scott. EMP having count (*)> = 5 group by deptno;

Deptno count (*) sum (SAL)
---------------------------
20 5 10875
30 6 9400

67. Order
Used to sort and output the queried results.
SQL> select deptno, ename, Sal from Scott. EMP order by deptno, Sal DESC;

Deptno ename Sal
----------------------------
10 King 5000
10 Clark 2450
10 Miller 1300
20 Scott 3000
20 Ford 3000
20 Jones 2975
20 Adams 1100
20 Smith 800
30 Blake 2850
30 Allen 1600
30 Turner 1500
30 ward 1250
30 Martin 1250
30 James 950

* 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