项目接近尾声了感觉将业务逻辑放到oracle中使得后台代码很精简oracle很有搞头！
PL\SQL:
create or replace procedure proc_client_List 客户多条件查询
(
pro_cursor out pkg_orderp_cursor 查询结果集
characters_ in varchar客户性质
states_ in varchar客户状态
type_ in varchar客户类型
calling_ in varchar客户行业
name_ in varchar客户名称
beginTime_ in date创建日期上限
endTime_ in date创建日期上限
area_ in number客户地区
clientsource_ in varchar客户来源
importent_ in varchar重要程度
start_row in number结果集起始行
end_row in number结果集结束行
) is
sql_str varchar():=
select * from
( select row_* rownum rownum_ from
(
select * from clientinfo c
where(:characters_ is null or ccharacters like :characters_)
and (:states_ is null or cstates like :states_)
and (:type_ is null or ctype like :type_)
and (:calling_ is null or ccalling like :calling_)
and (:name_ is null or cname like :name_)
and (:beginTime_ is null or ccreatetime > :beginTime_)
and (:endTime_ is null or ccreatetime < :endTime_)
and (:area_ is null or carea=:area_)
and (:clientsource_ is null or cclientsource like :clientsource_)
and (:importent_ is null or cimportent 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_rowstart_row;
end proc_client_List;
/
