Oracle PL/SQL攻略

目　录

第1章　PL/SQL基础　1
1.1　创建代码块　1
　问题　1
　解决方案　1
　原理分析　1
1.2　在SQL*Plus里执行代码块　2
　问题　2
　解决方案　2
　原理分析　2
1.3　把代码保存到脚本中　3
　问题　3
　解决方案　3
　原理分析　3
1.4　执行脚本　4
　问题　4
　解决方案　4
　原理分析　4
1.5　接受用户键盘输入　5
　问题　5
　解决方案　5
　原理分析　5
1.6　在SQL*Plus里显示结果　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　创建匹配数据库列类型的变量　13
　问题　13
　解决方案　13
　原理分析　14

第2章　基本的SQL操作　15
2.1　从数据库检索单行数据　15
　问题　15
　解决方案1　15
　解决方案2　16
　原理分析　17
2.2　限定列名与变量名　18
　问题　18
　解决方案　18
　原理分析　19
2.3　声明与列类型匹配的变量类型　19
　问题　19
　解决方案　19
　原理分析　20
2.4　把查询结果返回到PL/SQL记录中　20
　问题　20
　解决方案　20
　原理分析　21
2.5　创建自定义记录来接收查询结果　21
　问题　21
　解决方案　22
　原理分析　22
2.6　循环遍历查询的行结果　23
　问题　23
　解决方案1　23
　解决方案2　23
　原理分析　24
2.7　获取环境与会话信息　24
　问题　24
　解决方案　24
　原理分析　25
2.8　格式化查询结果　27
　问题　27
　解决方案　27
　原理分析　28
2.9　更新查询返回的结果行　28
　问题　28
　解决方案　28
　原理分析　29
2.10　更新游标返回的行结果　30
　问题　30
　解决方案　30
　原理分析　31
2.11　删除游标返回的行结果　31
　问题　31
　解决方案　31
　原理分析　32
2.12　执行事务　32
　问题　32
　解决方案　32
　原理分析　33
2.13　确保相同的数据对多个查询“可见”　34
　问题　34
　解决方案　34
　原理分析　34
2.14　从事务中执行另一个独立事务　35
　问题　35
　解决方案　35
　原理分析　36
2.15　找到并删除重复行数据　37
　问题　37
　解决方案　37
　原理分析　38

第3章　循环与逻辑　39
3.1 选择执行代码的时机　39
　问题　39
　解决方案　39
　原理分析　40
3.2　两个互斥条件中的抉择　40
　问题　40
　解决方案　40
　原理分析　41
3.3　多个互斥条件求值　41
　问题　41
　解决方案1　41
　解决方案2　42
　原理分析　43
3.4　一个有着多个输出结果的表达式　44
　问题　44
　解决方案　44
　原理分析　45
3.5　循环直至满足指定条件　45
　问题　45
　解决方案　45
　原理分析　46
3.6　遍历游标结果直到所有行结果都被返回　46
　问题　46
　解决方案　46
　原理分析　47
3.7　循环直到条件为FALSE　47
　问题　47
　解决方案　47
　原理分析　48
3.8　跳过当前循环迭代　48
　问题　48
　解决方案　48
　原理分析　49
3.9　迭代固定次数　50
　问题　50
　解决方案　50
　原理分析　50
3.10　逆向迭代　51
　问题　51
　解决方案　51
　原理分析　51
3.11　指定其他循环增量　52
　问题　52
　解决方案　52
　原理分析　52
3.12　使用奇数作为级数进行遍历　52
　问题　52
　解决方案　53
　原理分析　53
3.13　退出外层循环　53
　问题　53
　解决方案　53
　原理分析　54
3.14　跳转到代码中的指定位置　55
　问题　55
　解决方案　55
　原理分析　56

第4章　函数、过程与包　57
4.1　创建存储函数　57
　问题　57
　解决方案　57
　原理分析　58
4.2　从查询中执行存储函数　60
　问题　60
　解决方案　61
　原理分析　61
4.3　优化对于某一输入总是返回相同结果的函数　62
　问题　62
　解决方案　62
　原理分析　62
4.4　创建存储过程　63
　问题　63
　解决方案　63
　原理分析　64
4.5　执行存储过程　64
　问题　64
　解决方案　65
　原理分析　65
4.6　在过程或代码块中创建函数　66
　问题　66
　解决方案　66
　原理分析　67
4.7　按名称传递参数　67
　问题　67
　解决方案　67
　原理分析　67
4.8　设置默认参数值　68
　问题　68
　解决方案　68
　原理分析　68
4.9　组织相关程序形成一个独立单元　69
　问题　69
　解决方案　69
　原理分析　70
4.10　为包编写初始化代码　71
　问题　71
　解决方案　71
　原理分析　72
4.11　授予存储程序的创建与执行权限　73
　问题　73
　解决方案　73
　原理分析　73
4.12　执行包中的过程和函数　73
　问题　73
　解决方案　73
　原理分析　74
4.13　为存储程序创建公有名称　74
　问题　74
　解决方案　74
　原理分析　75
4.14　依次执行包程序　75
　问题　75
　解决方案　75
　原理分析　76
4.15　使用失败标志　77
　问题　77
　解决方案　77
　原理分析　78
4.16　将数据访问限制在包内　79
　问题　79
　解决方案　79
　原理分析　79
4.17　在你的权限集合下运行存储代码　80
　问题　80
　解决方案　80
　原理分析　80
4.18　在一个函数中接受多个参数集合　81
　问题　81
　解决方案　81
　原理分析　81
4.19　列举模式中的函数、过程和包　82
　问题　82
　解决方案　82
　原理分析　82
4.20　查看存储程序源代码　82
　问题　82
　解决方案　82
　原理分析　83

第5章　触发器　85
5.1　自动生成列值　85
　问题　85
　解决方案　85
　原理分析　86
5.2　保持关系数据同步　87
　问题　87
　解决方案　87
　原理分析　88
5.3　响应表的特定列更新　89
　问题　89
　解决方案　89
　原理分析　89
5.4　可更新的视图　90
　问题　90
　解决方案　90
　原理分析　92
5.5　改变应用程序的功能　92
　问题　92
　解决方案　93
　原理分析　93
5.6　验证输入数据　94
　问题　94
　解决方案　95
　原理分析　95
5.7　修正输入数据　95
　问题　95
　解决方案　95
　原理分析　96
5.8　替换列值　96
　问题　96
　解决方案　96
　原理分析　97
5.9　基于系统事件的触发器　98
　问题　98
　解决方案　98
　原理分析　99
5.10　与模式事件相关的触发器　100
　问题　100
　解决方案　100
　原理分析　101
5.11　同一事件激活两个触发器　102
　问题　102
　解决方案　102
　原理分析　103
5.12　创建响应多个事件的触发器　103
　问题　103
　解决方案　103
　原理分析　105
5.13　创建禁用状态的触发器106
　问题　106
　解决方案　106
　原理分析　107

第6章　类型转换　108
6.1　把字符串转换成数字　108
　问题　108
　解决方案　108
　原理分析　109
6.2　把字符串转换成日期　109
　问题　109
　解决方案　109
　原理分析　110
6.3　把数字转换成字符串　111
　问题　111
　解决方案　111
　原理分析　111
6.4　把日期转换成字符串　112
　问题　112
　解决方案　112
　原理分析　113
6.5　把字符串转换成时间戳　115
　问题　115
　解决方案　115
　原理分析　116
6.6　利用ANSI兼容的方式进行格式转换　116
　问题　116
　解决方案　116
　原理分析　117
6.7　PLS_INTEGER与NUMBER之间的隐式转换　118
　问题　118
　解决方案　118
　原理分析　119

第7章　数字、字符串和日期　120
7.1　字符串拼接　120
　问题　120
　解决方案　120
　原理分析　121
7.2　为日期增加天数　121
　问题　121
　解决方案　121
　原理分析　121
7.3　为日期增加月数　122
　问题　122
　解决方案　122
　原理分析　123
7.4　为日期增加年数　123
　问题　123
　解决方案　123
　原理分析　124
7.5　获取两个日期之间的间隔天数　125
　问题　125
　解决方案　125
　原理分析　125
7.6　为日期增加小时数、分钟数、秒数或天数　126
　问题　126
　解决方案　126
　原理分析　127
7.7　返回指定月份的第一天　129
　问题　129
　解决方案　129
　原理分析　129
7.8　返回月份的最后一天　130
　问题　130
　解决方案　130
　原理分析　130
7.9　数字的四舍五入　130
　问题　130
　解决方案　130
　原理分析　131
7.10　对日期时间值进行四舍五入　131
　问题　131
　解决方案　131
　原理分析　131
7.11　以毫秒单位来跟踪时间　132
　问题　132
　解决方案　132
　原理分析　133
7.12　把时区与日期和时间关联　133
　问题　133
　解决方案　133
　原理分析　133
7.13　利用样式在字符串中搜索　134
　问题　134
　解决方案　134
　原理分析　134
7.14　确定指定样式在字符串中的位置　135
　问题　135
　解决方案　135
　原理分析　136
7.15　字符串的查找与替换　137
　问题　137
　解决方案　137
　原理分析　138

第8章　动态SQL　139
8.1　执行编译时无法确定的单行查询　139
　问题　139
　解决方案1　139
　解决方案2　140
　原理分析1　141
　原理分析2　142
8.2　执行编译时无法确定的多行查询　142
　问题　142
　解决方案1　143
　解决方案2　143
　原理分析　144
8.3　编写动态INSERT语句　145
　问题　145
　解决方案1　145
　解决方案2　146
　原理分析　147
8.4　编写动态更新语句　148
　问题　148
　解决方案　148
　原理分析　151
8.5　编写动态删除语句　151
　问题　151
　解决方案　151
　原理分析　152
8.6　把动态查询结果数据放入记录中　153
　问题　153
　解决方案　153
　原理分析　154
8.7　执行动态PL/SQL块　154
　问题　154
　解决方案1　154
　解决方案2　155
　原理分析　155
8.8　运行时创建表　156
　问题　156
　解决方案　156
　原理分析　156
8.9　运行时修改表　157
　问题　157
　解决方案　157
　原理分析　157
8.10　查找出包含特殊字段值的所有表　158
　问题　158
　解决方案　158
　原理分析　160
8.11　在大对象中保存动态SQL　161
　问题　161
　解决方案1　161
　解决方案2　162
　原理分析　162
8.12　向动态SQL传递NULL值　163
　问题　163
　解决方案　163
　原理分析　164
8.13　在DMBS_SQL与原生动态SQL间切换　164
　问题　164
　解决方案　164
　原理分析　166
8.14　防止SQL注入攻击166
　问题　166
　解决方案　166
　原理分析　167

第9章　异常　169
9.1　捕获异常　169
　问题　169
　解决方案　169
　原理分析　171
9.2　捕获未知异常　173
　问题　173
　解决方案　173
　原理分析　175
9.3　创建并抛出命名的自定义异常　175
　问题　175
　解决方案　175
　原理分析　177
9.4　判断OTHERS处理程序中发生了哪个错误　177
　问题　177
　解决方案　177
　原理分析　179
9.5　抛出用户定义异常　180
　问题　180
　解决方案　181
　原理分析　181
9.6　在异常抛出后重定向控制权　182
　问题　182
　解决方案　183
　原理分析　184
9.7　抛出异常并继续处理　184
　问题　184
　解决方案　184
　原理分析　185
9.8　将无名异常与错误号关联　185
　问题　185
　解决方案　186
　原理分析　186
9.9　追踪异常源　187
　问题　187
　解决方案　187
　原理分析　189
9.10　显示PL/SQL编译器警告　190
　问题　190
　解决方案　190
　原理分析　191

第10章　PL/SQL集合与记录　193
10.1　创建并访问VARRAY　193
　问题　193
　解决方案　193
　原理分析　194
10.2　创建并访问索引表　194
　问题　194
　解决方案　194
　原理分析　195
10.3　创建简单的记录　195
　问题　195
　解决方案　195
　原理分析　195
10.4　创建并访问记录集合　196
　问题　196
　解决方案　196
　原理分析　196
10.5　创建并访问哈希数组集合　197
　问题　197
　解决方案　197
　原理分析　198
10.6　创建并访问复杂的集合　198
　问题　198
　解决方案　198
　原理分析　200
10.7　将集合作为调用参数使用　201
　问题　201
　解决方案　201
　原理分析　202
10.8　将集合作为返回参数使用　202
　问题　202
　解决方案　202
　原理分析　203
10.9　计算集合元素个数　203
　问题　203
　解决方案　204
　原理分析　204
10.10　从集合中删除记录　204
　问题　204
　解决方案　205
　原理分析　205
10.11　检查集合中是否存在指定的元素　206
　问题　206
　解决方案　206
　原理分析　207
10.12　增加集合大小　207
　问题　207
　解决方案　207
　原理分析　208
10.13　集合导航　208
　问题　208
　解决方案　208
　原理分析　209
10.14　对集合进行修剪　209
　问题　209
　解决方案　209
　原理分析　210

第11章　自动化日常任务　211
11.1　调度重复性作业　211
　问题　211
　解决方案　211
　原理分析　211
11.2　用电子邮件发送调度作业的输出　212
　问题　212
　解决方案　212
　原理分析　213
11.3　用电子邮件发送作业状态通知　213
　问题　213
　解决方案　213
　原理分析　213
11.4　按固定时间间隔刷新物化视图　214
　问题　214
　解决方案　214
　原理分析　216
11.5　与远程数据源保持数据同步　216
　问题　216
　解决方案　217
　原理分析　218
11.6　计划作业链　218
　问题　218
　解决方案　218
　原理分析　221

第12章　Oracle SQL Developer　223
12.1　创建标准的授权数据库连接　223
　问题　223
　解决方案　223
　原理分析　224
12.2　获取表的信息　225
　问题　225
　解决方案　225
　原理分析　226
12.3　启用显示输出功能　227
　问题　227
　解决方案　227
　原理分析　227
12.4　编写并执行PL/SQL　228
　问题　228
　解决方案　228
　原理分析　229
12.5　创建并执行脚本　231
　问题　231
　解决方案　231
　原理分析　232
12.6　将用户的输入值当作替代变量　233
　问题　233
　解决方案　233
　原理分析　233
12.7　保存可以快速访问的代码片段　234
　问题　234
　解决方案　234
　原理分析　234
12.8　创建函数　236
　问题　236
　解决方案　236
　原理分析　237
12.9　创建存储过程　239
　问题　239
　解决方案　239
　原理分析　240
12.10　创建包说明和包体　243
　问题　243
　解决方案　243
　原理分析　243
12.11　创建触发器　247
　问题　247
　解决方案　247
　原理分析　247
12.12　调试存储代码　250
　问题　250
　解决方案　250
　原理分析　250
12.13　在导航器中编译代码　252
　问题　252
　解决方案　253
　原理分析　253

第13章　性能分析与改进　254
13.1　安装DBMS_PROFILER　254
　问题　254
　解决方案　254
　原理分析　255
13.2　找出瓶颈　255
　问题　255
　解决方案　255
　原理分析　256
13.3　加速读写循环　257
　问题　257
　解决方案　257
　原理分析　258
13.4　将较大集合或复杂集合作为OUT参数使用　259
　问题　259
　解决方案　259
　原理分析　260
13.5　优化计算密集型代码　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　创建生成HTML页面的公用过程集合　265
　问题　265
　解决方案　265
　原理分析　266
14.3　创建输入表单　267
　问题　267
　解决方案　267
　原理分析　268
14.4　用PL/SQL创建基于Web的报表　270
　问题　270
　解决方案　270
　原理分析　272
14.5　显示表中的数据　273
　问题　273
　解决方案　273
　原理分析　274
14.6　通过数据库查询创建Web表单下拉列表　274
　问题　274
　解决方案　274
　原理分析　275
14.7　创建可排序Web报表　276
　问题　276
　解决方案　276
　原理分析　278
14.8　在Web页面之间传递数据　279
　问题　279
　解决方案　279
　原理分析　280
14.9　查看调试Web程序时发生的错误信息　280
　问题　280
　解决方案　281
　原理分析　281
14.10　用PL/SQL生成JavaScript　282
　问题　282
　解决方案　282
　原理分析　283
14.11　生成XML数据　284
　问题　284
　解决方案　284
　原理分析　285
14.12　用Ajax创建一个输入表单　286
　问题　286
　解决方案　286
　原理分析　287

第15章　数据库中的Java应用　289
15.1　创建Java数据库类　289
　问题　289
　解决方案　289
　原理分析　290
15.2　把Java类装载到数据库中　291
　问题　291
　原理分析　293
15.3　把编译后的Java类装载到数据库中　293
　问题　293
　解决方案　293
　原理分析　294
15.4　把Java类声明为存储过程　294
　问题　294
　解决方案　295
　原理分析　295
15.5　执行Java存储过程　295
　问题　295
　解决方案　295
　原理分析　296
15.6　从PL/SQL代码中调用Java存储过程　296
　问题　296
　解决方案　296
　原理分析　297
15.7　在PL/SQL与Java之间传递参数　298
　问题　298
　解决方案　298
　原理分析　299
15.8　创建与调用Java数据库函数　300
　问题　300
　解决方案　300
　原理分析　301
15.9　创建Java数据库触发器　301
　问题　301
　解决方案　301
　原理分析　303
15.10　从PL/SQL往Java中传递数据对象　303
　问题　303
　解决方案　303
　原理分析　305
15.11　把Java类嵌入PL/SQL包　306
　问题　306
　解决方案　306
　原理分析　307
15.12　向数据库中装载Java类库　307
　问题　307
　解决方案　307
　原理分析　309
15.13　删除Java类　309
　问题　309
　解决方案　310
　原理分析　310
15.14　用Java检索数据库元数据　310
　问题　310
　解决方案　310
　原理分析　311
15.15　查询数据库以解决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存储函数　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　构建utPLSQL测试包　331
　问题　331
　解决方案　331
　原理分析　332
17.4　编写utPLSQL单元测试过程　332
　问题　332
　解决方案　333
　原理分析　334
17.5　运行utPLSQL测试　335
　问题　335
　解决方案　335
　原理分析　338
17.6　构建utPLSQL测试套件　338
　问题　338
　解决方案　338
　原理分析　338
17.7　运行utPLSQL测试套件　339
　问题　339
　解决方案　339
　原理分析　345
17.8　重新配置utPLSQL参数　345
　问题　345
　解决方案　346
　原理分析　346
17.9　将utPLSQL测试结果重定向到文件　348
　问题　348
　解决方案　348
　原理分析　348
17.10　用Ant自动运行PL/SQL和Java存储过程的单元测试　349
　问题　349
　解决方案　350
　原理分析　351

索引　355