Oracle PL/SQL攻略

目　錄

第1章　PL/SQL基礎(chǔ)　1
1.1　創(chuàng)建代碼塊　1
　問題　1
　解決方案　1
　原理分析　1
1.2　在SQL*Plus里執(zhí)行代碼塊　2
　問題　2
　解決方案　2
　原理分析　2
1.3　把代碼保存到腳本中　3
　問題　3
　解決方案　3
　原理分析　3
1.4　執(zhí)行腳本　4
　問題　4
　解決方案　4
　原理分析　4
1.5　接受用戶鍵盤輸入　5
　問題　5
　解決方案　5
　原理分析　5
1.6　在SQL*Plus里顯示結(jié)果　7
　問題　7
　解決方案　7
　原理分析　7
1.7　在代碼中添加注釋　8
　問題　8
　解決方案　8
　原理分析　8
1.8　引用代碼塊　9
　問題　9
　解決方案　9
　原理分析　9
1.9　從嵌套塊中引用變量　10
　問題　10
　解決方案　10
　原理分析　11
1.10　忽略替換變量　11
　問題　11
　解決方案1　11
　解決方案2　11
　原理分析　12
1.11　改變替換變量字符 12
　問題　12
　解決方案　13
　原理分析　13
1.12　創(chuàng)建匹配數(shù)據(jù)庫列類型的變量　13
　問題　13
　解決方案　13
　原理分析　14

第2章　基本的SQL操作　15
2.1　從數(shù)據(jù)庫檢索單行數(shù)據(jù)　15
　問題　15
　解決方案1　15
　解決方案2　16
　原理分析　17
2.2　限定列名與變量名　18
　問題　18
　解決方案　18
　原理分析　19
2.3　聲明與列類型匹配的變量類型　19
　問題　19
　解決方案　19
　原理分析　20
2.4　把查詢結(jié)果返回到PL/SQL記錄中　20
　問題　20
　解決方案　20
　原理分析　21
2.5　創(chuàng)建自定義記錄來接收查詢結(jié)果　21
　問題　21
　解決方案　22
　原理分析　22
2.6　循環(huán)遍歷查詢的行結(jié)果　23
　問題　23
　解決方案1　23
　解決方案2　23
　原理分析　24
2.7　獲取環(huán)境與會話信息　24
　問題　24
　解決方案　24
　原理分析　25
2.8　格式化查詢結(jié)果　27
　問題　27
　解決方案　27
　原理分析　28
2.9　更新查詢返回的結(jié)果行　28
　問題　28
　解決方案　28
　原理分析　29
2.10　更新游標(biāo)返回的行結(jié)果　30
　問題　30
　解決方案　30
　原理分析　31
2.11　刪除游標(biāo)返回的行結(jié)果　31
　問題　31
　解決方案　31
　原理分析　32
2.12　執(zhí)行事務(wù)　32
　問題　32
　解決方案　32
　原理分析　33
2.13　確保相同的數(shù)據(jù)對多個查詢“可見”　34
　問題　34
　解決方案　34
　原理分析　34
2.14　從事務(wù)中執(zhí)行另一個獨立事務(wù)　35
　問題　35
　解決方案　35
　原理分析　36
2.15　找到并刪除重復(fù)行數(shù)據(jù)　37
　問題　37
　解決方案　37
　原理分析　38

第3章　循環(huán)與邏輯　39
3.1 選擇執(zhí)行代碼的時機　39
　問題　39
　解決方案　39
　原理分析　40
3.2　兩個互斥條件中的抉擇　40
　問題　40
　解決方案　40
　原理分析　41
3.3　多個互斥條件求值　41
　問題　41
　解決方案1　41
　解決方案2　42
　原理分析　43
3.4　一個有著多個輸出結(jié)果的表達式　44
　問題　44
　解決方案　44
　原理分析　45
3.5　循環(huán)直至滿足指定條件　45
　問題　45
　解決方案　45
　原理分析　46
3.6　遍歷游標(biāo)結(jié)果直到所有行結(jié)果都被返回　46
　問題　46
　解決方案　46
　原理分析　47
3.7　循環(huán)直到條件為FALSE　47
　問題　47
　解決方案　47
　原理分析　48
3.8　跳過當(dāng)前循環(huán)迭代　48
　問題　48
　解決方案　48
　原理分析　49
3.9　迭代固定次數(shù)　50
　問題　50
　解決方案　50
　原理分析　50
3.10　逆向迭代　51
　問題　51
　解決方案　51
　原理分析　51
3.11　指定其他循環(huán)增量　52
　問題　52
　解決方案　52
　原理分析　52
3.12　使用奇數(shù)作為級數(shù)進行遍歷　52
　問題　52
　解決方案　53
　原理分析　53
3.13　退出外層循環(huán)　53
　問題　53
　解決方案　53
　原理分析　54
3.14　跳轉(zhuǎn)到代碼中的指定位置　55
　問題　55
　解決方案　55
　原理分析　56

第4章　函數(shù)、過程與包　57
4.1　創(chuàng)建存儲函數(shù)　57
　問題　57
　解決方案　57
　原理分析　58
4.2　從查詢中執(zhí)行存儲函數(shù)　60
　問題　60
　解決方案　61
　原理分析　61
4.3　優(yōu)化對于某一輸入總是返回相同結(jié)果的函數(shù)　62
　問題　62
　解決方案　62
　原理分析　62
4.4　創(chuàng)建存儲過程　63
　問題　63
　解決方案　63
　原理分析　64
4.5　執(zhí)行存儲過程　64
　問題　64
　解決方案　65
　原理分析　65
4.6　在過程或代碼塊中創(chuàng)建函數(shù)　66
　問題　66
　解決方案　66
　原理分析　67
4.7　按名稱傳遞參數(shù)　67
　問題　67
　解決方案　67
　原理分析　67
4.8　設(shè)置默認參數(shù)值　68
　問題　68
　解決方案　68
　原理分析　68
4.9　組織相關(guān)程序形成一個獨立單元　69
　問題　69
　解決方案　69
　原理分析　70
4.10　為包編寫初始化代碼　71
　問題　71
　解決方案　71
　原理分析　72
4.11　授予存儲程序的創(chuàng)建與執(zhí)行權(quán)限　73
　問題　73
　解決方案　73
　原理分析　73
4.12　執(zhí)行包中的過程和函數(shù)　73
　問題　73
　解決方案　73
　原理分析　74
4.13　為存儲程序創(chuàng)建公有名稱　74
　問題　74
　解決方案　74
　原理分析　75
4.14　依次執(zhí)行包程序　75
　問題　75
　解決方案　75
　原理分析　76
4.15　使用失敗標(biāo)志　77
　問題　77
　解決方案　77
　原理分析　78
4.16　將數(shù)據(jù)訪問限制在包內(nèi)　79
　問題　79
　解決方案　79
　原理分析　79
4.17　在你的權(quán)限集合下運行存儲代碼　80
　問題　80
　解決方案　80
　原理分析　80
4.18　在一個函數(shù)中接受多個參數(shù)集合　81
　問題　81
　解決方案　81
　原理分析　81
4.19　列舉模式中的函數(shù)、過程和包　82
　問題　82
　解決方案　82
　原理分析　82
4.20　查看存儲程序源代碼　82
　問題　82
　解決方案　82
　原理分析　83

第5章　觸發(fā)器　85
5.1　自動生成列值　85
　問題　85
　解決方案　85
　原理分析　86
5.2　保持關(guān)系數(shù)據(jù)同步　87
　問題　87
　解決方案　87
　原理分析　88
5.3　響應(yīng)表的特定列更新　89
　問題　89
　解決方案　89
　原理分析　89
5.4　可更新的視圖　90
　問題　90
　解決方案　90
　原理分析　92
5.5　改變應(yīng)用程序的功能　92
　問題　92
　解決方案　93
　原理分析　93
5.6　驗證輸入數(shù)據(jù)　94
　問題　94
　解決方案　95
　原理分析　95
5.7　修正輸入數(shù)據(jù)　95
　問題　95
　解決方案　95
　原理分析　96
5.8　替換列值　96
　問題　96
　解決方案　96
　原理分析　97
5.9　基于系統(tǒng)事件的觸發(fā)器　98
　問題　98
　解決方案　98
　原理分析　99
5.10　與模式事件相關(guān)的觸發(fā)器　100
　問題　100
　解決方案　100
　原理分析　101
5.11　同一事件激活兩個觸發(fā)器　102
　問題　102
　解決方案　102
　原理分析　103
5.12　創(chuàng)建響應(yīng)多個事件的觸發(fā)器　103
　問題　103
　解決方案　103
　原理分析　105
5.13　創(chuàng)建禁用狀態(tài)的觸發(fā)器106
　問題　106
　解決方案　106
　原理分析　107

第6章　類型轉(zhuǎn)換　108
6.1　把字符串轉(zhuǎn)換成數(shù)字　108
　問題　108
　解決方案　108
　原理分析　109
6.2　把字符串轉(zhuǎn)換成日期　109
　問題　109
　解決方案　109
　原理分析　110
6.3　把數(shù)字轉(zhuǎn)換成字符串　111
　問題　111
　解決方案　111
　原理分析　111
6.4　把日期轉(zhuǎn)換成字符串　112
　問題　112
　解決方案　112
　原理分析　113
6.5　把字符串轉(zhuǎn)換成時間戳　115
　問題　115
　解決方案　115
　原理分析　116
6.6　利用ANSI兼容的方式進行格式轉(zhuǎn)換　116
　問題　116
　解決方案　116
　原理分析　117
6.7　PLS_INTEGER與NUMBER之間的隱式轉(zhuǎn)換　118
　問題　118
　解決方案　118
　原理分析　119

第7章　數(shù)字、字符串和日期　120
7.1　字符串拼接　120
　問題　120
　解決方案　120
　原理分析　121
7.2　為日期增加天數(shù)　121
　問題　121
　解決方案　121
　原理分析　121
7.3　為日期增加月數(shù)　122
　問題　122
　解決方案　122
　原理分析　123
7.4　為日期增加年數(shù)　123
　問題　123
　解決方案　123
　原理分析　124
7.5　獲取兩個日期之間的間隔天數(shù)　125
　問題　125
　解決方案　125
　原理分析　125
7.6　為日期增加小時數(shù)、分鐘數(shù)、秒數(shù)或天數(shù)　126
　問題　126
　解決方案　126
　原理分析　127
7.7　返回指定月份的第一天　129
　問題　129
　解決方案　129
　原理分析　129
7.8　返回月份的最后一天　130
　問題　130
　解決方案　130
　原理分析　130
7.9　數(shù)字的四舍五入　130
　問題　130
　解決方案　130
　原理分析　131
7.10　對日期時間值進行四舍五入　131
　問題　131
　解決方案　131
　原理分析　131
7.11　以毫秒單位來跟蹤時間　132
　問題　132
　解決方案　132
　原理分析　133
7.12　把時區(qū)與日期和時間關(guān)聯(lián)　133
　問題　133
　解決方案　133
　原理分析　133
7.13　利用樣式在字符串中搜索　134
　問題　134
　解決方案　134
　原理分析　134
7.14　確定指定樣式在字符串中的位置　135
　問題　135
　解決方案　135
　原理分析　136
7.15　字符串的查找與替換　137
　問題　137
　解決方案　137
　原理分析　138

第8章　動態(tài)SQL　139
8.1　執(zhí)行編譯時無法確定的單行查詢　139
　問題　139
　解決方案1　139
　解決方案2　140
　原理分析1　141
　原理分析2　142
8.2　執(zhí)行編譯時無法確定的多行查詢　142
　問題　142
　解決方案1　143
　解決方案2　143
　原理分析　144
8.3　編寫動態(tài)INSERT語句　145
　問題　145
　解決方案1　145
　解決方案2　146
　原理分析　147
8.4　編寫動態(tài)更新語句　148
　問題　148
　解決方案　148
　原理分析　151
8.5　編寫動態(tài)刪除語句　151
　問題　151
　解決方案　151
　原理分析　152
8.6　把動態(tài)查詢結(jié)果數(shù)據(jù)放入記錄中　153
　問題　153
　解決方案　153
　原理分析　154
8.7　執(zhí)行動態(tài)PL/SQL塊　154
　問題　154
　解決方案1　154
　解決方案2　155
　原理分析　155
8.8　運行時創(chuàng)建表　156
　問題　156
　解決方案　156
　原理分析　156
8.9　運行時修改表　157
　問題　157
　解決方案　157
　原理分析　157
8.10　查找出包含特殊字段值的所有表　158
　問題　158
　解決方案　158
　原理分析　160
8.11　在大對象中保存動態(tài)SQL　161
　問題　161
　解決方案1　161
　解決方案2　162
　原理分析　162
8.12　向動態(tài)SQL傳遞NULL值　163
　問題　163
　解決方案　163
　原理分析　164
8.13　在DMBS_SQL與原生動態(tài)SQL間切換　164
　問題　164
　解決方案　164
　原理分析　166
8.14　防止SQL注入攻擊166
　問題　166
　解決方案　166
　原理分析　167

第9章　異?！?69
9.1　捕獲異?！?69
　問題　169
　解決方案　169
　原理分析　171
9.2　捕獲未知異?！?73
　問題　173
　解決方案　173
　原理分析　175
9.3　創(chuàng)建并拋出命名的自定義異?！?75
　問題　175
　解決方案　175
　原理分析　177
9.4　判斷OTHERS處理程序中發(fā)生了哪個錯誤　177
　問題　177
　解決方案　177
　原理分析　179
9.5　拋出用戶定義異常　180
　問題　180
　解決方案　181
　原理分析　181
9.6　在異常拋出后重定向控制權(quán)　182
　問題　182
　解決方案　183
　原理分析　184
9.7　拋出異常并繼續(xù)處理　184
　問題　184
　解決方案　184
　原理分析　185
9.8　將無名異常與錯誤號關(guān)聯(lián)　185
　問題　185
　解決方案　186
　原理分析　186
9.9　追蹤異常源　187
　問題　187
　解決方案　187
　原理分析　189
9.10　顯示PL/SQL編譯器警告　190
　問題　190
　解決方案　190
　原理分析　191

第10章　PL/SQL集合與記錄　193
10.1　創(chuàng)建并訪問VARRAY　193
　問題　193
　解決方案　193
　原理分析　194
10.2　創(chuàng)建并訪問索引表　194
　問題　194
　解決方案　194
　原理分析　195
10.3　創(chuàng)建簡單的記錄　195
　問題　195
　解決方案　195
　原理分析　195
10.4　創(chuàng)建并訪問記錄集合　196
　問題　196
　解決方案　196
　原理分析　196
10.5　創(chuàng)建并訪問哈希數(shù)組集合　197
　問題　197
　解決方案　197
　原理分析　198
10.6　創(chuàng)建并訪問復(fù)雜的集合　198
　問題　198
　解決方案　198
　原理分析　200
10.7　將集合作為調(diào)用參數(shù)使用　201
　問題　201
　解決方案　201
　原理分析　202
10.8　將集合作為返回參數(shù)使用　202
　問題　202
　解決方案　202
　原理分析　203
10.9　計算集合元素個數(shù)　203
　問題　203
　解決方案　204
　原理分析　204
10.10　從集合中刪除記錄　204
　問題　204
　解決方案　205
　原理分析　205
10.11　檢查集合中是否存在指定的元素　206
　問題　206
　解決方案　206
　原理分析　207
10.12　增加集合大小　207
　問題　207
　解決方案　207
　原理分析　208
10.13　集合導(dǎo)航　208
　問題　208
　解決方案　208
　原理分析　209
10.14　對集合進行修剪　209
　問題　209
　解決方案　209
　原理分析　210

第11章　自動化日常任務(wù)　211
11.1　調(diào)度重復(fù)性作業(yè)　211
　問題　211
　解決方案　211
　原理分析　211
11.2　用電子郵件發(fā)送調(diào)度作業(yè)的輸出　212
　問題　212
　解決方案　212
　原理分析　213
11.3　用電子郵件發(fā)送作業(yè)狀態(tài)通知　213
　問題　213
　解決方案　213
　原理分析　213
11.4　按固定時間間隔刷新物化視圖　214
　問題　214
　解決方案　214
　原理分析　216
11.5　與遠程數(shù)據(jù)源保持數(shù)據(jù)同步　216
　問題　216
　解決方案　217
　原理分析　218
11.6　計劃作業(yè)鏈　218
　問題　218
　解決方案　218
　原理分析　221

第12章　Oracle SQL Developer　223
12.1　創(chuàng)建標(biāo)準(zhǔn)的授權(quán)數(shù)據(jù)庫連接　223
　問題　223
　解決方案　223
　原理分析　224
12.2　獲取表的信息　225
　問題　225
　解決方案　225
　原理分析　226
12.3　啟用顯示輸出功能　227
　問題　227
　解決方案　227
　原理分析　227
12.4　編寫并執(zhí)行PL/SQL　228
　問題　228
　解決方案　228
　原理分析　229
12.5　創(chuàng)建并執(zhí)行腳本　231
　問題　231
　解決方案　231
　原理分析　232
12.6　將用戶的輸入值當(dāng)作替代變量　233
　問題　233
　解決方案　233
　原理分析　233
12.7　保存可以快速訪問的代碼片段　234
　問題　234
　解決方案　234
　原理分析　234
12.8　創(chuàng)建函數(shù)　236
　問題　236
　解決方案　236
　原理分析　237
12.9　創(chuàng)建存儲過程　239
　問題　239
　解決方案　239
　原理分析　240
12.10　創(chuàng)建包說明和包體　243
　問題　243
　解決方案　243
　原理分析　243
12.11　創(chuàng)建觸發(fā)器　247
　問題　247
　解決方案　247
　原理分析　247
12.12　調(diào)試存儲代碼　250
　問題　250
　解決方案　250
　原理分析　250
12.13　在導(dǎo)航器中編譯代碼　252
　問題　252
　解決方案　253
　原理分析　253

第13章　性能分析與改進　254
13.1　安裝DBMS_PROFILER　254
　問題　254
　解決方案　254
　原理分析　255
13.2　找出瓶頸　255
　問題　255
　解決方案　255
　原理分析　256
13.3　加速讀寫循環(huán)　257
　問題　257
　解決方案　257
　原理分析　258
13.4　將較大集合或復(fù)雜集合作為OUT參數(shù)使用　259
　問題　259
　解決方案　259
　原理分析　260
13.5　優(yōu)化計算密集型代碼　260
　問題　260
　解決方案　261
　原理分析　261
13.6　改善運行時的初始化時間　262
　問題　262
　解決方案　262
　原理分析　262

第14章　在Web中使用PL/SQL　263
14.1　在Web上運行PL/SQL過程　263
　問題　263
　解決方案　263
　原理分析　264
14.2　創(chuàng)建生成HTML頁面的公用過程集合　265
　問題　265
　解決方案　265
　原理分析　266
14.3　創(chuàng)建輸入表單　267
　問題　267
　解決方案　267
　原理分析　268
14.4　用PL/SQL創(chuàng)建基于Web的報表　270
　問題　270
　解決方案　270
　原理分析　272
14.5　顯示表中的數(shù)據(jù)　273
　問題　273
　解決方案　273
　原理分析　274
14.6　通過數(shù)據(jù)庫查詢創(chuàng)建Web表單下拉列表　274
　問題　274
　解決方案　274
　原理分析　275
14.7　創(chuàng)建可排序Web報表　276
　問題　276
　解決方案　276
　原理分析　278
14.8　在Web頁面之間傳遞數(shù)據(jù)　279
　問題　279
　解決方案　279
　原理分析　280
14.9　查看調(diào)試Web程序時發(fā)生的錯誤信息　280
　問題　280
　解決方案　281
　原理分析　281
14.10　用PL/SQL生成JavaScript　282
　問題　282
　解決方案　282
　原理分析　283
14.11　生成XML數(shù)據(jù)　284
　問題　284
　解決方案　284
　原理分析　285
14.12　用Ajax創(chuàng)建一個輸入表單　286
　問題　286
　解決方案　286
　原理分析　287

第15章　數(shù)據(jù)庫中的Java應(yīng)用　289
15.1　創(chuàng)建Java數(shù)據(jù)庫類　289
　問題　289
　解決方案　289
　原理分析　290
15.2　把Java類裝載到數(shù)據(jù)庫中　291
　問題　291
　原理分析　293
15.3　把編譯后的Java類裝載到數(shù)據(jù)庫中　293
　問題　293
　解決方案　293
　原理分析　294
15.4　把Java類聲明為存儲過程　294
　問題　294
　解決方案　295
　原理分析　295
15.5　執(zhí)行Java存儲過程　295
　問題　295
　解決方案　295
　原理分析　296
15.6　從PL/SQL代碼中調(diào)用Java存儲過程　296
　問題　296
　解決方案　296
　原理分析　297
15.7　在PL/SQL與Java之間傳遞參數(shù)　298
　問題　298
　解決方案　298
　原理分析　299
15.8　創(chuàng)建與調(diào)用Java數(shù)據(jù)庫函數(shù)　300
　問題　300
　解決方案　300
　原理分析　301
15.9　創(chuàng)建Java數(shù)據(jù)庫觸發(fā)器　301
　問題　301
　解決方案　301
　原理分析　303
15.10　從PL/SQL往Java中傳遞數(shù)據(jù)對象　303
　問題　303
　解決方案　303
　原理分析　305
15.11　把Java類嵌入PL/SQL包　306
　問題　306
　解決方案　306
　原理分析　307
15.12　向數(shù)據(jù)庫中裝載Java類庫　307
　問題　307
　解決方案　307
　原理分析　309
15.13　刪除Java類　309
　問題　309
　解決方案　310
　原理分析　310
15.14　用Java檢索數(shù)據(jù)庫元數(shù)據(jù)　310
　問題　310
　解決方案　310
　原理分析　311
15.15　查詢數(shù)據(jù)庫以解決Java編譯問題　311
　問題　311
　解決方案　311
　原理分析　312

第16章　從JDBC、HTTP、Groovy和Jython中訪問PL/SQL　313
16.1　通過JDBC訪問PL/SQL存儲過程　313
　問題　313
　解決方案　313
　原理分析　315
16.2　從JDBC中訪問PL/SQL存儲函數(shù)　316
　問題　316
　解決方案　316
　原理分析　317
16.3　通過HTTP訪問PL/SQL過程　318
　問題　318
　解決方案　318
　原理分析　321
16.4　從Jython中訪問PL/SQL　322
　問題　322
　原理分析　324
16.5　從Groovy訪問PL/SQL　325
　問題　325
　解決方案　325
　原理分析　325

第17章　用utPLSQL進行單元測試　327
17.1　使用單元測試來測試PL/SQL代碼　327
　問題　327
　解決方案　327
　原理分析　329
17.2　安裝utPLSQL單元測試框架　329
　問題　329
　解決方案　329
　原理分析　330
17.3　構(gòu)建utPLSQL測試包　331
　問題　331
　解決方案　331
　原理分析　332
17.4　編寫utPLSQL單元測試過程　332
　問題　332
　解決方案　333
　原理分析　334
17.5　運行utPLSQL測試　335
　問題　335
　解決方案　335
　原理分析　338
17.6　構(gòu)建utPLSQL測試套件　338
　問題　338
　解決方案　338
　原理分析　338
17.7　運行utPLSQL測試套件　339
　問題　339
　解決方案　339
　原理分析　345
17.8　重新配置utPLSQL參數(shù)　345
　問題　345
　解決方案　346
　原理分析　346
17.9　將utPLSQL測試結(jié)果重定向到文件　348
　問題　348
　解決方案　348
　原理分析　348
17.10　用Ant自動運行PL/SQL和Java存儲過程的單元測試　349
　問題　349
　解決方案　350
　原理分析　351

索引　355