Oracle PL/SQL Best Practices. Optimizing Oracle Code Steven Feuerstein

Spis treści książki

• Oracle PL/SQL Best Practices
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Dedication
  • Preface
    • Structure of This Book
    • How to Use This Book
    • Not All Best Practices Are Created Equal
    • About the Code
    • Other Resources
    • Conventions Used in This Book
    • Comments and Questions
    • Acknowledgments
  • 1. The Development Process
    • DEV-01: Set standards and guidelines before writing any code.
    • DEV-02: Ask for help after 30 minutes on a problem.
    • DEV-03: Walk through each others code.
    • DEV-04: Validate standards against source code in the database.
    • DEV-05: Generate code whenever possible and appropriate.
    • DEV-06: Set up and use formal unit testing procedures.
    • DEV-07: Get independent testers for functional sign-off.
  • 2. Coding Style and Conventions
    • STYL-01: Adopt a consistent, readable format that is easy to maintain.
    • STYL-02: Adopt logical, consistent naming conventions for modules and data structures.
    • STYL-03: Standardize module and program headers.
    • STYL-04: Tag module END statements with module names.
    • STYL-05: Name procedures with verb phrases and functions with noun phrases.
    • STYL-06: Self-document using block and loop labels.
    • STYL-07: Express complex expressions unambiguously using parentheses.
    • STYL-08: Use vertical code alignment to emphasize vertical relationships.
    • STYL-09: Comment tersely with value-added information.
    • STYL-10: Adopt meaningful naming conventions for source files.
  • 3. Variables and Data Structures
    • 3.1. Declaring Variables and Data Structures
      • DAT-01: Match datatypes to computational usage.
      • DAT-02: Anchor variables to database datatypes using %TYPE and %ROWTYPE.
      • DAT-03: Use SUBTYPE to standardize application-specific datatypes.
      • DAT-04: Do not hard-code VARCHAR2 lengths.
      • DAT-05: Use CONSTANT declarations for variables whose values do not change.
      • DAT-06: Perform complex variable initialization in the executable section.
    • 3.2. Using Variables and Data Structures
      • DAT-07: Replace complex expressions with Boolean variables and functions.
      • DAT-08: Do not overload data structure usage.
      • DAT-09: Remove unused variables and code.
      • DAT-10: Clean up data structures when your program terminates (successfully or with an error).
      • DAT-11: Beware of and avoid implicit datatype conversions.
    • 3.3. Declaring and Using Package Variables
      • DAT-12: Package application-named literal constants together.
      • DAT-13: Centralize TYPE definitions in package specifications.
      • DAT-14: Use package globals judiciously and only in package bodies.
      • DAT-15: Expose package globals using "get and set" modules.
  • 4. Control Structures
    • 4.1. Conditional and Boolean Logic
      • CTL-01: Use ELSIF with mutually exclusive clauses.
      • CTL-02: Use IF...ELSIF only to test a single, simple condition.
      • CTL-03: Replace and simplify IF statements with Boolean expressions.
    • 4.2. Loop Processing
      • CTL-04: Never EXIT or RETURN from WHILE and FOR loops.
      • CTL-05: Use a single EXIT in simple loops.
      • CTL-06: Use a simple loop to avoid redundant code required by a WHILE loop.
      • CTL-07: Never declare the FOR loop index.
      • CTL-08: Scan collections using FIRST, LAST, and NEXT in loops.
      • CTL-09: Move static expressions outside of loops and SQL statements.
    • 4.3. Miscellaneous
      • CTL-10: Use anonymous blocks within IF statements to conserve resources.
      • CTL-11: Label and highlight GOTOs if using this normally unnecessary construct.
  • 5. Exception Handling
    • EXC-00: Set guidelines for application-wide error handling before you start coding.
      • EXC-00: Set guidelines for application-wide error handling before you start coding.
    • 5.1. Raising Exceptions
      • EXC-01: Verify preconditions using standardized assertion routines that raise violation exceptions.
      • EXC-02: Use the default exception-handling model to communicate module status back to calling PL/SQL programs.
      • EXC-03: Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQL host programs.
      • EXC-04: Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR.
      • EXC-05: Only RAISE exceptions for errors, not to branch execution control.
      • EXC-06: Do not overload an exception with multiple errors unless the loss of information is intentional.
    • 5.2. Handling Exceptions
      • EXC-07: Handle exceptions that cannot be avoided but can be anticipated.
      • EXC-08: Avoid hard-coded exposure of error handling by using standard, declarative procedures.
      • EXC-09: Use named constants to soft-code application-specific error numbers and messages.
      • EXC-10: Include standardized modules in packages to dump package state when errors occur.
      • EXC-11: Use WHEN OTHERS only for unknown exceptions that need to be trapped.
    • 5.3. Declaring Exceptions
      • EXC-12: Standardize named application exceptions in package specifications.
      • EXC-13: Document all package exceptions by module in package specifications.
      • EXC-14: Use the EXCEPTION_INIT pragma to name system exceptions that might be raised by your program.
  • 6. Writing SQL in PL/SQL
    • SQL-00: Establish and follow clear rules for how to write SQL in your application.
      • SQL-00: Establish and follow clear rules for how to write SQL in your application.
    • 6.1. General SQL and Transaction Management
      • SQL-01: Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
      • SQL-02: Use incremental COMMITs to avoid rollback segment errors when changing large numbers of rows.
      • SQL-03: Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs (Oracle8i).
    • 6.2. Querying Data from PL/SQL
      • SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.
      • SQL-05: Hide reliance on the dual table.
      • SQL-06: Define multi-row cursors in packages so they can be used from multiple programs.
      • SQL-07: Fetch into cursor records, never into a hard-coded list of variables.
      • SQL-08: Use COUNT only when the actual number of occurrences is needed.
      • SQL-09: Use a cursor FOR loop to fetch all rows in a cursor unconditionally.
      • SQL-10: Never use a cursor FOR loop to fetch just one row.
      • SQL-11: Specify columns to be updated in a SELECT FOR UPDATE statement.
      • SQL-12: Parameterize explicit cursors.
      • SQL-13: Use RETURNING to retrieve information about modified rows (Oracle8).
      • SQL-14: Use BULK COLLECT to improve performance of multi-row queries (Oracle8i).
    • 6.3. Changing Data from PL/SQL
      • SQL-15: Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.
      • SQL-16: Reference cursor attributes immediately after executing the SQL operation.
      • SQL-17: Check SQL%ROWCOUNT when updating or removing data that "should" be there.
      • SQL-18: Use FORALL to improve performance of collection-based DML (Oracle8i).
    • 6.4. Dynamic SQL and Dynamic PL/SQL
      • SQL-19: Encapsulate dynamic SQL parsing to improve error detection and cleanup.
      • SQL-20: Bind, do not concatenate, variable values into dynamic SQL strings.
      • SQL-21: Soft-code the maximum length of columns in DBMS_SQL.DEFINE_COLUMN calls.
      • SQL-22: Apply the invoker rights method to all stored code that executes dynamic SQL (Oracle8i).
      • SQL-23: Format dynamic SQL strings so they can be easily read and maintained.
  • 7. Program Construction
    • 7.1. Structure and Parameters
      • MOD-01: Encapsulate and name business rules and formulas behind function headers.
      • MOD-02: Standardize module structure using function and procedure templates.
      • MOD-03: Limit execution section sizes to a single page using modularization.
      • MOD-04: Use named notation to clarify, self-document, and simplify module calls.
      • MOD-05: Avoid side-effects in your programs.
      • MOD-06: Use NOCOPY to minimize overhead when collections and records are [IN] OUT parameters (Oracle8i).
    • 7.2. Functions
      • MOD-07: Limit functions to a single RETURN statement in the execution section.
      • MOD-08: Keep functions pure by avoiding [IN] OUT parameters.
      • MOD-09: Never return NULL from Boolean functions.
    • 7.3. Triggers
      • MOD-10: Minimize the size of trigger execution sections.
      • MOD-11: Consolidate "overlapping" DML triggers to control execution order.
      • MOD-12: Raise exceptions to report on do-nothing INSTEAD OF triggers.
      • MOD-13: Implement server problem logs and "to do" lists using database triggers.
      • MOD-14: Use ORA_% public synonyms to reference database and schema event trigger attributes.
      • MOD-15: Validate complex business rules with DML triggers.
      • MOD-16: Populate columns of derived values with triggers.
      • MOD-17: Use operational directives to provide more meaningful error messages from within triggers.
  • 8. Package Construction
    • PKG-01: Group related data structures and functionality together in a single package.
    • PKG-02: Provide well-defined interfaces to business data and functional manipulation using packages.
    • PKG-03: Freeze and build package specifications before implementing package bodies.
    • PKG-04: Implement flexible, user-adjustable functionality using package state toggles and related techniques.
    • PKG-05: Build trace "windows" into your packages using standardized programs.
    • PKG-06: Use package body persistent data structures to cache and optimize data-driven processing.
    • PKG-07: Insulate applications from Oracle version sensitivity using version-specific implementations.
    • PKG-08: Avoid bloating package code with unnecessary but easy-to-build modules.
    • PKG-09: Simplify and encourage module usage using overloading to widen calling options.
    • PKG-10: Consolidate the implementation of related overloaded modules.
    • PKG-11: Separate package specifications and bodies into different source code files.
    • PKG-12: Use a standard format for packages that include comment headers for each type of element defined in the package.
  • 9. Built-in Packages
    • 9.1. DBMS_OUTPUT
      • BIP-01: Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.
    • 9.2. UTL_FILE
      • BIP-02: Improve the functionality and error handling of UTL_FILE by using a comprehensive encapsulation package.
      • BIP-03: Validate the setup of UTL_FILE with simple tests.
      • BIP-04: Handle expected and named exceptions when performing file I/O.
      • BIP-05: Encapsulate UTL_FILE.GET_LINE to avoid propagating the NO_DATA_FOUND exception.
      • BIP-06: Soft-code directory names in your calls to UTL_FILE.FOPEN.
    • 9.3. DBMS_PIPE
      • BIP-07: Encapsulate interaction with specific pipes.
      • BIP-08: Provide explicit and appropriate timeout values when you send and receive messages.
      • BIP-09: Use RESET_BUFFER in exception handlers and before you pack data into the message buffer.
    • 9.4. DBMS_ JOB
      • BIP-10: Use your own submission procedure to improve job management capabilities.
      • BIP-11: Trap all errors in DBMS_ JOB-executed stored procedures and modify the job queue accordingly.
  • A. Best Practices Quick Reference
    • A.1. The Development Process
    • A.2. Coding Style and Conventions
    • A.3. Variables and Data Structures
      • Declaring Variables and Data Structures
      • Using Variables and Data Structures
      • Declaring and Using Package Variables
    • A.4. Control Structures
      • Conditional and Boolean Logic
      • Loop Processing
      • Miscellaneous
    • A.5. Exception Handling
      • Raising Exceptions
      • Handling Exceptions
      • Declaring Exceptions
    • A.6. Writing SQL in PL/SQL
      • General SQL and Transaction Management
      • Querying Data from PL/SQL
      • Changing Data from PL/SQL
      • Dynamic SQL and Dynamic PL/SQL
    • A.7. Program Construction
      • Structure and Parameters
      • Functions
      • Triggers
    • A.8. Package Construction
    • A.9. Built-in Packages
      • DBMS_OUTPUT
      • UTL_FILE
      • DBMS_PIPE
      • DBMS_ JOB
  • About the Author
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje