Assigning Values to Variables with the SELECT INTO Statement

To use table values in subprograms or packages, you must assign them to variables with SELECT INTO statements.

The following example shows, in bold font, the changes to make to the EMP_EVAL.calculate_score function to have it calculate running_total from table values.

Assigning Table Values to Variables with SELECT INTO

FUNCTION calculate_score ( evaluation_id IN scores.evaluation_id%TYPE
                         , performance_id IN scores.performance_id%TYPE )
                         RETURN NUMBER AS

  n_score       scores.score%TYPE;
  n_weight      performance_parts.weight%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT scores.score%TYPE := 9;
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
BEGIN
  SELECT s.score INTO n_score
  FROM SCORES s
  WHERE evaluation_id = s.evaluation_id 
  AND performance_id = s.performance_id;

  SELECT p.weight INTO n_weight
  FROM PERFORMANCE_PARTS p
  WHERE performance_id = p.performance_id;

  running_total := n_score * n_weight;
  RETURN running_total;
END calculate_score;

The add_eval procedure in the following example inserts a row into the EVALUATIONS table, using values from the corresponding row in the EMPLOYEES table. Add the add_eval procedure to the body of the EMP_EVAL package, but not to the specification. Because it is not in the specification, add_eval is local to the package—it can be invoked only by other subprograms in the package, not from outside the package.

Inserting a Table Row with Values from Another Table

PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
                   , today IN DATE )
AS
  job_id         EMPLOYEES.JOB_ID%TYPE;
  manager_id     EMPLOYEES.MANAGER_ID%TYPE;
  department_id  EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
  SELECT e.job_id INTO job_id
  FROM EMPLOYEES e
  WHERE employee_id = e.employee_id;

  SELECT e.manager_id INTO manager_id
  FROM EMPLOYEES e 
  WHERE employee_id = e.employee_id;

  SELECT e.department_id INTO department_id
  FROM EMPLOYEES e
  WHERE employee_id = e.employee_id;
 
  INSERT INTO EVALUATIONS (
    evaluation_id,
    employee_id,
    evaluation_date,
    job_id,
    manager_id,
    department_id,
    total_score
  )
  VALUES (
    evaluations_seq.NEXTVAL,   -- evaluation_id
    add_eval.employee_id,      -- employee_id
    add_eval.today,            -- evaluation_date
    add_eval.job_id,           -- job_id
    add_eval.manager_id,       -- manager_id
    add_eval.department_id,    -- department_id
    0                          -- total_score
  );
END add_eval;

Assigning Values to Variables with the SELECT INTO Statement

Related Topics