萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

利用預存程序實作依 IP 網段分頁列印

需求背景

某社區寬帶用戶管理系統需將用戶資料依 IP 網段 (如 172.20.128.x 為 128 網段) 分組,每個網段的用戶列印在獨立 A4 紙上。每頁最多 37 列;不足一頁者留白,超過一頁不足兩頁者補滿兩頁,以此類推。

資料模型簡述

  • userinfo:用戶基本資料 (含 ip_address、catalyst_id 等)。
  • catalyst:設備資料 (含 label)。
  • 臨時表 subip:彙整列印所需資料與空白列。

核心邏輯

  1. 建立游標依 IP 第 3 段 (SUBSTRING(ip_address,8,3)) 分組。
  2. 逐網段將符合條件的用戶資料 INSERT 進 subip。
  3. 統計筆數 @result:
    • @result > 37:補齊至 74 列 (兩頁)。
    • @result ≤ 37:補齊至 37 列 (一頁)。
  4. 游標迴圈結束後,對 subip 執行列印/報表輸出。

關鍵程式碼片段

DECLARE @result INT, @subip VARCHAR(20)
DECLARE cur_e SCROLL CURSOR FOR
    SELECT SUBSTRING(ip_address, 8, 3) FROM userinfo GROUP BY SUBSTRING(ip_address, 8, 3)

OPEN cur_e
FETCH FIRST FROM cur_e INTO @subip

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 寫入實際用戶資料
    INSERT INTO subip
    SELECT userinfo.username, userinfo.catalyst_port, userinfo.home_address,
           userinfo.ip_address, userinfo.phone, catalyst.label
    FROM userinfo, catalyst
    WHERE userinfo.catalyst_id = catalyst.id
      AND SUBSTRING(userinfo.ip_address, 8, 3) = @subip

    SET @result = @@ROWCOUNT

    -- 補齊空白列
    IF @result > 37
    BEGIN
        WHILE @result < 74
        BEGIN
            INSERT INTO subip SELECT '','','','','','',''  -- 空白列
            SET @result = @result + 1
        END
    END
    ELSE
    BEGIN
        WHILE @result < 37
        BEGIN
            INSERT INTO subip SELECT '','','','','','',''
            SET @result = @result + 1
        END
    END

    FETCH NEXT FROM cur_e INTO @subip
END

CLOSE cur_e
DEALLOCATE cur_e

優化與現代化建議

  • 移除游標:改用 CTE 或暫存表配合 ROW_NUMBER() / NTILE() 計算分頁,單一 INSERT 完成,效能大幅提升。
  • 參數化頁面大小:將 37 列設為參數,便於調整。
  • 列印端處理:現代應用多改為前端產生 PDF/HTML 列印,資料庫僅負責提供分頁資料集。
  • IP 解析:使用 PARSENAME 或 STRING_SPLIT (2016+) 取代 SUBSTRING,更健壯。

完整預存程序範本

CREATE PROC usp_PrintUsersBySubnet
    @RowsPerPage INT = 37
AS
BEGIN
    SET NOCOUNT ON
    -- 現代化寫法略...
END
copyright © 萬盛學電腦網 all rights reserved