Beginning XML Databases_AppB_XML.zip

CREATE OR REPLACE FUNCTION GETTABS(pTAB IN INTEGER) RETURN VARCHAR2 IS pTABS CHAR(3) := '***'; --spaces are trimmed out automatically n INTEGER; rSTR VARCHAR2(4000) := ''; BEGIN FOR n IN 1..pTAB LOOP rSTR := rSTR||pTABS; END LOOP; RETURN rSTR; END; / CREATE OR REPLACE PROCEDURE GETAREA(pAREA IN INTEGER, pTAB IN INTEGER) AS BEGIN IF pAREA > 0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<area>'||pAREA||'</area>'); END IF; END; / CREATE OR REPLACE PROCEDURE GETCITIESBYSTATE(pSTATE_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cCity IS SELECT * FROM CITY WHERE STATE_ID IS NOT NULL AND STATE_ID = pSTATE_ID; BEGIN FOR rCity IN cCity LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<city id="'||rCity.CITY_ID||'">'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<name>'||rCity.CITY||'</name>'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<population>'||rCity.POPULATION||'</population>'); GETAREA(rCity.AREA, pTAB+1); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</city>'); END LOOP; END; / CREATE OR REPLACE PROCEDURE GETCITIESBYCOUNTRY(pCOUNTRY_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cCity IS SELECT * FROM CITY WHERE STATE_ID IS NULL AND COUNTRY_ID = pCOUNTRY_ID; BEGIN FOR rCity IN cCity LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<city id="'||rCity.CITY_ID||'">'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<name>'||rCity.CITY||'</name>'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<population>'||rCity.POPULATION||'</population>'); GETAREA(rCity.AREA, pTAB+1); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</city>'); END LOOP; END; / CREATE OR REPLACE PROCEDURE GETSTATES(pCOUNTRY_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cState IS SELECT * FROM STATE WHERE COUNTRY_ID = pCOUNTRY_ID; BEGIN FOR rState IN cState LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<state id="'||rState.STATE_ID||'">'); DBMS_OUTPUT.PUT(GETTABS(pTAB+1)||'<name code="'||rState.CODE||'"'); IF rState.NICKNAME IS NOT NULL THEN DBMS_OUTPUT.PUT(' nickname="'||rState.NICKNAME||'"'); END IF; DBMS_OUTPUT.PUT_LINE('>'||rState.STATE||'</name>'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<population>'||rState.POPULATION||'</population>'); GETAREA(rState.AREA, pTAB+1); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</state>'); GETCITIESBYSTATE(rState.STATE_ID, PTAB+1); END LOOP; END; / CREATE OR REPLACE PROCEDURE GETPOPULATIONBYLANGUAGE(pPOPULATION_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cLanguage IS SELECT pl.language_id as LANGUAGE_ID, pl.male AS MALE, pl.female as FEMALE, l.language as LANGUAGE FROM POPULATIONBYLANGUAGE pl, LANGUAGE l WHERE pl.POPULATION_ID=pPOPULATION_ID AND l.LANGUAGE_ID=pl.LANGUAGE_ID ORDER BY l.LANGUAGE_ID; year INTEGER := 0; lROWS INTEGER := 0; BEGIN SELECT COUNT(*) INTO lROWS FROM POPULATIONBYLANGUAGE pl, LANGUAGE l WHERE pl.POPULATION_ID=pPOPULATION_ID AND l.LANGUAGE_ID=pl.LANGUAGE_ID ORDER BY l.LANGUAGE_ID; IF lROWS > 0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<languages>'); FOR rLanguage IN cLanguage LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<language name="'||rLanguage.LANGUAGE||'" language_id="'||rLanguage.LANGUAGE_ID||'">'); IF rLanguage.MALE>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<male>'||rLanguage.MALE||'</male>'); END IF; IF rLanguage.FEMALE>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<female>'||rLanguage.FEMALE||'</female>'); END IF; DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'</language>'); END LOOP; DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</languages>'); END IF; END; / CREATE OR REPLACE PROCEDURE GETPOPULATIONBYOCCUPATION(pPOPULATION_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cOccupation IS SELECT pl.occupation_id as OCCUPATION_ID, pl.male AS MALE, pl.female as FEMALE, l.occupation as OCCUPATION FROM POPULATIONBYOCCUPATION pl, OCCUPATION l WHERE pl.POPULATION_ID=pPOPULATION_ID AND l.OCCUPATION_ID=pl.OCCUPATION_ID ORDER BY l.OCCUPATION_ID; year INTEGER := 0; lROWS INTEGER := 0; BEGIN SELECT COUNT(*) INTO lROWS FROM POPULATIONBYOCCUPATION pl, OCCUPATION l WHERE pl.POPULATION_ID=pPOPULATION_ID AND l.OCCUPATION_ID=pl.OCCUPATION_ID ORDER BY l.OCCUPATION_ID; IF lROWS > 0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<occupations>'); FOR rOccupation IN cOccupation LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<occupation name="'||rOccupation.OCCUPATION||'" occupation_id="'||rOccupation.OCCUPATION_ID||'">'); IF rOccupation.MALE>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<male>'||rOccupation.MALE||'</male>'); END IF; IF rOccupation.FEMALE>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<female>'||rOccupation.FEMALE||'</female>'); END IF; DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'</occupation>'); END LOOP; DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</occupations>'); END IF; END; / CREATE OR REPLACE PROCEDURE GETPOPULATIONBYCOUNTRY(pCOUNTRY_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cPopulation IS SELECT * FROM POPULATION WHERE COUNTRY_ID=pCOUNTRY_ID ORDER BY COUNTRY_ID, YEAR; year INTEGER := 0; BEGIN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<population>'); --population_id excluded in year because heare it is relational FOR rPopulation IN cPopulation LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<year year="'||rPopulation.YEAR||'" population_id="'||rPopulation.POPULATION_ID||'" population="'||rPopulation.POPULATION||'">'); IF rPopulation.BIRTHS_PER_1000>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<births_per_1000>'||rPopulation.BIRTHS_PER_1000||'</births_per_1000>'); END IF; IF rPopulation.DEATHS_PER_1000>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<deaths_per_1000>'||rPopulation.DEATHS_PER_1000||'</deaths_per_1000>'); END IF; IF rPopulation.MIGRANTS_PER_1000>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<migrants_per_1000>'||rPopulation.MIGRANTS_PER_1000||'</migrants_per_1000>'); END IF; IF rPopulation.NATURAL_INCREASE_PERCENT>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<natural_increase_percent>'||rPopulation.NATURAL_INCREASE_PERCENT||'</natural_increase_percent>'); END IF; IF rPopulation.GROWTH_RATE>0 THEN DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+2)||'<growth_rate>'||rPopulation.GROWTH_RATE||'</growth_rate>'); END IF; GETPOPULATIONBYLANGUAGE(rPopulation.POPULATION_ID, PTAB+2); GETPOPULATIONBYOCCUPATION(rPopulation.POPULATION_ID, PTAB+2); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'</year>'); END LOOP; DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'</population>'); END; / CREATE OR REPLACE PROCEDURE GETCOUNTRIES(pREGION_ID IN INTEGER, pTAB IN INTEGER) AS CURSOR cCountry IS SELECT * FROM COUNTRY WHERE REGION_ID = pREGION_ID; states INTEGER := 0; population INTEGER := 0; BEGIN FOR rCountry IN cCountry LOOP DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB)||'<country id="'||rCountry.COUNTRY_ID||'" code="'||rCountry.CODE||'">'); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<name>'||rCountry.COUNTRY||'</name>'); SELECT COUNT(*) INTO population FROM POPULATION WHERE COUNTRY_ID=rCountry.COUNTRY_ID; IF population > 0 THEN GETPOPULATIONBYCOUNTRY(rCountry.COUNTRY_ID, pTAB+1); ELSE DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<population>'||rCountry.POPULATION||'</population>'); END IF; GETAREA(rCountry.AREA, pTAB+1); DBMS_OUTPUT.PUT_LINE(GETTABS(pTAB+1)||'<currency fxcode="'||rCountry.FXCODE||'" rate="'||rCountry.RATE||'">'||rCountry.CURRENCY||'</currency>'); SELECT COUNT(*) INTO states FROM STATE WHERE COUNTRY_ID=rCOUNTRY.COUNTRY_ID; IF states > 0 THEN GETSTATES(rC