记一个存储过程的例子
文章目录
create or replace procedure DATABUS_OPEX is
CURSOR CUR_TEST IS
SELECT
T.OBJECT_KEY,COUNT(T1.OBJECT_KEY) NUM
FROM 表名 T
LEFT JOIN (
SELECT OBJECT_KEY FROM 表名1 WHERE CODE='0025214000000071' AND RETRY_TIMES=5 AND STATUS='F'
) T1 ON T.OBJECT_KEY=T1.OBJECT_KEY
WHERE TO_CHAR(t.DELIVER_TIME,'yyyymmdd')=TO_CHAR(SYSDATE,'yyyymmdd') AND T.EVENT_KIND_ID='1009' AND STATUS='C' AND T.OBJECT_KEY='590000102801'
AND NOT EXISTS(SELECT 1 FROM 表名1 T2 WHERE CODE='0025214000000071' AND STATUS='C' AND T.OBJECT_KEY=T2.OBJECT_KEY)
GROUP BY T.OBJECT_KEY;
CUR CUR_TEST%ROWTYPE;
BEGIN
--For 循环
FOR CUR IN CUR_TEST LOOP
IF CUR.NUM=0 THEN
update 表名 t set STATUS='W2' where t.OBJECT_KEY=CUR.OBJECT_KEY and T.EVENT_KIND_ID='1009';
--循环体
ELSE
update 表名1 t set RETRY_TIMES=1 where t.OBJECT_KEY=CUR.OBJECT_KEY and CODE='0025214000000071';
END IF;
END LOOP;
END DATABUS_OPEX;
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论