oracle 游标的使用

额，一直提起游标就头疼，总感觉是很高大上的东西，望而却步...

今天要做的东西涉及到了实时更新数据，要用到JOB 存储过程  游标

 

通过在网上查资料，请教同事，也开始继续深入oracle，，，，小菜啊小菜。。。

做个笔记，方便以后查看

CREATE OR REPLACE PROCEDURE COST_MES_SL_INIT_SP_001(P_SYSDATE DATE) IS  
V_P_SYSDATE DATE;
V_YEAR NUMBER;
V_MONTH NUMBER;

CURSOR C_FACILITY_ID IS  --创建游标
SELECT FACILITY_ID   
FROM COST_BASE_FACILITY_VW
WHERE 1 = 1 /* FACILITY_ID IN (SELECT FACILITY_ID
FROM ZZYX_USER_JLMES_FACILITY_TB T
WHERE USER_ID = {$SYS_USERID$})*/
AND U_LOWER = 1
ORDER BY U_ORDER;

R_FACILITY_ID C_FACILITY_ID%ROWTYPE;   定义游标变量，我理解为对象

BEGIN

V_P_SYSDATE := P_SYSDATE;
SELECT ADD_MONTHS(TRUNC(SYSDATE), -1) INTO V_P_SYSDATE FROM DUAL; --获取上个月的日期

V_YEAR := EXTRACT(YEAR FROM V_P_SYSDATE);  --年
V_MONTH := EXTRACT(MONTH FROM V_P_SYSDATE);--月

OPEN C_FACILITY_ID;--打开游标
LOOP--开始循环游标
FETCH C_FACILITY_ID
INTO R_FACILITY_ID;
EXIT WHEN C_FACILITY_ID%NOTFOUND;
COST_MES_SL_INIT_SP(R_FACILITY_ID.FACILITY_ID, V_YEAR, V_MONTH);  --调用另一个存储过程
END LOOP;  --结束循环
CLOSE C_FACILITY_ID;   --关闭游标
COMMIT;
END COST_MES_SL_INIT_SP_001;