Created
August 15, 2021 23:18
-
-
Save Uriegas/de892d96ba2660c01dadaec831ef17d7 to your computer and use it in GitHub Desktop.
Final PL/SQL Semester 2 Project: Traveler Assistance
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Required tables: | |
SELECT * FROM COUNTRIES; | |
SELECT * FROM REGIONS; | |
SELECT * FROM CURRENCIES; | |
SELECT * FROM SPOKEN_LANGUAGES; | |
SELECT * FROM LANGUAGES; | |
--------------PACKAGE 1: TRAVELER ADMIN PACKAGE-------------- | |
CREATE OR REPLACE PACKAGE traveler_assistance_package AS | |
TYPE country_type IS RECORD( | |
country_name COUNTRIES.country_name%TYPE, | |
region REGIONS.region_name%TYPE, | |
currency CURRENCIES.currency_name%TYPE | |
); | |
TYPE countries_type IS TABLE OF country_type INDEX BY PLS_INTEGER; | |
TYPE country_language_type IS RECORD( | |
country_name COUNTRIES.country_name%TYPE, | |
language_name LANGUAGES.language_name%TYPE, | |
official_language SPOKEN_LANGUAGES.official%TYPE | |
); | |
TYPE country_languages_type IS TABLE OF country_language_type INDEX BY PLS_INTEGER; | |
--P1 | |
PROCEDURE country_demographics(v_country_name VARCHAR2); | |
--P2 | |
PROCEDURE find_region_and_currency(v_country_name IN VARCHAR2, country OUT country_type); | |
-- --P3 | |
PROCEDURE countries_in_same_region(v_region_name IN VARCHAR2, countries OUT countries_type); | |
-- --P4 | |
PROCEDURE print_region_array(countries countries_type); | |
-- --P5 | |
PROCEDURE country_languages(v_country_name IN VARCHAR2, country_lang OUT country_languages_type ); | |
-- --P6 | |
PROCEDURE print_language_array(country_langs country_languages_type); | |
END; | |
CREATE OR REPLACE PACKAGE BODY traveler_assistance_package AS | |
--P1 | |
PROCEDURE country_demographics(v_country_name VARCHAR2) IS | |
BEGIN | |
FOR country IN (SELECT * FROM COUNTRIES WHERE LOWER(COUNTRY_NAME) = LOWER(v_country_name)) LOOP | |
DBMS_OUTPUT.PUT_LINE(country.COUNTRY_NAME || ', ' || country.LOCATION || ', ' || country.POPULATION | |
|| ', ' || country.AIRPORTS || ', ' || country.CLIMATE); | |
END LOOP; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name); | |
END; | |
--P2 | |
PROCEDURE find_region_and_currency(v_country_name IN VARCHAR2, country OUT country_type) IS | |
BEGIN | |
SELECT c.country_name, r.region_name, cu.currency_name INTO country | |
FROM COUNTRIES c, REGIONS r, CURRENCIES cu | |
WHERE LOWER(c.COUNTRY_NAME) = LOWER(v_country_name) AND | |
c.REGION_ID = r.REGION_ID AND | |
c.CURRENCY_CODE = cu.CURRENCY_CODE; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name); | |
END; | |
--P3 | |
PROCEDURE countries_in_same_region(v_region_name IN VARCHAR2, countries OUT countries_type) IS | |
CURSOR countries_region IS SELECT c.country_name, r.region_name, cu.currency_name | |
FROM countries c, regions r, currencies cu | |
WHERE c.region_id = r.region_id | |
AND c.currency_code = cu.currency_code | |
AND LOWER(r.region_name) = LOWER(v_region_name); | |
i PLS_INTEGER := 1; | |
BEGIN | |
FOR country IN countries_region LOOP | |
countries(i) := country; | |
i := i + 1; | |
END LOOP; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_region_name); | |
END; | |
--P4 | |
PROCEDURE print_region_array(countries countries_type) IS | |
BEGIN | |
FOR i IN countries.FIRST .. countries.LAST LOOP | |
DBMS_OUTPUT.PUT_LINE(countries(i).country_name || ', ' || countries(i).region || ', ' || countries(i).currency); | |
END LOOP; | |
END; | |
--P5 | |
PROCEDURE country_languages(v_country_name IN VARCHAR2, country_lang OUT country_languages_type ) IS | |
CURSOR country_languages_cursor IS SELECT c.country_name, l.language_name, sl.official | |
FROM COUNTRIES c,LANGUAGES l, SPOKEN_LANGUAGES sl | |
WHERE LOWER(c.country_name) = LOWER(v_country_name) | |
AND c.country_id = sl.country_id | |
AND sl.language_id = l.language_id; | |
i PLS_INTEGER := 1; | |
BEGIN | |
FOR country_language IN country_languages_cursor LOOP | |
country_lang(i) := country_language; | |
i := i + 1; | |
END LOOP; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name); | |
END; | |
--P6 | |
PROCEDURE print_language_array(country_langs country_languages_type) IS | |
BEGIN | |
FOR i IN country_langs.FIRST .. country_langs.LAST LOOP | |
DBMS_OUTPUT.PUT_LINE(country_langs(i).country_name || ', ' || country_langs(i).language_name || ', ' || country_langs(i).official_language); | |
END LOOP; | |
END; | |
END; | |
----------TESTS---------- | |
------P1------ | |
--- Select of belize | |
SELECT * FROM countries WHERE country_name = 'Belize'; | |
--- Procedure of belize | |
BEGIN | |
TRAVELER_ASSISTANCE_PACKAGE.COUNTRY_DEMOGRAPHICS('Belize'); | |
END; | |
------P2------ | |
DECLARE | |
country_name VARCHAR2(50) := 'Belize'; | |
country TRAVELER_ASSISTANCE_PACKAGE.country_type; | |
BEGIN | |
TRAVELER_ASSISTANCE_PACKAGE.FIND_REGION_AND_CURRENCY(country_name, country); | |
DBMS_OUTPUT.PUT_LINE(country.country_name || ', ' || country.region || ', ' || country.currency); | |
END; | |
------P3 & P4------ | |
DECLARE | |
region_name VARCHAR2(50) := 'Central America'; | |
countries TRAVELER_ASSISTANCE_PACKAGE.countries_type; | |
BEGIN | |
TRAVELER_ASSISTANCE_PACKAGE.COUNTRIES_IN_SAME_REGION(region_name, countries); | |
DBMS_OUTPUT.PUT_LINE('Countries in the same region are: '); | |
TRAVELER_ASSISTANCE_PACKAGE.PRINT_REGION_ARRAY(countries); | |
END; | |
------P5 & P6------ | |
DECLARE | |
country_name VARCHAR2(50) := 'Belize'; | |
country_langs TRAVELER_ASSISTANCE_PACKAGE.country_languages_type; | |
BEGIN | |
TRAVELER_ASSISTANCE_PACKAGE.COUNTRY_LANGUAGES(country_name, country_langs); | |
DBMS_OUTPUT.PUT_LINE('Languages spoken in ' || country_name || ' are: '); | |
TRAVELER_ASSISTANCE_PACKAGE.PRINT_LANGUAGE_ARRAY(country_langs); | |
END; | |
--------------PACKAGE 2: TRAVELER ADMIN PACKAGE-------------- | |
CREATE OR REPLACE PACKAGE traveler_admin_package AS | |
TYPE object_rec IS RECORD( | |
name USER_DEPENDENCIES.name%TYPE, | |
type USER_DEPENDENCIES.type%TYPE, | |
referenced_name USER_DEPENDENCIES.referenced_name%TYPE, | |
referenced_type USER_DEPENDENCIES.referenced_type%TYPE | |
); | |
TYPE object_array IS TABLE OF object_rec INDEX BY PLS_INTEGER; | |
--P1 | |
PROCEDURE display_disabled_triggers; | |
--P2 | |
FUNCTION all_dependent_objects(object_name VARCHAR2) RETURN object_array; | |
--P3 | |
PROCEDURE print_dependent_objects(objects IN object_array); | |
END; | |
CREATE OR REPLACE PACKAGE BODY traveler_admin_package AS | |
--P1 | |
PROCEDURE display_disabled_triggers IS | |
CURSOR triggers IS SELECT trigger_name FROM user_triggers WHERE status = 'DISABLED'; | |
BEGIN | |
FOR trigger IN triggers LOOP | |
DBMS_OUTPUT.PUT_LINE( 'Trigger ' || trigger.trigger_name ||' is disabled' ); | |
END LOOP; | |
END; | |
--P2 | |
FUNCTION all_dependent_objects(object_name VARCHAR2) | |
RETURN object_array IS | |
CURSOR object_cur IS SELECT name, type, referenced_name, referenced_type | |
FROM USER_DEPENDENCIES WHERE referenced_name = UPPER( object_name ); | |
v_objects object_array; | |
i PLS_INTEGER := 1; | |
BEGIN | |
FOR v_object IN object_cur LOOP | |
v_objects(i) := v_object; | |
i := i + 1; | |
END LOOP; | |
IF (v_objects.COUNT < 1) THEN | |
RAISE NO_DATA_FOUND; | |
END IF; | |
RETURN v_objects; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN | |
RAISE_APPLICATION_ERROR(-20001, 'No data found'); | |
END; | |
--P3 | |
PROCEDURE print_dependent_objects(objects IN object_array) IS | |
BEGIN | |
DBMS_OUTPUT.PUT_LINE('NAME TYPE REFERENCED_NAME REFERENCED_TYPE'); | |
FOR i IN objects.FIRST .. objects.LAST LOOP | |
DBMS_OUTPUT.PUT_LINE( RPAD(objects(i).name, 31) || RPAD(objects(i).type, 31) || | |
RPAD(objects(i).referenced_name, 31) || RPAD(objects(i).referenced_type,31) ); | |
END LOOP; | |
END; | |
END; | |
----------TESTS---------- | |
------P1------ | |
--- See output of user_triggers | |
SELECT trigger_name FROM user_triggers; | |
--- DISABLE trigger | |
ALTER TRIGGER COUNT_TRIGGER DISABLE; | |
--- SHOW result | |
BEGIN | |
traveler_admin_package.display_disabled_triggers(); | |
END; | |
--- ENABLE trigger | |
ALTER TRIGGER COUNT_TRIGGER ENABLE; | |
--- SHOW result TYPE REFERENCED_NAME REFERENCED_TYPE | |
EMP_DETAILS_VIEW VIEW REGIONS | |
TABLE | |
TRAVELER_ASSISTANCE_PACKAGE PACKAGE REGIONS | |
TABLE | |
TRAVELER_ASSISTANCE_PACKAGE PACKAGE BODY REGIONS | |
TABLE | |
BEGIN | |
traveler_admin_package.display_disabled_triggers(); | |
END; | |
------P2 & P3------ | |
--- See user dependecies | |
SELECT * FROM USER_DEPENDENCIES WHERE referenced_name = 'REGIONS'; | |
--- See output of all_dependent_objects | |
DECLARE | |
v_objects TRAVELER_ADMIN_PACKAGE.object_array; | |
BEGIN | |
v_objects := traveler_admin_package.all_dependent_objects('regions'); | |
traveler_admin_package.print_dependent_objects(v_objects); | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment