账页程序源码(PL/SQL)

CREATE OR REPLACE PACKAGE BODY account AS

PROCEDURE ACCOUNTPAGE
(   
       P_ACCTDATE_FROM    IN  DATE,
       P_ACCTDATE_TO      IN  DATE,      
       P_ACCTNAME         IN  VARCHAR2,
       p_CURSOR           OUT mr
)
AS
       v_sqlstring    VARCHAR2(5000);
       v_temp         VARCHAR2(200);
       v_FACT_ACCT_ID INTEGER;                  --分录ID
       v_ACCTNAME     VARCHAR2(200);            --科目名称
       v_ACCTDATE     DATE;                     --日期
       v_AMTDR        NUMERIC(10,3) := 0.0;            --借方金额
       v_AMTCR        NUMERIC(10,3) := 0.0;            --贷方金额
       v_BALANCE      NUMERIC(10,3) := 0.0;            --余额 
       v_BALANCE_2    NUMERIC(10,3) := 0.0;
  BEGIN
       EXECUTE IMMEDIATE 'drop table T_TEMP';
       EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE T_TEMP
       (
              F_FACT_ACCT_ID INTEGER,                 --分录ID
              F_ACCTNAME     VARCHAR2(200),           --科目名称
              F_ACCTDATE     DATE,                    --日期
              F_AMTDR        NUMERIC(10,3),           --借方金额
              F_AMTCR        NUMERIC(10,3),           --贷方金额
              F_BALANCE      NUMERIC(10,3)            --余额
       )ON COMMIT delete ROWS';
       --DBMS_OUTPUT.put_line(v_BALANCE);
       v_sqlstring := 'SELECT FA.FACT_ACCT_ID,CE.NAME,FA.DATEACCT,FA.AMTACCTDR,FA.AMTACCTCR,0 FROM FACT_ACCT FA LEFT JOIN C_ELEMENTVALUE CE ON
                           FA.ACCOUNT_ID = CE.C_ELEMENTVALUE_ID AND CE.ISACTIVE = ''Y'' AND FA.ISACTIVE = ''Y''';                          

       IF (P_ACCTDATE_TO IS NOT NULL) THEN
          IF (P_ACCTDATE_FROM IS NOT NULL) THEN
               v_sqlstring := v_sqlstring || ' WHERE FA.DATEACCT <= TO_DATE('''|| TO_CHAR(P_ACCTDATE_TO,'yyyy-mm-dd')||''',''yyyy-mm-dd'') AND FA.DATEACCT >= TO_DATE('''|| TO_CHAR(P_ACCTDATE_FROM,'yyyy-mm-dd')||''',''yyyy-mm-dd'')';         
               --计算小于起始日期之前的余额
               SELECT SUM(FA.AMTACCTDR)-SUM(FA.AMTACCTCR) INTO v_BALANCE FROM FACT_ACCT FA WHERE FA.DATEACCT < TO_DATE(TO_CHAR(P_ACCTDATE_FROM,'yyyy-mm-dd'),'yyyy-mm-dd') AND FA.ISACTIVE = 'Y';
               IF (v_BALANCE IS NULL) THEN
                      v_BALANCE := 0.0;
                      --DBMS_OUTPUT.put_line(v_BALANCE ||'是空的');
               END IF;
          ELSE
               v_sqlstring := v_sqlstring || ' WHERE FA.DATEACCT <= TO_DATE('''|| TO_CHAR(P_ACCTDATE_TO,'yyyy-mm-dd')||''',''yyyy-mm-dd'')';
              
          END IF;      
       END IF;      
      
       IF (P_ACCTNAME IS NOT NULL) THEN
          v_sqlstring := v_sqlstring || ' WHERE CE.NAME = '|| P_ACCTNAME;         
       END IF;
      
       EXECUTE IMMEDIATE 'INSERT INTO T_TEMP ('||v_sqlstring||' )';     
      
       v_temp := 'SELECT * FROM T_TEMP ORDER BY F_ACCTDATE ';                   
       --定义游标        
       DECLARE
       TYPE v_type IS REF CURSOR;
       cv v_type;
       BEGIN      
       OPEN cv FOR
           v_temp;
           FETCH cv INTO v_FACT_ACCT_ID,v_ACCTNAME,v_ACCTDATE,v_AMTDR,v_AMTCR,v_BALANCE_2;      
          
       LOOP            
            v_BALANCE := v_BALANCE + v_AMTDR - v_AMTCR ;--计算余额
           
            IF (v_BALANCE <> 0) THEN
                EXECUTE IMMEDIATE 'UPDATE T_TEMP SET F_BALANCE = ' ||v_BALANCE||' WHERE F_FACT_ACCT_ID = '||v_FACT_ACCT_ID;            
            END IF;
            DBMS_OUTPUT.put_line(v_FACT_ACCT_ID||'  '||v_ACCTNAME||'  '||v_ACCTDATE||'  '||v_AMTDR||'  '||v_AMTCR||'  '||v_BALANCE);
            FETCH cv INTO v_FACT_ACCT_ID,v_ACCTNAME,v_ACCTDATE,v_AMTDR,v_AMTCR,v_BALANCE_2;
            EXIT WHEN cv%NOTFOUND;
       END LOOP;
      
      -- DBMS_OUTPUT.put_line(v_BALANCE);              
       CLOSE cv;
       END;                 
      --EXECUTE IMMEDIATE 'CREATE TABLE T_TEMP_TEST PARALLEL AS select * from t_temp ';
      OPEN p_CURSOR FOR
           SELECT * FROM T_TEMP;
     -- COMMIT;   
  END;
END account;
/



CREATE OR REPLACE PACKAGE account AS
  TYPE mr IS REF CURSOR;
  PROCEDURE ACCOUNTPAGE(P_ACCTDATE_FROM DATE,P_ACCTDATE_TO DATE,
          P_ACCTNAME VARCHAR2,p_CURSOR out mr);
END account;
/