O'Reilly - Oracle - Advanced Programming With Packages.pdf

(2154 KB) Pobierz
[Appendix A] Appendix: PL/SQL Exercises
[Appendix A] Appendix: PL/SQL Exercises
[Appendix A] Appendix: PL/SQL Exercises
Table of Contents
A. Appendix: PL/SQL Exercises. ......................................................................................................................2
A.1 Exercises. ..........................................................................................................................................2
A.1.1 Conditional Logic. ............................................................................................................2
A.1.2 Loops.. ...............................................................................................................................3
A.1.3 Exception Handling. .........................................................................................................6
A.1.4 Cursors. .............................................................................................................................8
A.1.5 Builtin Functions. ............................................................................................................11
A.1.6 Builtin Packages. .............................................................................................................12
A.1.7 Modules.. .........................................................................................................................13
A.1.8 Module Evaluation: Foreign Key Lookup. .....................................................................14
.............................................................................................................................................................................17
A.2 Solutions. ........................................................................................................................................17
A.2.1 Conditional Logic. ..........................................................................................................17
A.2.2 Loops.. .............................................................................................................................18
A.2.3 Exception Handling. .......................................................................................................20
A.2.4 Cursors. ...........................................................................................................................21
A.2.5 Builtin Functions. ............................................................................................................23
A.2.6 Builtin Packages. .............................................................................................................25
A.2.7 Modules.. .........................................................................................................................26
A.2.8 Module Evaluation: Foreign Key Lookup. .....................................................................28
1.2.1 The Iceberg Approach to Coding. ....................................................................................31
1.2.2 The Client−Side Layers. ..................................................................................................32
.............................................................................................................................................................................32
1. PL/SQL Packages. .........................................................................................................................................34
1.1 What Is a PL/SQL Package?. ...........................................................................................................34
.............................................................................................................................................................................35
1.2 What Are the Types and Layers of Packages?. ................................................................................35
.............................................................................................................................................................................37
1.3 What Are the Benefits of Packages?.. ..............................................................................................37
1.3.1 Enforced Information Hiding.. .........................................................................................37
1.3.2 Object−Oriented Design. .................................................................................................37
1.3.3 Top−Down Design.. .........................................................................................................37
1.3.4 Object Persistence. ...........................................................................................................37
1.3.5 Guaranteeing Transaction Integrity. ................................................................................38
1.3.6 Performance Improvement. .............................................................................................38
.............................................................................................................................................................................40
1.4 Using Packages. ...............................................................................................................................40
1.4.1 The Package Specification. ..............................................................................................40
1.4.2 Referencing Package Elements.. ......................................................................................41
1.4.3 The Memory−Resident Architecture of Packages. ..........................................................42
1.4.4 Access to Package Elements. ...........................................................................................45
.............................................................................................................................................................................48
1.5 Types of Packages.. ..........................................................................................................................48
1.5.1 Builtin Packages. .............................................................................................................48
1.5.2 Prebuilt Packages. ............................................................................................................50
1.5.3 Build−Your−Own Packages. ...........................................................................................50
.............................................................................................................................................................................51
1.6 Building Packages.. ..........................................................................................................................51
1.6.1 When Should You Build a Package?. ..............................................................................51
1.6.2 The Package Body. ..........................................................................................................56
1.6.3 The Initialization Section. ................................................................................................57
i
[Appendix A] Appendix: PL/SQL Exercises
Table of Contents
1.6.4 Working with Large Packages. ........................................................................................60
1.6.5 Calling Packaged Functions in SQL. ...............................................................................62
2.2.1 Choosing the Order of Elements. .....................................................................................66
.............................................................................................................................................................................67
2. Best Practices for Packages. .........................................................................................................................68
2.1 Starting With Packages. ...................................................................................................................70
.............................................................................................................................................................................70
2.2 Using Effective Coding Style for Packages. ....................................................................................72
.............................................................................................................................................................................74
2.3 Selecting Package Names. ...............................................................................................................74
2.3.1 Choosing Appropriate and Accurate Names ...................................................................74
2.3.2 Avoiding Redundancy. ....................................................................................................75
2.3.3 Avoiding Superfluous Naming Elements. .......................................................................76
.............................................................................................................................................................................78
2.4 Organizing Package Source Code. ...................................................................................................78
2.4.1 Creating Codependent Packages. .....................................................................................79
.............................................................................................................................................................................81
2.5 Constructing the Optimal Interface to Your Package. .....................................................................81
2.5.1 Seeing Developers as Users. ............................................................................................81
2.5.2 Making Your Programs Case−Insensitive. ......................................................................81
2.5.3 Avoiding Need for User to Know and Pass Literals. .......................................................82
.............................................................................................................................................................................85
2.6 Building Flexibility Into Your Packages. ........................................................................................85
2.6.1 Toggling Package Behavior. ............................................................................................86
2.6.2 Toggles for Code Generation. ..........................................................................................87
.............................................................................................................................................................................91
2.7 Building Windows Into Your Packages. ..........................................................................................91
2.7.1 Centralizing the View Mechanism. .................................................................................92
2.7.2 Designing the Window Interface. ....................................................................................92
2.7.3 Implementing the Window. .............................................................................................94
2.7.4 Summarizing the Window Technique. ............................................................................95
.............................................................................................................................................................................97
2.8 Overloading for Smart Packages. ....................................................................................................97
2.8.1 When to Overload. ...........................................................................................................97
2.8.2 Developing an Appreciation of Overloading. ................................................................102
2.9.1 Implementing Overloading with Private Programs. ......................................................103
2.9.2 Lava Lamp Code Consolidation. ...................................................................................103
...........................................................................................................................................................................104
2.9 Modularizing for Maintainable Packages. .....................................................................................107
...........................................................................................................................................................................110
2.10 Hiding Package Data.. ..................................................................................................................110
2.10.1 Gaining Control of Your Data. ....................................................................................110
2.10.2 Tracing Variable Reads and Writes. ............................................................................112
2.10.3 Simplifying Package Interfaces. ..................................................................................113
2.10.4 When to Make Data Public. .........................................................................................114
2.10.5 Anchoring to Public Variables. ....................................................................................115
3.8.1 When the num_in Argument Is 0. ..................................................................................117
3.8.2 When string_in Is NULL. ..............................................................................................118
ii
[Appendix A] Appendix: PL/SQL Exercises
Table of Contents
Not Found. .......................................................................................................................................................119
...........................................................................................................................................................................119
3. The PL/SQL Development Spiral. .............................................................................................................122
3.1 The Basic Problem. ........................................................................................................................122
...........................................................................................................................................................................124
3.2 Adding Value. ................................................................................................................................124
...........................................................................................................................................................................126
3.3 Supplying Backward Compatibility. ..............................................................................................126
...........................................................................................................................................................................128
3.4 Improving the User Interface. ........................................................................................................128
...........................................................................................................................................................................130
3.5 Rough Waters Ahead. ....................................................................................................................130
...........................................................................................................................................................................132
3.6 Building a Structured Function. .....................................................................................................132
...........................................................................................................................................................................135
3.7 Handling Program Assumptions. ...................................................................................................135
...........................................................................................................................................................................137
3.8 Broadening the Scope. ...................................................................................................................137
...........................................................................................................................................................................139
3.9 Considering Implementation Options. ...........................................................................................139
...........................................................................................................................................................................141
3.10 Choosing the Best Performer. ......................................................................................................141
...........................................................................................................................................................................144
3.11 Don't Forget Backward Compatibility. ........................................................................................144
...........................................................................................................................................................................145
3.12 Obliterating the Literals. ..............................................................................................................145
...........................................................................................................................................................................148
3.13 Glancing Backward, Looking Upward. .......................................................................................148
...........................................................................................................................................................................149
4. Getting Started with PL/Vision .................................................................................................................150
4.1 What Is PL/Vision?. .......................................................................................................................150
4.1.1 The Benefits of PL/Vision. ............................................................................................150
4.1.2 The Origins of PL/Vision. .............................................................................................151
...........................................................................................................................................................................153
4.2 PL/Vision Package Bundles. ..........................................................................................................153
4.2.1 Building Blocks. ............................................................................................................153
4.2.2 Developer Utilities. ........................................................................................................154
4.2.3 Plug−and−Play Components .........................................................................................155
...........................................................................................................................................................................156
4.3 Installation Instructions. .................................................................................................................156
4.3.1 What's On the Disk?. .....................................................................................................156
4.3.2 Storage Requirements. ...................................................................................................156
4.3.3 Beginning the Installation. .............................................................................................157
4.3.4 Using the PL/Vision Lite Online Reference. .................................................................158
4.3.5 Creating the PL/Vision Packages. .................................................................................159
4.3.6 Granting Access to PL/Vision. ......................................................................................160
...........................................................................................................................................................................162
4.4 Installing Online Help for PL/Vision. ............................................................................................162
4.4.1 Special Handling for PLVdyn. .......................................................................................162
4.4.2 A PL/Vision Initialization Script for SQL*Plus. ...........................................................163
iii
[Appendix A] Appendix: PL/SQL Exercises
Table of Contents
4.4.3 Converting Scripts to PL/SQL Programs. ......................................................................163
4.4.4 A Warning About Partial Installation. ...........................................................................164
4.4.5 Uninstalling PL/Vision. .................................................................................................164
...........................................................................................................................................................................166
4.5 Using Online Help. ........................................................................................................................166
4.5.1 Zooming in on help tex. ................................................................................................166
...........................................................................................................................................................................168
4.6 Summary of Files on Disk. ............................................................................................................168
4.6.1 Contents of the install Subdirectory. ..............................................................................168
4.6.2 Contents of the test Subdirectory. ..................................................................................168
4.6.3 Contents of the use Subdirectory. ..................................................................................169
...........................................................................................................................................................................172
5. PL/Vision Package Specifications. .............................................................................................................173
5.1 Common Package Elements. .........................................................................................................173
...........................................................................................................................................................................175
5.2 p: a DBMS_OUTPUT Substitute ..................................................................................................175
5.2.1 Toggling output from the p package. .............................................................................175
5.2.2 Setting the line separato. ...............................................................................................175
5.2.3 Setting the line prefix. ....................................................................................................175
5.2.4 The overloadings of the l procedure. .............................................................................175
...........................................................................................................................................................................177
5.3 PLV: Top−Level Constants and Functions. ...................................................................................177
5.3.1 PL/Vision constants. ......................................................................................................177
5.3.2 Anchoring datatypes. .....................................................................................................177
5.3.3 Setting the date format mask .........................................................................................177
5.3.4 Setting the NULL substitution value. ............................................................................177
5.3.5 Assertion routines. .........................................................................................................178
5.3.6 Miscellaneous programs. ...............................................................................................178
...........................................................................................................................................................................180
5.4 PLVcase: PL/SQL Code Conversion. ............................................................................................180
5.4.1 Package constants. .........................................................................................................180
5.4.2 Case−converting programs. ...........................................................................................180
...........................................................................................................................................................................182
5.5 PLVcat: PL/SQL Code Cataloguing. .............................................................................................182
5.5.1 Cataloguing package contents. ......................................................................................182
5.5.2 Identifying references in stored code. ............................................................................182
...........................................................................................................................................................................183
5.6 PLVchr: Operations on Single Characters. ....................................................................................183
5.6.1 PLVchr constants. ..........................................................................................................183
5.6.2 Character type functions. ...............................................................................................183
5.6.3 Other functions and procedures. ....................................................................................184
...........................................................................................................................................................................185
5.7 PLVcmt: Commit Processing. .......................................................................................................185
5.7.1 Controlling commit activity. ..........................................................................................185
5.7.2 Logging commit activity. ...............................................................................................185
5.7.3 Performing commits. .....................................................................................................185
5.7.4 Managing the commit counter. ......................................................................................185
...........................................................................................................................................................................187
5.8 PLVddd: DDL Syntax Dump. .......................................................................................................187
5.8.1 Including the schema. ....................................................................................................187
5.8.2 Including the storage parameter. ....................................................................................187
iv

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin