oracle动态更新数据,oracle使用cursor动态更新线上初始化数据

背景：线上系统初始化数据需要调整，同时剩余数量也需要动态的调整

DECLARE

CURSOR c_res

IS SELECT B.PRESENT_NAME,B.PRESENT_PRESENT_LEFT_NUM,B.PRESENT_NUM FROM table B;

commitsum NUMBER(10);

leftNum NUMBER(4);

presentNum NUMBER(4);

BEGIN

commitsum :=0;

presentNum:=0;

FOR r_res IN c_res

LOOP

commitsum :=commitsum+1;

leftNum:=r_res.PRESENT_NUM-r_res.PRESENT_LEFT_NUM;

CASE

WHEN r_res.PRESENT_NAME='1';

THEN presentNum=100;

WHEN r_res.PRESENT_NAME='2';

THEN presentNum=200;

END CASE;

UPDATE table set PRESENT_NUM=presentNum,PRESENT_LEFT_NUM = presentNum-leftNum

WHERE PRESENT_NAME = r_res.PRESENT_NAME;

IF(commitsum>100)

THEN

COMMIT;

commitsum:=0;

END IF;

END LOOP;

COMMIT;

END;