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:

• Use the cursor FOR loop. Whenever you need to read through every record fetched by a cursor, the cursor FOR loop will save you lots of typing over the "manual" approach of explicitly opening, fetching from, and closing the cursor.

• Work with records. Certainly, whenever you fetch data from a cursor, you should fetch into a record declared against that cursor with the %ROWTYPE attribute (covered in next section in more detail). But if you find yourself declaring multiple variables which are related, declare instead your own record TYPE. Your code will tighten up and will more clearly self-document relationships.

• Use local modules to avoid redundancy and improve readability. If you perform the same calculation twice or more in a procedure, create a function to perform the calculation and then call that function twice instead.

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:

• Anchor declarations of variables back to the database tables and columns they represent. Whenever you declare a variable which has anything to do with a database element, use the %TYPE or % ROWTYPE declaration attributes to define the datatype of those structures. If those database elements change, your compiled code is discarded. When recompiled, the changes are automatically applied to your code.

• Always fetch from an explicit cursor into a record declared with %ROWTYPE, as opposed to individual variables. Assuming that you followed my last piece of advice, that cursor is declared in a package. That cursor may, therefore, be changed without your knowledge. Suppose that another expression is added to the SELECT list. Your compiled code is then marked as being invalid. If you fetched into a record, however, upon recompiliation that record will take on the new structure of the cursor.

• Encapsulate access to your data structures within packages. I recommend, for example, that you never repeat a line of SQL in your application; that all SQL statements be hidden behind a package interface; and that most developers never write any SQL at all. They can simply call the appropriate package procedure or function, or open the appropriate package cursor. If they don't find what they need, they ask the owner of the package (who is intimate with the complex details of the data structure) to add or change an element.

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:

• Don't declare data in your package specification. Instead, "hide" it in the package body and build "get and set" programs to retrieve the data and change it. This way, you retain control over the data and also retain the flexibility to change your implementation without affecting the programs which rely on that data.

• Build toggles into your packages, such as a "local" debug mechanisms, which you can easily turn on and off. This way, a user of your package can modify the behavior of programs inside the package without having to change his or her own code.

• Avoid writing repetitive code inside your package bodies. This is a particular danger when you overload multiple programs with the same name. Often the implementation of each of these programs is very similar. You will be tempted to simply cut and paste and then make the necessary changes. However, you will be much better off if you take the time to create a private program in the package which incorporates all common elements, and then have each overloaded program call that program.

• Spend as much time as you can in your package specifications. Hold off on building your bodies until you have tested your interfaces (as defined by the specifications) by building compilable programs which touch on as many different packages as possible.

• Be prepared to work in and enhance multiple packages simultaneously. Suppose that you are building a package to maintain orders and that you run into a need for a function to parse a string. If your string package does not yet have this functionality, stop your work in the orders package and enhance the string package. Unit-test your generic function there. When you've got it working, deploy it in the orders package. Follow this disciplined approach to modularization and you will continually build up your toolbox of reusable utilities.

• Always keep your package specifications in separate files from your package bodies. If you change your body but not your specification, then a recompile only of the body will not invalidate any programs referencing the package.

• Compile all of the package specifications for your application before any of your bodies. That way, you will have minimized the chance that you will run into any unresolved or (seemingly) circular references.

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:

• Set as a rule that individual developers never write their own exception-handling code, never use the pragma EXCEPTION_INIT to assign names to error numbers, and never call RAISE_APPLICATION_ERROR with hardcoded numbers and text. Instead, consolidate exception handling programs into a single package, and predefine all application-specific exceptions in their appropriate packages. Build generic handler programs that, most importantly, hide the way you record exceptions in a log. Individual handler sections of code should never expose the particular implementation, such as an INSERT into a table.

• Never write implicit cursors (in other words, never use the SELECT INTO syntax). Instead, always declare explicit cursors. If you follow this advice, you will no longer spend time debating with yourself and others which course is the best. ("Well, if I use ROWNUM < 2 I never get the TOO_MANY_ROWS exception. So there!") This will improve your productivity. And you will have SQL which is more likely (and able) to be reused.

• Pick a coding style and stick to it. If you ever find yourself thinking things like "Should I indent three spaces or four?" or "How should I do the line breaks on this long procedure call?" or "Should I do everything in lowercase or uppercase or what?" then you are wasting time and doing an injustice to yourself. If you don't have a coding style, use mine -- it is offered in detail in Chapter 3, Effective Coding Style .

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:

• Never exit from a FOR loop (numeric or cursor) with an EXIT or RETURN statement. A FOR loop is a promise: my code will iterate from the starting to the ending value and will then stop execution.

• Never exit from a WHILE loop with an EXIT or RETURN statement. Rely solely on the WHILE loop condition to terminate the loop.

• Ensure that a function has a single successful RETURN statement as the last line of the executable section. Normally, each exception handler in a function would also return a value.

• Don't let functions have OUT or IN OUT parameters. The function should only return values through the RETURN clause.

• Make sure that the name of a function describes the value being returned (noun structure, as in "total_compensation"). The name of a procedure should describe the actions taken (verb-noun structure, as in "calculate_totals").

• Never declare the FOR loop index (either an integer or a record). This is done for you implicitly by the PL/SQL runtime engine.

• Do not use exceptions to perform branching logic. When you define your own exceptions, these should describe error situations only.

• When you use the ELSIF statement, make sure that each of the clauses is mutually exclusive. Watch out especially for logic like "sal BETWEEN 1 and 10000" and "sal BETWEEN 10000 and 20000."

• Remove all hardcoded " magic values" from your programs and replace them with named constants or functions defined in packages.

• Do not " SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with an explicit cursor.

• Do not use the names of tables or columns for variable names. This can cause compile errors. It can also result in unpredictable behavior inside SQL statements in your PL/SQL code. I once did a global search and replace of :GLOBAL.regcd to regcd (a local variable declared as VARCHAR2(10) but also, unfortunately, the name of a column). Our Q&A procedures were very weak and we ended up rolling out into production a program with a DELETE statement containing a WHERE clause that looked like this:

  WHERE regcd = regcd

  Needless to say, this caused many headaches. If I had simply changed the global reference to v_regcd, I would have avoided all such problems.

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. So...read on!