create procedure updateDealAndDealHistroy(
in dealId int
)
begin
declare @id int;
declare @dealName varchar(200);
declare @url varchar(1000);
declare @status int;
declare @regionId varchar(10);
declare @endedTime varchar(50);
declare @insertTime varchar(50);
declare @gpWebId int;
declare @num int;select @id=id,@dealName=dealName,@url=url,@status=status,@regionId=regionId,@endedTime=endedTIme,@insertTime=insertTime,@gpWebId=gpWebId from deal where TIMESTAMPDIFF(minute,endedTime,now())>10 and id=dealId;select @num=count(*) from deal where TIMESTAMPDIFF(minute,endedTime,now())<10 and id=dealId;if @num>0
delete from deal where id=dealId;
insert into dealHistroy values(@id,@dealName,@url,@status,@regionId,@endedTime,@insertTime,@gpWebId);end
在mysql里总是报declare @id int;有错,为什么?
in dealId int
)
begin
declare @id int;
declare @dealName varchar(200);
declare @url varchar(1000);
declare @status int;
declare @regionId varchar(10);
declare @endedTime varchar(50);
declare @insertTime varchar(50);
declare @gpWebId int;
declare @num int;select @id=id,@dealName=dealName,@url=url,@status=status,@regionId=regionId,@endedTime=endedTIme,@insertTime=insertTime,@gpWebId=gpWebId from deal where TIMESTAMPDIFF(minute,endedTime,now())>10 and id=dealId;select @num=count(*) from deal where TIMESTAMPDIFF(minute,endedTime,now())<10 and id=dealId;if @num>0
delete from deal where id=dealId;
insert into dealHistroy values(@id,@dealName,@url,@status,@regionId,@endedTime,@insertTime,@gpWebId);end
在mysql里总是报declare @id int;有错,为什么?
你可以set @id
但是,不能去declare之
in dealId int
)
begin
declare tempId int;
declare templName varchar(200);
declare tempUrl varchar(1000);
declare tempStatus int;
declare tempRegionId varchar(10);
declare tempEndedTime varchar(50);
declare tempInsertTime varchar(50);
declare tempGpWebId int;
declare tempNum int;select tempId=id,templName=dealName,tempUrl=url,tempStatus=status,tempRegionId=regionId,tempEndedTime=endedTIme,tempInsertTime=insertTime,tempGpWebId=gpWebId from deal where TIMESTAMPDIFF(minute,endedTime,now())>10 and id=dealId;select tempNum=count(*) from deal where TIMESTAMPDIFF(minute,endedTime,now())<10 and id=dealId;if tempNum>0
delete from deal where id=dealId;
insert into dealHistroy values(tempId,templName,tempUrl,tempStatus,tempRegionId,tempEndedTime,tempInsertTime,tempGpWebId);
end if;
end
我又从新改了下,还是delete from deal where id=dealId;这边报错
select tempNum=count(*) from deal where TIMESTAMPDIFF(minute,endedTime,now())<10 and id=dealId;
我这句话中也用到了 id=dealId怎么没报错?
DROP PROCEDURE IF EXISTS updateDealAndDealHistroy$$CREATE PROCEDURE updateDealAndDealHistroy(
IN dealId INT
)
BEGIN
DECLARE num INT;SELECT @id1:=`id`,@dealName:=dealName,@url1:=url,@status1:=`status`,@regionId1:=regionId,
@endedTime1:=endedTIme,@insertTime1:=insertTime,@gpWebId1:=gpWebId FROM deal
WHERE TIMESTAMPDIFF(MINUTE,endedTime,NOW())>10 AND id=dealId;SELECT COUNT(*) INTO num FROM deal WHERE TIMESTAMPDIFF(MINUTE,endedTime,NOW())<10 AND id=dealId;IF num>0 THEN
DELETE FROM deal WHERE id=dealId;
INSERT INTO dealHistroy VALUES(id1,dealName1,url1,status1,regionId1,endedTime1,insertTime1,gpWebId1);
END IF;
END;
$$
就少了这个,谢谢大家!