I have designed an Oracle database as below. My question is, how do I define a function in the BodyModel_TYPE to display no. of single deck buses in each body model and while that function has to accept a specific body model as input/parameter.
Diagram for the following database schema
The method I have coded is like:
ALTER TYPE BodyModel_TYPE
ADD MEMBER FUNCTION cal_single_deck_bus RETURN int
CASCADE;
CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS
MEMBER FUNCTION cal_single_deck_bus RETURN int IS
N int;
BEGIN
SELECT COUNT(t.BUSID) INTO N FROM SINGLEDECKBUS_TABLE T WHERE DEREF(t.BODYMODEL) = self;
RETURN N;
END cal_single_deck_bus;
END;
/
It does not have an input and the returned result is not as expected though. The full sql code is as following:
-- Engine Model
create or replace type EngineModel_TYPE as object
(EngineModelID int,
EngineDescription varchar2(45),
EngineType varchar2(25),
EngineCapacity int);
/
create table EngineModel_TABLE of EngineModel_TYPE;
alter table EngineModel_TABLE
add constraint EngineModelID_PK primary key (EngineModelID);
-- Body Model
create or replace type BodyModel_TYPE as object
(ModelID int,
ModelName varchar2(45),
FloorType varchar2(5),
Manufacturer varchar2(45),
Length number(8,2),
Width number(8,2),
NoOfAxles int);
/
create table BodyModel_TABLE of BodyModel_TYPE;
alter table BodyModel_TABLE
add constraint BodyModelID_PK primary key (ModelID);
-- Bus
create or replace type Bus_TYPE as object
(BusID int,
RegistrationNo VARCHAR2(10),
RegistrationExpireDate date,
EngineModel REF EngineModel_TYPE,
BodyModel REF BodyModel_TYPE) NOT FINAL;
/
-- Single Decker Bus
CREATE OR REPLACE TYPE SingleDeckBus_type UNDER Bus_type
(NoOfDoors INT,
SeatingCapacity int,
StandingCapacity int);
/
CREATE TABLE SingleDeckBus_table OF SingleDeckBus_type;
ALTER TABLE SingleDeckBus_table
ADD CONSTRAINT SingleDeckBus_BusId_pk PRIMARY KEY (BusId);
-- Double Decker bus
CREATE OR REPLACE TYPE DoubleDeckBus_type UNDER Bus_type
(LowerDeckSeatingCapacity int,
LowerDeckStandingCapacity int,
LuggageCapacity number(8,2),
UpperDeckSeatingCapacity int);
/
CREATE TABLE DoubleDeckBus_table OF DoubleDeckBus_type;
ALTER TABLE DoubleDeckBus_table
ADD CONSTRAINT DoubleDeckBus_BusId_pk PRIMARY KEY (BusId);
-- Accessory
CREATE OR REPLACE TYPE Accessory_type AS OBJECT
(AccessoryId INT,
AccessoryName VARCHAR2(45),
AccessoryDescription VARCHAR2(45),
InstallDate DATE,
SingleDeckBus REF SingleDeckBus_type,
DoubleDeckBus REF DoubleDeckBus_type);
/
CREATE TABLE Accessory_table OF Accessory_type;
ALTER TABLE Accessory_table
ADD CONSTRAINT AccessoryId_pk PRIMARY KEY(AccessoryId);
-- 2. Insert record statements (3 records for each table that support testing of your queries)
insert into EngineModel_TABLE values
(1, 'Description 1', 'Type 1', 10);
insert into EngineModel_TABLE values
(2, 'Description 2', 'Type 2', 20);
insert into EngineModel_TABLE values
(3, 'Description 3', 'Type 3', 30);
insert into BodyModel_TABLE values
(1, 'name 1', 'Type1', 'manufac1', 10.1, 5.1, 10);
insert into BodyModel_TABLE values
(2, 'name 2', 'Type2', 'manufac2', 10.2, 5.2, 20);
insert into BodyModel_TABLE values
(3, 'name 3', 'Type3', 'manufac3', 10.3, 5.3, 30);
insert into SingleDeckBus_table
SELECT 1, 'SA01', '1-Jan-2020', REF(E), REF(B), 1, 20, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 1 AND B.MODELID = 1;
insert into SingleDeckBus_table
SELECT 2, 'SA02', '2-Jan-2020', REF(E), REF(B), 2, 25, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 2 AND B.MODELID = 2;
insert into SingleDeckBus_table
SELECT 3, 'SA03', '3-Jan-2020', REF(E), REF(B), 3, 30, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 3 AND B.MODELID = 3;
insert into SingleDeckBus_table
SELECT 55, 'SA03', '3-Jan-2020', REF(E), REF(B), 3, 30, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 3 AND B.MODELID = 3;
insert into DOUBLEDECKBUS_TABLE
SELECT 4, 'SA04', '4-Jan-2020', REF(E), REF(B), 20, 10, 30.15, 20 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 1 AND B.MODELID = 1;
insert into DOUBLEDECKBUS_TABLE
SELECT 5, 'SA05', '5-Jan-2020', REF(E), REF(B), 25, 10, 35.15, 25 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 2 AND B.MODELID = 2;
insert into DOUBLEDECKBUS_TABLE
SELECT 6, 'SA06', '6-Jan-2020', REF(E), REF(B), 30, 10, 40.15, 30 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B
where E.ENGINEMODELID = 3 AND B.MODELID = 3;
insert into ACCESSORY_TABLE
SELECT 1, 'Acc1', 'AccDesc1', '1-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D
where s.BUSID = 1 AND d.BUSID = 4;
insert into ACCESSORY_TABLE
SELECT 2, 'Acc2', 'AccDesc2', '2-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D
where s.BUSID = 2 AND d.BUSID = 5;
insert into ACCESSORY_TABLE
SELECT 3, 'Acc3', 'AccDesc3', '3-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D
where s.BUSID = 3 AND d.BUSID = 6;
-- 3. Create a method in the appropriate class to display how many single deck buses in each body model.
ALTER TYPE BodyModel_TYPE
ADD MEMBER FUNCTION cal_single_deck_bus RETURN int
CASCADE;
CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS
MEMBER FUNCTION cal_single_deck_bus RETURN int IS
N int;
BEGIN
SELECT COUNT(t.BUSID) INTO N FROM SINGLEDECKBUS_TABLE T WHERE DEREF(t.BODYMODEL) = self;
RETURN N;
END cal_single_deck_bus;
END;
/
commit;
Your existing method function seems to get the right result, based on the inserts you showed:
SELECT b.ModelID, b.ModelName, b.cal_single_deck_bus() AS single_deck_bus_count
FROM BodyModel_TABLE b;
MODELID MODELNAME SINGLE_DECK_BUS_COUNT
---------- -------------------- ---------------------
1 name 1 1
2 name 2 1
3 name 3 2
That seems a reasonable way to do this. But if your assignment specifies that the function has to accept a body model - which I don't think the wording of your question 3 does, unless you've left part of it out - then you probably want a static function, not a member function:
-- remove your existing member function first
ALTER TYPE BodyModel_TYPE
DROP MEMBER FUNCTION cal_single_deck_bus RETURN int
CASCADE;
ALTER TYPE BodyModel_TYPE
ADD STATIC FUNCTION cal_single_deck_bus (p_bodymodel REF BodyModel_TYPE)
RETURN int CASCADE;
CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS
STATIC FUNCTION cal_single_deck_bus (p_bodymodel REF BodyModel_TYPE)
RETURN int IS
N int;
BEGIN
SELECT COUNT(t.BUSID) INTO N
FROM SINGLEDECKBUS_TABLE T
WHERE t.BODYMODEL = p_bodymodel;
RETURN N;
END cal_single_deck_bus;
END;
/
Then you can pass a specific reference from any of the related tables to the static function, or get the count for all body type for instance, e.g.:
SELECT b.ModelID, b.ModelName,
BodyModel_TYPE.cal_single_deck_bus(ref(b)) AS single_deck_bus_count
FROM BodyModel_TABLE b;
MODELID MODELNAME SINGLE_DECK_BUS_COUNT
---------- -------------------- ---------------------
1 name 1 1
2 name 2 1
3 name 3 2
The BodyModel_TYPE.cal_single_deck_bus(ref(b)) is a static call, as it's against the type itself rather than an instance of that type; compare with the earlier b.cal_single_deck_bus() call. In both cases b is an instance of the object type.
You could also get the reference from elsewhere though, e.g. in a rather contrived way:
SELECT d.BusID, d.UpperDeckSeatingCapacity,
deref(d.BodyModel).ModelID as ModelID,
deref(d.BodyModel).ModelName as ModelName,
BodyModel_TYPE.cal_single_deck_bus(d.BodyModel) AS single_deck_bus_count
FROM DoubleDeckBus_TABLE d
WHERE d.RegistrationNo = 'SA06';
BUSID UPPERDECKSEATINGCAPACITY MODELID MODELNAME SINGLE_DECK_BUS_COUNT
---------- ------------------------ ---------- -------------------- ---------------------
6 30 3 name 3 2
If you want to pass a model ID in, rather than an object reference, you will need to join the body model and single-decker bus tables together inside the static function:
-- remove your existing member function first
ALTER TYPE BodyModel_TYPE
DROP STATIC FUNCTION cal_single_deck_bus (p_bodymodel REF BodyModel_TYPE) RETURN int
CASCADE;
ALTER TYPE BodyModel_TYPE
ADD STATIC FUNCTION cal_single_deck_bus (p_modelid int)
RETURN int CASCADE;
CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS
STATIC FUNCTION cal_single_deck_bus (p_modelid int)
RETURN int IS
N int;
BEGIN
SELECT COUNT(sd.BUSID) INTO N
FROM BODYMODEL_TABLE b
JOIN SINGLEDECKBUS_TABLE sd
ON sd.BODYMODEL = REF(b)
WHERE b.ModelID = p_modelid;
RETURN N;
END cal_single_deck_bus;
END;
/
Then you can call it as:
SELECT BodyModel_TYPE.cal_single_deck_bus(3) AS single_deck_bus_count FROM dual;
SINGLE_DECK_BUS_COUNT
---------------------
2
or if you want to show related info as well:
SELECT b.ModelID, b.ModelName,
BodyModel_TYPE.cal_single_deck_bus(b.ModelID) AS single_deck_bus_count
FROM BodyModel_TABLE b;
MODELID MODELNAME SINGLE_DECK_BUS_COUNT
---------- -------------------- ---------------------
1 name 1 1
2 name 2 1
3 name 3 2