pl/sql编程简绍

简绍pl/sql编程的

https://www.cnblogs.com/defias/p/3334098.html

 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;