Orcale 分页可以看一下这一篇 简单搞一下 Oracle 存储过程动态SQL之获取查询分页!
先简单试一下
SELECT
*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
)
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
)
as
temp
where
Row between
1
and
10
看来还行......
继续改写一下:
declare
@v_index int ,
@v_size int
begin
set @v_index = 1 ;
set @v_size = 10 ;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
)
as temp where Row
between (@v_index - 1 ) * @v_size + 1
@v_index int ,
@v_size int
begin
set @v_index = 1 ;
set @v_size = 10 ;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
)
as temp where Row
between (@v_index - 1 ) * @v_size + 1
and (@v_index
-
1
)
*
@v_size
+
@v_size
end;
end;
用with 语句再进行改写一下,模仿别人写成存储过程
create procedure proc_get_test_list
(
@p_index int ,
@p_size int
)
as
begin
with temptb as
(
select row_number() over (order by id desc) as row, * from test
)
select * from temptb where row between
(@p_index - 1 ) * @p_size + 1 and (@p_index - 1 ) * @p_size + @p_size
end
(
@p_index int ,
@p_size int
)
as
begin
with temptb as
(
select row_number() over (order by id desc) as row, * from test
)
select * from temptb where row between
(@p_index - 1 ) * @p_size + 1 and (@p_index - 1 ) * @p_size + @p_size
end
传说这样写 可以提升语句效率
普通查询语句在1000条的条件下,响应时间几乎忽略!
测试一下存储过程:
应该算不分上下吧...... 后面再继续研究!