696 字
3 分鐘
【SQL】Stored Procedure 與 User-defined Function
SQL 裡的 Stored Procedure 和 User-defined Function，有點像、又有點不一樣？
這篇以 Microsoft SQL Server 討論，有些東西可能在不同資料庫會不太一樣。
Stored Procedure
- 宣告方式
CREATE PROCEDURE my_procedure @param1 AS INT, @param2 AS NVARCHAR(MAX), @param3 AS DATEASBEGIN -- 內容END- 使用方式
EXEC my_procedure @param1 = 123, @param2 = 'a string', @param3 = '2025-01-08';- 內容
- 可以有一至多條 DML 指令
- 可以有一至多條 SELECT 指令，如果有多條、會回傳多個 ResultSet
- 用途
- 把多個 SQL 指令包成一個 stored procedure
- 可以直接呼叫一次、做完裡面全部的事
- 效能較一條一條執行高
- 因為 SQL Server 已經預先編譯、做好執行計劃
- 可以另外設定權限
- 比如，不讓普通使用者直接存取這張資料表，但可以透過這個 stored procedure 修改這張表的部分內容
- 執行結果
- 就像直接執行這些指令一樣
- 根據內容，可能是「幾條資料列受影響」、也可能是 ResultSet，也可能都有
User-defined Function
根據回傳值與過程，還有分三種：
- Scalar functions：回傳一個值
- Table-valued functions（TVF）
- Inline table-valued function（ITVF）：回傳一張資料表，裡面只有一個 SELECT 指令
- Multi-statement table-valued function（MSTVF）：回傳一張資料表，這張資料表是經過多個指令組合出來的。
Scalar functions
- 宣告方式
CREATE FUNCTION my_scalar_function( @param1 AS INT, @param2 AS NVARCHAR(MAX), @param3 AS DATE)RETURNS NVARCHAR(MAX)ASBEGIN -- 內容 RETURN 單一個值END- 使用方式：作為一個值使用
SELECT my_scalar_function(123, 'a string', '2025-01-08')FROM table;- 內容
- 不能有 DML 指令
- 可以有 SELECT 指令和一些計算、流程控制等
- 要
RETURN一個值
- 用途
- 轉換資料
- 計算處理
- 執行結果
- 單一個值
Inline table-valued function（ITVF）
- 宣告方式
CREATE FUNCTION my_itvf( @param1 AS INT, @param2 AS NVARCHAR(MAX), @param3 AS DATE)RETURNS TABLEASRETURN -- 一條 SELECT 指令- 使用方式：在 FROM 子句裡作為一張資料表使用
SELECT *FROM my_itvf(123, 'a string', '2025-01-08');- 內容
- 只能有一條 SELECT 指令
- 用途
- 把複雜的 SELECT 指令包成一個函數，方便呼叫使用
- 執行結果
- 一張資料表
Multi-statement table-valued function（MSTVF）
- 宣告方式
CREATE FUNCTION my_mstvf( @param1 AS INT, @param2 AS NVARCHAR(MAX), @param3 AS DATE)RETURNS @result TABLE ( column1 INT, column2 NVARCHAR(MAX), column3 DATE)ASBEGIN -- 內容，要 INSERT 內容到 @result 裡面 RETURN;END- 使用方式：在 FROM 子句裡作為一張資料表使用
SELECT *FROM my_mstvf(123, 'a string', '2025-01-08');- 內容
- 可以有多條 DML 與 SELECT 指令
- 要 INSERT 內容到 @result 裡面
- 用途
- （可能有）做一些增刪改，最後回傳一個資料表
- 包成一個函數，方便呼叫使用
- 執行結果
- 一張資料表