oracle 存储过程 返回结果集

 

CREATE PROCEDURE prc_query_user(endTime IN  varchar2,oCursor OUT SYS_REFCURSOR) IS
BEGIN
open oCursor for
  select a.user_id,
         a.user_name,
         b.dept_name,
         b.pic_id
    from (select user_id, max(record_id) max_id
            from tb_record
           where datetime <=
                 to_date(to_char(s_curdate(), 'YYYY-MM-DD') || ' ' || endTime || ':00', 'YYYY-MM-DD HH24:MI:SS')
           group by user_id) t,
         tb_record a,
         t_user b
   where a.user_id = t.user_id
     and a.record_id = max_id
     and a.user_id > 0
     and a.user_id = b.user_id
   order by record_iddesc;
END;
/