Using a RECORD Datatype
Using Composite Data Structures; Records
Developing and Using Stored Procedures 4-31
situation, you could declare all the subprograms that are not already declared in the
package specification at the top of the package body. The definition of the subprogram
can be anywhere within the package body. See step 2 in the following task on
instructions for declaring function
eval_frequency
and procedures
salary_
schedule
and
add_eval
.
To use a RECORD type:
1.
In the
emp_eval
Body pane, add the definition of the
salary_schedule
procedure immediately before the
END emp_eval
statement, as shown by the
following code. Note that this code is similar to the content of the
BEGIN...END
block in
eval_frequency
that executes if the salary raise is nonzero.
PROCEDURE salary_schedule(emp IN sal_info) AS
accumulating_sal NUMBER; -- accumulator
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary of ' || emp.salary ||
' increases by ' || ROUND((emp.sal_raise * 100),0) ||
'% each year, it would be ');
accumulating_sal := emp.salary; -- assign value of sal to accumulator
WHILE accumulating_sal <= emp.sal_max LOOP
accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
DBMS_OUTPUT.PUT (ROUND( accumulating_sal,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END salary_schedule;
2.
In the
emp_eval
Body pane, near the top of the
emp_eval
body definition, enter
declarations for
eval_frequency
and
salary_schedule
. New code is in bold
font.
create or replace
PACKAGE BODY emp_eval AS
/* local subprogram declarations */
FUNCTION eval_frequency (employee_id employees.employee_id%TYPE) RETURN NUMBER;
PROCEDURE salary_schedule(emp IN sal_info);
PROCEDURE add_eval(employee_id IN NUMBER, today IN DATE);
/* subprogram definition */
PROCEDURE eval_department (dept_id IN NUMBER) AS
...
3.
In the
emp_eval
Body pane, edit
eval_frequency
function so that it uses the
new
sal_info
type as variable
emp_sal
, populates its fields, and invokes
salary_schedule
. Note that the code that was previously executed if the salary
raise was nonzero is no longer part of this function; it has been incorporated into
the
salary_schedule
procedure. Note also that the declarations at the top of the
functions changed. New code is in bold font.
FUNCTION eval_frequency (employee_id employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
emp_sal SAL_INFO; -- record for fields associated
-- with salary review
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
