create table xx(
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;create table yy(
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;
create procedure a()
BEGIN
declare a1,a2 int(10) default '0';
declare b1,b2 varchar(128) default '';
declare done1,done2 int(1) default 0;
declare cur1 CURSOR for select a,b from xx;
declare continue handler for not found set done1=1;
open cur1;
xxxx:loop
fetch cur1 into a1,b1;
if done1 =1 THEN
leave xxxx;
end if;BEGINdeclare cur2 cursor for select a,b from yy where b=b1;
declare continue handler for not found set done2=1;
open cur2;
fetch cur2 into a2,b2;
if a2 is null THEN
insert into yy values('',b1);
end if;
yyyy:loop
需求:
xx,yy 2个表中,把xx中数据,插入到yy中。如果b在,xx,yy都存在,则不插入yy中如果xx中的b在yy中的b中不存在,那么插入yy中。
问题if a2 >0 THEN
set a2=null;
else
insert into yy values('',b1);
end if;这样写,没问题。但是有点累赘。
我修改成这样
if a2 is nou null THENinsert into yy values('',b1);
end if;这样就出问题了,一条数据都插不进去。请问是为什么呢
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;create table yy(
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;
create procedure a()
BEGIN
declare a1,a2 int(10) default '0';
declare b1,b2 varchar(128) default '';
declare done1,done2 int(1) default 0;
declare cur1 CURSOR for select a,b from xx;
declare continue handler for not found set done1=1;
open cur1;
xxxx:loop
fetch cur1 into a1,b1;
if done1 =1 THEN
leave xxxx;
end if;BEGINdeclare cur2 cursor for select a,b from yy where b=b1;
declare continue handler for not found set done2=1;
open cur2;
fetch cur2 into a2,b2;
if a2 is null THEN
insert into yy values('',b1);
end if;
yyyy:loop
需求:
xx,yy 2个表中,把xx中数据,插入到yy中。如果b在,xx,yy都存在,则不插入yy中如果xx中的b在yy中的b中不存在,那么插入yy中。
问题if a2 >0 THEN
set a2=null;
else
insert into yy values('',b1);
end if;这样写,没问题。但是有点累赘。
我修改成这样
if a2 is nou null THENinsert into yy values('',b1);
end if;这样就出问题了,一条数据都插不进去。请问是为什么呢
create table xx(
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;create table yy(
a int(10) UNSIGNED not null primary key auto_increment,
b varchar(128) not null default ''
) engine=myisam default charset=utf8;
create procedure a()
BEGIN
declare a1,a2 int(10) default '0';
declare b1,b2 varchar(128) default '';
declare done1,done2 int(1) default 0;
declare cur1 CURSOR for select a,b from xx;
declare continue handler for not found set done1=1;
open cur1;
xxxx:loop
fetch cur1 into a1,b1;
if done1 =1 THEN
leave xxxx;
end if;BEGINdeclare cur2 cursor for select a,b from yy where b=b1;
declare continue handler for not found set done2=1;
open cur2;
fetch cur2 into a2,b2;
if a2 is null THEN
insert into yy values('',b1);
end if;
yyyy:loopfetch cur2 into a2,b2;
if done2=1 THEN
leave yyyy;
end if;end loop yyyy;
close cur2;
set done2=0;
end;end loop xxxx;
close cur1;
end;完整的是这样。老大,在循环里面,也可以声明游标的。哪个没问题,能编译通过。要写在begin里面