存储过程，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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/425856/viewspace-708054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/425856/viewspace-708054/