declare
V_FIRST NUMBER := 0;
begin
DBMS_OUTPUT.put_line('######执行初始化####');
for i in (select t.id,t.name from t_rpt_jys t ) loop
V_FIRST := i.id ;
DBMS_OUTPUT.put_line(V_FIRST);
end loop;
end;复杂一点例子
DECLARE
BEGIN_DATE VARCHAR2(32);
V_FIRST NUMBER := 0;
V_COUNT1 NUMBER := 0;
V_COUNT NUMBER := 0;
V_COUNT_SUB NUMBER := 0;
V_PRE_DAY_NET_AMOUNT NUMBER := 0;
V_MEMBER_ID COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.MEMBER_ID%TYPE;
V_ACCOUNT_ID COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.ACCOUNT_ID%TYPE;
V_BUSINESS_DATE COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.BUSINESS_DATE%TYPE;
V_BALANCE COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.BALANCE%TYPE; -- 账户余额
V_ACCRUAL COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.ACCRUAL%TYPE; -- 发生额
V_CHANNEL_FEE COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.SETTLE_CHANNEL_FEE%TYPE; -- 结算渠道费
V_NET_AMOUNT COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT.NET_AMOUNT%TYPE; -- 净额
BEGIN
DBMS_OUTPUT.put_line('######初始化日报表信息开始，请务必等待执行结束####');
for I in (SELECT A.MEMBER_ID,
M.MEMBER_NAME,
A.ACCOUNT_ID,
A.CREATE_TIME,
A.ACCOUNT_TYPE
FROM MEMBER.TR_MEMBER_ACCOUNT A, MEMBER.TM_MEMBER M
WHERE A.MEMBER_ID = M.MEMBER_ID
AND A.ACCOUNT_ID IS NOT NULL
ORDER BY A.CREATE_TIME ASC) LOOP
BEGIN_DATE := TO_CHAR(I.CREATE_TIME, 'YYYY-MM-DD');
WHILE BEGIN_DATE < TO_CHAR(SYSDATE, 'YYYY-MM-DD') LOOP
-- 发生额
SELECT --T.ACCOUNT_NO,
NVL(TT.SUM_TXN_AMT, '0.00')
INTO V_ACCRUAL
FROM DPM.T_DPM_OUTER_ACCOUNT T
LEFT JOIN (SELECT TT1.ACCOUNT_NO,
(SUM_TXN_AMT1 - SUM_TXN_AMT2) SUM_TXN_AMT
FROM (SELECT T1.ACCOUNT_NO, SUM(T1.TXN_AMT) SUM_TXN_AMT1
FROM DPM.T_DPM_OUTER_ACCOUNT_DETAIL T1
WHERE T1.DIRECTION = '1'
AND T1.TXN_TIME <
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') + 1
AND T1.TXN_TIME >
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss')
GROUP BY T1.ACCOUNT_NO) TT1
JOIN (SELECT T2.ACCOUNT_NO, SUM(T2.TXN_AMT) SUM_TXN_AMT2
FROM DPM.T_DPM_OUTER_ACCOUNT_DETAIL T2
WHERE T2.DIRECTION = '2'
AND T2.TXN_TIME <
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') + 1
AND T2.TXN_TIME >
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss')
GROUP BY T2.ACCOUNT_NO) TT2
ON TT1.ACCOUNT_NO = TT2.ACCOUNT_NO) TT
ON T.ACCOUNT_NO = TT.ACCOUNT_NO
WHERE T.ACCOUNT_NO = I.ACCOUNT_ID;
-- 结算渠道费
SELECT --TT1.ACCOUNT_NO,
(NVL(TT1.SUB_AMT, '0.00') - NVL(TT2.SUB_AMT, '0.00'))
INTO V_CHANNEL_FEE
FROM (SELECT O1.ACCOUNT_NO, T2.SUB_AMT
FROM DPM.T_DPM_OUTER_ACCOUNT O1
LEFT JOIN (SELECT UNIQUE T1.ACCOUNT_NO,
T1.DIRECTION,
SUM(T1.AMOUNT) OVER(PARTITION BY T1.ACCOUNT_NO, T1.DIRECTION) SUB_AMT
FROM DPM.T_DPM_ACCOUNT_ENTRY T1
WHERE T1.DIRECTION = '1'
AND t1.LAST_UPDATE_TIME <
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') + 1
AND t1.LAST_UPDATE_TIME >
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss')
AND T1.SYS_TRACE_NO LIKE 'FEE%') T2
ON O1.ACCOUNT_NO = T2.ACCOUNT_NO) TT1
LEFT JOIN (SELECT O1.ACCOUNT_NO, T2.SUB_AMT
FROM DPM.T_DPM_OUTER_ACCOUNT O1
LEFT JOIN (SELECT UNIQUE T1.ACCOUNT_NO,
T1.DIRECTION,
SUM(T1.AMOUNT) OVER(PARTITION BY T1.ACCOUNT_NO, T1.DIRECTION) SUB_AMT
FROM DPM.T_DPM_ACCOUNT_ENTRY T1
WHERE T1.DIRECTION = '2'
AND t1.LAST_UPDATE_TIME <
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') + 1
AND t1.LAST_UPDATE_TIME >
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss')
AND T1.SYS_TRACE_NO LIKE 'FEE%') T2
ON O1.ACCOUNT_NO = T2.ACCOUNT_NO) TT2
ON TT1.ACCOUNT_NO = TT2.ACCOUNT_NO
WHERE TT2.ACCOUNT_NO = I.ACCOUNT_ID;
-- 账户余额
SELECT AFTER_AMT
INTO V_BALANCE
FROM MEMBER.TR_MEMBER_ACCOUNT A
LEFT JOIN (select A.*
from (select AD.*,
row_number() over(partition by AD.ACCOUNT_NO order by AD.TXN_TIME desc) rw
from DPM.T_DPM_OUTER_ACCOUNT_DETAIL AD
WHERE AD.TXN_TIME <
TO_DATE(BEGIN_DATE || ' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') + 1) A
where A.rw = 1) T
ON A.ACCOUNT_ID = T.ACCOUNT_NO
WHERE A.ACCOUNT_ID = I.ACCOUNT_ID;
----为空的情况--
IF V_BALANCE IS NULL THEN
---查询
SELECT COUNT(1)
INTO V_COUNT
FROM DPM.T_DPM_OUTER_ACCOUNT_DETAIL CO
WHERE CO.ACCOUNT_NO = I.ACCOUNT_ID;
IF V_COUNT = 0 THEN
SELECT COUNT(1)
INTO V_COUNT_SUB
from dpm.t_dpm_outer_account_subset da
where da.account_no = I.ACCOUNT_ID;
IF V_COUNT_SUB = 0 THEN
V_BALANCE := 0.00;
ELSE
select SUM(DA.BALANCE)
INTO V_BALANCE
from dpm.t_dpm_outer_account_subset da
where da.account_no = I.ACCOUNT_ID;
END IF;
ELSE
SELECT BEFORE_AMT
INTO V_BALANCE
FROM (SELECT DO.BEFORE_AMT
FROM DPM.T_DPM_OUTER_ACCOUNT_DETAIL DO
WHERE DO.ACCOUNT_NO = I.ACCOUNT_ID
ORDER BY DO.TXN_TIME ASC)
WHERE ROWNUM = 1;
END IF;
END IF;
-- 净额
SELECT COUNT(1)
INTO V_FIRST
FROM COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT BAL
WHERE BAL.ACCOUNT_ID = I.ACCOUNT_ID
AND TO_CHAR(BAL.BUSINESS_DATE, 'YYYY-MM-DD') =
TO_CHAR(TO_DATE(BEGIN_DATE, 'YYYY-MM-DD') - 1);
IF V_FIRST = 0 THEN
V_NET_AMOUNT := V_ACCRUAL - V_CHANNEL_FEE;
ELSE
SELECT NVL(NET_AMOUNT, 0)
INTO V_PRE_DAY_NET_AMOUNT
FROM COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT BAL
WHERE TO_CHAR(BAL.BUSINESS_DATE, 'YYYY-MM-DD') =
TO_CHAR(TO_DATE(BEGIN_DATE, 'YYYY-MM-DD') - 1);
V_NET_AMOUNT := V_ACCRUAL - V_CHANNEL_FEE + V_PRE_DAY_NET_AMOUNT;
END IF;
SELECT COUNT(1)
INTO V_COUNT
FROM COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT BAL
WHERE BAL.ACCOUNT_ID = I.ACCOUNT_ID
AND TO_CHAR(BAL.BUSINESS_DATE, 'YYYY-MM-DD') = BEGIN_DATE;
IF V_COUNT = 0 THEN
INSERT INTO COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT
(ID,
MEMBER_ID,
MEMBER_NAME,
ACCOUNT_ID,
ACCOUNT_TYPE,
BUSINESS_DATE,
ACCRUAL,
SETTLE_CHANNEL_FEE,
BALANCE,
NET_AMOUNT,
CREATE_DATE)
VALUES
(COUNTER.SEQ_TB_MARKET_DAY_REPORT.NEXTVAL,
I.MEMBER_ID,
I.MEMBER_NAME,
I.ACCOUNT_ID,
I.ACCOUNT_TYPE
TO_DATE(BEGIN_DATE || ' 05:00:00', 'yyyy-mm-dd hh24:mi:ss'),
V_ACCRUAL,
V_CHANNEL_FEE,
V_BALANCE,
V_NET_AMOUNT,
SYSDATE);
ELSE
UPDATE COUNTER.TB_MARKET_ACCOUNT_DAY_REPORT BAL
SET BAL.ACCRUAL = V_ACCRUAL,
BAL.SETTLE_CHANNEL_FEE = V_CHANNEL_FEE,
BAL.BALANCE = V_BALANCE
WHERE BAL.ACCOUNT_ID = I.ACCOUNT_ID
AND TO_CHAR(BAL.BUSINESS_DATE, 'YYYY-MM-DD') = BEGIN_DATE;
END IF;
BEGIN_DATE := TO_CHAR(TO_DATE(BEGIN_DATE, 'YYYY-MM-DD') + 1,
'YYYY-MM-DD');
COMMIT;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line('######初始化日报表信息结束######');
END;
