Oracle PL/SQL攻略

内容概要

Josh Juneau

具有20多年的软件开发经验，他的职业生涯起步于数据库应用开发。成为Oracle数据库管理员后，Josh又开始使用PL/SQL为Oracle数据库开发应用。随着技术的积累，他开始尝试将Java加入到PL/SQL应用中，充分利用这两种技术的强大功能，开发了健壮的Oracle数据库应用。此后，他又对Jython和Groovy等JVM语言产生了浓厚兴趣，与他人合著了The Definitive Guide to Jython（第一作者），他还是Django-Jython项目（http://code.google.com/p/django-jython/）负责人。

Matt Arena

具有25年的Oracle数据库应用开发经验。自Web刚刚出现时，Matt就一直致力于基于Web的应用开发，他负责过项目开发生命周期的各阶段工作，但他最喜欢数据库建模和编程。

书籍目录

第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

编辑推荐

Oracle PL/SQL攻略cle专家20余年实战经验的结晶。采用“问题—原理分析—解决方案”的形式，循序渐进、针对性强。摆脱按部就班的学习顺序，按图索骥，找到相关问题的破解方法。

作者简介

PL/SQL是Oracle数据库对SQL语言的扩展，它开启了数据库中业务逻辑集中化的大门。借助于PL/SQL，Oracle的很多最新特性都得以崭露头角。PL/SQL也是非常成功的Oracle Application Express的基石。本书两位作者都具有20多年的数据库应用开发经验，对使用PL/SQL操作Oracle数据库的各种问题了如指掌。在本书中，作者将多年的实战经验总结为具体案例，通过“问题—原理分析—解决方案”的形式，介绍相关问题的破解方法。本书组织结构别具一格，内容由浅入深，读者只需浏览目录，从中找到你所面临的难题，对相关代码略作修改，难题便可迎刃而解。

本书可引领新手走进PL/SQL世界，也可以帮助专业人士在PL/SQL世界中所向披靡。

---

 Oracle PL/SQL攻略下载

---