Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 1.6 A Few of My Favorite (PL/SQL) ThingsChapter 1
Introduction to PL/SQL
Next: 2. PL/SQL Language Fundamentals

1.7 Best Practices for PL/SQL Excellence

Since the publication of the first edition of this book, I have had the pleasure of presenting my own relatively idiosyncratic approach to building PL/SQL-based applications to thousands of developers. I have also spent an increasingly large percentage of my time writing complex PL/SQL packages. In the process, I have honed my sense of what we all need to do to write excellent PL/SQL programs which will "stand the test of time." I have, in fact, become somewhat dogmatic about these principles or "best practices," but if not me, then who?

In this second edition, I've decided to share some of my thoughts on PL/SQL best practices, in very concentrated form, to enhance your reading of the book and to give you food for thought as you venture forth with your own development projects. This is by no means a comprehensive list, but I hope it will be a good start for the construction of your own best practices.

1.7.1 Write as Little Code as Possible

If you can use a program that someone else wrote -- someone you trust to have written it well and tested it thoroughly -- why would you want to write it yourself? Seems obvious, doesn't it? The less code you yourself write, the less likely it is that you will introduce bugs into your application, and the more likely it is that you will meet deadlines and stay within budget.

The basic PL/SQL language offers tons of functionality; you need to get familiar with the built-in functions so you know what you don't have to write. At most of my trainings, I ask the attendees how many arguments the INSTR function has. Most people figure there are two (the string and the substring). A few raise their hands for three, and a special one or two believe in four arguments for INSTR -- four is the correct answer. If you don't know that INSTR has four arguments, then you don't really know what INSTR does -- and can do -- for you. Investigate and discover!

Then there are the built-in packages, which greatly expand your horizons. These packages allow you to do things otherwise impossible inside PL/SQL, such as executing dynamic SQL, DDL, and PL/SQL code (DBMS_SQL), passing information through database pipes (DBMS_PIPES), and displaying information from within a PL/SQL program (DBMS_OUTPUT). It is no longer sufficient for a developer to become familiar simply with basic PL/SQL functions like TO_CHAR, ROUND, and so forth. Those functions have now become merely the innermost layer of useful functionality that Oracle Corporation has built upon (as should you). To take full advantage of the Oracle technology as it blasts its way to the 21st century, you must be aware of these packages and how they can help you.

Finally, as the PL/SQL marketplace matures, you will find that you can choose from prebuilt, third-party libraries of PL/SQL code, probably in the form of packages. These code libraries might perform specialized calculations or they might offer relatively generic extensions to the base PL/SQL language. As of the fall of 1997, there is just one commercially available PL/SQL library, PL/Vision from RevealNet (which I wrote). Soon, there will be more. Search the Web and check the advertisements in Oracle Magazine to find out what is available, so that you can avoid reinventing the wheel.

As you are writing your own code, you should also strive to reduce your code volume. Here are some specific techniques to keep in mind:

1.7.2 Synchronize Program and Data Structures

Data analysts, data modelers, and database administrators go to great lengths to get the structures in the database just right. Standards for entity and attribute names, referential integrity constraints, database triggers, you name it: by the time PL/SQL developers get to work, there is (or should be) a solid foundation for their work.

Problem is, whenever you code a SQL statement in your application, you are hardcoding data structures and relationships into your program. What happens when those relationships change? Unless you take special precautions, your program will break. You will spend way too much of your time maintaining existing applications. Your managers will look at you funny when you have to make up all sorts of lame excuses for widespread breakdowns of code resulting from the simplest database change.

Protect your code and your reputation. As much as possible, you want to write your code so that it will "automagically" adapt to changes in underlying data structures and relationships. You can do this by taking the following steps:

This last suggestion will have the greatest impact on your applications, but it is also among the most difficult to implement. To accomplish this goal (always execute SQL statements through a procedural interface), you will want to generate packages automatically for a table or view. This is the only way to obtain the consistency and code quality required for this segment of your application code. By the time this second edition is published, you should be able to choose from several different package generators. You can also build your own.

1.7.3 Center All Development Around Packages

Little did I know when I wrote the first edition of this book how much more I was to learn about PL/SQL -- and most of it was about packages. You should center all your PL/SQL development effort around packages. Don't build standalone procedures or functions unless you absolutely have to (some frontend tools cannot yet recognize the package syntax of dot notation: package.program). Expect that you will eventually construct groups of related functionality and start from the beginning with a package.

The more you use packages, the more you will discover you can do with them. The more you use packages, the better you will become at constructing clean, easy-to-understand interfaces (or APIs) to your data and your functionality. The more you use packages, the more effectively you will encapsulate acquired knowledge and then be able to reapply that knowledge at a later time -- and share it with others.

My second book, Advanced Oracle PL/SQL Programming with Packages, offers a detailed set of "best practices" for package design and usage; highlights follow:

1.7.4 Standardize Your PL/SQL Development Environment

When you get right down to it, programming consists of one long series of decisions punctuated by occasional taps on the keyboard. Your productivity is determined to a large extent by what you spend your time making decisions on. Take some time before you start your programming effort to set up standards among a wide variety of aspects. Here are some of my favorite standards, in no particular order:

1.7.5 Structured Code and Other Best Practices

Once you get beyond the "big ticket" best practices, there are many very concrete recommendations for how to write specific lines of code and constructs. Many of these suggestions have been around for years and apply to all programming languages. So if you took a good programming class in college, for example, don't throw away those books! The specific syntax may change, but the fundamental common sense motivation for what you have learned in the past will certainly work with PL/SQL as well.

Without a doubt, if you can follow these guidelines, you are sure to end up with programs which are easier to maintain and enhance:

There is lots more I could say about best practices for PL/SQL development, especially concerning the application of new Oracle8, object-oriented features. But if you follow the ideas I offer in this section, you will be writing code that is superior to just about everyone else's on this strange planet. on!

Previous: 1.6 A Few of My Favorite (PL/SQL) ThingsOracle PL/SQL Programming, 2nd EditionNext: 2. PL/SQL Language Fundamentals
1.6 A Few of My Favorite (PL/SQL) ThingsBook Index2. PL/SQL Language Fundamentals

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference