游标循环 [英] loop through cursor

问题描述

CREATE OR REPLACE TRIGGER DOTCOM.TRG_CHK
BEFORE INSERT OR UPDATE
ON DOTCOM.SM_SRET_DETAIL REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
CURSOR C2
   IS
   SELECT SD_SRET_QTY_BU,SD_QTY_BU FROM SM_SALES_DETAIL
   WHERE SD_SH_SYS_ID = 7777--SDSYSID
   AND SD_ITEM_CODE   = 'DOTFULL0064'--SDITEMCODE
   AND SD_LOC_CODE    = 'D01'--SDLOCNCODE
   for update of SD_SRET_QTY_BU;

BEGIN

     for rec in c2 loop
                VSD_QTY_BU := rec.SD_QTY_BU;
            VSD_SRET_QTY_BU := rec.SD_SRET_QTY_BU;

                IF   (VSD_QTY_BU - VSD_SRET_QTY_BU) >= 0 THEN
                    UPDATE SM_SALES_DETAIL SET SD_SRET_QTY_BU = SD_SRET_QTY_BU + newval
                WHERE current of c2;

             ELSE
                    RAISE_APPLICATION_ERROR (-20002,' RETURN QUANTITY EXCEEDED SOLD QUANTITY  ' || :NEW.CSRI_ITEM_CODE);



                END IF;
            END LOOP;
 END

当我运行此命令时，游标将返回2行，但是我想根据条件QTY_BU和RET_QTY_BU分别更新此行..仅两列对于这两行是不同的.

When I run this the cursor will return 2 rows, but I want to update both of this row seperately based on a condition QTY_BU and RET_QTY_BU..only this two columns are different for the two rows.
When I try to update it is updating on both the row.

推荐答案

您当前在代码中有此行

You currently have this line in your code

UPDATE SM_SALES_DETAIL SET SD_SRET_QTY_BU = SD_SRET_QTY_BU + newval            WHERE current of c2;

将其替换为以下

replace this with the follwing

UPDATE SM_SALES_DETAIL SET SD_SRET_QTY_BU = SD_SRET_QTY_BU + newval WHERE SD_QTY_BU=VSD_QTY_BU AND SD_SRET_QTY_BU=VSD_SRET_QTY_BU;

这篇关于游标循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！