dbexpress获取存储过程的返回值,解决方案
dbexpress获取存储过程的返回值,急急急!
使用DBExpress连接DB2数据库
然后调用存储过程
存储过程如下:
CREATE PROCEDURE proc_NewSave_Sys (v_uniqueguid_2 VARCHAR(36),
v_uniqueid_3 VARCHAR(32),
v_agent_4 VARCHAR(16),
v_callid_5 VARCHAR(24)
)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE RETURN_VALUE INTEGER;
DECLARE v_ret INTEGER;
DECLARE l_rowcount INTEGER;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT lsh
FROM t_sys
WHERE uniqueguid = v_uniqueguid_2;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
SET v_outvalue = -2;
OPEN temp_cursor;
SELECT COUNT(*)
INTO l_rowcount
FROM (SELECT lsh
FROM t_sys
WHERE uniqueguid = v_uniqueguid_2) temp_table;
IF l_rowcount > 0 THEN
SET l_error = '00000';
UPDATE t_sys SET
agent=v_agent_4,
callid=v_callid_5
WHERE
uniqueguid = v_uniqueguid_2;
COMMIT;
IF l_error != '00000' THEN
SET RETURN_VALUE = l_error;
RETURN RETURN_VALUE ;
ELSE
SET RETURN_VALUE = 1;
RETURN 1;
END IF;
ELSE
SET l_error = '00000';
INSERT INTO t_sys (uniqueguid,uniqueid,agent,callid)
VALUES (v_uniqueguid_2,v_uniqueid_3,v_agent_4,v_callid_5);
COMMIT;
IF l_error != '00000' THEN
SET RETURN_VALUE = l_error;
RETURN RETURN_VALUE ;
ELSE
SET RETURN_VALUE = 1;
RETURN 1;
END IF;
使用DBExpress连接DB2数据库
然后调用存储过程
存储过程如下:
CREATE PROCEDURE proc_NewSave_Sys (v_uniqueguid_2 VARCHAR(36),
v_uniqueid_3 VARCHAR(32),
v_agent_4 VARCHAR(16),
v_callid_5 VARCHAR(24)
)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE RETURN_VALUE INTEGER;
DECLARE v_ret INTEGER;
DECLARE l_rowcount INTEGER;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT lsh
FROM t_sys
WHERE uniqueguid = v_uniqueguid_2;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
SET v_outvalue = -2;
OPEN temp_cursor;
SELECT COUNT(*)
INTO l_rowcount
FROM (SELECT lsh
FROM t_sys
WHERE uniqueguid = v_uniqueguid_2) temp_table;
IF l_rowcount > 0 THEN
SET l_error = '00000';
UPDATE t_sys SET
agent=v_agent_4,
callid=v_callid_5
WHERE
uniqueguid = v_uniqueguid_2;
COMMIT;
IF l_error != '00000' THEN
SET RETURN_VALUE = l_error;
RETURN RETURN_VALUE ;
ELSE
SET RETURN_VALUE = 1;
RETURN 1;
END IF;
ELSE
SET l_error = '00000';
INSERT INTO t_sys (uniqueguid,uniqueid,agent,callid)
VALUES (v_uniqueguid_2,v_uniqueid_3,v_agent_4,v_callid_5);
COMMIT;
IF l_error != '00000' THEN
SET RETURN_VALUE = l_error;
RETURN RETURN_VALUE ;
ELSE
SET RETURN_VALUE = 1;
RETURN 1;
END IF;