Db2 paging stored procedures and calls

 

 

 

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

 

In ishowfield varchar (1000), -- display field

 

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

 

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 (1000) -- 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

 

BEGIN

 

Set OERROR = 'error! '| Strsql;

 

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

 

 

 

Call method:

 

Single table:

 

Connection table: