Plsql 案例未正确读取大小写

Question

我需要逐行读取数据并根据匹配的大小写进行更新。

我的代码只是读取ORBREFTND并从大小写中插入Refund，而不是读取其他情况。我做错了什么。

DECLARE
    v_tot_rows   NUMBER (3);
    v_eval       ISG.SWR_CERT_TXN_MSG_TYPE.DESC_TX%TYPE;
    CURSOR pltfm_msg_type_cur
    IS
        SELECT sctmt.PLTFM_MSG_TYPE_CD
          FROM ISG.SWR_CERT_TXN_MSG_TYPE  sctmt
               JOIN ISG.FEATURE_VALUES ft_val
                   ON sctmt.SWR_CERT_FETR_VAL_ID = ft_val.FEATURE_VAL
               JOIN ISG.FEATURE_CATEGORY ft_cat
                   ON ft_val.FEATURE_CAT = ft_cat.FEATURE_CAT
         WHERE     ft_cat.SHORT_DESCRIP = 'PLT'
               AND ft_val.FEATURE_VALUE = 'Orbital';
BEGIN
    FOR msg_code IN pltfm_msg_type_cur
    LOOP
        CASE msg_code.PLTFM_MSG_TYPE_CD
            WHEN 'WAC'
            THEN
                v_eval := 'Authorization and Mark for Capture';
            WHEN 'ORBAUTHCAP'
            THEN
                v_eval := 'Authorization Capture';
            WHEN 'ORBREFTND'
            THEN
                v_eval := 'Refund';
        END CASE;
        UPDATE ISG.SWR_CERT_TXN_MSG_TYPE sctmt
           SET sctmt.DESC_TX = v_eval;
    END LOOP;
    COMMIT;
    v_tot_rows := SQL%ROWCOUNT;
    /* Implicit Attribute %ROWCOUNT is used   to find the number of rows affected     by the update command */
    DBMS_OUTPUT.PUT_LINE ('Total records updated : ' || v_tot_rows);
END;

Answer 1

你缺少一个where-condition：

    UPDATE ISG.SWR_CERT_TXN_MSG_TYPE sctmt
       SET sctmt.DESC_TX = v_eval
     WHERE sctmt.primarykey = msg_code.primarykey; -- This one is missing. Don't know your primary key..

如果没有where，您将始终将最后一个游标对象的值设置为表中的所有对象ISG.SWR_CERT_TXN_MSG_TYPE。

您还需要从表中选择密钥：

    CURSOR pltfm_msg_type_cur
    IS
        SELECT sctmt.PLTFM_MSG_TYPE_CD,
               sctmt.PRIMARYKEY -- select key here
          FROM ISG.SWR_CERT_TXN_MSG_TYPE  sctmt
               JOIN ISG.FEATURE_VALUES ft_val
                   ON sctmt.SWR_CERT_FETR_VAL_ID = ft_val.FEATURE_VAL
               JOIN ISG.FEATURE_CATEGORY ft_cat
                   ON ft_val.FEATURE_CAT = ft_cat.FEATURE_CAT
         WHERE     ft_cat.SHORT_DESCRIP = 'PLT'
               AND ft_val.FEATURE_VALUE = 'Orbital';