
Oracle PL/SQL Best Practices
Description
Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.
More details
Other editions
New editions

Additional editions

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
System requirements
File format: PDF
Copy-Protection: Adobe-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Install the free reader Adobe Digital Editions prior to download (see eBook Help).
- Tablet/smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook before downloading (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (only limited: Kindle).
The file format PDF always displays a book page identically on any hardware. This makes PDF suitable for complex layouts such as those used in textbooks and reference books (images, tables, columns, footnotes). Unfortunately, on the small screens of e-readers or smartphones, PDFs are rather annoying, requiring too much scrolling.
This eBook uses Adobe-DRM, a „hard” copy protection. If the necessary requirements are not met, unfortunately you will not be able to open the eBook. You will therefore need to prepare your reading hardware before downloading.
Please note: We strongly recommend that you authorise using your personal Adobe ID after installation of any reading software.
For more information, see our eBook Help page.