DELIMITER $$USE `dw_center`$$DROP PROCEDURE IF EXISTS `p_call_proc`$$CREATE DEFINER = `root` @`%` PROCEDURE `p_call_proc` (
in_pname VARCHAR (50),
in_fre VARCHAR (5),
in_gameid INT,
in_start DATETIME,
in_end DATETIME
)
BEGIN
DECLARE v_sql VARCHAR (500) ;
##按小时调用过程
IF LOWER(in_fre) = 'h'
THEN SET @d_date := in_start ;
SELECT
@d_date ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 HOUR) )'
) ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 HOUR) ;
END WHILE ;
END IF ;
##按天调用过程
IF LOWER(in_fre) = 'd'
THEN SET @d_date := in_start ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 DAY) )'
) ;
SELECT
@v_sql ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 DAY) ;
END WHILE ;
END IF ;
##按月调用过程
IF LOWER(in_fre) = 'm'
THEN SET @d_date := in_start ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 MONTH) )'
) ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 MONTH) ;
END WHILE ;
END IF ;
END $$DELIMITER ;
各位大哥,今天写了个动态语句调用过程,测试的时候一直报错,找不出撒原因,求教!
错误代码: 1444
The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
in_pname VARCHAR (50),
in_fre VARCHAR (5),
in_gameid INT,
in_start DATETIME,
in_end DATETIME
)
BEGIN
DECLARE v_sql VARCHAR (500) ;
##按小时调用过程
IF LOWER(in_fre) = 'h'
THEN SET @d_date := in_start ;
SELECT
@d_date ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 HOUR) )'
) ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 HOUR) ;
END WHILE ;
END IF ;
##按天调用过程
IF LOWER(in_fre) = 'd'
THEN SET @d_date := in_start ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 DAY) )'
) ;
SELECT
@v_sql ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 DAY) ;
END WHILE ;
END IF ;
##按月调用过程
IF LOWER(in_fre) = 'm'
THEN SET @d_date := in_start ;
WHILE
(@d_date < in_end) DO SET @game_model := IF(
in_gameid IS NULL
OR in_gameid = '',
'(',
CONCAT('(', in_gameid, ',')
) ;
SET @v_sql := CONCAT(
'CALL ',
in_pname,
@game_model,
'''',
@d_date,
''',DATE_ADD(''',
@d_date,
''', INTERVAL 1 MONTH) )'
) ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @d_date := DATE_ADD(@d_date, INTERVAL 1 MONTH) ;
END WHILE ;
END IF ;
END $$DELIMITER ;
各位大哥,今天写了个动态语句调用过程,测试的时候一直报错,找不出撒原因,求教!
错误代码: 1444
The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货