procedure getSet(inputdate in char, pRetCursor out mycursor) as begin -----开始查询 OPEN pRetCursor FOR SELECT U1.CUSTNO, U1.CUSTNAME, U1.CURRBAL U1.AVGBAL U2.DEBTBALANCE, U2.DEBTBALANCE_AVG, U2.EXTERNALINTERESTPAY, U2.INTERALFUNDSTPR FROM --获取客户基本信息以及存款信息 (SELECT T4.CUSTNO AS CUSTNO, T4.CUSTNAME AS CUSTNAME, SUM(T5.CURRBAL) OVER (PARTITION BY T5.CUSTNO, T5.CURRCODE) AS CURRBAL, SUM(T5.AVGBAL) OVER (PARTITION BY T5.CUSTNO,T5.CURRCODE) AS AVGBAL FROM (SELECT DISTINCT T1.CUSTNO AS CUSTNO, T1.CUSTNAME AS CUSTNAME FROM CMS.CUSTINFO T1, CMS.USERACCT T2,BASEINFO.USERINFO T3 WHERE GOVTYPE='1' AND T2.USERCODE =T3.USERCODE AND T3.BRANCHCODE='000005' AND AND T1.CUSTNO = T2.CUSTNO) T4,DATAHOUSE.SAV_M_ACCTBAL T5 WHERE T4.CUSTNO=T5.CUSTNO AND T5.DATADATE=inputdate AND T5.CURRCODE = 'CNY') U1, CMS.PADATA U2 WHERE U1.CUSTNO=U2.CUSTNO AND SUBSTR(inputdate,1,6)=SUBSTR(U2.IMPORTTIME,1,6); end getSet; end PKG_PAFUNDSREPORT;