Additional PL/SQL Datatypes

On Day 9, "Programming an Oracle Database with PL/SQL," I presented some of the essentials of PL/SQL programming. As you've seen, PL/SQL supports all the datatypes that are available in SQL. However, PL/SQL also provides the following additional datatypes that aren't available for use in ordinary SQL statements:

• BOOLEAN
• BINARY_INTEGER, NATURAL, and POSITIVE
• %TYPE
• %ROWTYPE
• The PL/SQL table (or array)
• The user-defined record

The BOOLEAN Datatype

One of the additional datatypes that PL/SQL supports is BOOLEAN. Listing 10.12 illustrates how you declare a BOOLEAN variable. You also can initialize a BOOLEAN variable to either TRUE or FALSE.

Listing 10.12. Declaring a BOOLEAN variable and initializing it.

SQL> set serveroutput on
SQL>
SQL> declare
  2  Payment_Is_Late  boolean := TRUE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  if Payment_Is_Late then
  6     dbms_output.put_line(`The payment is late!');
  7  end if;
  8
  8  end;
  9  /
The payment is late!
PL/SQL procedure successfully completed.

ANALYSIS
In line 2, the BOOLEAN variable, Payment_Is_Late, is initialized to TRUE. In line 5, Payment_Is_Late is evaluated; because line 5 is equal to TRUE, line 6 is executed.

Until you assign a value to it, a BOOLEAN variable has the null value. In Listing 10.13, the BOOLEAN expression Day_of_Month > 5 is assigned to the BOOLEAN variable Payment_Is_Late.

Listing 10.13. Assigning BOOLEAN expressions to a BOOLEAN variable.

SQL> set serveroutput on
SQL>
SQL> declare
  2  Payment_Is_Late  boolean;
  3  Day_of_Month     integer;
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  select to_number(to_char(sysdate,'DD'))
  7    into Day_of_Month
  8  from dual;
  9
  9  Payment_Is_Late := Day_of_Month > 3;
 10
 10  if Payment_Is_Late then
 11     dbms_output.put_line(`The payment is late!');
 12  end if;
 13
 13  end;
 14  /
The payment is late!
PL/SQL procedure successfully completed.

The BINARY_INTEGER Datatype

The BINARY_INTEGER datatype stores signed integers in the range of _2,147,483,647 to 2,147,483,647. PL/SQL also provides two other datatypes that are subtypes of BINARY_INTEGER.

• NATURAL can store integers in the range of 0 to 2,147,483,647.
• POSITIVE can store integers in the range of 1 to 2,147,483,647.

You might want to declare variables that would never have a fractional part, such as a loop counter, with the NATURAL or POSITIVE datatype.

When you assign a real number to a variable that has been declared as BINARY_INTEGER, NATURAL, or POSITIVE, the number is truncated. Listing 10.14 provides an example of this.

Listing 10.14. A real number is truncated when assigned to a PL/SQL integer datatype.

SQL> declare
  2  Counter            natural;
  3
  3  begin
  4

  4  dbms_output.enable;
  5
  5  Counter := 103.2;
  6
  6  dbms_output.put_line(`Counter: ` || to_char(Counter,'999.999'));
  7
  7  end;
  8  /
Counter:  103.000

Using %TYPE

PL/SQL offers two notations for referencing Oracle table and column datatypes.

• Use %TYPE to declare a variable with the same datatype as a specified column (or previously declared variable).
• Use %ROWTYPE to declare a composite variable whose structure mirrors that of a specified table or cursor.

These two datatypes help integrate PL/SQL code with the table and column definitions that exist in the Oracle data dictionary.

To define a variable as having the same datatype as a column, use the %TYPE designation with the following syntax:

variable-name      table-name.column-name%TYPE;

The variables are defined as follows:

variable-name is the PL/SQL variable being declared.

table-name.column-name specifies the column whose datatype should be used for variable-name.

The beauty of using %TYPE is that it generally reduces the amount of work needed to maintain PL/SQL code. For example, you can increase or decrease the width of a column without having to change the declaration of any PL/SQL variables based on that column.

Using %ROWTYPE

You use the %ROWTYPE designation to declare a variable—a record, really—whose structure is identical to the structure of a specified table.

%ROWTYPE is used with the following syntax:

variable-name table-name%ROWTYPE;

The variables are defined as follows:

variable-name is the PL/SQL variable being declared.

table-name specifies the table to which variable-name corresponds.

For example, a record named Instructor_Rec is declared as Instructor%ROWTYPE. As a result, Instructor_Rec's fields have the same names and datatypes as the columns of the Instructor table.

Listing 10.15. Using %ROWTYPE in a SELECT statement.

SQLWKS> declare
     2>
     3> Instructor_Rec    Instructor%ROWTYPE;
     4>
     5> begin
     6>
     7> dbms_output.enable;
     8>
     9> select *
    10> into Instructor_Rec
    11> from Instructor
    12> where
    13> Instructor_ID = `P331';
    14>
    15> dbms_output.put_line(`Instructor ID: ` ||
ÂInstructor_Rec.Instructor_ID);
    16> dbms_output.put_line(`Last Name: ` || Instructor_Rec.Last_Name);
    17> dbms_output.put_line(`First Name: ` ||
ÂInstructor_Rec.First_Name);
    18>
    19> end;
    20> /
Statement processed.
Instructor ID: P331
Last Name: POULSON
First Name: RANIER

As you can see (line 15 through 17), the fields of a %ROWTYPE record are referenced by

variable-name.field-name

variable-name is the name of the declared %ROWTYPE variable.

field-name is the name of a column in the table specified in variable-name's declaration.

You also can assign one variable to another variable if they are both declared using the %ROWTYPE designation for the same table. Listing 10.16 illustrates this concept by assigning New_Patient to ER_Patient.

Listing 10.16. Assigning PL/SQL variables based on %ROWTYPE.

SQL> declare
  2
  2  New_Patient   Patient%ROWTYPE;
  3  ER_Patient    Patient%ROWTYPE;
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  select *
  7  into New_Patient
  8  from Patient
  9  where
 10  Patient_ID = `ZZ0123';
 11
 11  ER_Patient := New_Patient;
 12
 12  dbms_output.put_line(`ER_Patient.Body_Temp_Deg_F: ` ||

                                                    continues