簡單搞一下 Oracle 預存程序動態SQL之擷取查詢總數！

簡單的搞一下PL/SQL 預存程序開發

動態SQL實現 預存程序分頁

procedure proc_goods_search_count(p_type     number,
                                  p_keywords varchar2,
                                  p_number   out number) is
    v_sql varchar2(4000);
begin
    v_sql := ' select count(*)
                    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;

execute immediate v_sql
into p_number;

end proc_goods_search_count;

擷取查詢商品總數預存程序定義

procedure proc_goods_search_count(p_type     number,
                                  p_keywords varchar2,
                                  p_number   out number) 

傳入參數：商品類型 p_type、查詢關鍵字 p_keywords

傳出參數：查詢總數 p_number 

帶組裝動態SQL字串

v_sql varchar2(4000);

商品類型判斷篩選

 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;

動態執行SQL,並將執行結果複製與傳出參數 p_number

 execute immediate v_sql
into p_number;