Inserting New Data Rows in Procedures
Using Variables and Constants
Developing and Using Stored Procedures 4-21
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;
Compile and save
emp_eval
Body.
Similarly, add a new
add_eval
procedure for inserting new records into the
evaluations
table, based on the content of the corresponding row in the
employees
table. Note that
add_eval
is using the sequence
evaluations_seq
.
Example 45 Creating a new table row with values from another table
In the
emp_eval
Body pane, above the line
END emp_eval
, add procedure
add_
eval
, which uses some columns from the
employees
table to insert rows into the
evaluations
table. Note also that you will create the local function
add_eval
in the
body of the
emp_eval
package, but not declare it in the package specification. This
means that
add_eval
may be invoked only within the
emp_eval
package, by
another subprogram.
PROCEDURE add_eval(employee_id IN employees.employee_id%TYPE, today IN DATE) AS
-- placeholders for variables
job_id employees.job_id%TYPE;
manager_id employees.manager_id%TYPE;
department_id employees.department_id%TYPE;
BEGIN
-- extracting values from employees for later insertion into evaluations
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;
-- inserting a new row of values into evaluations table
INSERT INTO evaluations VALUES (
evaluations_seq.NEXTVAL, -- evaluation_id
employee_id, -- employee_id
today, -- evaluation_date
job_id, -- job_id
manager_id, -- manager_id
department_id, -- department_id
0); -- total_score
END add_eval;
Compile and save
emp_eval
Body.
See Also:
Oracle Database PL/SQL Language Reference for more information
on assigning values to variables
