Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_subprograms041.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

PROCEDURE eval_department (dept_id IN employees.department_id%TYPE);

PROCEDURE eval_everyone;

FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE 
                         , perf_id IN scores.performance_id%TYPE) 
                           RETURN NUMBER;
  TYPE SAL_INFO IS RECORD
      ( j_id jobs.job_id%type
      , sal_min jobs.min_salary%type
      , sal_max jobs.max_salary%type
      , salary employees.salary%type
      , sal_raise NUMBER(3,3));

TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
END emp_eval;
</pre>
(For instructions for changing a package specification, see <a href="tdddg_subprograms015.htm#CIHIEAGC">"Tutorial: Changing a Package Specification"</a>.)
</li>
<li>
In the <code>EMP_EVAL</code> package body, add a forward declaration for the procedure <code>eval_loop_control</code> and change the declaration of the procedure <code>add_eval</code>, as shown in bold font:
<pre xml:space="preserve" class="oac_no_warn">
create or replace
PACKAGE BODY EMP_EVAL AS

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
    RETURN PLS_INTEGER;

PROCEDURE salary_schedule(emp IN sal_info);

PROCEDURE add_eval(emp_record IN EMPLOYEES%ROWTYPE, today IN DATE);

PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
...
</pre>
(For a step-by-step example of changing a package body, see <a href="tdddg_subprograms019.htm#CIHBGDBA">"Tutorial: Declaring Variables and Constants in a Subprogram"</a>.)
</li>
<li><a id="CIHCCIHH" name="CIHCCIHH"></a>
Change the <code>eval_department</code> procedure to retrieve three separate result sets based on the department, and to call the <code>eval_loop_control</code> procedure, as shown in bold font:
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
 emp_cursor emp_refcursor_type;
 current_dept departments.department_id%TYPE;

BEGIN
 current_dept := dept_id;

FOR loop_c IN 1..3 LOOP
 OPEN emp_cursor FOR
 SELECT * 
 FROM employees
 WHERE current_dept = dept_id;

DBMS_OUTPUT.PUT_LINE
 ('Determining necessary evaluations in department #' ||
 current_dept);

eval_loop_control(emp_cursor);

DBMS_OUTPUT.PUT_LINE
      ('Processed ' || emp_cursor%ROWCOUNT || ' records.');

CLOSE emp_cursor;
 current_dept := current_dept + 10; 
 END LOOP;
END eval_department;
</pre></li>
<li>
Change the <code>add_eval</code> as shown in bold font:
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE)
AS
-- (Delete local variables)
BEGIN
 INSERT INTO EVALUATIONS (
 evaluation_id,
 employee_id,
 evaluation_date,
 job_id,
 manager_id,
 department_id,
 total_score
 )
 VALUES (
 evaluations_seq.NEXTVAL, -- evaluation_id
 emp_record.employee_id, -- employee_id
 today, -- evaluation_date
 emp_record.job_id, -- job_id
 emp_record.manager_id, -- manager_id
 emp_record.department_id, -- department_id
 0 -- total_score
);
END add_eval;
</pre></li>
<li>
Before <code>END</code> <code>EMP_EVAL</code>, add the following procedure, which fetches the individual records from the result set and processes them:
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
 emp_record EMPLOYEES%ROWTYPE;
 all_evals BOOLEAN;
 today DATE;
BEGIN
 today := SYSDATE;

IF (EXTRACT(MONTH FROM today) &lt; 6) THEN
    all_evals := FALSE;
  ELSE 
    all_evals := TRUE;
  END IF;

LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;

IF all_evals THEN
 add_eval(emp_record, today);
 ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
 add_eval(emp_record, today);
 END IF;
 END LOOP;
END eval_loop_control;
</pre></li>
<li>
Before <code>END</code> <code>EMP_EVAL</code>, add the following procedure, which retrieves a result set that contains all employees in the company:
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE eval_everyone AS
 emp_cursor emp_refcursor_type;
BEGIN
 OPEN emp_cursor FOR SELECT * FROM employees;
 DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
 eval_loop_control(emp_cursor);
 DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
 CLOSE emp_cursor;
END eval_everyone;
</pre></li>
<li>
Compile the <code>EMP_EVAL</code> package specification.
</li>
<li>
Compile the <code>EMP_EVAL</code> package body.
</li>
</ol>
<div class="helpinfonotealso">
<h2>Related Topics</h2>
<a href="tdddg_subprograms040.htm#BABEHCAJ">Using a Cursor Variable to Retrieve Result Set Rows One at a Time</a>
<a href="tdddg_subprograms032.htm#CIHHCJDI">Using Records and Cursors</a>
</div>
</div>



<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved. 
<a href="./dcommon/html/cpyr.htm">Legal Notices</a></td>
<td align="center"><a href="tdddg_subprograms040.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /> 
Previous</a>&nbsp;</td>
<td align="center"><a href="tdddg_subprograms042.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /> 
Next</a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de