CREATE PROCEDURE - Amazon Redshift
Privilèges requisSyntaxeParamètresNotes d’utilisationExemples

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

CREATE PROCEDURE

Crée une nouvelle procédure stockée ou remplace une procédure existante pour la base de données actuelle.

Pour plus d’informations et d’exemples, consultez Création de procédures stockées dans Amazon Redshift.

Privilèges requis

Vous devez être autorisé par l'une des méthodes suivantes pour exécuter CREATE OR REPLACE PROCEDURE :

  • Pour CREATE PROCEDURE :

    • Superuser

    • Utilisateurs disposant CREATE de USAGE privilèges sur le schéma dans lequel la procédure stockée est créée

  • Pour REPLACE PROCEDURE :

    • Superuser

    • Propriétaire de la procédure

Syntaxe

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

Paramètres

OU REPLACE

Clause qui spécifie que si une procédure ayant le même nom et les mêmes types de données, ou signature, existe déjà, la procédure existante est remplacée. Vous pouvez uniquement remplacer une procédure par une nouvelle procédure qui définit un ensemble identique de types de données.

Si vous définissez une procédure avec le même nom qu’une procédure existante, mais une signature différente, vous créez une nouvelle procédure. Autrement dit, le nom de la procédure est surchargé. Pour plus d'informations, consultez Surcharge des noms de procédure.

sp_procedure_name

Nom de la procédure. Si vous spécifiez un nom de schéma (tel que myschema.myprocedure), la procédure est créée à dans le schéma spécifié. Sinon, la procédure est créée dans le schéma en cours. Pour plus d’informations sur les noms valides, consultez Noms et identificateurs.

Nous vous conseillons d’utiliser le préfixe sp_ pour tous les noms des procédures stockées. Amazon Redshift réserve le préfixe sp_ pour les noms de procédures stockées. Utiliser le préfixe sp_ vous permet de garantir que le nom de votre procédure stockée n’est en conflit avec aucun nom de fonction ou de procédure stockée intégré à Amazon Redshift existant ou futur. Pour plus d'informations, consultez Dénomination des procédures stockées.

Vous pouvez définir plusieurs procédures portant le même nom si les types de données des arguments en entrée, ou signature, sont différents. Autrement dit, dans ce cas, le nom de la procédure est surchargé. Pour plus d'informations, consultez Surcharge des noms de procédure

[argname] [ argmode] argtype

Liste de noms d’arguments, de modes d’argument et de types de données. Seul le type de données est requis. Le nom et le mode sont facultatifs, et leur position peut être inversée.

Le mode d'argument peut être INOUT, ouINOUT. La valeur par défaut est IN.

Vous pouvez utiliser des INOUT arguments OUT et pour renvoyer une ou plusieurs valeurs à partir d'un appel de procédure. Lorsqu'il y a des INOUT arguments OUT ou, l'appel de procédure renvoie une ligne de résultat contenant n colonnes, où n est le nombre total OUT d'INOUTarguments.

INOUTles arguments sont des arguments d'entrée et de sortie en même temps. Les arguments d'entrée incluent à la fois des arguments IN et INOUT des arguments, et les arguments de sortie incluent à la fois OUT des INOUT arguments et des arguments.

OUTles arguments ne sont pas spécifiés dans le cadre de la CALL déclaration. Spécifiez INOUT les arguments dans l'CALLinstruction de procédure stockée. INOUTles arguments peuvent être utiles lors de la transmission et du renvoi de valeurs à partir d'un appel imbriqué, ainsi que lors du renvoi d'unrefcursor. Pour plus d’informations sur les types refcursor, consultez Curseurs.

Les types de données d’arguments peuvent être de n’importe quel type de données Amazon Redshift standard. En outre, refcursor peut être un type de données d’argument.

Vous pouvez spécifier un maximum de 32 arguments en entrée et de 32 arguments en sortie.

AS $$ procedure_body $$

Construction qui englobe la procédure à exécuter. Les mots-clés littéraux AS $$ et $$ sont obligatoires.

Amazon Redshift requiert que vous placiez l’instruction dans votre procédure à l’aide d’un format appelé guillemets dollar. Tout ce qui se trouve dans l’encadrement est passé exactement comme tel. Vous n’avez pas besoin de définir de séquence d’échappement pour les caractères spéciaux, car les contenus de la chaîne sont écrits littéralement.

Avec les guillemets dollar, vous utilisez une paire de symboles dollar ($$) pour marquer le début et la fin de l’instruction à exécuter, comme illustré dans l’exemple suivant.

$$ my statement $$

Le cas échéant, entre les symboles dollar de chaque paire, vous pouvez spécifier une chaîne pour aider à identifier l’instruction. La chaîne que vous utilisez doit être la même au début et à la fin des paires d’encadrement. Cette chaîne est sensible à la casse et suit les mêmes contraintes qu’un identificateur sans guillemets, sauf que celui-ci ne peut pas contenir de symboles dollar. L’exemple suivant utilise la chaîne test.

$test$ my statement $test$

Cette syntaxe est également utile les guillemets dollar imbriqués. Pour plus d'informations sur la cotation en dollars, consultez « Constantes de chaîne cotées en dollars » sous Structure lexicale dans la documentation Postgre. SQL

procedure_body

Un ensemble d'instructions PL/pG SQL valides. SQLLes instructions PL/Pg ajoutent aux SQL commandes des constructions procédurales, notamment des expressions conditionnelles et des boucles, afin de contrôler le flux logique. La plupart des SQL commandes peuvent être utilisées dans le corps de la procédure, y compris le langage de modification des données (DML) tel que COPYINSERT, UNLOAD et le langage de définition des données (DDL) tel que CREATETABLE. Pour de plus amples informations, veuillez consulter Guide de référence du langage PL/pgSQL.

LANGUAGEplpgsql

Valeur pour le langage. Spécifiez plpgsql. Vous devez avoir l’autorisation pour USAGE ON LANGUAGE pour utiliser plpgsql. Pour de plus amples informations, veuillez consulter GRANT.

NONATOMIC

Crée la procédure stockée dans un mode de transaction non atomique. NONATOMICle mode valide automatiquement les instructions contenues dans la procédure. De plus, lorsqu'une erreur se produit dans la NONATOMIC procédure, elle n'est pas renvoyée si elle est gérée par un bloc d'exceptions. Pour plus d’informations, consultez Gestion des transactions et RAISE.

Lorsque vous définissez une procédure stockée comme NONATOMIC, tenez compte des éléments suivants :

  • Lorsque vous imbriquez des appels de procédures stockées, toutes les procédures doivent être créées dans le même mode de transaction.

  • L'SECURITY DEFINERoption et l'SET configuration_parameteroption ne sont pas prises en charge lors de la création d'une procédure en NONATOMIC mode.

  • Tout curseur ouvert (explicitement ou implicitement) est fermé automatiquement lorsqu’une validation implicite est effectuée. Par conséquent, vous devez ouvrir une transaction explicite avant de commencer une boucle de curseur afin de vous assurer que toute itération comprise SQL dans la boucle n'est pas implicitement validée.

SECURITY INVOKER | SECURITY DEFINER

L’option SECURITY DEFINER n’est pas prise en charge lorsque NONATOMIC est spécifié.

Le mode de sécurité pour la procédure détermine les privilèges d’accès de la procédure lors de l’exécution. La procédure doit être autorisée à accéder aux objets de la base de données sous-jacente.

Pour le SECURITY INVOKER mode, la procédure utilise les privilèges de l'utilisateur qui appelle la procédure. L’utilisateur doit disposer d’autorisations explicites sur les objets de la base de données sous-jacente. La valeur par défaut est SECURITYINVOKER.

Pour le SECURITY DEFINER mode, la procédure utilise les privilèges du propriétaire de la procédure. Le propriétaire de la procédure est défini comme l’utilisateur propriétaire de la procédure au moment de l’exécution, pas nécessairement l’utilisateur qui a initialement défini la procédure. L’utilisateur qui appelle la procédure doit détenir un privilège EXECUTE sur la procédure, mais il n’a pas besoin de privilèges sur les objets sous-jacents.

SETconfiguration_parameter {VALEUR TO | = valeur}

Ces options ne sont pas prises en charge lorsque NONATOMIC est spécifié.

La SET clause entraîne le réglage de la valeur spécifiée configuration_parameter à la valeur spécifiée lors de la saisie de la procédure. Cette clause restaure ensuite configuration_parameter à sa valeur précédente à la fin de la procédure.

Notes d’utilisation

Si une procédure stockée a été créée à l'aide de l'SECURITYDEFINERoption, lors de l'appel de la USER fonction CURRENT _ depuis la procédure stockée, Amazon Redshift renvoie le nom d'utilisateur du propriétaire de la procédure stockée.

Exemples

Note

Si vous rencontrez une erreur similaire à ce qui suit lors de l’exécution de ces exemples :

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

Consultez Présentation des procédures stockées dans Amazon Redshift.

L’exemple suivant crée une procédure avec deux paramètres d’entrée.

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

Lorsque vous écrivez des procédures stockées, nous vous recommandons une bonne pratique pour sécuriser les valeurs sensibles :

Ne codez pas en dur des informations sensibles dans la logique des procédures stockées. Par exemple, n'attribuez pas de mot de passe utilisateur dans une CREATE USER instruction figurant dans le corps d'une procédure stockée. Cela présente un risque de sécurité, car les valeurs codées en dur peuvent être enregistrées sous forme de métadonnées de schéma dans les tables du catalogue. Transmettez plutôt des valeurs sensibles, telles que des mots de passe, en tant qu’arguments à la procédure stockée, au moyen de paramètres.

Pour plus d'informations sur les procédures stockées, consultez CREATEPROCEDUREla section Création de procédures stockées dans Amazon Redshift. Pour plus d’informations sur les tables catalogue, consultez Tables de catalogue système.

L'exemple suivant crée une procédure avec un paramètre IN, un OUT paramètre et un INOUT paramètre.

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;