0265-0268

Previous Table of Contents Next

Page 265

 CURSOR c_line_item RETURN line_item.merch_gross%TYPE;   ... END order_total; CREATE OR REPLACE PACKAGE BODY order_total AS   CURSOR c_line_item RETURN line_item.merch_gross%TYPE     SELECT merch_gross     FROM line_item     WHERE order_num = g_order_num;   ... END order_total; 

In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.

Procedure Variables

The most important feature of any language is how to define variables. Once you define the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follows the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating.

CAUTION

You must declare variables and constants before referencing them in any other statement.

Variable Declaration and Assignment

Any PL/SQL or SQL datatype is valid for variable definitions. The most commonly used datatypes are VARCHAR2, DATE, NUMBER (SQL datatypes), BOOLEAN, and BINARY_INTEGER (PL/SQL datatypes). PL/SQL scalar and composite datatypes are discussed in more detail later in this chapter.

Local Variables

Assume you want to declare two local variables named merch_gross and recip_count. The first, merch_gross, is to hold a 10-digit, floating-point number rounded to two decimal places; recip_count will hold an integer counter. Declare these variables as follows:

 merch_gross      NUMBER; recip_count        BINARY_INTEGER; 

Page 266

CAUTION

You can also declare merch_gross in this example as NUMBER(10,2) to explicitly show total digits and rounding. However, if it's related to a database field, a declaration of this type must change if the database definition changes.

You can use two methods to assign values to variables. The first is using an assignment operator:

 merch_gross := 10.50; 

The second method is to use a SQL SELECT or FETCH statement that assigns a database value:

 SELECT merch_gross INTO merch_gross FROM line_item WHERE order_num = g_order_num; 

Local Constants

Constant declaration is similar to variable declaration except that the CONSTANT keyword must follow the variable name . You must immediately assign a value to the CONSTANT:

 tax_rate CONSTANT NUMBER := 0.03; 

Global Variables

Global variables are defined in the same manner as local variables, but they are defined outside of all procedure definitions. Suppose you want to define the variables g_order_num and g_recip_counter to be available to all package subprograms. The following is an example of the syntax:

 CREATE OR REPLACE PACKAGE BODY      order_total AS      ... g_order_num        NUMBER; g_recip_counter    BINARY_INTEGER;      ... PROCEDURE      ... 

Notice that these global variables are defined in the package body specification area so they are not "seen" by applications that call the order_total packaged procedure.

CAUTION

If you use variable names that are the same as database column names , your results will be unpredictable when performing any database operations, such as SELECT or UPDATE, with the variables.

Page 267

The DEFAULT Keyword

The DEFAULT keyword enables you to initialize variables without using the assignment operator, as in the following example:

 merch_gross    NUMBER  DEFAULT  10.50; 

You can also use the DEFAULT keyword to initialize a subprogram's cursor parameters and fields in user -defined records.

Variable and Constant Attributes

The two attributes of PL/SQL variables and constants are %TYPE and %ROWTYPE. The %TYPE attribute enables you to declare variables similar to database columns without knowing the datatype of the column. You can define merch_gross from the previous example as follows:

 merch_gross    line_item.merch_gross%TYPE; 

Defining a variable in this manner enables you to put database changes in effect on the next compilation of a PL/SQL procedure without changing the code.

The %ROWTYPE attribute enables you to represent a row in a table with a record type that masks the database columns. Consider the sample database information in Table 12.1.

Table 12.1. Sample of data in table LINE_ITEM.

Column Name	Data
order_num	100
line_num	1
merch_gross	10.50
recipient_num	1000

You can define a cursor inside your procedure to pull information from the LINE_ITEM table. (See "Declaring Cursors" earlier in the chapter.) Along with defining the cursor, define a ROWTYPE variable to store the fields in this row:

 CURSOR c_line_item IS SELECT merch_gross, recipient_num FROM line_item WHERE order_num = g_ordnum; li_info  c_line_item%ROWTYPE; 

To retrieve the data, issue a FETCH:

 FETCH c_line_item INTO li_info; 

Page 268

After the FETCH, use dot notation to access the information pulled from the database:

 g_order_merch_total := g_order_merch_total + li_info.merch_gross; 

Scalar Datatypes

PL/SQL supports a wide range of scalar datatypes for defining variables and constants. Unlike composite datatypes, scalar datatypes have no accessible components . These datatypes fall into one of the following categories:

• Boolean
• Date/time
• Character
• Number

Now, take a closer look at the datatypes in each category.

Boolean

The BOOLEAN datatype, which takes no parameters, is used to store a binary value, TRUE or FALSE. This datatype can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this datatype.

Date/Time

The datatype DATE, which takes no parameters, is used to store date values. These DATE values include the time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE datatype are as follows:

Date First day of current month Time Midnight

Character

Character datatypes include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW datatypes take an optional parameter for specifying length:

 datatype(max_len) 

This length parameter, max_len, must be an integer literal, not a constant or variable. Table 12.2 shows maximum lengths and database column widths of character datatypes.

Previous Table of Contents Next