CREATE or replace PROCEDURE query_dynamic_cursor (IN pm25 double)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_time varchar(10) default '' ;
DECLARE v_value varchar(10) default '' ;
DECLARE v_warn varchar(10) default '' ;
DECLARE v_type varchar(10) default '' ;
DECLARE v_projName varchar(10) default '' ; DECLARE cur CURSOR for( SELECT time,value,warn,type,projName from data_view);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP VIEW IF EXISTS data_view; SET @sqlstr = "CREATE VIEW data_view as ";
#SET @sqlstr = CONCAT(@sqlstr , "SELECT username FROM webuser WHERE username like '", p_name,"%'");
SET @sqlstr = CONCAT(@sqlstr , "select DATE_FORMAT(a.collect_time,'%Y-%m-%d %H:%i:%s') time,a.pm25 'value', ",pm25,
" warn, 'pm2.5预警' type ,b.proj_name projName from c_environm_check a LEFT JOIN ifs_pro_base b on a.proj_id=b.proj_id and a.proj_code =b.proj_code where a.pm25>=",pm25); PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_time varchar(10) default '' ;
DECLARE v_value varchar(10) default '' ;
DECLARE v_warn varchar(10) default '' ;
DECLARE v_type varchar(10) default '' ;
DECLARE v_projName varchar(10) default '' ; DECLARE cur CURSOR for( SELECT time,value,warn,type,projName from data_view);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP VIEW IF EXISTS data_view; SET @sqlstr = "CREATE VIEW data_view as ";
#SET @sqlstr = CONCAT(@sqlstr , "SELECT username FROM webuser WHERE username like '", p_name,"%'");
SET @sqlstr = CONCAT(@sqlstr , "select DATE_FORMAT(a.collect_time,'%Y-%m-%d %H:%i:%s') time,a.pm25 'value', ",pm25,
" warn, 'pm2.5预警' type ,b.proj_name projName from c_environm_check a LEFT JOIN ifs_pro_base b on a.proj_id=b.proj_id and a.proj_code =b.proj_code where a.pm25>=",pm25); PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOC
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货