Chuan's Mindspace
【筆記：SQL】Store Procedure 與 User-defined Function
Chuan
  2025/01/08 18:03   2025/01/08 18:26  

　　SQL 裡的 Store Procedure 和 User-defined Function，有點像、又有點不一樣？

　　這篇以 Microsoft SQL Server 討論，有些東西可能在不同資料庫會不太一樣。


Store Procedure

  • 宣告方式
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE PROCEDURE my_procedure
      @param1 AS INT,
      @param2 AS NVARCHAR(MAX),
      @param3 AS DATE
    AS
    BEGIN
    	-- 內容
    END
  • 使用方式
    1
    2
    3
    4
    EXEC my_procedure
      @param1 = 123,
      @param2 = 'a string',
      @param3 = '2025-01-08';
  • 內容
    • 可以有一至多條 DML 指令
    • 可以有一至多條 SELECT 指令，如果有多條、會回傳多個 ResultSet
  • 用途
    • 把多個 SQL 指令包成一個 store procedure
    • 可以直接呼叫一次、做完裡面全部的事
    • 效能較一條一條執行高
      • 因為 SQL Server 已經預先編譯、做好執行計劃
    • 可以另外設定權限
      • 比如，不讓普通使用者直接存取這張資料表，但可以透過這個 store 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

  • 宣告方式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE FUNCTION my_scalar_function(
      @param1 AS INT,
      @param2 AS NVARCHAR(MAX),
      @param3 AS DATE)
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
      -- 內容
      RETURN 單一個值
    END
  • 使用方式：作為一個值使用
    1
    2
    SELECT my_scalar_function(123, 'a string', '2025-01-08')
    FROM table;
  • 內容
    • 不能有 DML 指令
    • 可以有 SELECT 指令和一些計算、流程控制等
    • 要 RETURN 一個值
  • 用途
    • 轉換資料
    • 計算處理
  • 執行結果
    • 單一個值

Inline table-valued function（ITVF）

  • 宣告方式
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE FUNCTION my_itvf(
      @param1 AS INT,
      @param2 AS NVARCHAR(MAX),
      @param3 AS DATE)
    RETURNS TABLE
    AS
    RETURN
      -- 一條 SELECT 指令
  • 使用方式：在 FROM 子句裡作為一張資料表使用
    1
    2
    SELECT *
    FROM my_itvf(123, 'a string', '2025-01-08');
  • 內容
    • 只能有一條 SELECT 指令
  • 用途
    • 把複雜的 SELECT 指令包成一個函數，方便呼叫使用
  • 執行結果
    • 一張資料表

Multi-statement table-valued function（MSTVF）

  • 宣告方式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE FUNCTION my_mstvf(
      @param1 AS INT,
      @param2 AS NVARCHAR(MAX),
      @param3 AS DATE)
    RETURNS @result TABLE (
      column1 INT,
      column2 NVARCHAR(MAX),
      column3 DATE
    )
    AS
    BEGIN
      -- 內容，要 INSERT 內容到 @result 裡面
      RETURN;
    END
  • 使用方式：在 FROM 子句裡作為一張資料表使用
    1
    2
    SELECT *
    FROM my_mstvf(123, 'a string', '2025-01-08');
  • 內容
    • 可以有多條 DML 與 SELECT 指令
    • 要 INSERT 內容到 @result 裡面
  • 用途
    • （可能有）做一些增刪改，最後回傳一個資料表
    • 包成一個函數，方便呼叫使用
  • 執行結果
    • 一張資料表

參考資料


  1. Store Procedure
  2. User-defined Function
    1. Scalar functions
    2. Inline table-valued function（ITVF）
    3. Multi-statement table-valued function（MSTVF）
  3. 參考資料
  1. Store Procedure
  2. User-defined Function
    1. Scalar functions
    2. Inline table-valued function（ITVF）
    3. Multi-statement table-valued function（MSTVF）
  3. 參考資料