CREATE PROCEDURE - Amazon Redshift
所需權限語法參數使用須知範例

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

CREATE PROCEDURE

為目前的資料庫建立新的預存程序或取代現有的程序。

如需詳細資訊和範例，請參閱 在 Amazon Redshift 中建立預存程序。

所需權限

您必須具有下列其中一種執行 CREATE OR REPLACE 的許可PROCEDURE：

  • 對於 CREATE PROCEDURE：

    • 超級使用者

    • 在建立預存程序的結構描述上具有 CREATE和 USAGE權限的使用者

  • 對於 REPLACE PROCEDURE：

    • 超級使用者

    • 程序擁有者

語法

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

參數

或 REPLACE

此子句指定如果已有一個程序的名稱和輸入引數資料類型 (或簽章) 與此程序相同，則取代現有程序。您只能將程序取代為定義一組相同資料類型的新程序。

如果您定義的程序與現有程序同名，但簽章不同，則會建立新程序。換言之，程序名稱是過載。如需詳細資訊，請參閱多載程序名稱。

sp_procedure_name

程序的名稱。如果您指定結構描述名稱 (例如 myschema.myprocedure)，則會在指定的結構描述中建立程序。否則會在目前結構描述中建立程序。如需有效名稱的相關資訊，請參閱 名稱與識別碼。

我們建議您在所有預存程序名稱前加上 sp_。Amazon Redshift 會保留預存程序名稱的 sp_ 字首。您可以使用 sp_ 字首，以確保您的預存程序不會與任何現有或未來的 Amazon Redshift 內建預存程序或函數名稱發生衝突。如需詳細資訊，請參閱命名預存程序。

如果輸入引數的資料類型 (或簽章) 不同，您可以定義多個同名的程序。換言之，在此情況下程序名稱是過載。如需詳細資訊，請參閱 多載程序名稱

[argname] [ argmode] argtype

引數名稱、引數模式和資料類型的清單。僅資料類型是必要的。名稱和模式是選用的，其位置可調換。

引數模式可以是 IN、 OUT或 INOUT。預設值為 IN。

您可以使用 OUT和 INOUT引數，從程序呼叫傳回一或多個值。有 OUT或 INOUT引數時，程序呼叫會傳回一個包含 n 資料欄的結果列，其中 n 是 OUT或 INOUT引數的總數。

INOUT 引數是同時輸入和輸出引數。輸入引數包含 IN 和INOUT引數，而輸出引數包含 OUT和 INOUT引數。

OUT 引數不會指定為CALL陳述式的一部分。在預存程序CALL陳述式中指定INOUT引數。INOUT 從巢狀呼叫傳遞和傳回值，以及傳回 時，引數非常有用refcursor。如需 refcursor 類型的詳細資訊，請參閱游標。

引數資料類型可以是任何標準 Amazon Redshift 資料類型。此外，引數資料類型還可以是 refcursor。

您最多可以指定 32 個輸入引數和 32 個輸出引數。

AS $$ procedure_body $$

包圍要執行之程序的結構。常值關鍵字 AS $$ 和 $$ 是必要的。

Amazon Redshift 會要求您使用稱為 $ 符號引用的格式包圍程序中的陳述式。包圍範圍當中的任何內容都會原封不動傳遞。您不需要逸出任何特殊字元，因為字串的內容是逐字撰寫。

使用 $ 符號引用時，您會使用一組 $$ 符號配對表示要執行之陳述式的開頭和結尾，如以下範例所示。

$$ my statement $$

您也可以選擇在每組配對的兩個 $ 符號之間指定字串來協助識別陳述式。您在包圍配對的開頭和結尾中使用的字串必須相同。此字串區分大小寫，且遵循與未加引號的識別碼相同的限制條件，不過後者不可包含 $ 符號。下列範例使用字串 test。

$test$ my statement $test$

此語法也適用於巢狀 $ 符號引用。如需美元報價的詳細資訊，請參閱 PostgreSQL 文件的 Lexical Structure 下的「美元報價字串常數」。

procedure_body

一組有效的 PL/pgSQL 陳述式。PL/pgSQL 陳述式使用程序建構增強SQL命令，包括迴圈和條件式表達式，以控制邏輯流程。大多數SQL命令都可以在程序內文中使用，包括資料修改語言 （DML），例如 COPY、 UNLOAD和 INSERT，以及資料定義語言 （DDL），例如 CREATE TABLE。如需詳細資訊，請參閱PL/pgSQL 語言參考。

LANGUAGE plpgsql

語言值。指定 plpgsql。您必須具有語言的使用許可，才能使用 plpgsql。如需詳細資訊，請參閱GRANT。

NONATOMIC

在非原子交易模式中建立預存程序。NONATOMIC 模式會自動遞交程序中的陳述式。此外，當NONATOMIC程序內發生錯誤時，如果錯誤是由例外區塊處理，則不會重新擲回錯誤。如需詳細資訊，請參閱 管理交易 和 RAISE。

將預存程序定義為 NONATOMIC 時，請考慮下列事項：

  • 當您巢狀化預存程序呼叫時，必須以相同的交易模式建立所有程序。

  • 在 NONATOMIC 模式下建立程序時，不支援 SECURITY DEFINER 選項和 SET configuration_parameter選項。

  • 處理隱含遞交時，任何已開啟的 (明確或隱含) 游標皆會自動關閉。因此，您必須先開啟明確的交易，才能開始游標迴圈，以確保迴圈的反覆運算SQL中的任何 不會隱含遞交。

SECURITY INVOKER | SECURITY DEFINER

指定 NONATOMIC 時不支援 SECURITY DEFINER 選項。

程序的安全模式決定程序在執行時間的存取權限。程序必須有存取基礎資料庫物件的許可。

對於 SECURITY INVOKER 模式，程序會使用呼叫程序之使用者的權限。使用者必須具有基礎資料庫物件的明確權限。預設值為 SECURITY INVOKER。

對於 SECURITY DEFINER 模式，程序會使用程序擁有者的權限。程序擁有者定義為在執行階段擁有該程序的使用者，而不一定是最初定義程序的使用者。呼叫程序的使用者必須有程序的執行權限，但不需要基礎物件的任何權限。

SET configuration_parameter { TO value | = value }

指定 NONATOMIC 時不支援這些選項。

SET 子句會在輸入程序時將指定的 configuration_parameter 設定為指定的值。當程序離開時，此子句會接著將 configuration_parameter 還原到先前的值。

使用須知

如果使用 SECURITYDEFINER選項建立預存程序，則在預存程序中叫用 CURRENT_USER 函數時，Amazon Redshift 會傳回預存程序擁有者的使用者名稱。

範例

注意

如果在執行這些範例時，您遇到類似以下的錯誤：

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

請參閱 Amazon Redshift 中的預存程序概觀。

下列範例建立具有兩個輸入參數的程序。

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;
注意

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

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

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

下列範例會建立具有一個 IN 參數、一個 OUT 參數和一個 INOUT 參數的程序。

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;