Tutorial: Creating and Invoking a Subprogram with a Record Parameter

If you declared the RECORD type sal_info in "Tutorial: Declaring a RECORD Type", this tutorial shows how to use the SQL Developer tool Edit to do the following:

Create a procedure, salary_schedule, which has a parameter of type sal_info.
Change the eval_frequency function so that it declares a record, emp_sal, of the type sal_info, populates its fields, and passes it to the salary_schedule procedure.

Because eval_frequency will invoke salary_schedule, the declaration of salary_schedule must precede the declaration of eval_frequency (otherwise, the package will not compile). However, the definition of salary_schedule can be anywhere in the package body.

To create salary_schedule and change eval_frequency:

On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

Under the hr_conn icon, a list of schema object types appears.
Expand Packages.

A list of packages appears.
Expand EMP_EVAL.

A list appears.
Right-click EMP_EVAL Body.

A list of choices appears.
Click Edit.

The EMP_EVAL Body pane appears, showing the code for the package body.

In the EMP_EVAL Body pane, immediately before END emp_eval, add this definition of the salary_schedule procedure:

PROCEDURE salary_schedule (emp IN sal_info) AS
  accumulating_sal  NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('If salary ' || emp.sal || 
    ' increases by ' || ROUND((emp.sal_raise * 100),0) || 
    '% each year, it will be:');

  accumulating_sal := emp.sal;

  WHILE accumulating_sal <= emp.sal_max LOOP
    accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
    DBMS_OUTPUT.PUT_LINE(ROUND(accumulating_sal,2) ||', ');
  END LOOP;
END salary_schedule;

A new EMP_EVAL Body pane opens, showing the changed CREATE PACKAGE BODY statement. The title of the pane is in italic font, which indicates that the changes have not been saved to the database.

In the EMP_EVAL Body pane, enter the code shown in bold font, in this position:

create or replace
PACKAGE BODY EMP_EVAL AS

FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER;
PROCEDURE salary_schedule(emp IN sal_info);
PROCEDURE add_eval(employee_id IN NUMBER, today IN DATE);
 
PROCEDURE eval_department (dept_id IN NUMBER) AS

Edit the eval_frequency function, making the changes shown in bold font:

FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
  emp_sal    SAL_INFO;  -- replaces sal, sal_raise, and sal_max
 
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;
 
  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
 
  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
 
     /* populate emp_sal */
 
     SELECT JOB_ID INTO emp_sal.j_id FROM EMPLOYEES
     WHERE EMPLOYEE_ID = emp_id;
 
     SELECT MIN_SALARY INTO emp_sal.sal_min FROM JOBS
     WHERE JOB_ID = emp_sal.j_id;
 
     SELECT MAX_SALARY INTO emp_sal.sal_max FROM JOBS
     WHERE JOB_ID = emp_sal.j_id;
 
     SELECT SALARY INTO emp_sal.sal FROM EMPLOYEES
     WHERE EMPLOYEE_ID = emp_id;
 
     emp_sal.sal_raise := 0;  -- default
 
     CASE emp_sal.j_id
       WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08;
       WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07;
       WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06;
       WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05;
       WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05;
       WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04;
       ELSE NULL;
     END CASE;
 
     IF (emp_sal.sal_raise != 0) THEN
       salary_schedule(emp_sal);
     END IF;
   ELSE
     eval_freq := 2;
   END IF;
 
   RETURN eval_freq;
 END eval_frequency;

Click Compile.

Tutorial: Creating and Invoking a Subprogram with a Record Parameter

Related Topics