请mysql 存储过程专家解答

Question

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经测试没错

Answer 1

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$$
然后贴出SELECT v_sql;结果

Answer 2

?这是程序的写法，在SP中用CONCAT
SET ASQL=CONCAT('th.fulldate_key >=',@A,....)

Answer 3

显示SQL语句内容、检查参数是否传递进去、字段类型与变量类型是否一致

Answer 4

贴出SELECT v_sql;结果
怎么操作才能有SELECT v_sql，结果

Answer 5

建议你不用？形式，直接用
CONCAT('th.fulldate_key >=',@A,....)
这种形式，看看SQL语句内容

Answer 6

SQL语句内容贴出来看看，直接在MYSQL中运行是否有误

Answer 7

我把问号改成时间后有结果，没错
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
    

Answer 8

SELECT STR_TO_DATE(startTimeStr, '%Y%m%d') INTO v_startTime;
SELECT DATE_ADD(v_startTime,INTERVAL 1 DAY) INTO v_endTime;
检查
SELECT v_startTime,v_endTime

Answer 9

这二句没错，我在其它的存储过程中有用到，  在mysql存储过程中有什么办法可以把变量输出到Messages，用来查看吗

Answer 10

1、SELECT v_startTime,v_endTime
检查结果
2、插入到临时表中，检查结果

Answer 11

看不到你的图，请直接贴文本。最在是在MYSQL命令行中直接执行（如下）。mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-12-10 16:27:50 |
+---------------------+
1 row in set (0.03 sec)mysql>

Answer 12

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>

Answer 13

 ON ( (DATE_ADD(th.fulldate_key,I |?
字符串超长？

Answer 14

不用动态执行吧，
select ....
 FROM gdmdw.t_dim_time th WHERE th.fulldate_key >= 你的参数
....
这种形式不行吗？

Answer 15

咋回事，论坛里就2个版主在回复问题了？？我也来凑个热闹。
MYSQL的存储过程在创建的时候，只会检查语法，不会检查语义。也就是说，你可以在存储过程里面查询不存在的表，创建也会成功。调试存储过程，可以用toad for mysql，这个工具可以单步调试。如果是命令行里的话，在你觉得有问题的地方，SELECT出你怀疑的对象，这样调试起来很快。

Answer 16

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 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>

Answer 17

mysql> select v_sql;
ERROR 1054 (42S22): Unknown column 'v_sql' in 'field list
  是这样吗 好像不行，toad for mysql 也没用过   看来真是菜

Answer 18

提供你的CRATE TABLE， INSERT INTO语句和 CREATE PROCEDURE 语句。
这样别人可以直接在自己的机器上模拟你的问题以便分析。

Answer 19

strutsx (strutsx)
  '截至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#结帖

Answer 20

把 ON ( (DATE_ADD(th.fulldate_key,INTERVAL 1 DAY) >=t.actbegin) AND th.fulldate_key < t.actend)  
  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 其实就是去了一对括号   还有就是在前面的一些代码消除了一些空格，就成功了！    但用到别外的电脑上又不行同一个程序在另外的电脑上不能正确运行是什么原因呢？
    

Answer 21

[Quote=引用 38 楼 acmain_chm 的回复:]引用同一个程序在另外的电脑上不能正确运行是什么原因呢？配置不一样。
[/Quote
是电脑配置还是mysql的配置   那个成功的mysql是5.0的   我这个是5.1的