049-053 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Datatypes

Table 2.3 lists a number of datatypes that are usable inside PL/SQL blocks, in addition to the default datatypes supported by the Oracle7 database.

**Table 2.3** PL/SQL supported datatypes.

Datatype	Description
binary_integer	Variables of this type store signed integers, ranging from -2 ³¹ - 1 through 2 ³¹ -1 (-2147483647..2147483647). Unlike number values, variables of the binary_integer type (and its subtypes ) can be used in expressions without being converted (theoretically providing a performance boost).
natural	A subtype of the binary_integer type that holds only positive integers ranging from 0 through 2 ³¹ - 1 (0..2147483647).
positive	A subtype of the binary_integer type that holds only positive integers ranging from 1 through 2 ³¹ - 1 (1..2147483647).
dec, decimal, double precision, float, integer, int, numeric, real, smallint	Subtypes of the number type that have the same constraints and range of values as the number type.
boolean	This type contains the values TRUE and FALSE . Variables of this type can also contain a NULL value. Since this datatype isn t implicitly supported by the database engine, you cannot define a column using this type or select output into a variable of this type.

In addition to the datatypes listed in Table 2.3, PL/SQL allows developers to define custom datatypes.

TIP: Datatype Conversions
Like Oracle and SQL, PL/SQL supports both implicit and explicit conversions of datatypes. The use of explicit conversions is strongly suggested.

Records

PL/SQL allows developers to create a record containing one or more columns . To use records, the record type must be specified before a variable can be created based on the type; this is shown by Listing 2.14. Figure 2.8 shows the structure of a record.

Figure 2.8 The structure of a PL/SQL record.

Listing 2.14 A PL/SQL record declaration.

 TYPE Student_rectype IS RECORD          (first_name          varchar2 (12),           last_name           varchar2 (15),           gpa                 number (3, 2));      Student_rec     Student_rectype;

Individual columns within Student_rec are referenced as

 Student_rec.first_name

Records are composite datatypes , constructed by the developer to suit a particular set of needs. ( Scalar datatypes are those datatypes that are automatically supported by the database engine and PL/SQL, like varchar2 , integer , and date .) The values returned from cursors are often fetched into record variables, which are then referenced by PL/SQL statements both as whole records and as individual columns.

Tables

A PL/SQL table is very similar to an array in C or Pascal. Like a record, the PL/SQL table must be declared first as a type declaration and then as a variable of the user -defined type, as shown in Listing 2.15.

Listing 2.15 A PL/SQL table declaration.

 DECLARE    TYPE Student_SSN_tabtype IS TABLE OF        integer (9)        INDEX BY binary_integer;    Student_SSN_table      Student_SSN_tabtype;

Like records, the PL/SQL table is a composite datatype. The number of rows that can be held in a PL/SQL table is limited only by the range of values for the INDEX variable. The PL/SQL table is indexed using a signed integer and can be navigated either forward or backward (unlike cursors, which can only be moved forward). Figure 2.9 illustrates the structure of a PL/SQL table.

Figure 2.9 The structure of a PL/SQL table.

As can be seen in the following line of code, references to records in a PL/SQL table are very similar to references to an array in C or Pascal.

 Student_SSN_table (1)

The main difference in the reference is the use of parentheses instead of square brackets to reference the index variable.

TIP: Referencing Elements In A PL/SQL Table
The elements in a PL/SQL table must be initialized to some value (even NULL ) before you can make any other reference to it in your code. If you reference an element that has not been initialized to some value, a NO_DATA_FOUND exception will be raised. Unlike the scalar datatypes, a PL/SQL table element does not automatically contain a NULL value. The safest method for dealing with these elements is to initialize each element to NULL , even before attempting to populate the element with another value.

Prior to version 2.3, PL/SQL tables could only have a scalar datatype column in addition to the index column. PL/SQL version 2.3 allows composite datatypes (records and tables) to be referenced inside a PL/SQL table. This limitation was probably the primary factor limiting the use of PL/SQL tables in applications. PL/SQL tables will become more common now that they are not quite as unwieldy to use.

Variables And Constants

Declaring variables and constants in a PL/SQL block is quite simple, as shown by the following section of code:

 DECLARE    vStudentFirstName     varchar2 (12);    <other variable declarations>    YES     CONSTANT      char (1) := 'Y';

A constant must be initialized when it is declared. Variables may be initialized when they are declared or after the BEGIN statement. Once initialized, a PL/SQL variable may be referenced in an embedded SQL statement or an expression.

TIP: Initializing Variables
It s a good idea to initialize most variables when they are declared. If you don t initialize a variable in the DECLARE subsection of the block, make sure you initialize it prior to using it in an expression or you ll get stung by a comparison with a NULL value.

Table of Contents