O'Reilly Media | Oracle PL/SQL Programming

Content preview·Buy reprint rights for this chapter

This chapter introduces PL/SQL, its origins, and its various versions. It also introduces you to what it means to be a PL/SQL programmer, and how PL/SQL programming differs from the kind of programming with which you may be familiar.

PL/SQL stands for "Procedural Language extensions to SQL." PL/SQL is available primarily as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. As you can see from the following code example, PL/SQL allows you to combine SQL statements with "standard" procedural constructs. This single program can either insert a company into or delete a company from the database. It relies on the IF statement (not a SQL statement) to determine which action to take:

PROCEDURE maintain_company
   (action_in IN VARCHAR2,
    id_in IN NUMBER,
    name_in IN VARCHAR2 := NULL)
IS
BEGIN
   IF action_in = 'DELETE'
   THEN
      DELETE FROM company WHERE company_id = id_in;

   ELSIF action_in = 'INSERT'
   THEN
      INSERT INTO company (company_id, name)
      VALUES (id_in, name_in);
   END IF;
END;

PL/SQL is an unusual—and an unusually powerful—programming language. You can write programs that look just like traditional 3GL modules, but you can also include calls to SQL statements, manipulate data through cursors, and take advantage of some of the newest developments in programming languages. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors. The tight integration of PL/SQL with SQL provides developers with the best of both worlds—declarative and procedural logic.

Figure 1.1 shows how PL/SQL fits within the client-server architecture of Oracle-based applications. It shows both an Oracle Forms client and a non-Oracle tool client, both executing against an Oracle Server database. Notice that the Oracle Forms client makes use of two versions of PL/SQL:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL stands for "Procedural Language extensions to SQL." PL/SQL is available primarily as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. As you can see from the following code example, PL/SQL allows you to combine SQL statements with "standard" procedural constructs. This single program can either insert a company into or delete a company from the database. It relies on the IF statement (not a SQL statement) to determine which action to take:

PROCEDURE maintain_company
   (action_in IN VARCHAR2,
    id_in IN NUMBER,
    name_in IN VARCHAR2 := NULL)
IS
BEGIN
   IF action_in = 'DELETE'
   THEN
      DELETE FROM company WHERE company_id = id_in;

   ELSIF action_in = 'INSERT'
   THEN
      INSERT INTO company (company_id, name)
      VALUES (id_in, name_in);
   END IF;
END;

PL/SQL is an unusual—and an unusually powerful—programming language. You can write programs that look just like traditional 3GL modules, but you can also include calls to SQL statements, manipulate data through cursors, and take advantage of some of the newest developments in programming languages. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors. The tight integration of PL/SQL with SQL provides developers with the best of both worlds—declarative and procedural logic.

Figure 1.1 shows how PL/SQL fits within the client-server architecture of Oracle-based applications. It shows both an Oracle Forms client and a non-Oracle tool client, both executing against an Oracle Server database. Notice that the Oracle Forms client makes use of two versions of PL/SQL:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The whole idea of "programming" in Oracle-based applications has a somewhat different meaning from what you might be used to in a third-generation, or even a wholly procedural fourth-generation, language.

Programming in Oracle generally entails a blending of technologies and programming styles. Suppose, for example, that you are building an application to maintain invoice information. You will first design and create a database, relying mostly on the declarative SQL language. However, you might also create database triggers, coded in PL/SQL, to implement complex business rules at the database level. From there you build the screens, charts, and reports that make up the user interface. You could use third-party tools, or you could rely on Oracle Developer/2000 (formerly the Cooperative Development Environment, or CDE), with such products as Oracle Forms, Oracle Graphics, and Oracle Reports.

Each of the Oracle Developer/2000 tools employs a nondeclarative or "fill-in-the-blanks" approach to development. Without writing any code in the traditional sense, you create a full-functioned screen with buttons, radio groups, menus, and pictures. These objects are, more or less, the graphical pieces of the program that the user views, touches, and acts upon. They are somewhat different in each tool. In Oracle Forms, these objects include items on the screen (buttons, radio groups, text fields), blocks (which correspond to tables in the database), and visual attributes (which control the way items appear to the user). In Oracle Reports, these objects include fields in the report, repeating frames of data, and parameters that are entered by the user to initiate the report. While there isn't any code per se for this part of your system, you have created objects that will be manipulated by the code you do write in PL/SQL.

Once you have built the graphical objects in your tools, you then associate PL/SQL procedural code with those objects. How? You use various kinds of triggers associated with those objects. Oracle Forms, for example, employs a sophisticated event-driven model, which is very conducive to programming in the GUI environment. You attach event triggers, such as When-Button-Pressed, to an object. Then, no matter how the user triggers an event (with the movement of a mouse, with the press of a key, or as an indirect result of another event), that trigger fires and executes the PL/SQL code you have written. The only way to apply a PL/SQL procedure or function to an object in Oracle Forms is through a trigger. All triggers are implemented in PL/SQL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle has a history of leading the software industry in providing declarative, nonprocedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, offer high levels of productivity, precisely by relying heavily on a "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.

In Oracle's early years, this declarative approach, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations and requirements became more demanding. Developers needed to get under the skin of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database procedures.

In 1991, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3. SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tool side, allowing developers to code their procedural logic in a natural, straightforward manner.

This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build "batch-processing" scripts of procedural and SQL statements. In other words, you could not store procedures or functions for execution at some later time. You could not construct a modular application or store complex business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, though support for functions was not documented and therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was a far cry from a full-fledged programming language.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One thing that may complicate using PL/SQL is that it is not a single product. There are several distinct, supported versions out there. Table 1.2 summarizes the various versions; the following sections describe the main features available in each of the versions in use today.

Table 1.2: PL/SQL Versions
Version/Release	Characteristics
Version 1.0	First available in SQLPlus as a batch-processing script. Oracle Version 6.0 was released at approximately the same time. PL/SQL was then implemented within SQLForms Version 3, the predecessor of Oracle Forms.
Release 1.1	Available only in the Oracle Developer/2000 Release 1 tools. This upgrade supports client-side packages and allows client-side programs to execute stored code transparently.
Version 2.0	Available with Release 7.0 (Oracle Server). Major upgrade to Version 1. Adds support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions, including DBMS_OUTPUT and DBMS_PIPE.
Release 2.1	Available with Release 7.1 of the Oracle Server Version. Supports programmer-defined subtypes, enables the use of stored functions inside SQL statements, and offers dynamic SQL with the DBMS_SQL package. With Version 2.1, you can now execute SQL DDL statements from within PL/SQL programs.
Release 2.2

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This whole book is full of advice about PL/SQL programming, but in this section I offer a few basic principles. These principles guide my own use of PL/SQL and I'd like to encourage you to adopt them as well.

We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you come to assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own tools, and often not in positive ways. "It can't run any faster than that; it's a pig." "I can't make it work the way the user wants; that'll have to wait for the next version." "If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle."

Sadly (or is it happily?), the reality is that your program could almost always run a little faster. The screen could function just the way the user wants it to. Although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and created a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you've developed, particularly regarding fourth-generation language (4GL) development with the Oracle tools. Be creative—step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn, how you will grow as a programmer and problem-solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying "You can't do that!" and instead simply nodded quietly and murmured "Now, if I do it this way..."

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL is a powerful, many-featured product. This is a lengthy book. I have gone to great lengths to make all the information within the covers highly accessible. Still, I thought it would be helpful to offer a quick review of some of my favorite aspects of the PL/SQL language.

It's all wonderful, of course, and I wouldn't trade PL/SQL for any other programming language in the world. Yet certain features and techniques have stood out for me as ways to improve the efficiency of my code and the productivity of my development effort.

The topics in the following sections offer just enough information to give you a sense of what is possible. Go to the appropriate chapter for detailed information.

You can use the %TYPE and %ROWTYPE declaration attributes to anchor the datatype of one variable to that of a previously existing variable or data structure. The anchoring data structure can be a column in a database table, the entire table itself, a programmer-defined record, or a local PL/SQL variable. In the following example, I declare a local variable with the same structure as the company name:

my_company company.name%TYPE;

See Chapter 4 for details.

PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount of code and fuss possible. Some of them are straightforward, such as the LENGTH function, which returns the length of the specified string. Others offer subtle variations which will aid you greatly—but only when you are aware of those variations.

Two of my favorites in this category of hidden talents are SUBSTR and INSTR, both character functions. SUBSTR returns a subportion of a string. INSTR returns the position in a string where a substring is found. Most developers only use these functions to search forward through the strings. By passing a negative starting location, however, SUBSTR will count from the end of the string. And INSTR will actually scan in reverse through the string for the nth occurrence of a substring.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

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.

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Every language—whether human or computer—has a syntax, vocabulary, and character set. In order to communicate within that language, you have to learn the rules that govern its usage. Many of us are very wary of learning a new computer language. Change is often scary, but, in general, programming languages are very simple tongues, and PL/SQL is a relatively simple programming language. The difficulty we have conversing in languages based on bytes is not with the language, but with the compiler or computer with which we are having the discussion. Compilers are, for the most part, very stupid. They are not creative, sentient beings. They are not capable of original thought. Their vocabulary is severely limited. Compilers just happen to think their dull thoughts very, very rapidly—and very inflexibly.

If I hear someone ask "gottabuck?", I can readily interpret that sentence and decide how to respond. If I instruct PL/SQL, on the other hand, to "gimme the next half-dozen records," I will not get very far in my application. To use the PL/SQL language, you must dot your i's and cross your t's—syntactically speaking. So, in this chapter, I cover the fundamental language rules that will help you converse with the PL/SQL compiler—the PL/SQL character set, lexical units, PRAGMA keyword, and block structure.

A PL/SQL program consists of a sequence of statements, each of which is made up of one or more lines of text. Text is made up of combinations of the characters shown in Table 2.1.

Table 2.1: PL/SQL Character Set
Type	Characters
Letters	A-Z, a-z
Digits

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A PL/SQL program consists of a sequence of statements, each of which is made up of one or more lines of text. Text is made up of combinations of the characters shown in Table 2.1.

Table 2.1: PL/SQL Character Set
Type	Characters
Letters	A-Z, a-z
Digits	0-9
Symbols	~ ! @ # $ % & * ( ) _ - + = \| [ ] { } : ; " ' < > , . ? /
Whitespace	Tab, space, carriage return

Note that PL/SQL is a case-insensitive language. Uppercase letters are treated the same way as lowercase letters except when the characters are surrounded by single quotes (when they are literal strings) or represent the value of a character variable.

Every valid statement in PL/SQL, from declaration to executable statement to keyword, is made up of various combinations of the above characters. Now you just have to figure out how to put them all together!

A number of these characters—both singly and in combination with other characters—have a special significance in PL/SQL. Table 2.2 lists these special symbols.

Table 2.2: Simple and Compound Symbols in PL/SQL
Symbol

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

An identifier is a name for a PL/SQL object, including any of the following:

Constant
Variable
Exception
Procedure
Function
Package
Record
PL/SQL table
Cursor
Reserved word

Properties of an identifier are summarized below:

Up to 30 characters in length
Must start with a letter
Can include $ (dollar sign), _ (underscore), and # (pound sign)
Cannot contain spaces

Remember that PL/SQL is not case-sensitive, so if the only difference between two identifiers is the case of one or more letters, PL/SQL treats those two identifiers as the same. For example, the following identifiers are all considered by PL/SQL to be the same, because the characters in the name are the same; the only difference is their case:

lots_of_$MONEY$
LOTS_of_$MONEY$
Lots_of_$Money$

The following strings are valid identifier names:

lots_of_$MONEY$	FirstName
company_id#	address_line1

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A literal is a value which is not represented by an identifier; it is simply a value. A literal may be composed of one of the following types of data:

Number: 415, 21.6, or NULL
String: `This is my sentence' or `31-JAN-94' or NULL
Boolean: TRUE, FALSE, or NULL

Notice that there is no way to indicate a true date literal. The value `31-JAN-94' is a string literal (any sequence of characters enclosed by single quotes is a string literal). PL/SQL and SQL automatically convert such a string to a date for you (by calling TO_DATE), but a date has only an internal representation.

A string literal can be composed of zero or more characters from the PL/SQL character set. A literal of zero characters is represented as '' (two consecutive single quotes with no characters between them) and is defined as the NULL string. This literal has a datatype of CHAR (fixed-length string).

PL/SQL is case-sensitive within string literals. The following two literals are different:

'Steven'
'steven'

The following condition, for example, evaluates to FALSE:

IF 'Steven' = 'steven'

The trickiest part of working with string literals comes when you need to include a single quote inside a string literal (as part of the literal itself). Generally, the rule is that you write two single quotes next to each other inside a string if you want the literal to contain a single quote in that position. The following table shows the literal in one column and the resulting "internal" string in the second column:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A PL/SQL program is made up of a series of statements. A statement is terminated with a semicolon (;), not with the physical end of a line. In fact, a single statement is often spread over several lines to make it more readable. The following IF statement takes up four lines and is indented to reinforce the logic behind the statement:

IF salary < min_salary (1994)
THEN
   salary := salary + salary*.25;
END IF;

There are two semicolons in this IF statement. The first semicolon indicates the end of the single executable statement within the IF-END IF construct. The second semicolon terminates the IF statement itself. This same statement could also be placed on a single physical line (if it would fit):

IF salary < min_salary (1994) THEN salary := salary + salary*.25; END IF;

The semicolons are still needed to terminate the logical, executable statements. I suggest that you do not, however, combine the different components of the IF statement on a single line. It is much more difficult to read. I also recommend that you never place more than one executable (or declaration) statement on each line. Compare the following statements:

DECLARE
   continue_scanning BOOLEAN := TRUE;
   scan_index NUMBER := 1;

and:

DECLARE
   continue_scanning BOOLEAN := TRUE; scan_index NUMBER := 1;

In the second example, the two different statements blur into a single stream of text. It is difficult to find the semicolon in the middle of a line.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Inline documentation, otherwise known as comments, is an important element of a good program. While this book offers many suggestions on how to make your program self-documenting through good naming practices and modularization, such techniques are seldom enough by themselves to guarantee a thorough understanding of a complex program.

PL/SQL offers two different styles for comments: single-line and multiline block comments.

The single-line comment is initiated with two hyphens (—), which cannot be separated by a space or any other characters. All text after the double hyphen, to the end of that physical line, is considered commentary and is ignored by the compiler. If the double hyphen appears at the beginning of the line, then that whole line is a comment.

Remember: the double hyphen comments out the remainder of a physical line, not a logical PL/SQL statement. In the following IF statement, I use a single-line comment to clarify the logic of the Boolean expression:

IF salary < min_salary (1994) -- Function returns min salary for year.
THEN
   salary := salary + salary*.25;
END IF;

While single-line comments are useful for documenting brief bits of code and also ignoring a line that you do not want executed at the moment, the multiline comment is superior for including longer blocks of commentary.

Multiline comments start with a slash-asterisk (/*) and end with an asterisk-slash (*/). PL/SQL considers all characters found between these two sequences of symbols to be part of the comment, and they are ignored by the compiler.

The following example of multiline comments shows a header section for a procedure. I use the double vertical bars in the left margin so that, as the eye moves down the left edge of the program, it can easily pick out the chunks of comments:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, to the compiler. Pragmas are processed at compile time; they do not execute during runtime.

A pragma is a special instruction to the compiler. Also called a pseudoinstruction, the pragma doesn't change the meaning of a program. It simply passes information to the compiler. It is very similar, in fact, to the tuning hints you can embed in a SQL statement inside a block comment.

PL/SQL offers the following pragmas:

EXCEPTION_INIT: Tells the compiler to associate a particular error number with an identifier you have declared as an exception in your program. See Chapter 8 for more information.
RESTRICT_REFERENCES: Tells the compiler the purity level (freedom from side effects) of a packaged program. See Chapter 17 for more information.
SERIALLY_REUSABLE: New to PL/SQL8. Tells the PL/SQL runtime engine that package-level data should not persist between references to that data. See Chapter 25 for more information.

The syntax for using the PRAGMA keyword is as follows:

PRAGMA <instruction>;

where <instruction> is a statement providing instructions to the compiler. You would call EXCEPTION_INIT as follows:

DECLARE
   no_such_sequence EXCEPTION;
   PRAGMA EXCEPTION_INIT (no_such_sequence, -2289);
BEGIN
   ...
END;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL is a block-structured language. Each of the basic programming units you write to build your application is (or should be) a logical unit of work. The PL/SQL block allows you to reflect that logical structure in the physical design of your programs.

The block structure is at the core of two key concepts and features of the PL/SQL language:

Modularization: The PL/SQL block is the basic unit of work from which modules, such as procedures and functions, are built. The ability to modularize is central to successfully developing complex applications.
Scope: The block provides a scope or context for logically related objects. In the block, you group together declarations of variables and executable statements that belong together.

You can create anonymous blocks (blocks of code that have no name) and named blocks, which are procedures and functions. Furthermore, you can build packages in PL/SQL that group together multiple procedures and functions.

The following sections briefly examine the block structure and related concepts.

Each PL/SQL block has up to four different sections (some are optional under certain circumstances):

Header: Relevant for named blocks only. The header determines the way the named block or program must be called.
Declaration section: The part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You can learn everything about a programming language—its syntax, high-performance tips, and advanced features—and still write programs that are virtually unreadable, hard to maintain, and devilishly difficult to debug—even by you, the author. You can be very smart and very clever, and yet develop applications that obscure your talent and accomplishments.

This chapter addresses the "look-and-feel" of your code—the aesthetic aspect of programming. I am sure that you have all experienced the pleasure of reading well-structured and well-formatted code. You have also probably experienced a pang of jealousy at that programmer's style and effort, wondering where she or he found the time to do it right. Developers always experience a feeling of intense pride and satisfaction from carefully and artfully designing the visual layout of their code. Yet few of us take the time to develop a style and use it consistently in our work.

Of course, the impact of a coding style goes well beyond the personal satisfaction of any individual. A consistent, predictable approach to building programs makes it easier to debug and maintain that code. If everyone takes her own approach to structuring, documenting, and naming her code, every program becomes its own little pool of quicksand. It is virtually impossible for another person to put in a foot and test the water (find the source of a problem, analyze dependencies, etc.) without being pulled under.

I discuss the elements of an effective coding style in the PL/SQL language at this juncture, before we get to any code, for two reasons:

To drive home the point that if you are going to adopt a coding style which will improve the readability and maintainability of your application, you need to do it at the beginning of your project. Programming style involves an attention to detail that can be built only during the construction process. You are not going to go back into existing code and modify the indentation, case, and documentation format after the project is done.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There is really just one fundamental objective of code layout:

Reveal and reinforce the logical structure of your program.

You could come up with ways of writing your code that are very pleasing to the eye, but doing so is less important than choosing a format that shows the structure and intent of the program.

It is easy to address the topic of effective code layout for PL/SQL because it is such a well structured language. It benefits greatly from Ada's block structure approach. Each control construct, such as IF and LOOP, has its own terminator keyword, such as END IF and END LOOP. Each logical block of code has an explicit beginning statement and an associated ending statement. This consistent and natural block style lends itself easily and naturally to standards for indentation and whitespace, which further expose the structure of the code.

Indentation is one of the most common and effective techniques used to display a program's logic via format. As illustrated in the following examples, programs that are indented are easier to read than those that are not indented, although programs that use excessive indentation are not much more readable than unindented programs. Here is an unindented IF statement:

IF to_number(the_value) > 22
THEN
IF max_totals = 0
THEN
calc_totals;
ELSE
WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;

The lack of indentation in this example makes it very difficult to pick out the statements that go with each clause in the IF statement. Some developers, unfortunately, go to the opposite extreme and use six or more spaces for indentation. (This usually occurs by relying on the tab key, which offers "logical" indentation—a tab can be equivalent to three spaces in one editor and eight in another. I suggest avoiding the use of tabs altogether.)

I have found that a three-space indentation not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. And, with deeply nested structures, you won't run off the right margin as quickly! Here is the three-space indented version of the previous nested IF statement:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Because PL/SQL is an extension to the SQL language, you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability.

PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful:

Right-align the reserved words for the clauses against the DML statement.

I recommend that you visually separate the SQL reserved words which identify the separate clauses from the application-specific column and table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of the SQL statement:

SELECT	INSERT	UPDATE	DELETE
SELECT FROM WHERE AND OR GROUP BY HAVING AND OR ORDER BY	INSERT INTO VALUES INSERT INTO SELECT FROM WHERE	UPDATE SET WHERE	DELETE FROM WHERE

Here are some examples of this format in use:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The control structures in your program are the most direct representation of the logic needed to implement your specifications. The format of these control structures, therefore, will have a significant impact on the readability of your code.

Indentation is the most important element of control structure layout. Always keep statements of the same "logical level" at the same indentation level. Let's see what this means for the various control structures of PL/SQL.

This conditional construct comes in three flavors:

IF <expression>
END IF;

IF <expression>
ELSE
END IF;

IF <expression>
ELSEIF <expression>
ELSE
END IF;

In general, the IF statement is composed of clauses in which there is a Boolean expression or condition and a section of code executed when that condition evaluates to TRUE.

So if you want to use indentation to reveal the logical structure of the simplest form of the IF statement (IF-END IF), I suggest one of these two styles:

New Line for THEN	Same Line for THEN
IF <expression> THEN executable_statements; END IF;	IF <expression> THEN executable_statements END IF;
IF <expression> THEN executable_statements; ELSE else_executable_statements; END IF;	IF <expression> THEN executable_statements ELSE else_executable_statements; END IF;

New Line for THEN

Same Line for THEN

IF <expression>
THEN
   executable_statements;
END IF;

IF <expression> THEN
   executable_statements
END IF;

IF <expression>
THEN
   executable_statements;
ELSE
   else_executable_statements;
END IF;

IF <expression> THEN
   executable_statements
ELSE
   else_executable_statements;
END IF;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As I've outlined in Chapter 2, every PL/SQL program is structured as a block containing up to four sections:

Header
Declaration section
Executable section
Exception section

The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. (See Chapter 15, for more information about the block structure.)

Consider the following function:

FUNCTION
company_name (company_id_in IN company.company_id%TYPE)    RETURN
VARCHAR2 IS cname company.company_id%TYPE; BEGIN
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;
EXCEPTION WHEN NO_DATA_FOUND THEN   RETURN NULL; END;

You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end?

Here is that same function after we apply some straightforward formatting rules to it:

FUNCTION company_name (company_id_in IN company.company_id%TYPE)
   RETURN VARCHAR2
IS
   cname company.company_id%TYPE;

BEGIN
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;

Now it is easy to see that the header of the function consists of:

FUNCTION company_name (company_id_in IN company.company_id%TYPE)
   RETURN VARCHAR2

The declaration section, which comes after the IS and before the BEGIN, clearly consists of a single declaration of the cname variable. The executable section consists of all the statements after the BEGIN and before the EXCEPTION statement; these are indented in from the BEGIN. Finally, the exception section shows a single specific exception handler and a WHEN OTHERS exception.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A package is a collection of related objects, including variables, TYPE statements (to define structures for records, tables, and cursors), exceptions, and modules. We have already covered structuring all the different objects which make up a package. Now, let's take a look at how to structure the package itself.

A package has both a specification and a body. The package specification contains the declarations or definitions of all those objects that are visible outside of the package—the public objects. This means that the objects can be accessed by any account that has been granted EXECUTE authority on the package. The package body contains the implementation of all cursors and modules defined in the specification, and the additional declaration and implementation of all other package objects. If an object, such as a string variable, is declared in the body and not in the package, then any module in the package can reference that variable, but no program outside of the package can see it. That variable is invisible or private to the package.

The first point to make about the package structure is that all objects declared in the specification exist within the context of the package and so should be indented from the PACKAGE statement itself, as shown below:

PACKAGE rg_select
IS
   list_name VARCHAR2(60);

   PROCEDURE init_list
      (item_name_in IN VARCHAR2,
       fill_action_in IN VARCHAR2 := 'IMMEDIATE');
   PROCEDURE delete_list;
   PROCEDURE clear_list;

END rg_select;

The same is true for the package body. I suggest that you always include a label for the END statement in a package so that you can easily connect up that END with the end of the package as a whole. I place the IS keyword on a new line to set off the first declaration in the package from the name of the package. You could always use a blank line. Notice that I use blank lines in rg_select to segregate different modules which are related by function. I think that logical grouping is always preferable to an arbitrary grouping such as alphabetical order.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The object of an effective coding style is to make the program more understandable and maintainable. Most programs will benefit from documentation which explains what is going on inside those programs. There are two forms of code documentation: external and internal. External documentation is descriptive information about a program which is written and stored separately from the program itself. Internal documentation, also known as inline documentation or comments, is placed within the program itself, either at the program level or the statement level. (For an introduction to inline documentation and the types of PL/SQL comments, see the section called Section 2.5 in Chapter 2.)

The best kind of internal documentation derives from your programming style. If you apply many of the guidelines in this chapter and throughout this book, you will be able to write code which is, to a great extent, self-documenting. Here are some general tips:

Write straightforward code that avoids clever tricks.
Think of names for variables and modules that accurately describe their purpose.
Use named constants instead of literal values.
Employ a clean, consistent layout.

Do all these things and more, and you will find that you need to write fewer comments to explain your code.

Reducing the need for comments is important. Few developers make or have the time for extensive documentation in addition to their development efforts, and, more importantly, many comments tend to duplicate the code. This raises a maintenance issue because those comments will have to be changed when the code is changed.

While it is my hope that after reading this book you will write more self-documenting code, there is little doubt that you will still need to comment your code. The following example shows the use of single- and multiline comments in PL/SQL:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A package is often a complicated and long construct. It is composed of many different types of objects, any of which may be public (visible to programs and users outside of the package) or private (available only to other objects in the package). Package structure is described in more detail in Chapter 16.

You can use some very simple documentation guidelines to clarify the structure of the package.

As usual when discussing packages, one must consider the specification separately from the body. As a meta-module or grouping of modules, the specification should have a standard header. This header needn't be as complicated as that of a specific module, because you do not want to repeat in the package header any information which also belongs in specific modules. I suggest using the template header shown in the following example. In the "Major Modifications" section of the header, do not include every change made to every object in the package. Instead note significant changes to the package as a whole, such as an expansion of scope, a change in the way the package and global variables are managed, etc. Place this header after the package name and before the IS statement:

PACKAGE package_name
/*
|| Author:
||
|| Overview:
||
|| Major Modifications (when, who, what)
||
*/
IS
   ...
END package_name;

The package specification is, in essence, a series of declaration statements. Some of those statements declare variables, while others declare modules. Follow the same recommendation in commenting a package as you do in commenting a module's declaration section: provide a comment for each declaration. In addition to the comments for a specific declaration, you may also find it useful to provide a banner before a group of related declarations to make that connection obvious to the reader.

Surround the banner with whitespace (blank lines for the start/end of a multiline comment block). While you can use many different formats for this banner, use the simplest possible design that gets the point across. Everything else is clutter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Almost every PL/SQL program you write contains internal data stored as variables, constants, records, or tables. This chapter refers to these various types of storage as variables. Variables may be used for many purposes; for example, they may store information retrieved from columns in a table or may hold calculated values for use only in the program. Variables may be scalar (made up of a single value) or composite (made up of multiple values or components).

The attributes of a variable are its name, datatype, and value (or values, in a complex datatype like a record). The name indicates the part of memory you want to access or change. The datatype determines the type of information you can store in the variable. The value (or values, in the case of a composite datatype) is the set of bits stored in the variable's memory location.

This chapter describes the kinds of names you can give PL/SQL elements and the different types of scalar variables you can declare and use in your programs. It also offers tips on how best to use program data in your code.

Identifiers are the names given to PL/SQL elements such as variables or nested tables or cursors. Identifiers:

Can be up to 30 characters in length
Must start with a letter
Can then be composed of any of the following: letters, numerals, $, #, and _

A named constant is a special kind of variable. A named constant has a name, datatype, and value, just like a regular variable. However, unlike a regular variable, the value of a named constant must be set when the constant is declared and may not change thereafter. Its value is constant. Unless otherwise mentioned, the information provided below for variables also applies to named constants.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Identifiers are the names given to PL/SQL elements such as variables or nested tables or cursors. Identifiers:

Can be up to 30 characters in length
Must start with a letter
Can then be composed of any of the following: letters, numerals, $, #, and _

A named constant is a special kind of variable. A named constant has a name, datatype, and value, just like a regular variable. However, unlike a regular variable, the value of a named constant must be set when the constant is declared and may not change thereafter. Its value is constant. Unless otherwise mentioned, the information provided below for variables also applies to named constants.

An unnamed constant is a literal value, such as 2 or Bobby McGee. A literal does not have a name, though it does have an implied (undeclared) datatype.

The name of your identifier should describe as accurately and concisely as possible what the identifier represents. Let's take a look at choosing the name for a variable. Outside of the actual use or context of a variable, the name is all the variable's got. And if the name is bad, the context is often distorted by the bad choice of a moniker.

The first step towards choosing an accurate name is to have a clear idea of how the variable is to be used. You might even take a moment to write down—in noncomputer terms—what the variable represents. You can then easily extract an appropriate name from this statement. For example, if a variable represents the "total number of calls made about lukewarm coffee," a good name for that variable would be total_calls_on_cold_coffee—or tot_cold_calls, if you are allergic to five-word variable names. A bad name for that variable would be "totcoffee" or t_#_calls_lwcoff, both of which are too cryptic to get the point across.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Each constant and variable element you use in your programs has a datatype. The datatype dictates the storage format, the restrictions on how the variable can be used, and the valid values which may be placed in that variable.

PL/SQL offers a comprehensive set of predefined scalar and composite datatypes. A scalar datatype is an atomic; it is not made up of other variable components. A composite datatype has internal structure or components. The two composite types currently supported by PL/SQL are the record and table (described in Chapter 9, and Chapter 10, respectively).

The scalar datatypes fall into one of four categories or families: number, character, Boolean, and date-time, as shown in Table 4.1.

Table 4.1: Datatype Categories
Category	Datatype

Table of Contents