SqlServer存储过程应用二:分页查询数据并动态拼接where条件 (上)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: SqlServer存储过程应用二:分页查询数据并动态拼接where条件

前言

开发中查询功能是贯穿全文的,我们来盘一盘使用存储过程分页查询,并且支持动态拼接where条件。

划重点:支持动态拼接where条件

对存储过程的使用有疑问的同学去【SqlServer存储过程的创建与使用】补补课。

至于大家是使用自定义sql查询还是相关ORM框架查询就不讨论了,我们就简单介绍存储过程的查询(自定义sql查询)。

 


 

创建存储过程并执行

流程图如下,我们根据流程图进行代码实现。

 


1、创建带参的存储过程

 

创建带参数的存储过程首先要在存储过程中声明该参数,每个存储过程参数都必须用惟一的名称进行定义。

与标准的Transact-SQL变量相同,参数名必须以@为前缀,并且遵从对象标识符规则。

当用户不提供该参数的值时可以使用一个默认值来代替。

在执行带参数的存储过程时,既可以通过显式指定参数名称并赋予适当的值,也可以通过提供在CREATE PROCEDURE语句中给定的参数值(不指定参数名称)来向存储过程传递值。

在存储过程PRO_Student_IN中命名4个参数,其定义顺序为@Chinese、@English、@maths和@class。

例如,将值传递给存储过程指定的参数名称。

EXEC PRO_Student_IN @class="三年一班",@Chinese=85,@maths=85,@English=85

例如,按照参数的位置传递,而不命名参数名称。

EXEC PRO_Student_IN 85,85,85,"三年一班"

 

2、定义一个参数,用于接受拼接后的sql语句

通过指定过程参数,调用程序可以将值传递给过程的主体。

如果将参数标记为 OUTPUT 参数,则过程参数还可以将值返回给调用程序。

一个过程最多可以有 2100 个参数,每个参数都有名称、数据类型和方向。 还可以为参数指定默认值(可选)。

使用过程调用提供的参数值必须为常量或变量,不能将函数名称作为参数值。 变量可以是用户定义的变量或系统变量(如 @@spid)。

  1. 需要指定参数名称;
  2. 指定参数数据类型;
  3. 可以指定参数默认值;
  4. 可以指定参数方式(默认为输入参数)。

 

3、创建一个临时表,用于存储查询拼接条件后的结果集

临时表与永久表相似,但临时表存储在tempdb中,当不再使用时会自动删除。临时表有两种类型:本地和全局。它们在名称、可见性以及可用性上有区别。

对于临时表有如下几个特点:

  • 本地临时表就是用户在创建表的时候添加了“#”前缀的表,其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限,其它连接不能访问该表;
  • 不同的数据库连接中,创建的本地临时表虽然“名字”相同,但是这些表之间相互并不存在任何关系;
  • 在SQLSERVER中,通过特别的命名机制保证本地临时表在数据库连接上的独立性。

真正的临时表利用了数据库临时表空间,由数据库系统自动进行维护,因此节省了表空间。并且由于临时表空间一般利用虚拟内存,大大减少了硬盘的I/O次数,因此也提高了系统效率。

临时表在事务完毕或会话完毕数据自动清空,不必记得用完后删除数据。

本地临时表

  • 本地临时表的名称以单个数字符号 (#) 打头;
  • 它们仅对当前的用户连接(也就是创建本地临时表的connection)是可见的;
  • 当用户从 SQL Server 实例断开连接时被删除。

4、分页查询返回最终的sql语句和总行数,查询条件加上临时表的数据,最后删除临时表

Sqlserver数据库分页查询一直是Sqlserver的短板,分页方式也有好几种,假设有表ARTICLE,字段ID、YEAR...(其他省略),

数据53210条(客户真实数据,量不大),分页查询每页30条,查询第1500页(即第45001-45030条数据),字段ID聚集索引,YEAR无索引。

第一种方案、最简单、普通的方法:

SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

平均查询100次所需时间:45s

 

第二种方案:

SELECT * FROM (  SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

平均查询100次所需时间:138S

 

第三种方案:

SELECT * FROM ARTICLE w1, 
(
    SELECT TOP 30 ID FROM 
    (
        SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC


平均查询100次所需时间:21S

 

第四种方案:

SELECT * FROM ARTICLE w1 
    WHERE ID in 
        (
            SELECT top 30 ID FROM 
            (
                SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
            ) w ORDER BY w.YEAR ASC, w.ID ASC
        ) 
    ORDER BY w1.YEAR DESC, w1.ID DESC

平均查询100次所需时间:20S

 

第五种方案:

SELECT w2.n, w1.* FROM ARTICLE w1, (  SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC

平均查询100次所需时间:15S


完整存储过程代码

/**author:熊泽        date:2021-04-16    project:SqlServer存储过程应用二:分页查询数据并动态拼接where条件*/
--创建一个查询学生的存储过程
CREATE PROCEDURE ProcedureStudent
    @pageIndex INT ,        --当前页(如1:第1页)
    @pageCount INT,            --每页条数(如50:每页50条)
    @rowTotal INT OUTPUT ,    --返回的总行数 
    @strWhere VARCHAR(5000) --程序动态拼接的sql查询条件
AS
BEGIN
    /**begin创建拼接动态条件 */
    DECLARE @sq_temp AS VARCHAR(2000)  --定义拼接后的sql语句
    CREATE TABLE #temp (  --创建一个拼接查询条件查询出来的结果用于做子查询
        Number VARCHAR(50)
    )
    SET @sq_temp ='SELECT Number FROM a_Students where 1 = 1 '
                + CASE WHEN ISNULL(@strWhere,'') = '' THEN '' ELSE @strWhere END --动态拼接的条件
    --将拼接的条件写入临时表
    INSERT INTO #temp (Number) EXEC (@sq_temp);
    /**end创建拼接动态条件 */
     --分页查询语句sql
     SELECT * FROM (SELECT  
                        row_number()over (order by a.Number desc)Id,
                        a.Number 学号 ,
                        a.Name 姓名 ,
                        b.ClassName 班级 ,
                        c.Java ,
                        c.Python ,
                        c.C# ,
                        c.SqlDB
                FROM    a_Students a
                        LEFT JOIN a_StudentClass b ON a.ClassId = b.ClassId
                        LEFT JOIN a_StudentsScore c ON a.Number = c.Number
                        WHERE  a.Number IN (SELECT  Number FROM #temp)
                       )temp
       WHERE temp.Id between (@pageIndex-1)*@pageCount+1 and @pageIndex*@pageCount;
       --返回总条数
       SELECT  @rowTotal=COUNT(*)  FROM dbo.a_Students WHERE Number IN (SELECT  Number FROM #temp)
       DROP TABLE #temp  --删除临时表
END
GO
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
存储 SQL Go
sqlserver存储过程
sqlserver存储过程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
254 1
|
6天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在从SQLServer捕获数据变更(CDC)时,开启CDC功能后对应的表中没有记录变化数据,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
25天前
|
存储 机器学习/深度学习 SQL
SQLSERVER存储过程语法详解
SQLSERVER存储过程语法详解
30 0
|
2月前
|
存储 数据库
sqlserver------数据库的存储过程(练习)
sqlserver------数据库的存储过程(练习)
27 1
|
2月前
|
SQL 机器学习/深度学习 算法
SQL SERVER ANALYSIS SERVICES决策树、聚类、关联规则挖掘分析电商购物网站的用户行为数据
SQL SERVER ANALYSIS SERVICES决策树、聚类、关联规则挖掘分析电商购物网站的用户行为数据
|
2月前
|
存储 SQL
SQL Server 存储过程 触发器 事务处理
SQL Server 存储过程 触发器 事务处理
|
2月前
|
SQL 机器学习/深度学习 数据采集
数据分享|SQL Server、Visual Studio、tableau对信贷风险数据ETL分析、数据立方体构建可视化
数据分享|SQL Server、Visual Studio、tableau对信贷风险数据ETL分析、数据立方体构建可视化
|
2月前
|
SQL 机器学习/深度学习 数据挖掘
SQL Server Analysis Services数据挖掘聚类分析职业、地区、餐饮消费水平数据
SQL Server Analysis Services数据挖掘聚类分析职业、地区、餐饮消费水平数据
|
2月前
|
SQL 存储 数据库
高效的SQLSERVER分页查询
高效的SQLSERVER分页查询