存储过程:
CREATE PROCEDURE `pro_ad_click_stat`(
IN userID BIGINT(20),
IN todayStart VARCHAR(20),
IN todayEnd VARCHAR(20),
IN beginTime VARCHAR(20),
IN endTime VARCHAR(20),
OUT totalClick INTEGER(11),
OUT click INTEGER(11),
OUT todayClick INTEGER(11),
OUT todayTotal FLOAT(9,2),
OUT total FLOAT(9,2)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare aSql varchar(4000);
declare bSql varchar(4000);
declare cSql varchar(4000);
set @a = 0;
set @b = 0.00;
set @c = 0;
set @d = 0.00;
set @e = 0;
set aSql="select count(id),sum(price) into @a,@b from adnote where 1=1 ";
set bSql="select count(id),sum(price) into @c,@d from adnote where 1=1 ";
set cSql="select count(id) into @e from adlog where 1=1 ";
if userID is not null and LENGTH(userID)>0 then
set aSql = CONCAT(aSql," and userID=",userID);
set bSql = CONCAT(bSql," and userID=",userID);
set cSql = CONCAT(cSql," and userID=",userID);
end if;
if todayStart is not null and LENGTH(todayStart)>0 then
set aSql = CONCAT(aSql," and clickTime>='",todayStart,"'");
end if;
if todayEnd is not null and LENGTH(todayStart)>0 then
set aSql = CONCAT(aSql," and clickTime<'",todayEnd,"'");
end if; if beginTime is not null and LENGTH(beginTime)>0 then
set bSql = CONCAT(bSql," and clickTime>='",beginTime,"'");
set cSql = CONCAT(cSql," and clickTime>='",beginTime,"'");
end if;
if endTime is not null and LENGTH(endTime)>0 then
set bSql = CONCAT(bSql," and clickTime<'",endTime,"'");
set cSql = CONCAT(cSql," and clickTime<'",endTime,"'");
end if;
set @_sSql = aSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set @_sSql = bSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set @_sSql = cSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set todayClick = @a;
set todayTotal = @b;
set click = @c;
set total = @d;
set totalClick = @e;
END;这个存储过程,会偶尔出现java.sql.SQLException: No output parameters returned by procedure.这个错误不是经常出现。
CREATE PROCEDURE `pro_ad_click_stat`(
IN userID BIGINT(20),
IN todayStart VARCHAR(20),
IN todayEnd VARCHAR(20),
IN beginTime VARCHAR(20),
IN endTime VARCHAR(20),
OUT totalClick INTEGER(11),
OUT click INTEGER(11),
OUT todayClick INTEGER(11),
OUT todayTotal FLOAT(9,2),
OUT total FLOAT(9,2)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare aSql varchar(4000);
declare bSql varchar(4000);
declare cSql varchar(4000);
set @a = 0;
set @b = 0.00;
set @c = 0;
set @d = 0.00;
set @e = 0;
set aSql="select count(id),sum(price) into @a,@b from adnote where 1=1 ";
set bSql="select count(id),sum(price) into @c,@d from adnote where 1=1 ";
set cSql="select count(id) into @e from adlog where 1=1 ";
if userID is not null and LENGTH(userID)>0 then
set aSql = CONCAT(aSql," and userID=",userID);
set bSql = CONCAT(bSql," and userID=",userID);
set cSql = CONCAT(cSql," and userID=",userID);
end if;
if todayStart is not null and LENGTH(todayStart)>0 then
set aSql = CONCAT(aSql," and clickTime>='",todayStart,"'");
end if;
if todayEnd is not null and LENGTH(todayStart)>0 then
set aSql = CONCAT(aSql," and clickTime<'",todayEnd,"'");
end if; if beginTime is not null and LENGTH(beginTime)>0 then
set bSql = CONCAT(bSql," and clickTime>='",beginTime,"'");
set cSql = CONCAT(cSql," and clickTime>='",beginTime,"'");
end if;
if endTime is not null and LENGTH(endTime)>0 then
set bSql = CONCAT(bSql," and clickTime<'",endTime,"'");
set cSql = CONCAT(cSql," and clickTime<'",endTime,"'");
end if;
set @_sSql = aSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set @_sSql = bSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set @_sSql = cSql;
prepare sqlstmt from @_sSql;
execute sqlstmt;
set todayClick = @a;
set todayTotal = @b;
set click = @c;
set total = @d;
set totalClick = @e;
END;这个存储过程,会偶尔出现java.sql.SQLException: No output parameters returned by procedure.这个错误不是经常出现。
建议当抛除异常时,保留输入的参数,然后检测一下语句的执行情况
也就是debug一下。
if endTime <> = '0000-00-00 00:00:00' then