drop procedure if exists test;
create procedure test(IN MGR varchar(10),IN ACCESST DATETIME)
begin
declare no_more_record INT DEFAULT 0;
DECLARE dn varchar(255);
declare kw varchar(255);
declare url varchar(255);
declare cur_record CURSOR FOR SELECT domain FROM tbl_siteinfo where manager=MGR;
declare CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;
drop table if exists tmp_table;
CREATE TEMPORARY TABLE tmp_table(
keyword varchar(255),
uri varchar(255),
se varchar(255),
ip varchar(255),
region varchar(255),
accesstime datetime
);
truncate tmp_table;
OPEN cur_record;
FETCH cur_record INTO dn;
WHILE no_more_record != 1 DO
insert into tmp_table(keyword,uri,se,ip,region,accesstime) select keyword,uri,se,ip,region,accesstime from tbl_keywordaccess where uri like concat('%',dn,'%') and accesstime >= ACCESST;
FETCH cur_record INTO dn;
END WHILE;
select * from tmp_table;
CLOSE cur_record;
end
create procedure test(IN MGR varchar(10),IN ACCESST DATETIME)
begin
declare no_more_record INT DEFAULT 0;
DECLARE dn varchar(255);
declare kw varchar(255);
declare url varchar(255);
declare cur_record CURSOR FOR SELECT domain FROM tbl_siteinfo where manager=MGR;
declare CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;
drop table if exists tmp_table;
CREATE TEMPORARY TABLE tmp_table(
keyword varchar(255),
uri varchar(255),
se varchar(255),
ip varchar(255),
region varchar(255),
accesstime datetime
);
truncate tmp_table;
OPEN cur_record;
FETCH cur_record INTO dn;
WHILE no_more_record != 1 DO
insert into tmp_table(keyword,uri,se,ip,region,accesstime) select keyword,uri,se,ip,region,accesstime from tbl_keywordaccess where uri like concat('%',dn,'%') and accesstime >= ACCESST;
FETCH cur_record INTO dn;
END WHILE;
select * from tmp_table;
CLOSE cur_record;
end