重金求一段SQL,工作难题﹐急﹗

用存储过程来实现，以下是实现步骤SQL> create table aaa(fid varchar2(25) primary key,
  2                   fsn_from varchar2(25),
  3                   fsn_to   varchar2(25));Table created.SQL> insert into aaa values('1','P4MKI-030916D00941','P4MKI-030916D00990');1 row created.SQL> insert into aaa values('2','P4MKI-030916D00100','P4MKI-030916D00500');1 row created.SQL> commit;Commit complete.SQL> select * from aaa;FID                       FSN_FROM                  FSN_TO                      
------------------------- ------------------------- -------------------------   
1                         P4MKI-030916D00941        P4MKI-030916D00990          
2                         P4MKI-030916D00100        P4MKI-030916D00500          SQL> create table bbb(fid varchar2(25),fsn varchar2(25));Table created.SQL> create or replace procedure populate_bbb(p_fid varchar2)
  2  as
  3     v_n_from    number;
  4     v_n_to        number;
  5     v_s_prefix  aaa.FSN_FROM%type;
  6     v_s_fsn      bbb.FSN%type;
  7  begin
  8      for c_aaa in (select * from aaa where fid=p_fid) loop
  9         v_s_prefix:=substr(c_aaa.FSN_FROM,1,13);
 10         v_n_from:=to_number(substr(c_aaa.FSN_FROM,14,5));
 11         v_n_to    :=to_number(substr(c_aaa.FSN_TO,14,5));
 12         for i in v_n_from .. v_n_to loop
 13            v_s_fsn:=v_s_prefix||lpad(to_char(i),5,'0');
 14            insert into bbb values(p_fid,v_s_fsn);
 15         end loop;
 16      end loop;
 17      commit;
 18  end;
 19  /Procedure created.SQL> execute populate_bbb('1');PL/SQL procedure successfully completed.SQL> set pagesize 100
SQL> select * from bbb;
FID                       FSN
------------------------- ------------------
1                         P4MKI-030916D00941
1                         P4MKI-030916D00942
1                         P4MKI-030916D00943
1                         P4MKI-030916D00944
1                         P4MKI-030916D00945
1                         P4MKI-030916D00946
1                         P4MKI-030916D00947
1                         P4MKI-030916D00948
1                         P4MKI-030916D00949
1                         P4MKI-030916D00950
1                         P4MKI-030916D00951
1                         P4MKI-030916D00952
1                         P4MKI-030916D00953
1                         P4MKI-030916D00954
1                         P4MKI-030916D00955
1                         P4MKI-030916D00956
1                         P4MKI-030916D00957
1                         P4MKI-030916D00958
1                         P4MKI-030916D00959
1                         P4MKI-030916D00960
1                         P4MKI-030916D00961
1                         P4MKI-030916D00962
1                         P4MKI-030916D00963
1                         P4MKI-030916D00964
1                         P4MKI-030916D00965
1                         P4MKI-030916D00966
1                         P4MKI-030916D00967
1                         P4MKI-030916D00968
1                         P4MKI-030916D00969
1                         P4MKI-030916D00970
1                         P4MKI-030916D00971
1                         P4MKI-030916D00972
1                         P4MKI-030916D00973
1                         P4MKI-030916D00974
1                         P4MKI-030916D00975
1                         P4MKI-030916D00976
1                         P4MKI-030916D00977
1                         P4MKI-030916D00978
1                         P4MKI-030916D00979
1                         P4MKI-030916D00980
1                         P4MKI-030916D00981
1                         P4MKI-030916D00982
1                         P4MKI-030916D00983
1                         P4MKI-030916D00984
1                         P4MKI-030916D00985
1                         P4MKI-030916D00986
1                         P4MKI-030916D00987
1                         P4MKI-030916D00988
1                         P4MKI-030916D00989
1                         P4MKI-030916D0099050 rows selected.SQL>

解决方案 »

  1.   

    我觉得可以用"序列"一个语句来生成,不过还没有写出代码来.
      

  2.   

    很棒啊。
    不过还要有点改进。如果编码规则不确定的话。
    只截取右边五位是不够的。还要比较开始和结束的编码是从哪里开始不同的。
    将剩余的位数截取出来计算。
      

  3.   

    呵呵，只是针对这个特定应用，抛砖引玉
      

  4.   

    如果编码中变化的部门包含字母呢?
      

  5.   

    如果有包含字母的话。
    就一定要事先确定编码规则了。否则谁知道该怎样处理。
      

  6.   

    计算机 要做的事情是有规则的事情 不会做没有规则的事情 所以 你这个问题 关键是找出来规则如果没有规则 一定处理不好
      

  7.   

    有规则才可做到,说明出怎样到这些编码,总有算法吧