三大数据库下的存储过程实现通用分页
三大数据库下的存储过程实现通用分页
SqlServer数据库下通用存储过程实现分页:
1 --通用存储过程分页 2 CREATE PROCEDURE procList 3 --我们需要传入的参数 4 @strWhere NVARCHAR(max), --查询条件 5 @PageSize INT, --每页显示多少条内容 6 @PageIndex INT , --当前页 7 @TableName NVARCHAR(max), --需要查询的表名 8 @ColName NVARCHAR(max), --排列用到的列名 9 --程序返回给用户的数据 10 @SumCount INT OUT, --共多少条记录 11 @PageCount INT OUT --共多少页 12 AS 13 BEGIN 14 --第一步:计算共多少条记录 15 DECLARE @sql_SumCount NVARCHAR(max) 16 SET @sql_SumCount='SELECT @SumCount=COUNT(*) FROM @TableName ' 17 --判断一下,查询条件是否为空 18 IF @strWhere <>'' 19 BEGIN 20 SET @Sql_SumCount=@sql_SumCount+' where '+@strWhere 21 END 22 --执行Sql语句 23 EXEC sys.sp_executesql @sql_SumCount,N'@SumCount int out',@SumCount OUTPUT 24 25 -------------------------------------------------------------- 26 27 --第二步:计算一共有多少页 28 --定义一个临时变量 29 DECLARE @temp FLOAT 30 --首先要判断一下是否存在余数 31 SET @temp=@SumCount%@PageSize 32 SET @PageCount=@SumCount/@PageSize 33 IF @temp <> 0 34 BEGIN 35 SET @PageCount=@PageCount+1 36 END 37 38 -------------------------------------------------------------- 39 40 --第三步:返回结果集 41 --定义一个变量,用于拼接分页的Sql语句 42 DECLARE @sql NVARCHAR(MAX) 43 SET @sql='WITH PageList AS (SELECT *,ROW_NUMBER() OVER(ORDER BY '+@ColName +' ) AS RowsId FROM '+@TableName 44 --判断查询条件是否为空 45 IF @strwhere <> '' 46 BEGIN 47 SET @sql=@sql+' where '+@strWhere 48 END 49 SET @sql=@sql+' ) ' 50 SET @sql=@sql+' SELECT * FROM PageList WHERE RowsId BETWEEN '+STR((@PageIndex-1)*@PageSize+1) + ' AND '+STR(@PageIndex*@PageSize) 51 END
MySql数据库下通用存储过程实现分页:
BEGIN SET @startrow = _pagesize * (_pageindex - 1) ; SET @pagesize = _pagesize ; SET @rowindex = 0 ; SET @strsql = CONCAT( ' select SQL_CALC_FOUND_ROWS ', _fields, ' from ', _tables, CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END, CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END, ' limit ', @startRow, ',', @pageSize ) ; PREPARE strsql FROM @strsql ; EXECUTE strsql ; SET _totalcount = FOUND_ROWS() ; IF (_totalcount <= _pagesize) THEN SET _pagecount = 1 ; ELSE IF (_totalcount % _pagesize > 0) THEN SET _pagecount = _totalcount / _pageSize + 1 ; ELSE SET _pagecount = _totalcount / _pageSize ; END IF ; END IF ; END
Oracle数据库下通用存储过程实现分页:
--创建包体,用于存储数据记录集 CREATE OR REPLACE PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR; END pkg_query; --创建存储过程 CREATE OR REPLACE PROCEDURE proc_query (p_tableName in varchar2, --表名 p_strWhere in varchar2, --查询条件 p_orderColumn in varchar2, --排序的列 p_orderStyle in varchar2, --排序方式 p_curPage in out Number, --当前页 p_pageSize in out Number, --每页显示记录条数 p_totalRecords out Number, --总记录数 p_totalPages out Number, --总页数 v_cur out pkg_query.cur_query) --返回的结果集 IS v_sql VARCHAR2(1000) := ''; --sql语句 v_startRecord Number(4); --开始显示的记录条数 v_endRecord Number(4); --结束显示的记录条数 BEGIN --记录中总记录条数 v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1'; IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN v_sql := v_sql || p_strWhere; END IF; EXECUTE IMMEDIATE v_sql INTO p_totalRecords; --验证页面记录大小 IF p_pageSize < 0 THEN p_pageSize := 0; END IF; --根据页大小计算总页数 IF MOD(p_totalRecords,p_pageSize) = 0 THEN p_totalPages := p_totalRecords / p_pageSize; ELSE p_totalPages := p_totalRecords / p_pageSize + 1; END IF; --验证页号 IF p_curPage < 1 THEN p_curPage := 1; END IF; IF p_curPage > p_totalPages THEN p_curPage := p_totalPages; END IF; --实现分页查询 v_startRecord := (p_curPage - 1) * p_pageSize + 1; v_endRecord := p_curPage * p_pageSize; v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' || '(SELECT * FROM ' || p_tableName; IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN v_sql := v_sql || ' WHERE 1=1' || p_strWhere; END IF; IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle; END IF; v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= ' || v_startRecord; DBMS_OUTPUT.put_line(v_sql); OPEN v_cur FOR v_sql; END proc_query;