5,889
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE XIAOWU.GETDATAS
(
IN p_tableName VARCHAR(1000),
IN p_strWhere VARCHAR(1000),
IN p_orderColumn VARCHAR(1000),
IN p_orderStyle VARCHAR(1000),
IN p_curPage INTEGER,
IN p_pageSize INTEGER,
IN p_primaryColumn VARCHAR(1000),
IN p_selectColumns VARCHAR(1000),
OUT p_totalRecords VARCHAR(1000),
OUT p_totalPages INTEGER
)
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE v_sql VARCHAR(3000) DEFAULT '';
DECLARE v_startRecord INTEGER DEFAULT 0;
DECLARE v_endRecord INTEGER DEFAULT 0;
DECLARE cursql STATEMENT;
DECLARE r_cur CURSOR WITH RETURN FOR cursql;
IF(p_selectColumns = '') THEN
SET p_selectColumns = '*';
END IF;
SET v_sql = 'SET (?) = (SELECT COUNT(' || p_primaryColumn || ') FROM ' || p_tableName || '';
IF (p_strWhere <> '') THEN
set v_sql = v_sql || ' WHERE ' || p_strWhere;
END IF;
set v_sql = v_sql || ')';
prepare cursql from v_sql;
execute cursql into p_totalRecords;
IF (MOD(p_totalRecords,p_pageSize) = 0) THEN
set p_totalPages = p_totalRecords / p_pageSize;
ELSE
set p_totalPages = p_totalRecords / p_pageSize + 1;
END IF;
SET v_startRecord = (p_curPage - 1) * p_pageSize + 1;
SET v_endRecord = p_curPage * p_pageSize;
SET v_sql = 'SELECT * FROM (SELECT A.*,rownumber() over(order by '|| p_orderColumn || ' ' || p_orderStyle || ') rowid FROM (SELECT ' || p_selectColumns || ' FROM ' || p_tableName;
IF(p_strWhere <> '') THEN
SET v_sql = v_sql || ' WHERE ' || p_strWhere;
END IF;
SET v_sql = v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle || ') A ) B WHERE ROWID >= ' || v_startRecord || ' AND ROWID <= '|| v_endRecord || '';
prepare cursql from v_sql;
open r_cur;
END;
public static void main(String[] args) throws Exception {
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
Connection connection = DriverManager.getConnection(
"jdbc:db2://127.0.0.1:50000/SAMPLE", "xiaowu", "xxiaowu#11#15");
PreparedStatement ps = connection.prepareStatement("CREATE PROCEDURE XXIAOWUXX(IN p_tableName VARCHAR(1000),IN p_strWhere VARCHAR(1000),IN p_orderColumn VARCHAR(1000),IN p_orderStyle VARCHAR(1000),IN p_curPage INTEGER,IN p_pageSize INTEGER,IN p_primaryColumn VARCHAR(1000),IN p_selectColumns VARCHAR(1000),OUT p_totalRecords VARCHAR(1000),OUT p_totalPages INTEGER) LANGUAGE SQL READS SQL DATA DYNAMIC RESULT SETS 1 BEGIN DECLARE v_sql VARCHAR(3000) DEFAULT '';DECLARE v_startRecord INTEGER DEFAULT 0;DECLARE v_endRecord INTEGER DEFAULT 0;DECLARE cursql STATEMENT;DECLARE r_cur CURSOR WITH RETURN FOR cursql;IF(p_selectColumns = '') THEN SET p_selectColumns = '*';END IF;SET v_sql = 'SET (?) = (SELECT COUNT(' || p_primaryColumn || ') FROM ' || p_tableName || '';IF (p_strWhere <> '') THEN set v_sql = v_sql || ' WHERE ' || p_strWhere;END IF;SET v_sql = v_sql || ')';PREPARE cursql FROM v_sql;EXECUTE cursql INTO p_totalRecords;IF (MOD(p_totalRecords,p_pageSize) = 0) THEN SET p_totalPages = p_totalRecords / p_pageSize;ELSE SET p_totalPages = p_totalRecords / p_pageSize + 1;END IF;SET v_startRecord = (p_curPage - 1) * p_pageSize + 1;SET v_endRecord = p_curPage * p_pageSize;SET v_sql = 'SELECT * FROM (SELECT A.*,rownumber() over(order by '|| p_orderColumn || ' ' || p_orderStyle || ') rowid FROM (SELECT ' || p_selectColumns || ' FROM ' || p_tableName;IF(p_strWhere <> '') THEN SET v_sql = v_sql || ' WHERE ' || p_strWhere;END IF;SET v_sql = v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle || ') A ) B WHERE ROWID >= ' || v_startRecord || ' AND ROWID <= '|| v_endRecord || '';PREPARE cursql FROM v_sql;OPEN r_cur;END;");
ps.executeUpdate();
DBUtil.closeDBResource(connection, ps, null, null);
}