Question

oracle数据库中提示标识符错误

这个是我的分页存储过程
不知道如何在v_sql变量中，‘是’为什么出错
create or replace
procedure selectnowgoods(pagenow in number,pagesize in number ,
myrows out number,mypagecount out number,mycursor out mypage.cursor_emp)
is
v_sql VARCHAR2(1000);
v_rules VARCHAR2(1000);
v_begin NUMBER:=(pagenow-1)* pagesize+1;
v_end NUMBER:= pagenow* pagesize;

BEGIN
v_sql:='select * from(select t1.*, rownum rn from (select * from tb_goods where newgoods=''是'' ) t1 where rownum <='|| v_end||') where rn>='|| v_begin;
open mycursor for v_sql;
v_sql:='select count(*) from tb_goods where newgoods=''是';
EXECUTE IMMEDIATE v_sql into myrows;

if mod(myrows, pagesize)=0 then
mypagecount:=myrows/ pagesize;
else
mypagecount:= myrows/ pagesize+1;
end if;

end;
这是报错的信息

ORA-00904: "是": 标识符无效
ORA-06512: 在 "SHUTIAN.SELECTNOWGOODS", line 12
ORA-06512: 在 line 11
这是个简单的错误，就是不懂怎么规范
急啊，哪位大神来搭救我呀

Answer 1

试试

create or replace
procedure selectnowgoods(pagenow in number,pagesize in number ,
myrows out number,mypagecount out number,mycursor out mypage.cursor_emp)
is
v_sql VARCHAR2(1000);
v_rules VARCHAR2(1000);
v_begin NUMBER:=(pagenow-1)* pagesize+1;
v_end NUMBER:= pagenow* pagesize;

BEGIN
v_sql:='select * from(select t1.*, rownum rn from (select * from tb_goods where newgoods=''是'' ) t1 where rownum <='|| v_end||') where rn>='|| v_begin || '';
open mycursor for v_sql;
v_sql:='select count(*) from tb_goods where newgoods=''是''';
EXECUTE IMMEDIATE v_sql into myrows;

if mod(myrows, pagesize)=0 then
mypagecount:=myrows/ pagesize;
else
mypagecount:= myrows/ pagesize+1;
end if;

end;

Answer 2

v_sql:='select count(*) from tb_goods where newgoods=''是';
你这代码是粘的吗？
印象中语句是v_sql:=“select count(*) from tb_goods where newgoods='是' ";不知道是不是我记错了

追问

不是沾的，oracle不用双引号，用单引

追答

那我记得表varchar2的字段值是''引得也记错了？