Oracle PL/SQL Best Practices

Tell a friend

Oracle PL/SQL Best Practices, Second Edition By Steven Feuerstein
October 2007
Pages: 292

| Table of Contents | Index | Sample Chapter | Colophon

Chapter 1 The Big Picture
1. Successful Applications Are Never an Accident
2. Best Practices for Successful Applications
3. Software is like ballet: choreograph the moves or end up with a mess.
4. Deferred satisfaction is a required emotion for best practices.
5. Contracts work for the real world; why not software, too?
6. Don't act like a bird: admit weakness and ignorance.
7. Five heads are better than one.
8. Don't write code that a machine could write for you instead.
9. We need more than brains to write software.
Chapter 2 Real Developers Follow Standards
1. Best Practices for Developing and Using Standards
2. It's a free country; I don't have to use carriage returns in my code.
3. Too much freedom is a very bad thing.
4. Good names lead to good code.
5. Put your checklists into your code.
6. Who needs comments? My code is self-documenting!
Chapter 3 Life After Compilation
1. Testing, Tracing, and Debugging
2. Best Practices for Testing, Tracing, and Debugging
3. Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!
4. Assume the worst, and you will never be disappointed.
5. Users really don't want to be programmers.
6. Do you take road trips without a destination in mind?
7. For every test you can think of, there are 10 tests waiting to be performed.
8. Sherlock Holmes never had it so good.
Chapter 4 What's Code Without Variables?
1. Best Practices for Declaring Variables and Data Structures
2. That column's never going to change!
3. There's more to data than columns in a table.
4. I take exception to your declaration section.
5. Best Practices for Using Variables and Data Structures
6. This logic is driving me crazy!
7. Go ahead and splurge: declare distinct variables for different usages.
8. Didn't your parents teach you to clean up after yourself?
9. Programmers are (or should be) control freaks.
10. Best Practices for Declaring and Using Package Variables
11. Danger, Will Robinson! Globals in use!
12. Packages should have a strong sense of personal space.
Chapter 5 Developer As Traffic Cop
1. Best Practices for Conditional and Boolean Logic
2. Reading your code should not require mental gymnastics.
3. KISS (Keep it Simple, Steven).
4. Beware the hidden costs of NULL.
5. Best Practices for Loop Processing
6. There's a right way and a wrong way to say goodbye.
7. Don't take out "programmers' insurance" . . . and don't worry about SkyNet.
8. There is more than one way to scan a collection.
9. Best Practices for Branching Logic
10. Maze-like programs are never a good thing.
Chapter 6 Doing the Right Thing When Stuff Goes Wrong
1. Best Practices for Understanding Error Handling
2. Ignorance is bad exception management.
3. All exceptions are not created equal.
4. One error management approach for all.
5. Best Practices for Nitty-Gritty, Everyday Exception Programming
6. Your code makes me feel dumb.
7. Avoid programmer apathy.
8. Best Practices for Coding Defensively
9. You weren't supposed to do that with my program!
Chapter 7 Break Your Addiction to SQL
1. SQL Is Bad!
2. General SQL Best Practices
3. The best way to avoid problematic code is to not write it.
4. You may write PL/SQL code, but SQL always takes precedence.
5. When one transaction is not enough.
6. I don't always want to save my changes.
7. Best Practices for Querying Data from PL/SQL
8. It's always better to fetch items into a single basket.
9. Answer the question being asked; that is, be a good listener.
10. Your code makes my head spin.
11. Best Practices for Changing Data from PL/SQL
12. Assume the worst!
13. Things only get more complicated over time.
14. Timing is everything in the world of cursors.
15. Best Practices for Dynamic SQL
16. Make it easy to untangle and debug your dynamic SQL statements.
17. Give the RDBMS a break.
18. So you think you know what users might do with your code?
19. It's rude to drop someone else's objects.
Chapter 8 Playing with Blocks (of Code)
1. Best Practices for Parameters
2. Once a program is in use, you can't change it willy-nilly.
3. What the heck do those parameter values mean?
4. Where'd that data come from?
5. Best Practices for Procedures and Functions
6. Write tiny chunks of code.
7. Every program should be an island (of purpose).
8. Gifts should always come tightly wrapped.
9. One way in, one way out: multiple exits confuse me.
10. Black or white programs don't know from NULL.
11. Best Practices for Packages
12. Where there is one program, there will soon be two.
13. "Easy to use code" is code that is used—and reused.
14. Best Practices for Triggers
15. Uncertainty in trigger execution is a most unsettling emotion.
16. "One-stop triggering" is so much easier to understand and maintain.
17. Your application should not be able to perform a "Houdini" with business rules.
Chapter 9 My Code Runs Faster Than Your Code
1. Best Practices for Finding Slow Code
2. Take the guesswork out of optimization.
3. There are so many ways to implement an algorithm; which is best?
4. Best Practices for High-Impact Tuning
5. Let Oracle do most of the tuning for you.
6. Who has time for querying (or inserting or deleting or updating) one row at a time?
7. If the SGA is so wonderful, why not emulate it?
8. When waiting is not an option...
9. Best Practices for Other Tuning
10. Sometimes "safe programming" is a little too slow.
11. PL/SQL loops should not resemble hamsters running in circles.
12. Tailor-made datatypes are the best fit for your programs.

Appendix Best Practices Quick Reference
Appendix Resources for PL/SQL Developers
1. Book Example Files
2. Books and Other Content
3. Free Software
Colophon

Return to Oracle PL/SQL Best Practices

Table of Contents

Chapter 1 The Big Picture

Successful Applications Are Never an Accident

Best Practices for Successful Applications

Software is like ballet: choreograph the moves or end up with a mess.

Deferred satisfaction is a required emotion for best practices.

Contracts work for the real world; why not software, too?

Don't act like a bird: admit weakness and ignorance.

Five heads are better than one.

Don't write code that a machine could write for you instead.

We need more than brains to write software.

Chapter 2 Real Developers Follow Standards

Best Practices for Developing and Using Standards

It's a free country; I don't have to use carriage returns in my code.

Too much freedom is a very bad thing.

Good names lead to good code.

Put your checklists into your code.

Who needs comments? My code is self-documenting!

Chapter 3 Life After Compilation

Testing, Tracing, and Debugging

Best Practices for Testing, Tracing, and Debugging

Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!

Assume the worst, and you will never be disappointed.

Users really don't want to be programmers.

Do you take road trips without a destination in mind?

For every test you can think of, there are 10 tests waiting to be performed.

Sherlock Holmes never had it so good.

Chapter 4 What's Code Without Variables?

Best Practices for Declaring Variables and Data Structures

That column's never going to change!

There's more to data than columns in a table.

I take exception to your declaration section.

Best Practices for Using Variables and Data Structures

This logic is driving me crazy!

Go ahead and splurge: declare distinct variables for different usages.

Didn't your parents teach you to clean up after yourself?

Programmers are (or should be) control freaks.

Best Practices for Declaring and Using Package Variables

Danger, Will Robinson! Globals in use!

Packages should have a strong sense of personal space.

Chapter 5 Developer As Traffic Cop

Best Practices for Conditional and Boolean Logic

Reading your code should not require mental gymnastics.

KISS (Keep it Simple, Steven).

Beware the hidden costs of NULL.

Best Practices for Loop Processing

There's a right way and a wrong way to say goodbye.

Don't take out "programmers' insurance" . . . and don't worry about SkyNet.

There is more than one way to scan a collection.

Best Practices for Branching Logic

Maze-like programs are never a good thing.

Chapter 6 Doing the Right Thing When Stuff Goes Wrong

Best Practices for Understanding Error Handling

Ignorance is bad exception management.

All exceptions are not created equal.

One error management approach for all.

Best Practices for Nitty-Gritty, Everyday Exception Programming

Your code makes me feel dumb.

Avoid programmer apathy.

Best Practices for Coding Defensively

You weren't supposed to do that with my program!

Chapter 7 Break Your Addiction to SQL

SQL Is Bad!

General SQL Best Practices

The best way to avoid problematic code is to not write it.

You may write PL/SQL code, but SQL always takes precedence.

When one transaction is not enough.

I don't always want to save my changes.

Best Practices for Querying Data from PL/SQL

It's always better to fetch items into a single basket.

Answer the question being asked; that is, be a good listener.

Your code makes my head spin.

Best Practices for Changing Data from PL/SQL

Assume the worst!

Things only get more complicated over time.

Timing is everything in the world of cursors.

Best Practices for Dynamic SQL

Make it easy to untangle and debug your dynamic SQL statements.

Give the RDBMS a break.

So you think you know what users might do with your code?