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

简单搞一下 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;