目前存在两个表：stops，line其中stops包含线路所经过的所有站点信息，line包含所有线路的信息，stops里站点按sort排序，line和station里有line_id关联，现在的目的是由这两个表生成一个路由视图route，路由表包含所有站点到站点的线路，所以在建立视图的时候就必须会用到循环查询，现在我能够通过条件控制任何一段路由，但是数据量比较大，用union不现实，所以怎样才能实现循环查询出每一条路由信息映射到视图当中？谢谢各位大侠了，在线焦急的等待，网上四处寻找都未果，特地来这真诚的寻求帮助！
-- 路由表 ----------------------
-- 线路id
t_line_id gzcx_route.line_id%type;
-- 交通类型
t_traffic_type gzcx_route.traffic_type%type;
-- 线路名称
t_line_name gzcx_route.line_name%type;
-- 起始站区号
t_from_district_code gzcx_route.from_district_code%type;
-- 起始站id
t_from_station_id gzcx_route.from_station_id%type;
-- 起始站名称
t_from_station_name gzcx_route.from_station_name%type;
-- 终点站区号
t_end_district_code gzcx_route.end_district_code%type;
-- 终点站id
t_end_station_id gzcx_route.end_station_id%type;
-- 终点站名称
t_end_station_name gzcx_route.end_station_name%type;
-- 运行时间
t_run_time gzcx_route.run_time%type;
-- 运行公里数
t_run_mileage gzcx_route.run_mileage%type;
-- 价格
t_price gzcx_route.price%type;
-- 起始点运行公里数
v_start_run_km gzcx_route.run_mileage%type;
-- 起始点运行时间
v_start_run_time gzcx_route.run_time%type;
-- 起始点价格
v_start_price gzcx_route.price%type; -- 终止点运行公里数
v_end_run_km gzcx_route.run_mileage%type;
-- 终止点运行时间
v_end_run_time gzcx_route.run_time%type;
-- 终止点价格
v_end_price gzcx_route.price%type; -- 线路游标
cursor cur_line is
select line_id,traffic_type,line_name from gzcx_line; -- 班次游标
cursor cur_stops( lineid gzcx_route.line_id%type ) is
select s.district_code,gs.station_id,gs.station_name,gs.run_time,gs.run_mileage, gs.price, gs.sort
from gzcx_stops gs, gzcx_station s
where gs.station_id = s.station_id and gs.line_id = lineid
order by gs.sort; -- 后续班次游标
cursor cur_stops_next( lineid gzcx_route.line_id%type, sortindex gzcx_stops.sort%type ) is
select s.district_code,gs.station_id,gs.station_name,gs.run_time,gs.run_mileage, gs.price
from gzcx_stops gs, gzcx_station s
where gs.station_id = s.station_id and gs.line_id = lineid and gs.sort > sortindex
order by gs.sort;
begin
-- 清空路由表
execute immediate 'truncate table gzcx_route'; -- 插入新数据
for lines_item in cur_line
loop
t_line_id := lines_item.line_id;
t_traffic_type := lines_item.traffic_type;
t_line_name := lines_item.line_name; for stops_item in cur_stops(t_line_id)
loop
-- 起始点数据
t_from_district_code := stops_item.district_code;
t_from_station_id := stops_item.station_id;
t_from_station_name := stops_item.station_name;
-- 起始点当前运行时间
v_start_run_time := stops_item.run_time;
-- 起始点当前运行里程数
v_start_run_km := stops_item.run_mileage;
-- 起始点价格
v_start_price := stops_item.price; for stops_next_item in cur_stops_next(t_line_id,stops_item.sort)
loop
-- 终止点数据
t_end_district_code := stops_next_item.district_code;
t_end_station_id := stops_next_item.station_id;
t_end_station_name := stops_next_item.station_name;
-- 终止点当前运行时间
v_end_run_time := stops_next_item.run_time;
-- 终止点当前运行里程数
v_end_run_km := stops_next_item.run_mileage;
-- 终止点价格
v_end_price := stops_next_item.price; insert into gzcx_route (line_id,
traffic_type,
line_name,
from_district_code,
from_station_id,
from_station_name,
end_district_code,
end_station_id,
end_station_name,
run_time,
run_mileage,
price,
re)
values (
t_line_id,
t_traffic_type,
t_line_name,
t_from_district_code,
t_from_station_id,
t_from_station_name,
t_end_district_code,
t_end_station_id,
t_end_station_name,
v_end_run_time - v_start_run_time,
v_end_run_km - v_start_run_km,
v_end_price - v_start_price,
''
);
end loop;
end loop;
end loop;
end SP_ROUTETABLE_CREATE;问题就是现在这个功能是用存储过程实现的，但是项目经理说把存储过程改为视图来实现！
select * from v_table
start with ...
connect by prior parent_id =chile_id
(line_id, traffic_type, line_name, from_district_code, from_staion_id, from_station_name, end_district_code, end_station_id, end_station_name, run_time, run_mileage, price, re)
as
select DISTINCT
T1.line_id,T1.traffic_type,T1.line_name,T2.district_code as from_district_code,
T2.station_id as from_station_id,T2.station_name as from_station_name,T3.district_code
as end_district_code,T3.station_id as end_station_id,T3.station_name
as end_station_name,T3.run_time-T2.run_time as run_time,T3.run_mileage-T2.run_mileage
as run_mileage,T3.price-T2.price as price,null as re
FROM (select TT1.line_id,TT1.traffic_type,TT1.line_name
from v_gzcx_line_cs TT1)
T1,
(select TT2.line_id,TT2.district_code,TT2.station_id,TT2.station_name,
TT2.run_time,TT2.run_mileage,TT2.price
from
(select TT1.line_id,TT1.traffic_type,TT1.line_name
from v_gzcx_line_cs TT1)
T1,
v_gzcx_stops_cs TT2
where TT2.line_id like T1.LINE_ID and TT2.SORT=1)
T2,
(select TT3.line_id,TT3.district_code,TT3.station_id,TT3.station_name,
TT3.run_time,TT3.run_mileage,TT3.price,TT3.SORT
from
(select TT2.line_id,TT2.district_code,TT2.station_id,TT2.station_name,
TT2.run_time,TT2.run_mileage,TT2.price,TT2.SORT
from
(select TT1.line_id,TT1.traffic_type,TT1.line_name
from v_gzcx_line_cs TT1)
T1,
v_gzcx_stops_cs TT2
where TT2.line_id like T1.LINE_ID and TT2.SORT=1)
T2,
v_gzcx_stops_cs TT3
where TT3.line_id like T2.LINE_ID AND TT3.SORT > T2.SORT)
T3;
这是我自己写的，不知道问题出在哪里哎郁闷！