create table USERINFO2
(
USER_ID CHAR(32) not null,
USER_CODE VARCHAR2(32) not null,
PASSWORD CHAR(32) not null,
USER_NAME VARCHAR2(50) not null
)insert into userinfo2 values('001','aaa','555','ycyc');
insert into userinfo2 values('002','bbb','666','iuiu');
insert into userinfo2 values('003','ccc','777','lolo');
insert into userinfo2 values('004','ddd','888','lyly');
insert into userinfo2 values('005','eee','999','lili');
insert into userinfo2 values('006','fff','1010','hyhy'); /*单执行sql语句实现分页可以实现(每页记录数两条,显示第三页数据)
select * from userinfo2 where rownum<=每页记录数 and user_id not in
(select user_id from userinfo2 where rownum<=(页数-1)*每页记录数)
*/
select * from userinfo2 where rownum<=2 and user_id not in
(select user_id from userinfo2 where rownum<=4)/*写一个分页的存储过程遇到问题*/
create or replace procedure splitPage
(pageSizev in varchar2,
intPage in varchar2)
as
declare
str varchar(300);
begin
str :='select * from userinfo where rownum<='+pageSize+' and user_id not in
(select user_id from userinfo where rownum<='+(intPage-1)*pageSize +')';
execute immediate str;
end;
/*报错如下:
PROCEDURE SYSTEM.SPLITPAGE 编译错误错误: PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
符号 "begin" 被替换为 "DECLARE" 后继续。
行: 5
文本: declare错误: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行: 12
请问这个分页的存储过程如何写才正确?*/
(
USER_ID CHAR(32) not null,
USER_CODE VARCHAR2(32) not null,
PASSWORD CHAR(32) not null,
USER_NAME VARCHAR2(50) not null
)insert into userinfo2 values('001','aaa','555','ycyc');
insert into userinfo2 values('002','bbb','666','iuiu');
insert into userinfo2 values('003','ccc','777','lolo');
insert into userinfo2 values('004','ddd','888','lyly');
insert into userinfo2 values('005','eee','999','lili');
insert into userinfo2 values('006','fff','1010','hyhy'); /*单执行sql语句实现分页可以实现(每页记录数两条,显示第三页数据)
select * from userinfo2 where rownum<=每页记录数 and user_id not in
(select user_id from userinfo2 where rownum<=(页数-1)*每页记录数)
*/
select * from userinfo2 where rownum<=2 and user_id not in
(select user_id from userinfo2 where rownum<=4)/*写一个分页的存储过程遇到问题*/
create or replace procedure splitPage
(pageSizev in varchar2,
intPage in varchar2)
as
declare
str varchar(300);
begin
str :='select * from userinfo where rownum<='+pageSize+' and user_id not in
(select user_id from userinfo where rownum<='+(intPage-1)*pageSize +')';
execute immediate str;
end;
/*报错如下:
PROCEDURE SYSTEM.SPLITPAGE 编译错误错误: PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
符号 "begin" 被替换为 "DECLARE" 后继续。
行: 5
文本: declare错误: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行: 12
请问这个分页的存储过程如何写才正确?*/
select * from (select A.*,rownum r from A where rownum<100) where r>0
(pageSize in varchar2,
intPage in varchar2)
is
str varchar(300);
begin
str :='select * from userinfo where rownum<='||pageSize||' and user_id not in
(select user_id from userinfo where rownum<='||to_char((intPage-1)*pageSize) || ')';
execute immediate str;
end;