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