有个转账流水表TRAN_FLOW，字段：FLOW_NO,CLIENT_NO（客户号）,PAY_ACCOUT,PAY_ACCOUNT_NAME（客户姓名）,RECEIVE_ACCOUNT（接受账号）,TRAN_DATE（日期）,AMOUNT(金额),STATUS（状态）
STATUS为90时成功，91时为失败。
A、输入：统计起始日期（必输），统计截止日期（笔数），客户号（可空），客户姓名（可空）；
B、输出：客户号、转账成功总笔数、转账失败总笔数、转账成功总金额、转账失败总金额、转账总笔数、转账总金额
自己写了包头，下面不会写了，求高手帮助下。
PROCEDURE PROC_STATIS_TRANSFER
(
IN_BEGINDATE in VARCHAR2,
IN_ENDDATE in VARCHAR2,
IN_CLIENTNO IN VARCHAR2,
IN_CLIENTNAME IN VARCHAR2,
OUT_RETCODE OUT VARCHAR2,
OUT_RESULT OUT REF_CURSOR
);
------解决方案--------------------
- SQL code
CREATE OR REPLACE PROCEDURE PROC_STATIS_TRANSFER( IN_BEGINDATE in VARCHAR2, IN_ENDDATE in VARCHAR2, IN_CLIENTNO IN VARCHAR2, IN_CLIENTNAME IN VARCHAR2, OUT_RETCODE OUT VARCHAR2, OUT_RESULT OUT REF_CURSOR )ISSUM_CNUMBER;--转账成功总笔数SUN_SNUMBER;--转账失败总笔数SUM_CAMOUT;--转账成功总金额SUM_SAMOUT;--转账失败总金额SUM_SUMNUMBER;--转账总笔数SUM_SUNAMOUT;--转账总金额BEGIN SELECT SUM(*),SUM(AMOUNT) INTO SUM_CNUMBER,SUM_CAMOUT FROM TRAN_FLOW WHERE STATUS=90 (AND TRAN_DATE BETWEEN TO_DATE(IN_BEGINDATE, 'yyyy/MM/dd HH24:mi:ss') AND TO_DATE(IN_ENDDATE, 'yyyy/MM/dd HH24:mi:ss') AND CLIENT_NO=IN_CLIENTNO);--后面为条件SELECT SUM(*),SUM(AMOUNT) INTO SUM_SNUMBER,SUM_SAMOUT FROM TRAN_FLOW WHERE STATUS=91 (AND TRAN_DATE BETWEEN TO_DATE(IN_BEGINDATE, 'yyyy/MM/dd HH24:mi:ss') AND TO_DATE(IN_ENDDATE, 'yyyy/MM/dd HH24:mi:ss') AND CLIENT_NO=IN_CLIENTNO);SELECT SUM(*),SUM(AMOUNT) INTO SUM_SUMNUMBER,SUM_SUNAMOUT FROM TRAN_FLOW; OUT_RETCODE:= ;--填写你想输出的参数就行 OUT_RESULTOUT_RESULT:= ;--填写你想输出的参数就行END;
------解决方案--------------------
存储过程的写法,注意返回的游标类型我帮你改了下.
- SQL code
CREATE OR REPLACE PROCEDURE PROC_STATIS_TRANSFER( IN_BEGINDATE in VARCHAR2, IN_ENDDATE in VARCHAR2, IN_CLIENTNO IN VARCHAR2, IN_CLIENTNAME IN VARCHAR2, OUT_RETCODE OUT VARCHAR2, OUT_RESULT OUT SYS_REFCURSOR)as sql_txt varchar2(1000);begin sql_txt := 'select min(CLIENT_NO), sum(case STATUS when 90 then 1 else null end), --转账成功总笔数 sum(case STATUS when 91 then 1 else null end), --转账失败总笔数 sum(case STATUS when 90 then AMOUNT else null end), --转账成功总金额 sum(case STATUS when 91 then AMOUNT else null end), --转账失败总金额 count(*), sum(AMOUNT) from TRAN_FLOW where CLIENT_NO=' || IN_CLIENTNO || ' and TRAN_DATE>to_date(''' || IN_BEGINDATE || ''') and TRAN_DATE<to_date(''' || IN_ENDDATE || ''')'; dbms_output.put_line(sql_txt); open OUT_RESULT for sql_txt; OUT_RETCODE := '0';EXCEPTION when others then OUT_RETCODE := '1';end;