同一个表中插入数据
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的结果。。
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的结果。。
解决方案 »
- 求助:Oracle SQL Developer 字体 Courier New 中文显示不正常
- 这道题帮我写下怎么做,我想了很久
- 紧急求援,Oracle中的逆累积分布函数,或者说累积逆分布函数
- vb6怎么存取blob字段?提示:数据类型不支持
- 数据库用户名密码修改的烦恼
- 能否将从8.1.7EXP的DMP通过9.2.0.1.0客户端IMP进9.2.0.5.0数据库?
- sql里面有没有象vb中的mid函数阿?---在线等待
- 由于数据文件过大,请问如何要在linux上挂载xfs文件系统?
- 大家来看看PL/SQL的小程序有什么问题?
- 在ORACLE中连接和会话有何区别???
- 请问:在oracle的存储过程中,如果获得查询语句的列数和各个列名?
- oracle PL/SQL 怎么截取BLOG字段的值插到一张表里
正解 rownum只是个虚拟字段,结果排序不一样,他的值都是不一样的
顺便问一下,truncate能否只清除表的一部分表空间呢?