我的这个出发器是从 mssql的出发器改过来的;现在要用mysql5.1 改的语句为:
-- test
create trigger myTrigger after insert
on myTable
for each row
begin
select a, b into va,vb from myTable where name = 'ipAddress';
label:begin
if (a) then
begin
if (b) then
begin
if not exists(x = 1) then
insert into NeiEntityAddressTable values(va,vb);
end if;
end;
else
begin
if not exists(x = 2) then
insert into myTable values(va,vb);
goto label;
end;
end if;
end;
end if;
end;
end;
现在总是提示我 goto label;这里出错,请帮我看下哪里的问题?
有了正确答案就给分
-- test
create trigger myTrigger after insert
on myTable
for each row
begin
select a, b into va,vb from myTable where name = 'ipAddress';
label:begin
if (a) then
begin
if (b) then
begin
if not exists(x = 1) then
insert into NeiEntityAddressTable values(va,vb);
end if;
end;
else
begin
if not exists(x = 2) then
insert into myTable values(va,vb);
goto label;
end;
end if;
end;
end if;
end;
end;
现在总是提示我 goto label;这里出错,请帮我看下哪里的问题?
有了正确答案就给分
create procedure sp_testttn()
label:begin
create TEMPORARY table tmpn(name varchar(255));
insert into tmpn(name) select usernames from users;
select * from tmpn;
drop table tmpn;
end label;你的end label在什么地方?
create trigger myTrigger after insert
on myTable
for each row
begin
select a, b into va,vb from myTable where name = 'ipAddress';
label:begin
if (a) then
begin
if (b) then
begin
if not exists(x = 1) then
insert into NeiEntityAddressTable values(va,vb);
end if;
end;
else
begin
if not exists(x = 2) then
insert into myTable values(va,vb);
goto label;
end;
end if;
end;
end if;
end label;
end;
还是报错;就是 goto label;这句报错;请看下原因何在,如何改?
on myTable
for each row
begin
select a, b into va,vb from myTable where name = 'ipAddress';
label zz:begin
if (a) then
begin
if (b) then
begin
if not exists(x = 1) then
insert into NeiEntityAddressTable values(va,vb);
end if;
end;
else
begin
if not exists(x = 2) then
insert into myTable values(va,vb);
goto zz;
end;
end if;
end;
end if;
end label;
end;
MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句,而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容,这个语句会慢被淘汰,所以我们在MySQL参考手册中没有提及。
用循环吧,WHILE、REPATE等等
再帮我看看,谢谢!
2、用WHILE、REPEAT代替;
3、动手做一下,具体的问题再问。
create trigger myTrigger after insert
on myTable
for each row
begin
select a, b into va,vb from myTable where name = 'ipAddress';
while (a) do
-- label:begin
-- if (a) then
begin
if (b) then
begin
if not exists(x = 1) then
insert into NeiEntityAddressTable values(va,vb);
end if;
end;
else
begin
if not exists(x = 2) then
insert into myTable values(va,vb);
-- goto label;
end;
end if;
end;
-- end if;
-- end label;
end while;
end;
if not exists(x = 2)
有问题,要判断什么?
for insert as
declare @entityId numeric(10)
declare @ips VARCHAR(200)
declare @ipadr varchar(30)
declare @idx int
declare @tmp varchar(500)
declare @ins numeric(10)begin
select @entityId = entityid,@ips = ltrim(RTRIM(value)) from inserted where name = 'ipAddress'
label:
if charindex('.', @ips)>1
begin
if charindex(',',@ips)=0
begin
if not exists(select * from NeiEntityAddressTable where entityid = @entityid and
ipaddress = @ips)
insert into NeiEntityAddressTable values(@entityId,@ips)
end
else
begin
select @idx = charindex(',',@ips)
select @ipadr = substring(@ips,0,@idx)
if not exists(select * from NeiEntityAddressTable where entityid = @entityid and
ipaddress = @ipadr)
insert into NeiEntityAddressTable
values(@entityId,@ipadr)
select @ips = substring(@ips,@idx+1,500)
goto label
end
end
end;
要改为mysql5.1中的存储过程;这个我改了好久没有成功;时间紧张;请看看关键的地方怎么改!谢谢!!!
charindex->INSTR
WHILE INSTR(@IPS,',')>1
ipaddress = @ips) THENif not exists(select * from NeiEntityAddressTable where entityid = @entityid and
ipaddress = @ipadr) THEN语句后面加分号试试,有问题再问
create trigger NeiEntityAttributeAddrTrg after insert
on NeiEntityAttributeTable
for each row
begin
declare entityId_ numeric(10);
declare ips_ VARCHAR(200);
declare ipadr_ varchar(30);
declare idx_ int;
declare tmp_ varchar(500);
declare ins_ numeric(10);
select entityid, ltrim(RTRIM(value)) into entityId_,ips_ from NeiEntityAttributeTable where name = 'ipAddress';
WHILE (charindex('.', ips_)>1) DO
begin
if (charindex(',',ips_)=0) then
begin
if not exists(select * from NeiEntityAddressTable where entityid = entityid_ and ipaddress = ips_) then
insert into NeiEntityAddressTable values(entityId_,ips_);
end if;
end;
else
begin
SET idx_ = charindex(',',ips_);
SET ipadr_ = substring(ips_,0,idx_);
SET ips_ = substring(ips_,idx_+1,500);
if not exists(select * from NeiEntityAddressTable where entityid = entityid_ and ipaddress = ipadr_) then
insert into NeiEntityAddressTable values(entityId_,ipadr_);
end if;
end;
end if;
end;
end WHILE;
end;
mysql中charindex函数?
加``,如 `keyword`2,函数不能返回结果集;怎么处理,
用SP OR 将结果集插入临时表
用`反引号来标识 `keyword`2,函数不能返回结果集;怎么处理,
一般是将结果放到临时表,但大部分直接在程序中处理。