Oracle PL/SQL Best Practices
Optimizing Oracle Code
1st Edition
Published on 9. April 2001
202 pages
978-0-596-51629-1 (ISBN)
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 "e;best practices"e;--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.
More details
Other editions
New editions
E-Book
10/2007
1st Edition
O'Reilly
€19.49
Available for download
Additional editions
Steven Feuerstein
Oracle PL/SQL Best Practices
Book
05/2001
O'Reilly
€16.00
Article exhausted; check for reprint
Content
- Intro
- Table of Contents
- 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
- 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 other's 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.
- 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.
- Variables and Data Structures
- 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.
- 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.
- 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.
- Control Structures
- 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.
- 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.
- Miscellaneous
- CTL-10: Use anonymous blocks within IF statements to conserve resources.
- CTL-11: Label and highlight GOTOs if using this normally unnecessary construct.
- Exception Handling
- EXC-00: Set guidelines for application-wide error handling before you start coding.
- 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/...
- EXC-03: Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQ...
- 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 inten...
- 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.
- 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 prog...
- Writing SQL in PL/SQL
- SQL-00: Establish and follow clear rules for how to write SQL in your application.
- 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 r...
- SQL-03: Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs (Oracle8i).
- 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).
- 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).
- 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.
- Program Construction
- 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 (Ora...
- 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.
- 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.
- 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...
- 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 implementati...
- 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 ...
- Built-in Packages
- DBMS_OUTPUT
- BIP-01: Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.
- UTL_FILE
- BIP-02: Improve the functionality and error handling of UTL_FILE by using a comprehensive encapsu...
- 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.
- 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.
- 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 accord...
- Best Practices Quick Reference
