create or replace procedure pro_get_mob_num(v_area_code in varchar2,
v_num_header in varchar2,
v_num_level in varchar2,
v_min_store in varchar2,
v_max_store in varchar2,
r_row_count out number,
r_cur out TESTPACKAGE.Test_CURSOR)
Authid Current_User is
p_where varchar2(1000) := '';
p_sql varchar2(4000);
begin
--拼装SQL语句
if v_area_code is not null and length(v_area_code) > 0 then
p_where := p_where || ' and t.area_code=''' || v_area_code || '''';
end if;
if v_num_header is not null and length(v_num_header) > 0 then
p_where := p_where || ' and t.num_head=''' || v_num_header || '''';
end if;
if v_num_level is not null and length(v_num_level) > 0 then
p_where := p_where || ' and t.num_level=''' || v_num_level || '''';
end if;
if v_min_store is not null and length(v_min_store) > 0 then
p_where := p_where || ' and t.pre_store>=' || to_number(v_min_store);
end if;
if v_max_store is not null and length(v_max_store) > 0 then
p_where := p_where || ' and t.pre_store<=' || to_number(v_max_store);
end if;
--1、返回总行数
p_sql := 'select count(*) as rcount from ym_mob_num t where t.state = 1' ||
p_where;
execute immediate p_sql
into r_row_count;
--2、返回查询结果
p_sql := 'select t.id,t.mob_num,t.uim_price,t.pre_store,t.num_head,t.num_level,t.area_code,t.state from ym_mob_num sample(10) t where t.state = 1' ||
p_where || ' and rownum <= 20';
open r_cur for p_sql;
end pro_get_mob_num;