create or replace procedure page_pro_singer(area in varchar2,sex in varchar2,
currentpage in number,totalrecord out number,totalpage out number, p_cursor
out page_package.page_cursor
)
is
everypage int:=4;
v_sql varchar2(1000);
v_begin number:=(currentpage-1)*everypage+1;
v_end number:=everypage*currentpage;begin
v_sql:='select * from(select t1.* ,rownum rnum from (select * from music where singerarea='||area||' and singersex='||sex||' ) t1
where rownum<='||v_end||') where rnum>='||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from music';execute immediate v_sql into totalrecord;
if mod(totalrecord,everypage)=0 then
totalpage:=totalrecord/everypage;
else
totalpage:=(totalrecord/everypage)+1;
end if;
end;
currentpage in number,totalrecord out number,totalpage out number, p_cursor
out page_package.page_cursor
)
is
everypage int:=4;
v_sql varchar2(1000);
v_begin number:=(currentpage-1)*everypage+1;
v_end number:=everypage*currentpage;begin
v_sql:='select * from(select t1.* ,rownum rnum from (select * from music where singerarea='||area||' and singersex='||sex||' ) t1
where rownum<='||v_end||') where rnum>='||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from music';execute immediate v_sql into totalrecord;
if mod(totalrecord,everypage)=0 then
totalpage:=totalrecord/everypage;
else
totalpage:=(totalrecord/everypage)+1;
end if;
end;
java.sql.SQLException: ORA-00904: "男": 标识符无效
ORA-06512: 在 "MYKTV.PAGE_PRO_SINGER", line 14
ORA-06512: 在 line 1
--估计是字符型拼接错了,如下:create or replace procedure page_pro_singer(area in varchar2,sex in varchar2,
currentpage in number,totalrecord out number,totalpage out number, p_cursor
out page_package.page_cursor
)
is
everypage int:=4;
v_sql varchar2(1000);
v_begin number:=(currentpage-1)*everypage+1;
v_end number:=everypage*currentpage;begin
v_sql:='select * from(select t1.* ,rownum rnum from (select * from music where singerarea='||area||' and singersex='''||sex||''' ) t1 --修改singersex
where rownum<='||v_end||') where rnum>='||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from music';execute immediate v_sql into totalrecord;
if mod(totalrecord,everypage)=0 then
totalpage:=totalrecord/everypage;
else
totalpage:=(totalrecord/everypage)+1;
end if;
end;
上面只是举了singersex,你看看是否还有其它栏位,如singerarea
你这样拼接的话
传入参数后是这样的结果
select * from music where and singersex=男
而正确的应该是
select * from music where and singersex='男'
所以字段是字符型的话拼接要这样
and singersex='''||sex||'''