Oracle PL/SQL Best Practices. 2nd Edition

Spis treści książki

• Oracle PL/SQL Best Practices, 2nd Edition
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Preface
    • Why Best Practices?
      • Best Practices and the Real World
      • They Call This "Work"?
    • The Cast of Characters
    • Structure of This Book
    • How to Use This Book
    • Conventions Used in This Book
    • About the Code
    • Comments and Questions
    • Safari Books Online
    • Acknowledgments
  • 1. The Big Picture
    • Successful Applications Are Never an Accident
      • Successful Applications Meet User Requirements
      • Successful Applications Are Maintainable
      • Successful Applications Run Fast Enough
    • Best Practices for 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.
        • Problem: In software, the ends (production code) are inseparable from the means (the build process).
        • Solution: Agree on a common development workflow built around standards, testing, and reviews.
        • Application-level workflow
          • Step 1: Define requirements and standards.
          • Step 2: Build the application foundation.
          • Step 3: Build the next application iteration.
          • Step 4: Validate the application iteration
        • Single-program construction workflow
          • Step 1: Prepare for program construction.
          • Step 2: Build an iteration of the program.
          • Step 3: Test the program iteration.
          • Step 4: Trace execution of the program.
          • Step 5: Debug the program.
          • Step 6: Validate the program: optimize and review.
          • Which steps do you perform?
    • 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.
        • Step 1. Validate program requirements
        • Step 2. Implement just the header of the program
        • Step 3. Define the tests that must be run
        • Step 4. Build your test code
    • Contracts work for the real world; why not software, too?
      • Match strict input expectations with guaranteed output results.
        • Problem: What is never discussed is never agreed upon.
        • Solution: Contracts capture agreements.
          • About Design by Contract.
          • Enforcing contracts in code.
    • Dont act like a bird: admit weakness and ignorance.
      • Ask for help (or at least take a break) after 30 minutes on a problem.
        • Problem: Steven is a hypocritical programmer.
        • Solution: Give your brain a break, and ask others for help.
    • Five heads are better than one.
      • Review and walk through one another's code; then do automated code reviews.
        • Problem: Sunita spent six months developing comprehensive coding standards for her group.
        • Solution: Move beyond documents to a review process that directly engages the development team.
        • Resources
    • Don't write code that a machine could write for you instead.
      • Generate code whenever possible.
        • Problem: Jasper is starting to feel more like a robot than a human being.
        • Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.
    • We need more than brains to write software.
      • Take care of your "host body": fingers, wrists, back, etc.
  • 2. Real Developers Follow Standards
    • Best Practices for 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.
        • Problem: Delaware writes code that no one else can read.
        • Solution: Use the built-in functionality of your IDE to automatically format your code.
    • Too much freedom is a very bad thing.
      • Adopt consistent naming conventions for subprograms and data structures.
        • Problem: Jasper's eagerness to help is overwhelmed by his hurry to get it done.
        • Solution: Rely on naming conventions that are intuitive and easy to apply.
    • Good names lead to good code.
      • Name procedures with verb phrases, and functions with noun phrases.
        • Problem: Badly formed or inaccurate names can greatly reduce usability of programs.
        • Solution: Construct subprogram names so they reflect both what they are and what they do.
    • Put your checklists into your code.
      • Define templates to foster standardization in package and program structure.
        • Problem: Checklists on paper rarely translate into changes in the way we write our code.
        • Solution: Make your checklists active and directly involved in development.
        • Resources
    • Who needs comments? My code is self-documenting!
      • Comment tersely with value-added information.
        • Problem: One person's clarity is another person's bewilderment.
        • Solution: Sometimes you really do need to add comments.
  • 3. Life After Compilation
    • Testing, Tracing, and Debugging
      • Testing
      • Tracing
      • Debugging
    • Best Practices for Testing, Tracing, and Debugging
    • Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!
      • Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.
        • Problem: DBMS_OUTPUT.PUT_LINE is inadequate for tracing.
        • Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.
    • Assume the worst, and you will never be disappointed.
      • Instrument your code to trace execution.
        • Problem: We live in the moment, and don't think about what it will take to maintain our code.
        • General Solution: Build instrumentation (tracing) into your application from the start.
        • Specific Solution 1: Embed trace calls in Boolean expressions to minimize overhead.
        • Specific Solution 2: Include standardized modules in packages to dump package state when errors occur.
        • Specific Solution 3: Build trace "windows" into your packages using standardized programs.
    • Users really don't want to be programmers.
      • Test your programs thoroughly, and as automatically as possible.
        • Problem: Sunita's team is dragged down off its pedestal of semi-godliness.
        • General Solution: Don't make testing an option, and don't just "try" a few things.
        • Specific Solution 1: A brief introduction to utPLSQL.
        • Specific Solution 2: A brief introduction to Quest Code Tester for Oracle.
        • Resources
    • Do you take road trips without a destination in mind?
      • Follow the test-driven development methodology.
        • Problem: We cannot trust our own minds to fully and objectively test our code.
        • Solution: Decide before you implement your program how you will know when it works correctly.
        • Resources
    • 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.
        • Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.
        • Solution: When it comes to testing, be happy with getting started and with steady progress.
    • Sherlock Holmes never had it so good.
      • Use source code debuggers to hunt down the cause of bugs.
        • Problem: Jasper writes some complicated code and has no idea what it is doing.
        • Solution: Use the built-in, interactive source debuggers available in almost every PL/SQL IDE.
  • 4. What's Code Without Variables?
    • Best Practices for Declaring Variables and Data Structures
    • That column's never going to change!
      • Always anchor variables to database datatypes using %TYPE and %ROWTYPE.
        • Problem: Lizbeth writes a "quick-and-dirty" program.
        • Solution: Assume that everything will change and that any program you write could be around for decades.
    • There's more to data than columns in a table.
      • Use SUBTYPEs to declare program-specific and derived datatypes.
        • Problem: Lizbeth learns her lesson but then cannot apply it.
        • Solution: Create a new datatype with SUBTYPE and anchor to that.
    • I take exception to your declaration section.
      • Perform complex variable initialization in the execution section.
        • Problem: The exception section of a block can only trap errors raised in the execution section.
        • Solution: Don't trust the declaration section to assign default values.
    • Best Practices for Using Variables and Data Structures
    • This logic is driving me crazy!
      • Replace complex expressions with well-named constants, variables, or functions.
        • Problem: Business rules can be complicated, and it's hard to keep them straight.
        • Solution: Simplify code to make the criteria for the business rules more obvious.
    • Go ahead and splurge: declare distinct variables for different usages.
      • Don't overload data structure usage.
        • Problem: World weariness infects Lizbeth's code.
        • Solution: Don't let your weariness show in your codeand don't recycle!
    • Didn't your parents teach you to clean up after yourself?
      • Clean up data structures when your program terminates (successfully or with an error).
        • Problem: Sometimes you really do need to clean up in a PL/SQL block.
        • Resources
    • Programmers are (or should be) control freaks.
      • Beware of and avoid implicit datatype conversions.
        • Problem: PL/SQL performs implicit conversionsbut they're not always what you want.
        • Solution: Perform explicit conversions rather than relying on implicit conversions.
        • Resources
    • Best Practices for Declaring and Using Package Variables
    • Danger, Will Robinson! Globals in use!
      • Use package globals sparingly and only in package bodies.
        • Problem: Jasper needs Lizbeth's program data. Delaware needs Jasper's program data.
        • Solution: Don't expose program data in package specifications, letting everyone see and change it.
    • Packages should have a strong sense of personal space.
      • Control access to package data with "get and set" modules.
        • Problem: Data structures declared in a package specification may end up bypassing business rules.
        • Solution: Declare data in the package body, and hide the data structures via functions in the package specification.
  • 5. Developer As Traffic Cop
    • Best Practices for Conditional and Boolean Logic
    • Reading your code should not require mental gymnastics.
      • Use IF . . . ELSIF only to test a single, simple condition.
        • Problem: IF-statement logic can become complex and confusing.
        • Solution: Simplify by specifying clauses at multiple levels.
    • KISS (Keep it Simple, Steven).
      • Use CASE to avoid lengthy sequences of IF statements.
        • Problem: Lizbeth needs to construct a string conditionally from a number of pieces.
        • Solution: CASE will greatly simplify the code.
        • Solution: Don't forget the ELSE in the CASE!
    • Beware the hidden costs of NULL.
      • Treat NULL conditions explicitly in conditional statements.
        • Problem: Why is Lizbeth's IF statement doing that?
        • Solution: Remember that a NULL is not equal to anything else, even another NULL, and code explicitly for that case.
    • Best Practices for Loop Processing
    • There's a right way and a wrong way to say goodbye.
      • Never EXIT or RETURN from WHILE and FOR loops.
        • Problem: Jasper writes a loop that offers many exit paths.
        • Solution: One way in, one way out.
    • Don't take out "programmers' insurance" . . . and don't worry about SkyNet.
      • Never declare the FOR loop index or any other implicitly declared structure.
        • Problem: The previous developer wrote some code just in case.
        • Solution: Cyberspace is a world of our making. Remove all uncertainty from your code.
    • There is more than one way to scan a collection.
      • Use FOR loops for dense collections, WHILE loops for sparse collections.
        • Problem: It's so hard to write code without making assumptions!
        • Solution: Write "full collection scans" so that there is no assumption about how the collection is filled.
    • Best Practices for Branching Logic
    • Maze-like programs are never a good thing.
      • Use GOTO and CONTINUE only when structured code is not an option.
        • Resources
  • 6. Doing the Right Thing When Stuff Goes Wrong
    • Best Practices for Understanding Error Handling
    • Ignorance is bad exception management.
      • Study how error raising, handling, and logging work in PL/SQL.
        • Problem: Know-it-alls don't know the most important thing: they're wrong!
        • Solution: Take some time to familiarize yourself with how PL/SQL exception management works and what PL/SQL offers to help you get the job done.
        • Resources
    • All exceptions are not created equal.
      • Distinguish between deliberate, unfortunate, and unexpected errors.
        • Deliberate exceptions
        • Unfortunate and unexpected exceptions
        • How to benefit from this categorization
        • Problem: Application logic in the exception is hard to find and maintain.
        • Solution: Avoid application logic in the exception section.
        • Problem: Unhandled unfortunate exceptions make your code less flexible and more difficult to reuse.
        • Solution: Transform the exception to a status indicator that can be interpreted by the user of that code.
        • Problem: Unhandled unexpected errors cause a loss of information about the cause of the error.
        • Solution: Handle those unexpected, "hard" errors and then re-raise the exception.
        • In conclusion . . .
    • One error management approach for all.
      • Use error-management standards to avoid confusion and conflicts.
        • General Problem: Chaos reigns!
        • Specific Problem 1: Get word back to the users.
        • Specific Problem 2: How do I log my error? Let me count the ways . . .
        • General Solution: One component, under source control, for all to use.
        • Specific Solution 1: No more RAISE_APPLICATION_ERROR.
        • Specific Solution 2: Use declarative error handler routines.
        • Resources
    • Best Practices for 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.
        • Problem: A little bit of laziness and a slight dose of advanced features can ruin a good program.
        • Solution: Avoid writing "clever" code that shows just how much you know and how smart you are.
    • Avoid programmer apathy.
      • Never use WHEN OTHERS THEN NULL.
        • Problem: The "I don't care" exception handler can cover up problems too indiscriminately.
        • Solution: Add value in WHEN OTHERS: log information and re-raise some exception or other!
    • Best Practices for Coding Defensively
    • You weren't supposed to do that with my program!
      • Use assertion routines to verify all assumptions made in your program.
        • Problem: Delaware expects everyone to be a know-it-all like him.
        • Solution: Assume nothing! Make all assumptions explicit, and then validate them.
        • Resources
  • 7. Break Your Addiction to SQL
    • SQL Is Bad!
      • Step 1. Hardcoding is bad.
      • Step 2: Every SQL statement you write is a hardcoding.
      • Step 3. Draw the logical conclusion.
    • General SQL Best Practices
    • The best way to avoid problematic code is to not write it.
      • Hide your SQL statements behind a programmatic interface.
        • Problem: PL/SQL developers are addicted to SQL!
        • Solution: Never repeat a SQL statement; instead, implement SQL behind procedures and functions.
          • Never hardcode or expose a query in your application-level code.
          • Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.
          • The path to an effective programmatic interface for your SQL.
    • You may write PL/SQL code, but SQL always takes precedence.
      • Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
        • Problem: Global search-and-replace to the rescue!
        • Solution: Always check after global search-and-replace, and qualify all variable references.
    • When one transaction is not enough.
      • Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs.
        • Problem: Your error log entries have disappeared!
        • Solution: Save your log information separately from your business transaction logic.
    • I don't always want to save my changes.
      • Don't hardcode COMMITs and ROLLBACKs in your code.
        • Problem: Is everything Lizbeth does in her programs some kind of hardcoding?
        • Solution: Call your own program to do the commit (or rollback), and make it more flexible.
        • Resources
    • Best Practices for 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.
        • Problem: A change in one place affects many othersnever a good idea.
        • Solution: Skip all those declarations and replace them with a single record.
    • Answer the question being asked; that is, be a good listener.
      • Use COUNT only when the actual number of occurrences is needed.
        • Problem: Delaware is not a good listener.
        • Solution: Use COUNT only when you need to know "How many rows?"
    • Your code makes my head spin.
      • Don't use a cursor FOR loop to fetch just one row.
        • Problem: Jasper chooses the wrong time to be lazy.
        • Solution: Use the cursor FOR loop only when fetching multiple rows.
    • Best Practices for Changing Data from PL/SQL
    • Assume the worst!
      • Don't forget exception handlers for your DML statements.
        • Problem: Jasper knows what he's supposed to do. He just can't bring himself to do it.
        • Solution: Make sure that any errors that can be anticipated are logged and communicated to the user.
    • Things only get more complicated over time.
      • List columns explicitly in your INSERT statements.
        • Problem: It's hard to think about what a table will be like in the future.
        • Solution: Always explicitly list the columns that are part of the INSERT statement.
    • Timing is everything in the world of cursors.
      • Reference cursor attributes immediately after executing the SQL operation.
        • Problem: I check the contents of the SQL%ROWCOUNT too late in the game.
        • Solution: Remember that SQL% attributes always refer to the most recently executed implicit cursor in your session.
    • Best Practices for Dynamic SQL
    • Make it easy to untangle and debug your dynamic SQL statements.
      • Always parse a string variable; do not EXECUTE IMMEDIATE a literal.
        • Problem: There's something wrong with Lizbeth's dynamic SQL, but she can't figure it out.
        • Solution: Make sure your exception section can display the string that failed to parse.
    • Give the RDBMS a break.
      • Avoid concatenation of variable values into dynamic SQL strings.
        • Problem: You have lots of dynamic SQL updates, with different values each time.
        • Solution: Bind, don't concatenate, to optimize performance and simplify dynamic string construction.
    • So you think you know what users might do with your code?
      • Do not allow malicious injection of code into your dynamic statements.
        • Problem: So many teenagers, so many ways to enter data at the web site.
        • Solution: Avoid concatenation of SQL text, rely on bind variables, and secure your schemas.
    • It's rude to drop someone else's objects.
      • Apply the invoker rights method to stored code that executes dynamic SQL.
        • Problem: A seemingly handy utility goes badly wrong.
        • Solution: Make sure your dynamic SQL programs run under the invoker's authority.
        • Resources
  • 8. Playing with Blocks (of Code)
    • Best Practices for Parameters
    • Once a program is in use, you can't change it willy-nilly.
      • Ensure backward compatibility as you add parameters.
        • Problem: Lizbeth's program needs to do more, and do it differently.
        • Solution: Make sure all new IN arguments have defaults, or add an overloading.
    • What the heck do those parameter values mean?
      • Use named notation to self-document subprogram calls and pass values more flexibly.
        • Problem: What seems obvious at the moment of writing is far less clear months or years later.
        • Solution: Use named notation to make everything clear.
    • Where'd that data come from?
      • Functions should return data only through the RETURN clause.
        • Problem: Jasper returns data in a very confusing manner.
        • Solution: Return multiple values through a single, composite structure or with a procedure.
    • Best Practices for Procedures and Functions
    • Write tiny chunks of code.
      • Limit execution section length to no more than 50 lines.
        • Problem: Lengthy blobs of spaghetti code are unmanageable.
        • Solution: Use step-wise refinement and local subprograms to make code transparent in purpose and design.
        • There's more to tiny chunks than just local subprograms.
        • Resources
    • Every program should be an island (of purpose).
      • Minimize side effects and maximize reuse by creating programs with narrowly defined purposes.
        • Problem: Delaware packs it all in and no one wants to use it.
        • Solution: Write programs with very specific purposes and avoid hidden (a.k.a. global) dependencies.
    • Gifts should always come tightly wrapped.
      • Hide business rules and formulas inside functions.
        • Problem: Jasper actually thinks a rule will never change!
        • Solution: Wrap or hide all 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.
        • Problem: Sunita tosses off "quick-and-dirty" code in a function lookup routine.
        • Problem: Sure, the program worksbut only if you assume a perfect world.
        • Solution: Don't allow multiple exit points from the function.
    • Black or white programs don't know from NULL.
      • Never return NULL from Boolean functions.
        • Problem: Jasper gets really confused with a Boolean function returning a NULL.
        • Solution: Ensure that a Boolean function returns only TRUE or FALSE.
    • Best Practices for Packages
    • Where there is one program, there will soon be two.
      • Avoid schema-level programs; instead, group related code into packages.
        • Problem: It seemed that there would be only one programnow there are many.
        • Solution: Put in the dot from the start: package.subprogram.
    • "Easy to use code" is code that is usedand reused.
      • Anticipate programmer needs and simplify call interfaces with overloading.
        • Problem: Some of Oracle's supplied packages are harder to use than they should be.
        • Solution: Create multiple programs with the same name (overloading) that anticipate user needs.
    • Best Practices for 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.
        • Problem: Seemingly random trigger behavior is driving Jasper nuts.
        • Solution: Consolidate "same event" triggers or use the FOLLOWS clause.
        • Resources
    • "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.
        • Problem: Lizbeth has created a dozen triggers and a support package on a critical database table.
        • Solution: Consolidate all logic into a compound trigger, and lose the package if it is present only for trigger implementation.
    • Your application should not be able to perform a "Houdini" with business rules.
      • Validate complex business rules with DML triggers.
        • Problem: There's more than one way to make an excuse.
        • Solution 1: Apply the business rule at the lowest level possible, to ensure that it cannot be avoided.
        • Solution 2: Populate columns of derived values with triggers.
  • 9. My Code Runs Faster Than Your Code
    • Best Practices for Finding Slow Code
    • Take the guesswork out of optimization.
      • Use trace facilities to gather raw data about program performance.
        • PL/SQL Profiler (DBMS_PROFILER)
        • PL/SQL Trace (DBMS_TRACE)
        • PL/SQL Hierarchical Profiler (DBMS_HPROF)
        • Application Data Profiler (DBMS_APPLICATION_INFO)
    • There are so many ways to implement an algorithm; which is best?
      • Build or find tools to calculate elapsed time.
        • Problem: There are hundreds of ways to implement a requirement. How can Lizbeth find the fastest version?
        • Solution: Use DBMS_UTILITY.GET_TIME and GET_CPU_TIME for very granular analysis.
        • Resources
    • Best Practices for 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.
        • Problem: It worked so quickly in SQL*Plus. Why is it so slow in PL/SQL?
        • Solution: Sometimes you have to help PL/SQL integrate with SQL.
          • Recommendations for BULK COLLECT
          • Recommendations for FORALL.
    • If the SGA is so wonderful, why not emulate it?
      • Cache static data in the fastest memory location possible.
        • Problem: Jasper queries the same, unchanging data over and over and over again.
        • Solution: If the normal SGA cache doesn't the trick, look for other ways to cache.
          • Per-session cache in a PL/SQL package.
          • Oracle Database 11g PL/SQL function result cache.
    • When waiting is not an option...
      • Use pipelined table functions to return data faster and to parallelize function execution.
        • Problem: Users hate to wait.
        • Solution: Design your function to return data while it is still running!
        • And that's not all!
    • Best Practices for 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 OUT parameters.
        • Problem: Delaware wants to use collections as parameters, but they are causing a bottleneck.
        • Solution: Turn off the default copying that comes with IN OUT arguments.
    • PL/SQL loops should not resemble hamsters running in circles.
      • Move static expressions outside of loops and SQL statements.
        • Problem: Lizbeth lost her focus as she wrote her loop.
        • Solution: Avoid executing anything inside a loop that doesn't change in that loop.
    • Tailor-made datatypes are the best fit for your programs.
      • Choose datatypes carefully to minimize implicit conversions of data.
        • Problem: Oracle goes out of its way to make it easy for us to write PL/SQL programs.
        • Solution: Avoid implicit conversions and choose your datatypes carefully.
  • A. Best Practices Quick Reference
  • B. Resources for PL/SQL Developers
    • Book Example Files
    • Books and Other Content
      • Oracle PL/SQL Books
      • Other Helpful Books
      • Online Content
    • Free Software
  • About the Author
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje