Orcale与sqlserver分页存储过程

最新推荐文章于 2023-09-11 15:39:46 发布
最新推荐文章于 2023-09-11 15:39:46 发布
阅读量217 收藏 1
点赞数
分类专栏： 数据库 文章标签： 分页 存储


1.Oracle分页存储过程


create or replace procedure Pager(
page in number,--数据页数，从1开始
pageSize in number,--每页大小
tableName nvarchar2,--表名
strWhere nvarchar2,--where条件
Orderby nvarchar2,
numCount out number,--总记录数
v_cur out pkg_query.cur_query) is

strSql varchar2(2000);--获取数据的sql语句
pageCount number;--该条件下记录页数
startIndex number;--开始记录
endIndex number;--结束记录


--分页获取数据

begin
  strSql:='select count(1) from '||tableName;
  if strWhere is not null or strWhere<>'' then
     strSql:=strSql||' where '||strWhere;
  end if; 
  EXECUTE IMMEDIATE strSql INTO numCount;
  --计算数据记录开始和结束
  pageCount:=numCount/pageSize+1;
  startIndex:=(page-1)*pageSize+1;
  endIndex:=page*pageSize;
 
  strSql:='select rownum ro, t.* from '||tableName||' t'; 
  strSql:=strSql||' where rownum<='||endIndex;
 
  if strWhere is not null or strWhere<>'' then
     strSql:=strSql||' and '||strWhere;
  end if;
 
  if  Orderby is not null or Orderby<>'' then
     strSql:=strSql||' order by '||Orderby;
  end if;
 
  strSql:='select * from ('||strSql||') where ro >='||startIndex; 
  DBMS_OUTPUT.put_line(strSql);

  OPEN v_cur FOR strSql;
end Pager;


2.sqlserver存储过程


CREATE PROC P_viewPage

    /**//**//**//*
        nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7  QQ:34813284
        敬告：适用于单一主键或存在唯一值列的表或视图
        ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
           
    */

    @TableName VARCHAR(200),     --表名
    @FieldList VARCHAR(2000),    --显示列名，如果是全部字段则为*
    @PrimaryKey VARCHAR(100),    --单一主键或唯一值键
    @Where VARCHAR(2000),        --查询条件 不含'where'字符，如id>10 and len(userid)>9
    @Order VARCHAR(1000),        --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc                                 
                                 --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
    @SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
    @RecorderCount INT,          --记录总数 0:会返回总记录
    @PageSize INT,               --每页输出的记录数
    @PageIndex INT,              --当前页数
    @TotalCount INT OUTPUT,      --记返回总记录
    @TotalPageCount INT OUTPUT   --返回总页数
AS
    SET NOCOUNT ON

    IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
    SET @Order = RTRIM(LTRIM(@Order))
    SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
    SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')

    WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
    BEGIN
        SET @Order = REPLACE(@Order,', ',',')
        SET @Order = REPLACE(@Order,' ,',',')    
    END

    IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' 
        OR ISNULL(@PrimaryKey,'') = ''
        OR @SortType < 1 OR @SortType >3
        OR @RecorderCount  < 0 OR @PageSize < 0 OR @PageIndex < 0        
    BEGIN 
        PRINT('ERR_00')       
        RETURN
    END    

    IF @SortType = 3
    BEGIN
        IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
        BEGIN PRINT('ERR_02') RETURN END
    END

    DECLARE @new_where1 VARCHAR(1000)
    DECLARE @new_where2 VARCHAR(1000)
    DECLARE @new_order1 VARCHAR(1000)   
    DECLARE @new_order2 VARCHAR(1000)
    DECLARE @new_order3 VARCHAR(1000)
    DECLARE @Sql VARCHAR(8000)
    DECLARE @SqlCount NVARCHAR(4000)

    IF ISNULL(@where,'') = ''
        BEGIN
            SET @new_where1 = ' '
            SET @new_where2 = ' WHERE  '
        END
    ELSE
        BEGIN
            SET @new_where1 = ' WHERE ' + @where 
            SET @new_where2 = ' WHERE ' + @where + ' AND '
        END

    IF ISNULL(@order,'') = '' OR @SortType = 1  OR @SortType = 2 
        BEGIN
            IF @SortType = 1 
            BEGIN 
                SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
                SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
            END
            IF @SortType = 2 
            BEGIN 
                SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
                SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
            END
        END
    ELSE
        BEGIN
            SET @new_order1 = ' ORDER BY ' + @Order
        END

    IF @SortType = 3 AND  CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
        BEGIN
            SET @new_order1 = ' ORDER BY ' + @Order
            SET @new_order2 = @Order + ','            
            SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')            
            SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
            SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)            
            IF @FieldList <> '*'
                BEGIN            
                    SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')                              
                    SET @FieldList = ',' + @FieldList                    
                    WHILE CHARINDEX(',',@new_order3)>0
                    BEGIN
                        IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
                        BEGIN 
                        SET @FieldList = 
                            @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))                        
                        END
                        SET @new_order3 = 
                        SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
                    END
                    SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))                     
                END            
        END

    SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
                    + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
    
    IF @RecorderCount  = 0
        BEGIN
             EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
                               @TotalCount OUTPUT,@TotalPageCount OUTPUT
        END
    ELSE
        BEGIN
             SELECT @TotalCount = @RecorderCount            
        END

    IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
        BEGIN
            SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)
        END

    IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
        BEGIN
            IF @PageIndex = 1 --返回第一页数据
                BEGIN
                    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
                               + @TableName + @new_where1 + @new_order1
                END
            IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据
                BEGIN
                    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
                               + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) 
                               + ' ' + @FieldList + ' FROM '
                               + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
                               + @new_order1                    
                END        
        END    
    ELSE
        BEGIN
            IF @SortType = 1  --仅主键正序排序
                BEGIN
                    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
                                       + @TableName + @new_where2 + @PrimaryKey + ' > '
                                       + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
                                       + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 
                                       + ' FROM ' + @TableName
                                       + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
                        END
                    ELSE  --反向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
                                       + 'SELECT TOP ' + STR(@PageSize) + ' ' 
                                       + @FieldList + ' FROM '
                                       + @TableName + @new_where2 + @PrimaryKey + ' < '
                                       + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
                                       + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 
                                       + ' FROM ' + @TableName
                                       + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 
                                       + ' ) AS TMP ' + @new_order1
                        END
                END
            IF @SortType = 2  --仅主键反序排序
                BEGIN
                    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
                                       + @TableName + @new_where2 + @PrimaryKey + ' < '
                                       + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
                                       + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 
                                       +' FROM '+ @TableName
                                       + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1                               
                        END 
                    ELSE  --反向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
                                       + 'SELECT TOP ' + STR(@PageSize) + ' ' 
                                       + @FieldList + ' FROM '
                                       + @TableName + @new_where2 + @PrimaryKey + ' > '
                                       + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
                                       + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 
                                       + ' FROM ' + @TableName
                                       + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 
                                       + ' ) AS TMP ' + @new_order1
                        END  
                END                         
            IF @SortType = 3  --多列排序，必须包含主键，且放置最后，否则不处理
                BEGIN
                    IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 
                    BEGIN PRINT('ERR_02') RETURN END
                    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                                       + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                                       + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
                                       + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
                                       + @new_order2 + ' ) AS TMP ' + @new_order1    
                        END
                    ELSE  --反向检索
                        BEGIN
                            SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '  
                                       + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                                       + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList
                                       + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
                                       + @new_order1 + ' ) AS TMP ' + @new_order1
                        END
                END
        END
    PRINT(@Sql)
    EXEC(@Sql)
GO

tocytochyy
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle sql server的区别,oracle与sqlserver的十大区别
weixin_30467861的博客
04-07 975
http://blog.csdn.net/it_fengli/article/details/8213839 --sql server 与 oracle的区别： --DBMS 数据库管理系统 --1.数据类型不同。 --sql server 的数据类型：int ,smallint ,char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,m...
SQL Server 在分页获取数据的同时获取到总记录数
01-19
SQL Server 获取数据的总记录数，有两种方式： 1.先分页获取数据，然后再查询一遍数据库获取到总数量 2.使用count(1) over()获取总记录数量 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) rn, ...
Oracle、 Mysql 、 SQLserver 分页查询
2301_78145658的博客
06-06 574
首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素 第二种方法： 还是以上面的结果为例，采用另外的一种方法 --数据的意思和上面提及的一样 select top 10 * from test where id > ( select isnull(max(id),0) from ( select top 40 id from test order by id ) A ) order by id。上面给出的这个分页查询语句，在大多数情况拥有较高的效率。
mysql oracle sqlserver分页,SQLServer,MySQL,Oracle 数据库分页 （转）
weixin_33610780的博客
04-13 100
原文：SQL Server关于分页 SQL的资料许多，有的使用存储过程，有的使用游标。本人不喜欢使用游标，我觉得它耗资、效率低；使用存储过程是个不错的选择，因为存储过程是颠末预编译的，执行效率高，也更灵活。先看看单条SQL 语句的分页 SQL 吧。方法1：适用于 SQL Server 2000/2005SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( S...
Oracle & PL/SQL 存储过程
Jancy2265的博客
09-08 922
Oracle PL/SQL 存储过程 Oracle 简介： 是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。 oracle安装 Oracle一般安装于公司服务器中，更多应该关注于客户端连接数据方法。 oracle连接 下载oracle客户端连接程序 https://www.ora
Oracle和MYSQL和SQLServer的分页
h_j_c_123的博客
07-13 360
Oracle和MYSQL和SQLServer的分页加存储过程： Oracle的分页是利用rownum关键字来进行分页的，实际上是将rownum查出来的数据作为一个新的数据源来进行再次查询也就是子查询，详情看代码： -- oracle 分页 select * from (select rownum rn , tt.* from T_LOGIN1 tt where rownum<=...
Oracle与Sql server的区别
weixin_44940145的博客
09-24 666
干货: –sql server 与 oracle的区别： –DBMS 数据库管理系统 –1.数据类型不同。 –sql server 的数据类型：int ,smallint ,char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima, –float,bit…… --oracle 的数据类型：number(p,s)...
sqlserver oracle游标区别,Oracle 和 SqlServer 的区别
weixin_35696012的博客
04-14 408
--sql server 与 oracle的区别：--DBMS 数据库管理系统--1.数据类型不同。--sql server 的数据类型：int ,smallint ,char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima,--float,bit……--oracle 的数据类型：number(p,s),char,...
MySQL、PostgreSQL、Oracle、SQL Server四大数据库对比
m0_52620144的博客
09-11 4614
在项目实战中接触到不同数据库，将其sql语法等需要注意的不同点记录下来
Oracle使用存储过程分页
思念十年后
04-26 4861
今天我要说的就是基于Oracle存储过程的一种简单的分页方式，首先就是页面前端发起对分页的请求，然后向Oracle存储过程中传入页面当前数，页面大小，查询的SQL语句，而存储过程返回来的则是数据总条数和当前页面要显示的数据集合。形式如下所示：procedure P_GetPageSize(p_pagesize int, p_startsize int,
MySQL、Oracle 与 Sql Server的区别
鲨鱼亲吻小鹿的博客
08-03 2073
我们来探讨下MySQL、Oracle 与 Sql Server的区别
SQL Server 存储过程及Oracle SQL语句分页
09-13
首先，我们来看看SQL Server中的分页存储过程。这里展示的是一个名为`usp_GetPageData`的存储过程，用于获取分页数据。这个存储过程接收三个参数：`@pageSize`(每页记录数)，`@pageIndex`(当前页数)，以及`@...
sqlserver利用存储过程去除重复行的sql语句
12-15
总之，这个存储过程展示了如何在SQL Server中有效地管理和删除重复数据，同时实现了分页查询，这些都是数据库操作中的常见需求。需要注意的是，实际应用中应考虑性能优化和安全措施，例如使用参数化查询以防止SQL...
实现ORACLE与SQLSERVER数据库间的数据抽取与转换工具
07-29
总结来说，实现“Oracle与SQLServer数据库间的数据抽取与转换工具”涉及的主要知识点包括：数据库连接、SQL查询、数据转换规则、多数据库系统间的语法差异理解和编程实现。这个工具的源代码压缩包"复件 zExtractor...
基于微信小程序的在线学习系统设计与实现.docx
08-29
基于微信小程序的在线学习系统设计与实现.docx
mayaABC文件建模李小龙动作B
08-29
mayaABC文件建模李小龙动作A，一整套的建模abc数据有动作
微专题 新情景条件下氧化还原反应方程式的书写 (1).pdf
最新发布
08-29
微专题 新情景条件下氧化还原反应方程式的书写 (1).pdf
新增数字经济专利等20多份数字经济学术资源大合集(全新整理)
08-29
1、资源内容地址：https://blog.csdn.net/2301_79696294/article/details/141267034 2、代码特点：今年全新，手工精心整理，放心引用，数据来自权威，相对于其他人的控制变量数据准确很多,适合写论文做实证用 ，不会出现数据造假问题 3、适用对象：大学生，本科生，研究生小白可用，容易上手！！！ 3、课程引用: 经济学，地理学，城市规划与城市研究，公共政策与管理，社会学，商业与管理 数据指标说明 一、新经济指数（2017-2022） 二、数字经济发展白皮书2017-2021年 三、北京大学商业银行数字化转型指数（2010-2021年） 四-1 全国31省数字经济测算2013-2020 四-2 全国31省数字经济发展测算指标 五、 中国城市数字经济指数（2017-2021） 六、中国城市数字经济成分（2011-2019） 七、《2018-2020县域数字乡村指数》 八、企业级数字经济核心产业专利
lesson26-LR多分类实战.zip
08-29
深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT深度学习与PyTorch入门实战视频教程 配套源代码和PPT
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值