background image
<< Using a RECORD Datatype | Using Explicit Cursors >>
<< Using a RECORD Datatype | Using Explicit Cursors >>

Retrieving Data from a Set

Retrieving Data from a Set Using Cursors and Cursor Variables
4-32 Oracle Database 2 Day Developer's Guide
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* populate emp_sal */
SELECT e.job_id INTO emp_sal.job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.min_salary INTO emp_sal.sal_min FROM jobs j
WHERE emp_sal.job_id = j.job_id;
SELECT j.max_salary INTO emp_sal.sal_max FROM jobs j
WHERE emp_sal.job_id = j.job_id;
SELECT e.salary INTO emp_sal.salary FROM employees e
WHERE employee_id = e.employee_id;
emp_sal.sal_raise := 0; -- default
CASE
emp_sal.
job_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 a salary raise is not zero, print the salary schedule */
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;
4.
Compile and save
emp_eval
Body.
The following message appears in the Messages - Log pane:
EMP_EVAL Body Compiled
Retrieving Data from a Set Using Cursors and Cursor Variables
A cursor is a type of pointer that is built into PL/SQL for querying the database,
retrieving a set of records (a result set), and enabling the developer to access these
records one row at a time. A cursor is a handle or a name for a private in-memory SQL
area that holds a parsed statement and related information. Oracle Database implicitly
manages cursors. However, there are a few interfaces that enable you to use cursors
explicitly, as a named resource within a program to more effectively parse embedded
SQL statements. The two main types of cursors are therefore defined as:
See Also:
Oracle Database PL/SQL Language Reference for information on
collections and records