卡自动提交游标实现
1. 卡自动提交SP
CREATE OR REPLACE PROCEDURE TOPUP.sp_card_scan_auditcommit (
currentDate IN DATE
)
AS
v_id INTEGER; --查询扫描卡序列号
v_batch_id INTEGER; --查询扫描卡批次号
v_count NUMBER (19); --查询扫描卡计时器
v_cardpay_id NUMBER (19); -- TAB_TOPUP_MOBILE_CARDPAY ID
v_carditem_id NUMBER (19); -- TAB_TOPUP_MOBILE_CARDITEM ID
v_scan_card_limit NUMBER (19); --查询扫描卡金额
v_balance NUMBER (19); --查询扫描卡金额
v_balancepay NUMBER (19); --查询扫描卡金额
v_parvalue NUMBER (19); --查询扫描卡金额
v_paymoney NUMBER (19); --查询扫描卡金额
v_card_code VARCHAR2 (50); --查询扫描卡序列号
v_sn VARCHAR2 (50); --查询扫描卡序列号
v_card_pwd VARCHAR2 (50); --查询扫描卡密码
v_password VARCHAR2 (50); --查询扫描卡密码
v_orderid VARCHAR2 (100); --TTF|年|月|日|分|秒|1xxx 总共19位
v_serialnumber VARCHAR2 (30); --T|年|月|日|分|秒|1xxx 总共17位
v_card_batch_otype VARCHAR2 (10);
v_otype VARCHAR2 (50);
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
v_operator_id VARCHAR2 (10);
commitBeginDate DATE; --审核开始日期
commitEndDate DATE; --审核结束日期
BEGIN
v_id := 0; --查询充值卡序列号
v_batch_id := 0; --查询扫描卡批次号
v_count := 1000;
v_cardpay_id := 0;
v_carditem_id := 0;
v_scan_card_limit := 0; --查询扫描卡金额
v_balance := 0; --查询扫描卡金额
v_balancepay := 0; --查询扫描卡金额
v_parvalue := 0; --查询扫描卡金额
v_paymoney := 0; --查询扫描卡金额
v_card_code := '0'; --查询扫描卡金额
v_sn := '0'; --查询扫描卡金额
v_card_pwd := '0'; --查询扫描卡密码
v_password := '0'; --查询扫描卡密码
v_orderid := '0'; --TTF|年|月|日|分|秒|1xxx 总共19位
v_serialnumber := '0'; --T|年|月|日|分|秒|1xxx 总共17位
v_card_batch_otype := '0';
v_otype := '0';
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
v_operator_id := '0';
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
commitBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --审核开始日期
commitEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --审核结束日期
currentDate IN DATE
)
AS
v_id INTEGER; --查询扫描卡序列号
v_batch_id INTEGER; --查询扫描卡批次号
v_count NUMBER (19); --查询扫描卡计时器
v_cardpay_id NUMBER (19); -- TAB_TOPUP_MOBILE_CARDPAY ID
v_carditem_id NUMBER (19); -- TAB_TOPUP_MOBILE_CARDITEM ID
v_scan_card_limit NUMBER (19); --查询扫描卡金额
v_balance NUMBER (19); --查询扫描卡金额
v_balancepay NUMBER (19); --查询扫描卡金额
v_parvalue NUMBER (19); --查询扫描卡金额
v_paymoney NUMBER (19); --查询扫描卡金额
v_card_code VARCHAR2 (50); --查询扫描卡序列号
v_sn VARCHAR2 (50); --查询扫描卡序列号
v_card_pwd VARCHAR2 (50); --查询扫描卡密码
v_password VARCHAR2 (50); --查询扫描卡密码
v_orderid VARCHAR2 (100); --TTF|年|月|日|分|秒|1xxx 总共19位
v_serialnumber VARCHAR2 (30); --T|年|月|日|分|秒|1xxx 总共17位
v_card_batch_otype VARCHAR2 (10);
v_otype VARCHAR2 (50);
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
v_operator_id VARCHAR2 (10);
commitBeginDate DATE; --审核开始日期
commitEndDate DATE; --审核结束日期
BEGIN
v_id := 0; --查询充值卡序列号
v_batch_id := 0; --查询扫描卡批次号
v_count := 1000;
v_cardpay_id := 0;
v_carditem_id := 0;
v_scan_card_limit := 0; --查询扫描卡金额
v_balance := 0; --查询扫描卡金额
v_balancepay := 0; --查询扫描卡金额
v_parvalue := 0; --查询扫描卡金额
v_paymoney := 0; --查询扫描卡金额
v_card_code := '0'; --查询扫描卡金额
v_sn := '0'; --查询扫描卡金额
v_card_pwd := '0'; --查询扫描卡密码
v_password := '0'; --查询扫描卡密码
v_orderid := '0'; --TTF|年|月|日|分|秒|1xxx 总共19位
v_serialnumber := '0'; --T|年|月|日|分|秒|1xxx 总共17位
v_card_batch_otype := '0';
v_otype := '0';
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
v_operator_id := '0';
--OTYPE的值：sz：代表移动：75 lt：代表联通：76 dx：代表电信： 77
commitBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --审核开始日期
commitEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD'); --审核结束日期
--审核开始日期
SELECT TRUNC (currentDate - 3) INTO commitBeginDate FROM DUAL;
--审核结束日期
SELECT TRUNC (currentDate + 1) INTO commitEndDate FROM DUAL;
SELECT TRUNC (currentDate + 1) INTO commitEndDate FROM DUAL;
--定义游标
DECLARE
CURSOR card_commit
IS
SELECT *
FROM (SELECT card.id,
card.batch_id,
card.card_limit AS paymoney,
card.card_limit AS balance,
card.card_limit AS balancepay,
card.card_limit AS parvalue,
card.card_code,
card.card_pwd,
cbatch.otype
FROM TAB_SCAN_CARD card, TAB_SCAN_CARD_BATCH cbatch
WHERE card.batch_id = cbatch.id
AND card.SUB_FLAG = 0
AND card.createdate >= commitBeginDate
AND card.createdate <= commitEndDate)
WHERE ROWNUM <= 250;
--开始使用游标取数据
BEGIN
OPEN card_commit;
OPEN card_commit;
LOOP
FETCH card_commit
INTO
v_id, v_batch_id, v_paymoney, v_balance, v_balancepay, v_parvalue, v_sn, v_password, v_otype;
FETCH card_commit
INTO
v_id, v_batch_id, v_paymoney, v_balance, v_balancepay, v_parvalue, v_sn, v_password, v_otype;
--游标取不到数据则退出
EXIT WHEN card_commit%NOTFOUND;
EXIT WHEN card_commit%NOTFOUND;
v_count := v_count + 1;
CASE
WHEN v_otype = 'sz'
THEN
v_operator_id := '75';
WHEN v_otype = 'lt'
THEN
v_operator_id := '76';
WHEN v_otype = 'dx'
THEN
v_operator_id := '77';
END CASE;
WHEN v_otype = 'sz'
THEN
v_operator_id := '75';
WHEN v_otype = 'lt'
THEN
v_operator_id := '76';
WHEN v_otype = 'dx'
THEN
v_operator_id := '77';
END CASE;
SELECT SEQ_TAB_TOPUP_MOBILECARDPAY.NEXTVAL
INTO v_cardpay_id
FROM DUAL;
INTO v_cardpay_id
FROM DUAL;
SELECT SEQ_TAB_TOPUP_MOBILECARDITEM.NEXTVAL
INTO v_carditem_id
FROM DUAL;
INTO v_carditem_id
FROM DUAL;
SELECT 'TTF' || TO_CHAR (SYSDATE, 'yyyymmddmiss') || v_count
INTO v_orderid
FROM DUAL;
INTO v_orderid
FROM DUAL;
SELECT 'T' || TO_CHAR (SYSDATE, 'yyyymmddmiss') || v_count
INTO v_serialnumber
FROM DUAL;
INTO v_serialnumber
FROM DUAL;
INSERT INTO tab_topup_mobile_cardpay (ID,
BALANCEPAY,
CREATEDATE,
FINISHEDDATE,
LASTDATE,
MOBILE,
ORDERID,
PAYMONEY,
REQUESTTIME,
SEND,
SENDCOUNT,
SENDTIME,
SENDURL,
USERNAME,
STATUS_ID,
SUPPLY_ID,
MONEY,
PRICE,
MSGPAY_ID,
PAYTYPE_ID,
SUBAREA1,
SUBAREA2,
PREPAID,
VERSION,
SERIALNUMBER,
SENDTYPE,
EMAIL,
LUCKY,
SPLIT,
SP,
DEFERRED,
SUBAREA3,
SUBAREA4,
BURSE,
NOTIFYURL,
NOTIFYSTATUS_ID)
VALUES (v_cardpay_id,
0,
SYSDATE,
'',
SYSDATE,
'',
v_orderid,
v_paymoney,
SYSDATE,
0,
0,
'',
'',
'',
43,
22,
0,
0,
NULL,
501,
'',
'',
0,
0,
v_serialnumber,
'background',
'',
0,
0,
0,
0,
'',
'',
'',
'',
NULL);
BALANCEPAY,
CREATEDATE,
FINISHEDDATE,
LASTDATE,
MOBILE,
ORDERID,
PAYMONEY,
REQUESTTIME,
SEND,
SENDCOUNT,
SENDTIME,
SENDURL,
USERNAME,
STATUS_ID,
SUPPLY_ID,
MONEY,
PRICE,
MSGPAY_ID,
PAYTYPE_ID,
SUBAREA1,
SUBAREA2,
PREPAID,
VERSION,
SERIALNUMBER,
SENDTYPE,
EMAIL,
LUCKY,
SPLIT,
SP,
DEFERRED,
SUBAREA3,
SUBAREA4,
BURSE,
NOTIFYURL,
NOTIFYSTATUS_ID)
VALUES (v_cardpay_id,
0,
SYSDATE,
'',
SYSDATE,
'',
v_orderid,
v_paymoney,
SYSDATE,
0,
0,
'',
'',
'',
43,
22,
0,
0,
NULL,
501,
'',
'',
0,
0,
v_serialnumber,
'background',
'',
0,
0,
0,
0,
'',
'',
'',
'',
NULL);
INSERT INTO tab_topup_mobile_carditem (ID,
BALANCE,
CREATEDATE,
IP,
LASTDATE,
LOCKTIME,
MOBILE,
PARVALUE,
PASSWORD,
PRICE,
SN,
VALID,
AREA_ID,
CARD_ID,
EMPLOYEE_ID,
OPERATOR_ID,
ITEM_ID,
STATUS_ID,
LOSS,
SUBCONTRACT,
SUBCONTRACTSTATUS_ID,
TTF,
DIALTIME,
REMATCH,
VERSION,
LOSSPARVALUE,
REDAIL,
REFERENCEPRICE,
scancard_id)
VALUES (v_carditem_id,
v_balance,
SYSDATE,
'',
SYSDATE,
'',
'',
v_parvalue,
v_password,
0,
v_sn,
1,
NULL,
475409,
NULL,
v_operator_id,
NULL,
43,
0,
0,
NULL,
'1',
'',
0,
0,
0,
0,
0,
v_id);
INSERT INTO tab_topup_mobile_cardpay_item (PAY_ID, CARDITEM_ID)
VALUES (v_cardpay_id, v_carditem_id);
UPDATE TAB_SCAN_CARD
SET sub_flag = 1
WHERE id = v_id;
SET sub_flag = 1
WHERE id = v_id;
COMMIT;
END LOOP;
END LOOP;
CLOSE card_commit;
END;
--发生异常时返回错误码
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'AUTOCOMMIT ERROR,PLEASE OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END;
/
END;
--发生异常时返回错误码
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'AUTOCOMMIT ERROR,PLEASE OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END;
/
2. 卡自动提交JOB
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'TOPUP.SP_CARD_SCAN_AUDITCOMMIT(SYSDATE);'
,next_date => to_date('09/01/2012 12:05:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+120/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'TOPUP.SP_CARD_SCAN_AUDITCOMMIT(SYSDATE);'
,next_date => to_date('09/01/2012 12:05:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+120/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-714604/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
审核数据库DDL操作记录实现
下一篇：
自动报表空值处理
请登录后发表评论
登录
全部评论
