Oracle multi-condition Query

The following articles mainly introduce the paging Stored Procedure of Oracle multi-condition query. The following describes the specific solution of the paging Stored Procedure of Oracle multi-condition query. I hope this will help you in your future studies. Putting the business logic in Oracle makes the background code very concise, and Oracle is very creative!

PL \ SQL:

Create or replace procedure proc_client_List -- customer multi-condition Query

(

Pro_cursor out pkg_order.p_cursor, -- query result set

Characters _ in varchar2, -- customer nature

States _ in varchar2, -- customer status

Type _ in varchar2, -- customer type

Calling _ in varchar2, -- customer Industry

Name _ in varchar2, -- CUSTOMER name

BeginTime _ in date, -- upper limit of creation date

EndTime _ in date, -- maximum creation date

Area _ in number, -- customer Region

Clientsource _ in varchar2, -- customer Source

Importent _ in varchar2, -- Importance

Start_row in number, -- start row of the result set

End_row in number -- end row of the result set

 
 

  
  
) 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;   
 
 

The above content is the description of the paging Stored Procedure for Oracle multi-condition query. I hope it will help you in this regard.