为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8
【操作系统】:windows
【CPU】:intel i5 11700
【问题描述】*:在dm管理工具可以执行调用存储工程。
请求大佬帮助!!!
通过mybatis 调用存储过程,一直提示字符串转换出错。
存储过程定义如下:
CREATE OR REPLACE PROCEDURE PC_SP_35_05_T_W
(
code OUT INT,
parentnodetype INT,
parentnodeid INT,
updatetime DATETIME,
bupdateduplicate INT
)
AS
Declare
mergeSQL NVARCHAR(3000);
duplicateCount INT;
maxUpdateTime DATETIME;
updateTimeCode INT;
BEGIN
IF (parentNodeType IS NULL) OR (parentNodeType<0) THEN
code = -101;
RETURN;
END IF;
IF (parentNodeID IS NULL) OR (parentNodeID<0) THEN
code = -102;
RETURN;
END IF;
IF (updateTime IS NULL) THEN
code = -103;
RETURN;
END IF;
IF (bUpdateDuplicate IS NULL) OR (bUpdateDuplicate<0) THEN
code = -104;
RETURN;
END IF;
-- 调整 maxUpdateTime 的计算逻辑
maxUpdateTime = updateTime;
IF (bUpdateDuplicate > 0) THEN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM PC_TB_05 a, TEMP_WRITESETUPNODES b WHERE a.NODETYPE = b.NODETYPE AND a.NODEID = b.NODEID' INTO duplicateCount;
IF (duplicateCount > 0) THEN
EXECUTE IMMEDIATE 'SELECT MAX(a.updatetime) FROM PC_TB_05 a, TEMP_WRITESETUPNODES b WHERE a.NODETYPE = b.NODETYPE AND a.NODEID = b.NODEID' INTO maxUpdateTime;
IF (maxUpdateTime >= updateTime) THEN
maxUpdateTime = maxUpdateTime + INTERVAL '1' SECOND;
ELSE
maxUpdateTime = updateTime;
END IF;
END IF;
END IF;
-- 修改 MERGE 语句的 ON 条件
mergeSQL = 'MERGE INTO PC_TB_05 DEST USING TEMP_WRITESETUPNODES SRC
ON (SRC.NODETYPE = DEST.NODETYPE AND SRC.NODEID = DEST.NODEID)
WHEN NOT MATCHED THEN
INSERT VALUES(src.nodeType, src.nodeID, src.nodeInternalName,
src.nodeName, src.version, src.parentNodeType, src.parentNodeID,
?, src.dataLength, src.data) ';
-- 修改 WHEN MATCHED 分支,显式更新父节点字段
IF (bUpdateDuplicate > 0 AND duplicateCount > 0) THEN
mergeSQL = mergeSQL || ' WHEN MATCHED THEN
UPDATE SET
dest.PARENTNODETYPE = src.PARENTNODETYPE,
dest.PARENTNODEID = src.PARENTNODEID,
dest.nodeName = src.nodeName,
dest.version = src.version,
dest.updateTime = ?,
dest.dataLength = src.dataLength,
dest.data = src.data';
EXECUTE IMMEDIATE mergeSQL USING maxUpdateTime, maxUpdateTime;
ELSE
EXECUTE IMMEDIATE mergeSQL USING maxUpdateTime;
END IF;
-- 调用更新父节点时间的函数
updateTimeCode = updateparentnodetime(PC_SP_35_05_t_w.parentNodeType, PC_SP_35_05_t_w.parentNodeID, PC_SP_35_05_t_w.updatetime, 'PC_TB_05');
RETURN;
END;
这个报错一般是数据类型不匹配导致的,检查下数据类型是否一致。