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

Dans ce didacticiel, vous allez voir la description détaillée de la façon de créer et d'exécuter les blocs nommés (procédures et fonctions).

Les procédures et fonctions sont les 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. Ils peuvent également être appelés ou référencés à l’intérieur des autres blocs.

En dehors de cela, nous aborderons les principales différences entre ces deux sous-programmes. Nous allons également discuter du Oracle fonctions intégrées.

Terminologies dans les sous-programmes PL/SQL

Avant d'en apprendre davantage sur les sous-programmes PL/SQL, nous aborderons les différentes terminologies qui font partie de ces sous-programmes. Vous trouverez ci-dessous les terminologies dont nous allons discuter.

Paramètres

Le paramètre est une variable ou un espace réservé de tout élément valide Type de données PL/SQL à travers lequel le sous-programme PL/SQL échange les valeurs avec le code principal. Ce paramètre permet de donner des informations aux sous-programmes et d'extraire de ces sous-programmes.

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

En fonction de leur objectif, les paramètres sont classés comme

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

Dans le paramètre

• Ce paramètre est utilisé pour donner une entrée aux sous-programmes.
• C'est une variable en lecture seule à l'intérieur des sous-programmes. Leurs valeurs ne peuvent pas être modifiées dans le sous-programme.
• Dans l'instruction appelante, ces paramètres peuvent être une variable, une valeur littérale ou une expression, par exemple, il peut s'agir d'une expression arithmétique comme « 5*8 » ou « a/b » où « a » et « b » sont des variables. .
• Par défaut, les paramètres sont de type IN.

Paramètre OUT

• Ce paramètre est utilisé pour obtenir la sortie des sous-programmes.
• Il s'agit d'une variable en lecture-écriture à l'intérieur des sous-programmes. Leurs valeurs peuvent être modifiées dans les sous-programmes.
• Dans l'instruction appelante, ces paramètres doivent toujours être une variable contenant la valeur des sous-programmes actuels.

Paramètre IN OUT

• Ce paramètre est utilisé à la fois pour donner des entrées et pour obtenir des sorties des sous-programmes.
• Il s'agit d'une variable en lecture-écriture à l'intérieur des sous-programmes. Leurs valeurs peuvent être modifiées dans les sous-programmes.
• Dans l'instruction appelante, ces paramètres doivent toujours être une variable contenant la valeur des sous-programmes.

Ces types de paramètres doivent être mentionnés au moment de la création des sous-programmes.

RETOUR

RETURN est le mot-clé qui demande au compilateur de basculer le contrôle du sous-programme vers l'instruction appelante. Dans le sous-programme, RETURN signifie simplement que le contrôle doit quitter le sous-programme. Une fois que le contrôleur a trouvé le mot-clé RETURN dans le sous-programme, le code suivant sera ignoré.

Normalement, le bloc parent ou principal appellera les sous-programmes, puis le contrôle passera de ce bloc parent aux sous-programmes appelés. RETURN dans le sous-programme ramènera le contrôle à son bloc parent. Dans le cas des fonctions, l'instruction RETURN renvoie également la valeur. Le type de données de cette valeur est toujours mentionné au moment de la déclaration de la fonction. Le type de données peut être de n’importe quel type de données PL/SQL valide.

Remarque: Le sous-programme n'est rien d'autre qu'une procédure et il doit être créé manuellement selon les exigences. Une fois créés, ils seront stockés en tant qu'objets de base de données.

Vous trouverez ci-dessous les caractéristiques de l'unité de sous-programme Procédure en PL/SQL :

• Les procédures sont des blocs autonomes d'un programme qui peuvent être stockés dans le base de données.
• L'appel à ces procédures PLSQL peut être effectué en faisant référence à leur nom, pour exécuter les instructions PL/SQL.
• Il est principalement utilisé pour exécuter un processus en PL/SQL.
• Il peut avoir des blocs imbriqués, ou il peut être défini et imbriqué dans les autres blocs ou packages.
• Il contient une partie déclaration (facultatif), une partie exécution, une partie gestion des exceptions (facultatif).
• Les valeurs peuvent être transmises dans Oracle procédure ou extrait de la procédure via des paramètres.
• Ces paramètres doivent être inclus dans l'instruction appelante.
• Une procédure en SQL peut avoir une instruction RETURN pour renvoyer le contrôle au bloc appelant, mais elle ne peut renvoyer aucune valeur via l'instruction RETURN.
• Les procédures ne peuvent pas être appelées directement à partir des instructions SELECT. Ils peuvent être appelés depuis un autre bloc ou via le mot-clé EXEC.

Syntaxe

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

• CREATE PROCEDURE demande au compilateur de créer une nouvelle procédure dans Oracle. Le mot clé « OR REPLACE » demande à la compilation 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' sera utilisé lorsque la procédure stockée dans Oracle est imbriqué dans d’autres blocs. Si la procédure est autonome, « AS » sera utilisé. Hormis cette norme de codage, les deux ont la même signification.

Exemple 1 : création d'une procédure et appel à l'aide d'EXEC

Dans cet exemple, nous allons créer un Oracle procédure qui prend le nom en entrée et imprime le message de bienvenue en sortie. Nous allons utiliser la commande EXEC pour appeler la procédure.

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

Explication du code :

• Ligne de code 1: Création de la procédure de nom 'welcome_msg' et d'un paramètre 'p_name' de type 'IN'.
• Ligne de code 4: Impression du message de bienvenue en concaténant le nom de l'entrée.
• La procédure est compilée avec succès.
• Ligne de code 7: Appel de la procédure à l'aide de la commande EXEC avec le paramètre 'Guru99'. La procédure est exécutée et le message est imprimé sous le titre « Bienvenue Guru99 ».

Qu'est-ce que la fonction ?

Functions est un sous-programme PL/SQL autonome. Comme les procédures PL/SQL, les fonctions ont un nom unique par lequel elles peuvent être référencées. Ceux-ci sont stockés sous forme d’objets de base de données PL/SQL. Vous trouverez ci-dessous quelques-unes des caractéristiques des fonctions.

• Les fonctions sont un bloc autonome principalement utilisé à des fins de calcul.
• La fonction utilise le mot-clé RETURN pour renvoyer la valeur, et le type de données de celle-ci est défini au moment de la création.
• Une fonction doit soit renvoyer une valeur, soit déclencher l'exception, c'est-à-dire que le retour est obligatoire dans les fonctions.
• La fonction sans instruction DML peut être directement appelée dans une requête SELECT alors que la fonction avec opération DML ne peut être appelée qu'à partir d'autres blocs PL/SQL.
• Il peut avoir des blocs imbriqués, ou il peut être défini et imbriqué dans les autres blocs ou packages.
• Il contient une partie déclaration (facultatif), une partie exécution, une partie gestion des exceptions (facultatif).
• Les valeurs peuvent être transmises à la fonction ou extraites de la procédure via les paramètres.
• Ces paramètres doivent être inclus dans l'instruction appelante.
• Une fonction PLSQL peut également renvoyer la valeur via des paramètres OUT autres que l'utilisation de RETURN.
• Puisqu'il renverra toujours la valeur, dans l'instruction appelante, il sera toujours accompagné d'un opérateur d'affectation pour remplir les variables.

Syntaxe

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

• CREATE FUNCTION demande au compilateur de créer une nouvelle fonction. Le mot clé « OR REPLACE » demande au compilateur 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 » sera utilisé lorsque la procédure est imbriquée dans d'autres blocs. Si la procédure est autonome, « AS » sera utilisé. Hormis cette norme de codage, les deux ont la même signification.

Exemple 1 : création d'une fonction et appel à l'aide d'un bloc anonyme

Dans ce programme, nous allons créer une fonction qui prend le nom en entrée et renvoie le message de bienvenue en sortie. Nous allons utiliser un bloc anonyme et une instruction select pour appeler la fonction.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Explication du code :

• Ligne de code 1: Création du Oracle fonction avec le nom 'welcome_msg_func' et avec un paramètre 'p_name' de type 'IN'.
• Ligne de code 2: déclarer le type de retour comme VARCHAR2
• Ligne de code 5: Renvoie la valeur concaténée 'Bienvenue' et la valeur du paramètre.
• Ligne de code 8: Bloc anonyme pour appeler la fonction ci-dessus.
• Ligne de code 9: Déclaration de la variable avec le type de données identique au type de données de retour de la fonction.
• Ligne de code 11: Appel de la fonction et remplissage de la valeur de retour à la variable 'lv_msg'.
• Ligne de code 12: Impression de la valeur de la variable. Le résultat que vous obtiendrez ici est « Bienvenue Guru99 »
• Ligne de code 14: Appel de la même fonction via l'instruction SELECT. La valeur de retour est directement dirigée vers la sortie standard.

Similitudes entre procédure et fonction

• Les deux peuvent être appelés depuis d’autres blocs PL/SQL.
• Si l'exception déclenchée dans le sous-programme n'est pas gérée dans le sous-programme gestion des exceptions section, puis il se propagera 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 contre. Fonction : différences clés

Procédure	Fonction
Utilisé principalement pour exécuter certains processus	Utilisé principalement pour effectuer certains calculs
Impossible d'appeler dans l'instruction SELECT	Une fonction qui ne contient aucune instruction DML peut être appelée dans l'instruction SELECT
Utilisez le paramètre OUT pour renvoyer la valeur	Utilisez RETURN pour renvoyer la valeur
Il n'est pas obligatoire de renvoyer la valeur	Il est obligatoire de renvoyer la valeur
RETURN quittera simplement le contrôle du sous-programme.	RETURN quittera le contrôle du sous-programme et renvoie également la valeur
Le type de données de retour ne sera pas spécifié au moment de la création	Le type de données de retour est obligatoire au moment de la création

Fonctions intégrées dans PL/SQL

PL / SQL contient diverses fonctions intégrées pour travailler avec des chaînes et des types de données de date. Ici, nous allons voir les fonctions couramment utilisées et leur utilisation.

Fonctions de conversion

Ces fonctions intégrées sont utilisées pour convertir un type de données en un autre type de données.

Nom de la fonction	Utilisation	Exemple
À_CHAR	Convertit l'autre type de données en type de données caractère	TO_CHAR(123);
TO_DATE ( chaîne, format )	Convertit la chaîne donnée en date. La chaîne doit correspondre au format.	TO_DATE('2015-JAN-15', 'YYYY-MON-DD'); Sortie: 1 / 15 / 2015
TO_NUMBER (texte, format)	Convertit le texte en type numérique du format donné. L'information '9' indique le nombre de chiffres	Sélectionnez TO_NUMBER('1234′,'9999') dans dual ; Sortie1234 Sélectionnez TO_NUMBER('1,234.45′,'9,999.99') dans dual ; Sortie1234

Fonctions de chaîne

Ce sont les fonctions qui sont utilisées sur le type de données caractère.

Nom de la fonction	Utilisation	Exemple
INSTR(texte, chaîne, début, occurrence)	Donne la position d'un texte particulier dans la chaîne donnée. texte – Chaîne principale chaîne – texte qui doit être recherché start – position de départ de la recherche (facultatif) conformité – occurrence de la chaîne recherchée (facultatif)	Sélectionnez INSTR('AEROPLANE','E',2,1) dans le double Sortie2 Sélectionnez INSTR('AEROPLANE','E',2,2) dans le double Sortie: 9 (2nd apparition de E)
SUBSTR ( texte, début, longueur)	Donne la valeur de sous-chaîne de la chaîne principale. texte – chaîne principale départ – position de départ longueur – longueur à sous-chaîner	sélectionnez substr('avion',1,7) dans dual Sortie: aéronautique
SUPÉRIEUR (texte)	Renvoie la majuscule du texte fourni	Sélectionnez upper('guru99') parmi dual ; Sortie: GURU99
INFÉRIEUR (texte)	Renvoie la minuscule du texte fourni	Sélectionnez inférieur (« AerOpLane ») parmi dual ; Sortie: avion
INITCAP (texte)	Renvoie le texte donné avec la lettre de départ en majuscule.	Sélectionnez (« guru99 ») à partir du double Sortie: Gourou99 Sélectionnez (« mon histoire ») parmi les doubles Sortie: Mon histoire
LONGUEUR ( texte )	Renvoie la longueur de la chaîne donnée	Sélectionnez LONGUEUR (« guru99 ») dans dual ; Sortie6
LPAD (texte, longueur, pad_char)	Remplit la chaîne sur le côté gauche pour la longueur donnée (chaîne totale) avec le caractère donné	Sélectionnez LPAD('guru99', 10, '$') dans dual ; Sortie: $$$$gourou99
RPAD (texte, longueur, pad_char)	Remplit la chaîne du côté droit pour la longueur donnée (chaîne totale) avec le caractère donné	Sélectionnez RPAD('guru99′,10,'-') dans le double Sortie: gourou99—-
LTRIM (texte)	Supprime l'espace blanc de début du texte	Sélectionnez LTRIM(' Guru99') dans dual ; Sortie: Gourou99
RTRIM (texte)	Supprime l'espace blanc de fin du texte	Sélectionnez RTRIM('Guru99') dans dual ; Sortie; Gourou99

Fonctions de date

Ce sont des fonctions utilisées pour manipuler les dates.

Nom de la fonction	Utilisation	Exemple
ADD_MONTHS (date, nombre de mois)	Ajoute les mois donnés à la date	ADD_MONTH('2015-01-01',5); Sortie: 05 / 01 / 2015
SYSDATE	Renvoie la date et l'heure actuelles du serveur	Sélectionnez SYSDATE dans Dual ; Sortie: 10/4/2015 2:11:43
TRUNC	Arrondir la variable date à la valeur la plus basse possible	sélectionnez sysdate, TRUNC(sysdate) dans dual ; Sortie: 10/4/2015 2:12:39 10/4/2015
ROUND	Arrondit la date à la limite la plus proche, supérieure ou inférieure	Sélectionnez sysdate, ROUND(sysdate) à partir de dual Sortie: 10/4/2015 2:14:34 10/5/2015
MOIS_BETWEEN	Renvoie le nombre de mois entre deux dates	Sélectionnez MONTHS_BETWEEN (sysdate+60, sysdate) à partir du double Sortie2

Résumé

Dans ce chapitre, nous avons appris ce qui suit.

• Comment créer une procédure et différentes façons de l'appeler
• Comment créer une fonction et différentes façons de l'appeler
• Similitudes et différences entre la procédure et la fonction
• Paramètres et terminologies courantes RETURN dans les sous-programmes PL/SQL
• Fonctions intégrées communes dans Oracle PL / SQL