复制CREATEPROCEDURE KYJT.USP_A_FY(IN PTBLNAME VARCHAR(1000),通用 --表名 IN PSHOWNAME VARCHAR(1000), -- 需要显示字段名 IN PFLDNAME VARCHAR(1000), --字段名(主表主键，不可重复) IN PPAGESIZE INTEGER,翻页 -- 页尺寸 如果为0 默认返回前一千万条数据 可以认为是站群服务器返回所有数据 IN PPAGEINDEX INTEGER,--页码 IN PORDERTYPE INTEGER, --设置排序类型, 非 0 值则降序（按主键排序） IN PSTRWHERE VARCHAR(1000) --查询条件 (注意: 不要加 WHERE) ) MODIFIES SQL DATA DETERMINISTIC LANGUAGE SQL BEGIN /**//*---------------------------------------------------------------- * Copyright (C) 2006 笑疯 * 版权所有。免费源码下载 * * 过程功能描述： * 多功能通用翻页查询语句(DB2) * * 创建标识：longping520@126.com(2006-5-1修改) * //-----------------------------------------------------------------------*/ DECLARE C_STRSQL VARCHAR(6000); -- 主语句 DECLARE C_STRTMP VARCHAR(100); -- 临时变量 DECLARE C_STRORDER VARCHAR(400); -- 排序类型 DECLARE bill_task CURSORWITHRETURNTO 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; -- 设置排序规则 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 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.云服务器提供商 |
喜欢
讨厌