楼主你的程序有几点出现错误
1、在过程创建临时表不能直接写,用动态写
如:
str:='create global temporary table tmp_somebzdm as ...'
execute immediate str;
2、create global temporary table tmp_somebzdm as
select * from ....
这样语句在过程体内相当于
create global temporary table tmp_somebzdm as
select * from table 1=2;
因为oracle只允许临时表获得其表的结构,而不带记录.对于以上错误,建义楼主使用游标实现,而中间省去临时表的作用:create or replace package body jxkh_pkg_getsomebzdm
as
procedure jxkh_sp_getsomebzdm
(
p_jjdepartmentid in jxkh_dir_bzdm.jjdepartmentid%type,p_kjdepartmentid in jxkh_dir_bzdm.kjdepartmentid%type,
p_pagesize in number,p_currentpage in number,p_sortcolumn in varchar2,p_sort varchar2,somebzdm out refCursor
)
is
str varchar2(2000);
begin
if lower(p_sort)='desc' then
open somebzdm for select * from (select * from jxkh_view_bzdm where jjdepartmentid=p_jjdepartmentid and kjdepartmentid=p_kjdepartmentid order by p_sortcolumn desc) where rownum<(((1+p_currentpage)*p_pagesize)+1) minus
select * from tmp_somebzdm where rownum<(p_currentpage*p_pagesize+1);
else
open somebzdm for select * from (select * from jxkh_view_bzdm where jjdepartmentid=p_jjdepartmentid and kjdepartmentid=p_kjdepartmentid order by p_sortcolumn) where rownum<(((1+p_currentpage)*p_pagesize)+1) minus
select * from tmp_somebzdm where rownum<(p_currentpage*p_pagesize+1);
end if;
end jxkh_sp_getsomebzdm ;
end;
1、在过程创建临时表不能直接写,用动态写
如:
str:='create global temporary table tmp_somebzdm as ...'
execute immediate str;
2、create global temporary table tmp_somebzdm as
select * from ....
这样语句在过程体内相当于
create global temporary table tmp_somebzdm as
select * from table 1=2;
因为oracle只允许临时表获得其表的结构,而不带记录.对于以上错误,建义楼主使用游标实现,而中间省去临时表的作用:create or replace package body jxkh_pkg_getsomebzdm
as
procedure jxkh_sp_getsomebzdm
(
p_jjdepartmentid in jxkh_dir_bzdm.jjdepartmentid%type,p_kjdepartmentid in jxkh_dir_bzdm.kjdepartmentid%type,
p_pagesize in number,p_currentpage in number,p_sortcolumn in varchar2,p_sort varchar2,somebzdm out refCursor
)
is
str varchar2(2000);
begin
if lower(p_sort)='desc' then
open somebzdm for select * from (select * from jxkh_view_bzdm where jjdepartmentid=p_jjdepartmentid and kjdepartmentid=p_kjdepartmentid order by p_sortcolumn desc) where rownum<(((1+p_currentpage)*p_pagesize)+1) minus
select * from tmp_somebzdm where rownum<(p_currentpage*p_pagesize+1);
else
open somebzdm for select * from (select * from jxkh_view_bzdm where jjdepartmentid=p_jjdepartmentid and kjdepartmentid=p_kjdepartmentid order by p_sortcolumn) where rownum<(((1+p_currentpage)*p_pagesize)+1) minus
select * from tmp_somebzdm where rownum<(p_currentpage*p_pagesize+1);
end if;
end jxkh_sp_getsomebzdm ;
end;
我在广东江门,做程序的