简单搞一下 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 isnotnulland p_type <>0then
      v_sql := v_sql ||' and a.goods_type ='|| p_type;
    endif;
    if p_keywords isnotnullthen
      v_sql := v_sql ||' and a.goods_name like ''%'|| p_keywords ||'%''';
    endif;
  
    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 isnotnulland p_type <>0then
      v_sql := v_sql ||' and a.goods_type ='|| p_type;
    endif;

关键字匹配

if p_keywords isnotnullthen
      v_sql := v_sql ||' and a.goods_name like ''%'|| p_keywords ||'%''';
    endif;

动态执行SQL,并将执行结果复制与传出参数 p_number

execute immediate v_sql
      into p_number;