1
edit
Changes
YSL
,→1.
''Seung Yeon''
<pre>
If anyone know how to put these two linesROLLBACK;select * from countries where flag IS NOT NULL;after END; in pl/sql block please let me know how....I finally figured out how to put set serveroutput on before DECLAREbut if i put those two lines after END; program doesnt work...If you have same problem remove last two line and try everything else should work fine.This is Question 2</pre><pre>set serveroutput onset verify offset pagesize 200accept id prompt 'Enter value for region: '; DECLARE v_region REGIONS.region_id%TYPE := &id; v_region_name regions.region_name%TYPE; v_country_id countries.country_id%TYPE; v_country_name countries.country_name%TYPE; v_region_id countries.region_id%TYPE; v_country_count NUMBER:=0;BEGIN SELECT region_name INTO v_region_name FROM regions WHERE region_id = v_region; SELECT department_idt1.country_id, t1.country_name, t1.region_id INTO v_country_id, department_namev_country_name, SUBSTRv_region_id FROM (NVLSELECT country_id, country_name, region_id FROM countries WHERE country_id NOT IN (SELECT country_id FROM locations GROUP BY country_id)) t1 WHERE t1.region_id = v_region; SELECT COUNT(country_id) INTO v_country_count FROM countries WHERE country_id NOT IN (SELECT country_id FROM locations GROUP BY country_id); DBMS_OUTPUT.PUT_LINE ('In the region ' || v_region || ' there is ONE country ' || v_country_name || ' with NO city,.'); DBMS_OUTPUT.PUT_LINE ('Not Assigned YetNumber of countries with NO cities listed is: '|| v_country_count); UPDATE countries SET flag = concat('Empty_',1,25to_char(region_id)) City, COUNT WHERE country_id NOT IN (DISTINCT job_id) "# of Jobs"SELECT country_id FROM locations l RIGHT OUTER JOIN departments dUSING GROUP BY country_id);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (location_id'This region ID does NOT exist: ' || v_region);LEFT OUTER JOIN employees e WHEN TOO_MANY_ROWS THENUSING DBMS_OUTPUT.PUT_LINE(department_id'This region ID has MORE THAN ONE country without cities listed: ' || v_region); END;ROLLBACK;GROUP BY department_id, department_name, cityselect * from countries where flag IS NOT NULL;
/
</pre>
