DELIMITER $$USE `gdmods`$$DROP PROCEDURE IF EXISTS `sp_test`$$CREATE DEFINER=`root`@`localhost` 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<t.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key)
WHEN (th.fulldate_key<t.actbegin AND DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)>t.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)<t.actend)
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 ;这个存储过程可以创建成功,但调用时报错,You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 28
中间的查询和插入sql经测试没错
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<t.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key)
WHEN (th.fulldate_key<t.actbegin AND DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)>t.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)<t.actend)
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 ;这个存储过程可以创建成功,但调用时报错,You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 28
中间的查询和插入sql经测试没错
解决方案 »
- 请教一个分表储存的问题,是分表存储好,还是不分?!
- mysql批处理命令
- MySql去重--提取
- 存储过程问题
- 关于ASP实现mysql库的备份和删除
- 我要如何打开一个没有打开的端口呀。如我机器现在没有打开3306,而我现在要用它,也就是要打开3306这个端口,要如何操作呀?(3306是mysql
- 关于mysql 模糊配对的问题.....
- 请问,postgreSql装在windows下,如何打开postgreSql对多字节语言的支持?
- sql文件比较大 执行失败 仅仅是一张表而已 求解决啊
- 关于存储过程不起作用的问题!
- 如何将mysql无限级树结构转换为平面列表
- 关于mysql插入自动递增日期
SELECT v_sql;
SET @SQL = v_sql;
PREPARE s1 FROM @SQL;
END$$EXECUTE s1 USING @a,@b,@c,@d,@e,@f;
DEALLOCATE PREPARE s1;
然后贴出SELECT v_sql;结果
SET ASQL=CONCAT('th.fulldate_key >=',@A,....)
怎么操作才能有SELECT v_sql,结果
CONCAT('th.fulldate_key >=',@A,....)
这种形式,看看SQL语句内容
SELECT "work_time_key",th.time_key,t.agentid, SUM(
CASE
WHEN (th.fulldate_key>=t.actbegin AND th.fulldate_key<t.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key)
WHEN (th.fulldate_key<t.actbegin AND DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)>t.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)<t.actend)
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 >= STR_TO_DATE('20101206', '%Y%m%d')
AND th.fulldate_key < STR_TO_DATE('20101207', '%Y%m%d')) th LEFT JOIN (
SELECT t.agentid,t.actbegin,t.actend FROM gdmods.tagentoprinfo t
WHERE OperateType="0"
AND t.actend>= STR_TO_DATE('20101206', '%Y%m%d')
AND t.actend< STR_TO_DATE('20101207', '%Y%m%d')
UNION ALL
SELECT t.agentid,MAX(t.actbegin), STR_TO_DATE('20101207', '%Y%m%d')
FROM gdmods.tagentoprinfo t WHERE (OperateType="1" OR OperateType="0")
AND t.actend>= STR_TO_DATE('20101206', '%Y%m%d')
AND t.actend< STR_TO_DATE('20101207', '%Y%m%d')
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
SELECT DATE_ADD(v_startTime,INTERVAL 1 DAY) INTO v_endTime;
检查
SELECT v_startTime,v_endTime
这二句没错,我在其它的存储过程中有用到, 在mysql存储过程中有什么办法可以把变量输出到Messages,用来查看吗
检查结果
2、插入到临时表中,检查结果
+---------------------+
| now() |
+---------------------+
| 2010-12-10 16:27:50 |
+---------------------+
1 row in set (0.03 sec)mysql>
mysql> use gdmods;
Database changed
mysql> CALL sp_test('1','20101206')
-> ;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
| v_sql |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
|
INSERT INTO tmp_gz_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<t.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key) WHEN (th.fulldate_key<t.actbegin AND DATE_ADD(th.fulldate_key,INTERVAL 1
DAY)>t.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)<t.actend)
THEN UNIX_TIMESTAMP(DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)) - UNIX_TIM
ESTAMP(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<? ) t
ON ( (DATE_ADD(th.fulldate_key,I |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected, 1 warning (0.16 sec)mysql>
字符串超长?
select ....
FROM gdmdw.t_dim_time th WHERE th.fulldate_key >= 你的参数
....
这种形式不行吗?
MYSQL的存储过程在创建的时候,只会检查语法,不会检查语义。也就是说,你可以在存储过程里面查询不存在的表,创建也会成功。调试存储过程,可以用toad for mysql,这个工具可以单步调试。如果是命令行里的话,在你觉得有问题的地方,SELECT出你怀疑的对象,这样调试起来很快。
-> ;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
| v_sql |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
|
INSERT INTO tmp_gz_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<t.actend)
THEN UNIX_TIMESTAMP(t.actend)-UNIX_TIMESTAMP(th.fulldate_key) WHEN (th.fulldate_key<t.actbegin AND DATE_ADD(th.fulldate_key,INTERVAL 1
DAY)>t.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)<t.actend)
THEN UNIX_TIMESTAMP(DATE_ADD(th.fulldate_key,INTERVAL 1 DAY)) - UNIX_TIM
ESTAMP(t.actbegin) END
) AS worktime
FROM (SELECT th.time_key,th.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<? ) t
ON ( (DATE_ADD(th.fulldate |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.05 sec)ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 27
mysql>
ERROR 1054 (42S22): Unknown column 'v_sql' in 'field list
是这样吗 好像不行,toad for mysql 也没用过 看来真是菜
这样别人可以直接在自己的机器上模拟你的问题以便分析。
'截至2010-12-10 14:44:23 用户结帖率0.00% 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
AND t.actend IS NOT NULL
改成ON (DATE_ADD(th.fulldate_key,INTERVAL 1 DAY))>=t.actbegin AND th.fulldate_key<t.actend AND t.actend IS NOT NULL 其实就是去了一对括号 还有就是在前面的一些代码消除了一些空格,就成功了! 但用到别外的电脑上又不行同一个程序在另外的电脑上不能正确运行是什么原因呢?
[/Quote
是电脑配置还是mysql的配置 那个成功的mysql是5.0的 我这个是5.1的