简单搞一下 Oracle 存储过程动态SQL之获取查询分页！

Oracle 动态SQL之查询分页

原理：动态组装SQL，利用oracle rownum 虚列嵌套查询实现！

procedure proc_goods_search_list( p_type number ,
p_keywords varchar2 ,
p_index number ,
p_size number ,
p_order number ,
p_cursor out p_cursor) is
v_start NUMBER ( 10 );
v_end NUMBER ( 10 );
v_sql varchar2 ( 4000 );
begin
v_start : = (p_index - 1 ) * p_size + 1 ;
v_end : = p_index * p_size;

v_sql : = ' select a.serialno as goodsId,
a.goods_name as goodsName,
a.sale_price as price,
a.sale_amount as saleAmount,
a.comment_count as commentCount,
a.goods_images as goodsImage,
a.promotion_type as promotionType,
nvl(a.promotion_price,0) as promotionPrice,
a.publish_time as createTime,
a.promotion_start_time as promotionStartTime,
a.promotion_end_time as promotionEndTime
from lt_goods a
where a.status = 2 ' ;

if p_type is not null and p_type <> 0 then
v_sql : = v_sql || ' and a.goods_type = ' || p_type;
end if ;
if p_keywords is not null then
v_sql : = v_sql || ' and a.goods_name like '' % ' || p_keywords || ' % ''' ;
end if ;

if p_order is not null and p_order = 0 then
-- 全部商品
v_sql : = v_sql || ' order by goodsId desc ' ;
end if ;

if p_order is not null and p_order = 1 then
-- 时间
v_sql : = v_sql || ' order by createTime desc ' ;
end if ;

if p_order is not null and p_order = 2 then
-- 销量
v_sql : = v_sql || ' order by saleAmount desc ' ;
end if ;

if p_order is not null and p_order = 3 then
-- 价格
v_sql : = v_sql || ' order by price desc ' ;
end if ;
v_sql : = ' select m.*,rownum as id from ( ' || v_sql || ' )m ' ;
v_sql : = ' select * from ( ' || v_sql || ' ) where id <= ' ||
to_char(v_end);
v_sql : = ' select * from ( ' || v_sql || ' ) where id >= ' ||
to_char(v_start);
/ ×dbms_output.put_line(v_sql);× /
open p_cursor for v_sql;

end proc_goods_search_list;

查询分页存储过程定义

procedure proc_goods_search_list( p_type number ,
p_keywords varchar2 ,
p_index number ,
p_size number ,
p_order number ,
p_cursor out p_cursor)

 参数说明：

  传入参数： 商品类型        p_type,

                 查询关键字     p_keywords,

                 分页索引        p_index,

                 页包含数量     p_size,

                 排序标识        p_order,

  传出参数： 查询游标       p_cursor

动态SQL局部变量及分页算法

分页原理，通过利用rownum oracle 虚列标识，通过两层嵌套查询实现

v_start NUMBER ( 10 );
v_end NUMBER ( 10 );
v_sql varchar2 ( 4000 );

 

v_start := (p_index -1) * p_size +1;
v_end := p_index * p_size;

v_sql : = ' select m.*,rownum as id from ( ' || v_sql || ' )m ' ;
v_sql : = ' select * from ( ' || v_sql || ' ) where id <= ' ||
to_char(v_end);
v_sql : = ' select * from ( ' || v_sql || ' ) where id >= ' ||
to_char(v_start);

调试组装SQL

dbms_output.put_line(v_sql);

动态执行并输出查询列表

open p_cursor for v_sql;

转载于:https://www.cnblogs.com/darjuan/archive/2011/05/24/oracle_procedure_pager.html