CREATE PROCEDURE - Amazon Redshift
Erforderliche BerechtigungenSyntaxParameterNutzungshinweiseBeispiele

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

CREATE PROCEDURE

Erstellt eine neue gespeicherte Prozedur oder ersetzt eine vorhandene Prozedur für die aktuelle Datenbank.

Weitere Informationen und Beispiele finden Sie unter Erstellen von gespeicherten Prozeduren in Amazon Redshift.

Erforderliche Berechtigungen

Sie benötigen eine der folgenden Berechtigungen, um CREATE OR REPLACE PROCEDURE ausführen zu können:

  • Für CREATE PROCEDURE:

    • Superuser

    • Benutzer mit den Berechtigungen CREATE und USAGE für das Schema, in dem die gespeicherte Prozedur erstellt wird

  • Für REPLACE PROCEDURE:

    • Superuser

    • Besitzer des Verfahrens

Syntax

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 } ]

Parameter

OR REPLACE

Eine Klausel, die angibt, dass die vorhandene Prozedur ersetzt wird, wenn eine Prozedur mit demselben Namen und denselben Eingabeargument-Datentypen bzw. derselben Signatur vorhanden ist. Sie können eine Prozedur nur durch eine neue Prozedur ersetzen, wenn diese einen identischen Satz von Datentypen definiert.

Wenn Sie eine Prozedur mit dem gleichen Namen wie eine vorhandene Prozedur, aber mit einer anderen Signatur definieren, erstellen Sie eine neue Prozedur. Mit anderen Worten: Der Prozedurname ist überladen. Weitere Informationen finden Sie unter Überladen von Prozedurnamen.

sp_procedure_name

Der Name der Prozedur. Wenn Sie einen Schemanamen angeben (beispielsweise myschema.myprocedure), wird die Funktion im angegebenen Schema erstellt. Andernfalls wird die Prozedur im aktuellen Schema erstellt. Weitere Informationen zu gültigen Namen finden Sie unter Namen und Kennungen.

Es wird empfohlen, dass Sie alle gespeicherten Prozeduren mit dem Präfix benennen sp_. Amazon Redshift reserviert das Präfix sp_ für gespeicherte Prozeduren. Durch die Verwendung des Präfix sp_ können Sie sicherstellen, dass keine Konflikte zwischen den Namen Ihrer gespeicherten Prozeduren und den Namen von bereits vorhandenen oder künftigen in Amazon Redshift integrierten gespeicherten Prozeduren oder Funktionen auftreten. Weitere Informationen finden Sie unter Benennen von gespeicherten Prozeduren.

Sie können mehr als eine Prozedur mit demselben Namen definieren, wenn sich die Datentypen für die Eingabeargumente bzw. Signaturen unterscheiden. Mit anderen Worten: In diesem Fall ist der Prozedurname überladen. Weitere Informationen finden Sie unter Überladen von Prozedurnamen

[argname] [ argmode] argtype

Eine Liste von Argumentnamen, Argumentmodi und Datentypen. Nur der Datentyp ist erforderlich. Name und Modus sind optional und ihre Position kann vertauscht werden.

Der Argumentmodus kann IN, OUT oder INOUT sein. Der Standardwert ist IN.

Sie können OUT- und INOUT-Argumente verwenden, um bei einem Prozeduraufruf einen oder mehrere Werte zurückzugeben. Wenn OUT- oder INOUT-Argumente vorhanden sind, gibt der Prozeduraufruf eine Ergebnisreihe mit n Spalten zurück, wobei n die Gesamtzahl der OUT- oder INOUT-Argumente ist.

INOUT-Argumente sind gleichzeitig Ein- und Ausgabeargumente. Eingabeargumente umfassen sowohl IN- als auch INOUT-Argumente und Ausgabeargumente umfassen sowohl OUT- als auch INOUT-Argumente.

OUT-Argumente werden im Rahmen der CALL-Anweisung nicht angegeben. Sie können INOUT-Argumente in der CALL-Anweisung für gespeicherte Prozeduren angeben. INOUT-Argumente können nützlich sein, wenn Werte übergeben oder von einem verschachtelten Aufruf zurückgegeben werden und auch wenn ein zurückgegeben wird refcursor. Weitere Informationen zu refcursor-Typen finden Sie unter Cursor.

Die Argumentdatentypen können jeder beliebige Amazon-Redshift-Standarddatentyp sein. Eine weitere Möglichkeit für Argumentdatentypen ist außerdem refcursor.

Sie können maximal 32 Eingabeargumente und 32 Ausgabeargumente angeben.

AS $$ procedure_body $$

Ein Konstrukt, das die auszuführende Anweisung umschließt. Die Literalschlüsselwörter AS $$ und $$ sind erforderlich.

Amazon Redshift erfordert, dass Sie die Anweisung in Ihrer Prozedur unter Verwendung eines Formats umschließen, das Dollaranführung genannt wird. Alles innerhalb der Umschließung wird exakt wie angegeben übergeben. Sie müssen für Sonderzeichen keine Escape-Zeichen verwenden, da der Inhalt der Zeichenfolge wie exakt wie angegeben geschrieben wird.

Bei der Dollaranführung verwenden Sie ein Dollarzeichenpaar ($$), um den Anfang und das Ende der auszuführenden Anweisung zu kennzeichnen, wie im folgenden Beispiel gezeigt.

$$ my statement $$

Optional können Sie zwischen den beiden Dollarzeichen eine Zeichenfolge angeben, um die Identifizierung der Anweisung zu unterstützen. Die von Ihnen im umschließenden Dollarzeichenpaar am Anfang und am Ende verwendete Zeichenfolge muss identisch sein. Diese Zeichenfolge unterscheidet zwischen Groß- und Kleinschreibung und unterliegt den gleichen Einschränkungen wie Bezeichner, die nicht in einer Anführung stehen, darf jedoch keine Dollarzeichen enthalten. Im folgenden Beispiel wird die Zeichenfolge "test" verwendet.

$test$ my statement $test$

Dieses Syntax ist auch für die verschachtelte Dollaranführung nützlich. Weitere Informationen zur Dollaranführung finden Sie unter "Zeichenfolgenkonstanten in Dollaranführung" im Abschnitt Lexikalische Struktur der PostgreSQL-Dokumentation.

procedure_body

Eine Reihe gültiger PL/pgSQL statements. PL/pgSQL Anweisungen erweitert SQL-Befehle um prozedurale Konstrukte, einschließlich Schleifenausdrücken und bedingten Ausdrücken, um den logischen Fluss zu steuern. Die meisten SQL-Befehle können in der Prozedur verwendet werden, einschließlich DML-Befehlen wie COPY, UNLOAD und INSERT und DDL-Befehlen wie CREATE TABLE. Weitere Informationen finden Sie unter PL/pgSQL-Sprachreferenz.

LANGUAGE plpgsql

Ein Sprachenwert. Geben Sie an plpgsql. Sie müssen eine Berechtigung für die Nutzung der Sprache haben, um verwenden zu können plpgsql. Weitere Informationen finden Sie unter GRANT.

NONATOMIC

Erstellt die gespeicherte Prozedur in einem nicht atomaren Transaktionsmodus. Im Modus NONATOMIC werden die Anweisungen innerhalb der Prozedur automatisch festgeschrieben. Zudem wird ein Fehler innerhalb der NONATOMIC-Prozedur nicht erneut ausgelöst, wenn er durch einen Ausnahmeblock behandelt wird. Weitere Informationen erhalten Sie unter Verwalten von Transaktionen und RAISE.

Wenn Sie eine gespeicherte Prozedur als NONATOMIC definieren, beachten Sie Folgendes:

  • Wenn Sie Aufrufe von gespeicherten Prozeduren verschachteln, müssen alle Prozeduren in demselben Transaktionsmodus erstellt werden.

  • Die Optionen SECURITY DEFINER und SET configuration_parameter werden nicht unterstützt, wenn eine Prozedur im NONATOMIC-Modus erstellt wird.

  • Jeder (explizit oder implizit) geöffnete Cursor wird automatisch geschlossen, wenn ein impliziter Commit verarbeitet wird. Daher müssen Sie eine explizite Transaktion öffnen, bevor Sie eine Cursor-Schleife starten, um sicherzustellen, dass SQL innerhalb der Iteration der Schleife nicht implizit festgeschrieben wird.

SECURITY INVOKER | SECURITY DEFINER

Die Option SECURITY DEFINER wird nicht unterstützt, wenn NONATOMIC angegeben ist.

Der Sicherheitsmodus für die Prozedur bestimmt die Zugriffsrechte der Prozedur zur Laufzeit. Die Prozedur muss über die Berechtigung zum Zugreifen auf die zugrunde liegenden Datenbankobjekte verfügen.

Im SECURITY INVOKER-Modus verwendet die Prozedur die Rechte des Benutzers, der die Prozedur aufruft. Der Benutzer muss über explizite Berechtigungen für die zugrunde liegenden Datenbankobjekte verfügen. Standardmäßig ist SECURITY INVOKER festgelegt.

Im Modus SECURITY DEFINER verwendet die Prozedur die Berechtigungen des Eigentümers der Prozedur. Der Eigentümer der Prozedur ist definiert als der Benutzer, der die Prozedur zur Laufzeit besitzt. Dies muss nicht zwingend der Benutzer sein, der die Prozedur ursprünglich definiert hat. Der Benutzer, der die Prozedur aufruft, muss über Ausführungsrechte für die Prozedur verfügen. Er benötigt aber keine Rechte für die zugrunde liegenden Objekte.

SET configuration_parameter { TO value | = value }

Diese Optionen werden nicht unterstützt, wenn NONATOMIC angegeben ist.

Die SET-Klausel bewirkt, dass der angegebene configuration_parameter beim Starten der Prozedur auf den angegebenen Wert gesetzt wird. Beim Beenden der Prozedur setzte die Klausel den configuration_parameter dann wieder auf seinen vorherigen Wert zurück.

Nutzungshinweise

Wenn eine gespeicherte Prozedur mit der Option SECURITY DEFINER erstellt wurde, gibt Amazon Redshift beim Aufrufen der CURRENT_USER-Funktion innerhalb der gespeicherten Prozedur den Benutzernamen des Besitzers der gespeicherten Prozedur zurück.

Beispiele

Anmerkung

Wenn beim Ausführen dieser Beispiele ein Fehler ähnlich dem folgenden auftritt:

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

Siehe Übersicht über gespeicherte Prozeduren in Amazon Redshift.

Das folgende Beispiel erstellt eine Prozedur mit zwei Eingabeparametern.

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;
Anmerkung

Wenn Sie gespeicherte Prozeduren schreiben, empfehlen wir eine bewährte Methode zum Sichern sensibler Werte:

Nehmen Sie keine Hardkodierung für sensible Informationen in der gespeicherten Prozedurlogik vor. Weisen Sie beispielsweise kein Benutzerkennwort in einer CREATE USER-Anweisung im Text einer gespeicherten Prozedur zu. Dies stellt ein Sicherheitsrisiko dar, da hartkodierte Werte als Schema-Metadaten in Katalogtabellen aufgezeichnet werden können. Übergeben Sie stattdessen mithilfe von Parametern sensible Werte wie Passwörter als Argumente an die gespeicherte Prozedur.

Weitere Informationen über gespeicherte Prozeduren finden Sie unter CREATE PROCEDURE und Erstellen von gespeicherten Prozeduren in Amazon Redshift. Weitere Informationen über die Katalogtabellen finden Sie unter Systemkatalogtabellen.

Das folgende Beispiel erstellt eine Prozedur mit einem IN-Parameter, einem OUT-Parameter und einem INOUT-Parameter.

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;

Im folgenden Beispiel wird eine Prozedur erstellt, die den Parameter verwendet. SECURITY DEFINER Diese Prozedur wird mit den Rechten des Benutzers ausgeführt, dem die Prozedur gehört.

CREATE OR REPLACE PROCEDURE sp_get_current_user_definer()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

Im folgenden Beispiel wird eine Prozedur erstellt, die den SECURITY INVOKER Parameter verwendet. Diese Prozedur wird mit den Rechten des Benutzers ausgeführt, der die Prozedur ausführt.

CREATE OR REPLACE PROCEDURE sp_get_current_user_invoker()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY INVOKER;