Amazon Redshift 中的預存程序概觀 - Amazon Redshift

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

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「工作台/J」時，我們建議使用版本 124。當您使用SQL工作台階 /J 版本 125 時，請考慮指定替代分隔符號作為因應措施。

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

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

如需詳細資訊，請參閱 SQL WorkBench/J 文件中的替代分隔符號。或者使用具有更好支援剖析CREATEPROCEDURE陳述式的用戶端，例如 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

注意

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

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

如需預存程序的相關資訊，請參閱 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)