Oracle Procédures stockées et fonctions PL/SQL avec exemples

Que sont les sous-programmes PL/SQL ?

Ce tutoriel vous explique en détail comment créer et exécuter les blocs, procédures et fonctions nommés.

Les procédures et les fonctions sont des sous-programmes qui peuvent être créés et enregistrés dans la base de données en tant qu'objets de base de données. Elles peuvent également être appelées ou référencées à l'intérieur d'autres blocs.

Nous abordons également les principales différences entre ces deux sous-programmes et discutons des Oracle fonctions intégrées.

Terminologies dans les sous-programmes PL/SQL

Avant d'aborder les sous-programmes PL/SQL, nous allons examiner les différentes terminologies qui font partie de ces sous-programmes.

Paramètres

Un paramètre est une variable ou un espace réservé de toute valeur valide Type de données PL/SQL par lequel le sous-programme PL/SQL échange des valeurs avec le code principal. Ce paramètre permet l'entrée de données dans les sous-programmes et leur échange.tracextraction de valeurs à partir d'eux.

• Ces paramètres doivent être définis avec les sous-programmes au moment de la création.
• Ils sont inclus dans l'instruction d'appel pour interagir avec les sous-programmes.
• Le type de données du paramètre dans le sous-programme et dans l'instruction appelante doit être identique.
• La taille du type de données ne doit pas être mentionnée lors de la déclaration du paramètre, car cette taille est dynamique.

En fonction de leur finalité, les paramètres sont classés comme suit :

1. Dans le paramètre
2. Paramètre OUT
3. Paramètre IN OUT

Dans le paramètre

• Utilisé pour fournir des données d'entrée aux sous-programmes.
• Il s'agit d'une variable en lecture seule à l'intérieur des sous-programmes ; sa valeur ne peut pas être modifiée à l'intérieur du sous-programme.
• Dans l'instruction d'appel, il peut s'agir d'une variable, d'une valeur littérale ou d'une expression, telle que '5*8' ou 'a/b'.
• Par défaut, les paramètres sont de type IN.

Paramètre OUT

• Utilisé pour obtenir les résultats des sous-programmes.
• Il s'agit d'une variable en lecture-écriture à l'intérieur des sous-programmes ; sa valeur peut être modifiée à l'intérieur de ceux-ci.
• Dans l'instruction d'appel, il doit toujours s'agir d'une variable contenant la valeur provenant du sous-programme.

Paramètre IN OUT

• Utilisé à la fois pour fournir des données d'entrée et pour obtenir des données de sortie des sous-programmes.
• Il s'agit d'une variable en lecture-écriture à l'intérieur des sous-programmes ; sa valeur peut être modifiée à l'intérieur de ceux-ci.
• Dans l'instruction d'appel, il doit toujours s'agir d'une variable contenant la valeur provenant du sous-programme.

Le type de paramètre doit être mentionné lors de la création des sous-programmes.

RETOUR

Le mot-clé RETURN indique au compilateur de transférer l'exécution du sous-programme à l'instruction appelante. Dans un sous-programme, RETURN signifie simplement que l'exécution doit quitter le sous-programme ; une fois que le contrôleur rencontre RETURN, le code suivant est ignoré.

Normalement, le bloc parent ou principal appelle les sous-programmes, et le contrôle passe du bloc parent au sous-programme appelé. L'instruction RETURN dans le sous-programme renvoie le contrôle au bloc parent. Dans le cas des fonctions, l'instruction RETURN renvoie également une valeur, dont le type de données est spécifié lors de la déclaration de la fonction.

Qu'est-ce qu'une procédure en PL/SQL ?

A Procédure En PL/SQL, une sous-unité de programme (procédure) est un groupe d'instructions PL/SQL pouvant être appelées par leur nom. Chaque procédure possède un nom unique et est stockée dans la sous-unité de programme. Oracle base de données en tant qu'objet de base de données.

À noter: Un sous-programme est simplement une procédure, et il doit être créé manuellement selon les besoins. Une fois créé, il est stocké en tant qu'objet de base de données.

Les caractéristiques d'une unité de sous-programme de procédure en PL/SQL sont les suivantes :

• Les procédures sont des blocs autonomes qui peuvent être stockés dans le base de données.
• Ils peuvent être appelés par leur nom pour exécuter les instructions PL/SQL.
• Ils servent principalement à exécuter un processus.
• Ils peuvent contenir des blocs imbriqués, ou être imbriqués à l'intérieur d'autres blocs ou paquets.
• Ils contiennent une partie déclaration (facultative), une partie exécution et une partie gestion des exceptions (facultative).
• Les valeurs peuvent être transmises à une procédure ou récupérées depuis celle-ci via des paramètres.
• Ces paramètres doivent être inclus dans l'instruction appelante.
• Une procédure peut comporter une instruction RETURN pour rendre le contrôle au bloc appelant, mais elle ne peut pas renvoyer de valeur via RETURN.
• Les procédures ne peuvent pas être appelées directement à partir d'instructions SELECT ; elles peuvent être appelées à partir d'un autre bloc ou via le mot-clé EXEC.

Syntaxe

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• L'instruction CREATE PROCEDURE indique au compilateur de créer une nouvelle procédure. Le mot-clé OR REPLACE lui indique de remplacer la procédure existante (le cas échéant) par la procédure actuelle.
• Le nom de la procédure doit être unique.
• Le mot-clé « IS » est utilisé lorsque la procédure stockée est imbriquée dans un autre bloc. Si la procédure est autonome, on utilise « AS ». Hormis cette convention de codage, les deux mots-clés ont la même signification.

Exemple 1 : Création d'une procédure et appel de celle-ci à l'aide de EXEC. Dans cet exemple, nous créons un Oracle Procédure qui prend un nom en entrée et affiche un message de bienvenue en sortie, en utilisant la commande EXEC pour l'appeler.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Explication:

• Code ligne 1: Création de la procédure nommée 'welcome_msg' et comportant un paramètre 'p_name' de type 'IN'.
• Code ligne 4: Impression du message de bienvenue par concaténation du nom saisi.
• La procédure a été compilée avec succès.
• Code ligne 7: Appel de la procédure à l'aide de EXEC avec le paramètre 'Guru99'. La procédure s'exécute et imprime « Bienvenue » Guru99 ".

Qu'est-ce qu'une fonction ?

Une fonction est un sous-programme PL/SQL autonome. À l'instar d'une procédure, une fonction possède un nom unique et est stockée en tant qu'objet de base de données PL/SQL. Ses caractéristiques sont les suivantes :

• Les fonctions sont des blocs autonomes utilisés principalement pour les calculs.
• Une fonction utilise le mot-clé RETURN pour renvoyer une valeur dont le type de données est défini au moment de sa création.
• Une fonction doit soit renvoyer une valeur, soit lever une exception ; l’instruction `return` est obligatoire dans les fonctions.
• Une fonction sans instructions DML peut être appelée directement dans une requête SELECT, tandis qu'une fonction avec instructions DML ne peut être appelée que depuis d'autres blocs PL/SQL.
• Il peut contenir des blocs imbriqués, ou être imbriqué à l'intérieur d'autres blocs ou paquets.
• Il contient une partie déclaration (facultative), une partie exécution et une partie gestion des exceptions (facultative).
• Les valeurs peuvent être transmises à la fonction ou récupérées depuis celle-ci via des paramètres.
• Ces paramètres doivent être inclus dans l'instruction appelante.
• Une fonction peut également renvoyer une valeur via les paramètres OUT en plus de l'utilisation de RETURN.
• Puisqu'elle renvoie toujours une valeur, l'instruction appelante utilise toujours un opérateur d'affectation pour remplir une variable.

Syntaxe

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• L'instruction CREATE FUNCTION indique au compilateur de créer une nouvelle fonction. L'instruction OR REPLACE indique de remplacer la fonction existante (le cas échéant) par la fonction actuelle.
• Le nom de la fonction doit être unique.
• Le type de données RETURN doit être mentionné.
• Le mot-clé « IS » est utilisé lorsque la fonction est imbriquée dans un autre bloc. Si la fonction est autonome, on utilise « AS ».

Exemple 1 : Création d’une fonction et appel de celle-ci à l’aide d’un bloc anonyme. Dans ce programme, nous créons une fonction qui prend un nom en entrée et renvoie un message de bienvenue, en utilisant un bloc anonyme et une instruction SELECT pour l'appeler.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Explication:

• Code ligne 1: Création de la fonction nommée 'welcome_msg_func' et comportant un paramètre 'p_name' de type 'IN'.
• Code ligne 2: Déclaration du type de retour comme VARCHAR2.
• Code ligne 5: Renvoie la valeur concaténée 'Bienvenue' et la valeur du paramètre.
• Code ligne 8: Bloc anonyme pour appeler la fonction ci-dessus.
• Code ligne 9: Déclarer la variable avec le même type de données que le type de retour de la fonction.
• Code ligne 11: Appel de la fonction et affectation de la valeur de retour à la variable 'lv_msg'.
• Code ligne 12: Affichage de la valeur de la variable. Le résultat est « Bienvenue » Guru99 ".
• Code ligne 14: Appel de la même fonction via une instruction SELECT. La valeur de retour est dirigée vers la sortie standard.

Similitudes entre une procédure et une fonction

• Les deux peuvent être appelés depuis d’autres blocs PL/SQL.
• Si une exception levée dans le sous-programme n'est pas gérée dans son gestion des exceptions section, elle se propage au bloc appelant.
• Les deux peuvent avoir autant de paramètres que nécessaire.
• Les deux sont traités comme des objets de base de données en PL/SQL.

Procédure vs Fonction : Différences clés

Fonctions intégrées dans PL/SQL

PL / SQL Ce module contient diverses fonctions intégrées permettant de manipuler les types de données chaîne de caractères et date. Nous présentons ici les fonctions les plus couramment utilisées et leur mode d'emploi.

Fonctions de conversion

Ces fonctions intégrées convertissent un type de données en un autre.

Fonctions de chaîne

Ces fonctions sont utilisées sur le type de données caractère.

Fonctions de date

Ces fonctions servent à manipuler les dates.

FAQ