关于插入顺序问题

[playumen]

同一个表中插入数据
select rownum,mb_id,gp_id,gi_id,gi_name,gi_describe from dps_groupitem where mb_id = 3 and gp_id = 25;
rownum mb_id gp_id gi_id gi_name gi_describe
1 1 3 25 01 省公司 col_1_1_3_7 = '4400'
2 2 3 25 02 广州 col_1_1_3_7 = '4401'
3 3 3 25 03 深圳 col_1_1_3_7 = '4402'
4 4 3 25 04 珠海 col_1_1_3_7 = '4403'
5 5 3 25 05 汕头 col_1_1_3_7 = '4404'
6 6 3 25 06 韶关 col_1_1_3_7 = '4405'
7 7 3 25 07 河源 col_1_1_3_7 = '4406'
8 8 3 25 08 梅州 col_1_1_3_7 = '4407'
9 9 3 25 09 惠州 col_1_1_3_7 = '4408'
10 10 3 25 10 汕尾 col_1_1_3_7 = '4409'
通过pro插入数据。
pro如下：

create or replace procedure COPYGROUP1
(CopyMB_Name in varchar2,CopyGP_Name in varchar2,ToMB_Name in varchar2,a integer)
is
 CopyGPID varchar2(200);
 CopyMBID varchar2(200);
 CopyColumn varchar2(500);
 Copygpp_propid varchar(50);
 ToMBID varchar2(200);
 ToPropID varchar2(200);
 ToColumn varchar2(200);
 ToGPID varchar2(50);
 Toprop_name varchar(2000);
 d_qty integer;
 d_qty1 integer;
 Togiid varchar(50);
 Toginame varchar(500);
 Togides varchar(2000);

 cursor cursor_gpp_propid is select gpp_propid from dps_groupproperty where mb_id = CopyMBID and gp_id = CopyGPID;
 cursor cursor_mb_id is select mb_id from dps_mainbody where mb_id <> CopyMBID and mb_id in (select mb_id from dps_group where gp_name =CopyGP_Name);
 cursor cursor_gi_id is select gi_id,gi_name,gi_describe from dps_groupitem where mb_id = CopyMBID and gp_id = CopyGPID order by gi_id;

begin
--查询要复制的对象ID
select mb_id into CopyMBID from dps_mainbody where mb_name = CopyMB_Name;
--查询要复制的分组ID
select gp_id into CopyGPID from dps_group where mb_id = CopyMBID and gp_name = CopyGP_Name;
--一个对象插入一个分组
if a = 1 then
  --查询复制到的对象ID
  select mb_id into ToMBID from dps_mainbody where mb_name = ToMB_Name;
  --判断复制到的分组ID是否存在,并查出
  select count(gp_id) into d_qty from dps_group where mb_id = ToMBID and gp_name = CopyGP_Name;
  if d_qty = 0 then
    select max(gp_id)+1 into ToGPID from dps_group;
    insert into dps_group values(1,1,ToMBID,ToGPID,CopyGP_Name,'系统管理员',sysdate,'系统管理员',sysdate);
    commit;
  end if;
  select gp_id into ToGPID from dps_group where mb_id = ToMBID and gp_name = CopyGP_Name;
  delete from dps_groupitem where mb_id = ToMBID and gp_id = ToGPID;
  open cursor_gi_id;
  loop
    fetch cursor_gi_id into Togiid,Toginame,Togides;
    exit when cursor_gi_id%notfound;
    insert into dps_groupitem values(1,1,ToMBID,ToGPID,Togiid,Toginame,Togides,'系统管理员',sysdate,'系统管理员',sysdate);
    commit;
  end loop;
  close cursor_gi_id;
  /*insert into dps_groupitem
        select 1,1,ToMBID,ToGPID,GI_ID,gi_name,gi_describe,gi_creator,sysdate,gi_creator,sysdate from dps_groupitem
        where mb_id = CopyMBID and gp_id = CopyGPID;
        commit;*/
  --查询复制的条件字段
  open cursor_gpp_propid;
  loop
    fetch cursor_gpp_propid into Copygpp_propid;
    exit when cursor_gpp_propid%notfound;
    select prop_name,prop_columnname into Toprop_name,CopyColumn from dps_property where mb_id = CopyMBID and prop_id = Copygpp_propid;
    select prop_columnname,prop_id into ToColumn,ToPropID from dps_property where mb_id = ToMBID and prop_name = Toprop_name;
    select count(*) qty into d_qty1 from dps_groupproperty where mb_id = ToMBID and gp_id = ToGPID and gpp_propid = ToPropID;
    if d_qty1 = 0 then
       insert into dps_groupproperty values(1,1,ToMBID,ToGPID,ToPropID);
       commit;
    end if;
    update dps_groupitem set gi_describe = replace(gi_describe,CopyColumn,ToColumn) where mb_id = ToMBID and gp_id = ToGPID;
    commit;
  end loop;
  close cursor_gpp_propid;
end if;
--所有对象插入一个分组
if a = 2 then
  open cursor_mb_id;
  loop
    fetch cursor_mb_id into ToMBID;
    exit when cursor_mb_id%notfound;
    select count(gp_id) into d_qty from dps_group where mb_id = ToMBID and gp_name = CopyGP_Name;
    if d_qty = 0 then
       select max(gp_id)+1 into ToGPID from dps_group;
       insert into dps_group values(1,1,ToMBID,ToGPID,CopyGP_Name,'系统管理员',sysdate,'系统管理员',sysdate);
       commit;
       end if;
       select gp_id into ToGPID from dps_group where mb_id = ToMBID and gp_name = CopyGP_Name;
       delete from dps_groupitem where mb_id = ToMBID and gp_id = ToGPID;
       open cursor_gi_id;
       loop
            fetch cursor_gi_id into Togiid,Toginame,Togides;
            exit when cursor_gi_id%notfound;
            insert into dps_groupitem values(1,1,ToMBID,ToGPID,Togiid,Toginame,Togides,'系统管理员',sysdate,'系统管理员',sysdate);
            commit;
       end loop;
       close cursor_gi_id;
       /*insert into dps_groupitem
        select 1,1,ToMBID,ToGPID,GI_ID,gi_name,gi_describe,gi_creator,sysdate,gi_creator,sysdate from dps_groupitem
        where mb_id = CopyMBID and gp_id = CopyGPID;
        commit;*/
       --查询复制的条件字段
       open cursor_gpp_propid;
       loop
            fetch cursor_gpp_propid into Copygpp_propid;
            exit when cursor_gpp_propid%notfound;
            select prop_name,prop_columnname into Toprop_name,CopyColumn from dps_property where mb_id = CopyMBID and prop_id = Copygpp_propid;
            select prop_columnname,prop_id into ToColumn,ToPropID from dps_property where mb_id = ToMBID and prop_name = Toprop_name;
            select count(*) qty into d_qty1 from dps_groupproperty where mb_id = ToMBID and gp_id = ToGPID and gpp_propid = ToPropID;
            if d_qty1 = 0 then
              insert into dps_groupproperty values(1,1,ToMBID,ToGPID,ToPropID);
              commit;
            end if;
            update dps_groupitem set gi_describe = replace(gi_describe,CopyColumn,ToColumn) where mb_id = ToMBID and gp_id = ToGPID;
            commit;
       end loop;
       close cursor_gpp_propid;
   end loop;
   close cursor_mb_id;
end if;
end;

执行
exec COPYGROUP1 （成本中心,公司,公司,1）;
但得出的结果确是：
select rownum,mb_id,gp_id,gi_id,gi_name,gi_describe from dps_groupitem where mb_id = 1 and gp_id = 30;
rownum mb_id gp_id gi_id gi_name gi_describe
1 1 1 30 03 深圳 col_1_1_1_3 = '4402'
2 2 1 30 01 省公司 col_1_1_1_3 = '4400'
3 3 1 30 02 广州 col_1_1_1_3 = '4401'
4 4 1 30 04 珠海 col_1_1_1_3 = '4403'
5 5 1 30 05 汕头 col_1_1_1_3 = '4404'
6 6 1 30 06 韶关 col_1_1_1_3 = '4405'
7 7 1 30 07 河源 col_1_1_1_3 = '4406'
8 8 1 30 08 梅州 col_1_1_1_3 = '4407'
9 9 1 30 09 惠州 col_1_1_1_3 = '4408'
10 10 1 30 10 汕尾 col_1_1_1_3 = '4409'
11 11 1 30 11 东莞 col_1_1_1_3 = '4410'
12 12 1 30 12 中山 col_1_1_1_3 = '4411'
13 13 1 30 13 江门 col_1_1_1_3 = '4412'

关键问题来了，为什么我按顺序插入的，但rownum会不一样呢，比如“省公司”是最先插入的，刚开始rownum是1，但后面的插入使rownum发生了变化。。什么问题，怎么样解决？
因为程序没排序，已无法修改，无排序的情况就是用rownum进行排序的，。。主要想实现表1的结果。。

[真的不知道取什么名]

是不是在其他字段上建了索引？

[Phoenix_99]

rownum 是一个可变量，它随着你的查询结果变化而变化

[hlx198654]

[Quote=引用 2 楼 phoenix_99 的回复:]

rownum 是一个可变量，它随着你的查询结果变化而变化
[/Quote]

正解 rownum只是个虚拟字段，结果排序不一样，他的值都是不一样的

[关山路遥]

来学习 一下！~

[minitoy]

原因rownum默认是按rowid排序的，你的表有增删改操作。有可能新加的数据使用的是原来数据删除后留下的空间，这样导致了数据乱序排列。

[gdd714]

rownum不是真实的，要看rowid

[gdd714]

rownum不是真实的，要看rowid

[playumen]

rowid所对应的物理块是固定的么，如：AAAORFAAFAADkzoAAL 每次按不同条件查询时，会不会变换？

[playumen]

如果我删除一条记录，插入下一条记录，那么下一条记录会不会占用原记录的rowid，
顺便问一下，truncate能否只清除表的一部分表空间呢？

[minitoy]

占用不占用以前的rowid，要看ptfree和ptuse。[Quote=引用 9 楼 playumen 的回复:]
如果我删除一条记录，插入下一条记录，那么下一条记录会不会占用原记录的rowid，
顺便问一下，truncate能否只清除表的一部分表空间呢？
[/Quote]

[minitoy]

rowid对应的块是固定的。你可以看看rowid的组成就晓得了。[Quote=引用 8 楼 playumen 的回复:]
rowid所对应的物理块是固定的么，如：AAAORFAAFAADkzoAAL 每次按不同条件查询时，会不会变换？
[/Quote]