簡單搞一下 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;