Exemples de création et d'exécution de procédures stockées dans Oracle

Oracle est un système de gestion de base de données très puissant qui possède de nombreuses fonctions et fonctionnalités avancées, parmi lesquelles les procédures stockées en font partie. Une procédure stockée est un ensemble d'instructions SQL prédéfinies pour les opérations de base de données qui peuvent être stockées dans la base de données pour une utilisation ultérieure par appel.

Chez Oracle, les procédures stockées sont écrites en PL/SQL, un langage qui combine SQL et programmation. PL/SQL possède de solides capacités de manipulation de données et de contrôle de processus, et peut facilement écrire des procédures stockées efficaces.

Avantages des procédures stockées

Le principal avantage des procédures stockées est qu'elles peuvent augmenter l'efficacité d'exécution de la base de données et réduire la surcharge de communication réseau. Étant donné que la procédure stockée a été précompilée et optimisée, il n'est pas nécessaire de l'analyser et de l'optimiser à plusieurs reprises pendant l'exécution et peut être directement appelée pour l'exécution. De plus, les procédures stockées peuvent également implémenter des opérations dynamiques via des paramètres, ce qui non seulement simplifie le code mais évite également les risques tels que l'injection SQL.

Création et exécution de procédures stockées

Ce qui suit décrit comment créer et exécuter des procédures stockées dans Oracle.

Créer une procédure stockée

Dans Oracle, la création d'une procédure stockée nécessite l'utilisation de l'instruction CREATE PROCEDURE. La syntaxe est la suivante :

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
      pl/sql_code_block;
END [procedure_name];

Parmi elles :

• CREATE PROCEDURE : L'instruction pour créer une procédure stockée.
• OR REPLACE : Paramètre facultatif Si ce paramètre est spécifié, cela signifie que la procédure stockée créée sera remplacée si elle existe déjà.
• procedure_name : Le nom de la procédure stockée.
• parameter_name : paramètres d'entrée et/ou de sortie facultatifs utilisés pour spécifier l'entrée et la sortie de la procédure stockée.
• parameter_type : Le type de paramètre, qui peut être un type de données tel que VARCHAR2, NUMBER, ou un type de curseur, tel que SYS_REFCURSOR.
• IS | AS : Paramètre facultatif, utilisé pour préciser le type de langage de la procédure stockée, IS représente le début (bloc PL/SQL), AS représente la fin (bloc PL/SQL).
• pl/sql_code_block : bloc de code PL/SQL, qui contient l'implémentation logique spécifique de la procédure stockée.

L'exemple de code suivant montre comment créer une procédure stockée simple qui accepte deux paramètres et génère leur somme :

CREATE OR REPLACE PROCEDURE add_nums(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
IS
BEGIN
    sum := num1 + num2;
END add_nums;

Exécution d'une procédure stockée

Dans Oracle, l'exécution d'une procédure stockée nécessite l'utilisation de l'instruction EXECUTE ou EXECUTE IMMEDIATE . Par exemple, pour exécuter l'exemple de programme ci-dessus, vous pouvez utiliser l'instruction suivante :

DECLARE
    result NUMBER;
BEGIN
    add_nums(10, 20, result);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || result);
END;

Ici, nous utilisons l'instruction DECLARE pour déclarer le résultat de la variable qui doit être utilisée, appeler la procédure stockée add_nums et afficher le résultat à l'écran. .

Type de paramètre

Dans une procédure stockée, les paramètres peuvent être des paramètres d'entrée, des paramètres de sortie ou des paramètres bidirectionnels.

• Paramètres d'entrée : Spécifiez l'entrée de la procédure stockée.
• Paramètres de sortie : spécifiez la sortie de la procédure stockée.
• Paramètres bidirectionnels : peuvent être une entrée ou une sortie.

La méthode de déclaration des types de paramètres est la suivante :

(param_name [IN | OUT | IN OUT] param_type [, ...])

Dans cette déclaration, [IN | OUT | IN OUT] est un paramètre facultatif utilisé pour spécifier le type du paramètre. Si le type de paramètre n'est pas spécifié, la valeur par défaut est le type IN, c'est-à-dire le paramètre d'entrée.

Exemple de code :

CREATE OR REPLACE PROCEDURE my_proc (
    num IN NUMBER,
    str IN OUT VARCHAR2,
    cur OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 逻辑实现
END my_proc;

Dans le code ci-dessus, nous déclarons une procédure stockée my_proc qui contient trois paramètres. Le premier paramètre num est le paramètre d'entrée, le deuxième paramètre str est le paramètre bidirectionnel et le troisième paramètre cur est les paramètres de sortie. .

Traitement des jeux d'enregistrements

Lors de l'utilisation de procédures stockées pour exploiter des données, il est souvent nécessaire de renvoyer une liste de résultats de requête. Oracle propose deux types de jeux d'enregistrements : les curseurs et les tables PL/SQL.

Curseur

Un curseur est une structure de données qui renvoie un ensemble de résultats, qui peut parcourir les résultats de la requête. Les curseurs peuvent être explicites ou implicites. Les curseurs explicites nécessitent la déclaration d'une variable de curseur et son ouverture et sa fermeture dans le code. Les curseurs implicites sont automatiquement créés et gérés par Oracle.

Voici une procédure stockée qui montre comment utiliser un curseur :

CREATE OR REPLACE PROCEDURE get_employee(
    id_list IN VARCHAR2,
    emp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_cur FOR 'SELECT * FROM employees WHERE id IN (' || id_list || ')';
END get_employee;

Dans cet exemple, nous déclarons une procédure stockée get_employee avec deux paramètres, qui accepte une liste d'ID d'employé séparés par des virgules comme paramètres d'entrée et renvoie un curseur emp_cur contenant les informations sur l'employé sélectionné.

Table PL/SQL

La table PL/SQL est une structure de données de type tableau qui peut stocker un ensemble de valeurs. Les tables PL/SQL ont de nombreuses applications pratiques dans les procédures stockées, comme transmettre un ensemble de données à une procédure stockée, etc.

Dans Oracle, les tables PL/SQL peuvent être déclarées et utilisées dans des procédures stockées, comme le code suivant :

CREATE OR REPLACE PACKAGE my_package
IS
    TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER);
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER)
    IS
        total NUMBER := 0;
    BEGIN
        FOR indx IN 1 .. nums.COUNT LOOP
            total := total + nums(indx);
        END LOOP;
        sum := total;
    END sum_nums;
END my_package;

Ici, nous avons créé un package nommé my_package, qui déclare un PL/SQL nommé num_list Le type de table et un procédure stockée sum_nums qui utilise ce type. sum_nums accepte un argument de type num_list et calcule leur somme.

Conclusion

Dans Oracle, les procédures stockées sont l'un des outils importants pour la maintenance des bases de données. Elles ont des capacités d'exécution et une dynamique efficaces. Nous pouvons également utiliser des procédures stockées pour lui permettre d'exécuter une certaine logique métier au lieu de simplement exécuter une seule instruction SQL, ce qui peut améliorer la réutilisabilité et la maintenabilité. Parce qu'ils peuvent être stockés dans une base de données, partagés et accessibles par plusieurs applications ou processus. L'utilisation de procédures stockées présente de nombreux avantages, et il est difficile de tous les couvrir dans un court article. Cependant, nous pensons que tant que nous avons une compréhension et une application approfondies, nous bénéficierons beaucoup du travail réel.

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!