Oracle PL/SQL Best Practices

Name: Oracle PL/SQL Best Practices | Optimizing Oracle Code
Brand: O'Reilly
Price: 25.39 EUR
Availability: OnlineOnly

Optimizing Oracle Code

Steven Feuerstein(Autor*in)

O'Reilly (Verlag)

1. Auflage

Erschienen am 9. April 2001

202 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-0-596-51629-1 (ISBN)

25,39 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Zur neuen Auflage

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

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

Systemvoraussetzungen

Als PDF speichern Als Link merken