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;