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;