create or replace procedure Sp_Rpt_Catering(
iStartDate date ,--- 统计开始时间
iEndDate date , --- 统计结束时间
iTimeUnit int , --- 计单位 1天;2.周;3.年
rCursor out pack_Service.T_RetDataSet -- 返回结果集
) is revenue_pro number; -- 省号确认收入(元)
revenue_serv number; -- 业支确认收入(元)
revenueTotal number; -- 累计业务收入(元)
callAmount number; -- 总话务量
callTrouAmount number; -- 其中接通话务量
amount_book number; -- 预定量
amount_succ number; -- 成功订单量(张)
transRate number; -- 订单转化率
table_name varchar2(40);
sql_tmp varchar2(100);
begin
savepoint point1;
select 444444444444 into amount_book from cateringorder t where t.orderdate between iStartDate and iEndDate ;
select count(*) into amount_succ from cateringorder t where upper(t.isdiscard) ='FALSE' and t.orderdate between iStartDate and iEndDate ;
select count(*)/amount_book into transRate from cateringorder t where upper(t.isdiscard) ='FALSE' and t.orderdate between iStartDate and iEndDate ;
open rCursor for
select 333 revenue_pro,434 revenue_serv,454 revenueTotal,
54 callAmount,555 callTrouAmount, amount_book,
44 amount_succ, 444 transRate from dual ; dbms_output.put_line(amount_book||'=='||amount_succ||'=='||amount_book); exception
when others then
rollback to savepoint point1;
return ;
end Sp_Rpt_Catering;
执行之后 :java.sql.SQLException: Cursor is closed.
在 补充一点 。。 如果我 去掉 上边的 3个 select,就可以不出错! 不知道为什么?
select 444444444444 into amount_book
from cateringorder t where t.orderdate between iStartDate and iEndDate ;
--你这个语句不就是 amount_book:=444444444444 吗?select count(*) into amount_succ
from cateringorder t
where upper(t.isdiscard) ='FALSE' and t.orderdate between iStartDate and iEndDate ;select count(*)/amount_book into transRate
from cateringorder t
where upper(t.isdiscard) ='FALSE' and t.orderdate between iStartDate and iEndDate ;
--这个不就是transRate:=amount_succ/amount_book吗?