1
edit
Changes
YSL
,→2.
<pre>
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT region PROMPT 'Enter value for region: '
DECLARE
v_region_id REGIONS.region_id%TYPE;
country COUNTRIES.country_name%TYPE;
BEGIN
SELECT region_id INTO v_region_id
FROM REGIONS
WHERE region_id = ®ion;
BEGIN <<inner>>
SELECT country_name INTO country
FROM COUNTRIES
WHERE region_id = ®ion
AND country_id NOT IN (
SELECT DISTINCT country_id
FROM LOCATIONS
);
IF SQL%ROWCOUNT = 1 THEN
DBMS_OUTPUT.PUT_LINE('In the region ' || ®ion || ' there is ONE country ' || country || ' with NO city');
UPDATE COUNTRIES
SET flag = 'Empty_' || region_id
WHERE country_id NOT IN (
SELECT DISTINCT country_id
FROM LOCATIONS
);
DBMS_OUTPUT.PUT_LINE('Number of countries with NO cities listed is: ' || SQL%ROWCOUNT);
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('This region ID has MORE THAN ONE country without cities listed: ' || ®ion);
END inner;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || ®ion);
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>
===3.===