Mapping elements of VRA to a number
487375Jul 7 2006 — edited Jul 11 2006hi,
My project involves maintaining the database for a bus company.
Each bus_route is divided into regions. Each bus-stop on a route maps to a region.
eg. bus-stop numbers 1,2,4,5,6,8 map to Region 2.
I was thinking of having a table regions with the following structure:
create table regions
(
Region_id NUMBER,
route_id VARCHAR2(20),
bus_stops stops_va
);
INSERT INTO regions VALUES(2, '459UP', STOPS_VA(1,2,4,5, 6, 8));
However, given a route_id and bus-stop number determining the region is a little tedious involving
looping through the varray elements of all rows with given route_id.
Is there an easier and more efficient way of doing this?
Amina