刚学习PL SQL,现在要实现这样的功能,就是把一个表中的一个字段的数据同步到另一个表中的一个字段中,不谈这个问题是否有意义,只谈技术问题把address表
persoid addressname
1 test1
1 test2
1 test3
2 test4
3 test5
3 test6
4 test7
等等更新到person表中
id address
1 更新为'test1,test2,test3,'
2 更新为'test4,'
等等,就是把address多条记录的addressname更新到person表中,我是这样写的,正确吗
create or replace package body PersonPacages is procedure UpdatePerson(message out varchar2) is
personid integer;
cursor cursor_info is
select personid,addressname from address order by personid;
begin
personid :=0;
for c in cursor_info
loop
if personid != c.personid then
update person set address='' where id=c.personid;
end if;
update person set address=address+','+c.addressname+',' where id=c.personid;
personid:=c.personid;
end loop;
commit;
message:='success';
end;
end PersonPacages;
persoid addressname
1 test1
1 test2
1 test3
2 test4
3 test5
3 test6
4 test7
等等更新到person表中
id address
1 更新为'test1,test2,test3,'
2 更新为'test4,'
等等,就是把address多条记录的addressname更新到person表中,我是这样写的,正确吗
create or replace package body PersonPacages is procedure UpdatePerson(message out varchar2) is
personid integer;
cursor cursor_info is
select personid,addressname from address order by personid;
begin
personid :=0;
for c in cursor_info
loop
if personid != c.personid then
update person set address='' where id=c.personid;
end if;
update person set address=address+','+c.addressname+',' where id=c.personid;
personid:=c.personid;
end loop;
commit;
message:='success';
end;
end PersonPacages;
select a.persoid ,wm_concat(a.addressname) addressname
from address a
group by a.persoid
;
究竟怎么写,我写的正确吗,你们说了那么多,又没说明白什么东西
create or replace trigger tri_up before update on address
for each row
begin
update person a set address=(select wm_concat(addressname) from address b where a.id=b.persoid)
where exixts(select 1 from address c where a.id=c.persoid);
end;create table address (persoid number,addressname varchar2(20))insert into address
select 1,'test1' from dual union all
select 1,'test2' from dual union all
select 1,'test3' from dual union all
select 2,'test4' from dual union all
select 3,'test5' from dual union all
select 3,'test6' from dual union all
select 4,'test7' from dual
commitcreate table person (id number,address varchar2(20))create or replace function fun_zf(v_id number) return varchar2
as
str varchar2(100):='';
begin
for i in(select addressname from address where persoid=v_id) loop
str:=str||','||i.addressname;
end loop;
return substr(str,2);end; 1* insert into person select distinct persoid,fun_zf(persoid) from address
SQL> /已创建4行。--更新
update person a set address=(select fun_zf(b.persoid) from address b where a.id=b.persoid group by b.persoid)
where exists(select 1 from address c where a.id=c.persoid)SQL> commit
2 /提交完成。SQL> select * from person
2 / ID ADDRESS
---------- --------------------
4 test7
2 test4
3 test5,test6
1 test1,test2,test3
就把address表中的addressname更新到person表的address中,用,号拼接起来
cursor c_yy is
select t.* from b t ;
vr_xx c_yy%rowtype;
begin
open c_yy;
loop
fetch c_yy into vr_xx;
exit when c_yy%notfound;
if vr_xx.l2 is not null then
update a set a.l2=trim(a.l2)||' '||trim(vr_xx.l2) where a.id=vr_xx.id;
end if;
end loop;
end;