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

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