OraclePLSQL--Complex extended user-defined types (decode the code )

Complex extended user-defined types

(decode the code J)

DECLARE

/*

Simple type declaration

*/

    TYPE BonusCompensation

      IS RECORD (CashPayment    NUMBER(6),

                 CompanyCar     BOOLEAN,

                 VacationWeeks  NUMBER(2)

                  );

/*

Extended type declaration

*/

      TYPE EmpRecord

      IS RECORD  (ssn          employee.ssn%TYPE,

                 LName         employee.LName%TYPE,

                 FNAME          employee.FName%TYPE,

                 DName         department.DName%TYPE,

                 BonusPayment  BonusCompensation

                  );

/*

Instance declaration

*/

    BestEmp  EmpRecord;

/*

Another extended type declaration along with instance declaration.

*/

    TYPE ManagerRecord

    IS RECORD (Lname         employee.Lname%TYPE,

               ssn           employee.ssn%TYPE,

BonusPayment  BonusCompensation

               );

    BestManager  ManagerRecord;

BEGIN

/*

Less than meaningful logic to determine the employee who should receive a

bonus. The main focus in this example is the ability to store database values

within the record instance.

*/

  SELECT essn, LName,Fname, DName

  INTO BestEmp.ssn,

       BestEmp.LName,

       BestEmp.Fname,

       BestEmp.DName

  FROM employee, department, works_on

  WHERE employee.dno = department.dnumber

  AND employee.ssn = works_on.essn

  AND hours = (SELECT MAX(hours) FROM works_on)

  AND ROWNUM <= 1;

/*

The next segment of code accesses the values within the record instance.

*/

  BestEmp.BonusPayment.CashPayment := 5000;

  BestEmp.BonusPayment.CompanyCar := TRUE;

  BestEmp.BonusPayment.VacationWeeks := 1;

  dbms_output.put_line ('Best employee Family name: ' || BestEmp.FName);

  dbms_output.put_line ('Best employee name: ' || BestEmp.LName);

  dbms_output.put_line ('Best employee department: ' || BestEmp.DName);

  dbms_output.put_line ('Best employee bonus payment: ' ||

                         BestEmp.BonusPayment.CashPayment);

  IF BestEmp.BonusPayment.CompanyCar = TRUE THEN

        dbms_output.put_line ('Company car also provided');

  END IF;

  IF BestEmp.BonusPayment.VacationWeeks > 0 THEN

        dbms_output.put_line ('Extra vacation weeks granted: ' ||

                              BestEmp.BonusPayment.VacationWeeks);

  END IF;

/*

A similar set of instructions uses the other record instance. This is used to

perform similar logic for a manager who is selected for bonus compensation.

*/

  SELECT Lname,ssn

  INTO BestManager.Lname,BestManager.ssn

  FROM employee, department

  WHERE employee.ssn = department.MgrSSN

  AND

  ROWNUM <= 1;

  BestManager.BonusPayment.CashPayment := 10000;

  BestManager.BonusPayment.CompanyCar := TRUE;

  BestManager.BonusPayment.VacationWeeks := 2;

  dbms_output.put_line ('Best manager Name: ' || BestManager.Lname);

  dbms_output.put_line ('Best manager SSN: ' || BestManager.ssn);

  dbms_output.put_line ('Best manager bonus payment: ' ||

                         BestManager.BonusPayment.CashPayment);

  IF BestManager.BonusPayment.CompanyCar = TRUE THEN

        dbms_output.put_line ('Company car also provided');

  END IF;

  IF BestManager.BonusPayment.VacationWeeks > 0 THEN

        dbms_output.put_line ('Extra vacation weeks granted: ' ||

                              BestManager.BonusPayment.VacationWeeks);

  END IF;

END;

anonymous block completed

Best employee Family name: Bhutta

Best employee name: Ali

Best employee department: Operation

Best employee bonus payment: 5000

Company car also provided

Extra vacation weeks granted: 1

Best manager Name: Rafique

Best manager SSN: 333445555

Best manager bonus payment: 10000

Company car also provided

Extra vacation weeks granted: 2