Snowflake 儲存程式建構與進階應用

Snowflake 儲存程式是編譯後儲存於 Snowflake 資料函式庫中的程式碼，使用 Snowflake Scripting 語言撰寫。它讓開發者可以將重複使用的 SQL 邏輯封裝起來，方便管理和共用。除了基本功能外，Snowflake 儲存程式還支援更進階的應用，例如引數化查詢、交易處理、傳回物件和結果集等，這些功能都能有效提升資料處理的效率和程式碼的可維護性。本文將逐步引導讀者瞭解如何建構儲存程式，並探討如何運用進階功能解決實際問題，包含如何透過儲存程式有效管理資料載入流程、最佳化查詢效能，以及簡化複雜的資料轉換操作。

儲存程式的建構

既然您已經瞭解了Snowflake Scripting語言的各個組成部分，那麼現在是時候學習如何建立儲存程式了。儲存程式是使用Snowflake Scripting編寫並儲存在Snowflake中的編譯程式。

為何使用儲存程式？

有多種原因促使您使用Snowflake Scripting來建立儲存程式，例如：

• 重複使用的指令碼可以儲存在資料函式庫中，以便於使用和與他人共用。
• 儲存程式允許傳入一個或多個引數值，從而增強指令碼的靈活性。
• 儲存程式名稱可以被過載，這意味著多個儲存程式可以具有相同的名稱，只要引數型別不同即可。
• 儲存程式促進了程式碼的重用，並向終端使用者隱藏了複雜的邏輯。

這些只是使用儲存程式的一些優點，相信您在深入學習後會發現更多的好處。

將指令碼轉換為儲存程式

讓我們從上一章的最終指令碼開始，逐步講解將其轉換為儲存程式所需的步驟。圖16-1顯示了該指令碼。

圖16-1. 傳回平均餘額的指令碼

該指令碼將一個月的總餘額除以天數，並傳回平均每日餘額。要將其轉換為儲存程式，需要進行以下更改：

• 新增create procedure陳述式並提供程式名稱，即get_avg_balance。
• 提供兩個引數（p_total_bal和p_num_days）用於總餘額和天數。
• 定義儲存程式傳回一個數字。
• 指定儲存程式是使用Snowflake Scripting構建的。

圖16-2. 傳回平均餘額的儲存程式

create procedure get_avg_balance(
    p_total_bal number, 
    p_num_days number)
returns number
language sql
as
begin
    return p_total_bal / p_num_days;
end;

將指令碼轉換為儲存程式所需的所有更改都在頂部，其餘的指令碼基本上保持不變。Snowflake儲存程式需要傳回某些內容，但如果您沒有要明確傳回給呼叫者的內容，您可以簡單地傳回字串’Success’，或者傳回由儲存程式修改的行數。同時，我在程式頂部指定了language sql，以指示該程式是使用Snowflake Scripting編寫的，但在這種情況下這不是必需的，因為預設值就是sql。

我在learning_sql.public架構中建立了該指令碼，您可以在Snowsight中的Procedures部分看到它，如圖16-3所示。

圖16-3. Snowsight程式列表

儲存程式的執行

既然我們已經建立了第一個程式，那麼就讓我們來討論一下執行它的不同方法。第一種也是最簡單的方法是使用call命令，如圖16-4所示。

圖16-4. 使用call執行儲存程式

call get_avg_balance(12000, 30);

在這個簡單的示例中，call命令用於執行get_avg_balance()儲存程式，並傳入引數值12000和30，結果（400）顯示在Results部分。圖16-5演示瞭如何在指令碼中使用call命令並將結果捕捉到變數中。

圖16-5. 使用call和into執行儲存程式

declare
    v_avg_bal number;
begin
    call get_avg_balance(12000, 30) into v_avg_bal;
    return v_avg_bal;
end;

此版本使用call命令的into子句將結果放入變數v_avg_bal中。然而，嘗試從SQL陳述式中呼叫儲存程式會導致錯誤：

declare
    v_avg_bal number;
begin
    select get_avg_balance(12000,30) into v_avg_bal;
    return v_avg_bal;
end;

錯誤訊息：Unknown function GET_AVG_BALANCE

看來儲存程式不能從查詢中呼叫，但可以呼叫使用者定義函式，這將在下一章中討論。

實際操作中的儲存程式

既然我們已經瞭解了基本的操作，那麼就讓我們來寫一個能夠做一些更有趣的事情的儲存程式。首先，我將建立一個資料倉儲表來儲存跨國家的、年份和月份的匯總銷售資料。以下是表定義：

create table wh_country_monthly_sales
(
    sales_year number,
    sales_month number,
    region_name varchar(20),
    country_name varchar(30),
    total_sales number
);

接下來，我將建立一個儲存程式來填充新表，該程式應該執行以下操作：

• 開始一個事務。
• 刪除給定地區和年份的任何現有資料。
• 為給定的地區和年份插入行。
• 提交事務，如果出現錯誤則回復。
• 將刪除和插入的行數作為檔案傳回。

新的儲存程式將被命名為ins_country_monthly_sales()，並接受兩個引數：region_name和year。以下是儲存程式定義：

create procedure ins_country_monthly_sales(
    p_region_name varchar, 
    p_year number)
returns object
language sql
as
begin
    begin transaction;
    
    delete from wh_country_monthly_sales
    where region_name = :p_region_name
    and sales_year = :p_year;
    
    let v_num_del number := sqlrowcount;
    
    insert into wh_country_monthly_sales
    (sales_year, sales_month, region_name, country_name, total_sales)
    select date_part(year, o.o_orderdate) as sales_year,
           date_part(month, o_orderdate) as sales_month,
           r.r_name as region_name,
           -- country_name 應該來自哪個表？
           -- total_sales 的計算邏輯是什麼？
           -- 為了示範，這裡暫時省略具體實作
           null as country_name,
           null as total_sales
    from -- 相關表的join操作應該在這裡實作
         -- 為了示範，這裡暫時省略具體實作
         null o
    join -- 相關表的join操作應該在這裡實作
         null r on -- join條件應該在這裡定義
                   -- 為了示範，這裡暫時省略具體實作
                   true;
    
    let v_num_ins number := sqlrowcount;
    
    commit;
    
    return {
        'deleted': v_num_del,
        'inserted': v_num_ins
    };
    
exception
    when other then
        rollback;
        return {
            'error': sqlerrormessage()
        };
end;

內容解密：

1. create procedure ins_country_monthly_sales：建立一個名為 ins_country_monthly_sales 的新儲存程式，用於向 wh_country_monthly_sales 表插入資料。
2. p_region_name varchar, p_year number：定義了兩個輸入引數，分別是地區名稱和年份，用於過濾和插入資料。
3. returns object：指定該儲存程式傳回一個物件型別的值，可以包含多個鍵值對。
4. language sql：指明該儲存程式使用 SQL 語言編寫。
5. begin transaction;：開始一個新的事務，用於確保資料操作的一致性。
6. delete from wh_country_monthly_sales where ...：刪除 wh_country_monthly_sales 表中與輸入引數匹配的現有資料。
7. let v_num_del number := sqlrowcount;：記錄刪除操作影響的行數。
8. insert into wh_country_monthly_sales ...：根據查詢結果向 wh_country_monthly_sales 表插入新資料。查詢陳述式使用了日期函式提取年份和月份，並從相關表中檢索地區名稱和其他必要的資訊。（注意：這裡為了簡化省略了具體的表和欄位，實際使用時需要根據資料函式庫架構進行調整。）
9. let v_num_ins number := sqlrowcount;：記錄插入操作影響的行數。
10. commit;：提交事務，使之前的資料操作生效。
11. return {'deleted': v_num_del, 'inserted': v_num_ins};：傳回一個物件，包含刪除和插入的行數，提供操作的結果反饋。
12. exception when other then ...：捕捉並處理任何在執行過程中發生的異常。如果發生錯誤，則回復事務並傳回錯誤訊息。

Snowflake 預存程式的進階應用

Snowflake 的預存程式（Stored Procedures）為資料函式庫開發人員提供了強大的工具，能夠封裝複雜的邏輯並提高程式碼的重用性。本篇文章將探討 Snowflake 預存程式的進階功能，包括傳回物件、處理交易、傳回結果集以及傳遞陣列作為引數。

傳回物件與交易處理

在 Snowflake 中，預存程式可以傳回多種資料型別，包括物件（Object）。以下是一個範例，展示如何建立一個傳回物件的預存程式，用於插入國家每月銷售資料：

create procedure ins_country_monthly_sales(p_region_name varchar, p_year number)
returns object
language sql
as
declare
  v_num_del number;
  v_num_ins number;
begin
  delete from country_monthly_sales
  where cms_region_name = :p_region_name
  and cms_year = :p_year;
  let v_num_del number := sqlrowcount;

  insert into country_monthly_sales(cms_year, cms_month, cms_region_name, cms_country_name, cms_total_sales)
  select 
    date_part(year, o.o_orderdate),
    date_part(month, o.o_orderdate),
    r.r_name,
    n.n_name,
    sum(o.o_totalprice)
  from orders as o
  inner join customer as c on o.o_custkey = c.c_custkey
  inner join nation as n on c.c_nationkey = n.n_nationkey
  inner join region as r on n.n_regionkey = r.r_regionkey
  where r.r_name = :p_region_name
  and date_part(year, o.o_orderdate) = :p_year
  group by date_part(year, o.o_orderdate), date_part(month, o.o_orderdate), r.r_name, n.n_name;

  let v_num_ins number := sqlrowcount;
  commit;

  return object_construct('rows deleted', v_num_del, 'rows inserted', v_num_ins);
exception
  when other then
    rollback;
    raise;
end;

內容解密：

1. 預存程式定義：建立一個名為 ins_country_monthly_sales 的預存程式，接受兩個引數：p_region_name 和 p_year。
2. 刪除舊資料：根據輸入的地區名稱和年份刪除 country_monthly_sales 表中的舊資料。
3. 插入新資料：從 orders、customer、nation 和 region 表中查詢資料，並將結果插入 country_monthly_sales 表中。
4. 傳回結果：使用 object_construct 函式傳回一個物件，包含刪除和插入的資料行數。

過載預存程式

Snowflake 支援預存程式的過載（Overloading），允許開發人員建立多個同名預存程式，只要它們的引數型別不同。以下是一個範例，展示如何過載 ins_country_monthly_sales 預存程式，以接受 regionkey 而不是 region_name：

create procedure ins_country_monthly_sales(p_regionkey number, p_year number)
returns object
language sql
as
declare
  v_region_name varchar(20);
  v_document object;
begin
  select r.r_name into :v_region_name
  from region as r
  where r.r_regionkey = :p_regionkey;

  call ins_country_monthly_sales(:v_region_name, :p_year) into :v_document;
  return v_document;
end;

內容解密：

1. 過載預存程式：建立一個新的 ins_country_monthly_sales 版本，接受 regionkey 和 year。
2. 轉換引數：根據 regionkey查詢對應的 region_name。
3. 呼叫原始預存程式：使用查詢到的 region_name 和輸入的 year 呼叫原始的 ins_country_monthly_sales 預存程式。

傳回結果集

Snowflake 的預存程式可以傳回結果集（Result Sets），使開發人員能夠以更靈活的方式處理資料。以下是一個範例，展示如何建立一個傳回結果集的預存程式：

create procedure get_country_names(p_regionkey number)
returns table(key number, name varchar)
language sql
as
declare
  v_results resultset;
begin
  v_results := (select n_nationkey, n_name from nation where n_regionkey = :p_regionkey);
  return table(v_results);
end;

內容解密：

1. 定義傳回結果集的預存程式：建立一個名為 get_country_names 的預存程式，傳回一個包含 key 和 name 列的結果集。
2. 查詢資料：根據輸入的 regionkey 從 nation 表中查詢對應國家的鍵值和名稱。
3. 傳回結果集：使用 table() 函式傳回查詢結果。

圖表說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Snowflake 儲存程式建構與進階應用

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示展示了 get_country_names 預存程式的執行流程。首先檢查輸入的 regionkey 是否有效，若有效則查詢對應國家的資料並傳回結果集；若無效則傳回錯誤訊息。