Ensuring that Variables, Constants, and Parameters Have Correct Data Types

After "Tutorial: Declaring Variables and Constants in a Subprogram", the code for the calculate_score function, in the body of the package EMP_EVAL, is:

FUNCTION calculate_score ( evaluation_id IN NUMBER
                          , performance_id IN NUMBER )
                          RETURN NUMBER AS
  n_score       NUMBER(1,0);                -- variable
  n_weight      NUMBER;                     -- variable
  max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
  max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
  BEGIN
    /* TODO implementation required */
    RETURN NULL;
  END calculate_score;

The variables, constants, and parameters of the function represent values from the tables SCORES (created in "Creating Tables with the CREATE TABLE Statement") and PERFORMANCE_PARTS (created in "Tutorial: Creating a Table with the Create Table Tool"):

• Variable n_score will hold a value from the SCORE column of the SCORES table, and constant max_score will be compared to such values.

• Variable n_weight will hold a value from the WEIGHT column of the PERFORMANCE_PARTS table, and constant max_weight will be compared to such values.

• Parameter evaluation_id will hold a value from the EVALUATION_ID column of the SCORES table.

• Parameter performance_id will hold a value from the PERFORMANCE_ID column of the SCORES table.

Therefore, each variable, constant, and parameter has the same data type as its corresponding column.

If the data types of the columns change, you want the data types of the variables, constants, and parameters to change to the same data types; otherwise, the calculate_score function will be invalidated.

To ensure that the data types of the variables, constants, and parameters will always match those of the columns, declare them with the %TYPE attribute. The %TYPE attribute supplies the data type of a table column or another variable, ensuring the correct data type assignment.