below is my mysql(MariaDB) server stored procedure code
# call `pkrss_get_rssitem_vo`(2, 0, 0, 0, 1, 0, 0, 0);
# flag = 1: select items
# flag =-1: select items count
# flag = 2: select items with image mode
# flag =-2: select items with image mode count
# flag = 3: get top 1 item with each table
# flag = 4: get top n item with each table
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
DECLARE s VARCHAR(512);
DECLARE s2 VARCHAR(512);
DECLARE t VARCHAR(128);
DECLARE selImgMode INT DEFAULT 0;
DECLARE selCountMode INT DEFAULT 0;
IF flag = -1 THEN
SET selCountMode = 1;
END IF;
IF flag = -2 THEN
SET selCountMode = 1;
SET selImgMode = 1;
END IF;
IF flag = 2 THEN
SET selImgMode = 1;
END IF;
SET s = '';
IF selCountMode = 1 THEN
SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
IF tid != 0 THEN
SET s = CONCAT(s,' AND a.tid=', tid);
END IF;
IF cid != 0 THEN
SET s = CONCAT(s,' AND a.cid=', cid);
END IF;
IF selImgMode = 1 THEN
SET s = CONCAT(s,' AND a.item_imgs is not null');
END IF;
SET @t2 = s;
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
SET s = CONCAT(s,'SELECT a.*');
IF tid = 0 THEN
SET s = CONCAT(s,',t.title AS table_name');
END IF;
IF cid = 0 THEN
SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
END IF;
SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
IF tid = 0 THEN
SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
END IF;
IF cid = 0 THEN
SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
END IF;
SET s = CONCAT(s,' WHERE 1=1');
IF selImgMode = 1 THEN
SET s = CONCAT(s,' AND a.item_imgs is not null');
END IF;
IF tid != 0 THEN
SET s = CONCAT(s,' AND a.tid=', tid);
END IF;
IF cid != 0 THEN
SET s = CONCAT(s,' AND a.cid=', cid);
END IF;
IF flag = 3 THEN
# get top 1 item with each table
SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
# select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
ELSEIF flag = 4 THEN
# get top 3 records with each table
SET s = CONCAT('SET @num := 0, @tid := 0;', s);
SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
# set @num := 0, @tid := 0;
# select b.* from (
# select a.*,
# @num := if(@tid=tid, @num + 1, 1) as row_number,
# @tid := a.tid as other_tid
# from pkrss_rssitem_l2 as a
# order by a.tid,a.id desc
# ) as b
# where b.row_number <= 3 order by b.tid,b.id desc limit 20;
END IF;
SET s = CONCAT(s,' ORDER BY');
IF idOrderBy != 0 THEN
IF idOrderBy > 0 THEN
SET s = CONCAT(s,' a.id ASC');
ELSE
SET s = CONCAT(s,' a.id DESC');
END IF;
ELSE
IF timeOrderBy > 0 THEN
SET s = CONCAT(s,' a.item_pubdate ASC');
ELSE
SET s = CONCAT(s,' a.item_pubdate DESC');
END IF;
END IF;
SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
SET t = CONCAT('_tmp_',UUID());
SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
`tid` int(11),
`cid` int(11),
`item_pubdate` datetime,
`item_title` varchar(127) COLLATE utf8_bin,
`item_desc` varchar(246) COLLATE utf8_bin,
`item_link` varchar(246) COLLATE ascii_bin,
`item_authors` varchar(64) COLLATE utf8_bin,
`item_imgs` text COLLATE utf8_bin,
`table_name` varchar(127) COLLATE utf8_bin,
`catalog_name` varchar(64) COLLATE utf8_bin,
`catalog_cls` varchar(32) COLLATE utf8_bin
);');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
IF tid = 0 THEN
SET s2 = CONCAT(s2,',`table_name`');
END IF;
IF cid = 0 THEN
SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
END IF;
SET @t2 = CONCAT(s2,') ', s, ';');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set @t2 = CONCAT('SELECT * FROM `',t,'`');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;;
DELIMITER ;
And mysql return below error message
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
DECLARE s VARCHAR(512)
MySQL 返回： 文档
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3
How to fixed
add "DELIMITER ;;" to front and end rows:
DELIMITER ;;
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
DECLARE s VARCHAR(512);
DECLARE s2 VARCHAR(512);
DECLARE t VARCHAR(128);
DECLARE selImgMode INT DEFAULT 0;
DECLARE selCountMode INT DEFAULT 0;
IF flag = -1 THEN
SET selCountMode = 1;
END IF;
IF flag = -2 THEN
SET selCountMode = 1;
SET selImgMode = 1;
END IF;
IF flag = 2 THEN
SET selImgMode = 1;
END IF;
SET s = '';
IF selCountMode = 1 THEN
SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
IF tid != 0 THEN
SET s = CONCAT(s,' AND a.tid=', tid);
END IF;
IF cid != 0 THEN
SET s = CONCAT(s,' AND a.cid=', cid);
END IF;
IF selImgMode = 1 THEN
SET s = CONCAT(s,' AND a.item_imgs is not null');
END IF;
SET @t2 = s;
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
SET s = CONCAT(s,'SELECT a.*');
IF tid = 0 THEN
SET s = CONCAT(s,',t.title AS table_name');
END IF;
IF cid = 0 THEN
SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
END IF;
SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
IF tid = 0 THEN
SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
END IF;
IF cid = 0 THEN
SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
END IF;
SET s = CONCAT(s,' WHERE 1=1');
IF selImgMode = 1 THEN
SET s = CONCAT(s,' AND a.item_imgs is not null');
END IF;
IF tid != 0 THEN
SET s = CONCAT(s,' AND a.tid=', tid);
END IF;
IF cid != 0 THEN
SET s = CONCAT(s,' AND a.cid=', cid);
END IF;
IF flag = 3 THEN
# get top 1 item with each table
SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
# select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
ELSEIF flag = 4 THEN
# get top 3 records with each table
SET s = CONCAT('SET @num := 0, @tid := 0;', s);
SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
# set @num := 0, @tid := 0;
# select b.* from (
# select a.*,
# @num := if(@tid=tid, @num + 1, 1) as row_number,
# @tid := a.tid as other_tid
# from pkrss_rssitem_l2 as a
# order by a.tid,a.id desc
# ) as b
# where b.row_number <= 3 order by b.tid,b.id desc limit 20;
END IF;
SET s = CONCAT(s,' ORDER BY');
IF idOrderBy != 0 THEN
IF idOrderBy > 0 THEN
SET s = CONCAT(s,' a.id ASC');
ELSE
SET s = CONCAT(s,' a.id DESC');
END IF;
ELSE
IF timeOrderBy > 0 THEN
SET s = CONCAT(s,' a.item_pubdate ASC');
ELSE
SET s = CONCAT(s,' a.item_pubdate DESC');
END IF;
END IF;
SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
SET t = CONCAT('_tmp_',UUID());
SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
`tid` int(11),
`cid` int(11),
`item_pubdate` datetime,
`item_title` varchar(127) COLLATE utf8_bin,
`item_desc` varchar(246) COLLATE utf8_bin,
`item_link` varchar(246) COLLATE ascii_bin,
`item_authors` varchar(64) COLLATE utf8_bin,
`item_imgs` text COLLATE utf8_bin,
`table_name` varchar(127) COLLATE utf8_bin,
`catalog_name` varchar(64) COLLATE utf8_bin,
`catalog_cls` varchar(32) COLLATE utf8_bin
);');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
IF tid = 0 THEN
SET s2 = CONCAT(s2,',`table_name`');
END IF;
IF cid = 0 THEN
SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
END IF;
SET @t2 = CONCAT(s2,') ', s, ';');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set @t2 = CONCAT('SELECT * FROM `',t,'`');
PREPARE stmt FROM @t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;;
DELIMITER ;
