Oracle PL/SQL Best Practices
Optimizing Oracle Code
Please login to add notes
- Table of Contents
- + Preface
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
- + Declaring Variables and Data Structures
- + Conditional and Boolean Logic
- 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
- EXC-00: Set guidelines for application-wide error handling before you start coding.
- 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...
- 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
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.
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-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.
- + Structure and Parameters
- 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 ...
- + DBMS_OUTPUT
- 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
- + DBMS_JOB
- Best Practices Quick Reference
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.
Test the closed alpha on paperc.com