动态SQL

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;