oracle&mysql

/**
 * 初始化数据
 */

-- 初始化 update td_basic_mile_file 别名
update td_basic_mile_file a set 
en_alias_name=(select station_name from td_basic_station_branch b where a.en_station_name=b.file_station_name and rownum=1)
,ex_alias_name=(select station_name from td_basic_station_branch b where a.ex_station_name=b.file_station_name and rownum=1);
-- branch表到每个站点（file_station_name）必须只有一条记录，否则报错

-- 桩号
update td_basic_mile_file a 
set stakenum=(select min(stakenum) from td_basic_station b 
where a.en_alias_name=b.stationname) ; 
-- 不能用b.name, 不用min会多行，有bug
update td_basic_mile_file a 
set stakenum2=(select min(stakenum) from td_basic_station b 
where a.ex_alias_name=b.stationname) ; 
update td_basic_mile_file set stakenum2='45' where id=6;

-- 上行，下行
update td_basic_mile_file a set opposite='0' where to_number(stakenum)<to_number(stakenum2) ; 
update td_basic_mile_file a set opposite='1' where to_number(stakenum)>to_number(stakenum2) ; 

-- 初始化 收费单元等编码
update td_basic_mile_file a set unit_node_code=(select id||'11' from td_basic_station b where a.en_alias_name=b.name)
,opposite_node_code=(select id||'22' from td_basic_station b where a.en_alias_name=b.name)
,en_code=(select id from td_basic_station b where a.en_alias_name=b.name)
,ex_code=(select id from td_basic_station b where a.ex_alias_name=b.name) 
,create_time='2019-08-17 16:48:49'
where a.opposite='0';

-- null处理
update td_basic_mile_file a set unit_node_code=(select id||'33' from td_basic_station b where a.ex_alias_name=b.name)
,opposite_node_code=(select id||'44' from td_basic_station b where a.ex_alias_name=b.name)
where unit_node_code is null or unit_node_code=''  or unit_node_code like '12345%' and a.opposite='0';

update td_basic_mile_file a set unit_node_code='G005544001014111',opposite_node_code='G005544001014122'where id=14;
update td_basic_mile_file a set unit_node_code='G005544001013111',opposite_node_code='G005544001013122'where id=17;

-- 反向生成一份
insert into td_basic_mile_file 
select seq_file.nextval,ex_station_name,en_station_name,miles,create_time,null,ex_alias_name,en_alias_name,
opposite_node_code,unit_node_code,EX_CODE,EN_CODE,1-to_number(opposite),stakenum2,stakenum
from td_basic_mile_file t;


/**
 * 开始生成数据
 */
drop table td_node_code;

create table td_node_code as 
-- 收费站
select substr(b.id,1,7) toll_road_id,substr(b.id,1,11) section_id,b.id road_node_id
,b.name road_node_name ,'3' road_node_type, '0' status, null opposite_id, '166'version
from td_basic_station b,(select distinct en_alias_name from td_basic_mile_file) c
where b.NAME = c.en_alias_name

union all
-- 收费单元(包括反向)
select distinct substr(unit_node_code,1,7) toll_road_id,substr(b.unit_node_code,1,11) section_id,unit_node_code road_node_id,
en_alias_name||'-'||ex_alias_name road_node_name 
,'1' road_node_type, '0' status, opposite_node_code opposite_id, '166'version
from td_basic_mile_file b
;


drop table td_node_relation;
create table td_node_relation as 
-- 单元与单元relation
select distinct a.unit_node_code en_road_node_id,b.unit_node_code ex_road_node_id , a.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id 

union
-- 反向单元与单元relation
select distinct b.opposite_node_code en_road_node_id,a.opposite_node_code ex_road_node_id , b.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id -- 注意这里是b.miles

union
-- 上行-站与单元relation
select distinct b.en_code en_road_node_id,b.unit_node_code ex_road_node_id ,-- 不distinct会重复
'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name 
and a.up_en='1' and en_code is not null and b.opposite='0'

union
-- 上行-单元与站relation
select distinct b.unit_node_code en_road_node_id,b.ex_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name -- 这里是ex_alias_name
and a.up_ex='1' and ex_code is not null and b.opposite='0' -- null则没有，不能生成关系

union
-- 下行-站与单元relation
select distinct b.ex_code en_road_node_id,b.opposite_node_code ex_road_node_id ,'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name 
and a.down_en='1' and ex_code is not null and b.opposite='0'

union
-- 下行-单元与站relation
select distinct b.opposite_node_code en_road_node_id,b.en_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name 
and a.down_ex='1' and en_code is not null and b.opposite='0'

union
-- link-relation
select b.unit_node_code en_road_node_id,c.unit_node_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_road_link a,td_basic_mile_file b,td_basic_mile_file c
where a.en_start_station=b.en_station_name and a.en_end_station=b.ex_station_name and
a.ex_start_station=c.en_station_name and a.ex_end_station=c.ex_station_name
;


--------------------------------
-- 费率表
--------------------------------

-- update fee_file
update td_basic_fee_file a set unit_node_code=(select unit_node_code from td_basic_mile_file b where a.mile_file_id=b.id)
,opposite_node_code=(select opposite_node_code from td_basic_mile_file b where a.mile_file_id=b.id);

drop table td_province_price;
create table td_province_price as 
select substr(unit_node_code,1,7) toll_road_id,substr(unit_node_code,1,11) section_id,unit_node_code toll_Interval_Id,
vehicle_Type,price fee,'166' version, '190819' valid_date
from td_basic_fee_file;




mysql

/**
 * 初始化数据
 */

-- 初始化 update td_basic_mile_file 别名
update td_basic_mile_file a set 
en_alias_name=(select station_name from td_basic_station_branch b where a.en_station_name=b.file_station_name limit 1)
,ex_alias_name=(select station_name from td_basic_station_branch b where a.ex_station_name=b.file_station_name limit 1);
-- branch表到每个站点（file_station_name）必须只有一条记录，否则报错

-- 桩号
update td_basic_mile_file a 
set stakenum=(select min(stakenum) from td_basic_station b 
where a.en_alias_name=b.stationname) ; 
-- 不能用b.name, 不用min会多行，有bug
update td_basic_mile_file a 
set stakenum2=(select min(stakenum) from td_basic_station b 
where a.ex_alias_name=b.stationname) ; 
update td_basic_mile_file set stakenum2='45' where id=6;

-- 上行，下行
update td_basic_mile_file a set opposite='0' where cast(stakenum as unsigned int)<cast(stakenum2 as unsigned int) ; 
update td_basic_mile_file a set opposite='1' where cast(stakenum as unsigned int)>cast(stakenum2 as unsigned int) ; 

-- 初始化 收费单元等编码
update td_basic_mile_file a set unit_node_code=(select concat(id,'11') from td_basic_station b where a.en_alias_name=b.name)
,opposite_node_code=(select concat(id,'22') from td_basic_station b where a.en_alias_name=b.name)
,en_code=(select id from td_basic_station b where a.en_alias_name=b.name)
,ex_code=(select id from td_basic_station b where a.ex_alias_name=b.name) 
,create_time='2019-08-17 16:48:49'
where a.opposite='0';

-- null处理
update td_basic_mile_file a set unit_node_code=(select concat(id,'33') from td_basic_station b where a.ex_alias_name=b.name)
,opposite_node_code=(select concat(id,'44') from td_basic_station b where a.ex_alias_name=b.name)
where unit_node_code is null or unit_node_code=''  or unit_node_code like '12345%' and a.opposite='0';

update td_basic_mile_file a set unit_node_code='G005544001014111',opposite_node_code='G005544001014122'where id=14;
update td_basic_mile_file a set unit_node_code='G005544001013111',opposite_node_code='G005544001013122'where id=17;

-- 反向生成一份
insert into td_basic_mile_file 
select null,ex_station_name,en_station_name,miles,create_time,null,ex_alias_name,en_alias_name,
opposite_node_code,unit_node_code,EX_CODE,EN_CODE,1-cast(opposite as UNSIGNED integer),stakenum2,stakenum
from td_basic_mile_file t;


/**
 * 开始生成数据
 */
drop table td_node_code;

create table td_node_code as 
-- 收费站
select substr(b.id,1,7) toll_road_id,substr(b.id,1,11) section_id,b.id road_node_id
,b.name road_node_name ,'3' road_node_type, '0' status, null opposite_id, '166'version
from td_basic_station b,(select distinct en_alias_name from td_basic_mile_file) c
where b.NAME = c.en_alias_name

union all
-- 收费单元(包括反向)
select distinct substr(unit_node_code,1,7) toll_road_id,substr(b.unit_node_code,1,11) section_id,unit_node_code road_node_id,
concat(en_alias_name,'-',ex_alias_name) road_node_name
,'1' road_node_type, '0' status, opposite_node_code opposite_id, '166'version
from td_basic_mile_file b
;


drop table td_node_relation;

create table td_node_relation as 
-- 单元与单元relation
select distinct a.unit_node_code en_road_node_id,b.unit_node_code ex_road_node_id , a.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id 

union
-- 反向单元与单元relation
select distinct b.opposite_node_code en_road_node_id,a.opposite_node_code ex_road_node_id , b.miles,'166' version, '190819' valid_date
from td_basic_mile_file a, td_basic_mile_file b where a.next_id=b.id -- 注意这里是b.miles

union
-- 上行-站与单元relation
select distinct b.en_code en_road_node_id,b.unit_node_code ex_road_node_id ,-- 不distinct会重复
'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name 
and a.up_en='1' and en_code is not null and b.opposite='0'

union
-- 上行-单元与站relation
select distinct b.unit_node_code en_road_node_id,b.ex_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name -- 这里是ex_alias_name
and a.up_ex='1' and ex_code is not null and b.opposite='0' -- null则没有，不能生成关系

union
-- 下行-站与单元relation
select distinct b.ex_code en_road_node_id,b.opposite_node_code ex_road_node_id ,'0' miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.ex_alias_name 
and a.down_en='1' and ex_code is not null and b.opposite='0'

union
-- 下行-单元与站relation
select distinct b.opposite_node_code en_road_node_id,b.en_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_station_branch a,td_basic_mile_file b
where a.station_name=b.en_alias_name 
and a.down_ex='1' and en_code is not null and b.opposite='0'

union
-- link-relation
select b.unit_node_code en_road_node_id,c.unit_node_code ex_road_node_id ,b.miles miles,'166' version, '190819' valid_date
from td_basic_road_link a,td_basic_mile_file b,td_basic_mile_file c
where a.en_start_station=b.en_station_name and a.en_end_station=b.ex_station_name and
a.ex_start_station=c.en_station_name and a.ex_end_station=c.ex_station_name
;


--------------------------------
-- 费率表
--------------------------------

-- update fee_file
update td_basic_fee_file a set unit_node_code=(select unit_node_code from td_basic_mile_file b where a.mile_file_id=b.id)
,opposite_node_code=(select opposite_node_code from td_basic_mile_file b where a.mile_file_id=b.id);

drop table td_province_price;
create table td_province_price as 
select substr(unit_node_code,1,7) toll_road_id,substr(unit_node_code,1,11) section_id,unit_node_code toll_Interval_Id,
vehicle_Type,price fee,'166' version, '190819' valid_date
from td_basic_fee_file;



CREATE DEFINER=`motor`@`%` PROCEDURE `NewProc`()
BEGIN
	 -- 自定义变量
-- 	declare stopflag int default 0;
	DECLARE done BOOLEAN DEFAULT 0 ;
	
--   DECLARE var_me DOUBLE DEFAULT NULL ;
--   DECLARE var_pay_time TIMESTAMP DEFAULT NULL ;
  DECLARE var_en_name VARCHAR (100) DEFAULT NULL ;
  DECLARE var_ex_name VARCHAR (100) DEFAULT NULL ;
	DECLARE v_next_id VARCHAR (100) DEFAULT NULL ;
	DECLARE v_id VARCHAR (100) DEFAULT NULL ;
	DECLARE v_name VARCHAR (100) DEFAULT NULL ;
	DECLARE v_count int DEFAULT 0 ;
	DECLARE v_count2 int DEFAULT 0 ;
	DECLARE v_is_station BOOLEAN DEFAULT false;
	DECLARE v_miles VARCHAR (100) DEFAULT NULL ;

	declare cur CURSOR for select en_alias_name,ex_alias_name,next_id,miles from td_basic_mile_file;
-- 	1 游标是保存查询结果的临时内存区域
--   2 游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集
	
--   declare continue handler for not found set stopflag=1;
	declare continue handler for not found set done=1;
-- 	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
--   3 声明句柄，当游标变量中保存的结果都查询一遍(遍历)，到达结尾，就把变量stopflag设置为1 用于循环中判断是否遍历结束

  #Routine body goes here...
	delete from td_node_code;
	delete from td_node_relation; -- important!
	
	-- select 1;
	open cur; # 打开游标

	-- 批读取数据到指定变量上
  FETCH cur INTO var_en_name,var_ex_name,v_next_id,v_miles;
	while(done=0) DO # 如果游标还没到结尾，就继续循环
  begin
		# 根据入口生成收费站
		select count(*) into v_count2 from td_basic_station where name=var_en_name;
		if (v_count2 > 0) then
			select id,name into v_id,v_name from td_basic_station where name=var_en_name;
			select count(*) into v_count from td_node_code where road_node_name=var_en_name;
			if (v_count < 1) then
				insert into td_node_code -- (toll_road_id,section_id,road_node_id,road_node_name) 
				values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
			end if;
			set v_is_station = true;
		end if;
		
		# 生成收费单元
		insert into td_node_code 
		values(substr(v_id,1,7),substr(v_id,1,11),concat(v_id,'10'),concat(var_en_name,'-',var_ex_name),'1','0',null,'166','190818');
		
		# 生成节点关系
		if (v_is_station = true) then
			insert into td_node_relation 
			values(v_id,concat(v_id,'10'),v_miles,'166','190818');
		end if;
		
-- 		if (v_next_id is null) then
-- 		  select var_ex_name;
-- 			select id,name into v_id,v_name from td_basic_station where name=var_ex_name;
-- 			insert into td_node_code 
--  			values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
-- 		end if;
		
		set v_is_station = false;
		# 根据出口生成收费站
		select count(*) into v_count2 from td_basic_station where name=var_ex_name;
		if (v_count2 > 0) then
			select id,name into v_id,v_name from td_basic_station where name=var_ex_name;
			select count(*) into v_count from td_node_code where road_node_name=var_ex_name;
			if (v_count < 1) then
				insert into td_node_code 
				values(substr(v_id,1,7),substr(v_id,1,11),v_id,v_name,'3','0',null,'166','190818');
			end if;
			set v_is_station = true;
		end if;
		
		# 生成节点关系
		if (v_is_station = true) then
			insert into td_node_relation 
			values(concat(v_id,'10'),v_id,v_miles,'166','190818');
		end if;
		
    FETCH cur INTO var_en_name,var_ex_name,v_next_id,v_miles;
  end;
  end while;
  close cur;
END

转载于:https://my.oschina.net/u/2464371/blog/3097900