MySQL存储过程

第一次自己写存储过程：

注意：

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; -- 删除存储过程