oracle 游标--FOR循环实现
CREATE OR REPLACE PROCEDURE TOPUP.sp_card_paystat (currentDate IN DATE)
AS
v_paystat_id NUMBER (19); --查询供应商ID
v_count NUMBER; --查询充值卡数量
v_otype VARCHAR2 (50); --查询充值卡类型
gatherBeginDate DATE; --统计开始日期
gatherEndDate DATE; --统计结束日期
BEGIN
v_paystat_id := 0; --查询供应商ID
v_count := 0; --查询充值卡数量
v_otype := '0'; --查询充值卡类型
gatherBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --统计开始日期
gatherEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --统计结束日期
AS
v_paystat_id NUMBER (19); --查询供应商ID
v_count NUMBER; --查询充值卡数量
v_otype VARCHAR2 (50); --查询充值卡类型
gatherBeginDate DATE; --统计开始日期
gatherEndDate DATE; --统计结束日期
BEGIN
v_paystat_id := 0; --查询供应商ID
v_count := 0; --查询充值卡数量
v_otype := '0'; --查询充值卡类型
gatherBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --统计开始日期
gatherEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --统计结束日期
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
--统计开始日期
--SELECT TRUNC (currentDate - 25) INTO gatherBeginDate FROM DUAL;
--SELECT TRUNC (currentDate - 25) INTO gatherBeginDate FROM DUAL;
SELECT TRUNC (currentDate - 1) INTO gatherBeginDate FROM DUAL;
--统计结束日期
--SELECT TRUNC (currentDate - 24) INTO gatherEndDate FROM DUAL;
--SELECT TRUNC (currentDate - 24) INTO gatherEndDate FROM DUAL;
SELECT TRUNC (currentDate) INTO gatherEndDate FROM DUAL;
--定义游标
DECLARE
CURSOR card_paystat
IS
SELECT *
FROM ( SELECT c.supply_id,
i.parvalue,
i.operator_id,
COUNT ( * ) AS total
FROM tab_topup_mobile_cardpay c,
tab_topup_mobile_carditem i
WHERE c.id = i.card_id
AND i.ttf = 1
AND i.status_id = 43
AND c.supply_id = 22
AND c.finisheddate >= gatherBeginDate
AND c.finisheddate <= gatherEndDate
GROUP BY c.supply_id, i.parvalue, i.operator_id) ic;
c_paystat card_paystat%ROWTYPE;
--operatorid ic.operator_id%type;
--operatorid ic.operator_id%type;
--开始使用游标取数据
BEGIN
FOR c_paystat IN card_paystat
LOOP
SELECT SEQ_CARD_PAYSTAT.NEXTVAL INTO v_paystat_id FROM DUAL;
FOR c_paystat IN card_paystat
LOOP
SELECT SEQ_CARD_PAYSTAT.NEXTVAL INTO v_paystat_id FROM DUAL;
CASE
WHEN c_paystat.operator_id = '75'
THEN
v_otype := 'sz';
WHEN c_paystat.operator_id = '76'
THEN
v_otype := 'lt';
WHEN c_paystat.operator_id = '77'
THEN
v_otype := 'dx';
END CASE;
INSERT INTO TAB_CARD_PAYSTAT (ID,
STAT_START_DATE,
STAT_END_DATE,
CARD_LIMIT,
SUPPLY_ID,
CARD_TYPE,
COUNT,
VALID,
CREATEDATE,
LASTDATE)
VALUES (v_paystat_id,
gatherBeginDate,
gatherEndDate,
c_paystat.parvalue,
c_paystat.supply_id,
v_otype,
c_paystat.total,
1,
SYSDATE,
NULL);
STAT_START_DATE,
STAT_END_DATE,
CARD_LIMIT,
SUPPLY_ID,
CARD_TYPE,
COUNT,
VALID,
CREATEDATE,
LASTDATE)
VALUES (v_paystat_id,
gatherBeginDate,
gatherEndDate,
c_paystat.parvalue,
c_paystat.supply_id,
v_otype,
c_paystat.total,
1,
SYSDATE,
NULL);
COMMIT;
END LOOP;
END;
--发生异常时返回错误码
END LOOP;
END;
--发生异常时返回错误码
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'AUTO CARD PAY GATHER ERROR,PLEASE OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END;
/
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'AUTO CARD PAY GATHER ERROR,PLEASE OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-714884/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
自动报表空值处理
请登录后发表评论
登录
全部评论
