Database Programming with PL/SQL
5-6: Using Multiple Cursors
Practice Activities
Try It / Solve It
1. Write and run a PL/SQL block which produces a listing of departments and their employees. Use
the DEPARTMENTS and EMPLOYEES tables. In a cursor FOR loop, retrieve and display the
department_id and department_name for each department, and display a second line containing '-
---------' as a separator. In a nested cursor FOR loop, retrieve and display the first_name,
last_name, and salary of each employee in that department, followed by a blank line at the end of
each department. Order the departments by department_id, and the employees in each
department by last_name.
You will need to declare two cursors, one to fetch and display the departments, the second to
fetch and display the employees in that department, passing the department_id as a parameter.
Your output should look something like this (only the first few departments are shown):
10 Administration
-----------------------------
Jennifer Whalen 4400
20 Marketing
-----------------------------
Pat Fay 6000
Michael Hartstein 13000
50 Shipping
-----------------------------
Curtis Davies 3400
Randall Matos 2600
Kevin Mourgos 5800
Trenna Rajs 3500
Peter Vargas 2500
DECLARE
CURSOR dept_cursor IS
SELECT department_id, department_name
FROM departments
ORDER BY department_id;
CURSOR emp_cursor(v_deptno departments.department_id%TYPE) IS
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = v_deptno
ORDER BY last_name;
dept_record dept_cursor%ROWTYPE;
emp_record emp_cursor%ROWTYPE;
BEGIN
FOR dept_record IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE(dept_record.department_id || ' ' || dept_record.department_name);
DBMS_OUTPUT.PUT_LINE('--------------------------------');
FOR emp_record IN emp_cursor(dept_record.department_id) LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.first_name || ' ' || emp_record.last_name || ' ' || emp_record.salary);
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END LOOP;
END;
2. Write and run a PL/SQL block which produces a report listing world regions, countries in those
regions, and the land area and population for each country.
You will need two cursors: an outer loop cursor which fetches and displays rows from the
REGIONS table, and an inner loop cursor which fetches and displays rows from the COUNTRIES
table for countries in that region, passing the region_id as a parameter.
Restrict your regions to those in the Americas (region_name like ‘%America%’). Order your output
by region_name, and by country_name within each region.
Your output should look something like this (only the first two regions are shown):
13 Central America
-----------------------------
Belize 22966 287730
Republic of Costa Rica 51100 4075261
Republic of El Salvador 21040 6822378
Republic of Guatemala 108890 12293545
Republic of Honduras 112090 7326496
Republic of Nicaragua 129494 5570129
Republic of Panama 78200 3191319
United Mexican States 1972550 107449525
21 North America
-----------------------------
Bermuda 53 65773
Canada 9984670 33098932
Greenland 2166086 56361
Territorial Collectivity of Saint Pierre and Miquelon 242 7026
United States of America 9631420 298444215
DECLARE
CURSOR region_curs IS
SELECT region_id, region_name
FROM wf_world_regions
WHERE region_name LIKE '%America%'
ORDER BY region_name;
CURSOR country_curs(p_region_id wf_world_regions.region_id%TYPE) IS
SELECT country_name, area, population
FROM wf_countries
WHERE region_id = p_region_id
ORDER BY country_name;
region_rec region_curs%ROWTYPE;
country_rec country_curs%ROWTYPE;
BEGIN
FOR region_rec IN region_curs LOOP
DBMS_OUTPUT.PUT_LINE(region_rec.region_id || ' ' || region_rec.region_name);
DBMS_OUTPUT.PUT_LINE('--------------------');
FOR country_rec IN country_curs(region_rec.region_id) LOOP
DBMS_OUTPUT.PUT_LINE(country_rec.country_name || ' ' || country_rec.area || ' ' ||country_rec.population);
END LOOP;
END LOOP;
END;
3. Modify your block from question 2 to display the names of official spoken languages in each
country. You will need three cursors and three loops. The first two cursors should fetch and display
regions and countries, as in question 2. The innermost loop should accept a country_id as a
parameter, and fetch and display the name of each official language, using a join of the
SPOKEN_LANGUAGES table and the LANGUAGES table.
Within each country, the languages should be ordered by language_name. Test your block,
restricting regions to those in the Americas.
Your output should look something like this (only the first two regions are shown):
13 Central America
-----------------------------
Belize 22966 287730
--- English
Republic of Costa Rica 51100 4075261
--- Spanish
Republic of El Salvador 21040 6822378
Republic of Guatemala 108890 12293545
Republic of Honduras 112090 7326496
Republic of Nicaragua 129494 5570129
--- Spanish
Republic of Panama 78200 3191319
--- Spanish
United Mexican States 1972550 107449525
21 North America
-----------------------------
Bermuda 53 65773
--- English
Canada 9984670 33098932
--- English
--- French
Greenland 2166086 56361
Territorial Collectivity of Saint Pierre and Miquelon 242 7026
--- French
United States of America 9631420 298444215
--- English
DECLARE
CURSOR region_curs IS
SELECT region_id, region_name
FROM wf_world_regions
WHERE region_name LIKE '%America%'
ORDER BY region_name;
CURSOR country_curs(p_region_id wf_world_regions.region_id%TYPE) IS
SELECT country_name, area, population, country_id
FROM wf_countries
WHERE region_id = p_region_id
ORDER BY country_name;
CURSOR language_curs(p_country_id wf_countries.country_id%TYPE) IS
SELECT language_name
FROM wf_spoken_languages sl, wf_languages l
WHERE sl.language_id = l.language_id
AND official = 'Y'
AND country_id = p_country_id
ORDER BY language_name;
region_rec region_curs%ROWTYPE;
country_rec country_curs%ROWTYPE;
language_rec language_curs%ROWTYPE;
BEGIN
FOR region_rec IN region_curs LOOP
DBMS_OUTPUT.PUT_LINE(region_rec.region_id || ' ' || region_rec.region_name);
DBMS_OUTPUT.PUT_LINE('--------------------');
FOR country_rec IN country_curs(region_rec.region_id) LOOP
DBMS_OUTPUT.PUT_LINE(country_rec.country_name || ' ' || country_rec.area || ' ' ||country_rec.population);
FOR language_rec IN language_curs(country_rec.country_id) LOOP
DBMS_OUTPUT.PUT_LINE('---' || language_rec.language_name);
END LOOP;
END LOOP;
END LOOP;
END;
0 Comments