Utilisation de procédures stockées dans DataStage

Une procédure mémorisée est un bloc de constructions procédurales et d'instructions SQL imbriquées. Utilisez l'option Appeler la procédure sous la propriété Méthode de lecture ou Méthode d'écriture de l'onglet Etape pour appeler une procédure stockée existante à partir d'une base de données. Utilisez l'option d'appel d'instruction de procédure pour écrire votre propre syntaxe d'instruction de procédure dans la zone de texte.

Connecteurs pris en charge

Les connecteurs suivants prennent en charge les procédures mémorisées. Les procédures mémorisées peuvent être exécutées sur des connecteurs avec des liens d'entrée, des liens de sortie ou les deux.

Amazon RDS for PostgreSQL
IBM Cloud® Databases for PostgreSQL
IBM Db2®
IBM® Db2 Big SQL
IBM Db2 on Cloud
IBM Db2 for i
IBM Db2 Warehouse
IBM Db2 for z/OS®
Google BigQuery
Microsoft Azure SQL Database
Microsoft SQL Server
Microsoft Azure Synapse Analytics
Oracle
PostgreSQL
SAP ASE
Snowflake
Teradata

Procédures mémorisées dans une étape source

Sélectionnez Ajouter une valeur de retour de procédure au schéma pour ajouter le code retour de procédure et le message au schéma en tant que colonnes ProcCode et ProcMess. La première colonne du lien de sortie contient la valeur du code. La deuxième colonne contient la valeur du message. Si vous sélectionnez également Transférer les données de ligne, les colonnes d'entrée sont ajoutées au lien de sortie après les colonnes ProcCode et ProcMess .

Procédures mémorisées dans une étape cible

Sélectionnez Exécuter la procédure pour chaque ligne pour exécuter la procédure pour chaque ligne des données d'entrée. Si vous ne sélectionnez pas Exécuter la procédure pour chaque ligne, la procédure ne s'exécute qu'une seule fois, pour la première ligne. Par défaut, Exécuter la procédure pour chaque ligne est sélectionné. Cette procédure n'est pas prise en charge par le connecteur Snowflake.

Syntaxe

Les appels de procédure mémorisée utilisent ? comme marqueur de paramètre. La syntaxe suivante appelle une procédure mémorisée avec le nom <procedure_name> sur un paramètre d'entrée @<column_name> et une colonne de sortie ?, les deux entre parenthèses.

CALL <procedure_name>(@<column_name>, ?)

Si le paramètre d'entrée est une chaîne, placez le nom entre guillemets.

? = CALL <procedure_name>('@<string_column_name>', ?)

Dans l'onglet Étape, sous Propriétés générales de la source si vous choisissez Méthode de lecture > Énoncé de procédure d'appel et sélectionnez Ajouter la valeur de retour de la procédure au schéma, deux colonnes (ProcCode et ProcMess) sont alors ajoutées à la sortie en tant que premières colonnes du schéma de sortie.

Dans l'onglet Stage, sous SQL > Instruction de procédure d'appel :

Pour les connecteurs (à l'exception de Microsoft SQL Server et SAP ASE) qui prennent en charge les procédures stockées, fournissez toujours la déclaration suivante :
```
CALL (@column_name, ?)
```
Pour les connecteurs Microsoft SQL Server et SAP ASE, la syntaxe de ? = est valide et le code de retour de la procédure est capturé dans ? et envoyé à la colonne de sortie. Vous devez toujours fournir la déclaration suivante :
```
? = CALL (@column_name, ?)
```

Si la procédure stockée est appelée dans une étape ne comportant que des liens de sortie, ? indique toujours un paramètre de sortie. Si la procédure stockée est appelée dans une étape ne comportant que des liens d'entrée, ? indique toujours un paramètre d'entrée.


CALL <procedure_name>()
CALL <procedure_name>(?, ?)
CALL <procedure_name>(4,?)

Une procédure stockée pour le connecteur PostgreSQL ne peut pas renvoyer plusieurs lignes dans les paramètres de sortie. Pour obtenir plusieurs lignes, utilisez plutôt le tableau des fonctions définies par l'utilisateur.

Valeurs des paramètres

Dans la section Valeurs des paramètres de l' onglet Etape, utilisez les spécifications de paramètres pour mapper les paramètres d'entrée et de sortie de votre procédure mémorisée aux colonnes d'entrée et de sortie. Cela détermine les colonnes dans lesquelles les données sont renvoyées. Les noms des paramètres dans le Onglet de scène doivent correspondre aux noms des paramètres de votre procédure stockée. Chaque nom de paramètre peut être mappé à une seule colonne. Si aucune correspondance n'est fournie, les données de sortie pour chaque colonne sont renvoyées dans l'ordre où les colonnes sont spécifiées. Un connecteur avec un lien d'entrée aura des paramètres d'entrée, un connecteur avec un lien de sortie aura des paramètres de sortie, et un connecteur avec des liens d'entrée et de sortie aura les deux.

Lorsqu'un connecteur possède à la fois des liens d'entrée et de sortie, vous pouvez modifier la valeur du marqueur/littéral pour vos paramètres d'entrée dans l'onglet Étape. Vous pouvez spécifier un nom de paramètre d'entrée comme :

@: Valeur par défaut du paramètre. La valeur de la colonne spécifiée dans le mappage est transmise au paramètre.

Littéral: Constante spécifiée comme paramètre d'entrée. La valeur de la constante est utilisée et la valeur du lien d'entrée est ignorée.

Paramètre local ou paramètre d'un jeu de paramètres: Pour un paramètre d'un jeu de paramètres, utilisez le format (#name#) . La valeur du paramètre est utilisée et la valeur de la liaison d'entrée est ignorée.

Chaque paramètre doit être mis en correspondance avec la colonne d'entrée. Par exemple, P1 doit être mappé vers COLUMN_1, et P2 vers COLUMN_2. Le mappage des paramètres ne remplace pas la définition des colonnes. Cependant, vous pouvez ignorer la valeur d'un lien d'entrée et utiliser la valeur de la colonne Marqueur / Littéral.

Paramètres de `Inout`

Pour le connecteur Microsoft SQL Server vous pouvez utiliser un type de paramètre inout pour vos procédures stockées. Le paramètre inout agit à la fois comme un paramètre d'entrée et de sortie. Une procédure reçoit une valeur pour le paramètre, la traite et renvoie une valeur modifiée par le biais du même paramètre. Vous ne pouvez utiliser les paramètres inout que lorsque connector est configuré en mode transformation.

La valeur d'entrée du paramètre inout ne peut être transmise que par le lien ou à partir d'une carte de paramètres dans la section Valeurs des paramètres. Les valeurs d'entrée du paramètre inout ne peuvent pas être fixées dans votre déclaration. Par exemple, ?=(CALL) test (1,?,?) est une affirmation incorrecte, tandis que ?=CALL test (?,?,?) est correcte.

Paramètres optionnels

Vous ne pouvez utiliser des paramètres facultatifs que pour les procédures stockées. Les fonctions définies par l'utilisateur ne prennent pas en charge les paramètres optionnels. Une procédure stockée peut avoir un ou plusieurs paramètres facultatifs. Si vous souhaitez omettre les paramètres facultatifs lors de l'appel d'une procédure stockée, utilisez l'instruction Call procedure. Si une procédure stockée possède un ou plusieurs paramètres facultatifs, tous les paramètres facultatifs doivent être définis après les paramètres de sortie. Les paramètres facultatifs ayant la valeur " Null doivent être regroupés à la fin.

Les connecteurs suivants prennent en charge le paramètre facultatif dans les procédures stockées :

IBM Db2
IBM Db2 Big SQL
IBM Db2 on Cloud
IBM Db2 for i
IBM Db2 Warehouse
Microsoft Azure SQL Database
Microsoft SQL Server
Oracle
Snowflake

Exemple: Une procédure stockée " MYSCHEMA.MYSP possède deux paramètres d'entrée obligatoiresinparam1, inparam2), deux entrées facultativesoptinparam3, optinparam4) et deux paramètres de sortie. Voir les exemples suivants d'utilisation valide et invalide de l'instruction Call procedure :

Valide :

Dans ce cas, la valeur d'entrée pour optinparam3 et optinparam4 est utilisée :

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, optinparam3, optinparam4)

Dans ce cas, la valeur d'entrée de l'optinparam3 est utilisée et la valeur par défaut de l'optinparam4 est utilisée :

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, optinparam3)

Dans ce cas, la valeur par défaut des optinparam3 et optinparam4 est utilisée :

CALL MYSCHEMA.MYSP(in1, in2, ?, ?)

Invalide :

CALL MYSCHEMA.MYSP(inparam1, inparam2, optinparam3, optinparam4, ?, ?)

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, , optinparam4)

Expiration de la requête

Pour les requêtes à exécution longue et les procédures stockées, définissez la propriété Query timeout afin de limiter le nombre de secondes d'exécution d'une procédure. La valeur par défaut est 300 secondes. Si vous définissez une valeur supérieure à 600 secondes, vous devez également éditer la variable d'environnement $OSH_JOB_START_TIMEOUT et définir une valeur supérieure au délai d'attente de la requête. Cette propriété est disponible sur Microsoft SQL Server, Microsoft Azure SQL Database, IBM Db2, IBM Db2 Big SQL, IBM Db2 on Cloud, IBM Db2 for i, IBM Db2 Warehouseet IBM Db2 for z/OS.

Fonctions définies par l'utilisateur (UDF)

Lorsque l'option Appeler une procédure ou Appeler une instruction de procédure est sélectionnée, vous pouvez sélectionner Fonction définie par l'utilisateur pour spécifier une fonction définie par l'utilisateur à exécuter. Une fonction définie par l'utilisateur peut prendre des paramètres d'entrée et renvoyer une valeur unique (pour les fonctions scalaires) ou un ensemble de valeurs (pour les fonctions de table). Cette propriété est disponible sur Amazon RDS for PostgreSQL, 'Db2, IBM Cloud Databases for PostgreSQL, 'IBM Db2 Big SQL, IBM 'Db2 on Cloud, IBM 'Db2 for i' , IBM 'Db2 Warehouse, IBM 'Db2 for z/OS, PostgreSQL, 'Snowflake, et Teradata. Dans IBM " Db2 for z/OS, les fonctions définies par l'utilisateur doivent être appelées dans le cadre d'une instruction SELECT. Dans Teradata, des fonctions de type macro sont également disponibles.

Les fonctions définies par l'utilisateur prennent en charge les modes source et transformation. Pour le mode Source, les données sont lues à partir de la base de données. En mode transformation, les données sont lues, transformées et écrites dans le nœud cible.

Vous pouvez utiliser deux types de fonctions définies par l'utilisateur :

Scalaire

Renvoie une seule valeur.
Tableau

Renvoie un tableau.

Lorsqu'une fonction n'a pas de paramètres d'entrée, elle peut être exécutée lorsque la base de données sert de source. Lorsqu'une fonction a des paramètres d'entrée, elle peut être exécutée lorsque la base de données agit comme un transformateur.

Utilisez les instructions de procédure suivantes pour vos fonctions définies par l'utilisateur :

Pour une fonction scalaire définie par l'utilisateur :
```
SELECT  “Sclr_udf_name”(@arg1,@arg2,…);
```
Pour la fonction définie par l'utilisateur du tableau :
```
SELECT * FROM  “table_udf_name”(@arg1,@arg2,…);
```