The PRAGMA Keyword

The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, to the compiler. Also called apseudoinstruction, a pragma simply passes information to the compiler rather than getting transformed into a particular execution.

The syntax for using the PRAGMA keyword is as follows:

PRAGMA instruction;

where instruction is a statement providing instructions to the compiler. The PL/SQL compiler will accept such directives anywhere in the declaration section.

PL/SQL offers the following pragmas:

 

AUTONOMOUS_TRANSACTION

Tells the PL/SQL runtime engine to commit or roll back any changes made to the database inside the current block without affecting the main or outer transaction. See Chapter 13 for more information. Introduced in Oracle8i.

 

EXCEPTION_INIT

Tells the compiler to associate a particularerror number with an identifier you have declared as an exception in your program. See Chapter 6 for more information.

 

RESTRICT_REFERENCES

Tells the compiler the purity level (freedom from side effects) of a packaged program. See Chapter 16 for more information.

 

SERIALLY_REUSABLE

Tells the PL/SQL runtime engine that package-level data should not persist between references to that data. See Chapter 17 for more information. Introduced in Oracle8.

The following block demonstrates the use of the EXCEPTION_INIT pragma to name a built-in exception that would otherwise have only a number.

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

Labels

A PL/SQL label is a way to name a particular part of your program. Syntactically, a label has the format:

<<identifier>>

where identifier is a valid PL/SQL identifier (up to 30 characters in length and starting with a letter, as discussed earlier in Section 3.3). There is no terminator; labels appear directly in front of the thing they're labeling, which must be an executable statement—even if it is merely the NULL statement.

BEGIN ... <<the_spot>> NULL;

Because anonymous blocks are themselves executable statements, a label can "name" ananonymous block for the duration of its execution. For example:

<<insert_but_ignore_dups>>BEGIN INSERT INTO catalog VALUES (...);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;END insert_but_ignore_dups;

One reason you might label a block is to improve the readability of your code. When you give something a name, you self-document that code. You also clarify your own thinking about what that code is supposed to do, sometimes ferreting out errors in the process.

Another reason to use a block label is to allow you to qualify references to elements from an enclosing block that have duplicate names in the current, nested block. Here's a schematic example:

<<outerblock>>DECLARE counter INTEGER := 0;BEGIN ... DECLARE counter INTEGER := 1; BEGIN IF counter = outerblock.counter THEN ... END IF; END;END;

Without the block label, there would be no way to distinguish between the two "counter" variables. Again, though, a better solution would probably have been to use distinct variable names.

A third function of labels is to serve as the target of aGOTO statement. However, these days, GOTO statements are virtually nonexistent, thanks to Edsger Dijkstra's now-legendary essay on the subject[4] (and the fact that exception handling is usually a better way to go). In all the PL/SQL code I've ever seen, I recall only one GOTO.

[4] "Go To Statement Considered Harmful," which originally appeared in the March 1968 Communications of the ACM, was influential enough to introduce the phrase considered harmful into the lexicon of computerese.

Although few programs I've seen or worked on require the use of labels, there is one final use of this feature that is more significant than the previous three combined: a label can serve as a target for the EXIT statement in nested loops. Here's the example code:

BEGIN <<outer_loop>> LOOP LOOP EXIT outer_loop;END LOOP; some_statement ; END LOOP;END;

Without the <<outer_loop>> label, the EXIT statement would have exited only the inner loop and would have executed some_statement. But I didn't want it to do that. So, in this case, the label provides functionality that PL/SQL does not offer in any other straightforward way.

 

Part II: PL/SQL Program Structure

This part of the book presents the basic PL/SQL programming elements and statement constructs. Chapters 4 through 6 describe conditional (IF and CASE) and sequential control statements (e.g., GOTO and NULL), loops, and exception handling in the PL/SQL language. When you complete this section of the book you will know how to construct blocks of code that correlate to the complex requirements in your applications.

Chapter 4

Chapter 5

Chapter 6