Test CA
Skiing/Snowboarding, Canada

Powerblog

Searching, sorting and paging in Oracle Store Procedure

create or replace PROCEDURE UP_GETSITEOWNERINFO
(
p_inStartRowIndex in number, p_inEndRowIndex in number,
p_vchSiteCode in VARCHAR2 :=null,
p_vchOwnerName in VARCHAR2 :=null,
p_vchCnic in VARCHAR2 :=null,
p_inSortExp in varchar2, p_outTotalRows out number,
p_cursor OUT sys_refcursor
)
AS
BEGIN
select count(*) into p_outTotalRows from SITE
           inner join SITE_OWNERS  on SITE_OWNERS.vchsitecode= SITE.VCHSITECODE
          inner join OWNER  on OWNER.INTOWNERID= Site_Owners.intownerid
where (p_vchSiteCode is null or SITE.vchsitecode like p_vchSiteCode)
and (p_vchcnic is null or vchcnic like p_vchcnic)
and (p_vchownername is null or
      vchfirstname like p_vchownername);


   open p_cursor for select * from
        (select SITE.vchsitecode,SITE.vchcity,SITE.vchdistrict,SITE.dtmdatecreated
        , OWNER.vchfirstname as OwnerName
        , OWNER.vchcnic
        , ROW_NUMBER()
        OVER
        (ORDER BY
          Decode(p_inSortExp,'VCHSITECODED Ascending',SITE.vchsitecode) ASC,
          Decode(p_inSortExp,'VCHSITECODE Descending',SITE.vchsitecode) DESC,
          Decode(p_inSortExp,'OWNERNAME Ascending',OWNER.vchfirstname) ASC,
          Decode(p_inSortExp,'OWNERNAME Descending',OWNER.vchfirstname) DESC,
          Decode(p_inSortExp,'VCHCNIC Ascending',OWNER.vchcnic) ASC,
          Decode(p_inSortExp,'VCHCNIC Descending',OWNER.vchcnic) DESC,
           SITE.vchsitecode)
           R from SITE
           inner join SITE_OWNERS  on SITE_OWNERS.vchsitecode= SITE.VCHSITECODE
          inner join OWNER  on OWNER.INTOWNERID= Site_Owners.intownerid
where (p_vchSiteCode is null or SITE.vchsitecode like p_vchSiteCode)
and (p_vchcnic is null or vchcnic like p_vchcnic)
and (p_vchownername is null or
      vchfirstname like p_vchownername)
        )
WHERE R BETWEEN ((p_inStartRowIndex - 1) * p_inEndRowIndex + 1) and (p_inStartRowIndex * p_inEndRowIndex) ;
END UP_GETSITEOWNERINFO;

Posted By:Test CA On 20 Sep 2011 AT 22:22 | 0 COMMENTS
External Url:http://ryzn1.blogspot.com/2011/09/searching-sorting-and-paging-in-oracle.html
Topics: General

COMMENTS

     * Log in or Register to post a comment

   

LOCATION

Quebec, Canada

NETWORKS

Skiing/Snowboarding, Athletes/Active Lifestyle

FEATURED NUTRITION ARTICLE

All trademarks and other intellectual properties on this site are owned by Société des Produits Nestlé S.A., Vevey, Switzerland or are used with permission.