Then you can reference any one of the fields of the emp_rec directly, like this:

IF emp_rec.pay_type is null THEN...

Record Variables Based on Cursor Definitions

Records that are based on an Oracle cursor draw their structure from the SELECT statement in the cursor. This type of record has the same number, name, and datatypes of columns as those in the cursor. The %rowtype attribute is used to declare the record that is based on a cursor. In the following example, the record named emp_rec is based on the cursor named get_emp_cur:

DECLARE

CURSOR get_emp_cur is         -- cursor declaration
SELECT emp_name, pay_rate
from employee
WHERE pay_type = `H';

emp_rec  get_emp_cur%rowtype; -- record declaration

As you did with other Oracle records, you use dot notation to reference a specific field in the record, as shown following:

IF emp_rec.pay_rate > 3000 THEN ....

Declarations at the Package Level

Declaring a package has two parts: the package specification and the package body. The package body defines all the functions, procedures, and other constructs that are declared in the package specification. The package specification declares all variables, constants, cursors, procedures, and functions. This section reviews the declarations of variables in the package specification.

The package specification contains the declaration of all objects that will be used in the package body. The following are several examples of a package specification that declares a cursor, variable, constant, and record:

package emp_data is     -- package specification

pay_raise     constant real := 1.25;
high_rate     INTEGER;
CURSOR salary_cur (emp_id INTEGER, pay_rate NUMBER (9,2));
TYPE sal_rec is record (emp_name VARCHAR2(32), pay_rate NUMBER (9,2));
emp_rec  salary_cur%rowtype;

END emp_data;

One of the advantages of declaring items in a package is that they are global in nature and accessible by all. This means that any program in your application can use the variables, cursors, constants, and records declared in the package specification. You treat these items

(except constants) as if they were declared locally, in that you can change their values as needed. This global nature of the data is only within a session and is not available across multiple sessions. This means, for example, that the variables running in one user's applications are not accessible to a different user's application unless the DBMS_PIPE package is used.

PL/SQL Tables

Like the PL/SQL record, the table is another composite datatype. PL/SQL tables are objects of type TABLE, and look similar to database tables but differ slightly. The following sections explore the PL/SQL table.

A PL/SQL table will have at least one column and one primary key. These two items remain unnamed. The column can have any datatype, whereas the primary key must be of the type BINARY_INTEGER. These tables are unconstrained in physical size and grow dynamically when rows are added. Hence, a row in a PL/SQL table does not exist until the primary key and single column are added to the table.

Declaring a PL/SQL Table

Declare the PL/SQL table in the declarative portion of your block, package, or other subprogram. The following declares a PL/SQL table called emp_table, which is based on the existing column emp_id in the employee table:

DECLARE
TYPE emp_table is table of employee.emp_id%type
INDEX BY BINARY_INTEGER;

You can also declare this as a not null table, as shown following:

DECLARE
TYPE emp_table is table of employee.emp_id%type not null
INDEX BY BINARY_INTEGER;

In both of the preceding examples, the clause INDEX BY BINARY_INTEGER is a mandatory feature of the PL/SQL table declaration. The INDEX BY clause is of the type BINARY_INTEGER, as this will lead to the fastest retrieval on the binary primary key.

Referencing Values in a PL/SQL Table

To reference a specific row in a PL/SQL table, you specify a primary key value using the array-like syntax. For example, to evaluate the twelfth employee from the emp_table (defined earlier), the statement would look like

IF emp_table(12) like `Loretta%' THEN ....

Likewise, the following examples are valid references to PL/SQL tables, including using a table as a parameter:

building_floor_max_table(current_floor + 21);
part_num_cat (-150);

procedure calc_new_salaries (calc_salaries in emp_table);

Using a PL/SQL Table

PL/SQL allows you to manipulate the rows of a PL/SQL table, similar to a normal database table. You can insert, update, and delete rows in a PL/SQL table.

PL/SQL Table Insert

To insert rows into a PL/SQL table, you must use an iterative construct. As shown in the following example, to insert records into the guest_count_table, the WHILE statement is used. This PL/SQL block will insert the first ten thousand guests into the table:

DECLARE                  -- declare table type
TYPE guest_count_table
is table of VARCHAR2(55)
INDEX BY BINARY_INTEGER;

procedure find_10000_guest_table
(start_date  in date,
guest_count_out out guest_count_table)
is
guest_count BINARY_INTEGER := 0;
BEGIN
WHILE guest_count <= 10000          -- begin iterations
LOOP
guest_count = guest_count + 1;      -- guest (row) counter
/* the following inserts into the PL/SQL table */
guest_count_out (guest_count) := curr_guest_function;
END LOOP;
END find_10000_guest_table;

You can also directly assign a value into the PL/SQL table. The following line will insert a record into the emp_table:

emp_table(102) := `Jacqueline Loraine';

Remember that the PL/SQL table is unconstrained and can contain virtually an unlimited number of rows. It is a good idea to manually track the current row count so that you can insert rows sequentially. This is easily done with a counter that you increment inside your loop. Likewise, the PL/SQL table function count is available to tell you how many rows are in the table. This function is covered in detail later in this chapter in the section "PL/SQL Table Functions."