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;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
