Oracle查询上一笔交易信息,oracle交易怎么统计

SQL codeCREATE 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

)

IS

SUM_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_RESULT

OUT_RESULT:= ;--填写你想输出的参数就行

END;

------解决方案--------------------

存储过程的写法,注意返回的游标类型我帮你改了下.SQL codeCREATE 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

dbms_output.put_line(sql_txt);

open OUT_RESULT for sql_txt;

OUT_RETCODE := '0';

EXCEPTION

when others then

OUT_RETCODE := '1';

end;