Database Programming with PL/SQL
5-4: Cursors with Parameters
Practice Activities
Try It / Solve It
1. Describe the benefit of using one or more parameters with a cursor.
Instead of using more cursors with different values, we can use the same cursor by creating a parameter that we can change when we open the cursor.
2. Write a PL/SQL block to display the country name and the area of each country in a chosen
region. The region_id should be passed to the cursor as a parameter. Test your block using two
region_ids: 5 (South America) and 30 (Eastern Asia). Do not use a cursor FOR loop.
DECLARE
CURSOR countries_cursor (p_region_id NUMBER) IS
SELECT country_name, area
FROM wf_countries
WHERE region_id = p_region_id;
v_countries countries_cursor%ROWTYPE;
BEGIN
OPEN countries_cursor (5);
LOOP
FETCH countries_cursor INTO v_countries;
EXIT WHEN countries_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
CLOSE countries_cursor;
OPEN countries_cursor (30);
LOOP
FETCH countries_cursor INTO v_countries;
EXIT WHEN countries_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
CLOSE countries_cursor;
END;
3. Modify your answer to question 2 to use a cursor FOR loop. You must still declare the cursor
explicitly in the DECLARE section. Test it again using regions 5 and 30.
DECLARE
CURSOR countries_cursor (p_region_id NUMBER) IS
SELECT country_name, area
FROM wf_countries
WHERE region_id = p_region_id;
v_countries countries_cursor%ROWTYPE;
BEGIN
FOR v_countries IN countries_cursor (5) LOOP
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
FOR v_countries IN countries_cursor (30) LOOP
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
END;
4. Modify your answer to question 3 to display the country_name and area of each country in a
chosen region that has an area greater than a specific value. The region_id and specific area
should be passed to the cursor as two parameters. Test your block twice using region_id 5 (South
America): the first time with area = 200000 and the second time with area = 1000000.
DECLARE
CURSOR countries_cursor (p_region_id NUMBER, p_area NUMBER) IS
SELECT country_name, area
FROM wf_countries
WHERE region_id = p_region_id AND area > p_area;
v_countries countries_cursor%ROWTYPE;
BEGIN
FOR v_countries IN countries_cursor (5, 200000) LOOP
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
FOR v_countries IN countries_cursor (5, 10000000) LOOP
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.area);
END LOOP;
END;
5. Modify your answer to question 4 to fetch and display two sets of countries in a single execution of
the block. You should open and close the cursor twice, passing different parameter values to it
each time. Before each set of output rows, display the message “Region: <region_id> Minimum
Area: <area>”., for example “Region: 5 Minimum Area: 200000”. Test your changes using (5,
200000) and (30, 500000).
DECLARE
CURSOR cur_countries (p_region_id NUMBER, p_area NUMBER) IS
SELECT country_name, area
FROM wf_countries
WHERE region_id=p_region_id AND area>p_area;
v_country_record cur_countries%ROWTYPE;
BEGIN
OPEN cur_countries(5,200000);
DBMS_OUTPUT.PUT_LINE('Region: ' || 5 || ' Minimum Area: ' || 200000);
LOOP
FETCH cur_countries INTO v_country_record;
EXIT WHEN cur_countries%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name || ' with area ' || v_country_record.area);
END LOOP;
CLOSE cur_countries;
DBMS_OUTPUT.PUT_LINE('**********');
OPEN cur_countries(30,500000);
DBMS_OUTPUT.PUT_LINE('Region: ' || 30 || ' Minimum Area: ' || 500000);
LOOP
FETCH cur_countries INTO v_country_record;
EXIT WHEN cur_countries%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name || ' with area ' || v_country_record.area);
END LOOP;
CLOSE cur_countries;
END;
0 Comments