14.3. Variable Declarations

14.3.1. Using %TYPE in Variable Declarations

Often times variables will be declared in SPL programs that will be used to hold values from tables in the database. In order to ensure compatibility between the table columns and the SPL variables, the data types of the two should be the same.

However, as quite often happens, a change might be made to the table definition. If the data type of the column is changed, the corresponding change may be required to the variable in the SPL program.

Instead of coding the specific column data type into the variable declaration the column attribute, %TYPE, can be used instead. A qualified column name in dot notation or the name of a previously declared variable must be specified as a prefix to %TYPE. The data type of the column or variable prefixed to %TYPE is assigned to the variable being declared. If the data type of the given column or variable changes, the new data type will be associated with the variable without the need to modify the declaration code.

name { table.column | variable }%TYPE;

name is the identifier assigned to the variable or formal parameter that is being declared. column is the name of a column in table. variable is the name of a variable that was declared prior to the variable identified by name.

In the following example a procedure queries the emp table using an employee number, displays the employee's data, finds the average salary of all employees in the department to which the employee belongs, and then compares the chosen employee's salary with the department average.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN NUMBER
)
IS
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_deptno        NUMBER(2);
    v_avgsal        NUMBER(7,2);
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || v_deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = v_deptno;
    IF v_sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
            || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
            || v_avgsal);
    END IF;
END;

Instead of the above, the procedure could be written as follows without explicitly coding the emp table data types into the declaration section of the procedure.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_avgsal        v_sal%TYPE;
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || v_deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = v_deptno;
    IF v_sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
            || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
            || v_avgsal);
    END IF;
END;

The following is sample output from executing this procedure.

EXEC emp_sal_query(7698);

Employee # : 7698
Name       : BLAKE
Job        : MANAGER
Hire Date  : 01-MAY-81
Salary     : 2850
Dept #     : 30
Employee's salary is more than the department average of 1566.67

14.3.2. Record Declarations

A record is a named, ordered collection of fields. A field is similar to a variable - it has an identifier and data type, but also has the property that it belongs to a record and must be referenced using dot notation with the record name as its qualifier. The TYPE IS RECORD statement is used to define a record type. A record type is a definition of a record and in itself, cannot be used to manipulate data.

The following is the syntax for defining a record type.

TYPE rectype_name IS RECORD ( field_1 datatype_1 [, field_2 datatype_2 ] ... );

rectype_name is an identifier assigned to the record type. field_1, field_2,... are identifiers assigned to the fields of the record type. datatype_1, datatype_2,... are the data types of field_1, field_2,... respectively.

In order to make use of a record type, a record variable must be declared with that record type. The following is the syntax for declaring a record variable.

recvariable_name rectype_name

recvariable_name is an identifier assigned to the record variable. rectype_name is the identifier of a previously declared record type.

Dot notation is used to make reference to the fields in the record.

recvariable_name.field

recvariable_name is a previously declared record variable and field is the identifier of a field belonging to the record type from which recvariable_name is defined.

The following example a procedure queries the emp table using an employee number, displays the employee's data, finds the average salary of all employees in the department to which the employee belongs, and then compares the chosen employee's salary with the department average salary making use of an explicit RECORD type:

CREATE OR REPLACE PROCEDURE emp_sal_rec (
    p_empno         IN emp.empno%TYPE
)
IS
    TYPE r_emp IS RECORD(
        emp_no          emp.empno%TYPE,
        emp_name        emp.ename%TYPE,
        emp_job         emp.job%TYPE,
        emp_hiredate    emp.hiredate%TYPE,
        emp_sal         emp.sal%TYPE,
        emp_deptno      emp.deptno%TYPE
    );
    r               r_emp;
    v_avgsal        NUMBER(7,2);
BEGIN
    SELECT empno, ename, job, hiredate, sal, deptno INTO r FROM emp
        WHERE empno = p_empno;

    DBMS_OUTPUT.PUT_LINE('Employee # : ' || r.emp_no);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || r.emp_name);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || r.emp_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || r.emp_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || r.emp_sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || r.emp_deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = r.emp_deptno;
    IF r.emp_sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
            || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
            || v_avgsal);
    END IF;
END;

Following is the output from executing this stored procedure:

EXEC  emp_sal_rec(7698);
INFO:  Employee # : 7698
INFO:  Name       : BLAKE
INFO:  Job        : MANAGER
INFO:  Hire Date  : 01-MAY-81 00:00:00
INFO:  Salary     : 2850.00
INFO:  Dept #     : 30
INFO:  Employee's salary is more than the department average of 1566.67   
   

14.3.3. Table Declarations

Just like a record a table can also be declared as a TYPE in SPL. The TYPE IS TABLE statement is used to define a table type.

The following is the syntax for defining a table type.

TYPE table_type_name IS TABLE OF column_type INDEX BY BINARY_INTEGER;

table_type_name is the name of the table structure you are creating.

column_type is the datatype of the single column in the table which must be a scalar or record datatype.

You must always specify INDEX BY BINARY_INTEGER at the end of the TYPE...TABLE statement which acts like a primary key.

In order to make use of a table type, a table variable must be declared with that table type. The following is the syntax for declaring a record variable.

tblvariable_name tbltype_name

tblvariable_name is an identifier assigned to the table variable. tbltype_name is the identifier of a previously declared table type.

The following example shows a simple TABLE type which contains based on a record datatype:

CREATE or REPLACE PROCEDURE load_emp IS 
  TYPE employee IS TABLE of emp%ROWTYPE INDEX BY binary_integer; 
  e employee; 
CURSOR c IS SELECT * FROM emp; 
  i int := 1; 
BEGIN 
     OPEN c; 
        LOOP 
           FETCH c INTO e (i); 
           EXIT WHEN c%notfound; 
           i := i+1; 
        END LOOP; 
     CLOSE c; 
     
     FOR i IN 1 .. e.count LOOP 
        DBMS_OUTPUT.PUT_LINE ('Employee Number : ' || e(i).empno, 
                              'Employee Name : '||e(i).ename, 
                              'Employee Department Number : '||e(i).deptno); 
     END LOOP; 
END; 

EXEC load_emp;

The above Example has the following output:

INFO:  ("Employee Number : 7369","Employee Name : SMITH","Employee Department Number : 20")
INFO:  ("Employee Number : 7499","Employee Name : ALLEN","Employee Department Number : 30")
INFO:  ("Employee Number : 7521","Employee Name : WARD","Employee Department Number : 30")
INFO:  ("Employee Number : 7566","Employee Name : JONES","Employee Department Number : 20")
INFO:  ("Employee Number : 7654","Employee Name : MARTIN","Employee Department Number : 30")
INFO:  ("Employee Number : 7698","Employee Name : BLAKE","Employee Department Number : 30")
INFO:  ("Employee Number : 7782","Employee Name : CLARK","Employee Department Number : 10")
INFO:  ("Employee Number : 7788","Employee Name : SCOTT","Employee Department Number : 20")
INFO:  ("Employee Number : 7839","Employee Name : KING","Employee Department Number : 10")
INFO:  ("Employee Number : 7844","Employee Name : TURNER","Employee Department Number : 30")
INFO:  ("Employee Number : 7876","Employee Name : ADAMS","Employee Department Number : 20")
INFO:  ("Employee Number : 7900","Employee Name : JAMES","Employee Department Number : 30")
INFO:  ("Employee Number : 7902","Employee Name : FORD","Employee Department Number : 20")
INFO:  ("Employee Number : 7934","Employee Name : MILLER","Employee Department Number : 10")
   

14.3.4. Using %ROWTYPE in Record Declarations

Using the %TYPE attribute provides an easy way to create a variable dependent upon a column's data type. Using the %ROWTYPE attribute, a record can be defined that contains fields corresponding to all columns of a given table. Each field takes on the data type of its corresponding column.

A record is a named, ordered collection of fields. A field is similar to a variable - it has an identifier and data type, but also has the property that it belongs to a record and must be referenced using dot notation with the record name as its qualifier.

A record can be declared using the %ROWTYPE attribute. The %ROWTYPE attribute is prefixed by a table name. Each column in the named table defines an identically named field in the record with the same data type as the column.

record table%ROWTYPE;

record is an identifier assigned to the record. table is the name of a table whose columns are to define the fields in the record.

The following example shows how the emp_sal_query procedure from the prior section can be modified to use emp%ROWTYPE to create a record named r_emp instead of declaring individual variables for the columns in emp.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    r_emp           emp%ROWTYPE;
    v_avgsal        emp.sal%TYPE;
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || r_emp.deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = r_emp.deptno;
    IF r_emp.sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the department average of '
            || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the department average of '
            || v_avgsal);
    END IF;
END;