熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle多條件查詢分頁存儲過程

2013-11-13 15:33:51  來源: Oracle 

  項目接近尾聲了感覺將業務邏輯放到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;

  /


From:http://tw.wingwit.com/Article/program/Oracle/201311/16924.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.