本文介绍了在带有Informix的存储过程中使用MERGE语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的存储过程: http://tinyurl.com/pbk8qfb
I have a stored procedure like this : http://tinyurl.com/pbk8qfb
CREATE PROCEDURE PS_TAB_TSEN(NOMFIC VARCHAR(70), LIBSEN VARCHAR(35),
CODAPPFOF VARCHAR(3))
DEFINE NUMINTNAV VARCHAR(10);
BEGIN;
LET NUMINTNAV = '0';
SELECT TO_CHAR(NUM_INT_NAV) INTO NUMINTNAV
FROM TAB_INAV WHERE NOM_FIC = NOMFIC;
MERGE INTO TAB_TSEN T
USING (SELECT COD_SEN, LIBSEN AS LIB_SEN, CODAPPFOF AS COD_APP_FOF
FROM TAB_TSEN
WHERE COD_SEN = NUMINTNAV) AS S
ON (T.COD_SEN = S.COD_SEN)
WHEN MATCHED THEN
UPDATE SET T.COD_SEN = S.COD_SEN, T.LIB_SEN = S.LIB_SEN,
T.COD_APP_FOF = S.COD_APP_FOF
WHEN NOT MATCHED THEN
INSERT (T.COD_SEN, T.LIB_SEN, T.COD_APP_FOF)
VALUES (S.COD_SEN, S.LIB_SEN, S.COD_APP_FOF);
COMMIT;
END PROCEDURE;
运行时一切都很好,但是当我检查表时,插入语句和更新语句都没有结果.
When I run it all is good, but when I check in the table I see no result, for the insert statement and for the update statement too.
我这样称呼它:
CALL PS_TAB_TSEN('~/PSS/Order/OrderList.aspx', 'Liste des commandes', 'NET');
我不知道发生了什么事...
I don't understand what is going on...
推荐答案
问题解决了,我用这个替换存储过程:
Problem solved, I replace my stored procedure by this one :
CREATE PROCEDURE PS_TAB_TSEN(NOMFIC VARCHAR(70), LIBSEN VARCHAR(35), CODAPPFOF VARCHAR(3))
DEFINE ISPRESENT INTEGER;
DEFINE NUMINTNAV VARCHAR(10);
BEGIN;
LET NUMINTNAV = '0';
SELECT TO_CHAR(NUM_INT_NAV) INTO NUMINTNAV FROM TAB_INAV WHERE NOM_FIC = NOMFIC;
LET ISPRESENT = 0;
SELECT COUNT(*) INTO ISPRESENT FROM TAB_TSEN WHERE COD_SEN = NUMINTNAV;
IF(ISPRESENT > 0) THEN
UPDATE TAB_TSEN SET T.COD_SEN = S.COD_SEN, T.LIB_SEN = S.LIB_SEN, T.COD_APP_FOF = S.COD_APP_FOF
ELSE
INSERT INTO TAB_TSEN (T.COD_SEN, T.LIB_SEN, T.COD_APP_FOF) VALUES (S.COD_SEN, S.LIB_SEN, S.COD_APP_FOF);
END IF;
COMMIT;
END PROCEDURE;
这篇关于在带有Informix的存储过程中使用MERGE语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!