Db2 paging stored procedures and calls

Source: Internet
Author: User

 

 

 

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.