1. 基本介紹與基本除錯方式

#SQL #Transact-SQL

Posted by Mephisto on 2025-02-07

0. Motivation

目前待的公司使用的資料庫系統為 Microsoft SQL Server(之後簡稱 SQL Server)，所以決定要來記錄一些有關 Transact-SQL(T-SQL)資料庫語言的知識。那這篇分為兩個部分，主要說明一些常見的專有名詞，以及基本的錯誤處理機制。

1. 簡單介紹與專有名詞

有關歷史的簡介，可以看一下這邊的維基百科，這邊簡要講一下 SQL 的歷史，SQL 在 1986 年成為美國國家標準學會(ANSI)的一項標準，然後在1987年成為國際標準化組織(ISO)標準，那 T-SQL 是 Microsoft SQL Server 支援的資料庫查詢語言，它的語法遵從 ANSI-SQL 92 標準所制定，間單的說，T-SQL 就是 ANSI-SQL 可程式化的擴充版本。

下面講一下比較常見的專有名詞，之後會細講如何使用這些功能:

  • 批次(Batches) : SQL Server 資料庫引擎執行的執行單位，當中，一個批次可以包含多個 T-SQL 指令敘述，下面為範例 :
USE TestDB;
GO  -- GO 指令並不是 T-SQL 指令，只是用來分隔出一至多個批次的符號

-- 創建測試表
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL
    DROP TABLE dbo.Employee;
GO

CREATE TABLE dbo.Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Age INT,
    Position NVARCHAR(50),
    Salary DECIMAL(10,2)
);
GO

-- 使用 GO 批次插入 3 組資料
INSERT INTO dbo.Employee (Name, Age, Position, Salary)
VALUES ('John Doe', 30, 'Developer', 60000);
GO

INSERT INTO dbo.Employee (Name, Age, Position, Salary)
VALUES ('Jane Smith', 28, 'Designer', 55000);
GO

INSERT INTO dbo.Employee (Name, Age, Position, Salary)
VALUES ('Mike Johnson', 35, 'Manager', 75000);
GO

-- 查詢結果
SELECT * FROM dbo.Employee;
GO
  • 預存程序(Stored Procedures) : 可以想成 預先執行若干個 T-SQL 敘述，以簡化對資料庫的操作，下面為範例 :
USE TestDB;
GO

-- 確保 Employee 表存在
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL
    DROP TABLE dbo.Employee;
GO

-- 建立 Employee 表
CREATE TABLE dbo.Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Age INT,
    Position NVARCHAR(50),
    Salary DECIMAL(10,2)
);
GO

-- 創建儲存程序 usp_AddEmployee
CREATE PROCEDURE usp_AddEmployee
    @Name NVARCHAR(100),
    @Age INT,
    @Position NVARCHAR(50),
    @Salary DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    -- 如果年齡超過 30，薪資增加 10%
    IF @Age > 30
        SET @Salary = @Salary * 1.10;

    -- 插入資料
    INSERT INTO dbo.Employee (Name, Age, Position, Salary)
    VALUES (@Name, @Age, @Position, @Salary);

    -- 返回插入的資料
    SELECT * FROM dbo.Employee WHERE EmployeeID = SCOPE_IDENTITY();
END;
GO

-- 測試執行儲存程序
EXEC usp_AddEmployee @Name = 'John Doe', @Age = 32, @Position = 'Senior Developer', @Salary = 70000;
GO

-- 查詢 Employee 表查看結果
SELECT * FROM dbo.Employee;
GO
  • 自訂函數(User Defined Functions) : 類似一般程式語言的函數，可以讓我們自行擴充 SQL Server 系統函數，下面為範例 :
USE TestDB;
GO

-- 確保 Employee 表存在
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL
    DROP TABLE dbo.Employee;
GO

-- 建立 Employee 表，加入 HireDate 欄位
CREATE TABLE dbo.Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL
);
GO

-- 插入一些測試資料
INSERT INTO dbo.Employee (Name, HireDate) VALUES 
('John Doe', '2015-06-15'),
('Jane Smith', '2018-09-23'),
('Mike Johnson', '2020-01-10');
GO

-- 創建 User Defined Function (UDF) 計算年資
CREATE FUNCTION dbo.fn_CalculateYearsOfService(@HireDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @YearsOfService INT;
    SET @YearsOfService = DATEDIFF(YEAR, @HireDate, GETDATE());
    RETURN @YearsOfService;
END;
GO

-- 使用函數查詢員工的年資
SELECT 
    EmployeeID, 
    Name, 
    HireDate, 
    dbo.fn_CalculateYearsOfService(HireDate) AS YearsOfService
FROM dbo.Employee;
GO
  • 觸發程序(Triggers) : 一種特殊用途的預存程序，當資料表操作符合指定條件時，就會自動執行觸發程序，下面為範例 :
    先創建兩張表
USE TestDB;
GO

-- 確保 Employee 表存在
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL
    DROP TABLE dbo.Employee;
GO

-- 創建 Employee 表
CREATE TABLE dbo.Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL
);
GO

-- 插入測試資料
INSERT INTO dbo.Employee (Name, Salary) VALUES 
('John Doe', 50000),
('Jane Smith', 55000),
('Mike Johnson', 60000);
GO

以及

-- 創建 SalaryHistory 表，用於記錄薪資變動
CREATE TABLE dbo.SalaryHistory (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT NOT NULL,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangeDate DATETIME DEFAULT GETDATE()
);
GO

創建觸發程序 :

-- 創建 Trigger：當 Employee 表的 Salary 更新時，自動記錄變更
CREATE TRIGGER trg_AfterSalaryUpdate
ON dbo.Employee
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 插入舊薪資與新薪資的變動記錄
    INSERT INTO dbo.SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate)
    SELECT 
        i.EmployeeID, 
        d.Salary AS OldSalary, 
        i.Salary AS NewSalary, 
        GETDATE() 
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary <> d.Salary;  -- 只記錄薪資有變動的情況
END;
GO

測試 :

-- 更新員工薪資
UPDATE dbo.Employee
SET Salary = 52000
WHERE EmployeeID = 1;
GO

UPDATE dbo.Employee
SET Salary = 58000
WHERE EmployeeID = 2;
GO

-- 檢查 SalaryHistory 表，確認薪資變更記錄
SELECT * FROM dbo.SalaryHistory;
GO

2. 錯誤處理

這邊我使用 《SQL Server 2022/2019 資料庫設計與開發實務》附的範例

2-1. try/catch

基本語法 :

begin try
    T-SQL 指令敘述
end try
begin catch
    T-SQL 指令敘述
end catch

範例 :

begin try
    select 1/0
end try
begin catch
    --顯示錯誤訊息
    select error_number() as ErrorNumber,       --傳回錯誤號碼
           error_severity() as ErrorSeverity,   --傳回錯誤嚴重性代碼
           error_state() as ErrorState,         --傳回錯誤的狀態碼
           error_procedure() as ErrorProcedure, --傳回發生錯誤的預存或觸發程序名稱
           error_line() as ErrorLine,           --傳回造成錯誤的行列號
           error_message() as ErrorMessage      --傳回完整的錯誤訊息
end catch

結果 :

2-2. sp_addmessage 和 sp_dropmessage 以及 paiserror()

  1. sp_addmessage : 新增自訂錯誤訊息到sys.messages表。
  2. paiserror : 觸發自訂錯誤並回傳錯誤資訊。
  3. sp_dropmessage : 刪除自訂錯誤訊息。

範例 :

-- 新增自訂錯誤訊息 (訊息編號: 50001)
exec sp_addmessage 
    @msgnum = 50001,                                                   -- 自訂錯誤編號
    @severity = 16,                                                    -- 嚴重性級別 (1-25, 16 表示一般錯誤)
    @msgtext = N'Invalid employee ID: %d. Please provide a valid ID.', -- 錯誤訊息內容，%d 代表動態變數（類似 printf 格式化）
    @lang = 'us_english',                                              -- 指定語言，可以新增其他語言比如說繁體中文 '繁體中文'，但是必須新增英文的版本
GO


-- 嘗試觸發錯誤
declare @EmpID INT = -1  -- 假設這是不正確的員工 ID

if @EmpID <= 0
begin
  --語法
  --raiserror ( { 錯誤編號 | 錯誤訊息 } 嚴重性級別, 錯誤狀態, @EmpID 會動態填入 %d 的位置。)
    raiserror (50001, 16, 1, @EmpID)  -- 50001 是我們剛剛定義的錯誤編號
end
GO


-- 刪除錯誤編號 50001
exec sp_dropmessage @msgnum = 50001, @lang = 'us_english'
GO

結果 :

2-3. throw()

範例 :

begin try
    select 1/0
end try
begin catch
    throw 51000, '除以零的錯誤...', 1
end catch

結果 :

$\star$ : paiserror 與 throw 比較表

特性 RAISEERROR THROW
適用版本 SQL Server 2000 及以上 SQL Server 2012 及以上
是否支援 sp_addmessage 註冊錯誤 ✅ 支援 ❌ 不支援
是否可以使用格式化輸出 (%d, %s) ✅ 可以 ❌ 不能
是否支援變數作為錯誤訊息 ✅ 可以 (RAISERROR(@message, 16, 1)) ❌ 不能 (THROW 不能直接接收變數)
是否支援錯誤號碼 (message_id) ✅ 可以 (RAISERROR(50001, 16, 1)) ❌ 只能手動指定錯誤訊息
是否需要指定 STATE (狀態碼) ✅ 需要 (RAISERROR (50000, 16, 1)) ❌ 不需要 (THROW 只需 THROW;)
是否支援 TRY...CATCH ✅ 可以 ✅ 可以
是否可在 CATCH 中 THROW 當前錯誤 ❌ 需要 RAISEERROR(ERROR_MESSAGE(), 16, 1) ✅ THROW; 可直接丟出當前錯誤
是否記錄到 SQL Server 錯誤日誌 (ERRORLOG) 🚫 預設不記錄，需加 WITH LOG 🚫 預設不記錄
適用場景 自訂錯誤訊息，可格式化，可與 sp_addmessage 結合 簡單拋出錯誤，適合 TRY...CATCH

2-4. 查詢自訂的錯誤訊息

select message_id, language_id, text, severity, is_event_logged
  from sys.messages
 where message_id >= 50000; -- 50000 以上為使用者自訂錯誤

特定語系 :

select message_id, language_id, text, severity, is_event_logged
  from sys.messages
 where message_id >= 50000 AND language_id = 1028; -- 1028 代表繁體中文

$\star$ 1033(英文, us_english）, 1028(繁體中文, Traditional Chinese）


#SQL #Transact-SQL

Mephisto
軟體工程師 @捷鵬國際股份有限公司




Related Posts

[4] 運算子 + 條件判斷

[4] 運算子 + 條件判斷

 bessyhuang
CSS 語法架構 (Cascading Style Sheets)

CSS 語法架構 (Cascading Style Sheets)

 pei_______
變成rule的形狀(4) - ESLint + Prettier + Stylelint 問題集

變成rule的形狀(4) - ESLint + Prettier + Stylelint 問題集

 TempuraEngineer

Comments