|
|
最初由 lfree 发布
[B]这个主要是讲什么的？ [/B]
¢HAPTER 1 ¢OMMON oRACLE FUNCTIONS: a FUNCTION rEVIEW. . . . . . . 1
¢ALLING sIMPLE sQl FUNCTIONS . . . . . . . . . . . . . . . . . . 3
nUMERIC FUNCTIONS. . . . . . . . . . . . . . . . . . . . . . . . . 4
¢OMMON nUMERICAL mANIPULATION FUNCTIONS . . . . . . . 4
nEAR ffALUE FUNCTIONS. . . . . . . . . . . . . . . . . . . . . 7
nULL ffALUE FUNCTION . . . . . . . . . . . . . . . . . . . . . 10
lOG AND eXPONENTIAL FUNCTIONS . . . . . . . . . . . . . . 12
oRDINARY tRIGONOMETRY FUNCTIONS. . . . . . . . . . . . . 14
HYPERBOLIC tRIG FUNCTIONS . . . . . . . . . . . . . . . . . 16
sTRING FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . . 18
tHE instr FUNCTION . . . . . . . . . . . . . . . . . . . . 18
tHE sUbstr FUNCTION . . . . . . . . . . . . . . . . . . . 20
tHE rePla¢e FUNCTION . . . . . . . . . . . . . . . . . . 23
tHE trim FUNCTION . . . . . . . . . . . . . . . . . . . . . 24
dATE FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . 27
¢HAPTER 2 rEPORTING tOOLS IN oRACLE’S sQl*PLUS . . . . . . . . . . . . 31
¢olUmn . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
FORMATTING nUMBERS. . . . . . . . . . . . . . . . . . . . . . . 35
sCRIPTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
FORMATTING dATES. . . . . . . . . . . . . . . . . . . . . . . . . 41
breaK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
¢omPUte . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
rEMARKS IN sCRIPTS . . . . . . . . . . . . . . . . . . . . . . . . 48
ttitle AND btitle . . . . . . . . . . . . . . . . . . . . . . 49
rEFERENCES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
V
¢HAPTER 3 tHE aNALYTICAL FUNCTIONS IN oRACLE
(aNALYTICAL FUNCTIONS i). . . . . . . . . . . . . . . . . . . . . . . . . . . 53
fiHAT aRE aNALYTICAL FUNCTIONS? . . . . . . . . . . . . . . . . 53
tHE rOW-NUMBERING AND rANKING FUNCTIONS . . . . . . . . . 55
tHE oRDER IN fiHICH THE aNALYTICAL FUNCTION iS
PROCESSED IN THE sQl sTATEMENT . . . . . . . . . . . . . . . . 65
a sele¢t WITH JUST A From ¢LAUSE . . . . . . . . . . 66
a sele¢t WITH oRDERING . . . . . . . . . . . . . . . . . 66
a fiHere ¢LAUSE iS aDDED TO THE sTATEMENT . . . . . . 67
aN aNALYTICAL FUNCTION iS aDDED TO THE sTATEMENT . . . 67
a JOIN iS aDDED TO THE sTATEMENT . . . . . . . . . . . . . 68
tHE JOIN fiITHOUT THE aNALYTICAL FUNCTION . . . . . . 69
aDDING oRDERING TO A JOINED rESULT. . . . . . . . . . 70
aDDING AN aNALYTICAL FUNCTION TO A QUERY THAT
¢ONTAINS A JOIN (AND oTHER fiHere ¢ONDITIONS) . . 71
tHE oRDER WITH GroUP bffi iS PRESENT . . . . . . . . . . 72
aDDING oRDERING TO THE QUERY ¢ONTAINING THE
GroUP bffi. . . . . . . . . . . . . . . . . . . . . . . . . . 73
aDDING AN aNALYTICAL FUNCTION TO THE GroUP bffi
WITH order bffi ffERSION . . . . . . . . . . . . . . . . . . 74
¢HANGING THE FINAL oRDERING AFTER HAVING aDDED
AN aNALYTICAL FUNCTION. . . . . . . . . . . . . . . . . . . . 75
USING HaffinG WITH AN aNALYTICAL FUNCTION . . . . . . . 76
fiHERE THE aNALYTICAL FUNCTIONS ¢AN BE USED IN A
sQl sTATEMENT . . . . . . . . . . . . . . . . . . . . . . . . . . 77
mORE tHAN oNE aNALYTICAL FUNCTION mAY bE USED IN
A sINGLE sTATEMENT . . . . . . . . . . . . . . . . . . . . . . . . 78
tHE PERFORMANCE iMPLICATIONS OF USING aNALYTICAL
FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
nULLS AND aNALYTICAL FUNCTIONS . . . . . . . . . . . . . . . . . 86
PARTITIONING WITH Partition-bffi. . . . . . . . . . . . . . . 95
a PROBLEM THAT USES rofi-nUmber FOR A sOLUTION . . . . 96
ntile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
ranK, Per¢ent-ranK, AND ¢Ume-dist. . . . . . . 105
rEFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
VI
¢ONTENTS
¢HAPTER 4 aGGREGATE FUNCTIONS USED AS aNALYTICAL FUNCTIONS
(aNALYTICAL FUNCTIONS ii). . . . . . . . . . . . . . . . . . . . . . . . . . 111
tHE USE OF aGGREGATE FUNCTIONS IN sQl . . . . . . . . . . . 111
ratio-to-rePort. . . . . . . . . . . . . . . . . . . . . . 115
fiINDOWING sUBCLAUSES WITH PHYSICAL oFFSETS IN
aGGREGATE aNALYTICAL FUNCTIONS . . . . . . . . . . . . . . . . 120
aN eXPANDED eXAMPLE OF A PHYSICAL fiINDOW . . . . . . . . 127
dISPLAYING A rUNNING tOTAL USING sUm AS AN
aNALYTICAL FUNCTION . . . . . . . . . . . . . . . . . . . . . . . 131
UnboUnded FollofiinG . . . . . . . . . . . . . . . . 134
PARTITIONING aGGREGATE aNALYTICAL FUNCTIONS. . . . . . . . . 135
lOGICAL fiINDOWING . . . . . . . . . . . . . . . . . . . . . . . 137
tHE rOW ¢OMPARISON FUNCTIONS—lead AND laG . . . . 143
laG AND lead oPTIONS. . . . . . . . . . . . . . . . . . 146
¢HAPTER 5 tHE USE OF aNALYTICAL FUNCTIONS IN rEPORTING
(aNALYTICAL FUNCTIONS iii) . . . . . . . . . . . . . . . . . . . . . . . . . 149
GroUP bffi . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
GROUPING AT mULTIPLE lEVELS . . . . . . . . . . . . . . . . . . 155
rollUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
¢Ube. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
GroUPinG WITH rollUP AND ¢Ube . . . . . . . . . . . 162
¢HAPTER 6 tHE model OR sPreadsHeet PREDICATE IN
oRACLE’S sQl. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
tHE bASIC model ¢LAUSE . . . . . . . . . . . . . . . . . . . 166
rULE 1. tHE rESULT sET . . . . . . . . . . . . . . . . . . . 169
rULE 2. Partition bffi. . . . . . . . . . . . . . . . . . 169
rULE 3. dimension bffi . . . . . . . . . . . . . . . . . 170
rULE 4. measUres . . . . . . . . . . . . . . . . . . . . 170
rUles THAT USE oTHER ¢OLUMNS . . . . . . . . . . . . . . . 174
rUles THAT USE sEVERAL oTHER rOWS TO ¢OMPUTE
nEW rOWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
retUrn UPdated rofis . . . . . . . . . . . . . . . . . 183
USING ¢OMPARISON oPERATORS ON THE lHs . . . . . . . . . . 184
aDDING A sUMMATION rOW—USING THE rHs TO
GENERATE nEW rOWS USING aGGREGATE dATA . . . . . . . . . 186
sUMMING WITHIN A PARTITION . . . . . . . . . . . . . . . . . . 189
VII
¢ONTENTS
aGGREGATION ON THE rHs WITH ¢ONDITIONS ON THE
aGGREGATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
rEVISITING ¢ff WITH ffALUE oFFSETS—USING mULTIPLE
measUres ffALUES . . . . . . . . . . . . . . . . . . . . . . 193
oRDERING OF THE rHs . . . . . . . . . . . . . . . . . . . . . . 198
aUtomati¢ VERSUS seQUential order . . . . . . . 202
tHE For ¢LAUSE, UPdate, AND UPsert . . . . . . . . . 206
iTERATION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
a sQUARE rOOT iTERATION eXAMPLE . . . . . . . . . . . . 214
rEFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
¢HAPTER 7 rEGULAR eXPRESSIONS: sTRING sEARCHING AND
oRACLE 10G. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
a sIMPLE tABLE TO iLLUSTRATE AN re . . . . . . . . . . . . . . 225
reGeflP-instr. . . . . . . . . . . . . . . . . . . . . . . . 226
a sIMPLE re USING reGeflP-instr . . . . . . . . . 230
mETACHARACTERS . . . . . . . . . . . . . . . . . . . . . . . . . 231
bRACKETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
rANGES (mINUS sIGNS) . . . . . . . . . . . . . . . . . . . . . . 239
reGeflP-liKe . . . . . . . . . . . . . . . . . . . . . . . . 239
nEGATING ¢ARETS . . . . . . . . . . . . . . . . . . . . . . . . . 241
bRACKETED sPECIAL ¢LASSES . . . . . . . . . . . . . . . . . . . 243
oTHER bRACKETED ¢LASSES. . . . . . . . . . . . . . . . . . 246
tHE aLTERNATION oPERATOR. . . . . . . . . . . . . . . . . . . . 247
rEPETITION oPERATORS—AKA “QUANTIFIERS” . . . . . . . . . . 248
mORE aDVANCED QUANTIFIER rEPEAT oPERATOR
mETACHARACTERS—*, $, AND ? . . . . . . . . . . . . . . . . . 251
reGeflP-sUbstr . . . . . . . . . . . . . . . . . . . . . . 253
eMPTY sTRINGS AND THE ? rEPETITION ¢HARACTER . . . . . 258
reGeflt-rePla¢e . . . . . . . . . . . . . . . . . . . . . 259
GROUPING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
tHE bACKSLASH (\) . . . . . . . . . . . . . . . . . . . . . . . . 262
tHE bACKSLASH AS AN eSCAPE ¢HARACTER . . . . . . . . . 263
aLTERNATIVE QUOTING mECHANISM IN oRACLE 10G. . . . . . 264
bACKREFERENCE. . . . . . . . . . . . . . . . . . . . . . . . 265
rEFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
VIII
¢ONTENTS
¢HAPTER 8 ¢OLLECTION AND oo sQl IN oRACLE . . . . . . . . . . . . . . 269
aSSOCIATIVE aRRAYS. . . . . . . . . . . . . . . . . . . . . . . . 270
tHE obJe¢t tffiPe—¢OLUMN oBJECTS . . . . . . . . . . . 273
¢reate A table WITH THE ¢OLUMN tYPE IN iT . . . . 274
insert ffALUES INTO A tABLE WITH THE ¢OLUMN
tYPE IN iT . . . . . . . . . . . . . . . . . . . . . . . . . . 275
dISPLAY THE nEW tABLE (sele¢t * AND sele¢t
BY ¢OLUMN nAME). . . . . . . . . . . . . . . . . . . . . . 275
¢olUmn FORMATTING IN sele¢t . . . . . . . . . . . 277
sele¢tING oNLY oNE ¢OLUMN IN THE ¢OMPOSITE. . . . 277
sele¢t WITH A fiHere ¢LAUSE . . . . . . . . . . . . 278
USING UPdate WITH tffiPeED ¢OLUMNS. . . . . . . . . 278
¢REATE rOW oBJECTS—reF tffiPe. . . . . . . . . . . . . . 279
lOADING THE “ROW OBJECT” tABLE . . . . . . . . . . . . . . 281
UPdate dATA IN A tABLE OF rOW oBJECTS . . . . . . . . 283
¢reate A tABLE THAT rEFERENCES oUR rOW oBJECTS. . 284
insert ffALUES INTO A tABLE THAT ¢ONTAINS rOW
oBJECTS (t¢ro). . . . . . . . . . . . . . . . . . . . . . . 284
UPdate A tABLE THAT ¢ONTAINS rOW oBJECTS
(t¢ro) . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
sele¢t FROM THE t¢ro—sEEING rOW
aDDRESSES . . . . . . . . . . . . . . . . . . . . . . . . . . 286
dereF (dEREFERENCE) THE rOW aDDRESSES. . . . . 286
oNE-STEP insertS INTO A t¢ro . . . . . . . . . . . . . 287
sele¢tING iNDIVIDUAL ¢OLUMNS IN t¢roS. . . . . . . 288
dELETING rEFERENCED rOWS. . . . . . . . . . . . . . . . . 289
tHE rOW oBJECT tABLE AND THE ffalUe FUNCTION . . . 291
¢REATING USER-DEFINED FUNCTIONS FOR ¢OLUMN
oBJECTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
ffarraffiS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
¢reate tffiPe FOR ffarraffiS . . . . . . . . . . . . . 299
¢reate table WITH A ffarraffi . . . . . . . . . . . 300
lOADING A tABLE WITH A ffarraffi IN iT—insert
ffalUeS WITH ¢ONSTANTS . . . . . . . . . . . . . . . . . 301
mANIPULATING THE ffarraffi . . . . . . . . . . . . . . . . 302
tHE table FUNCTION. . . . . . . . . . . . . . . . . 303
tHE ffarraffi sELF-JOIN . . . . . . . . . . . . . . . . 305
IX
¢ONTENTS
tHE tHe AND ffalUe FUNCTIONS . . . . . . . . . . 306
tHE ¢ast FUNCTION . . . . . . . . . . . . . . . . . . 308
USING Pl⁄sQl TO ¢REATE FUNCTIONS TO
aCCESS eLEMENTS . . . . . . . . . . . . . . . . . . . . 311
¢REATING USER-DEFINED FUNCTIONS FOR ffarraffiS. . 320
nESTED tABLES . . . . . . . . . . . . . . . . . . . . . . . . . . 324
sUMMARY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
rEFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
¢HAPTER 9 sQl AND flml. . . . . . . . . . . . . . . . . . . . . . . . . . . 337
fiHAT iS flml? . . . . . . . . . . . . . . . . . . . . . . . . . . 338
dISPLAYING flml IN A bROWSER . . . . . . . . . . . . . . . . . 342
sQlTOflml. . . . . . . . . . . . . . . . . . . . . . . . . . . 344
GENERATING flml FROM “oRDINARY” tABLES . . . . . . . . 344
flmlTOsQl. . . . . . . . . . . . . . . . . . . . . . . . . . . 347
rEFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
aPPENDIX a sTRING FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . 357
aPPENDIX b sTATISTICAL FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . 371
iNDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 |
|
楼主: claymail
IP卡
狗仔卡
发表于 2006-7-14 09:24
显身卡
楼主
