建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
Oracle多條件查詢實(shí)際分頁存儲(chǔ)過程實(shí)操
以下的文章主要是介紹Oracle多條件查詢分頁存儲(chǔ)過程，以下就是Oracle多條件查詢分頁存儲(chǔ)過程具體方案的描述，希望在你今后的學(xué)習(xí)中會(huì)有所幫助。將業(yè)務(wù)邏輯放到Oracle中使得后臺(tái)代碼很精簡(jiǎn)，Oracle很有搞頭！
PL\SQL:
create or replace procedure proc_client_List --客戶多條件查詢
(
pro_cursor out pkg_order.p_cursor, --查詢結(jié)果集
characters_ in varchar2,--客戶性質(zhì)
states_ in varchar2,--客戶狀態(tài)
type_ in varchar2,--客戶類型
calling_ in varchar2,--客戶行業(yè)
name_ in varchar2,--客戶名稱
beginTime_ in date,--創(chuàng)建日期上限
endTime_ in date,--創(chuàng)建日期上限
area_ in number,--客戶地區(qū)
clientsource_ in varchar2,--客戶來源
importent_ in varchar2,--重要程度
start_row in number,--結(jié)果集起始行
end_row in number--結(jié)果集結(jié)束行
- ) 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;
上述的相關(guān)內(nèi)容就是對(duì)Oracle多條件查詢分頁存儲(chǔ)過程的描述，希望會(huì)給你帶來一些幫助在此方面。
本文名稱：Oracle多條件查詢實(shí)際分頁存儲(chǔ)過程實(shí)操
當(dāng)前鏈接：http://www.5511xx.com/article/djioeci.html
