下面一段sql server的存储过程,想改为mysql的存储过程写法,看了半天的mysql5的手册,也不得要领,请各位帮帮忙,提点一下,谢谢!
create procedure delCustomer
@customerID smallint
as
DECLARE @i int , @Did int
select @i=count(id) from domain where customerID=@customerID
set @j=0
while @j<=@i begin
select @Did=id from domain where customerID=@customerID
delete from web where domainID=@Did
delete from email where domainID=@Did
set @j=@j+1
end
delete from customer where id=@customerID
go
create procedure delCustomer
@customerID smallint
as
DECLARE @i int , @Did int
select @i=count(id) from domain where customerID=@customerID
set @j=0
while @j<=@i begin
select @Did=id from domain where customerID=@customerID
delete from web where domainID=@Did
delete from email where domainID=@Did
set @j=@j+1
end
delete from customer where id=@customerID
go
BEGIN
select count(id) into @i from domain where customerID=mycustomerID ;
set @j=0 ;
while @j<=@i do
select id into @Did from domain where customerID=mycustomerID ;
delete from web where domainID=@Did;
delete from email where domainID=@Did;
set @j=@j+1 ;
end while;
delete from customer where id=mycustomerID ;
END;楼主可以试验一下
读了,如果直接从控制台上执行,可能还要:
delimiter //
CREATE PROCEDURE `test001`(mycustomerID INTEGER(11))
BEGIN
select count(id) into @i from domain where customerID=mycustomerID ;
set @j=0 ;
while @j<=@i do
select id into @Did from domain where customerID=mycustomerID ;
delete from web where domainID=@Did;
delete from email where domainID=@Did;
set @j=@j+1 ;
end while;
delete from customer where id=mycustomerID ;
END //
delimiter ;
改为:
CREATE PROCEDURE `delCustomer`(mycustomerID INTEGER(11))我把smallint改为 mycustomerID 楼主不介意吧。
smallint的取值范围比较小,所以还是改为int吧。