Table of Contents Previous Next


3 Stored Procedure Language : 3.3 Variable Declarations : 3.3.2 Using %TYPE in Variable Declarations

Often, 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.
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.
Note: The %TYPE attribute can be used with formal parameter declarations as well.
name { { table | view }.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 or view. variable is the name of a variable that was declared prior to the variable identified by name.
Note: The variable does not inherit any of the column’s other attributes such as might be specified on the column with the NOT NULL clause or the DEFAULT clause.
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.
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.
Note: p_empno shows an example of a formal parameter defined using %TYPE.
v_avgsal illustrates the usage of %TYPE referring to another variable instead of a table column.

3 Stored Procedure Language : 3.3 Variable Declarations : 3.3.2 Using %TYPE in Variable Declarations

Table of Contents Previous Next