CREATE PROCEDURE - Amazon Redshift

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

CREATE PROCEDURE

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

必要的權限

以下是創建過程所需的權限:

  • 如需創建過程:

    • 超級使用者

    • 具有創建 [或替換] 過程權限的用户

  • 對於替換過程:

    • 超級使用者

    • 具有創建 [或替換] 過程權限的用户

    • 程序擁有者

Syntax (語法)

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

參數

OR REPLACE

此子句指定如果已有一個程序的名稱和輸入引數資料類型 (或簽章) 與此程序相同,則取代現有程序。您只能將程序取代為定義一組相同資料類型的新程序。您必須是超級使用者或擁有者才能取代程序。

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

sp_procedure_name

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

我們建議您將所有儲存過程名稱作為前綴sp_。Amazon Redshift 會保留sp_前綴作為存儲過程名稱。通過使用sp_之前,您可以確保您的預存程序不會與任何現有或 future 的 Amazon Redshift 內建預存程序或函數名稱發生衝突。如需詳細資訊,請參閱 命名預存程序

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

[argname] [ argmode] argtype

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

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

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

INOUT 引數同時為輸入和輸出引數。輸入引數包括 IN 和 INOUT 引數,輸出引數包括 OUT 和 INOUT 引數。

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

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

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

AS $$ procedure_body $$

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

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

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

$$ my statement $$

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

$test$ my statement $test$

此語法也適用於巢狀 $ 符號引用。如需 $ 符號引用的詳細資訊,請參閱 PostgreSQL 文件中的辭典結構下的「$ 符號引用的字串常數」。

procedure_body

一組有效的 PL/pgSQL 陳述式。PL/pgSQL 陳述式以程序性建構 (包括迴圈和條件式表達式) 來擴增 SQL 命令,以控制邏輯流程。大部分 SQL 命令可用於程序主體中,包括資料修改語言 (DML) (例如 COPY、UNLOAD 和 INSERT) 和資料定義語言 (DDL) (例如 CREATE TABLE)。如需詳細資訊,請參閱 PL/pgSQL 語言參考

LANGUAGE plpgsql

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

SECURITY INVOKER | SECURITY DEFINER

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

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

若為 SECURITY DEFINER 模式,程序會以程序擁有者的身分,使用資料庫權限來執行。呼叫程序的使用者必須有程序的執行權限,但不需要基礎物件的任何權限。

SET configuration_parameter { TO value | = value }

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

範例

注意

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

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;

下列範例建立具有一個 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;