SqlServer儲存過程應用二：分頁查詢資料並動態拼接where條件

學習中的苦與樂發表於2021-04-16
SQLServer儲存過程

前言

開發中查詢功能是貫穿全文的，我們來盤一盤使用儲存過程分頁查詢，並且支援動態拼接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

呼叫儲存過程

1、沒有動態拼接where條件

--呼叫分頁儲存過程，沒有where條件
DECLARE @total INT 
EXEC ProcedureStudent 1,5,@total OUT,''
SELECT @total 返回的總行數

2、有動態拼接where條件

--呼叫分頁儲存過程，有where條件：學號為100014的資料
DECLARE @total INT 
EXEC ProcedureStudent 1,5,@total OUT,' and Number=''100014'''  --動態拼接條件：學號為100014的資料
SELECT @total 返回的總行數

 

 

我們就介紹到這裡吧，拜拜。

 

歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
出處： https://www.cnblogs.com/xiongze520/p/14667408.html
創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置註明作者和原文連結。  

 

相關文章