本文共 2121 字,大约阅读时间需要 7 分钟。
哪位大侠帮看个mysql存储过程,哪里不对========================DELIMITER$$ALTERPROCEDURE`sp_test`(vdnIdCHAR(2),startTimeStrCHAR(8))BEGINDECLAREv_startTimeDATETIME;DECLAREv_...
哪位大侠帮看个mysql存储过程,哪里不对
========================
DELIMITER $$
ALTER PROCEDURE `sp_test`(vdnId CHAR(2),startTimeStr CHAR(8))
BEGIN
DECLARE v_startTime DATETIME;
DECLARE v_endTime DATETIME;
DECLARE v_VDNName CHAR(2);
DECLARE v_sql VARCHAR(1000);
/*判断输入参数的合法性*/
SELECT t.VDN_name INTO v_VDNName FROM gdmdw.t_dim_vdn t WHERE t.vdn_key = vdnId;
SELECT STR_TO_DATE(startTimeStr, '%Y%m%d') INTO v_startTime;
SELECT DATE_ADD(v_startTime,INTERVAL 1 DAY) INTO v_endTime;
/*实现业务逻辑*/
-- 整理时长
SET v_sql = CONCAT('
INSERT INTO tmp_',v_VDNName,'_tagentopr
(kpi_key,time_key,agent_key,kpi_value)
(
SELECT "work_time_key",th.time_key,t.agentid, SUM(
CASE
WHEN (th.fulldate_key>=t.actbegin AND th.fulldate_key
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key)
WHEN (th.fulldate_keyt.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(t.actbegin)
WHEN (DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)>t.actbegin) AND (DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)
THEN UNIX_TIMESTAMP(DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)) - UNIX_TIMESTAMP(t.actbegin)
END
) AS worktime
FROM (SELECT time_key,fulldate_key
FROM gdmdw.t_dim_time th WHERE th.fulldate_key >= ?
AND th.fulldate_key ) th LEFT JOIN (
SELECT t.agentid,t.actbegin,t.actend FROM gdmods.tagentoprinfo t
WHERE OperateType="0"
AND t.actend>= ?
AND t.actend
UNION ALL
SELECT t.agentid,MAX(t.actbegin), ?
FROM gdmods.tagentoprinfo t WHERE (OperateType="1" OR OperateType="0")
AND t.actend>=?
AND t.actend
GROUP BY t.agentid
HAVING MAX(t.actbegin)=MAX(t.actend)
) t
ON ( (DATE_ADD(th.fulldate_key,INTERVAL 1 DAY) >=t.actbegin) AND th.fulldate_key < t.actend)
AND t.actend IS NOT NULL
GROUP BY agentid,time_key');
SET @a = v_startTime;
SET @b = v_endTime;
SET @c = v_startTime;
SET @d= v_endTime;
SET @e=v_endTime;
SET @f= v_startTime;
SET @g = v_endTime;
SELECT v_sql;
SET @SQL = v_sql;
PREPARE s1 FROM @SQL;
EXECUTE s1 USING @a,@b,@c,@d,@e,@f;
DEALLOCATE PREPARE s1;
END$$
DELIMITER ;
是不是要声明7个不一样的参数对应7个问号,或者你是说少个@g? 能详细说下吗?
展开
转载地址:https://blog.csdn.net/weixin_33946505/article/details/113979486 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!