一种报表输出的设计方法

DECLARE
uMID   VARCHAR2(20);
uName  VARCHAR2(20);
uJB    NUMBER; uZE    NUMBER; uCS    NUMBER; uSF   NUMBER;

uZE1   NUMBER; uCS1   NUMBER; uSF1   NUMBER;
uZE2   NUMBER; uCS2   NUMBER; uSF2   NUMBER;
uZE3   NUMBER; uCS3   NUMBER; uSF3   NUMBER;
uZE4   NUMBER; uCS4   NUMBER; uSF4   NUMBER;

uTGDS  NUMBER;   -- 退股点数
u1XB   NUMBER;   -- 1星币
u1XBA  NUMBER;   -- 1星币A,未股改点位
u2XB   NUMBER;   -- 2星以上奖,未股改点位
uYJJB  NUMBER;   -- 原奖金币
uYZJ   NUMBER;   -- 原总奖
uYWF   NUMBER;   -- 原未发
uXJ    NUMBER;   -- 星奖
uTJ    NUMBER;   -- 推荐
uKTJ   NUMBER;   -- 开拓奖

CURSOR CC IS
  SELECT FPID,TO_CHAR(FACTDATE,'YYYY-MM-DD') RQ,
    CASE
      WHEN FACTDATE >= TO_DATE('2016-09-01','YYYY-MM-DD') AND 
           FACTDATE <  TO_DATE('2016-10-03','YYYY-MM-DD') THEN 230 * 40
      WHEN FACTDATE >= TO_DATE('2016-10-03','YYYY-MM-DD') AND 
           FACTDATE <  TO_DATE('2017-03-08','YYYY-MM-DD') THEN 140 * 40
      WHEN FACTDATE >= TO_DATE('2017-03-08','YYYY-MM-DD') AND 
           FACTDATE <  TO_DATE('2017-07-05','YYYY-MM-DD') THEN  80 * 40
    END ZE
  FROM APP_STOCKDETAILBAK WHERE FMID = uMID
  ORDER BY FPID;

BEGIN
  uMID := 'M00002363';
  SELECT 0,0,0,0,0 INTO u1XB,uXJ,uYJJB,uYWF,uYZJ FROM DUAL;
  SELECT 0,0,0 INTO uZE1,uCS1,uSF1 FROM DUAL;
  SELECT 0,0,0 INTO uZE2,uCS2,uSF2 FROM DUAL;
  SELECT 0,0,0 INTO uZE3,uCS3,uSF3 FROM DUAL;
  SELECT 0,0,0 INTO uZE4,uCS4,uSF4 FROM DUAL;
  SELECT COUNT(*) INTO uTGDS FROM APP_STOCKDETAILBAK WHERE FMID = uMID;

  DBMS_OUTPUT.PUT_LINE(F_GetXM(uMID)||' ( '||uMID||' )退股');
  DBMS_OUTPUT.PUT_LINE(uTGDS||' 个点位退股');

  DBMS_OUTPUT.PUT_LINE(RPAD('点位号',16,' ') || RPAD('日期',16,' ') || LPAD('级别',6,' ') || LPAD('次数',6,' ') || LPAD('总额',8,' ') || LPAD('实发',8,' ') || LPAD('应发',8,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('-',68,'-'));
  FOR C IN CC LOOP
    SELECT NVL(SUM(FMCHANGE),0),COUNT(*) INTO uSF,uCS FROM APP_DAILY WHERE FTYPE = '星奖' AND FGRADE = 1 AND FPID = C.FPID;
    uZE := C.ZE;
    uCS1 := uCS1 + uCS;
    uSF1 := uSF1 + uSF;
    uZE1 := uZE1 + uZE;

    u1XB := u1XB + uZE - uSF;
    uYZJ := uYZJ + uZE;
    uYWF := uYWF + uZE - uSF;
    uXJ  := uXJ + uZE;

    DBMS_OUTPUT.PUT_LINE(RPAD(C.FPID,16,' ') || RPAD(C.RQ,16,' ') || LPAD(1,6,' ') || LPAD(uCS,6,' ') || LPAD(uZE,8,' ') || LPAD(uSF,8,' ') || LPAD(uZE - uSF,8,' ') );

    SELECT FGRADE INTO uJB FROM APP_POSITION WHERE FPID = C.FPID;
    SELECT NVL(SUM(FMCHANGE),0),COUNT(*) INTO uSF,uCS FROM APP_DAILY WHERE FTYPE = '星奖' AND FGRADE IN (2,3,4) AND FPID = C.FPID;
    IF (uJB > 1) THEN
      IF (uJB = 2) THEN
        uZE := 350 * 50;
        uCS2 := uCS2 + uCS;
        uSF2 := uSF2 + uSF;
        uZE2 := uZE2 + uZE;
      ELSIF (uJB = 3) THEN
        uZE := 720 * 50;
        uCS3 := uCS3 + uCS;
        uSF3 := uSF3 + uSF;
        uZE3 := uZE3 + uZE;
      ELSIF (uJB = 4) THEN
        uZE := 2710 * 50;
        uCS4 := uCS4 + uCS;
        uSF4 := uSF4 + uSF;
        uZE4 := uZE4 + uZE;
      END IF;
      uYJJB := uYJJB + uZE - uSF;
      uYZJ := uYZJ + uZE;
      uYWF := uYWF + uZE - uSF;
      uXJ  := uXJ + uZE;
      DBMS_OUTPUT.PUT_LINE(RPAD(C.FPID,16,' ') || RPAD(C.RQ,16,' ') || LPAD(uJB,6,' ') || LPAD(uCS,6,' ') || LPAD(uZE,8,' ') || LPAD(uSF,8,' ') || LPAD(uZE - uSF,8,' ') );
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(RPAD('-',68,'-'));


  DBMS_OUTPUT.PUT_LINE(RPAD('合计',32,' ') || LPAD(1,6,' ') || LPAD(uCS1,6,' ') || LPAD(uZE1,8,' ') || LPAD(uSF1,8,' ') || LPAD(uZE1-uSF1,8,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('合计',32,' ') || LPAD(2,6,' ') || LPAD(uCS2,6,' ') || LPAD(uZE2,8,' ') || LPAD(uSF2,8,' ') || LPAD(uZE2-uSF2,8,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('合计',32,' ') || LPAD(3,6,' ') || LPAD(uCS3,6,' ') || LPAD(uZE3,8,' ') || LPAD(uSF3,8,' ') || LPAD(uZE3-uSF3,8,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('合计',32,' ') || LPAD(4,6,' ') || LPAD(uCS4,6,' ') || LPAD(uZE4,8,' ') || LPAD(uSF4,8,' ') || LPAD(uZE4-uSF4,8,' '));

  DBMS_OUTPUT.PUT_LINE(RPAD('-',68,'-'));
  DBMS_OUTPUT.PUT_LINE(RPAD('点位号',16,' ') || RPAD('日期',16,' ') || LPAD('级别',6,' ') || LPAD('次数',6,' ') || LPAD('总额',8,' ') || LPAD('实发',8,' ') || LPAD('应发',8,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('-',68,'-'));

  SELECT NVL(SUM(FMONEY),0) INTO uTJ  FROM APP_MONEY_BACKUP_20171029 WHERE FTYPE = '推荐'   AND FMID = uMID;
  uYZJ := uYZJ + uTJ;
  SELECT NVL(SUM(FMONEY),0) INTO uKTJ FROM APP_MONEY_BACKUP_20171029 WHERE FTYPE = '开拓奖' AND FMID = uMID;
  uYZJ := uYZJ + uKTJ;

  DBMS_OUTPUT.PUT_LINE('现修改如下：');
  DBMS_OUTPUT.PUT_LINE(RPAD('-',30,'-'));
  DBMS_OUTPUT.PUT_LINE(RPAD('股改：',16,' ')||LPAD(0,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('星奖：',16,' ')||LPAD(uXJ,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('推荐：',16,' ')||LPAD(uTJ,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('开拓奖',16,' ')||LPAD(uKTJ,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('股改一星币：',16,' ')||LPAD(u1XB,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('原奖金币：',16,' ')||LPAD(uYJJB,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('原结算：',16,' ')||LPAD(0,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('原未发：',16,' ')||LPAD(uYWF,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('原总奖：',16,' ')||LPAD(uYZJ,6,' '));
  DBMS_OUTPUT.PUT_LINE(RPAD('-',30,'-'));
END;

输出结果如下