oracle存储过程返回游标集。。。写了半天没搞定，oracle大神帮帮我

--申明包结构
create or replace package atii.mypackage as
type mycursor is ref cursor;
procedure queryCount(startDate in date,endDate in date,countList out mycursor);
end mypackage;

--创建包体
create or replace package body atii.mypackage as
procedure queryCount(startDate in date,endDate in date,countList out mycursor)
as
begin
open countList for
select t.createDate,count(t.createDate)
from
(select case when createtime>=(trunc(createtime)+18/24) then trunc(createtime)+1
else trunc(createtime) end createDate
from t_count ) t
where t.createDate>=to_date('2017-08-19','yyyy-MM-dd') and t.createDate<=to_date('2017-08-24','yyyy-MM-dd')
group by t.createDate
order by t.createDate;
end queryCount;
end mypackage;

经过测试是可行的。在包结构中可以声明你要返回的游标类型呢。

一条sql就写出来了：
select newDate ,count(*)
from (
select (case when to_number(to_char(datefield,'hh24'))<18 then trunc(sysdate) else trunc(sysdate+1) end )as newDate
from table
)group by newDate
order by newDate

这个看的我头晕，实在不想写了告诉你思路 上面那个sql的话应该就差了一个between and 只要把between查到的所有时间进行一下转换再根据这个newDate查询count就没问题了

//获取所有时间  
    select (case when to_number(to_char(datefield,'hh24'))<18 then trunc(sysdate) else trunc(sysdate+1) end)，另一个参数  as newDate from table  where time between time_a and time_b    （我也不知道这个转换对不对  自己看吧  ）  这样拿到转换后的时间

ex： 2017-8-19 asd
2017-8-19 asd
2017-8-20 bcd
2017-8-21 bdc
2017-8-22 bdc 这样取出的数据如上 去除了时间的干扰
之后就是分组查询了

create or replace package body mypackage as
procedure queryCount(startDate in date,endDate in date,countList out mycursor)
as
begin
open countList for
select t.createDate,count(t.createDate)
from
(select case when createtime>=(trunc(createtime)+18/24) then trunc(createtime)+1
else trunc(createtime) end createDate
from t_count ) t
where t.createDate>=startDate and t.createDate<=endDate
group by t.createDate
order by t.createDate;
end queryCount;
end mypackage;

改成这样。

是说的这个么，不是的话，说清楚点。

貌似直接查询也可以实现：
sql 分解：
1.统计每天 0点到18点（不包含18点，若包含吧下面的17改为18）的数据
select DATE_FORMAT(create_time, '%Y-%m-%d'),count(1) from user

WHERE DATE_FORMAT(create_time, '%Y-%m-%d %H') BETWEEN DATE_FORMAT(create_time, '%Y-%m-%d 00')
AND DATE_FORMAT(create_time, '%Y-%m-%d 17')
group by DATE_FORMAT(create_time, '%Y-%m-%d') order by DATE_FORMAT(create_time, '%Y-%m-%d') ;

2.统计每天18点（包含18点）以后的数据，按您的要求 把结果作为第二天的数据（即把日期加1天返回）：
select DATE_FORMAT(create_time, '%Y-%m-%d') date_str,count(1) from user

WHERE DATE_FORMAT(create_time, '%Y-%m-%d %H') BETWEEN DATE_FORMAT(create_time, '%Y-%m-%d 18')
AND DATE_FORMAT(create_time, '%Y-%m-%d 23')
group by DATE_FORMAT(create_time, '%Y-%m-%d') order by DATE_FORMAT(create_time, '%Y-%m-%d') ;

3.sql 合并把 18点以后数据 根据日期相加并根据日期排序：
select date_str, sum(num) from (
select DATE_FORMAT(create_time, '%Y-%m-%d') date_str,count(1) num from user
WHERE DATE_FORMAT(create_time, '%Y-%m-%d %H') BETWEEN DATE_FORMAT(create_time, '%Y-%m-%d 00')
AND DATE_FORMAT(create_time, '%Y-%m-%d 17')
group by DATE_FORMAT(create_time, '%Y-%m-%d')
union all
select DATE_FORMAT(date_sub(create_time,interval -1 day), '%Y-%m-%d') date_str,count(1) num from user
WHERE DATE_FORMAT(create_time, '%Y-%m-%d %H') BETWEEN DATE_FORMAT(create_time, '%Y-%m-%d 18')
AND DATE_FORMAT(create_time, '%Y-%m-%d 23')
group by DATE_FORMAT(create_time, '%Y-%m-%d') ) a group by date_str order by date_str

ps: user 是表名

declare
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;

时间取如果小于18点算成当天 否则暂时不取 每次只取当天