Use Oracle Object Features - Exercise

Contact Us or call 1-877-932-8228
Use Oracle Object Features - Exercise

Use Oracle Object Features

Duration: 20 to 30 minutes.
  1. Create an object type named car_typ. It should include the attributes for id (NUMBER), maker(VARCHAR2), model_name(VARCHAR2), year_made (NUMBER) and miles per gallon (NUMBER). It should include functions to get the id and a string representation of an instance, as well as a function to determine the age of the car (in years) and the gallons needed to drive a given number of miles.
  2. Create the implementation for the car_typ listed above.
  3. Create a table named car_obj_table to hold instances if this class.
  4. Create a car instance with id set to 101, maker set to Ford, model_name set to Torino, year set to 1971, and miles per gallon set to 11.
  5. Create a query to select the maker, model, age and gallons needed to travel 22 miles for the instance created.
  6. Remove the car_obj_table and type car_typ.

Solution:

Specialized-Topics/Solutions/solution.sql
create or replace TYPE car_typ IS OBJECT (
  id             NUMBER,
  maker          VARCHAR2(25),
  model_name     VARCHAR2(20),
  year_made      NUMBER,
  mpg            NUMBER, 
  MAP MEMBER FUNCTION get_id RETURN NUMBER, 
  MEMBER FUNCTION to_string  RETURN VARCHAR2,
  MEMBER FUNCTION age RETURN NUMBER,
  MEMBER FUNCTION gallons_needed(v_miles VARCHAR2) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY car_typ IS
  MAP MEMBER FUNCTION get_id RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;

  MEMBER FUNCTION to_string  
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR(id) || ' ' || maker || ' ' || model_name || ' '
                       || 'Year: ' || year_made || ' ' || mpg;
  END;

  MEMBER FUNCTION age
  RETURN NUMBER
  IS
  BEGIN
     RETURN  trunc(months_between(sysdate,
                  to_date('01/01/' || year_made, 'MM/DD/YYYY'))/12);
  END;
   
  MEMBER FUNCTION gallons_needed(v_miles VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
     RETURN round(v_miles / mpg);
  END;

END;
/

CREATE TABLE 
  car_obj_table OF car_typ;

INSERT INTO car_obj_table 
VALUES
(car_typ(101, 'Ford', 'Torino', 1971, 11) );

SELECT c.maker, c.model_name, c.age(), c.gallons_needed(22)
FROM car_obj_table c;

drop table car_obj_table;

drop type car_typ;

Code Explanation