Entire Site
Free Trial
Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Appendix A. Best Practices Quick Reference
Appendix A. Best Practices Quick Reference
The Best Practices Quick Reference provided in this appendix compiles the best practice titles across all the chapters into a concise list; for each best practice, the first, sometimes tongue-in-cheek title of each best practice appears in the left column and the second, more serious title in the right column. Once you have studied the individual best practices, you can use Table A-1 as a checklist, to be reviewed before you begin coding a new program or application.
| Chapter 1, The Big Picture | |
| Successful Applications | |
| Software is like ballet: choreograph the moves or end up with a mess. | Put into place a practical workflow that emphasizes iterative development based on a shared foundation. |
| Deferred satisfaction is a required emotion for best practices. | Hold off on implementing the body of your program until your header is stable and your tests are defined. |
| Contracts work for the real world; why not software, too? | Match strict input expectations with guaranteed output results. |
| Don't act like a bird: admit weakness and ignorance. | Ask for help (or at least take a break) after 30 minutes on a problem. |
| Five heads are better than one. | Review and walk through one another's code; then do automated code reviews. |
| Don't write code that a machine could write for you instead. | Generate code whenever possible. |
| We need more than brains to write software. | Take care of your "host body": fingers, wrists, back, etc. |
| Chapter 2, Real Developers Follow Standards | |
| Developing and Using Standards | |
| It's a free country; I don't have to use carriage returns in my code. | Adopt a consistent format that is easy to read and maintain. |
| Too much freedom is a very bad thing. | Adopt consistent naming conventions for subprograms and data structures. |
| Good names lead to good code. | Name procedures with verb phrases, and functions with noun phrases. |
| Put your checklists into your code. | Define templates to foster standardization in package and program structure. |
| Who needs comments? My code is self-documenting! | Comment tersely with value-added information. |
| Chapter 3, Life After Compilation | |
| Testing, Tracing, and Debugging | |
| Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE! | Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly. |
| Assume the worst, and you will never be disappointed. | Instrument your code to trace execution. |
| Users really don't want to be programmers. | Test your programs thoroughly, and as automatically as possible. |
| Do you take road trips without a destination in mind? | Follow the test-driven development methodology. |
| For every test you can think of, there are 10 tests waiting to be performed. | Don't worry about getting to 100 percent test coverage. |
| Sherlock Holmes never had it so good. | Use source code debuggers to hunt down the cause of bugs. |
| Chapter 4, What's Code Without Variables? | |
| Declaring Variables and Data Structures | |
| That column's never going to change! | Always anchor variables to database datatypes using %TYPE and %ROWTYPE. |
| There's more to data than columns in a table. | Use SUBTYPEs to declare program-specific and derived datatypes. |
| I take exception to your declaration section. | Perform complex variable initialization in the execution section. |
| Using Variables and Data Structures | |
| This logic is driving me crazy! | Replace complex expressions with well-named constants, variables or functions. |
| Go ahead and splurge: declare distinct variables for different usages. | Don't overload data structure usage. |
| Didn't your parents teach you to clean up after yourself? | Clean up data structures when your program terminates (successfully or with an error). |
| Programmers are (or should be) control freaks. | Beware of and avoid implicit datatype conversions. |
| Declaring and Using Package Variables | |
| Danger, Will Robinson! Globals in use! | Use package globals sparingly and only in package bodies. |
| Packages should have a strong sense of personal space. | Control access to package data with "get and set" modules. |
| Chapter 5, Developer As Traffic Cop | |
| Conditional and Boolean Logic | |
| Reading your code should not require mental gymnastics. | Use IF . . . ELSIF only to test a single, simple condition. |
| KISS (Keep it Simple, Steven). | Use CASE to avoid lengthy sequences of IF statements. |
| Beware the hidden costs of NULL. | Treat NULL conditions explicitly in conditional statements. |
| Loop Processing | |
| There's a right way and a wrong way to say goodbye. | Never EXIT or RETURN from WHILE and FOR loops. |
| Don't take out "programmers' insurance" . . . and don't worry about SkyNet. | Never declare the FOR loop index or any other implicitly declared structure. |
| There is more than one way to scan a collection. | Use FOR loops for dense collections, WHILE loops for sparse collections. |
| Branching Logic | |
| Maze-like programs are never a good thing. | Use GOTO and CONTINUE only when structured code is not an option. |
| Chapter 6, Doing the Right Thing When Stuff Goes Wrong | |
| Understanding Error Handling | |
| Ignorance is bad exception management. | Study how error raising, handling, and logging work in PL/SQL. |
| All exceptions are not created equal. | Distinguish between deliberate, unfortunate, and unexpected errors. |
| One error management approach for all. | Use error-management standards to avoid confusion and conflicts. |
| Nitty-Gritty Everyday Exception Programming | |
| Your code makes me feel dumb. | Use the EXCEPTION_INIT pragma to name exceptions and make your code more accessible. |
| Avoid programmer apathy. | Never use WHEN OTHERS THEN NULL. |
| Coding Defensively | |
| You weren't supposed to do that with my program! | Use assertion routines to verify all assumptions made in your program. |
| Chapter 7, Break Your Addiction to SQL | |
| General SQL | |
| The best way to avoid problematic code is to not write it. | Hide your SQL statements behind a programmatic interface. |
| You may write PL/SQL code, but SQL always takes precedence. | Qualify PL/SQL variables with their scope names when referenced inside SQL statements. |
| When one transaction is not enough. | Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs. |
| I don't always want to save my changes. | Don't hardcode COMMITs and ROLLBACKs in your code. |
| Querying Data from PL/SQL | |
| It's always better to fetch items into a single basket. | Fetch into cursor records, never into a hardcoded list of variables. |
| Answer the question being asked; that is, be a good listener. | Use COUNT only when the actual number of occurrences is needed. |
| Your code makes my head spin. | Don't use a cursor FOR loop to fetch just one row. |
| Changing Data from PL/SQL | |
| Assume the worst! | Don't forget exception handlers for your DML statements. |
| Things only get more complicated over time. | List columns explicitly in your INSERT statements. |
| Timing is everything in the world of cursors. | Reference cursor attributes immediately after executing the SQL operation. |
| Dynamic SQL | |
| Make it easy to untangle and debug your dynamic SQL statements. | Always parse a string variable; do not EXECUTE IMMEDIATE a literal. |
| Give the RDBMS a break. | Avoid concatenation of variable values into dynamic SQL strings. |
| So you think you know what users might do with your code? | Do not allow malicious injection of code into your dynamic statements. |
| It's rude to drop someone else's objects. | Apply the invoker rights method to stored code that executes dynamic SQL. |
| Chapter 8, Playing with Blocks (of Code) | |
| Parameters | |
| Once a program is in use, you can't change it willy-nilly. | Ensure backward compatibility as you add parameters. |
| What the heck do those parameter values mean? | Use named notation to self-document subprogram calls and pass values more flexibly. |
| Where'd that data come from? | Functions should return data only through the RETURN clause. |
| Procedures and Functions | |
| Write tiny chunks of code. | Limit execution section length to no more than 50 lines. |
| Every program should be an island (of purpose). | Minimize side effects and maximize reuse by creating programs with narrowly defined purposes. |
| Gifts should always come tightly wrapped. | Hide business rules and formulas inside functions. |
| One way in, one way out: multiple exits confuse me. | Limit functions to a single RETURN statement in the execution section. |
| Black or white programs don't know from NULL. | Never return NULL from Boolean functions. |
| Packages | |
| Where there is one program, there will soon be two. | Avoid schema-level programs; instead, group related code into packages. |
| "Easy to use code" is code that is used—and reused. | Anticipate programmer needs and simplify call interfaces with overloading. |
| Triggers | |
| Uncertainty in trigger execution is a most unsettling emotion. | Consolidate "overlapping" DML triggers to control execution order, or use the FOLLOW syntax of Oracle Database 11g. |
| "One-stop triggering" is so much easier to understand and maintain. | Use Oracle Database 11g compound triggers to consolidate all related trigger logic on a table. |
| Your application should not be able to perform a "Houdini" with business rules. | Validate complex business rules with DML triggers. |
| Chapter 9, My Code Runs Faster Than Your Code | |
| Finding Slow Code | |
| Take the guesswork out of optimization. | Use trace facilities to gather raw data about program performance. |
| There are so many ways to implement an algorithm; which is best? | Build or find tools to calculate elapsed time. |
| High-Impact Tuning | |
| Let Oracle do most of the tuning for you. | Make sure your code is being optimized when compiled. |
| Who has time for querying (or inserting or deleting or updating) one row at a time? | Use BULK COLLECT and FORALL to improve performance of multirow SQL operations in PL/SQL. |
| If the SGA is so wonderful, why not emulate it? | Cache static data in the fastest memory location possible. |
| When waiting is not an option... | Use pipelined table functions to return data faster and to parallelize function execution. |
| Other Tuning | |
| Sometimes "safe programming" is a little too slow. | Use NOCOPY with care to minimize overhead when collections and records are OUT or IN OUTparameters. |
| PL/SQL loops should not resemble hamsters running in circles. | Move static expressions outside of loops and SQL statements. |
| Tailor-made datatypes are the best fit for your programs. | Choose datatypes carefully to minimize implicit conversions of data. |
