Dans ce tutoriel, vous allez voir la description détaillée sur 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 couvrirons les différences majeures entre ces deux sous-programmes. Nous allons également discuter des fonctions intégrées d'Oracle.
Dans ce didacticiel Oracle Stored Procedure, vous apprendrez:
- Terminologies dans les sous-programmes PL / SQL
- Qu'est-ce que la procédure en PL / SQL?
- Qu'est-ce que la fonction?
- Similitudes entre procédure et fonction
- Procédure Vs. Fonction: Différences clés
- Fonctions intégrées dans PL / SQL
Terminologies dans les sous-programmes PL / SQL
Avant d'en apprendre davantage sur les sous-programmes PL / SQL, nous discuterons des différentes terminologies qui font partie de ces sous-programmes. Voici les terminologies que nous allons discuter.
Paramètre:
Le paramètre est une variable ou un espace réservé de tout type de données PL / SQL valide à travers lequel le sous-programme PL / SQL échange les valeurs avec le code principal. Ce paramètre permet de donner des entrées 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.
Sur la base de leur objectif, les paramètres sont classés comme
- Paramètre IN
- Paramètre OUT
- Paramètre IN OUT
Paramètre IN:
- 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 à l'intérieur du sous-programme.
- Dans l'instruction appelante, ces paramètres peuvent être une variable ou une valeur littérale ou une expression, par exemple, cela pourrait être l'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.
- C'est une variable en lecture-écriture à l'intérieur des sous-programmes. Leurs valeurs peuvent être modifiées à l'intérieur des sous-programmes.
- Dans l'instruction d'appel, 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 une entrée et pour obtenir la sortie des sous-programmes.
- C'est une variable en lecture-écriture à l'intérieur des sous-programmes. Leurs valeurs peuvent être modifiées à l'intérieur des sous-programmes.
- Dans l'instruction d'appel, 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.
REVENIR
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 la commande 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 la commande passera de ces blocs parents 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.
Qu'est-ce que la procédure en PL / SQL?
Une procédure en PL / SQL est une unité de sous-programme qui se compose d'un groupe d'instructions PL / SQL qui peuvent être appelées par leur nom. Chaque procédure en PL / SQL a son propre nom unique par lequel elle peut être référencée et appelée. Cette unité de sous-programme dans la base de données Oracle est stockée en tant qu'objet de base de données.
Remarque: le sous- programme n'est rien d'autre qu'une procédure, et il doit être créé manuellement selon l'exigence. Une fois créés, ils seront stockés en tant qu'objets de base de données.
Voici 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 la base de données.
- L'appel à ces procédures PLSQL peut être fait en se référant à 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 (facultative), une partie exécution, une partie gestion des exceptions (facultative).
- Les valeurs peuvent être passées dans la procédure Oracle ou extraites de la procédure via des paramètres.
- Ces paramètres doivent être inclus dans l'instruction d'appel.
- 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( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE indique au compilateur de créer une nouvelle procédure dans Oracle. Le mot-clé 'OR REPLACE' indique à 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ée dans d'autres blocs. Si la procédure est autonome, alors «AS» sera utilisé. Hormis cette norme de codage, les deux ont la même signification.
Exemple 1: Créer une procédure et l'appeler à l'aide de EXEC
Dans cet exemple, nous allons créer une procédure Oracle 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)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Explication du code:
- Ligne de code 1 : Création de la procédure avec le nom 'welcome_msg' et avec un paramètre 'p_name' de type 'IN'.
- Ligne de code 4 : impression du message d'accueil en concaténant le nom d'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 la forme "Welcome Guru99".
Qu'est-ce que la fonction?
Functions est un sous-programme PL / SQL autonome. Comme la procédure 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. Voici quelques-unes des caractéristiques des fonctions.
- Les fonctions sont un bloc autonome qui est principalement utilisé à des fins de calcul.
- La fonction utilise le mot-clé RETURN pour renvoyer la valeur, et le type de données de celui-ci est défini au moment de la création.
- Une fonction doit renvoyer une valeur ou lever l'exception, c'est-à-dire que le retour est obligatoire dans les fonctions.
- La fonction sans instruction DML peut être directement appelée dans la 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 (facultative), une partie exécution, une partie gestion des exceptions (facultative).
- Les valeurs peuvent être passées dans la fonction ou extraites de la procédure via les paramètres.
- Ces paramètres doivent être inclus dans l'instruction d'appel.
- Une fonction PLSQL peut également renvoyer la valeur via des paramètres OUT autres que l'utilisation de RETURN.
- Comme il retournera toujours la valeur, dans l'instruction d'appel, il accompagne toujours l'opérateur d'affectation pour remplir les variables.
Syntaxe
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION demande au compilateur de créer une nouvelle fonction. Le mot clé 'OR REPLACE' indique 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 sera imbriquée dans d'autres blocs. Si la procédure est autonome, alors «AS» sera utilisé. Hormis cette norme de codage, les deux ont la même signification.
Exemple 1: Créer une fonction et l'appeler à 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.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 de la fonction Oracle 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 : retour de la valeur concaténée 'Welcome' et de 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 même type de données que le type de données de retour de la fonction.
- Ligne de code 11 : Appel de la fonction et remplissage de la valeur de retour dans la variable 'lv_msg'.
- Ligne de code 12 : impression de la valeur de la variable. Le résultat que vous obtiendrez ici est "Welcome Guru99"
- Ligne de code 14 : Appel de la même fonction via l'instruction SELECT. La valeur de retour est dirigée directement vers la sortie standard.
Similitudes entre procédure et fonction
- Les deux peuvent être appelés à partir d'autres blocs PL / SQL.
- Si l'exception déclenchée dans le sous-programme n'est pas traitée dans la section de gestion des exceptions de sous-programme, 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
| Procédure | Fonction |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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 | Usage | Exemple |
|---|---|---|
| TO_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: 15/01/2015 |
| TO_NUMBER (texte, format) | Convertit le texte en type numérique du format donné. Informat '9' indique le nombre de chiffres | Sélectionnez TO_NUMBER ('1234', '9999') de dual; Sortie: 1234 Sélectionnez TO_NUMBER ('1,234,45', '9,999.99') à partir de dual; Sortie: 1234 |
Fonctions de chaîne
Ce sont les fonctions qui sont utilisées sur le type de données caractère.
| Nom de la fonction | Usage | Exemple |
|---|---|---|
| INSTR (texte, chaîne, début, occurrence) | Donne la position d'un texte particulier dans la chaîne donnée.
| Sélectionnez INSTR ('AIRPLANE', 'E', 2,1) à partir de la double sortie : 2 Sélectionnez INSTR ('AIRPLANE', 'E', 2,2) à partir de la double sortie: 9 (2 ème occurrence de E) |
| SUBSTR (texte, début, longueur) | Donne la valeur de sous-chaîne de la chaîne principale.
| sélectionnez substr ('avion', 1,7) à partir de la sortie double : aeropla |
| UPPER (texte) | Renvoie les majuscules du texte fourni | Sélectionnez supérieur ('guru99') de dual; Sortie : GURU99 |
| INFÉRIEUR (texte) | Renvoie la minuscule du texte fourni | Sélectionnez inférieur ('avion') à partir du double; Sortie : avion |
| INITCAP (texte) | Renvoie le texte donné avec la lettre de départ en majuscules. | Sélectionnez ('guru99') à partir de la double sortie : Guru99 Sélectionnez ('mon histoire') de la double sortie : Mon histoire |
| LONGUEUR (texte) | Renvoie la longueur de la chaîne donnée | Sélectionnez LENGTH ('guru99') de dual; Sortie : 6 |
| LPAD (texte, longueur, pad_char) | Remplit la chaîne dans le côté gauche pour la longueur donnée (chaîne totale) avec le caractère donné | Sélectionnez LPAD ('guru99', 10, '$') de dual; Sortie : $$$$ guru99 |
| 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, '-') à partir de la sortie double : guru99 ---- |
| LTRIM (texte) | Coupe l'espace blanc principal du texte | Sélectionnez LTRIM ('Guru99') de dual; Sortie : Guru99 |
| RTRIM (texte) | Coupe l'espace blanc de fin du texte | Sélectionnez RTRIM ('Guru99') de dual; Sortie ; Guru99 |
Fonctions de date
Ce sont des fonctions utilisées pour manipuler les dates.
| Nom de la fonction | Usage | Exemple |
|---|---|---|
| ADD_MONTHS (date, nombre de mois) | Ajoute les mois donnés à la date | ADD_MONTH ('2015-01-01', 5); Sortie : 01/05/2015 |
| SYSDATE | Renvoie la date et l'heure actuelles du serveur | Sélectionnez SYSDATE de dual; Sortie : 10/4/2015 14:11:43 PM |
| TRONQUE | Arrondir la variable de date à la valeur la plus basse possible | sélectionnez sysdate, TRUNC (sysdate) de dual; Sortie : 04/10/2015 14:12:39 04/10/2015 |
| ROND | Arrondit la date à la limite la plus proche supérieure ou inférieure | Sélectionnez sysdate, ROUND (sysdate) à partir de la double sortie : 10/4/2015 2:14:34 PM 10/5/2015 |
| MONTHS_BETWEEN | Renvoie le nombre de mois entre deux dates | Sélectionnez MONTHS_BETWEEN (sysdate + 60, sysdate) à partir de la sortie double : 2 |
Résumé
Dans ce chapitre, nous avons appris ce qui suit.
- Comment créer une procédure et différentes manières 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 de RETURN dans les sous-programmes PL / SQL
- Fonctions intégrées courantes dans Oracle PL / SQL