Using Composite Data Structures
Using Composite Data Structures; Records
Developing and Using Stored Procedures 4-29
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.salary INTO salary FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.max_salary INTO sal_max FROM jobs j
WHERE job_id = j.job_id;
CASE job_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL;
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (sal_raise != 0) THEN -- start code for salary schedule printout
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year, it would be ');
LOOP
salary := salary * (1 + sal_raise);
EXIT WHEN salary > sal_max;
DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Using Composite Data Structures; Records
A composite data structure, or a record, is a group of related data items stored in
fields, each with its own name and data type. You can think of a record as a variable
that can hold a table row, or some columns from a table row. The fields correspond to
See Also:
Oracle Database PL/SQL Language Reference for more information
on
LOOP...EXIT WHEN
statement
