Declaring Datatypes for PL/SQL Variables

Declaring Datatypes for PL/SQL Variables

As part of the declaration for each PL/SQL variable, you declare its datatype. Usually, this datatype is one of the types shared between PL/SQL and SQL, such as NUMBER or VARCHAR2. For easier maintenance of code that interacts with the database, you can also use the special qualifiers %TYPE and %ROWTYPE to declare variables that hold table columns or table rows. For more information on datatypes, see Chapter 3, "PL/SQL Datatypes".
%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named last_name in a table named employees. To declare a variable named v_last_name that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows:

v_last_name employees.last_name%TYPE;

Declaring v_last_name with %TYPE has two advantages. First, you need not know the exact datatype of last_name. Second, if you change the database definition of last_name, perhaps to make it a longer character string, the datatype of v_last_name changes accordingly at run time.

For more information on %TYPE, see "Using the %TYPE Attribute" and "%TYPE Attribute".
%ROWTYPE

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. See "Cursors".

Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you declare a record named dept_rec. Its fields have the same names and datatypes as the columns in the departments table.

DECLARE
  dept_rec departments%ROWTYPE; -- declare record variable

You use dot notation to reference fields, as the following example shows:

v_deptid := dept_rec.department_id;

If you declare a cursor that retrieves the last name, salary, hire date, and job class of an employee, you can use %ROWTYPE to declare a record that stores the same information as shown in Example 1-6. When you execute the FETCH statement, the value in the last_name column of the employees table is assigned to the last_name field of employee_rec, the value in the salary column is assigned to the salary field, and so on.

Example 1-6 Using %ROWTYPE with an Explicit Cursor

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary, hire_date, job_id FROM employees
       WHERE employee_id = 120;
-- declare record variable that represents a row fetched from the employees table
   employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor and use it to fetch data into employee_rec
  OPEN c1;
  FETCH c1 INTO employee_rec;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
END;
/