大家好:
我现在需要一个存储过程,就是把一个表中,同一id的多条数据,插入另外一张表,这个表里有一个字段来区分同一id的多条数据。下面是我写的一个存储过程,以前没写过,网上查了点知识临时编的,编译不通过。请帮忙看看! 谢谢!declare
CURSOR cur IS
select count(*) count, a.id from circuit_port a group by a.id;//查出同样id的数据个数
circuitName VARCHAR2(256);
v_count number; circuitid number;
begin
for cur_result in cur LOOP
v_count := cur_result.count;
IF(v_count>1)THEN //如果大于1,把每条数据插入另一张表,aport字段不会重复,因此用他和id来定位一条数据。
circuitName := cur_result.id;
select aport into cursor_port from circuit_port where id = circuitName;
for v_port in cursor_port LOOP
circuitid:= -1 ;//区分每条数据的字段。
insert into temp_topo select id, circuitid, -1, anename, anename, aport, -1, 0, -2 seq from circuit_port where aport = v_aport and id = circuitName ;
insert into temp_topo select id, circuitid, -1, znename, znename, zport, -1, 0, -1 seq from circuit_port where aport = v_aport and id = circuitName and zport is not null ;
circuitid := circuitid - 1 ;
end LOOP;
END IF;
close cursor_port; end LOOP;
close cur;
end;
我现在需要一个存储过程,就是把一个表中,同一id的多条数据,插入另外一张表,这个表里有一个字段来区分同一id的多条数据。下面是我写的一个存储过程,以前没写过,网上查了点知识临时编的,编译不通过。请帮忙看看! 谢谢!declare
CURSOR cur IS
select count(*) count, a.id from circuit_port a group by a.id;//查出同样id的数据个数
circuitName VARCHAR2(256);
v_count number; circuitid number;
begin
for cur_result in cur LOOP
v_count := cur_result.count;
IF(v_count>1)THEN //如果大于1,把每条数据插入另一张表,aport字段不会重复,因此用他和id来定位一条数据。
circuitName := cur_result.id;
select aport into cursor_port from circuit_port where id = circuitName;
for v_port in cursor_port LOOP
circuitid:= -1 ;//区分每条数据的字段。
insert into temp_topo select id, circuitid, -1, anename, anename, aport, -1, 0, -2 seq from circuit_port where aport = v_aport and id = circuitName ;
insert into temp_topo select id, circuitid, -1, znename, znename, zport, -1, 0, -1 seq from circuit_port where aport = v_aport and id = circuitName and zport is not null ;
circuitid := circuitid - 1 ;
end LOOP;
END IF;
close cursor_port; end LOOP;
close cur;
end;
cursor_port是什么
而且用for循环的话
close cursor_port; 这一句应该去掉
这个也去掉
用for循环的时候
已经隐含了
open
fetch
close
你不要自己再添加
--查出同样id的数据个数
for c1 in (select id from circuit_port group by id having count(*) > 1) loop
-- 区分每条数据的字段
for c2 in (select aport from circuit_port where id = c1.id) loop
insert into temp_topo
select id, circuitid, -1, anename, anename, aport, -1, 0, -2 seq
from circuit_port
where aport = c2.aport
and id = c1.id;
insert into temp_topo
select id, circuitid, -1, znename, znename, zport, -1, 0, -1 seq
from circuit_port
where aport = c2.aport
and id = c1.id
and zport is not null;
end loop;
end loop;
end;
可能是有一个地方我没说清楚,我再说一下需求:
1、从circuit_port表中导数据到temp_topo表。一条Circuit_port数据拆分成2条。
如:id=1的数据,有aport=a,zport=z,把这条数据到到temp_topo表中就是两条数据:
第一条:id=1,Circuitid=-1,port=a,seq=-2
第二条(如果zport不为空):id=1,Circuitid=-1,port=z,seq=-1
2、同一个id的数据在circuit_port中可能有多条数据,对每条数据按1操作,不同的是insert的时候Circuitid要变化一下,如:id=2的数据在circuit_port表中有2条,对第一条insert的时候Circuitid=-1,第二条insert的时候Circuitid=-2,依次递减。
id_num number;
v_id circuit_port.id%type;
curcuitid number;
begin
for cur1 in (select count(1) ct,id
from circuit_port group by id)
loop
id_num:=cur1.ct;
v_id:=cur1.id;
curcuitid:=-1;
if id_num>1 then
for cur2 in (select * from circuit_port
where id=v_id)
loop
insert into temp_topo values
(cur2.id,circuitid,-1,cur2.anename,cur2.anename,cur2.aport,-1,0,-2);
if cur2.zport is not null then
insert into temp_topo values
(cur2.id,circuitid,-1,cur2.znename,cur2.znename,cur2.zport,-1,0,-1);
end if;
curcuitid:=curcuitid-1;
end loop;
end if;
end loop;
end;
报了个列在此处不允许?在insert的时候。
表结构如下:create table TEMP_TOPO
(
CODE VARCHAR2(255),
CIRCUITID NUMBER,
NEID NUMBER,
NAME VARCHAR2(255),
NAMEINEMS VARCHAR2(255),
PORTNAME VARCHAR2(255),
PORTID NUMBER,
PORTTYPE NUMBER,
SEQ NUMBER
)
把values后面的值的circuitid改成curcuitid