DB2 database General Stored Procedure paging program "bibimbap"

This article mainly describes the paging program for general stored procedures in DB2 databases. If you encounter a paging program for general stored procedures in DB2 databases during actual operations, however, if you do not know how to operate on it correctly, the following articles must be good teachers and friends for you.

Create procedure sales. DB2PAGINATION (in itbname varchar (2000), -- table name

In ishowfield varchar (1000), -- display field

In ijoin varchar (1000), -- join conditions such as inline and external connections)

In iwhere varchar (2000), -- Query condition (Note: Do not add WHERE)

In iorder varchar (100), -- sorting condition (Note: Do not add order)

In ipagesize integer, -- if the page size is 0, the first 1 million pieces of data are returned by default.

Inout iocurrentpageix integer, -- input and output: Current page

Out opagestartrow integer, -- output: Current start line

Out opageendrow integer, -- output: current end row

Out ototalrows integer, -- output: current total number of records

Out ohaspreviouspage integer, -- output: whether the previous page exists

Out ohasnextpage integer, -- output: whether the next page exists

Out ototalpages integer, -- output: Total number of pages

Out oerror varchar (100) -- output: error message

Result sets 1

MODIFIES SQL DATA

NOT DETERMINISTIC

LANGUAGE SQL

BEGIN

/**//*----------------------------------------------------------------

* Copyright (C) 2007 Huacius

* All Rights Reserved.

*

* Stored procedure page

*

*

//-----------------------------------------------------------------------*/

Declare strsql varchar (6000); -- subject sentence

DECLARE result cursor with return to caller for S2;

DECLARE exit handler FOR sqlexception -- Exception capture FOR paging programs of common stored procedures in DB2 database

 
 

  
  
BEGIN  
  
  
set OERROR = 'error!';  
  
  
END;  
  
  
-- BODY start --  
  
  
if(iwhere <> '') then  
  
  
set iwhere = ' where ' || iwhere;  
  
  
end if;  
  
  
if(iorder <> '') then  
  
  
set iorder = 'order by ' || iorder;  
  
  
end if;  
  
  
if(ijoin <> '') then  
  
  
set ijoin = ' ' || ijoin;  
  
  
end if;  
  
  
set strsql = 'select count(*) from ' || itbname || ijoin || iwhere;  
  
  
prepare s2 from strsql;  
  
  
open result;  
  
  
fetch result into ototalrows;  
 
 

Total number of records

Close result;

If (ipagesize = 0) then

Set ipagesize = 1000000; -- number of entries displayed per page

End if;

Set ototalpages = (ototalrows-1)/ipagesize + 1; -- total number of pages

If (iocurrentpageix <1) then

Set iocurrentpageix = 1; -- current page

Else

If (iocurrentpageix> ototalpages) then

Set iocurrentpageix = ototalpages;

End if;

End if;

Set opagestartrow = ipagesize * (iocurrentpageix-1) + 1; -- start number of each page

If (iocurrentpageix = ototalpages) then

Set opageendrow = ototalrows; -- end number of each page

Else

Set opageendrow = ipagesize * iocurrentpageix;

End if;

If (iocurrentpageix> 1) then

Set ohaspreviouspage = 1; -- whether the previous page exists

Else

Set ohaspreviouspage = 0;

End if;

If (iocurrentpageix <ototalpages) then

Set ohasnextpage = 1; -- whether the next page exists

 
 

  
  
else  
  
  
set ohasnextpage = 0;  
  
  
end if;  
  
  
set strsql = 'select * from (select rownumber() over(' || iorder || ') as rownum,'  
  
  
|| ishowfield   
  
  
|| ' from '  
  
  
|| itbname  
  
  
|| ijoin  
  
  
|| iwhere  
  
  
|| ') as temp where rownum between ' || rtrim(char(opagestartrow)) || ' and ' || rtrim(char(opageendrow));  
  
  
prepare s2 from strsql;  
  
  
open result;  
  
  
-- BODY end --  
  
  
END  
 
 

The above content is an introduction to the paging program of the general stored procedure in DB2 database. I hope you will get some benefits.