Tài liệu Beginning PL/SQL - From Novice to Professional

Foreword . xii
About the Author . xiv
Acknowledgments . xv
Introduction (The Big Picture) xvi
■CHAPTER 1 Genesis . 1
Tables 1
An Entity Relationship Diagram . 3
Data Definition Language (DDL) 3
It’s Your Turn to Create a Table 4
Indexes . 5
DDL Again 6
It’s Your Turn to Create an Index . 6
Constraints . 7
Column Constraints 7
Table Constraints 7
It’s Your Turn to Create a Constraint 9
Triggers . 10
Views . 12
Insert . 12
Insert .Values 13
It’s Your Turn to Insert with Values . 14
Insert . Select 15
It’s Your Turn to Insert with Select . 17
Update 19
Fix a Mistake with Update 19
It’s Your Turn to Update 19
Update and Subqueries 20
Delete . 21
A Change in Order 21
It’s Your Turn to Delete 21
vi ■CONTENTS
Select . 22
Joins 23
Query Your Heart Out 24
Our Working Example 25
Our Example Narrative . 26
Our Example ERD . 26
Create a Code Table . 29
It’s Your Turn to Create Code Tables . 33
Create a Content Table . 33
It’s Your Turn to Create Content Tables . 36
Create an Intersection Table 37
It’s Your Turn to Create Intersection Tables 38
Summary 38
■CHAPTER 2 PL/SQL Is Square, Man! 39
Anonymous Blocks 40
Anonymous Block Example . 40
It’s Your Turn to Execute an Anonymous Block . 41
Exceptions . 42
Common Exceptions . 42
Catching an Exception . 42
Functions 42
Create a Function . 43
It’s Your Turn to Create a Function . 45
Procedures 47
Create a Procedure 47
It’s Your Turn to Create a Procedure . 49
Nested Blocks 51
An Example of Nesting Blocks . 51
Rules for Nesting . 53
Packages 54
Create a Package Specification 54
It’s Your Turn to Create a Package Specification . 56
Create a Package Body 57
It’s Your Turn to Create a Package Body 62
Benefits of Using Packages . 64
Summary 64
■CONTENTS vii
■CHAPTER 3 Now Where Did I Put That? . 67
PL/SQL Data Types 67
Variables 68
Variable Naming 68
Variable Declarations 69
Variable Anchors 70
Variable Assignments 70
NULL Value 72
It’s Your Turn to Declare Variables . 73
Scope . 74
Scope Rules 74
It’s Your Turn to Scope Things Out . 78
Types . 79
Table Types 79
Record Types 83
Single Dimension My Foot! . 84
Parameters 85
Parameter Naming 86
Parameter Declarations 86
Parameter Scope . 87
It’s Your Turn to Declare Parameters . 91
Summary 91
■CHAPTER 4 Whoa! One Statement at a Time, Please! 93
Inserts 93
Catching a DUP_VAL_ON_INDEX Exception . 98
Using PL/SQL to Predetect a Duplicate 101
IF I Don’t Tell You Now, When ELSE Can I? . 107
Using SQL to Predetect a Duplicate . 108
It’s Your Turn to Insert 112
Updates 115
Updating a Row . 115
Using SQL to Perform Complex Updates . 116
Deletes . 118
Selects . 119
What Do You Mean, “No Data Found?” 121
Too Many Rows—You’re Kidding, Right? 123
It’s Your Turn to Select 124
Summary . 126
viii ■CONTENTS
■CHAPTER 5 Next Please, One Row at a Time! 127
Cursors . 127
Cursor Declarations 127
Fetching Rows from a Cursor Manually 128
Cursor Records 130
A Singleton Cursor . 130
It’s Your Turn to Fetch Manually 136
Fetching Rows from a Cursor Automatically 143
It’s Your Turn to Fetch Automatically 144
Bulk Collect . 148
Bulk Collect with a Cursor . 148
Bulk Collect with a Select Statement 151
It’s Your Turn to Bulk Collect . 152
For All 156
Summary . 161
■CHAPTER 6 Just Like It Is in the Real World! 163
Information Modeling . 163
Object Orientation 165
Key Tenets 165
Reuse 166
Service Orientation . 167
A Roadmap to Reality . 167
Step 1: Table Packages . 168
Step 2: User-Defined Types 168
Step 3: Object Views . 169
Step 4: Object Tables . 169
A Change in Order 169
Object Types 170
Create a User-Defined Type Specification 170
Create a User-Defined Type Implementation 174
It’s Your Turn to Create a User-Defined Type . 179
Object Views 184
Create an Object View 185
It’s Your Turn to Create an Object View 187
Object Tables 188
Create an Object Table 188
It’s Your Turn to Create an Object Table . 189
■CONTENTS ix
Impedance Mismatch My Foot! . 192
Nested Types and Collections 192
It’s Your Turn to Prove There’s No Impedance Mismatch . 194
Summary . 209
■CHAPTER 7 So Just What’s Happening Here? . 211
Prevention 212
Anchors 213
Data Type Prefixes . 213
Explicit Conversions 213
Preparation . 214
Blocking 215
Bread Crumbs . 216
After the Fact 217
Success Messages . 218
Failure Messages 219
It’s Your Turn to Use put_line() . 220
As It Happens . 222
A DEBUG Table 222
A DEBUG Package 232
It’s Your Turn to Use Debug Logging 236
One Step at a Time . 243
Debugging with Oracle SQL Developer 243
Debugging Anonymous PL/SQL 249
Debugging with TOAD for Oracle . 249
Debugging with Visual Studio 250
It’s Your Turn to Use a Debugger . 250
Profiling 251
Profiler’s Tables . 251
Profiler’s Methods 251
Profiling Reports . 253
It’s Your Turn to Profile . 259
Hey, Don’t Forget SQL! 262
Explain Plan . 262
Physics vs. Explain Plan . 266
It’s Your Turn to Use Explain Plan . 268
TKPROF 273
Summary . 273

■CHAPTER 8 Test, Test, Test, and Test Again . 275
SQL Test Patterns 277
PL/SQL Test Patterns . 279
A Testing Tool . 279
A TEST Table 283
A TEST Package Specification 284
A TEST Package Body 287
It’s Your Turn to Create a Testing Tool . 295
Testing . 307
Testing a Code Table Package . 307
It’s Your Turn to Test a Code Table Package 317
Testing a Content Table Package . 318
It’s Your Turn to Test a Content Table Package . 325
Testing an Intersection Table Package 326
It’s Your Turn to Test an Intersection Table Package . 333
Testing a Type . 334
It’s Your Turn to Test a Type . 337
Automating Testing . 337
Automate Test Processing . 339
It’s Your Turn to Automate Test Processing . 344
Summary . 344
■CHAPTER 9 What Does This Thing Do Anyway? . 347
Indestructible Documentation . 348
SQL*Plus Documentation Tools . 349
GUI Development Environment Tools 350
Rules for Documentation Comments 353
Documentation on Demand 353
A Text-Based Documentation Formatting Tool 353
Accessing Documentation on Demand 358
It’s Your Turn to Access Documentation on Demand . 359
Distributable Documentation . 360
An HTML-Based Documentation Formatting Tool 360
Generating Distributable Documentation . 364
It’s Your Turn to Generate Distributable Documentation 366
Documentation Distribution 366
Summary . 367
■CHAPTER 10 Fairy Tales . 369
Polymorphic Commands 370
The Unhappy SQL Programmer . 373
Code Table Methods 374
Content Table Methods . 376
Intersection Table Methods 383
Hierarchical Table Methods 386
The Black Box . 390
Table Methods 391
Universal Methods . 392
Divide and Conquer 395
Data Migration . 396
On-Demand Data Processing 399
Polling Data Processing . 404
Interfacing 412
Reporting . 419
Summary . 428
■APPENDIX How to Download, Install, and Use Oracle 431
How to Download Oracle Database Software . 431
How to Install Oracle Database Software . 437
How to Use SQL*Plus . 443
How to Download This Book’s Source Code 444
How to Create a New Username 447
How to Write a SQL*Plus Script . 447
How to Execute a SQL*Plus Script 448
How to Describe Your Tables and Stored Procedures 448
■INDEX 451