Amazon Redshift 中的預存程序概觀

預存程序通常用於封裝資料轉換、資料驗證的邏輯和特定商業邏輯。將多個 SQL 步驟結合在一個預存程序中，可以減少應用程式和資料庫之間的往返次數。

如需精細的存取控制，您可以建立預存程序來執行功能，而不需要讓使用者存取基礎資料表。例如，只有擁有者或超級使用者才能截斷資料表，使用者需要寫入權限，才能將資料插入資料表。您可以建立預存程序來執行任務，而不是授予使用者對基礎資料表的權限。然後，您會給予使用者權限來執行預存程序。

具有 DEFINER 安全屬性的預存程序會以預存程序擁有者的權限執行。根據預設，預存程序具有 INVOKER 安全性，這表示程序會使用程序呼叫使用者的權限。

若要建立預存程序，請使用 CREATE PROCEDURE 命令。若要執行程序，請使用 CALL 命令。本節稍後提供範例。

注意

在建立 Amazon Redshift 預存程序時，有些用戶端可能會顯示下列錯誤。

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

導致此錯誤的原因是用戶端無法正確剖析含有分號分隔陳述式和貨幣符號 ($) 引用的 CREATE PROCEDURE 陳述式。這會導致只有一部分陳述式傳送至 Amazon Redshift 伺服器。您通常可以透過用戶端的 Run as batch 或 Execute selected 選項來解決此錯誤。

舉例而言，使用 Aginity 用戶端時，您可以利用 Run entire script as batch 選項。建議您使用 SQL Workbench/J 版本 124。使用 SQL Workbench/J 版本 125 時，請考慮指定替代分隔符號來解決這個問題。

CREATE PROCEDURE 包含以分號 (;) 分隔的 SQL 陳述式。定義替代分隔符號 (例如斜線 (/))，並將其放在 CREATE PROCEDURE 陳述式的結尾，會將陳述式傳送至 Amazon Redshift 伺服器進行處理。以下是範例。

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

如需詳細資訊，請參閱 SQL Workbench/J 文件中的 Alternate delimiter (替代分隔符號)。或者使用具有更好支援剖析 CREATE 程序陳述式的用戶端，例如 Amazon Redshift 主控台中的查詢編輯器或 TablePlus.

主題

• 命名預存程序
• 預存程序的安全和權限
• 傳回結果集
• 管理交易
• 捕捉錯誤
• 記錄預存程序日誌
• 預存程序支援的考量

下列範例顯示不具有輸出引數的程序。根據預設，引數是輸入 (IN) 引數。

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL   

注意

當您撰寫預存程序時，我們建議您採用保護敏感值的最佳作法：

不要在預存程序邏輯中對任何敏感資訊進行硬式編碼。例如，請勿在預存程序主體的 CREATE USER 陳述式中指派使用者密碼。這會造成安全性風險，因為硬式編碼值可以記錄為目錄資料表中的結構描述資料。應改為透過參數將敏感值 (例如密碼) 當做引數傳遞給預存程序。

如需預存程序的詳細資訊，請參閱 CREATE PROCEDURE 和在 Amazon Redshift 中建立預存程序。如需有關目錄資料表的詳細資訊，請參閱 系統目錄資料表。

下列範例顯示具有輸出引數的程序。引數為輸入 (IN)、輸入和輸出 (INOUT) 及輸出 (OUT)。

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)