|
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.
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 .
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.
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.
Another drawback to explicit declarations arises when working with PL/SQL variables which store and manipulate calculated values not found in the database. Suppose my programmers built an application to manage my company's finances. I am very bottom-line oriented, so many different programs make use of a total_revenue variable, declared as follows:
total_revenue NUMBER (10,2);
Yes, I like to track my total revenue down to the last penny. Now, in 1992, when specifications for the application were first written, the maximum total revenue I ever thought I could possibly obtain from any single customer was $99 million, so we used the NUMBER (10,2) declaration, which seemed like plenty. Then in 1995, my proposal to convert B-2 bombers to emergency transport systems to deliver Midwestern wheat to famine regions was accepted: a $2 billion contract! I was just about ready to pop the corks on the champagne when my lead programmer told me the bad news: I wouldn't be able to generate reports on this newest project and customer: those darn total_revenue variables were too small!
What a bummer...I had to fire the guy.
Just kidding. Instead, we quickly searched out any and all instances of the revenue variables so that we could change the declarations. This was a time-consuming job because we had spread equivalent declarations throughout the entire application. I had, in effect, denormalized my local data structures, with the usual consequences on maintenance. If only I had a way to define each of local total revenue variables in relation to a single datatype.
If only they had used %TYPE!
The %TYPE declaration attribute anchors the datatype of one variable to that of another data structure at the time a PL/SQL block is compiled. If a change is made to the "source" datatype, then any program which contains a declaration anchored to this datatype must be recompiled before it will be able to use this new state of the datatype.
The consequences of this rule differ for PL/SQL modules stored in the database and those defined in client-side tools, such as Oracle Forms.
Consider the following declaration of company_name in the procedure display_company:
PROCEDURE display_company (company_id_in IN INTEGER) IS company_name company.name%TYPE; BEGIN ... END;
When PL/SQL compiles this module, it looks up the structure of the company table in the data dictionary, finds the column NAME, and obtains its datatype. It then uses this data dictionary-based datatype to define the new variable.
What, then, is the impact on the compiled display_company procedure if the datatype for the name column of the company table changes? There are two possibilities:
If display_company is a stored procedure, then the compiled code will be marked as "invalid." The next time a program tries to run display_company, it will be recompiled automatically before it is used.
If display_company is a client-side procedure, then the Oracle Server cannot mark the program as invalid. The compiled client source code remains compiled using the old datatype. The next time you execute this module, it could cause a VALUE_ERROR exception to be raised.
Whether stored or in client-side code, you should make sure that all affected modules are recompiled after data structure changes.
You can nest usages of %TYPE in your declarations as well:
DECLARE /* The "base" variable */ unlimited_revenue NUMBER; /* Anchored to unlimited revenue */ total_revenue unlimited_revenue%TYPE; /* Anchored to total revenue */ total_rev_94 total_revenue%TYPE; total_rev_95 total_revenue%TYPE; BEGIN
In this case total_revenue is based on unlimited_revenue and both variables for 1994 and 1995 are based on the total_revenue variable. There is no practical limit on the number of layers of nested usages of %TYPE.
The declaration of the source variable for your %TYPE declarations does not need to be in the same declaration section as the variables which use it. That variable must simply be visible in that section. The variable could be a global PL/SQL variable (defined in a package) or be defined in an PL/SQL block which contains the current block, as in the following example:
PROCEDURE calc_revenue IS unlimited_revenue NUMBER; total_revenue unlimited_revenue%TYPE; BEGIN IF TO_CHAR (SYSDATE, 'YYYY') = '1994' THEN DECLARE total_rev_94 total_revenue%TYPE; BEGIN ... END; END IF; END calc_revenue;
When you declare a variable, you can also specify the need for the variable to be NOT NULL This NOT NULL declaration constraint is transferred to variables declared with the %TYPE attribute. If I include a NOT NULL in my declaration of a source variable (one that is referenced afterwards in a %TYPE declaration), I must also make sure to specify a default value for the variables which make use of that source variable. Suppose I declare max_available_date NOT NULL in the following example:
DECLARE max_available_date DATE NOT NULL := LAST_DAY (ADD_MONTHS (SYSDATE, 3)); last_ship_date max_available_date%TYPE;
The declaration of last_ship_date will then fail to compile, with the following message:
a variable declared NOT NULL must have an initialization assignment.
If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column.
The NOT NULL column constraint does not apply to variables declared with the %TYPE attribute. The following code will compile successfully:
DECLARE -- Company name is a NOT NULL column in the company table. comp_name company.name%TYPE; BEGIN comp_name := NULL;
You will be able to declare the comp_name variable without specifying a default, and you will be able to NULL out the contents of that variable.
|
|
|
|
| 4.4 Variable Declarations |
|
4.6 Programmer-Defined Subtypes |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.