Advertisement
Guest User

Untitled

a guest
May 25th, 2016
50
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 4.21 KB | None | 0 0
  1. CREATE OR REPLACE PACKAGE is309exam_pkg
  2. IS
  3. FUNCTION CALC_PERCENT_OF_GOAL_PF (
  4.   p_projID            IN INTEGER       -- required
  5.    ) RETURN INTEGER;
  6. END is309exam_pkg;
  7. /
  8.  
  9.  
  10. CREATE OR REPLACE PACKAGE BODY is309exam_pkg
  11. IS
  12. FUNCTION CALC_PERCENT_OF_GOAL_PF (
  13.   p_projID            IN INTEGER       -- required
  14.    ) RETURN INTEGER
  15. IS
  16.  ex_error        ERROR;
  17.  
  18.  lv_goal_num     GG_PROJECT.PROJECT_GOAL%TYPE;
  19.  lv_amtDonated_num  GG_DONATIONDETAIL.DONATIONDETAIL_AMOUNT%ROWTYPE;
  20. BEGIN
  21.   -- Check that the parameter is not NULL
  22.  IF p_projID IS NULL THEN
  23.   lv_errorMsg_txt := 'The project identifier is missing.';
  24.   RAISE ex_Error;
  25.   p_projID := 9999;
  26.    
  27.   -- Retrieve the goal of the project
  28.  
  29.  lv_errorMsg_txt := 'Project ' || p_projID || ' was not found.';
  30.  SELECT project_goal INTO lv_goal_num, lv_projID
  31.  FROM GG_PROJECT
  32.  WHERE project_id = p_projID;
  33.  
  34.   -- Retrieve the total amount contributed to this project
  35.   lv_errormsg_txt := 'Error calculating amount donated for project' || p_projID;
  36.   SELECT SUM(donationdetail_amount) INTO lv_amtDonated_num
  37.   FROM GG_DONATIONDETAIL
  38.   WHERE project_id = p_projID;
  39.    
  40.    
  41.   -- Calculate the percentage & return this value
  42.   lv_errormsg_txt := 'Error calculating the percent donated for project ' || p_projID;
  43.    
  44.  
  45.   -- Calculate the percentage donated
  46.   IF lv_goal_num = 0 THEN
  47.    lv_errorMsg_txt := 'Unable to calculate the percentage donated.  Division by zero.'; -- Shouldn't happen
  48.    RAISE ex_Error;
  49.   ELSE
  50.     lv_percentageDonated := 100*(lv_amtDonated_num/lv_goal_num);
  51.   END IF;
  52.    
  53. EXCEPTION  
  54. WHEN NO_DATA_FOUND THEN
  55.   PRINT(lv_errorMsg_txt);
  56.   RETURN NULL;
  57. WHEN ex_error THEN
  58.   PRINT(lv_errorMsg_txt);
  59.   RETURN NULL;
  60. END CALC_PERCENT_OF_GOAL_PF;
  61. PROCEDURE LIST_PROJECTS_PP (
  62.   p_orgID             IN INTEGER       -- required
  63.    )
  64. IS
  65.   lv_errorMsg_txt     VARCHAR(100);
  66.   ex_Error            ERROR;
  67.    
  68.   CURSOR cur_project IS
  69.   SELECT project_id,
  70.   project_title,
  71.   project_goal,
  72.   c.country_iso3166,
  73.   country_name
  74.   FROM gg_project p
  75.   LEFT JOIN gg_country c
  76.   ON p.country_iso3166 = c.country_iso3166
  77.   WHERE p.org_id = p_orgID;
  78.  
  79.   rec_project             cur_project%ROWTYPE;
  80.    
  81.   CURSOR cur_categories IS
  82.   SELECT category_name
  83.   FROM GG_PROJCAT
  84.   WHERE project_id = rec_project.project_id;
  85.    
  86.   rec_categories          cur_categories%ROWTYPE;
  87.    
  88.   lv_count_num            INTEGER;
  89.   lv_percentOfGoal_num    INTEGER;
  90.    
  91. BEGIN
  92.   NULL;
  93.   -- Check whether the p_orgID is NULL
  94.   IF p_orgID IS NULL THEN
  95.     lv_errorMsg_txt := 'The organization identifier must not be NULL.';
  96.     RAISE ex_Error;
  97.   END IF;
  98.    
  99.   -- Check whether the p_orgID value is valid
  100.   SELECT COUNT(*) INTO lv_count_num
  101.   FROM GG_ORGANIZATION
  102.   WHERE org_id = p_orgID;
  103.    
  104.   IF lv_count_num = 0 THEN
  105.     lv_errorMsg_txt := 'Organization ' || p_orgID || ' does not exist.';
  106.     RAISE ex_Error;
  107.   END IF;
  108.    
  109.   -- Open cursor that contains each active project sponsored by this organization
  110.    
  111.   OPEN cur_project;
  112.    
  113.   LOOP
  114.     GET cur_project INTO rec_project;
  115.      
  116.     EXIT WHEN cur_project%NOTFOUND;
  117.      
  118.      
  119.   -- For each project, calculate the percent to goal
  120.     lv_percentOfGoal_num := CALC_PERCENT_OF_GOAL_PF(rec_project.project_id);
  121.      
  122.   -- Display required information
  123.    
  124.     DBMS_OUTPUT.PUT_LINE('Project ID:      ' || rec_project.project_id);
  125.     DBMS_OUTPUT.PUT_LINE('Project title:   ' || rec_project.project_title);
  126.     DBMS_OUTPUT.PUT_LINE('Country:         ' || rec_project.country_name);
  127.     DBMS_OUTPUT.PUT_LINE('Goal:            ' || rec_project.project_goal);
  128.     DBMS_OUTPUT.PUT_LINE('Percent of goal: ' || lv_percentOfGoal_num || '%');
  129.    
  130.   -- Display categories
  131.    
  132.     OPEN cur_categories;
  133.     LOOP
  134.       FETCH cur_categories INTO rec_categories;
  135.  
  136.     DBMS_OUTPUT.PUT_LINE('       Category:  ' || rec_categories.category_name);
  137.  
  138.      
  139.     CLOSE cur_categories;
  140.    
  141.   END LOOP;
  142.     CLOSE cur_project;
  143. EXCEPTION
  144. WHEN ex_error THEN
  145.   DBMS_OUTPUT.PUT_LINE(lv_errorMsg_txt);
  146. WHEN OTHERS THEN
  147.   DBMS_OUTPUT.PUT_LINE('An error has occurred.');
  148.   DBMS_OUTPUT.PUT_LINE('Error code:  ' || SQLCODE);
  149.   DBMS_OUTPUT.PUT_LINE('Error msg :  ' || SQLERRM);
  150. END list_projects_pp;
  151. END GLOBALGIVING_pkg;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement