SHARE
TWEET

function calling another funciton

e_codes Oct 13th, 2019 115 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* pilot_age is another function that has been created and tested and works, It displays the age of the employee from aa_employee table.  This new function is supposed to take in that value and use it here*/
  2.  
  3. CREATE OR REPLACE FUNCTION new_pilot
  4. (sf_emp_num IN NUMBER,
  5. pilot_age IN NUMBER)
  6.  
  7. RETURN VARCHAR2
  8. IS
  9. CURSOR cur_pilot2 IS
  10.  
  11.  SELECT emp_num , emp_fname,PIL_MED_TYPE, TRUNC(MONTHS_BETWEEN(SYSDATE,PIL_MED_DATE)) LastPhys
  12.  FROM aa_pilot INNER JOIN aa_employee
  13.  USING(emp_num)
  14. WHERE sf_emp_num = EMP_NUM;
  15.  
  16.  
  17. lv_Status VARCHAR2(60);
  18. ex_notPilot EXCEPTION;
  19.  
  20. BEGIN
  21.  
  22. FOR rec_pilot2 IN cur_pilot2 LOOP
  23.  
  24.  
  25. IF cur_pilot2%NOTFOUND THEN
  26. RAISE ex_notPilot;
  27. ELSIF pilot_age < 40 AND rec_pilot2.LastPhys < 60 AND rec_pilot2.PIL_MED_TYPE = 3
  28. THEN lv_Status := 'Third Class Physical is Valid';
  29. ELSIF pilot_age > 40 AND rec_pilot2.LastPhys > 24 AND rec_pilot2.PIL_MED_TYPE = 3
  30. THEN lv_Status := 'Third Class Physical is Expired';
  31. ELSIF rec_pilot2.PIL_MED_TYPE = 2 AND rec_pilot2.LastPhys > 12
  32. THEN  lv_Status := 'Second Class Physical is Expired';
  33. ELSIF pilot_age < 40 AND rec_pilot2.LastPhys > 12 AND rec_pilot2.PIL_MED_TYPE = 1
  34. THEN lv_Status := 'First Class Physical is Expired';
  35. ELSIF pilot_age > 40 AND rec_pilot2.LastPhys > 6 AND rec_pilot2.PIL_MED_TYPE = 1
  36. THEN lv_Status := 'First Class Physical is Expired';
  37. ELSE
  38. lv_Status := 'Does not need a Physical';
  39. END IF;
  40. RETURN lv_Status;
  41. END LOOP;
  42.  
  43. EXCEPTION WHEN ex_notPilot THEN
  44. DBMS_OUTPUT.PUT_LINE('Not a pilot');
  45. END;
  46.  
  47. /*call to the function using a select statement. Code works with all other employees that exist (101,104,105,106,109) on the aa_pilot table.  I want the code to display a not a pilot message when calling upon employee 102 because that emp_num does not exist in the aa_pilot table */
  48.  
  49. --This select statements call upon emp_num 101 and displays correct message
  50.  
  51. SELECT emp_title || ' ' || emp_fname || ' ' || emp_lname || ' ' ||
  52. new_pilot(emp_num, pilot_age(emp_num)) "Physical" FROM aa_employee WHERE emp_num = 101;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top