Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PACKAGE is309exam_pkg
- IS
- FUNCTION CALC_PERCENT_OF_GOAL_PF (
- p_projID IN INTEGER -- required
- ) RETURN INTEGER;
- END is309exam_pkg;
- /
- CREATE OR REPLACE PACKAGE BODY is309exam_pkg
- IS
- FUNCTION CALC_PERCENT_OF_GOAL_PF (
- p_projID IN INTEGER -- required
- ) RETURN INTEGER
- IS
- ex_error ERROR;
- lv_goal_num GG_PROJECT.PROJECT_GOAL%TYPE;
- lv_amtDonated_num GG_DONATIONDETAIL.DONATIONDETAIL_AMOUNT%ROWTYPE;
- BEGIN
- -- Check that the parameter is not NULL
- IF p_projID IS NULL THEN
- lv_errorMsg_txt := 'The project identifier is missing.';
- RAISE ex_Error;
- p_projID := 9999;
- -- Retrieve the goal of the project
- lv_errorMsg_txt := 'Project ' || p_projID || ' was not found.';
- SELECT project_goal INTO lv_goal_num, lv_projID
- FROM GG_PROJECT
- WHERE project_id = p_projID;
- -- Retrieve the total amount contributed to this project
- lv_errormsg_txt := 'Error calculating amount donated for project' || p_projID;
- SELECT SUM(donationdetail_amount) INTO lv_amtDonated_num
- FROM GG_DONATIONDETAIL
- WHERE project_id = p_projID;
- -- Calculate the percentage & return this value
- lv_errormsg_txt := 'Error calculating the percent donated for project ' || p_projID;
- -- Calculate the percentage donated
- IF lv_goal_num = 0 THEN
- lv_errorMsg_txt := 'Unable to calculate the percentage donated. Division by zero.'; -- Shouldn't happen
- RAISE ex_Error;
- ELSE
- lv_percentageDonated := 100*(lv_amtDonated_num/lv_goal_num);
- END IF;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- PRINT(lv_errorMsg_txt);
- RETURN NULL;
- WHEN ex_error THEN
- PRINT(lv_errorMsg_txt);
- RETURN NULL;
- END CALC_PERCENT_OF_GOAL_PF;
- PROCEDURE LIST_PROJECTS_PP (
- p_orgID IN INTEGER -- required
- )
- IS
- lv_errorMsg_txt VARCHAR(100);
- ex_Error ERROR;
- CURSOR cur_project IS
- SELECT project_id,
- project_title,
- project_goal,
- c.country_iso3166,
- country_name
- FROM gg_project p
- LEFT JOIN gg_country c
- ON p.country_iso3166 = c.country_iso3166
- WHERE p.org_id = p_orgID;
- rec_project cur_project%ROWTYPE;
- CURSOR cur_categories IS
- SELECT category_name
- FROM GG_PROJCAT
- WHERE project_id = rec_project.project_id;
- rec_categories cur_categories%ROWTYPE;
- lv_count_num INTEGER;
- lv_percentOfGoal_num INTEGER;
- BEGIN
- NULL;
- -- Check whether the p_orgID is NULL
- IF p_orgID IS NULL THEN
- lv_errorMsg_txt := 'The organization identifier must not be NULL.';
- RAISE ex_Error;
- END IF;
- -- Check whether the p_orgID value is valid
- SELECT COUNT(*) INTO lv_count_num
- FROM GG_ORGANIZATION
- WHERE org_id = p_orgID;
- IF lv_count_num = 0 THEN
- lv_errorMsg_txt := 'Organization ' || p_orgID || ' does not exist.';
- RAISE ex_Error;
- END IF;
- -- Open cursor that contains each active project sponsored by this organization
- OPEN cur_project;
- LOOP
- GET cur_project INTO rec_project;
- EXIT WHEN cur_project%NOTFOUND;
- -- For each project, calculate the percent to goal
- lv_percentOfGoal_num := CALC_PERCENT_OF_GOAL_PF(rec_project.project_id);
- -- Display required information
- DBMS_OUTPUT.PUT_LINE('Project ID: ' || rec_project.project_id);
- DBMS_OUTPUT.PUT_LINE('Project title: ' || rec_project.project_title);
- DBMS_OUTPUT.PUT_LINE('Country: ' || rec_project.country_name);
- DBMS_OUTPUT.PUT_LINE('Goal: ' || rec_project.project_goal);
- DBMS_OUTPUT.PUT_LINE('Percent of goal: ' || lv_percentOfGoal_num || '%');
- -- Display categories
- OPEN cur_categories;
- LOOP
- FETCH cur_categories INTO rec_categories;
- DBMS_OUTPUT.PUT_LINE(' Category: ' || rec_categories.category_name);
- CLOSE cur_categories;
- END LOOP;
- CLOSE cur_project;
- EXCEPTION
- WHEN ex_error THEN
- DBMS_OUTPUT.PUT_LINE(lv_errorMsg_txt);
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('An error has occurred.');
- DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
- DBMS_OUTPUT.PUT_LINE('Error msg : ' || SQLERRM);
- END list_projects_pp;
- END GLOBALGIVING_pkg;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement