PACKAGE personnel_cur_pkg 
IS
	-- Full access to employee table.
	CURSOR emp_full_cur RETURN employee%ROWTYPE;

	-- Full access to department table.
	CURSOR dept_full_cur RETURN department%ROWTYPE;

	/*
	|| Lots of variations of name formats. Useful in forms and reports.
	|| First create a record (we have several derived values and so will
	|| not be able to use a %ROWTYPE against a table).
	*/
	TYPE emp_names_rec IS RECORD 
		(first_name employee.first_name%TYPE,
		 last_name employee.last_name%TYPE,
		 last_first_name	VARCHAR2 (100),
		 first_last_name	VARCHAR2 (100),
		 full_name			VARCHAR2 (100),
		 initcap_name		VARCHAR2 (100)
		);
	CURSOR emp_names_cur (id_in IN NUMBER) RETURN emp_names_rec;

	/*
	|| Record and cursor to return the employee/department key/desc 
	|| combo. This is an example of a pre-joined cursor for use by
	|| developers who should not have to worry about how to get the data.
	*/
	TYPE emp_dept_rec IS RECORD 
		(employee_id employee.employee_id%TYPE,
		 employee_name VARCHAR2 (100),
		 department_id department.department_id%TYPE,
		 department_name department.name%TYPE
		);
	CURSOR emp_dept_cur RETURN emp_dept_rec;

	/*
	|| Record and cursor to generate analysis data for the employee and
	|| department tables. Again, use a record to include derived data.
	*/
	TYPE sal_analysis_rec IS RECORD
		(department_id department.department_id%TYPE,
		 department_name department.name%TYPE,
		 max_salary NUMBER,
		 min_salary NUMBER,
		 avg_salary NUMBER
		);
	CURSOR sal_analysis_cur (dept_in IN NUMBER) RETURN sal_analysis_rec;

END personnel_cur_pkg;
 
PACKAGE personnel_cur_pkg IS
	/*
	|| Retrieve all data from (potentially) all employee records. Notice 
	|| that both the return list and the SELECT statement are dynamic.
	|| As columns are added to the employee table they become available
	|| through this cursor without any code changes. The same is true for
	|| the dept_full_cur cursor.
	*/
	CURSOR emp_full_cur RETURN employee%ROWTYPE
	IS
		SELECT * FROM employee;

	CURSOR dept_full_cur RETURN department%ROWTYPE;
	IS
		SELECT * FROM department;

	/*
	|| Construct variations of employee name. Suppose I have a 
	|| Jenny Humboldt, who is also a PhD. Then the cursor returns
	|| the following name formats:
	||		first_name			JENNY
	||		last_name			HUMBOLDT
	||		last_first_name	HUMBOLDT, JENNY
	||		first_last_name	JENNY HUMBOLDT
	||		full_name			MS. JENNY HUMBOLDT, PhD
	||		initcap_name		Jenny Humboldt
	*/
	CURSOR emp_names_cur (id_in IN NUMBER) RETURN emp_names_rec
	IS
		SELECT 	UPPER (first_name) 
			first_name, 
					UPPER (last_name) 
			last_name, 
			 		UPPER (last_name)  ||', ' || UPPER (first_name) 
			last_first_name,
			 	 	UPPER (first_name) || ' ' || UPPER (last_name) 
			first_last_name,
				 	DECODE (gender, 'M', 'Mr. ', 'Ms. ') ||
					UPPER (first_name) || ' ' || UPPER (last_name) ||
					DECODE (title, NULL, NULL, ', ' || title) 
			full_name,
				 INITCAP (UPPER (first_name) || ' ' || UPPER (last_name)) 
			initcap_name
		  FROM employee
		 WHERE employee_id = id_in;

	-- Join of employee and department tables, hidden behind cursor.
	CURSOR emp_dept_cur RETURN emp_dept_rec
	IS
		SELECT employee_id, 
				 first_name || ' ' || last_name,
				 D.department_id, 
				 department.name
		  FROM employee E, department D
		 WHERE E.department_id = D.department_id;

	-- Apply group functions to produce analytical values for cursor.
	CURSOR sal_analysis_cur (dept_in IN NUMBER) RETURN sal_analysis_rec
	IS
		SELECT E.department_id, D.name,
				 MAX (salary), MIN (salary), AVG (salary)
		  FROM employee E, department D
		 WHERE E.department_id = dept_in
			AND D.department_id = dept_in
		 GROUP BY E.department_id, D.name;

END personnel_cur_pkg;
/* 
To use one of these cursors, simply qualify the name of the cursor with the package name, 
as follows:

OPEN personnel_cur_pkg.sal_analysis_cur (:dept.dept_id);
FETCH personnel_cur_pkg.sal_analysis_cur 
	INTO sal_rec;
IF sal_rec.avg_salary < company_wide_avg_sal
THEN
	give_bonuses (:dept.dept_id);
END IF;
CLOSE personnel_cur_pkg.sal_analysis_cur;

As you can see, you will get the most leverage and flexibility out of such a package and 
package cursors if you can make use of the programmer-defined record structures.  
*/