--存储过程如下： CREATE OR REPLACE PROCEDURE get_infoco( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量 --定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN Dbms_Output.put_line(' 222222 '); BEGIN --在往来账表t_gl_acctcussent存在 SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID AND EXISTS(SELECT 1 FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=T_GL_VoucherAssistRecord.FID); --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); EXCEPTION WHEN no_data_found THEN --在往来账表t_gl_acctcussent存在 --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END; END IF; END LOOP; CLOSE cur; --最后对临时表数据进行分类汇总 OPEN resultSet FOR select FcstmName 客户或供应商, FDescription 摘要, CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限, CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Sum_FEndBalanceFor ELSE NULL END 贷方 FROM( SELECT FcstmName,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY FcstmName) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY FcstmName ORDER BY FBizDate DESC ) rn FROM tempco) WHERE rn=1; COMMIT; END;
--测试： Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --创建会话级临时表 SQL> CREATE global temporary TABLE tempco 2 ( 3 FAssistGrpID VARCHAR2(44), 4 FBeginBalanceFor NUMBER(19,4), 5 FEndBalanceFor NUMBER(19,4), 6 FCstmNum NVARCHAR2(80) , 7 FCstmName NVARCHAR2(255), 8 FSplrNum NVARCHAR2(80) , 9 FSplrName NVARCHAR2(255), 10 ForgNum NVARCHAR2(80), 11 ForgName NVARCHAR2(255), 12 FPrdBegDate TIMESTAMP(6), 13 FPrdEndDate TIMESTAMP(6), 14 FGLVARID VARCHAR2(44), 15 FBillID VARCHAR2(44), 16 FDescription NVARCHAR2(200), 17 FBizDate TIMESTAMP(6), 18 FVouchNum NVARCHAR2(80), 19 FVouchBizDate TIMESTAMP(6) , 20 FVouchBookedDate TIMESTAMP(6) 21 ) on commit preserve rows ;Table created.SQL> set serveroutput on SQL> col 客户或供应商 format a8 SQL> col 摘要 format a30 SQL> col 发生年限 format a8 SQL> col 借方 format 9999 SQL> col 贷方 format 9999 SQL> var cur refcursor SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供 摘要 发生年限 借方 贷方 -------- ------------------------------ -------- ----- ----- 林金兰 cxf20100108做的凭证客户林金兰 小于1年 2100 陈凤 小于1年 1500SQL>
--修改了下过程中一些问题：逻辑二，逐笔循环判断是否在往来账表t_gl_acctcussent中存在 --之前逻辑理解错了，你现在看看呢 CREATE OR REPLACE PROCEDURE get_infoco( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量 --定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE; v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ) LOOP v_FGLVARID := rst.FGLVARID; v_FBillID := rst.FBillID; v_FDescription := rst.FDescription; v_FBizDate := rst.FBizDate; --逐笔循环判断 --判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID; IF v_count>0 THEN --存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); ELSE --不存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF ; END LOOP; EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; END LOOP; CLOSE cur; --最后对临时表数据进行分类汇总 OPEN resultSet FOR select FcstmName 客户或供应商, FDescription 摘要, CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限, CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Sum_FEndBalanceFor ELSE NULL END 贷方 FROM( SELECT FcstmName,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY FcstmName) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY FcstmName ORDER BY FBizDate DESC ) rn FROM tempco) WHERE rn=1; COMMIT; END; /
谢谢gelyon！ 具体问题请查看链接http://fannycen.bokee.com/viewdiary.232605011.html，因为CSDN不能贴图的... 我看了，逻辑基本上按我讲的思路来做，通过调试还有三个地方的逻辑需要修正： 1： IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN...END 里的语句： SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last这句语句中如果有相同记账日期，还要比较T_GL_voucher的Fcreatetime字段（创建时间，timestamp类型），取最近创建时间的那条凭证。 所以判断条件还要加上这种情况。2： --判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID;这个语句还需要条件：T_GL_AcctCussent.FcompanyID 与T_org_baseUnit.fid关联取得T_org_baseUnit.Fnumber=入参ForgNum 以及T_GL_AcctCussent.FisInit=1 3：对临时表数据进行分类汇总部分： (1)只是按FcstmName进行了分类汇总，没有分类汇总到供应商FPrvrName. 因为临时表tempco有一个特别：在一行记录中FcstmName/FSplrName字段不会同时有内容。要么这两个字段都为空；要么FcstmName为空而FSplrName不为空；要么FCstmName不为空而FSplrName为空。 所以需要同时汇总FcstmName和FPrvrName. 我不知道思路是怎么样做？ 假设临时表tempco的内容如下： 汇总出来应该是：不知道可不可以用unit all来连接两个汇总？(2)查询日期与日期判断。我的条件是如果记账日期非空，则与FvouchBookedate相减，否则与业务日期FBizDate相减比较。当然也可以在设计临时表时设计一个临时日期字段来存储凭证的记账日期（存在凭证时），或者存储往来账导入时的业务日期（不存在凭证，初始数值是导入的），或者存储当期会计期间的结束日期FprdEndDate（不存在凭证，初始数值是手工录入），这样临时表的这几个值都可以不要。最后在分类汇总时用入参的查询日期与这个临时日期字段来比较就可以。 4：临时表tempco在分类汇总后不用删除回收吗？
--我过程代码多了，回复时候报： 回复内容过长！ --因此分开来 --过程如下： CREATE OR REPLACE PROCEDURE get_infoco( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量--定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%'; --定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE; v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last --这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ) LOOP v_FGLVARID := rst.FGLVARID; v_FBillID := rst.FBillID; v_FDescription := rst.FDescription; v_FBizDate := rst.FBizDate; --逐笔循环判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1 AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN --存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); ELSE --不存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF ; END LOOP; EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; END LOOP; CLOSE cur;--未完，接下面
--接上面！ --最后对临时表数据进行分类汇总 OPEN resultSet FOR SELECT Fcstm_FSplr_Name 客户或供应商, FDescription 摘要, Decode(FvouchbookedDate,NULL, CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END , CASE WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END ) 发生年限, CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方 FROM( SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL ) WHERE rn=1; COMMIT; END; /--存储过程到此结果！
--测试： Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on SQL> set linesize 32767 SQL> col 客户或供应商 format a20 SQL> col 摘要 format a40 SQL> col 发生年限 format a20 SQL> col 借方 format 999999 SQL> col 贷方 format 999999 SQL> var cur refcursor SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed. SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 广东世源集团 20101213做的凭证辅助账 小于1年 4800 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 20101213做的凭证 小于1年 5800 SQL> conn ucnzp/ucnzp@cdfn01 Connected. SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 小于1年 1000SQL>
--我这里不会有错啊，我是用工具SQLTools创建的，SQLPLUS中我也能创建成功， --可能是游标申明必须在类型变量申明之前吧，你将rs T_table 这个申明写到 cursor申明后面试试--这样吧，我来总结下，下面是总结版，最终版，你直接按照下面来测试就行了，前面回复的都不管 --由于回复内容比较长，分开回复--第一步：创建会话级临时表DROP TABLE tempco;CREATE global temporary TABLE tempco ( FAssistGrpID VARCHAR2(44), FBeginBalanceFor NUMBER(19,4), FEndBalanceFor NUMBER(19,4), FCstmNum NVARCHAR2(80) , FCstmName NVARCHAR2(255), FSplrNum NVARCHAR2(80) , FSplrName NVARCHAR2(255), ForgNum NVARCHAR2(80), ForgName NVARCHAR2(255), FPrdBegDate TIMESTAMP(6), FPrdEndDate TIMESTAMP(6), FGLVARID VARCHAR2(44), FBillID VARCHAR2(44), FDescription NVARCHAR2(200), FBizDate TIMESTAMP(6), FVouchNum NVARCHAR2(80), FVouchBizDate TIMESTAMP(6) , FVouchBookedDate TIMESTAMP(6) ) on commit preserve rows ;--第二步：创建过程： CREATE OR REPLACE PROCEDURE get_infoco( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量 --定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE; v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last --这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ) LOOP v_FGLVARID := rst.FGLVARID; v_FBillID := rst.FBillID; v_FDescription := rst.FDescription; v_FBizDate := rst.FBizDate; --逐笔循环判断 --判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1 AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN --存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); ELSE --不存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF ; END LOOP; EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; END LOOP; CLOSE cur;--过程未完，接下面
--过程继续，接上面 --最后对临时表数据进行分类汇总 --这里我修改了下，根据Nvl(FvouchbookedDate,p_FSearchDate)来判断发生年限，减少了代码量 OPEN resultSet FOR SELECT Fcstm_FSplr_Name 客户或供应商, FDescription 摘要, CASE WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 , CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方 FROM( SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL ) WHERE rn=1; COMMIT; END; /--过程到此结束！ --准备工作也已完成，下面是测试！--第三步：测试：--SQLPLUS下测试： Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on SQL> set linesize 32767 SQL> col 客户或供应商 format a20 SQL> col 摘要 format a40 SQL> col 发生年限 format a20 SQL> col 借方 format 999999 SQL> col 贷方 format 999999 SQL> var cur refcursor SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed. SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 广东世源集团 20101213做的凭证辅助账 小于1年 4800 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 20101213做的凭证 小于1年 5800 SQL> conn ucnzp/ucnzp@cdfn01 Connected. SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 小于1年 1000SQL>
回paddy,结构基本完整，谢谢。经测试有一些需要加强判断： 1） IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )这里的select ....from t_gl_voucherAssistRecord where ...筛出来可能会是空的。这个需要做判断一下，如果为空那就直接插入临时表，跳到下面部分的语句，否则按按步执行：--不存在 向临时表插入数据: INSERT INTO gxyjTemptable(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
2) --第一种情况:如果辅助账余额表的初始余额与期末余额不一致,而且期末余额不为0.则表示该条记录肯定有凭证存在.因为期末余额=期初余额+多张凭证辅助账数额 ---需要反查到关联了 多少张凭证,并取出这些凭证记账日期是最新的那条凭证记录;如果相同记账日期有多条,则取创建时间最新的那条凭证. IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_Voucher.FBizStatus=5 and T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last , T_GL_Voucher.Fcreatetime DESC nulls last )WHERE rn=1; 这里的orderby 我这样写得对不对？因为Fcreatetime并不在select 后。 我这边还需要加一个条件.即T_GL_voucher.FcompanyID 关联 T_org_baseUnit.FID 取T_org_baseUnit.Fnumber=入参p_ForgNum 3)汇总部分： CASE WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年' WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 这里，条件应该是：如果FvouchBookedDate为空，则year(P_FsearchDate)-year(FBizDate) 如果FvouchBookedDate非空，取year(P_FsearchDate)-year(vouchBookedDate)
呵呵，paddy确实是oracle开发高手呀~~挺厉害！
--ok~\(≧▽≦)/~啦啦啦 --你再测试下：CREATE OR REPLACE PROCEDURE get_infoco( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量 --定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE; v_count NUMBER := 0 ; --记录是否在往来账表中存在！ v_cnt NUMBER := 0 ; --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ORDER BY FVouchBookedDate DESC nulls last --这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！ SELECT Count(*) INTO v_cnt FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID; --存在 逐笔循环再判断是否在往来账表t_gl_acctcussent中存在 IF v_cnt >0 THEN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ) LOOP v_FGLVARID := rst.FGLVARID; v_FBillID := rst.FBillID; v_FDescription := rst.FDescription; v_FBizDate := rst.FBizDate; --逐笔循环判断 --判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1 AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN --存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); ELSE --不存在 向临时表插入数据: INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF ; END LOOP; ELSE --v_cnt = 0 直接将该笔rs.FAssistGrpID插入临时表 INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF; EXCEPTION WHEN others THEN Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; END LOOP; CLOSE cur;--未完，接下面
--接上面！ --最后对临时表数据进行分类汇总 --这里的发生年限按照你信需求修改了 OPEN resultSet FOR SELECT Fcstm_FSplr_Name 客户或供应商, FDescription 摘要, CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 , CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方 FROM( SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL ) WHERE rn=1; COMMIT; END; /--过程结束！
paddy辛苦了！不胜感激！经过实际数据测试，发现我之前的逻辑不完整，分类汇总还需要再加上另一个表T_GL_initAssistBalance的汇总值。 T_GL_initAssistBalance的测试数据如下：--辅助账初始化表T_GL_initASSISTBALANCE --字段说明（依次）：单位ID（T_GL_initASSISTBALANCE.ForgUnitID=T_org_baseUnit.Fid)、、科目ID（FAccountID=T_bd_AccountView.FID）、辅助账横表ID （FAssistgrpID=t_bd_assistantHG.FID）、期初余额、期末累计余额create table T_GL_initASSISTBALANCE ( FORGUNITID VARCHAR2(44) not null, FACCOUNTID VARCHAR2(44) not null, FASSISTGRPID VARCHAR2(44) not null, FBEGINBALANCEFOR NUMBER(19,4) default 0 not null, FENDBALANCEFOR NUMBER(19,4) default 0 not null )insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID, FBEGINBALANCEFOR, FENDBALANCEFOR) values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'Db3EjfIxQqSoK9YA4Uk5IJ2pmCY=', '1eMNUUmfR925jC2W+GK5f0GKbLs=', 1000.0000, 1000.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID, FBEGINBALANCEFOR, FENDBALANCEFOR) values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', '4j8T4CrHRy+mdqWxGu2daJ2pmCY=', '2LC9qDgBShmzZzUWcjaNREGKbLs=', 1000.0000, 1000.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID, FBEGINBALANCEFOR, FENDBALANCEFOR) values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'ApBGyh1WS5yHiCHw3rxKIZ2pmCY=', 'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', 500.0000, 500.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID, FBEGINBALANCEFOR, FENDBALANCEFOR) values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'dsgoA3GERKej7F3Hiv51DJ2pmCY=', 'SYqPncg7Qd64uG8k5Ss67EGKbLs=', 500.0000, 500.0000); T_GL_initAssistBalance 与组织表t_org_baseUnit、科目表T_bd_AccountView、辅助账横表t_bd_assistantHG的关联如下：--假设以如下select 语句做为结果集tempDB --假设以如下select 语句做为结果集tempDB SELECT DISTINCT T_GL_initAssistBalance.FOrgUnitID, T_GL_initAssistBalance.FAccountID, T_GL_initAssistBalance.FAssistGrpID, T_GL_initAssistBalance.FBeginBalanceFor, T_GL_initAssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_initAssistBalance ON T_BD_AssistantHG.FID = T_GL_initAssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_initAssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_initAssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID WHERE and T_ORG_BaseUnit.FNumber =p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';
需要在存储过程的分类汇总部分加上TempDB的汇总数据，如下 SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn FROM gxyjTemptable WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL )这句，在sum(FendBalanceFor)后面还需加上sum(tempDB.FbeginBalanceFor)，条件是tempDB数据集的tempDB.ForgNumber =p_ForgNum AND tempDb.FAccountNum like p_FAccountNum||'%' 而且 tempDB.FHGCstmID=tempco.FHGCstmID或tempDB.FHGPrvdID=tempco.FHGPrvdID （注：26楼代码中tempCo临时表中还没有这两个字段，需要加上才得，要以此为分类汇总条件，目前以FprvdName、FcstmName为汇总条件不严谨，因为如果存在同名的人，那数据就不正确了）比如汇总客户FcstmName='陈凤',其FCstmID='f4A7uDShSpiuGP7F0mPflL8MBA4='，那么tempDB汇总条件就是tempDB.FHGCstmID='f4A7uDShSpiuGP7F0mPflL8MBA4='
--第二步：创建存储过程如下： CREATE OR REPLACE PROCEDURE get_infoco2( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 ,resultSet OUT sys_refcursor --返回结果集 ) IS --定义一个类型，用来指明中间表的结果 TYPE T_TABLE IS RECORD ( FBalType T_GL_AssistBalance.FBalType%TYPE, FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE, FPeriodID T_GL_AssistBalance.FPeriodID%TYPE, FAccountID T_GL_AssistBalance.FAccountID%TYPE, FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE, FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE, FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE, ForgNum T_ORG_BaseUnit.FNumber%TYPE, ForgName T_ORG_BaseUnit.FName_L2%TYPE, FPrdBegDate T_BD_Period.FBeginDate%TYPE, FPrdEndDate T_BD_Period.FEndDate%TYPE, FPrdNum T_BD_Period.Fnumber%TYPE, FAccountNum T_BD_AccountView.FNumber%TYPE, FAccountName T_BD_AccountView.FName_L2%TYPE, FAccountFDC T_BD_AccountView.FDC%TYPE, FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE, FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE, FCstmNum T_BD_Customer.FNumber%TYPE, FCstmName T_BD_Customer.FName_L2%TYPE, FSplrNum T_BD_Supplier.FNumber%TYPE, FSplrName T_BD_Supplier.FName_L2%TYPE); rs T_TABLE; --获取游标结果集的类型变量 --定义游标，根据入参查询结果集 CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID, T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate, T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN T_GL_AcctCussent CROSS JOIN T_GL_Voucher CROSS JOIN T_GL_VoucherAssistRecord WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量 v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ; v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE; v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE; v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE; v_FVouchNum T_GL_Voucher.FNumber%TYPE; v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE; v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE; v_count NUMBER := 0 ; --记录是否在往来账表中存在！ v_cnt NUMBER := 0 ; --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！--过程begin BEGIN OPEN cur ; LOOP FETCH cur INTO rs ; EXIT WHEN cur%NOTFOUND; IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN BEGIN SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate FROM ( SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription, T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum, T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_voucher.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum) ORDER BY FVouchBookedDate DESC nulls last --这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last )WHERE rn=1; --向临时表插入数据: INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate); EXCEPTION WHEN no_data_found THEN Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN BEGIN --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！ SELECT Count(*) INTO v_cnt FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID; --存在 逐笔循环再判断是否在往来账表t_gl_acctcussent中存在 IF v_cnt >0 THEN FOR rst IN ( SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID, T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID ) LOOP v_FGLVARID := rst.FGLVARID; v_FBillID := rst.FBillID; v_FDescription := rst.FDescription; v_FBizDate := rst.FBizDate; --逐笔循环判断 --判断是否在往来账表t_gl_acctcussent中存在 SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1 AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN --存在 向临时表插入数据: INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate, FGLVARID,FBillID,FDescription,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate, v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate); ELSE --不存在 向临时表插入数据: INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF ; END LOOP; ELSE --v_cnt = 0 直接将该笔rs.FAssistGrpID插入临时表 INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate) VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate); END IF;--过程未完，接下面：
--接上面： EXCEPTION WHEN others THEN Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID); END; END IF; END LOOP; CLOSE cur; --最后对临时表数据进行分类汇总 OPEN resultSet FOR SELECT Fcstm_FSplr_Name 客户或供应商, FDescription 摘要, CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 , CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方, CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方 FROM( --这里根据你的逻辑，修改了分类汇总，哎，好麻烦啊！！ /*SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID) ORDER BY FBizDate DESC ) rn FROM tempco WHERE FHGCstmID IS NOT NULL OR FHGPrvdID IS NOT NULL ) WHERE rn=1;*/ SELECT a.Fcstm_FSplr_Name,a.FvouchbookedDate,a.FBizDate,a.FDescription, Sum(a.Sum_FEndBalanceFor+Nvl(b.Sum_FbeginBalanceFor,0))over(PARTITION BY a.FHGCstm_FHGPrvd_ID) AS Sum_FEndBalanceFor FROM( SELECT FHGCstm_FHGPrvd_ID,Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,Sum_FEndBalanceFor FROM ( SELECT Nvl(FHGCstmID,FHGPrvdID) FHGCstm_FHGPrvd_ID,Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription, Sum(FEndBalanceFor)over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID)) AS Sum_FEndBalanceFor, Row_Number()over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID) ORDER BY FBizDate DESC ) rn FROM tempco2 WHERE FHGCstmID IS NOT NULL OR FHGPrvdID IS NOT NULL) WHERE rn=1 ) a, ( SELECT Nvl(FHGCstmID,FHGPrvdID) FHGCstm_FHGPrvd_ID,Sum(Decode(FAccountFDC,-1,-FbeginBalanceFor,FbeginBalanceFor)) AS Sum_FbeginBalanceFor FROM( SELECT DISTINCT T_GL_initAssistBalance.FOrgUnitID, T_GL_initAssistBalance.FAccountID, T_GL_initAssistBalance.FAssistGrpID, T_GL_initAssistBalance.FBeginBalanceFor, T_GL_initAssistBalance.FEndBalanceFor, T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_AccountView.FNumber AS FAccountNum, T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID, T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName, T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName FROM T_BD_Customer RIGHT OUTER JOIN T_BD_Supplier RIGHT OUTER JOIN T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN T_GL_initAssistBalance ON T_BD_AssistantHG.FID = T_GL_initAssistBalance.FAssistGrpID LEFT OUTER JOIN T_BD_AccountView ON T_GL_initAssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN T_ORG_BaseUnit ON T_GL_initAssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID WHERE T_ORG_BaseUnit.FNumber ='01.01' AND T_BD_ACCOUNTVIEW.Fnumber like '%' AND T_BD_AssistantHG.FCustomerID IS NOT NULL OR T_BD_AssistantHG.FProviderID IS NOT NULL ) GROUP BY Nvl(FHGCstmID,FHGPrvdID) ) b WHERE a.FHGCstm_FHGPrvd_ID=b.FHGCstm_FHGPrvd_ID(+) ); COMMIT; END; / --过程到此结束！--第三步：测试：Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on SQL> set linesize 32767 SQL> col 客户或供应商 format a20 SQL> col 摘要 format a40 SQL> col 发生年限 format a20 SQL> col 借方 format 999999 SQL> col 贷方 format 999999 SQL> var cur refcursor SQL> EXEC get_infoco2('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 广东世源集团 20101213做的凭证辅助账 小于1年 5800 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 20101213做的凭证 小于1年 7300SQL> conn ucnzp/ucnzp@cdfn01 Connected. SQL> EXEC get_infoco2('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方 -------------------- ---------------------------------------- -------------------- ------- ------- 林金兰 cxf20100108做的凭证客户林金兰 小于1年 700 陈凤 小于1年 2500SQL>
paddy实在太棒了，我慢慢看，太谢谢了！
上面过程中tempDB的条件我用的是实际的值，那是测试用的，没改过了，你自己改一下就OK了 WHERE T_ORG_BaseUnit.FNumber ='01.01' AND T_BD_ACCOUNTVIEW.Fnumber like '%' 改成： T_ORG_BaseUnit.FNumber =p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%'
--那你就先创建一张实体表撒 drop table temp; create table temp( Fcstm_FSplr_Name NVARCHAR2(255), －－客户或供应商 FDescription NVARCHAR2(200), －－摘要 Between_Year NVARCHAR2(255), --发生年限 FEndBalanceFor NUMBER(19,4), --借方 FEndBalanceTo NUMBER(19,4) －－贷方 );--过程做相应处理就是了，不要返回结果集 CREATE OR REPLACE PROCEDURE get_infoco2( p_ForgNum IN VARCHAR2 --公司 如：'01.01' ,p_FSearchDate IN DATE --具体日期 如：2010-1-20 ,p_FAccountNum IN VARCHAR2 --科目代码 如：1121 -- ,resultSet OUT sys_refcursor --返回结果集 ) IS .... .... begin .... .... .... --OPEN resultSet FOR 不要这个 改成： insert into temp SELECT Fcstm_FSplr_Name , FDescription , CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年' WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年' ELSE '五年以上' END Between_Year , CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END FEndBalanceFor, CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END FEndBalanceTo FROM( 。
end;
补充：如果这个temp表中的数据只是最近一次调用的存储过程中的数值,那么insert into temp 之前先delete 掉旧数据。
(
FID VARCHAR2(44) not null,
FCUSTOMERID VARCHAR2(44),
FPROVIDERID VARCHAR2(44)
)insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('1eMNUUmfR925jC2W+GK5f0GKbLs=', '', 'GJ8Z5OVyT3+ycES72ZPpKzfGffw=');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('2LC9qDgBShmzZzUWcjaNREGKbLs=', 'f4A7uDShSpiuGP7F0mPflL8MBA4=', '');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('FFrCI0VUQ+6VpaFmwZWF9kGKbLs=', '', 'GJ8Z5OVyT3+ycES72ZPpKzfGffw=');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('R5CYhF9hQNqjX8vt9kFdiUGKbLs=', 'f4A7uDShSpiuGP7F0mPflL8MBA4=', '');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('SYqPncg7Qd64uG8k5Ss67EGKbLs=', '', '');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('kjwvzfbDQnSWM3nfHXqbGkGKbLs=', 'MnDNTbz/SICkjZbOf9INYL8MBA4=', '');insert into t_bd_assistanthg (FID, FCUSTOMERID, FPROVIDERID)
values ('v/1yDNH/Qp2pjsH3X4J3D0GKbLs=', 'MnDNTbz/SICkjZbOf9INYL8MBA4=', '');
--6、客户表T_BD_CUSTOMER:FID、姓名、编号
create table T_BD_CUSTOMER
(
FID VARCHAR2(44) not null,
FNUMBER NVARCHAR2(80) not null,
FNAME_L2 NVARCHAR2(255)
)insert into t_bd_customer (FID, FNUMBER, FNAME_L2)
values ('MnDNTbz/SICkjZbOf9INYL8MBA4=', 'L-00068', '林金兰');insert into t_bd_customer (FID, FNUMBER, FNAME_L2)
values ('f4A7uDShSpiuGP7F0mPflL8MBA4=', 'C-00007', '陈凤');--7、供应商表T_BD_SUPPLIER：FID、供应商名称、供应商编码
create table T_BD_SUPPLIER
(
FID VARCHAR2(44) not null,
FNUMBER NVARCHAR2(80) not null,
FNAME_L2 NVARCHAR2(255)
)
insert into t_bd_supplier (FID, FNUMBER, FNAME_L2)
values ('GJ8Z5OVyT3+ycES72ZPpKzfGffw=', '6102-01055', '广东世源集团');insert into t_bd_supplier (FID, FNUMBER, FNAME_L2)
values ('71AFW+KIQxmEbMLwwn/OhzfGffw=', '3182-02305', '北京巨富公司');--8、凭证辅助账明细表:FID、凭证ID（FBILLID=T_GL_VOUCHER.FID）、辅助账横表ID（FASSGRPID=T_GL_ASSISTBALANCE.FASSISTGRPID)、业务日期、描述create table T_GL_VOUCHERASSISTRECORD
(
FID VARCHAR2(44) not null,
FBILLID VARCHAR2(44),
FASSGRPID VARCHAR2(44),
FBIZDATE TIMESTAMP(6) not null,
FDESCRIPTION NVARCHAR2(200)
)insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('HHvaWvRsToW+4CEcvflJmA//vKo=', '', '1eMNUUmfR925jC2W+GK5f0GKbLs=', '01-JAN-10 12.00.00.000000 AM', '应付工程款项目AA1000元');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('sLbd4D4qQSy55esg9O0p6g//vKo=', '7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '1eMNUUmfR925jC2W+GK5f0GKbLs=', '31-JAN-10 12.00.00.000000 AM', '20101213做的凭证辅助账');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('QK0jfExkQ4SY2MlFVOaTQw//vKo=', '', '2LC9qDgBShmzZzUWcjaNREGKbLs=', '01-JAN-10 12.00.00.000000 AM', '应收工程款项目AA1000元');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('KhGYD28TSHKSw4DoEvZucw//vKo=', '7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '2LC9qDgBShmzZzUWcjaNREGKbLs=', '31-JAN-10 12.00.00.000000 AM', '20101213做的凭证');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('BlXN9nDxTLa9JGh+LJ/nYA//vKo=', '', 'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', '01-JAN-10 12.00.00.000000 AM', '');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('4iA/TyxhREGLg9VBPHCuEg//vKo=', 'NblocXPKTTOC/+jKYVpVwyZS4B4=', 'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', '31-MAR-10 12.00.00.000000 AM', '');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('+xNAEqptS8G0u0EOAoGj4A//vKo=', 'jFVs2y9KQc21SWjiJVKXWiZS4B4=', 'SYqPncg7Qd64uG8k5Ss67EGKbLs=', '08-JAN-10 12.00.00.000000 AM', 'cxf20100108做的凭证金融机构');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('kwLPp6yTR6azR4bjN5V+lA//vKo=', 'jFVs2y9KQc21SWjiJVKXWiZS4B4=', 'v/1yDNH/Qp2pjsH3X4J3D0GKbLs=', '08-JAN-10 12.00.00.000000 AM', 'cxf20100108做的凭证客户林金兰');--9、凭证表T_GL_VOUCHER: FID、凭证编号、业务日期、记账日期、组织ID（FcompanyID=t_org_baseUnit.FID）
create table T_GL_VOUCHER
(
FID VARCHAR2(44) not null,
FNUMBER NVARCHAR2(80),
FBIZDATE TIMESTAMP(6) not null,
FBOOKEDDATE TIMESTAMP(6) not null,
FCOMPANYID VARCHAR2(44) not null
)insert into T_GL_Voucher (FID, FNUMBER, FBIZDATE, FBOOKEDDATE, FCOMPANYID)
values ('7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '0001', '31-JAN-10 12.00.00.000000 AM', '31-JAN-10 12.00.00.000000 AM', '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');insert into T_GL_Voucher (FID, FNUMBER, FBIZDATE, FBOOKEDDATE, FCOMPANYID)
values ('jFVs2y9KQc21SWjiJVKXWiZS4B4=', '0002', '08-JAN-10 12.00.00.000000 AM', '09-JAN-10 12.00.00.000000 AM', '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');--10、初始余额表T_GL_ACCTCUSSENT:FID、凭证辅助账记录表ID（FVCHASSISTRECORDID=T_GL_VoucherAssistRecord.FID）、是否初始化（=1表示初始化数据）、单位ID（FcompanyID=T_org_baseUnit.Fid）
create table T_GL_ACCTCUSSENT
(
FID VARCHAR2(44) not null,
FVCHASSISTRECORDID VARCHAR2(44),
FISINIT NUMBER(10) default 0 not null,
FCOMPANYID VARCHAR2(44) not null
)insert into t_gl_acctcussent (FID, FVCHASSISTRECORDID, FISINIT, FCOMPANYID)
values ('qJOjmIotS3yzVdGGfGWXN84thQo=', 'QK0jfExkQ4SY2MlFVOaTQw//vKo=', 1, '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');insert into t_gl_acctcussent (FID, FVCHASSISTRECORDID, FISINIT, FCOMPANYID)
values ('s9hdbsFRQtqnqt6atCGXYs4thQo=', 'HHvaWvRsToW+4CEcvflJmA//vKo=', 1, '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');
还没理解你的具体逻辑需求，特别是那个excel 看起来很费劲，能不能上传一个你弄好的excel，不要图片，可供我们下载啊
csdn个人资源里你上传一个excel就可以的
哎，费劲啊
--3、会计期间表t_bd_period(期间起始时间，期间结束时间)
--8、凭证辅助账明细表:业务日期
--9、凭证表T_GL_VOUCHER: 业务日期、记账日期
非常谢谢了，兄弟。
逻辑还要看看，没搞清楚，比较难理解。
类似'09-JAN-10 12.00.00.000000 AM 是表示2010-01-09号上午12点
我改了，谢谢~~
--3、会计期间表t_bd_period(FID,期间编号，期间起始时间，期间结束时间)
create table T_BD_PERIOD
(
FID VARCHAR2(44) not null,
FNUMBER NUMBER(10) default 200801 not null，
FBEGINDATE TIMESTAMP(6) not null,
FENDDATE TIMESTAMP(6) not null
)insert into t_bd_period (FID, FNUMBER, FBEGINDATE, FENDDATE)
values ('rc4UK8e6QrK6gJbq34IuaII4jEw=', 201001, to_timestamp('2010-01-01 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'));insert into t_bd_period (FID, FNUMBER, FBEGINDATE, FENDDATE)
values ('K2hDrW1OQXKI3K085Np6vII4jEw=', 201002, to_timestamp('2010-02-01 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), to_timestamp('2010-02-28 0.00.00', 'yyyy-mm-dd hh24.mi.ss'));insert into t_bd_period (FID, FNUMBER, FBEGINDATE, FENDDATE)
values ('br1XHiSiR9e+KHMjV1g9wYI4jEw=', 201003, to_timestamp('2010-03-01 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), to_timestamp('2010-03-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'));--9、凭证辅助账明细表:FID、凭证ID（FBILLID=T_GL_VOUCHER.FID）、辅助账横表ID（FASSGRPID=t_bd_assistanthg.fid)、业务日期、描述create table T_GL_VOUCHERASSISTRECORD
(
FID VARCHAR2(44) not null,
FBILLID VARCHAR2(44),
FASSGRPID VARCHAR2(44),
FBIZDATE TIMESTAMP(6) not null,
FDESCRIPTION NVARCHAR2(200)
)insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('HHvaWvRsToW+4CEcvflJmA//vKo=', '', '1eMNUUmfR925jC2W+GK5f0GKbLs=', to_timestamp('2010-01-01 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '应付工程款项目AA1000元');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('sLbd4D4qQSy55esg9O0p6g//vKo=', '7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '1eMNUUmfR925jC2W+GK5f0GKbLs=', to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '20101213做的凭证辅助账');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('QK0jfExkQ4SY2MlFVOaTQw//vKo=', '', '2LC9qDgBShmzZzUWcjaNREGKbLs=', to_timestamp('2010-01-01 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '应收工程款项目AA1000元');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('KhGYD28TSHKSw4DoEvZucw//vKo=', '7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '2LC9qDgBShmzZzUWcjaNREGKbLs=', to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '20101213做的凭证');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('BlXN9nDxTLa9JGh+LJ/nYA//vKo=', '', 'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('4iA/TyxhREGLg9VBPHCuEg//vKo=', 'NblocXPKTTOC/+jKYVpVwyZS4B4=', 'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', to_timestamp('2010-03-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('+xNAEqptS8G0u0EOAoGj4A//vKo=', 'jFVs2y9KQc21SWjiJVKXWiZS4B4=', 'SYqPncg7Qd64uG8k5Ss67EGKbLs=', to_timestamp('2010-01-08 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), 'cxf20100108做的凭证金融机构');insert into T_GL_VoucherAssistRecord (FID, FBILLID, FASSGRPID, FBIZDATE, FDESCRIPTION)
values ('kwLPp6yTR6azR4bjN5V+lA//vKo=', 'jFVs2y9KQc21SWjiJVKXWiZS4B4=', 'v/1yDNH/Qp2pjsH3X4J3D0GKbLs=', to_timestamp('2010-01-08 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), 'cxf20100108做的凭证客户林金兰');--8、凭证表T_GL_VOUCHER: FID、凭证编号、业务日期、记账日期、组织ID（FcompanyID=t_org_baseUnit.FID）create table T_GL_VOUCHER
(
FID VARCHAR2(44) not null,
FNUMBER NVARCHAR2(80),
FBIZDATE TIMESTAMP(6) not null,
FBOOKEDDATE TIMESTAMP(6) not null,
FCOMPANYID VARCHAR2(44) not null
)insert into T_GL_Voucher (FID, FNUMBER, FBIZDATE, FBOOKEDDATE, FCOMPANYID)
values ('7LrCcCV2RKaMO8WtqdnJtSZS4B4=', '0001', to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), to_timestamp('2010-01-31 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');insert into T_GL_Voucher (FID, FNUMBER, FBIZDATE, FBOOKEDDATE, FCOMPANYID)
values ('jFVs2y9KQc21SWjiJVKXWiZS4B4=', '0002', to_timestamp('2010-01-08 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), to_timestamp('2010-01-09 0.00.00', 'yyyy-mm-dd hh24.mi.ss'), '93CqeTWeRSm6pE1rMA5Yu8znrtQ=');
CREATE OR REPLACE PROCEDURE get_infoco(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量
--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
Dbms_Output.put_line(' 222222 ');
BEGIN
--在往来账表t_gl_acctcussent存在
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
AND EXISTS(SELECT 1 FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=T_GL_VoucherAssistRecord.FID);
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
EXCEPTION WHEN no_data_found THEN
--在往来账表t_gl_acctcussent存在
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END;
END IF;
END LOOP;
CLOSE cur;
--最后对临时表数据进行分类汇总
OPEN resultSet FOR
select FcstmName 客户或供应商,
FDescription 摘要,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Sum_FEndBalanceFor ELSE NULL END 贷方
FROM(
SELECT FcstmName,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY FcstmName) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY FcstmName ORDER BY FBizDate DESC ) rn
FROM tempco)
WHERE rn=1;
COMMIT;
END;
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --创建会话级临时表
SQL> CREATE global temporary TABLE tempco
2 (
3 FAssistGrpID VARCHAR2(44),
4 FBeginBalanceFor NUMBER(19,4),
5 FEndBalanceFor NUMBER(19,4),
6 FCstmNum NVARCHAR2(80) ,
7 FCstmName NVARCHAR2(255),
8 FSplrNum NVARCHAR2(80) ,
9 FSplrName NVARCHAR2(255),
10 ForgNum NVARCHAR2(80),
11 ForgName NVARCHAR2(255),
12 FPrdBegDate TIMESTAMP(6),
13 FPrdEndDate TIMESTAMP(6),
14 FGLVARID VARCHAR2(44),
15 FBillID VARCHAR2(44),
16 FDescription NVARCHAR2(200),
17 FBizDate TIMESTAMP(6),
18 FVouchNum NVARCHAR2(80),
19 FVouchBizDate TIMESTAMP(6) ,
20 FVouchBookedDate TIMESTAMP(6)
21 ) on commit preserve rows ;Table created.SQL> set serveroutput on
SQL> col 客户或供应商 format a8
SQL> col 摘要 format a30
SQL> col 发生年限 format a8
SQL> col 借方 format 9999
SQL> col 贷方 format 9999
SQL> var cur refcursor
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供 摘要 发生年限 借方 贷方
-------- ------------------------------ -------- ----- -----
林金兰 cxf20100108做的凭证客户林金兰 小于1年 2100
陈凤 小于1年 1500SQL>
这里其实还有很多地方要修改的，
比如，我临时表tempco，可以直接将excel中的A-AF列创建成临时表tempco，
这样在最后对临时表数据进行分类汇总的时候，所有字段都有就比较方便，根据你的逻辑来写就容易多了
这里我有些逻辑没理顺，所以创建临时表tempco的时候少考虑了后面的内容，比如客户或供应商药根据
T列FCustomerID、U列Fproviderid是否为空 来判断，这里我临时表tempco里没加入这两栏位，直接根据的FcstmName
来汇总的你可以再循环RS的时候将游标结果集中数据全部插入临时表，这样后面处理就好了，你再修改下吧
有问题再讨论
--修改了下过程中一些问题：逻辑二，逐笔循环判断是否在往来账表t_gl_acctcussent中存在
--之前逻辑理解错了，你现在看看呢
CREATE OR REPLACE PROCEDURE get_infoco(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量
--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;
v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )
LOOP
v_FGLVARID := rst.FGLVARID;
v_FBillID := rst.FBillID;
v_FDescription := rst.FDescription;
v_FBizDate := rst.FBizDate;
--逐笔循环判断
--判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID; IF v_count>0 THEN
--存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
ELSE
--不存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF ;
END LOOP;
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
END LOOP;
CLOSE cur;
--最后对临时表数据进行分类汇总
OPEN resultSet FOR
select FcstmName 客户或供应商,
FDescription 摘要,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Sum_FEndBalanceFor ELSE NULL END 贷方
FROM(
SELECT FcstmName,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY FcstmName) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY FcstmName ORDER BY FBizDate DESC ) rn
FROM tempco)
WHERE rn=1;
COMMIT;
END;
/
--测试：
SQL> var cur refcursor
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供 摘要 发生年限 借方 贷方
-------- ------------------------------ -------- ----- -----
林金兰 cxf20100108做的凭证客户林金兰 小于1年 1400
陈凤 小于1年 2000SQL>
具体问题请查看链接http://fannycen.bokee.com/viewdiary.232605011.html，因为CSDN不能贴图的...
我看了，逻辑基本上按我讲的思路来做，通过调试还有三个地方的逻辑需要修正：
1： IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN...END
里的语句： SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last这句语句中如果有相同记账日期，还要比较T_GL_voucher的Fcreatetime字段（创建时间，timestamp类型），取最近创建时间的那条凭证。
所以判断条件还要加上这种情况。2： --判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID;这个语句还需要条件：T_GL_AcctCussent.FcompanyID 与T_org_baseUnit.fid关联取得T_org_baseUnit.Fnumber=入参ForgNum 以及T_GL_AcctCussent.FisInit=1 3：对临时表数据进行分类汇总部分：
(1)只是按FcstmName进行了分类汇总，没有分类汇总到供应商FPrvrName.
因为临时表tempco有一个特别：在一行记录中FcstmName/FSplrName字段不会同时有内容。要么这两个字段都为空；要么FcstmName为空而FSplrName不为空；要么FCstmName不为空而FSplrName为空。
所以需要同时汇总FcstmName和FPrvrName.
我不知道思路是怎么样做？
假设临时表tempco的内容如下：
汇总出来应该是：不知道可不可以用unit all来连接两个汇总？(2)查询日期与日期判断。我的条件是如果记账日期非空，则与FvouchBookedate相减，否则与业务日期FBizDate相减比较。当然也可以在设计临时表时设计一个临时日期字段来存储凭证的记账日期（存在凭证时），或者存储往来账导入时的业务日期（不存在凭证，初始数值是导入的），或者存储当期会计期间的结束日期FprdEndDate（不存在凭证，初始数值是手工录入），这样临时表的这几个值都可以不要。最后在分类汇总时用入参的查询日期与这个临时日期字段来比较就可以。 4：临时表tempco在分类汇总后不用删除回收吗？
--我过程代码多了，回复时候报： 回复内容过长！
--因此分开来
--过程如下：
CREATE OR REPLACE PROCEDURE get_infoco(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';
--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;
v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last --这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )
LOOP
v_FGLVARID := rst.FGLVARID;
v_FBillID := rst.FBillID;
v_FDescription := rst.FDescription;
v_FBizDate := rst.FBizDate;
--逐笔循环判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1
AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN
--存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
ELSE
--不存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF ;
END LOOP;
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
END LOOP;
CLOSE cur;--未完，接下面
--接上面！ --最后对临时表数据进行分类汇总
OPEN resultSet FOR
SELECT Fcstm_FSplr_Name 客户或供应商,
FDescription 摘要,
Decode(FvouchbookedDate,NULL,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END ,
CASE WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(FvouchbookedDate,'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END ) 发生年限,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方
FROM(
SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn
FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL )
WHERE rn=1;
COMMIT;
END;
/--存储过程到此结果！
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> set linesize 32767
SQL> col 客户或供应商 format a20
SQL> col 摘要 format a40
SQL> col 发生年限 format a20
SQL> col 借方 format 999999
SQL> col 贷方 format 999999
SQL> var cur refcursor
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed.
SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
广东世源集团 20101213做的凭证辅助账 小于1年 4800
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 20101213做的凭证 小于1年 5800
SQL> conn ucnzp/ucnzp@cdfn01
Connected.
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 小于1年 1000SQL>
rs T_TABLE; --获取游标结果集的类型变量
---定义游标，根据入参查询结果集
CURSO这是怎么一回事呢？
--我这里不会有错啊，我是用工具SQLTools创建的，SQLPLUS中我也能创建成功，
--可能是游标申明必须在类型变量申明之前吧，你将rs T_table 这个申明写到 cursor申明后面试试--这样吧，我来总结下，下面是总结版，最终版，你直接按照下面来测试就行了，前面回复的都不管
--由于回复内容比较长，分开回复--第一步：创建会话级临时表DROP TABLE tempco;CREATE global temporary TABLE tempco
(
FAssistGrpID VARCHAR2(44),
FBeginBalanceFor NUMBER(19,4),
FEndBalanceFor NUMBER(19,4),
FCstmNum NVARCHAR2(80) ,
FCstmName NVARCHAR2(255),
FSplrNum NVARCHAR2(80) ,
FSplrName NVARCHAR2(255),
ForgNum NVARCHAR2(80),
ForgName NVARCHAR2(255),
FPrdBegDate TIMESTAMP(6),
FPrdEndDate TIMESTAMP(6),
FGLVARID VARCHAR2(44),
FBillID VARCHAR2(44),
FDescription NVARCHAR2(200),
FBizDate TIMESTAMP(6),
FVouchNum NVARCHAR2(80),
FVouchBizDate TIMESTAMP(6) ,
FVouchBookedDate TIMESTAMP(6)
) on commit preserve rows ;--第二步：创建过程：
CREATE OR REPLACE PROCEDURE get_infoco(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量
--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;
v_count NUMBER := 0 ; --记录是否在往来账表中存在！--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last
--这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )
LOOP
v_FGLVARID := rst.FGLVARID;
v_FBillID := rst.FBillID;
v_FDescription := rst.FDescription;
v_FBizDate := rst.FBizDate;
--逐笔循环判断
--判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1
AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN
--存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
ELSE
--不存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF ;
END LOOP;
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
END LOOP;
CLOSE cur;--过程未完，接下面
--过程继续，接上面
--最后对临时表数据进行分类汇总
--这里我修改了下，根据Nvl(FvouchbookedDate,p_FSearchDate)来判断发生年限，减少了代码量
OPEN resultSet FOR
SELECT Fcstm_FSplr_Name 客户或供应商,
FDescription 摘要,
CASE WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 ,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方
FROM(
SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn
FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL )
WHERE rn=1;
COMMIT;
END;
/--过程到此结束！
--准备工作也已完成，下面是测试！--第三步：测试：--SQLPLUS下测试：
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> set linesize 32767
SQL> col 客户或供应商 format a20
SQL> col 摘要 format a40
SQL> col 发生年限 format a20
SQL> col 借方 format 999999
SQL> col 贷方 format 999999
SQL> var cur refcursor
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed.
SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
广东世源集团 20101213做的凭证辅助账 小于1年 4800
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 20101213做的凭证 小于1年 5800
SQL> conn ucnzp/ucnzp@cdfn01
Connected.
SQL> EXEC get_infoco('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 小于1年 1000SQL>
1） IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )这里的select ....from t_gl_voucherAssistRecord where ...筛出来可能会是空的。这个需要做判断一下，如果为空那就直接插入临时表，跳到下面部分的语句，否则按按步执行：--不存在 向临时表插入数据:
INSERT INTO gxyjTemptable(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
---需要反查到关联了 多少张凭证,并取出这些凭证记账日期是最新的那条凭证记录;如果相同记账日期有多条,则取创建时间最新的那条凭证.
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_Voucher.FBizStatus=5 and T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last , T_GL_Voucher.Fcreatetime DESC nulls last
)WHERE rn=1;
这里的orderby 我这样写得对不对？因为Fcreatetime并不在select 后。
我这边还需要加一个条件.即T_GL_voucher.FcompanyID 关联 T_org_baseUnit.FID 取T_org_baseUnit.Fnumber=入参p_ForgNum
3)汇总部分：
CASE WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=1 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=2 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=3 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))>=4 AND (Trunc(Nvl(FvouchbookedDate,p_FSearchDate),'yyyy')-Trunc(FBizDate,'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 这里，条件应该是：如果FvouchBookedDate为空，则year(P_FsearchDate)-year(FBizDate)
如果FvouchBookedDate非空，取year(P_FsearchDate)-year(vouchBookedDate)
--ok~\(≧▽≦)/~啦啦啦
--你再测试下：CREATE OR REPLACE PROCEDURE get_infoco(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量
--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;
v_count NUMBER := 0 ; --记录是否在往来账表中存在！
v_cnt NUMBER := 0 ; --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
ORDER BY FVouchBookedDate DESC nulls last
--这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
--判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！
SELECT Count(*) INTO v_cnt FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID; --存在 逐笔循环再判断是否在往来账表t_gl_acctcussent中存在
IF v_cnt >0 THEN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )
LOOP
v_FGLVARID := rst.FGLVARID;
v_FBillID := rst.FBillID;
v_FDescription := rst.FDescription;
v_FBizDate := rst.FBizDate;
--逐笔循环判断
--判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1
AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN
--存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
ELSE
--不存在 向临时表插入数据:
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF ;
END LOOP;
ELSE --v_cnt = 0 直接将该笔rs.FAssistGrpID插入临时表
INSERT INTO tempco(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FCstmNum,FCstmName,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FCstmNum,rs.FCstmName,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF; EXCEPTION WHEN others THEN
Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
END LOOP;
CLOSE cur;--未完，接下面
--接上面！ --最后对临时表数据进行分类汇总
--这里的发生年限按照你信需求修改了
OPEN resultSet FOR
SELECT Fcstm_FSplr_Name 客户或供应商,
FDescription 摘要,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 ,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方
FROM(
SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn
FROM tempco WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL )
WHERE rn=1;
COMMIT;
END;
/--过程结束！
T_GL_initAssistBalance的测试数据如下：--辅助账初始化表T_GL_initASSISTBALANCE
--字段说明（依次）：单位ID（T_GL_initASSISTBALANCE.ForgUnitID=T_org_baseUnit.Fid)、、科目ID（FAccountID=T_bd_AccountView.FID）、辅助账横表ID
（FAssistgrpID=t_bd_assistantHG.FID）、期初余额、期末累计余额create table T_GL_initASSISTBALANCE
(
FORGUNITID VARCHAR2(44) not null,
FACCOUNTID VARCHAR2(44) not null,
FASSISTGRPID VARCHAR2(44) not null,
FBEGINBALANCEFOR NUMBER(19,4) default 0 not null,
FENDBALANCEFOR NUMBER(19,4) default 0 not null
)insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID,
FBEGINBALANCEFOR, FENDBALANCEFOR)
values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'Db3EjfIxQqSoK9YA4Uk5IJ2pmCY=',
'1eMNUUmfR925jC2W+GK5f0GKbLs=', 1000.0000, 1000.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID,
FBEGINBALANCEFOR, FENDBALANCEFOR)
values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', '4j8T4CrHRy+mdqWxGu2daJ2pmCY=',
'2LC9qDgBShmzZzUWcjaNREGKbLs=', 1000.0000, 1000.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID,
FBEGINBALANCEFOR, FENDBALANCEFOR)
values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'ApBGyh1WS5yHiCHw3rxKIZ2pmCY=',
'R5CYhF9hQNqjX8vt9kFdiUGKbLs=', 500.0000, 500.0000);insert into T_GL_initAssistBalance (FORGUNITID, FACCOUNTID, FASSISTGRPID,
FBEGINBALANCEFOR, FENDBALANCEFOR)
values ('93CqeTWeRSm6pE1rMA5Yu8znrtQ=', 'dsgoA3GERKej7F3Hiv51DJ2pmCY=',
'SYqPncg7Qd64uG8k5Ss67EGKbLs=', 500.0000, 500.0000);
T_GL_initAssistBalance 与组织表t_org_baseUnit、科目表T_bd_AccountView、辅助账横表t_bd_assistantHG的关联如下：--假设以如下select 语句做为结果集tempDB
--假设以如下select 语句做为结果集tempDB
SELECT DISTINCT T_GL_initAssistBalance.FOrgUnitID, T_GL_initAssistBalance.FAccountID,
T_GL_initAssistBalance.FAssistGrpID, T_GL_initAssistBalance.FBeginBalanceFor, T_GL_initAssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_initAssistBalance ON T_BD_AssistantHG.FID = T_GL_initAssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_initAssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_initAssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID
WHERE and T_ORG_BaseUnit.FNumber =p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';
SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FcstmName,FSplrName)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FcstmName,FSplrName) ORDER BY FBizDate DESC ) rn
FROM gxyjTemptable WHERE FcstmName IS NOT NULL OR FSplrName IS NOT NULL )这句，在sum(FendBalanceFor)后面还需加上sum(tempDB.FbeginBalanceFor)，条件是tempDB数据集的tempDB.ForgNumber =p_ForgNum AND tempDb.FAccountNum like p_FAccountNum||'%' 而且 tempDB.FHGCstmID=tempco.FHGCstmID或tempDB.FHGPrvdID=tempco.FHGPrvdID
（注：26楼代码中tempCo临时表中还没有这两个字段，需要加上才得，要以此为分类汇总条件，目前以FprvdName、FcstmName为汇总条件不严谨，因为如果存在同名的人，那数据就不正确了）比如汇总客户FcstmName='陈凤',其FCstmID='f4A7uDShSpiuGP7F0mPflL8MBA4='，那么tempDB汇总条件就是tempDB.FHGCstmID='f4A7uDShSpiuGP7F0mPflL8MBA4='
--由于你现在要在分类汇总新增这段逻辑，因此会涉及到临时表结构的调整，
--这里新增新增客户FHGCstmID和厂商FHGPrvdID,并修改之前的按照 Nvl(FcstmName,FSplrName)来汇总
--根据 Nvl(FHGCstmID,FHGPrvdID)，因为按照你所述姓名可能相同情况
--下面是具体过程和步骤：--第一步：创建临时表2：
DROP TABLE tempco2;
--创建会话级临时表
CREATE global temporary TABLE tempco2
(
FAssistGrpID VARCHAR2(44),
FBeginBalanceFor NUMBER(19,4),
FEndBalanceFor NUMBER(19,4),
FHGCstmID VARCHAR2(44), --临时表新增客户ID
FCstmNum NVARCHAR2(80) ,
FCstmName NVARCHAR2(255),
FHGPrvdID VARCHAR2(44), --临时表新增厂商ID
FSplrNum NVARCHAR2(80) ,
FSplrName NVARCHAR2(255),
ForgNum NVARCHAR2(80),
ForgName NVARCHAR2(255),
FPrdBegDate TIMESTAMP(6),
FPrdEndDate TIMESTAMP(6),
FGLVARID VARCHAR2(44),
FBillID VARCHAR2(44),
FDescription NVARCHAR2(200),
FBizDate TIMESTAMP(6),
FVouchNum NVARCHAR2(80),
FVouchBizDate TIMESTAMP(6) ,
FVouchBookedDate TIMESTAMP(6)
) on commit preserve rows ;
--第二步：创建存储过程如下：
CREATE OR REPLACE PROCEDURE get_infoco2(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
,resultSet OUT sys_refcursor --返回结果集
)
IS
--定义一个类型，用来指明中间表的结果
TYPE T_TABLE IS RECORD (
FBalType T_GL_AssistBalance.FBalType%TYPE,
FOrgUnitID T_GL_AssistBalance.FOrgUnitID%TYPE,
FPeriodID T_GL_AssistBalance.FPeriodID%TYPE,
FAccountID T_GL_AssistBalance.FAccountID%TYPE,
FAssistGrpID T_GL_AssistBalance.FAssistGrpID%TYPE,
FBeginBalanceFor T_GL_AssistBalance.FBeginBalanceFor%TYPE,
FEndBalanceFor T_GL_AssistBalance.FEndBalanceFor%TYPE,
ForgNum T_ORG_BaseUnit.FNumber%TYPE,
ForgName T_ORG_BaseUnit.FName_L2%TYPE,
FPrdBegDate T_BD_Period.FBeginDate%TYPE,
FPrdEndDate T_BD_Period.FEndDate%TYPE,
FPrdNum T_BD_Period.Fnumber%TYPE,
FAccountNum T_BD_AccountView.FNumber%TYPE,
FAccountName T_BD_AccountView.FName_L2%TYPE,
FAccountFDC T_BD_AccountView.FDC%TYPE,
FHGCstmID T_BD_AssistantHG.FCustomerID%TYPE,
FHGPrvdID T_BD_AssistantHG.FProviderID%TYPE,
FCstmNum T_BD_Customer.FNumber%TYPE,
FCstmName T_BD_Customer.FName_L2%TYPE,
FSplrNum T_BD_Supplier.FNumber%TYPE,
FSplrName T_BD_Supplier.FName_L2%TYPE);
rs T_TABLE; --获取游标结果集的类型变量
--定义游标，根据入参查询结果集
CURSOR cur IS SELECT DISTINCT T_GL_AssistBalance.FBalType, T_GL_AssistBalance.FOrgUnitID, T_GL_AssistBalance.FPeriodID, T_GL_AssistBalance.FAccountID,
T_GL_AssistBalance.FAssistGrpID, T_GL_AssistBalance.FBeginBalanceFor, T_GL_AssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_Period.FBeginDate AS FPrdBegDate,
T_BD_Period.FEndDate AS FPrdEndDate, T_BD_Period.Fnumber AS FPrdNum, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_AssistBalance ON T_BD_AssistantHG.FID = T_GL_AssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_AssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_BD_Period ON T_GL_AssistBalance.FPeriodID = T_BD_Period.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_AssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID CROSS JOIN
T_GL_AcctCussent CROSS JOIN
T_GL_Voucher CROSS JOIN
T_GL_VoucherAssistRecord
WHERE T_ORG_BaseUnit.FNumber = p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%';--定义全局变量
v_FGLVARID T_GL_VoucherAssistRecord.FID%TYPE ;
v_FBillID T_GL_VoucherAssistRecord.FBillID%TYPE;
v_FDescription T_GL_VoucherAssistRecord.FDescription%TYPE;
v_FBizDate T_GL_VoucherAssistRecord.FBizDate%TYPE;
v_FVouchNum T_GL_Voucher.FNumber%TYPE;
v_FVouchBizDate T_GL_Voucher.FBizDate%TYPE;
v_FVouchBookedDate T_GL_Voucher.FBookedDate%TYPE;
v_count NUMBER := 0 ; --记录是否在往来账表中存在！
v_cnt NUMBER := 0 ; --判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！--过程begin
BEGIN
OPEN cur ;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.FBeginBalanceFor<>rs.FEndBalanceFor AND rs.FEndBalanceFor <>0 THEN
BEGIN
SELECT FGLVARID, FBillID, FDescription, FBizDate, FVouchNum, FVouchBizDate, FVouchBookedDate
INTO v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate
FROM (
SELECT ROWNUM rn,T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,T_GL_VoucherAssistRecord.FDescription,
T_GL_VoucherAssistRecord.FBizDate,T_GL_Voucher.FNumber AS FVouchNum,
T_GL_Voucher.FBizDate AS FVouchBizDate, T_GL_Voucher.FBookedDate AS FVouchBookedDate
FROM T_GL_VoucherAssistRecord LEFT OUTER JOIN T_GL_Voucher
ON T_GL_VoucherAssistRecord.FBillID = T_GL_Voucher.FID
WHERE T_GL_VoucherAssistRecord.FbillID IS NOT NULL
AND T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID
AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_voucher.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum)
ORDER BY FVouchBookedDate DESC nulls last
--这里由于测试数据里T_GL_voucher里没有Fcreatetime这个字段，你自己测试的时候再加上 ORDER BY FVouchBookedDate DESC nulls last,Fcreatetime DESC nulls last
)WHERE rn=1;
--向临时表插入数据:
INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate,FVouchNum,FVouchBizDate,FVouchBookedDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate,v_FVouchNum,v_FVouchBizDate,v_FVouchBookedDate);
EXCEPTION WHEN no_data_found THEN
Raise_Application_Error('-20001','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
IF rs.FBeginBalanceFor<>0 AND rs.FEndBalanceFor = rs.FEndBalanceFor THEN
BEGIN
--判断每笔rs.FAssistGrpID是否在T_GL_VoucherAssistRecord表中存在！
SELECT Count(*) INTO v_cnt FROM T_GL_VoucherAssistRecord WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID; --存在 逐笔循环再判断是否在往来账表t_gl_acctcussent中存在
IF v_cnt >0 THEN
FOR rst IN (
SELECT T_GL_VoucherAssistRecord.FID AS FGLVARID, T_GL_VoucherAssistRecord.FBillID,
T_GL_VoucherAssistRecord.FDescription,T_GL_VoucherAssistRecord.FBizDate
FROM T_GL_VoucherAssistRecord
WHERE T_GL_VoucherAssistRecord.FAssGrpID =rs.FAssistGrpID )
LOOP
v_FGLVARID := rst.FGLVARID;
v_FBillID := rst.FBillID;
v_FDescription := rst.FDescription;
v_FBizDate := rst.FBizDate;
--逐笔循环判断
--判断是否在往来账表t_gl_acctcussent中存在
SELECT Count(*) INTO v_count FROM T_GL_AcctCussent WHERE T_GL_AcctCussent.FVchAssistRecordID=v_FGLVARID AND T_GL_AcctCussent.FisInit=1
AND EXISTS (SELECT 1 FROM T_org_baseUnit WHERE T_GL_AcctCussent.FcompanyID=T_org_baseUnit.Fid AND T_org_baseUnit.Fnumber=p_ForgNum); IF v_count>0 THEN
--存在 向临时表插入数据:
INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,
FGLVARID,FBillID,FDescription,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,
v_FGLVARID,v_FBillID,v_FDescription,v_FBizDate);
ELSE
--不存在 向临时表插入数据:
INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF ;
END LOOP;
ELSE --v_cnt = 0 直接将该笔rs.FAssistGrpID插入临时表
INSERT INTO tempco2(FAssistGrpID,FBeginBalanceFor,FEndBalanceFor,FHGCstmID,FCstmNum,FCstmName,FHGPrvdID,FSplrNum,FSplrName,ForgNum,ForgName,FPrdBegDate,FPrdEndDate,FBizDate)
VALUES (rs.FAssistGrpID,rs.FBeginBalanceFor,rs.FEndBalanceFor,rs.FHGCstmID,rs.FCstmNum,rs.FCstmName,rs.FHGPrvdID,rs.FSplrNum,rs.FSplrName,rs.ForgNum,rs.ForgName,rs.FPrdBegDate,rs.FPrdEndDate,rs.FPrdEndDate);
END IF;--过程未完，接下面：
--接上面：
EXCEPTION WHEN others THEN
Raise_Application_Error('-20002','can not find data in T_GL_VoucherAssistRecord where FAssistGrpID='||rs.FAssistGrpID);
END;
END IF;
END LOOP;
CLOSE cur; --最后对临时表数据进行分类汇总
OPEN resultSet FOR
SELECT Fcstm_FSplr_Name 客户或供应商,
FDescription 摘要,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 ,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END 借方,
CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END 贷方
FROM(
--这里根据你的逻辑，修改了分类汇总，哎，好麻烦啊！！
/*SELECT Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID) ORDER BY FBizDate DESC ) rn
FROM tempco WHERE FHGCstmID IS NOT NULL OR FHGPrvdID IS NOT NULL )
WHERE rn=1;*/
SELECT a.Fcstm_FSplr_Name,a.FvouchbookedDate,a.FBizDate,a.FDescription,
Sum(a.Sum_FEndBalanceFor+Nvl(b.Sum_FbeginBalanceFor,0))over(PARTITION BY a.FHGCstm_FHGPrvd_ID) AS Sum_FEndBalanceFor
FROM(
SELECT FHGCstm_FHGPrvd_ID,Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,Sum_FEndBalanceFor
FROM (
SELECT Nvl(FHGCstmID,FHGPrvdID) FHGCstm_FHGPrvd_ID,Nvl(FcstmName,FSplrName) Fcstm_FSplr_Name,FvouchbookedDate,FBizDate,FDescription,
Sum(FEndBalanceFor)over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID)) AS Sum_FEndBalanceFor,
Row_Number()over(PARTITION BY Nvl(FHGCstmID,FHGPrvdID) ORDER BY FBizDate DESC ) rn
FROM tempco2 WHERE FHGCstmID IS NOT NULL OR FHGPrvdID IS NOT NULL)
WHERE rn=1
) a,
(
SELECT Nvl(FHGCstmID,FHGPrvdID) FHGCstm_FHGPrvd_ID,Sum(Decode(FAccountFDC,-1,-FbeginBalanceFor,FbeginBalanceFor)) AS Sum_FbeginBalanceFor
FROM(
SELECT DISTINCT T_GL_initAssistBalance.FOrgUnitID, T_GL_initAssistBalance.FAccountID,
T_GL_initAssistBalance.FAssistGrpID, T_GL_initAssistBalance.FBeginBalanceFor, T_GL_initAssistBalance.FEndBalanceFor,
T_ORG_BaseUnit.FNumber AS ForgNum, T_ORG_BaseUnit.FName_L2 AS ForgName, T_BD_AccountView.FNumber AS FAccountNum,
T_BD_AccountView.FName_L2 AS FAccountName, T_BD_AccountView.FDC AS FAccountFDC, T_BD_AssistantHG.FCustomerID AS FHGCstmID,
T_BD_AssistantHG.FProviderID AS FHGPrvdID, T_BD_Customer.FNumber AS FCstmNum, T_BD_Customer.FName_L2 AS FCstmName,
T_BD_Supplier.FNumber AS FSplrNum, T_BD_Supplier.FName_L2 AS FSplrName
FROM T_BD_Customer RIGHT OUTER JOIN
T_BD_Supplier RIGHT OUTER JOIN
T_BD_AssistantHG ON T_BD_Supplier.FID = T_BD_AssistantHG.FProviderID ON
T_BD_Customer.FID = T_BD_AssistantHG.FCustomerID RIGHT OUTER JOIN
T_GL_initAssistBalance ON T_BD_AssistantHG.FID = T_GL_initAssistBalance.FAssistGrpID LEFT OUTER JOIN
T_BD_AccountView ON T_GL_initAssistBalance.FAccountID = T_BD_AccountView.FID LEFT OUTER JOIN
T_ORG_BaseUnit ON T_GL_initAssistBalance.FOrgUnitID = T_ORG_BaseUnit.FID
WHERE T_ORG_BaseUnit.FNumber ='01.01' AND T_BD_ACCOUNTVIEW.Fnumber like '%'
AND T_BD_AssistantHG.FCustomerID IS NOT NULL OR T_BD_AssistantHG.FProviderID IS NOT NULL
)
GROUP BY Nvl(FHGCstmID,FHGPrvdID)
) b
WHERE a.FHGCstm_FHGPrvd_ID=b.FHGCstm_FHGPrvd_ID(+)
);
COMMIT;
END;
/
--过程到此结束！--第三步：测试：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> set linesize 32767
SQL> col 客户或供应商 format a20
SQL> col 摘要 format a40
SQL> col 发生年限 format a20
SQL> col 借方 format 999999
SQL> col 贷方 format 999999
SQL> var cur refcursor
SQL> EXEC get_infoco2('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'%',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
广东世源集团 20101213做的凭证辅助账 小于1年 5800
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 20101213做的凭证 小于1年 7300SQL> conn ucnzp/ucnzp@cdfn01
Connected.
SQL> EXEC get_infoco2('01.01',To_Date('2010-01-20','yyyy-mm-dd'),'1121',:cur) ;PL/SQL procedure successfully completed.SQL> print cur客户或供应商 摘要 发生年限 借方 贷方
-------------------- ---------------------------------------- -------------------- ------- -------
林金兰 cxf20100108做的凭证客户林金兰 小于1年 700
陈凤 小于1年 2500SQL>
WHERE T_ORG_BaseUnit.FNumber ='01.01' AND T_BD_ACCOUNTVIEW.Fnumber like '%'
改成：
T_ORG_BaseUnit.FNumber =p_ForgNum AND T_BD_ACCOUNTVIEW.Fnumber like p_FAccountNum||'%'
哈哈，paddy，我终于调试成功了，非常谢谢你！
不过还是要给这个存储过程做些微调：
1）存储过程不需返回结果集了。
2）在分类汇总部分代码中，将分类汇总后的结果集放到一个实体表中。这个实体表中需要在数据库中存在的，不能动态创建，其结果与分类汇总后的结果集一致。
--那你就先创建一张实体表撒
drop table temp;
create table temp(
Fcstm_FSplr_Name NVARCHAR2(255), －－客户或供应商
FDescription NVARCHAR2(200), －－摘要
Between_Year NVARCHAR2(255), --发生年限
FEndBalanceFor NUMBER(19,4), --借方
FEndBalanceTo NUMBER(19,4) －－贷方
);--过程做相应处理就是了，不要返回结果集
CREATE OR REPLACE PROCEDURE get_infoco2(
p_ForgNum IN VARCHAR2 --公司 如：'01.01'
,p_FSearchDate IN DATE --具体日期 如：2010-1-20
,p_FAccountNum IN VARCHAR2 --科目代码 如：1121
-- ,resultSet OUT sys_refcursor --返回结果集
)
IS
....
....
begin
....
....
....
--OPEN resultSet FOR 不要这个 改成：
insert into temp
SELECT Fcstm_FSplr_Name ,
FDescription ,
CASE WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy')) <1 THEN '小于1年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=1 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<2 THEN '1-2年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=2 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<3 THEN '2-3年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=3 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<4 THEN '3-4年'
WHEN (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))>=4 AND (Trunc(p_FSearchDate,'yyyy')-Trunc(Nvl(FvouchbookedDate,FBizDate),'yyyy'))<5 THEN '4-5年'
ELSE '五年以上'
END Between_Year ,
CASE WHEN Sum_FEndBalanceFor>0 THEN Sum_FEndBalanceFor ELSE NULL END FEndBalanceFor,
CASE WHEN Sum_FEndBalanceFor<0 THEN Abs(Sum_FEndBalanceFor) ELSE NULL END FEndBalanceTo
FROM(
。
end;
汇总数据量不是很大的话 ，就用delete from temp;还是可以的
想问一下paddy，汇总数据量大到多少数量级，才需要用execute immediate 'truncate table temp';??