db2 通用分页存储过程
CREATE PROCEDURE DB2ADMIN.PROC_GETPAGE(INOUT CURRENTPAGE INTEGER,
IN STRSQL VARCHAR(500),
IN PAGESIZE INTEGER,
OUT TOTALPAGE INTEGER,
OUT TOTALRECORD INTEGER
)
SPECIFIC DB2ADMIN.SQL080201103307110
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE tmpsql VARCHAR(1000); -- 主语句
DECLARE orderFiled VARCHAR(50);
DECLARE sVARCHAR(1000);
DECLARE v_StartINTEGER;
DECLARE v_End INTEGER;
DECLARE result CURSOR WITH RETURN TO CALLER FOR S2;
SET orderFiled =getOrderField(strsql);
if(length(orderFiled )>0) then
set orderFiled =' order by ' || orderFiled ;
end if;
SET s=getSQL(strsql);
set tmpsql = 'select count(*) from (' || strsql || ') as a';
prepare s2 from tmpsql;
open result;
fetch result into totalrecord;-- 总记录数
close result;
if(pagesize = 0) then
set pagesize = 20;-- 每页显示数
end if;
set totalPage = (totalrecord - 1) / pagesize + 1;-- 总页数
if(currentPage < 1) then
set currentPage = 1;-- 当前页
else
if(currentPage > totalPage) then
set currentPage = totalPage;
end if;
end if;
set v_Start = (currentPage-1) * pagesize ;
set v_End = currentPage * pagesize;
settmpsql ='select * from ' ||
'(select rownumber() over() as row, ' ||
'w.* from ( select * from (' || s || ') n' || orderFiled|| ') w) w1 where row between '|| char(v_Start ) ||' and'|| char(v_End );
prepare s2 from tmpsql ;
open result;
END;
使用了两个java函数
import java.util.regex.*;
import COM.ibm.db2.app.UDF;
public class FunctionDB2 extends UDF
{
public static String getOrderField(String sql)
{
Pattern Regex = Pattern.compile("select(.+)order\\s*by\\s*(.+)",
Pattern.CANON_EQ | Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE);
Matcher RegexMatcher = Regex.matcher(sql);
if (RegexMatcher.find()) {
return RegexMatcher.group(2);
}
return "";
}
public static String getSQL(String sql)
{
Pattern Regex = Pattern.compile("(.+)order\\s*by\\s*(.+)",
Pattern.CANON_EQ | Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE);
Matcher RegexMatcher = Regex.matcher(sql);
if (RegexMatcher.find()) {
return RegexMatcher.group(1);
}
return sql;
}
}
页:
[1]