SHARE
TWEET

Untitled

a guest Oct 10th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET SERVEROUTPUT ON;
  2.  
  3. DECLARE
  4.  
  5.   --Define the RECORD object
  6.   TYPE EmpRecord
  7.     IS RECORD (ssn          employee.ssn%TYPE,
  8.                LName        employee.LName%TYPE,
  9.                DName        department.DName%TYPE,
  10.                BonusPayment NUMBER(6)
  11.                );
  12.  
  13.   --Declare variables
  14.   ActiveEmp   EmpRecord;
  15.   InactiveEmp EmpRecord;
  16.  
  17. BEGIN
  18. <<LocateActive>>
  19.   SELECT essn, LName, DName, 5000
  20.   INTO ActiveEmp
  21.   FROM employee emp
  22.     INNER JOIN department dep ON emp.dno = dep.dnumber
  23.     INNER JOIN works_on wo ON emp.ssn = wo.essn
  24.   WHERE
  25.     hours = (SELECT MAX(hours) FROM works_on)
  26.     AND ROWNUM <= 1;
  27.    
  28. <<OutputActive>>
  29.   DBMS_OUTPUT.put_line('Active employee name: ' || ActiveEmp.LName);
  30.   DBMS_OUTPUT.put_line('Active employee department: ' || ActiveEmp.DName);
  31.   DBMS_OUTPUT.put_line('Active employee bonus: $' || ActiveEmp.BonusPayment);
  32.  
  33. <<LocateInactive>>
  34.   SELECT essn, LName, DName, 0
  35.   INTO ActiveEmp
  36.   FROM employee emp
  37.     INNER JOIN department dep ON emp.dno = dep.dnumber
  38.     INNER JOIN works_on wo ON emp.ssn = wo.essn
  39.   WHERE
  40.     hours = (SELECT MIN(hours) FROM works_on)
  41.     AND ROWNUM <= 1;
  42.    
  43.     --Test whether the Active and Inactive individual are the same
  44.     IF ActiveEmp.ssn = InactiveEmp.ssn THEN
  45.       GOTO Conclusion;
  46.     END IF;
  47.    
  48. <<OutputActive>>
  49.   DBMS_OUTPUT.put_line('');
  50.   DBMS_OUTPUT.put_line('Inactive employee name: ' || InactiveEmp.LName);
  51.   DBMS_OUTPUT.put_line('Inactive employee department: ' || InactiveEmp.DName);
  52.   DBMS_OUTPUT.put_line('Inactive employee bonus: $' || InactiveEmp.BonusPayment);
  53.  
  54. <<Conclusion>>
  55.   NULL;
  56.  
  57. END;
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top