oracle数据库goto语句,存储过程，Oracle10g里边的goto语句

编了一个临时的存储过程，需要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;