chenjiexixi
2017-08-22 03:14
采纳率: 21.4%
浏览 2.3k

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

需要查询某个表(tablename)参数,一段时间(开始日期到结束日期)的count数。
我自己想法是声明一个游标来遍历开始日期到结束日期,把每一天的count和这天的日期都装到另一个游标里(我不知道怎么写出返回游标装个对象这种)。最烦的就是有个条件需要把18点以后的数据算到第二天。也就是说如果数据时间为2017/8/22 18:00,那这条数据的时间应该是2017/8/23。所以很麻烦,拜托oracle大神帮帮我!!!
反正最后的结果是:
2017-8-19 52
2017-8-20 45
2017-8-21 56
2017-8-22 12
这个时间也不是固定的,也是参数,最好还是排下序,谢谢!!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • linlin_1998 2017-08-22 07:25
    已采纳

    --申明包结构
    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;

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

    点赞 评论
  • cloudyzhao 2017-08-22 05:21

    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点算成当天 否则暂时不取 每次只取当天

    点赞 评论
  • lihongbiao0610 2017-08-22 05:42

    一条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

    点赞 评论
  • Tsui丶 2017-08-22 06:54

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

    点赞 评论
  • Tsui丶 2017-08-22 07:16
    //获取所有时间  
        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 这样取出的数据如上 去除了时间的干扰
    之后就是分组查询了

    点赞 评论
  • linlin_1998 2017-08-22 07:47

    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;

    改成这样。

    点赞 评论
  • linlin_1998 2017-08-22 08:16

    图片说明
    是说的这个么,不是的话,说清楚点。

    点赞 评论
  • kerwinzhou 2017-08-22 08:38

    貌似直接查询也可以实现:
    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 是表名

    点赞 评论

相关推荐 更多相似问题