想写一个存储过程,加入调用的时候用call p1('2008-11-01');
希望存储过程,按照2008-11-01这个变量得到将表visit200811
从visit200811这个表中得到访问数据,然后插入2008-11-01和访问数据插入到bymonth中,
请高手看看,该如何写?CREATE PROCEDURE `p1`(in a varchar(100))
begin
declare cname varchar(40);
declare csql varchar(255);
declare stmt1 varchar(255);
declare num1 int;
set cname=concat("visit",substring(a,1,4),substring(a,6,2));
set @csql =concat("select count(*) into num1 from ",cname);
PREPARE stmt1 FROM @csql;
EXECUTE stmt1;
insert into bymonth(StatDate,HitNum)value(a,num1);
end;
希望存储过程,按照2008-11-01这个变量得到将表visit200811
从visit200811这个表中得到访问数据,然后插入2008-11-01和访问数据插入到bymonth中,
请高手看看,该如何写?CREATE PROCEDURE `p1`(in a varchar(100))
begin
declare cname varchar(40);
declare csql varchar(255);
declare stmt1 varchar(255);
declare num1 int;
set cname=concat("visit",substring(a,1,4),substring(a,6,2));
set @csql =concat("select count(*) into num1 from ",cname);
PREPARE stmt1 FROM @csql;
EXECUTE stmt1;
insert into bymonth(StatDate,HitNum)value(a,num1);
end;
上传到www.access911.net/csdn
以供测试
begin
declare cname varchar(40);
declare csql varchar(255);
declare stmt1 varchar(255);
declare num1 int;
#set cname=concat("visit",substring(a,1,4),substring(a,6,2));
#set @csql =concat("select count(*) into num1 from ",cname); #应该是在这个地方有问题,但不知道如何修改?
#PREPARE stmt1 FROM @csql;
#EXECUTE stmt1;
#上面引号的功能不能实现下面的结果
select count(*)into num1 from visit200811 insert into bymonth(StatDate,HitNum)value(a,num1);
end;
:http://access911.net/csdn/FileDescription.asp?mdb=2008-12-18&id=34
bymonth数据是空的,目前没有数据。
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`p1`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(in a varchar(200))
begin
declare cname varchar(40);
declare csql varchar(255);
declare stmt1 varchar(255);
set cname=concat("visit",substring(a,1,4),substring(a,6,2));
select cname;
set @csql =concat("select count(*) into @num1 from ",cname);
select @csql;
PREPARE stmt1 FROM @csql;
EXECUTE stmt1;
select @num1;
insert into bymonth(StatDate,HitNum) values(cast(a as date),@num1);
END$$DELIMITER ;