Oracle使用游标批量更新表数据:
BEGIN
FOR JSC IN
(
SELECT JS.*
FROM SF_JMMJJS_T JS,SF_EBZCB_T CB,SF_JMYH_T YH
WHERE
JS.YHBH=CB.YHBH AND JS.MJBH=CB.MJBH AND JS.CNQ=CB.CNQ
AND JS.YHBH=YH.YHBH
AND JS.YSJE = 0
AND JS.SFMJ>0
AND JS.GNZT = '正常'
AND JS.ZF = 0
AND JS.CNQ = '2016-2017'
AND CB.ZT=2
AND YH.SFEBZ='是'
AND YH.ZF=0
AND YH.GNZT = '正常'
)LOOP
UPDATE SF_JMMJJS_T
SET YSJE=(CASE WHEN ROUND(JSC.MJYS*JSC.JSBL+JSC.JLYS,0)>JSC.MJYS
THEN JSC.MJYS
ELSE
ROUND(JSC.MJYS*JSC.JSBL+JSC.JLYS,0)
END)
WHERE SF_JMMJJS_T.BH=JSC.BH;
END LOOP;
END;
COMMIT;
补充:
游标的另一种写法:
简单写个结构
CREATE OR REPLACE FUNCTION SF_ZNJ_F
(
V_YHLX VARCHAR2, --用户类型
V_MJBH SF_JMSF_T.YHBH%TYPE, --用户编号
V_FYLB SF_JMSF_T.FYLB%TYPE, --费用类别
V_JSRQ SF_JMSF_T.JFRQ%TYPE, --交费日期
V_CNQ SF_JMSF_T.CNQ%TYPE
) RETURN NUMBER IS
V_MIDDATE DATE;
RESULT NUMBER;
--查询开始计算滞纳金开始之后是否有交费
CURSOR JMMJJF_C IS
SELECT JFRQ, JFJE, ZNJ
FROM SF_JMSF_T
WHERE JFRQ > V_KSRQ AND CNQ = V_CNQ AND YHBH = V_YHBH AND ZF = 0 AND
FYLB = V_FYLB
ORDER BY JFRQ;
BEGIN
OPEN JMMJJF_C;
FETCH JMMJJF_C
INTO V_JFRQ, V_JFJE, V_ZNJ;
--如果没有交费记录
IF JMMJJF_C%NOTFOUND THEN
XXXXXX
END IF;
ELSE
--如果有交费记录
WHILE JMMJJF_C%FOUND LOOP
XXXXX
FETCH JMMJJF_C
INTO V_JFRQ, V_JFJE, V_ZNJ;
END LOOP;
CLOSE JMMJJF_C;
END SF_ZNJ_F;