SQL Server 資料庫建立、預存程序與使用者自訂函數完整實戰指南

在 SQL Server 環境中，掌握資料庫建立、資料表設計、預存程序撰寫以及使用者自訂函數的開發，是後端開發者的核心技能。本文將透過完整的程式碼範例，從零開始示範如何建立包含多個檔案群組的資料庫、設計帶有約束條件的資料表、撰寫具備輸入輸出參數的預存程序，以及建立標量函數與資料表值函數。

一、建立多檔案群組資料庫

合理規劃資料檔案與記錄檔的初始大小、最大值與成長增量，能有效提升 I/O 效能並避免頻繁自動擴充造成的效能抖動。

CREATE DATABASE MyDb
ON
(
    NAME = mainDb,
    FILENAME = 'C:\MyDb\mainDb.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 4MB
),
(
    NAME = secondDb,
    FILENAME = 'C:\MyDb\secondDb.ndf',
    SIZE = 15MB,
    MAXSIZE = 28MB,
    FILEGROWTH = 2MB
)
LOG ON
(
    NAME = log_Db,
    FILENAME = 'C:\MyDb\log_Db.ldf',
    SIZE = 20MB,
    FILEGROWTH = 10%
);

二、建立帶約束條件的學生資料表

透過 PRIMARY KEY、NOT NULL、CHECK 等約束，確保資料完整性與業務邏輯正確性。

USE MyDb;
GO

CREATE TABLE Student
(
    StuId INT PRIMARY KEY IDENTITY(1,1),
    StuName VARCHAR(20) NOT NULL,
    StuAge INT NOT NULL CHECK (StuAge BETWEEN 20 AND 50),
    StuSex VARCHAR(4) NOT NULL CHECK (StuSex IN ('F','M')),
    StuDept VARCHAR(20) CHECK (StuDept IN ('軟工系','環藝系','電子商務系')),
    StuAddress VARCHAR(20) NOT NULL
);

三、測試資料插入與基本查詢

INSERT INTO Student VALUES ('孫業寶', 22, 'M', '軟工系', '河北省邢台市');
INSERT INTO Student VALUES ('孫婷', 20, 'F', '電子商務系', '河北省邢台市');
INSERT INTO Student VALUES ('孟幾', 22, 'F', '電子商務系', '河北省邢台市');
INSERT INTO Student VALUES ('小五', 22, 'M', '軟工系', '河北省石家莊市');
INSERT INTO Student VALUES ('王丹丹', 22, 'M', '軟工系', '河北省阜陽市');
INSERT INTO Student VALUES ('陳海波', 22, 'M', '軟工系', '河北省合肥市');

SELECT * FROM Student;

四、單一輸入輸出參數預存程序

統計指定系別的學生人數，若無資料則列印提示訊息。

CREATE PROC MyProc
    @Dept VARCHAR(20),
    @Count INT OUTPUT
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM Student WHERE StuDept = @Dept)
        PRINT '沒有指定類型的學生存在！！';
    ELSE
        SELECT @Count = COUNT(*) FROM Student WHERE StuDept = @Dept;
END;
GO

-- 執行測試
DECLARE @Result INT;
EXEC MyProc '軟工系', @Result OUTPUT;
PRINT @Result;

五、多輸入多輸出參數預存程序

依地區與性別統計學生總數與平均年齡。

CREATE PROC SearchStu
    @Area VARCHAR(20),
    @Sex VARCHAR(2),
    @Count INT OUTPUT,
    @AvgAge INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*), @AvgAge = AVG(StuAge)
    FROM Student
    WHERE StuAddress = @Area AND StuSex = @Sex;
END;
GO

-- 執行測試
DECLARE @StuNo INT, @StuAvgAge INT;
EXEC SearchStu '河北省邢台市', 'M', @StuNo OUTPUT, @StuAvgAge OUTPUT;
SELECT @StuNo AS 學生總數, @StuAvgAge AS 平均年齡;

六、使用者自訂標量函數：計算立方體體積

CREATE FUNCTION dbo.CubicVolume
(
    @CubeLength INT,
    @CubeHeight INT,
    @CubeWidth INT
)
RETURNS INT
AS
BEGIN
    RETURN (@CubeLength * @CubeHeight * @CubeWidth);
END;
GO

-- 呼叫函數
SELECT dbo.CubicVolume(10, 10, 10) AS 體積;

七、內嵌資料表值函數：依系別查詢學生明細

CREATE FUNCTION f_StuInfo(@StuDept VARCHAR(20))
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Student WHERE StuDept = @StuDept
);
GO

-- 呼叫函數
SELECT * FROM dbo.f_StuInfo('軟工系');

八、多陳述式資料表值函數：轉換性別代碼為中文

CREATE FUNCTION f_StuSexType(@StuDept VARCHAR(10))
RETURNS @t_StuDetailInfo TABLE
(
    StuName VARCHAR(20),
    StuAge INT,
    StuSex VARCHAR(4)
)
AS
BEGIN
    INSERT INTO @t_StuDetailInfo
    SELECT StuName, StuAge,
        CASE StuSex
            WHEN 'M' THEN '男'
            WHEN 'F' THEN '女'
        END
    FROM Student
    WHERE StuDept = @StuDept;
    RETURN;
END;
GO

-- 呼叫函數
SELECT * FROM dbo.f_StuSexType('軟工系');

透過上述完整範例，涵蓋了資料庫架構規劃、資料完整性約束、預存程序的參數化設計、以及三種使用者自訂函數的實際應用場景，為實務開發提供了可直接參考的最佳實踐範本。