Oracle PL/SQL Best Practices

Name: Oracle PL/SQL Best Practices | Write the Best PL/SQL Code of Your Life
Brand: O'Reilly
Price: 19.49 EUR
Availability: OnlineOnly

Write the Best PL/SQL Code of Your Life

Steven Feuerstein(Author)

O'Reilly (Publisher)

1st Edition

Published on 22. October 2007

294 pages

E-Book

ePUB with Adobe-DRM

System requirements

978-0-596-55152-0 (ISBN)

€19.49incl. 7% vat

System requirements

for ePUB with Adobe-DRM

E-Book Single Licence

Available for download

Description

Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.

Alles über E-Books, Kopierschutz & Dateiformate finden Sie in unserem Info- & Hilfebereich.

In this compact book, Steven Feuerstein, widely recognized as one of the world's leading experts on the Oracle PL/SQL language, distills his many years of programming, teaching, and writing about PL/SQL into a set of best practices-recommendations for developing successful applications. Covering the latest Oracle release, Oracle Database 11gR2, Feuerstein has rewritten this new edition in the style of his bestselling Oracle PL/SQL Programming. The text is organized in a problem/solution format, and chronicles the programming exploits of developers at a mythical company called My Flimsy Excuse, Inc., as they write code, make mistakes, and learn from those mistakes-and each other.This book offers practical answers to some of the hardest questions faced by PL/SQL developers, including:What is the best way to write the SQL logic in my application code?How should I write my packages so they can be leveraged by my entire team of developers?How can I make sure that all my team's programs handle and record errors consistently?Oracle PL/SQL Best Practices summarizes PL/SQL best practices in nine major categories: overall PL/SQL application development; programming standards; program testing, tracing, and debugging; variables and data structures; control logic; error handling; the use of SQL in PL/SQL; building procedures, functions, packages, and triggers; and overall program performance.This book is a concise and entertaining guide that PL/SQL developers will turn to again and again as they seek out ways to write higher quality code and more successful applications.&quote;This book presents ideas that make the difference between a successful project and one that never gets off the ground. It goes beyond just listing a set of rules, and provides realistic scenarios that help the reader understand where the rules come from. This book should be required reading for any team of Oracle database professionals.&quote;--Dwayne King, President, KRIDAN Consulting

More details

Other editions

Content

Intro
Table of Contents
Preface
Why Best Practices?
Best Practices and the Real World
They Call This "Work"?
The Cast of Characters
Structure of This Book
How to Use This Book
Conventions Used in This Book
About the Code
Comments and Questions
Safari Books Online
Acknowledgments
The Big Picture
Successful Applications Are Never an Accident
Successful Applications Meet User Requirements
Successful Applications Are Maintainable
Successful Applications Run Fast Enough
Best Practices for Successful Applications
Problem: In software, the ends (production code) are inseparable from the means (the build process).
Solution: Agree on a common development workflow built around standards, testing, and reviews.
Application-level workflow
Single-program construction workflow
Step 1. Validate program requirements
Step 2. Implement just the header of the program
Step 3. Define the tests that must be run
Step 4. Build your test code
Problem: What is never discussed is never agreed upon.
Solution: Contracts capture agreements.
Problem: Steven is a hypocritical programmer.
Solution: Give your brain a break, and ask others for help.
Problem: Sunita spent six months developing comprehensive coding standards for her group.
Solution: Move beyond documents to a review process that directly engages the development team.
Resources
Problem: Jasper is starting to feel more like a robot than a human being.
Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.
Real Developers Follow Standards
Best Practices for Developing and Using Standards
Problem: Delaware writes code that no one else can read.
Solution: Use the built-in functionality of your IDE to automatically format your code.
Problem: Jasper's eagerness to help is overwhelmed by his hurry to get it done.
Solution: Rely on naming conventions that are intuitive and easy to apply.
Problem: Badly formed or inaccurate names can greatly reduce usability of programs.
Solution: Construct subprogram names so they reflect both what they are and what they do.
Problem: Checklists on paper rarely translate into changes in the way we write our code.
Solution: Make your checklists active and directly involved in development.
Resources
Problem: One person's clarity is another person's bewilderment.
Solution: Sometimes you really do need to add comments.
Life After Compilation
Testing, Tracing, and Debugging
Testing
Tracing
Debugging
Best Practices for Testing, Tracing, and Debugging
Problem: DBMS_OUTPUT.PUT_LINE is inadequate for tracing.
Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.
Problem: We live in the moment, and don't think about what it will take to maintain our code.
General Solution: Build instrumentation (tracing) into your application from the start.
Specific Solution 1: Embed trace calls in Boolean expressions to minimize overhead.
Specific Solution 2: Include standardized modules in packages to dump package state when errors o...
Specific Solution 3: Build trace "windows" into your packages using standardized programs.
Problem: Sunita's team is dragged down off its pedestal of semi-godliness.
General Solution: Don't make testing an option, and don't just "try" a few things.
Specific Solution 1: A brief introduction to utPLSQL.
Specific Solution 2: A brief introduction to Quest Code Tester for Oracle.
Resources
Problem: We cannot trust our own minds to fully and objectively test our code.
Solution: Decide before you implement your program how you will know when it works correctly.
Resources
Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.
Solution: When it comes to testing, be happy with getting started and with steady progress.
Problem: Jasper writes some complicated code and has no idea what it is doing.
Solution: Use the built-in, interactive source debuggers available in almost every PL/SQL IDE.
What's Code Without Variables?
Best Practices for Declaring Variables and Data Structures
Problem: Lizbeth writes a "quick-and-dirty" program.
Solution: Assume that everything will change and that any program you write could be around for d...
Problem: Lizbeth learns her lesson but then cannot apply it.
Solution: Create a new datatype with SUBTYPE and anchor to that.
Problem: The exception section of a block can only trap errors raised in the execution section.
Solution: Don't trust the declaration section to assign default values.
Best Practices for Using Variables and Data Structures
Problem: Business rules can be complicated, and it's hard to keep them straight.
Solution: Simplify code to make the criteria for the business rules more obvious.
Problem: World weariness infects Lizbeth's code.
Solution: Don't let your weariness show in your code-and don't recycle!
Problem: Sometimes you really do need to clean up in a PL/SQL block.
Resources
Problem: PL/SQL performs implicit conversions-but they're not always what you want.
Solution: Perform explicit conversions rather than relying on implicit conversions.
Resources
Best Practices for Declaring and Using Package Variables
Problem: Jasper needs Lizbeth's program data. Delaware needs Jasper's program data.
Solution: Don't expose program data in package specifications, letting everyone see and change it.
Problem: Data structures declared in a package specification may end up bypassing business rules.
Solution: Declare data in the package body, and hide the data structures via functions in the pac...
Developer As Traffic Cop
Best Practices for Conditional and Boolean Logic
Problem: IF-statement logic can become complex and confusing.
Solution: Simplify by specifying clauses at multiple levels.
Problem: Lizbeth needs to construct a string conditionally from a number of pieces.
Solution: CASE will greatly simplify the code.
Solution: Don't forget the ELSE in the CASE!
Problem: Why is Lizbeth's IF statement doing that?
Solution: Remember that a NULL is not equal to anything else, even another NULL, and code explici...
Best Practices for Loop Processing
Problem: Jasper writes a loop that offers many exit paths.
Solution: One way in, one way out.
Problem: The previous developer wrote some code just in case.
Solution: Cyberspace is a world of our making. Remove all uncertainty from your code.
Problem: It's so hard to write code without making assumptions!
Solution: Write "full collection scans" so that there is no assumption about how the collection i...
Best Practices for Branching Logic
Resources
Doing the Right Thing When Stuff Goes Wrong
Best Practices for Understanding Error Handling
Problem: Know-it-alls don't know the most important thing: they're wrong!
Solution: Take some time to familiarize yourself with how PL/SQL exception management works and w...
Resources
Deliberate exceptions
Unfortunate and unexpected exceptions
How to benefit from this categorization
Problem: Application logic in the exception is hard to find and maintain.
Solution: Avoid application logic in the exception section.
Problem: Unhandled unfortunate exceptions make your code less flexible and more difficult to reuse.
Solution: Transform the exception to a status indicator that can be interpreted by the user of th...
Problem: Unhandled unexpected errors cause a loss of information about the cause of the error.
Solution: Handle those unexpected, "hard" errors and then re-raise the exception.
In conclusion . . .
General Problem: Chaos reigns!
Specific Problem 1: Get word back to the users.
Specific Problem 2: How do I log my error? Let me count the ways . . .
General Solution: One component, under source control, for all to use.
Specific Solution 1: No more RAISE_APPLICATION_ERROR.
Specific Solution 2: Use declarative error handler routines.
Resources
Best Practices for Nitty-Gritty, Everyday Exception Programming
Problem: A little bit of laziness and a slight dose of advanced features can ruin a good program.
Solution: Avoid writing "clever" code that shows just how much you know and how smart you are.
Problem: The "I don't care" exception handler can cover up problems too indiscriminately.
Solution: Add value in WHEN OTHERS: log information and re-raise some exception or other!
Best Practices for Coding Defensively
Problem: Delaware expects everyone to be a know-it-all like him.
Solution: Assume nothing! Make all assumptions explicit, and then validate them.
Resources
Break Your Addiction to SQL
SQL Is Bad!
Step 1. Hardcoding is bad.
Step 2: Every SQL statement you write is a hardcoding.
Step 3. Draw the logical conclusion.
General SQL Best Practices
Problem: PL/SQL developers are addicted to SQL!
Solution: Never repeat a SQL statement
instead, implement SQL behind procedures and functions.
Problem: Global search-and-replace to the rescue!
Solution: Always check after global search-and-replace, and qualify all variable references.
Problem: Your error log entries have disappeared!
Solution: Save your log information separately from your business transaction logic.
Problem: Is everything Lizbeth does in her programs some kind of hardcoding?
Solution: Call your own program to do the commit (or rollback), and make it more flexible.
Resources
Best Practices for Querying Data from PL/SQL
Problem: A change in one place affects many others-never a good idea.
Solution: Skip all those declarations and replace them with a single record.
Problem: Delaware is not a good listener.
Solution: Use COUNT only when you need to know "How many rows?"
Problem: Jasper chooses the wrong time to be lazy.
Solution: Use the cursor FOR loop only when fetching multiple rows.
Best Practices for Changing Data from PL/SQL
Problem: Jasper knows what he's supposed to do. He just can't bring himself to do it.
Solution: Make sure that any errors that can be anticipated are logged and communicated to the user.
Problem: It's hard to think about what a table will be like in the future.
Solution: Always explicitly list the columns that are part of the INSERT statement.
Problem: I check the contents of the SQL%ROWCOUNT too late in the game.
Solution: Remember that SQL% attributes always refer to the most recently executed implicit curso...
Best Practices for Dynamic SQL
Problem: There's something wrong with Lizbeth's dynamic SQL, but she can't figure it out.
Solution: Make sure your exception section can display the string that failed to parse.
Problem: You have lots of dynamic SQL updates, with different values each time.
Solution: Bind, don't concatenate, to optimize performance and simplify dynamic string construction.
Problem: So many teenagers, so many ways to enter data at the web site.
Solution: Avoid concatenation of SQL text, rely on bind variables, and secure your schemas.
Problem: A seemingly handy utility goes badly wrong.
Solution: Make sure your dynamic SQL programs run under the invoker's authority.
Resources
Playing with Blocks (of Code)
Best Practices for Parameters
Problem: Lizbeth's program needs to do more, and do it differently.
Solution: Make sure all new IN arguments have defaults, or add an overloading.
Problem: What seems obvious at the moment of writing is far less clear months or years later.
Solution: Use named notation to make everything clear.
Problem: Jasper returns data in a very confusing manner.
Solution: Return multiple values through a single, composite structure or with a procedure.
Best Practices for Procedures and Functions
Problem: Lengthy blobs of spaghetti code are unmanageable.
Solution: Use step-wise refinement and local subprograms to make code transparent in purpose and ...
There's more to tiny chunks than just local subprograms.
Resources
Problem: Delaware packs it all in and no one wants to use it.
Solution: Write programs with very specific purposes and avoid hidden (a.k.a. global) dependencies.
Problem: Jasper actually thinks a rule will never change!
Solution: Wrap or hide all business rules and formulas inside functions.
Problem: Sunita tosses off "quick-and-dirty" code in a function lookup routine.
Problem: Sure, the program works-but only if you assume a perfect world.
Solution: Don't allow multiple exit points from the function.
Problem: Jasper gets really confused with a Boolean function returning a NULL.
Solution: Ensure that a Boolean function returns only TRUE or FALSE.
Best Practices for Packages
Problem: It seemed that there would be only one program-now there are many.
Solution: Put in the dot from the start: package.subprogram.
Problem: Some of Oracle's supplied packages are harder to use than they should be.
Solution: Create multiple programs with the same name (overloading) that anticipate user needs.
Best Practices for Triggers
Problem: Seemingly random trigger behavior is driving Jasper nuts.
Solution: Consolidate "same event" triggers or use the FOLLOWS clause.
Resources
Problem: Lizbeth has created a dozen triggers and a support package on a critical database table.
Solution: Consolidate all logic into a compound trigger, and lose the package if it is present on...
Problem: There's more than one way to make an excuse.
Solution 1: Apply the business rule at the lowest level possible, to ensure that it cannot be avo...
Solution 2: Populate columns of derived values with triggers.
My Code Runs Faster Than Your Code
Best Practices for Finding Slow Code
PL/SQL Profiler (DBMS_PROFILER)
PL/SQL Trace (DBMS_TRACE)
PL/SQL Hierarchical Profiler (DBMS_HPROF)
Application Data Profiler (DBMS_APPLICATION_INFO)
Problem: There are hundreds of ways to implement a requirement. How can Lizbeth find the fastest ...
Solution: Use DBMS_UTILITY.GET_TIME and GET_CPU_TIME for very granular analysis.
Resources
Best Practices for High-Impact Tuning
Problem: It worked so quickly in SQL*Plus. Why is it so slow in PL/SQL?
Solution: Sometimes you have to help PL/SQL integrate with SQL.
Problem: Jasper queries the same, unchanging data over and over and over again.
Solution: If the normal SGA cache doesn't the trick, look for other ways to cache.
Problem: Users hate to wait.
Solution: Design your function to return data while it is still running!
And that's not all!
Best Practices for Other Tuning
Problem: Delaware wants to use collections as parameters, but they are causing a bottleneck.
Solution: Turn off the default copying that comes with IN OUT arguments.
Problem: Lizbeth lost her focus as she wrote her loop.
Solution: Avoid executing anything inside a loop that doesn't change in that loop.
Problem: Oracle goes out of its way to make it easy for us to write PL/SQL programs.
Solution: Avoid implicit conversions and choose your datatypes carefully.
Best Practices Quick Reference
Resources for PL/SQL Developers
Book Example Files
Books and Other Content
Oracle PL/SQL Books
Other Helpful Books
Online Content
Free Software
Index

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

Oracle PL/SQL Best Practices

Description

More details

Other editions

Additional editions

Previous edition

Content

System requirements