编了一个临时的存储过程,需要Continue和break的功能,oralcle10g还没有这功能,但是可用goto实现
create or replace procedure P_SetRoutTypByRoutEng(p_message in out varchar2) is
-- author: Cinyun Qiu
-- purpose: 航线类型为空,根据航线中的航站的类型添加航线类型
-- date: 2008-05-07
-- comment:只判断了航线包含5个航站的情况,因为目前发现正式环境航线最长的也只包含5个航站
-- update: 2008-05-08 增加航线为空,航段不为空,根据航段设置航线类型。航段只考虑包含2个航站
v_airport_type cms_airport.airport_type%type;
cursor flight_route_cursor is
select t.flight_id, t.route_eng, t.route_type, t.leg
from cms_receipt_flight t;
tmp_flight_route_cursor flight_route_cursor%rowtype;
begin
open flight_route_cursor;
loop
《start_loop》--要英文的两个尖括号
fetch flight_route_cursor
into tmp_flight_route_cursor;
if flight_route_cursor%notfound then
close flight_route_cursor;
exit;
end if;
if tmp_flight_route_cursor.route_eng is null then
-------------------------------------------------------------
-- 航线为空,根据航站判断
if tmp_flight_route_cursor.leg is not null then
p_message := tmp_flight_route_cursor.leg || '__leg' ||
substr(tmp_flight_route_cursor.leg, 1, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code = substr(tmp_flight_route_cursor.leg, 1, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
p_message := tmp_flight_route_cursor.leg || '__leg' ||
substr(tmp_flight_route_cursor.leg, 5, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code = substr(tmp_flight_route_cursor.leg, 5, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
end if;
end if;
end if;
goto start_loop;
------------------------------------------------------------
end if;
if tmp_flight_route_cursor.route_type is not null then
goto start_loop;
end if;
if substr(tmp_flight_route_cursor.route_eng, 1, 3) = 'FHB'
then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
p_message := tmp_flight_route_cursor.route_eng || '__' ||
substr(tmp_flight_route_cursor.route_eng, 1, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code = substr(tmp_flight_route_cursor.route_eng, 1, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
p_message := tmp_flight_route_cursor.route_eng || '__' ||
substr(tmp_flight_route_cursor.route_eng, 5, 3);
if substr(tmp_flight_route_cursor.route_eng, 5, 3) = 'PET' then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
if substr(tmp_flight_route_cursor.route_eng, 5, 3) = 'BAK' then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code =
substr(tmp_flight_route_cursor.route_eng, 5, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
if length(tmp_flight_route_cursor.route_eng) = 7 then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
p_message := tmp_flight_route_cursor.route_eng || '__' ||
substr(tmp_flight_route_cursor.route_eng, 9, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code =
substr(tmp_flight_route_cursor.route_eng, 9, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
if length(tmp_flight_route_cursor.route_eng) = 11 then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
p_message := tmp_flight_route_cursor.route_eng || '__' ||
substr(tmp_flight_route_cursor.route_eng, 13, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code =
substr(tmp_flight_route_cursor.route_eng, 13, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
if length(tmp_flight_route_cursor.route_eng) = 15 then
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
goto start_loop;
end if;
p_message := tmp_flight_route_cursor.route_eng || '__' ||
substr(tmp_flight_route_cursor.route_eng, 17, 3);
select t.airport_type
into v_airport_type
from cms_airport t
where t.airport_code =
substr(tmp_flight_route_cursor.route_eng, 17, 3);
if v_airport_type = 'INT' then
update cms_receipt_flight f
set f.route_type = 'INT'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
elsif v_airport_type = 'REG' then
update cms_receipt_flight f
set f.route_type = 'REG'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
else
update cms_receipt_flight f
set f.route_type = 'DOM'
where f.flight_id = tmp_flight_route_cursor.flight_id;
commit;
end if;
end if;
end if;
end if;
end if;
end loop;
commit;
p_message := 'TRUE';
exception
when others then
rollback;
p_message := p_message || ';' || ' And error code is ' ||
to_char(SQLCODE) || ';' || substr(SQLERRM, 1, 130);
end P_SetRoutTypByRoutEng;