oracle返回临时表,存储过程返回临时表的问题

嗯，如果没有复杂计算，建议直接用ref cursor。

create or replace package pkg_rome_in_out as

--************************************************

-- ** Get login and logout information of site.

-- ** In parameters: S_RQ

-- ** In parameters: E_RQ

-- ** In parameters: RDC_ID

-- ** In parameters: BRANCH_ID

-- ** In parameters: SITE_NO

-- ** In parameters: PAGE_NO

-- ** Returns:Ref Cursor

-- ** p_in_out_site:Ref Cursor(s_rq,e_rq,online_time)

-- ** p_in_out_branch:Ref Cursor(site_no,s_rq,e_rq,online_time)

-- ** p_in_out_rdc:Ref Cursor(site_no,s_rq,e_rq,online_time)

-- ************************************************

type t_ref is ref cursor;

procedure p_in_out_site(srq in date,erq in date,rdcid in varchar2,siteno in varchar2,page_no in number,num out number,cur_ref out t_ref);

--procedure p_in_out_branch(s_rq in date,e_rq in date,rdcid in varchar2,branchid in varchar2,page_no in number,online_time out varchar,cur_ref out t_ref);

--procedure p_in_out_rdc(s_rq in date,e_rq in date,rdcid in varchar2,page_no in number,online_time out varchar,cur_ref out t_ref);

end pkg_rome_in_out;

/

create or replace package body pkg_rome_in_out as

procedure p_in_out_site(srq in date,erq in date,rdcid in varchar2,siteno in varchar2,page_no in number,num out number,cur_ref out t_ref) is

begin

if page_no is null or page_no<0 then

return;

end if;

select count(*) into num from site_login_logout

where rq >= srq

and rq <= erq

and rdc_id = rdcid

and site_no = siteno;

if page_no = 0 then

open cur_ref for 'select to_char(s_rq,''yyyy-mm-dd hh24:mi:ss'') s_rq,to_char(e_rq,''yyyy-mm-dd hh24:mi:ss'') e_rq,

trunc((e_rq-s_rq)*24)||''小时''||lpad(round(mod((e_rq-s_rq)*(24)*60,60),0),2,''0'')||''分'' online_time

from site_login_logout

where rq >= :s_rq

and rq <= :e_rq

and rdc_id = :rdc_id

and site_no = :site_no

order by rq' using srq,erq,rdcid,siteno;

else

open cur_ref for 'select /*+ FIRST_ROWS */ to_char(s_rq,''yyyy-mm-dd hh24:mi:ss'') s_rq,to_char(e_rq,''yyyy-mm-dd hh24:mi:ss'') e_rq,

trunc((e_rq-s_rq)*24)||''小时''||lpad(round(mod((e_rq-s_rq)*(24)*60,60),0),2,''0'')||''分'' online_time

from (select rownum rn,s_rq,e_rq from site_login_logout

where rq >= :s_rq

and rq <= :e_rq

and rdc_id = :rdc_id

and site_no = :site_no

and rownum <= :row_e

order by rq)

where rn >= :row_s'

using srq,erq,rdcid,siteno,page_no*fn_get_system_config('ROWS_PER_PAGE'),(page_no - 1)*fn_get_system_config('ROWS_PER_PAGE')+1;

end if;

exception when others then

null;

end p_in_out_site;

end pkg_rome_in_out;

/