Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

by Steven Feuerstein
Released April 2001
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596001216

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

In this book, Steven Feuerstein, widely recognized as one of the world's experts on the Oracle PL/SQL language, distills his many years of programming, writing, and teaching about PL/SQL into a set of PL/SQL language "best practices"--rules for writing code that is readable, maintainable, and efficient. Too often, developers focus on simply writing programs that run without errors--and ignore the impact of poorly written code upon both system performance and their ability (and their colleagues' ability) to maintain that code over time.Oracle PL/SQL Best Practices is a concise, easy-to-use reference to Feuerstein's recommendations for excellent PL/SQL coding. It answers the kinds of questions PL/SQL developers most frequently ask about their code:

  • How should I format my code?
  • What naming conventions, if any, should I use?
  • How can I write my packages so they can be more easily maintained?
  • What is the most efficient way to query information from the database?
  • How can I get all the developers on my team to handle errors the same way?
The book contains 120 best practices, divided by topic area. It's full of advice on the program development process, coding style, writing SQL in PL/SQL, data structures, control structures, exception handling, program and package construction, and built-in packages. It also contains a handy, pull-out quick reference card. As a helpful supplement to the text, code examples demonstrating each of the best practices are available on the O'Reilly web site.Oracle PL/SQL Best Practices is intended as a companion to O'Reilly's larger Oracle PL/SQL books. It's a compact, readable reference that you'll turn to again and again--a book that no serious developer can afford to be without.

Table of contents

  1. A Note Regarding Supplemental Files
  2. Dedication
  3. Preface
    1. Structure of This Book
    2. How to Use This Book
    3. Not All Best Practices Are Created Equal
    4. About the Code
    5. Other Resources
    6. Conventions Used in This Book
    7. Comments and Questions
    8. Acknowledgments
  4. 1. The Development Process
    1. DEV-01: Set standards and guidelines before writing any code.
    2. DEV-02: Ask for help after 30 minutes on a problem.
    3. DEV-03: Walk through each other’s code.
    4. DEV-04: Validate standards against source code in the database.
    5. DEV-05: Generate code whenever possible and appropriate.
    6. DEV-06: Set up and use formal unit testing procedures.
    7. DEV-07: Get independent testers for functional sign-off.
  5. 2. Coding Style and Conventions
    1. STYL-01: Adopt a consistent, readable format that is easy to maintain.
    2. STYL-02: Adopt logical, consistent naming conventions for modules and data structures.
    3. STYL-03: Standardize module and program headers.
    4. STYL-04: Tag module END statements with module names.
    5. STYL-05: Name procedures with verb phrases and functions with noun phrases.
    6. STYL-06: Self-document using block and loop labels.
    7. STYL-07: Express complex expressions unambiguously using parentheses.
    8. STYL-08: Use vertical code alignment to emphasize vertical relationships.
    9. STYL-09: Comment tersely with value-added information.
    10. STYL-10: Adopt meaningful naming conventions for source files.
  6. 3. Variables and Data Structures
    1. 3.1. Declaring Variables and Data Structures
    2. 3.2. Using Variables and Data Structures
    3. 3.3. Declaring and Using Package Variables
  7. 4. Control Structures
    1. 4.1. Conditional and Boolean Logic
    2. 4.2. Loop Processing
    3. 4.3. Miscellaneous
  8. 5. Exception Handling
    1. EXC-00: Set guidelines for application-wide error handling before you start coding.
    2. 5.1. Raising Exceptions
    3. 5.2. Handling Exceptions
    4. 5.3. Declaring Exceptions
  9. 6. Writing SQL in PL/SQL
    1. SQL-00: Establish and follow clear rules for how to write SQL in your application.
    2. 6.1. General SQL and Transaction Management
    3. 6.2. Querying Data from PL/SQL
    4. 6.3. Changing Data from PL/SQL
    5. 6.4. Dynamic SQL and Dynamic PL/SQL
  10. 7. Program Construction
    1. 7.1. Structure and Parameters
    2. 7.2. Functions
    3. 7.3. Triggers
  11. 8. Package Construction
    1. PKG-01: Group related data structures and functionality together in a single package.
    2. PKG-02: Provide well-defined interfaces to business data and functional manipulation using packages.
    3. PKG-03: Freeze and build package specifications before implementing package bodies.
    4. PKG-04: Implement flexible, user-adjustable functionality using package state toggles and related techniques.
    5. PKG-05: Build trace “windows” into your packages using standardized programs.
    6. PKG-06: Use package body persistent data structures to cache and optimize data-driven processing.
    7. PKG-07: Insulate applications from Oracle version sensitivity using version-specific implementations.
    8. PKG-08: Avoid bloating package code with unnecessary but easy-to-build modules.
    9. PKG-09: Simplify and encourage module usage using overloading to widen calling options.
    10. PKG-10: Consolidate the implementation of related overloaded modules.
    11. PKG-11: Separate package specifications and bodies into different source code files.
    12. PKG-12: Use a standard format for packages that include comment headers for each type of element defined in the package.
  12. 9. Built-in Packages
    1. 9.1. DBMS_OUTPUT
    2. 9.2. UTL_FILE
    3. 9.3. DBMS_PIPE
    4. 9.4. DBMS_ JOB
  13. A. Best Practices Quick Reference
    1. A.1. The Development Process
    2. A.2. Coding Style and Conventions
    3. A.3. Variables and Data Structures
      1. Declaring Variables and Data Structures
      2. Using Variables and Data Structures
      3. Declaring and Using Package Variables
    4. A.4. Control Structures
      1. Conditional and Boolean Logic
      2. Loop Processing
      3. Miscellaneous
    5. A.5. Exception Handling
      1. Raising Exceptions
      2. Handling Exceptions
      3. Declaring Exceptions
    6. A.6. Writing SQL in PL/SQL
      1. General SQL and Transaction Management
      2. Querying Data from PL/SQL
      3. Changing Data from PL/SQL
      4. Dynamic SQL and Dynamic PL/SQL
    7. A.7. Program Construction
      1. Structure and Parameters
      2. Functions
      3. Triggers
    8. A.8. Package Construction
    9. A.9. Built-in Packages
      1. DBMS_OUTPUT
      2. UTL_FILE
      3. DBMS_PIPE
      4. DBMS_ JOB
  14. About the Author
  15. Colophon
  16. Copyright

Product information

  • Title: Oracle PL/SQL Best Practices
  • Author(s): Steven Feuerstein
  • Release date: April 2001
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596001216