Procedure Seledataformatch(
SQLWHERE in VARCHAR2,
L_PAGESIZE in number,
L_PAGENUM in number,
tempCursor out T_CURSOR
)
is
PageLowerBound number;
PageUpperBound number;
begin
PageLowerBound := L_PAGESIZE * L_PAGENUM;
PageUpperBound := PageLowerBound + L_PAGESIZE + 1;
OPEN tempCursor FOR
SELECT
ROWNUM,t.name,t.sing,t.id,t.datetime,t.coid,t.operatorsname
FROM(
select
ROWNUM,tt.name,tc.sing,tt.id,tc.datetime,tt.coid,tt.operatorsname
from toptest tt
where tt.isdisplay =SQLWHERE ) t
WHERE ROWNUM > PageLowerBound AND PageUpperBound ;在sqlwhere中我输入一个字符串
例:0 and username is not null and colname is not null
PAGESIZE = 20
pagenum = 1
为什么老提示我数字无效?我在sqlwindow里搞成sql语句测试可以出来结果.但当我输入pagesize=20,pagenum=2时就出不来数据啦.实际是有数据的.这儿哪儿写错啦?
SQLWHERE in VARCHAR2,
L_PAGESIZE in number,
L_PAGENUM in number,
tempCursor out T_CURSOR
)
is
PageLowerBound number;
PageUpperBound number;
begin
PageLowerBound := L_PAGESIZE * L_PAGENUM;
PageUpperBound := PageLowerBound + L_PAGESIZE + 1;
OPEN tempCursor FOR
SELECT
ROWNUM,t.name,t.sing,t.id,t.datetime,t.coid,t.operatorsname
FROM(
select
ROWNUM,tt.name,tc.sing,tt.id,tc.datetime,tt.coid,tt.operatorsname
from toptest tt
where tt.isdisplay =SQLWHERE ) t
WHERE ROWNUM > PageLowerBound AND PageUpperBound ;在sqlwhere中我输入一个字符串
例:0 and username is not null and colname is not null
PAGESIZE = 20
pagenum = 1
为什么老提示我数字无效?我在sqlwindow里搞成sql语句测试可以出来结果.但当我输入pagesize=20,pagenum=2时就出不来数据啦.实际是有数据的.这儿哪儿写错啦?
FROM SCOTT.EMP
WHERE ROWID IN (SELECT RID
FROM (SELECT ROWNUM RNO, ROWID RID
FROM (SELECT ROWID
FROM SCOTT.EMP
WHERE DEPTNO = 10
ORDER BY ENAME)
WHERE ROWNUM <= 3)
WHERE RNO >= 1)
WHERE ROWNUM > PageLowerBound AND PageUpperBound ; 语法应该这么写:
WHERE ROWNUM between PageLowerBound AND PageUpperBound ; 单实际上rownum无法取PageLowerBound>1的记录.也就是你为什么输入page=2时,没有记录的原因。rownum的使用参见1楼。