mysql存储过程执行动态sql

原创于 2011-08-31 18:07:11 发布 · 4.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储 #sql

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文详细阐述了如何使用SQL语句进行事件选择与优化，包括根据请求者、事件类型和时间范围进行筛选，并实现分页展示。通过案例分析，展示了SQL查询在不同条件下的优化策略，帮助开发者提高查询效率。


DROP PROCEDURE IF EXISTS vtzero_good.event_select_requestor;
delimiter &&
CREATE PROCEDURE vtzero_good.`event_select_requestor`(in req varchar(20),in type int,in curpage int)
BEGIN
  DECLARE sqllimit varchar(500);
  set sqllimit='select id,requestor,operator,priority,title,startdate,enddate from event where requestor= ' ;
  if (curpage<=0) then
    set curpage=0;
  end if;
  case type 
  when 0 then
  set sqllimit=concat(sqllimit,"'", req,"' limit ", curpage,',15');
  when 1 then
  set sqllimit=concat(sqllimit,"'", req,"' and enddate='0001-01-01' limit ", curpage,' ,15');
  when 2 then
  set sqllimit=concat(sqllimit,"'", req,"' and enddate>'0001-01-01' limit ", curpage,' ,15');
  end case;
  set @sql = sqllimit;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END &&