在带有Informix的存储过程中使用MERGE语句 [英] Use MERGE statement in a stored procedure with Informix

问题描述

我有一个这样的存储过程: 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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！