oracle多条件查询分页存储过程


项目接近尾声了,感觉将业务逻辑放到oracle中使得后台代码很精简,oracle很有搞头!

  PL\SQL:

  create or replace procedure proc_client_List --客户多条件查询

  (

  pro_cursor out pkg_order.p_cursor, --查询结果集

  characters_ in varchar2,--客户性质

  states_ in varchar2,--客户状态

  type_ in varchar2,--客户类型

  calling_ in varchar2,--客户行业

  name_ in varchar2,--客户名称

  beginTime_ in date,--创建日期上限

  endTime_ in date,--创建日期上限

  area_ in number,--客户地区

  clientsource_ in varchar2,--客户来源

  importent_ in varchar2,--重要程度

  start_row in number,--结果集起始行

  end_row in number--结果集结束行

  ) is

  sql_str varchar2(1000):=

  'select * from

  ( select row_.*, rownum rownum_ from

  (

  select * from clientinfo c

  where(:characters_ is null or c.characters like :characters_)

  and (:states_ is null or c.states like :states_)

  and (:type_ is null or c.type like :type_)

  and (:calling_ is null or c.calling like :calling_)

  and (:name_ is null or c.name like :name_)

  and (:beginTime_ is null or c.createtime > :beginTime_)

  and (:endTime_ is null or c.createtime < :endTime_)

  and (:area_ is null or c.area=:area_)

  and (:clientsource_ is null or c.clientsource like :clientsource_)

  and (:importent_ is null or c.importent like :importent_)

  ) row_ where rownum <= :end_row

  )

  where rownum_ > :start_row';

  begin

  open pro_cursor for sql_str using

  characters_,'%'||characters_||'%',

  states_,'%'||states_||'%',

  type_,'%'||type_||'%',

  calling_,'%'||calling_||'%',

  name_,'%'||name_||'%',

  beginTime_,beginTime_,

  endTime_,endTime_,

  area_,area_,

  clientsource_,'%'||clientsource_||'%',

  importent_,'%'||importent_||'%',

  end_row,start_row;

  end proc_client_List;

  /


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3