项目接近尾声了,感觉将业务逻辑放到oracle中使得后台代码很精简,oracle很有搞头!
PLSQL:
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;
/
—-想了解更多的数据库相关异常处理怎么解决关注<计算机技术网(www.ctvol.com)!!>
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/79056.html