Let's take a look at the total number of queries obtained from dynamic SQL statements in Oracle stored procedures!

Simple development of PL/SQL stored procedures

Paging of stored procedure using dynamic 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;

Obtain the Stored Procedure Definition for querying the total number of items

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

Input parameter: product type p_type, query keyword p_keywords

Outgoing parameter: Total number of queries p_number

Assemble dynamic SQL strings

v_sql varchar2(4000);

Product type filtering

 if p_type is not null and p_type <> 0 then
      v_sql := v_sql || ' and a.goods_type =' || p_type;
end if;

Keyword match

 if p_keywords is not null then
      v_sql := v_sql || ' and a.goods_name like ''%' || p_keywords || '%''';
end if;

Dynamically execute the SQL statement and copy and transmit the execution result to the p_number parameter.

 execute immediate v_sql
into p_number;