Exemple de procédure stockée Oracle

Une procédure stockée est un programme de base de données précompilé qui contient un ensemble d'instructions SQL et d'instructions de contrôle qui peuvent être appelées en cas de besoin. Cet article présentera les connaissances de base et des exemples de procédures stockées de base de données Oracle.

1. Notions de base des procédures stockées

1.1 Avantages des procédures stockées

Les procédures stockées sont une méthode efficace pour améliorer les performances des bases de données. Ils améliorent l'efficacité des interactions des applications avec la base de données car les instructions SQL sont précompilées côté base de données, ce qui leur permet d'effectuer les opérations plus rapidement lorsqu'elles sont appelées. Cela augmente également la sécurité des données car les procédures stockées peuvent effectuer des vérifications d'autorisation avant de créer et de modifier des données dans la base de données.

1.2 Création de procédures stockées

Vous pouvez utiliser les outils de développement Oracle SQL pour créer des procédures stockées. Oracle SQL Developer et SQL Plus sont des outils couramment utilisés.

Ce qui suit est la syntaxe de base pour créer une procédure stockée :

CREATE [OR REPLACE] PROCEDURE nom_procédure
([nom_paramètre IN/OUT type de données [, nom_paramètre IN/OUT type de données …]])
IS
BEGIN
instruction(s );
EXCEPTION
exception_handler;
END;

Parmi eux, les paramètres sont facultatifs, et la commande '[OR REPLACE]' peut préciser que l'application doit exister et conserver l'état de la procédure stockée.

1.3 Paramètres d'entrée et de sortie des procédures stockées

Les procédures stockées peuvent accepter des paramètres d'entrée et des paramètres de sortie. Les paramètres d'entrée peuvent être utilisés pour effectuer des opérations conditionnelles dans une procédure stockée ou pour transmettre des données à une procédure stockée. Les paramètres de sortie sont utilisés pour renvoyer des informations telles que des valeurs ou des valeurs spécifiées dans le processus de sortie.

Voici comment certains paramètres interagissent :

IN : Les paramètres d'entrée sont utilisés pour transmettre des valeurs aux procédures stockées.

OUT : les paramètres de sortie ne sont pas utilisés pour les données d'entrée, mais peuvent renvoyer des valeurs via des procédures stockées.

INOUT : Les paramètres d'entrée/sortie permettent de passer une valeur en paramètre et peuvent être modifiés via la valeur de retour d'exécution de la procédure stockée.

1.4 Gestion des exceptions des procédures stockées

Les procédures stockées peuvent gérer des exceptions comme les fonctions. Lorsqu'une erreur se produit dans la procédure stockée, vous pouvez configurer une gestion des exceptions. Il peut implémenter la gestion des messages d'erreur personnalisés et utiliser des comportements spécifiés pour soumettre ces erreurs lorsque des erreurs se produisent.

Voici la syntaxe de base pour créer la gestion des exceptions :

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
instruction(s);
EXCEPTION

  WHEN exception_name THEN
     statement(s);

END;

2. instance

Voici quelques exemples courants de procédures stockées :

2.1 Requête simple d'une procédure stockée

Ce qui suit est un exemple de procédure stockée simple, qui affichera les données qui remplissent les conditions du tableau :

CREATE OR REPLACE PROCEDURE get_emp_data
(
ID IN NUMBER,
NAME OUT VARCHAR2,
SALARY OUT NUMBER
)
IS
BEGIN
SELECT Employee_name,salary INTO NAME,SALARY FROM Employees WHERE Employee_id = ID;
END;

L'instance de procédure stockée ci-dessus a besoin à passer 2 paramètres : ID est un paramètre d'entrée obligatoire, qui définit l'ID de l'employé dont les informations doivent être interrogées ; tandis que le nom et le salaire sont des paramètres de sortie, qui acceptent respectivement les valeurs des colonnes correspondantes dans les résultats de la requête ; .

Récupérer la valeur du paramètre de sortie de la procédure stockée Vous pouvez appeler la procédure stockée comme une fonction :

DECLARE
emp_name VARCHAR2(20);
emp_salary NUMBER(10,2);
BEGIN
get_emp_data (100, emp_name,emp_salary);
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_salary);
END;

Dans le code ci-dessus, l'ID du paramètre de procédure stockée est défini à 100, le nom et le salaire de l'employé seront donc renvoyés.

2.2 Opération d'insertion d'une procédure stockée

Ce qui suit est un exemple de procédure stockée, qui implémente la fonction d'insertion d'une ligne de données dans la liste d'employés spécifiée :

CRÉER OU REMPLACER UNE PROCÉDURE add_employee
(
ID EN NUMÉRO,
NOM DANS VARCHAR2,
ÂGE EN NOMBRE,
SALAIRE EN NOMBRE
)
IS
BEGIN
INSÉRER DANS LES VALEURS des employés (ID,NOM,ÂGE,SALAIRE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employé ajouté.');
EXCEPTION
QUAND LES AUTRES PUIS

DBMS_OUTPUT.PUT_LINE('Error adding employee.');

FIN ;

L'instance de procédure stockée ci-dessus nécessite 4 paramètres d'entrée : identifiant de l'employé, nom de l'employé, âge de l'employé et salaire de l'employé, qui sont ensuite insérés dans la table "employés". Lorsque l'insertion est réussie, le message « Employé ajouté » s'affichera, et lorsque l'insertion échoue, le message « Erreur lors de l'ajout d'un employé » s'affichera.

2.3 Opération de mise à jour de la procédure stockée

L'exemple suivant fournit la fonction d'augmenter de 10 % le salaire d'un employé avec un ID spécifié dans la table des employés :

CRÉER OU REPLACER LA PROCÉDURE augmentation_employé_salaire
(
ID EN NUMÉRO
)
IS
CURSOR c_employee_salary IS

SELECT salary FROM employees WHERE employee_id = ID;

v_employee_salary NUMBER;
BEGIN
OPEN c_employee_salary;
FETCH c_employee_salary INTO v_employee_salary;
v_employee_salary := v_employee_salary * 1.1;
UPDATE employés SET salaire = v_employee _salary WHERE employé_id = ID;
COMMIT;
DBMS_OUTPUT .PUT_LINE('Le salaire a augmenté. ');
EXCEPTION
QUAND NO_DATA_FOUND ALORS

DBMS_OUTPUT.PUT_LINE('Employee not found.');

QUAND LES AUTRES PUIS

DBMS_OUTPUT.PUT_LINE('Error increasing salary.');

END;

L'exemple de procédure stockée ci-dessus nécessite 1 paramètre d'entrée : ID d'employé, il obtient le salaire de l'employé en fonction de l'ID d'employé, le multiplie par 1,1 et le met à jour dans la table. Une fois mis à jour correctement, le message « salaire augmenté » sera affiché ; lorsque l'employé ne peut pas être trouvé, le message « employé non trouvé » sera affiché ; lorsque d'autres erreurs se produisent, le message « erreur d'augmentation du salaire » sera affiché.

Résumé

Dans cet article, nous avons présenté les bases des procédures stockées de base de données Oracle et quelques exemples. Les procédures stockées peuvent améliorer les performances des bases de données et la sécurité des données, et sont très utiles pour les tâches qui doivent être effectuées fréquemment. Grâce à quelques exemples, vous pourrez mieux comprendre comment créer et utiliser des procédures stockées Oracle.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!