如何实现Oracle多条件查询的分页存储过程

如何实现Oracle多条件查询的分页存储过程

项目接近尾声了源码天空，感觉将业务逻辑放到Oracle中使得后台代码很精简，Oracle很有搞头！PL\SQL:create or replace procedure proc_client_List --客户Oracle多条件查询(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; 上述的相关内容就是对Oracle多条件查询的分页存储过程的描述，希望会给你带来一些帮助在此方面。

额