4.5 Anchored Declarations

Chapter 4
Variables and Program Data

 

---

This section describes the use of the %TYPE declaration attribute to anchor the datatype of one variable to another data structure, such as a PL/SQL variable or a column in a table. When you anchor a datatype, you tell PL/SQL to set the datatype of one variable from the datatype of another element.

The syntax for an anchored datatype is:

<variable name> <type attribute>%TYPE [optional default value assignment];

where <variable name> is the name of the variable you are declaring and <type attribute> is any of the following:

• Previously declared PL/SQL variable name

• Table column in format "table.column"

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

Figure 4.2: Anchored declarations with %TYPE

Here are some examples of %TYPE used in declarations:

• Anchor the datatype of monthly_sales to the datatype of total_sales:

  total_sales NUMBER (20,2); monthly_sales total_sales%TYPE;

• Anchor the datatype of the company ID variable to the database column:

  company_id# company.company_id%TYPE;

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 database (accessed through SQL).

NOTE: PL/SQL also offers the %ROWTYPE declaration attribute, which allows you to create anchored datatypes for PL/SQL record structures. %ROWTYPE is described in Chapter 9.

4.5.1 Benefits of Anchored Declarations

All the declarations you have so far seen -- character, numeric, date, Boolean -- specify explicitly the type of data for that variable. In each of these cases, the declaration contains a direct reference to a datatype and, in most cases, a constraint on that datatype. You can think of this as a kind of hardcoding in your program. While this approach to declarations is certainly valid, it can cause problems in the following situations:

• Synchronization with database columns. The PL/SQL variable "represents" database information in the program. If I declare explicitly and then change the structure of the underlying table, my program may not work properly.

• Normalization of local variables. The PL/SQL variable stores calculated values used throughout the application. What are the consequences of repeating (hardcoding) the same datatype and constraint for each declaration in all of my programs?

Let's take a look at each of these scenarios in more detail.

4.5.1.1 Synchronization with database columns

Databases hold information that needs to be stored and manipulated. Both SQL and PL/SQL perform these manipulations. Your PL/SQL programs often read data from a database into local program variables, and then write information from those variables back into the database.

Suppose I have a company table with a column called NAME and a datatype of VARCHAR2(60). I can therefore create a local variable to hold this data as follows:

DECLARE    cname VARCHAR2(60);

and then use this variable to represent this database information in my program. Now, consider an application which uses the company entity. There may be a dozen different screens, procedures, and reports which contain this same PL/SQL declaration, VARCHAR2(60), over and over again. And everything works just fine...until the business requirements change or the DBA has a change of heart. With a very small effort, the definition of the name column in the company table changes to VARCHAR2(100), in order to accommodate longer company names. Suddenly the database can store names which will raise VALUE_ERROR exceptions when FETCHed into the company_name variable.

My programs have become incompatible with the underlying data structures. All declarations of cname (and all the variations programmers employed for this data throughout the system) must be modified. Otherwise, my application is simply a ticking time bomb, just waiting to fail. My variable, which is a local representation of database information, is no longer synchronized with that database column.