当我执行 call P_AGINGDATA('');
. 我得到以下错误:
Error Code: 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
错误消息说我不能调用引用同一语句的例程。但我没有!
它让我头痛。谁能给我一些建议?
我直接呼叫的程序
CREATE PROCEDURE P_AGINGDATA
--
DECLARE CUR_AGING CURSOR FOR
SELECT ID,
TABLE_NAME,
COLUMN_NAME,
AGING_OPTION,
MAX_RESERVE_DAY,
HISTORY_TABLE_NAME
FROM t_cm_aging_config T
WHERE T.RULE_VALID = 1
ORDER BY ID;
OPEN CUR_AGING;
loop_label:
LOOP
FETCH CUR_AGING
INTO V_ID, V_TABLE_NAME, V_COLUMN_NAME, V_AGING_OPTION, V_MAX_RESERVE_DAY, V_HISTORY_TABLE_NAME;
IF NOT_FOUND = 1 THEN
LEAVE loop_label;
END IF;
IF UPPER(V_AGING_OPTION) IN ('HISTORY', 'DELETE') THEN
-- todo
ELSEIF UPPER(V_AGING_OPTION) = 'EXECUTE' THEN
SET @GetName = CONCAT('CALL ',V_TABLE_NAME,'(',V_MAX_RESERVE_DAY,');');
PREPARE stmt FROM @GetName;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END LOOP loop_label;
CLOSE CUR_AGING;
END $$
在前一个过程的循环中调用的另一个过程
CREATE PROCEDURE P_CM_AGING_CUSTOMER ( IN v_max_reserve_day BIGINT )
BEGIN
DECLARE v_days_from_1970 BIGINT;
DECLARE v_start_unix_timestamp BIGINT;
DECLARE v_has_exception INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_has_exception = 1;
CREATE TABLE IF NOT EXISTS TMP_OFFERING_INST_ID (OFFERING_INST_ID BIGINT(13));
TRUNCATE TABLE TMP_OFFERING_INST_ID;
SET @Insertofferinst = CONCAT('INSERT INTO TMP_OFFERING_INST_ID select offering_inst_id from t_cm_offer_inst t where status=''9'' and ACTIVE_FLAG=''0'' and '
, ' CREATED_TIME < 86400000*(',ifnull(v_days_from_1970, ''),' - ',ifnull(v_max_reserve_day, ''),') and not exists (select 1 from t_cm_offer_inst a where t.customer_id=a.customer_id and (a.status<>''9'' or a.ACTIVE_FLAG<>''0''))');
PREPARE stmt FROM @Insertofferinst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
-- related data inserted into tmp table,start delete data
SET @Deletedata = 'DELETE FROM T_CM_OFFER_INST_FEE WHERE OFFERING_INST_ID IN (SELECT OFFERING_INST_ID FROM TMP_OFFERING_INST_ID)';
PREPARE stmt FROM @Deletedata;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
END;
$$
2条答案
按热度按时间6za6bjd01#
错误说明
prepared语句包含引用同一语句的存储例程调用。
您已经准备好了stmt。更改要执行的变量名,请尝试
yi0zb3m42#
最后,我发现准备好的调用例程的语句是不正确的。
下面的语法是正确的,