Anchored Declarations

You can and often will declare variables using "hardcoded" or explicit datatypes, as follows:

l_company_name VARCHAR2(100);

Oracle offers an alternative method, called anchoring, that offers some significant advantages, particularly when the variable you are declaring is related to or will be populated from another source, such as a row in a table.

When you "anchor" a datatype, you tell PL/SQL to set the datatype of your variable based on the datatype of an already defined data structure—another PL/SQL variable, a predefined TYPE or SUBTYPE, a database table, or a specific column in a table. PL/SQL offers two kinds of anchoring:

Scalar anchoring

Use the %TYPE attribute to define your variable based on a table's column or some other PL/SQL scalar variable.

Record anchoring

Use the %ROWTYPE attribute to define your record structure based on a table or a predefined PL/SQL explicit cursor.

The syntax for an anchored datatype is:

variable name type attribute%TYPE [optional default value assignment];variable name table_name | cursor_name%ROWTYPE [optional default value assignment];

where variable name is the name of the variable you are declaring, and type attribute is either a previously declared PL/SQL variable name or a table column specification in the format table.column.

This anchoring reference is resolved at the time the code is compiled; there is no runtime overhead to anchoring. The anchor also establishes a dependency between the code and the anchored element (the table, cursor, or package containing the variable referenced). This means that if those elements are changed, the code in which the anchoring takes place is marked INVALID. When it is recompiled, the anchor will again be resolved, thereby keeping the code current with the anchored element.

Figure 7-1 shows how the datatype is drawn from both a database table and a PL/ SQL variable.

Figure 7-1. Anchored declarations with %TYPE

Here is anexample of anchoring a variable to a database column:

l_company_id company.company_id%TYPE;

You can also anchor against PL/SQL variables; this is usually done to avoid redundant declarations of the same hardcoded datatype. In this case, the best practice is to create a "reference" variable in a package and then reference that package variable in %TYPE statements. (You could also create SUBTYPEs in your package; this topic is covered later in the chapter.) The following example shows just a portion of a package intended to make it easier to work with Oracle Advanced Queuing (AQ):

/* File on web: aq.pkg */CREATE OR REPLACE PACKAGE aqIS /* Standard datatypes for use with Oracle AQ. */ SUBTYPE msgid_type IS RAW(16); SUBTYPE name_type IS VARCHAR2(49); ...END aq;

AQ message IDs are of type RAW(16). Rather than have to remember that (and hardcode it into my application again and again), I can simply declare an AQ message ID as follows:

DECLARE my_ msg_id aq.msgid_type;BEGIN

Then, if Oracle ever changes its datatype for a message ID, I can change my SUBTYPE definition in the aq package and all declarations will be updated with the next recompilation.

Anchored declarations provide an excellent illustration of the fact that PL/SQL is not just a procedural-style programming language, but was designed specifically as an extension to the Oracle SQL language. A very thorough effort was made by Oracle Corporation to tightly integrate the programming constructs of PL/SQL to the underlying SQL database.

Anchored declarations offer some important benefits when it comes to writing applications that adapt easily to change over time.