DB2 General paging Stored Procedure

Numerous online usersPaging StorageA general stored procedure is selected.DB2Page turning stored procedure in:

 
 

  
  
Create procedure kyjt. USP_A_FY (in ptblname varchar (1000), -- table name
  
  
In pshowname varchar (1000), -- the field name must be displayed.
  
  
In pfldname varchar (1000), -- field name (primary key of the primary table, which cannot be repeated)
  
  
In ppagesize integer, -- if the page size is 0, the first 10 million pieces of data are returned by default.
  
  
In ppageindex integer, -- page number
  
  
In pordertype integer, -- set the sorting type. If the value is not 0, the data is sorted by the primary key IN descending order)
  
  
In pstrwhere varchar (1000) -- Query condition (Note: Do not add WHERE)
  
  
)
  
  
MODIFIES SQL DATA
  
  
DETERMINISTIC
  
  
LANGUAGE SQL
  
  
BEGIN
  
  
/**//*----------------------------------------------------------------
  
  
* Copyright (C) 2006 laugh crazy
  
  
* All Rights Reserved.
  
  
*
  
  
* Process function description:
  
  
* Versatile generic paging query statement (DB2)
  
  
*
  
  
* Create an identifier: longping520@126.com (modification)
  
  
*
  
  
//-----------------------------------------------------------------------*/
  
  
 
  
  
DECLARE C_STRSQL VARCHAR (6000); -- subject sentence
  
  
DECLARE C_STRTMP VARCHAR (100); -- Temporary Variable
  
  
DECLARE C_STRORDER VARCHAR (400); -- sort type
  
  
 
  
  
DECLARE bill_task cursor with return to caller for S1;
  
  
 
  
  
-- Set pshowname = PSHOWNAME | ',' | PFLDNAME;
  
  
 
  
  
IF (PPAGEINDEX = 0) THEN
  
  
Set ppageindex = 1;
  
  
End if;
  
  
 
  
  
IF (PPAGESIZE = 0) THEN
  
  
Set ppagesize = 10000000;
  
  
End if;
  
  
 
  
  
-- Set sorting rules
  
  
IF (PORDERTYPE <> 0) THEN
  
  
SET C_STRTMP = '<(SELECT min ';
  
  
SET C_STRORDER = 'ORDER BY' | PFLDNAME | 'desc ';
  
  
ELSE
  
  
SET C_STRTMP = '> (select max ';
  
  
SET C_STRORDER = 'ORDER BY' | PFLDNAME | 'asc ';
  
  
End if;
  
  
 
  
  
--
  
  
SET C_STRSQL = 'select' | PSHOWNAME | 'from' | PTBLNAME | 'where' | PFLDNAME | ''| C_STRTMP | '(' |' TBLTMP. TTT) FROM (SELECT '| PFLDNAME | 'as TTT from' | PTBLNAME | ''| C_STRORDER | 'fetch first' | CHAR (PPAGEINDEX-1) * PPAGESIZE) | 'rows ONLY) as tbltmp) '| C_STRORDER | 'fetch FIRST' | CHAR (PPAGESIZE) | 'rows only ';
  
  
--
  
  
IF (PSTRWHERE <> '') THEN
  
  
SET C_STRSQL = 'select' | PSHOWNAME | 'from' | PTBLNAME | 'where' | PFLDNAME | ''| C_STRTMP | '(' |' TBLTMP. TTT) FROM (SELECT '| PFLDNAME | 'as TTT from' | PTBLNAME | 'where' | PSTRWHERE | ''| C_STRORDER | 'fetch FIRST' | CHAR (PPAGEINDEX-1) * PPAGESIZE) | 'rows ONLY) as tbltmp) AND '| PSTRWHERE | ''| C_STRORDER | 'fetch FIRST' | CHAR (PPAGESIZE) | 'rows only ';
  
  
End if;
  
  
--
  
  
IF (PPAGEINDEX = 1) THEN
  
  
 
  
  
SET C_STRTMP = '';
  
  
 
  
  
IF (PSTRWHERE <> '') THEN
  
  
SET C_STRTMP = 'where' | PSTRWHERE;
  
  
End if;
  
  
 
  
  
SET C_STRSQL = 'select' | PSHOWNAME | 'from' | PTBLNAME | ''| C_STRTMP |'' | C_STRORDER | 'fetch FIRST '| CHAR (PPAGESIZE) | 'rows only ';
  
  
End if;
  
  
 
  
  
PREPARE S1 FROM C_STRSQL;
  
  
-- OPEN C_STRSQL;
  
  
-- EXECUTE S1;
  
  
OPEN bill_task;
  
  
 
  
  
END