Monday, December 19, 2005

20 PL/SQL Coding Tips

Gary pointed me to a recent AskTom thread that contained a list of PL/SQL Coding Practises. The ensuing discussion added a few more, and I'd like to throw in a few of my own as well. I also think most of these tips are useful for other languages, but this is first and foremost an Oracle developer's blog.

Here they are, with the order and wording slightly revised in some cases (removed the biblical undertones for politically correct reasons), and my comments after each one. Each of these could be a separate blog on their own, but I restrained myself to only brief comments.

Design (Pre-Coding):

1. Ask Why

A popular philosophy in the Oracle blogging community, and championed by Tom Kyte, is the notion of asking why. Why is this code being written, why is it being done the way that it is, etc.

From Tom Kyte's Blog: http://tkyte.blogspot.com/2005/05/why.html

2. Gather requirements thoroughly

Based on the experiences of many, gather all the requirements and do not begin anything until all parties have agreed on what the end result will have to be in order to be considered successful.

3. Design, including for scalability and security, first.

With the exception of quasi-prototypes and small proofs of concept, no coding should begin until it has been designed. This is the time to think about scalability and security. Those aren't issues to tackle after the fact.

4. Set up proper testing/development/debugging environment

I find that developers are far more effective if they have a reliable, accurate and convenient environment for testing and debugging. Also, it is usually better to set that up first, rather than patching something together on the fly.

5. Use source control

"Consider source control non-optional, and a factor on which your job depends." - a former director of mine. That simplifies your decision, doesn't it?

6. Choose the right tools

Notepad and SQL*Plus might work for you now, but for a larger project you might want to consider a different IDE. You'll also want to look at source control systems, and code libraries.

7. Write test cases before coding

It seems like many people agree on this rule, but consider it a fantasy because how often do you see this done in practise? Ideally you would do this right after #2 (gather requirements thoroughly). That way you can say "when these tests pass, you are done."

Coding:

8. Check and handle errors

Up front, decide a common way of handling errors in the EXCEPTIONS block. You can figure out what works best, customer error types, an error table, an error file, whatever works as long as you are checking and handling all errors.

For starters, check Chapter 7 of the PL/SQL User's Guide and Reference: Handling PL/SQL Errors:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624.pdf

9. Comment your code

This one resulted in a lot of debate on the thread. No, commenting your code is not a substitute for making your code readable, but then again, neither is making your code readable a substitute for commenting. Indeed, I agree with the one suggestion that you should write your comments BEFORE you write the code!

10. Use proper indexes

11. Maximise SQL and minimise PL/SQL

There are books written on the efficiency gain of preferring SQL over PL/SQL.

12. Instrument your code for debugging

Choose your favourite method: debug statements judiciously places throughout your code, or perhaps using DBMS_PROFILE (that is definitely a topic I'll do soon), or something else entirely. As long as you have a way to troubleshoot bugs and performance issues later on.

13. Make use of bulk processing

14. Minimise client code and maximise server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

15. Use bind variables, not concatenation

Not just for performance reasons, but also for security (thwart SQL injection). I think Tom Kyte made his living off this topic for awhile.

I blogged about bind variables once: http://thinkoracle.blogspot.com/2005/06/bind-variables-in-plsql.html

16. Think in sets.

17. Use procedures/functions to name and modularize your code for reuse

Don't just create one big massive function that does many, specific things. Best to break it out into specific tasks, which can be optimized and reused. Also makes you think more about your detailed design.

18. Use unique and meaningful names

Unique names can be found easier in the code with "Find". Meaningful names make your code easier to understand. If you can't think of a meaningful name for a procedure/variable, maybe you don't have a clear idea on its purpose and you need to re-think your design.

Coding and Afterwards:

19. Stress test your code with significant data, and benchmark it

Doesn't matter how much time you spent thinking about performance and bottlenecks during the design and implementation, you might still have missed a few things.

20. Perform a code review with your peers.

Code reviews are good to find mistakes, but also for knowledge transfer. I also think you take more pride in your work when you have the opportunity to share it, rather than just hide in a cubicle as an anonymous coder.

Comments are welcome, but I also encourage you to visit the thread and follow up to the wider audience there:
http://asktom.oracle.com/pls/ask/f?p=4950:8:18231831513891656743::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:51960184066540


Steven Feuerstein is also one of my favourite champions on good PL/SQL programming. Here is a previous blog on one of his articles on refactoring (the link to which is within):
http://thinkoracle.blogspot.com/2005/05/steven-feuerstein-on-refactoring.html

Comments:
Rob,

This blog is quite timely for me because if the developers of the application I'm working on had read this, they might have followed tips 13 and 14 and avoided some of the problems we're running into.

http://oracledoug.blogspot.com/2005/12/another-10046-success.html

Cheers,

Doug
 
Hi,
to 17. Use procedures/functions to name and modularize your code for reuse
i would say - don not use singel functions/procedures at all but use Packages instead to
organize as set of function/Procedures and types to a design aspect.


Further i would like to say - the data dicitonary is a repository. Out of an repository
you can generate code for db objects as Triggers/Views/Tale API's, Packages .. with PL/SQL;

An important pont doing a database oriented project is the dployment of the db objects.
The Use of PL/SQL helps to automate something better then a pure SQL*PLUs SQL-Script

Very important too is a PL/SQL code beautifier - helping better to understand the logic behind.

Regards
Karl
 
Karl,

Excellent points! I will definitely incorporate those into a revised version next week.

I can't believe I overlooked using packages, after all, I blogged on this recently:
http://thinkoracle.blogspot.com/2005/10/oracle-packages.html

Doug:

I think you posted your comment before my blog was even posted! That's fast!
 
Rob, great expansion on the original :-)
Cosmin
 
This comment has been removed by a blog administrator.
 
Bit late maybe, but I am glad my Georg Cantor comment made it in your top 20.
 
Regarding:

================================
19. Stress test your code with significant data, and benchmark it

Doesn't matter how much time you spent thinking about performance and bottlenecks during the design and implementation, you might still have missed a few things.
================================

Check out the following options for semi-automated and automated testing:

utPLSQL - the original open source framework based on the xUnit framework. I wrote the original version back in 1999 but never really used it because you have to write so much code (same problem with Junit).

PL/Unit and PLUTO - light and object-oriented versions of utPLSQL

dbFit - a tabular script approach based on the FitNess framework

Quest Code Tester for Oracle - the first commercial automated testing product. I designed and have been involved in development of this tool since its inception. It offers the greatest level of automation of any of these tools.

Hope that helps,
Steven Feuerstein
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
Man this post gets a lot of spam comments!
 
I am thoroughly convinced in this said post. I am currently searching for ways in which I could enhance my knowledge in this said topic you have posted here. It does help me a lot knowing that you have shared this information here freely. I love the way the people here interact and shared their opinions too. I would love to track your future posts pertaining to the said topic we are able to read.
 
Very useful info and good tips...thank you..


http://sql-videotutorials.blogspot.com/
 
Thanks for sharing useful information.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?