第一次自己写存储过程:
注意:
MySQL用CONCAT用作动态拼接SQL语句
-- 存储过程:拿取结果集
DELIMITER // -- MySQL默认用;号作为分隔符,此处是将结束符变为//,后面再变回;
CREATE PROCEDURE get_trends_info(IN p_stime CHAR(20), IN p_tabs CHAR(255), IN p_grade1 INT, IN p_grade2 INT, IN p_type INT)
BEGIN
DECLARE p_result VARCHAR(1000) -- 实例一个变量,实例变量必须放在BEGIN后面,临时变量用@+字符组成
#拼接SQL语句
IF(p_tabs is null or p_tabs = '') THEN
SET @center_sql1 = CONCAT(' AND 1 = 1 ');
ELSE
SET @center_sql1 = CONCAT(' AND t.tabs regexp "',p_tabs,'"');
END IF;
IF(p_grade1!='') THEN
SET @center_sql2 = CONCAT(' AND t.grade1 = ',p_grade1);
ELSE
SET @center_sql2 = CONCAT(' AND 1=1 ');
END IF;
IF(p_grade2!='') THEN
SET @center_sql3 = CONCAT(' AND t.grade2 = ',p_grade2);
ELSE
SET @center_sql3 = CONCAT(' AND 1=1');
END IF;
IF(p_type!='') THEN
SET @center_sql4 = CONCAT(' AND t.type = ',p_type);
ELSE
SET @center_sql4 = CONCAT(' AND 1=1 ');
END IF;
SET @center_sql5 = CONCAT(' AND t.ctime >= "',p_stime,'"');
# 拿取结果集
# 拿取对应结果集的总数:最多取九条
SET @counts_sql = CONCAT('SELECT 18 - count(*) into @recordcount -- 此处是将查询出来的值赋值给一个变量FROM trends t WHERE t.status = 2',@center_sql1,@center_sql2,@center_sql3,@center_sql4,@center_sql5);
PREPARE stmt1 FROM @counts_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
# 获取动态语句的返回值
# 获取另外一条可以得到的数据
SET @p_limit = @recordcount;
#结果集大于9条时,取9条数据
IF(@p_limit<=9) THEN
# 用户喜欢的标签
SET @trends_sql = CONCAT('SELECT t._id,t.title,t.store,t.landmark,t.type,t.visit,t.except,t.coll,t.userid,
t.price,t.status,date_format(t.ctime,"%Y-%m-%d %H:%i:%s") as ctime,date_format(t.ptime,"%Y-%m-%d %H:%i:%s") as ptime,
t.tabs,t.loc,t.media,t.froms,t.types,t.grade1,t.grade2
FROM trends t WHERE t.status = 2 ',@center_sql1,@center_sql2,@center_sql3,@center_sql4,@center_sql5,' ORDER BY t.ctime LIMIT 9');
# 其他标签
SET @result_sql = CONCAT('SELECT t._id,t.title,t.store,t.landmark,t.type,t.visit,t.except,t.coll,t.userid,
t.price,t.status,date_format(t.ctime,"%Y-%m-%d %H:%i:%s") as ctime,date_format(t.ptime,"%Y-%m-%d %H:%i:%s") as ptime,
t.tabs,t.loc,t.media,t.froms,t.types,t.grade1,t.grade2
FROM trends t WHERE t.status = 2 ',@center_sql2,@center_sql3,@center_sql4,@center_sql5,' AND _id NOT IN (SELECT t._id
FROM trends t WHERE t.status = 2',@center_sql1,@center_sql2,@center_sql3,@center_sql4,@center_sql5,') ORDER BY ctime LIMIT 9');
ELSE
# 用户喜欢的标签
SET @trends_sql = CONCAT('SELECT t._id,t.title,t.store,t.landmark,t.type,t.visit,t.except,t.coll,t.userid,
t.price,t.status,date_format(t.ctime,"%Y-%m-%d %H:%i:%s") as ctime,date_format(t.ptime,"%Y-%m-%d %H:%i:%s") as ptime,
t.tabs,t.loc,t.media,t.froms,t.types,t.grade1,t.grade2
FROM trends t WHERE t.status = 2 ',@center_sql1,@center_sql2,@center_sql3,@center_sql4,@center_sql5,' ORDER BY t.ctime');
# 其他标签
SET @result_sql = CONCAT('SELECT t._id,t.title,t.store,t.landmark,t.type,t.visit,t.except,t.coll,t.userid,
t.price,t.status,date_format(t.ctime,"%Y-%m-%d %H:%i:%s") as ctime,date_format(t.ptime,"%Y-%m-%d %H:%i:%s") as ptime,
t.tabs,t.loc,t.media,t.froms,t.types,t.grade1,t.grade2
FROM trends t WHERE t.status = 2 ',@center_sql2,@center_sql3,@center_sql4,@center_sql5,' AND _id NOT IN (SELECT t._id
FROM trends t WHERE t.status = 2',@center_sql1,@center_sql2,@center_sql3,@center_sql4,@center_sql5,') ORDER BY ctime LIMIT ',@p_limit);
END IF;
SET @last_sql = CONCAT('SELECT a.* FROM (',@trends_sql,') a UNION SELECT b.* FROM (',@result_sql, ') b');
PREPARE stmt2 FROM @last_sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
# 合并结果集
END //
DELIMITER ;
SHOW PROCESSLIST; -- 查询数据库当前在执行的语句
call get_trends_info('2017-05-15 00:00:00','测试标签1|测试标签5',1,1,1); -- 调用存储过程
select @p;
drop procedure get_trends_info; -- 删除存储过程