1
edit
Changes
YSL
,→3.
<pre>
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT region PROMPT 'Enter value for region: '
DECLARE
CURSOR c_region_cursor IS
SELECT region_id
FROM REGIONS
WHERE region_id = ®ion;
TYPE empty_cntry_tab_type IS TABLE OF
COUNTRIES%ROWTYPE
INDEX BY PLS_INTEGER;
CURSOR c_empty_cntry_cursor IS
SELECT region_id, country_name
FROM COUNTRIES
WHERE country_id NOT IN (
SELECT DISTINCT country_id
FROM LOCATIONS
)
ORDER BY country_name;
v_region_id REGIONS.region_id%TYPE;
v_index NUMBER := 1;
v_counter NUMBER := 0;
empty_cntry_tab empty_cntry_tab_type;
BEGIN
FOR region IN c_region_cursor LOOP
v_counter := v_counter + 1;
END LOOP;
IF v_counter = 0 THEN
DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || ®ion);
ELSE
v_counter := 1;
UPDATE COUNTRIES
SET flag = 'Empty_' || region_id
WHERE country_id NOT IN (
SELECT DISTINCT country_id
FROM LOCATIONS
);
FOR cntry IN c_empty_cntry_cursor LOOP
empty_cntry_tab(v_index).region_id := cntry.region_id;
empty_cntry_tab(v_index).country_name := cntry.country_name;
DBMS_OUTPUT.PUT_LINE('Index Table Key: ' || v_index || ' has a value of ' || empty_cntry_tab(v_index).country_name);
v_index := v_index + 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE('========================================================================');
DBMS_OUTPUT.PUT_LINE('Total number of elements in the Index Table or Number of countries with NO cities listed is: ' ||
empty_cntry_tab.COUNT);
DBMS_OUTPUT.PUT_LINE('Second element (Country) in the Index Table is: ' ||
empty_cntry_tab(empty_cntry_tab.NEXT(empty_cntry_tab.FIRST)).country_name);
DBMS_OUTPUT.PUT_LINE('Before the last element (Country) in the Index Table is: ' ||
empty_cntry_tab(empty_cntry_tab.PRIOR(empty_cntry_tab.LAST)).country_name);
DBMS_OUTPUT.PUT_LINE('========================================================================');
FOR cntry IN c_empty_cntry_cursor LOOP
IF cntry.region_id = ®ion THEN
DBMS_OUTPUT.PUT_LINE('In the region : ' || ®ion || ' there is country ' || cntry.country_name || ' with NO city.');
v_counter := v_counter + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('========================================================================');
DBMS_OUTPUT.PUT_LINE('Total Number of countries with NO cities listed in the Region ' || ®ion || ' is: ' || v_counter);
END IF;
END;
/
SELECT *
FROM COUNTRIES
WHERE country_id NOT IN (
SELECT DISTINCT country_id
FROM LOCATIONS
)
AND flag IS NOT NULL
ORDER BY region_id, country_name;
ROLLBACK;
</pre>
<pre>
</pre>
===4.===