La procédure stockée Oracle renvoie un jeu de résultats

Dans Oracle Database, une procédure stockée est un bloc de code compilé et réutilisable qui accepte les paramètres d'entrée et effectue une série d'opérations, renvoyant finalement un résultat. Le résultat peut être une valeur scalaire, un ensemble de résultats stocké dans une table temporaire ou un curseur, ou une valeur transmise à l'appelant via un paramètre OUT.

Dans le travail quotidien, nous avons souvent besoin d'écrire des procédures stockées pour effectuer certaines opérations par lots, des tâches de longue durée ou une logique de traitement de données complexe. Cependant, lorsque nous devons renvoyer un jeu de résultats dans une procédure stockée, nous rencontrons souvent quelques problèmes : Comment sortir le jeu de résultats ? Quel est le format du jeu de résultats de sortie ? Comment gérer plusieurs jeux de résultats ?

En réponse à ces problèmes, cet article présentera comment renvoyer des ensembles de résultats dans les procédures stockées Oracle et fournira des exemples de code pour aider les lecteurs à mieux comprendre.

1. Utilisez un curseur pour renvoyer un ensemble de résultats

Dans les procédures stockées Oracle, nous pouvons utiliser un curseur pour renvoyer un ensemble de résultats. Plus précisément, nous devons définir une variable de type REF CURSOR, puis remplir les données dans le curseur via l'opération OPEN-FETCH-CLOSE, et enfin renvoyer le curseur à l'appelant en tant que paramètre OUT.

Ce qui suit est un exemple de code simple qui montre comment utiliser un curseur pour renvoyer tous les enregistrements de la table des employés :

CREATE OR REPLACE PROCEDURE get_all_employees(cur OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN cur FOR
    SELECT * FROM employees;
END;

Dans le code ci-dessus, nous définissons un fichier nommé get_all_employees a un paramètre OUT cur de type SYS_REFCURSOR, qui représente l'ensemble de résultats renvoyé. Dans la procédure stockée, nous utilisons OPEN cur FOR pour remplir le curseur avec les résultats de l'exécution de l'instruction SELECT. Enfin, à la fin de la procédure stockée, le curseur se ferme automatiquement. get_all_employees 的存储过程，它有一个 OUT 参数 cur，类型为 SYS_REFCURSOR，表示返回的结果集。在存储过程中，我们通过 OPEN cur FOR 来将 SELECT 语句执行结果填充到游标中。最后，在存储过程结束时，游标会自动关闭。

在调用存储过程时，我们需要先声明一个与游标类型相同的变量，并将它作为参数传递给存储过程，然后使用 FETCH 语句来从游标中读取数据行：

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
BEGIN
  get_all_employees(emp_cur);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理数据行
  END LOOP;
  CLOSE emp_cur;
END;

在上面的代码中，我们先声明了一个名为 emp_cur 的游标变量，然后调用 get_all_employees 存储过程，并将 emp_cur 作为参数传递进去。接下来，我们使用 LOOP 和 FETCH 语句来从游标中逐行读取数据，并在每个循环迭代中用 emp_rec 变量来存储当前行的数据。在读取完所有数据后，我们需要手动关闭游标，以释放资源。

需要注意的是，在以上的代码中，我们使用了 %ROWTYPE 来定义了一个类型为 employees 表的行类型变量 emp_rec。这样，在 FETCH 语句中，就无需手动为每个字段指定变量，而是可以将整个数据行读取到 emp_rec 变量中。这种方式可以使代码更加简洁和易读。

值得一提的是，在 Oracle 12c 中，我们还可以使用 FETCH BULK COLLECT INTO 语句来一次性将多行数据读取到 PL/SQL 表或者数组变量中，以提高代码效率。由于 BULK COLLECT 的使用较为复杂，本文不再赘述，读者可以自行搜索相关资料深入学习。

2. 使用临时表返回结果集

除了游标外，我们还可以使用临时表来返回一个结果集。具体来说，我们可以在存储过程中创建一个临时表，并将数据填充到表中，最后将表名作为 OUT 参数返回给调用者。

以下是一个简单的示例代码，演示如何使用临时表返回员工表中的所有记录：

CREATE OR REPLACE PROCEDURE get_all_employees(tbl_name OUT VARCHAR2)
AS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_employees
  AS SELECT * FROM employees;
  
  tbl_name := 'temp_employees';
END;

在上面的代码中，我们先创建了一个名为 temp_employees 的全局临时表，在创建表的同时，将 employees 表中的所有记录填充到表中。接下来，我们将表名 "temp_employees" 通过 OUT 参数 tbl_name 返回给调用者。

在调用存储过程时，我们可以通过表名来访问临时表中的数据：

DECLARE
  tbl_name VARCHAR2(30);
BEGIN
  get_all_employees(tbl_name);
  SELECT * FROM TABLE(tbl_name);
END;

在上面的代码中，我们声明了一个名为 tbl_name 的变量，用来存储存储过程返回的表名。在调用存储过程 get_all_employees 时，tbl_name 将会被更新为 "temp_employees"。之后，我们可以通过 SELECT * FROM TABLE(tbl_name)

Lors de l'appel d'une procédure stockée, nous devons d'abord déclarer une variable du même type que le curseur, la transmettre en tant que paramètre à la procédure stockée, puis utiliser l'instruction FETCH pour lire les lignes de données de le curseur : # 🎜🎜#

CREATE OR REPLACE PROCEDURE get_employees_and_stats(cur OUT SYS_REFCURSOR, total_salary OUT NUMBER)
AS
BEGIN
  OPEN cur FOR SELECT * FROM employees;
  SELECT SUM(salary) INTO total_salary FROM employees;
END;

Dans le code ci-dessus, nous déclarons d'abord une variable de curseur nommée emp_cur, puis appelons la procédure stockée get_all_employees et définissons le emp_cur est passé en paramètre. Ensuite, nous utilisons les instructions LOOP et FETCH pour lire les données du curseur ligne par ligne, et utilisons la variable emp_rec pour stocker les données de la ligne actuelle dans chaque itération de boucle. Après avoir lu toutes les données, nous devons fermer manuellement le curseur pour libérer les ressources.

Il est à noter que dans le code ci-dessus, on utilise %ROWTYPE pour définir une variable de type ligne de type employés table emp_rec . De cette façon, dans l'instruction FETCH, au lieu de spécifier manuellement des variables pour chaque champ, la ligne entière de données peut être lue dans la variable emp_rec. Cette approche rend le code plus concis et plus lisible.
3. Il convient de mentionner que dans Oracle 12c, nous pouvons également utiliser l'instruction FETCH BULK COLLECT INTO pour lire plusieurs lignes de données dans une table PL/SQL ou une variable de tableau en même temps afin d'améliorer l'efficacité du code. L’utilisation de BULK COLLECT étant relativement complexe, cet article n’entrera pas dans les détails. Les lecteurs peuvent rechercher des informations pertinentes et en savoir plus.

Utiliser une table temporaire pour renvoyer un ensemble de résultats

En plus des curseurs, nous pouvons également utiliser des tables temporaires pour renvoyer un ensemble de résultats. Plus précisément, nous pouvons créer une table temporaire dans la procédure stockée, remplir la table avec des données et enfin renvoyer le nom de la table à l'appelant en tant que paramètre OUT. #🎜🎜##🎜🎜#Ce qui suit est un exemple de code simple qui montre comment utiliser une table temporaire pour renvoyer tous les enregistrements de la table des employés : #🎜🎜#

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
  total_salary NUMBER;
BEGIN
  get_employees_and_stats(emp_cur, total_salary);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理员工数据行
  END LOOP;
  -- 处理工资汇总数据（total_salary）
  CLOSE emp_cur;
END;

#🎜🎜#Dans le code ci-dessus, nous créons d'abord un nommé Pour la table temporaire globale de temp_employees, lors de la création de la table, remplissez tous les enregistrements de la table employees dans la table. Ensuite, nous renvoyons le nom de la table "temp_employees" à l'appelant via le paramètre OUT tbl_name. #🎜🎜##🎜🎜#Lors de l'appel d'une procédure stockée, on peut accéder aux données de la table temporaire via le nom de la table : #🎜🎜#rrreee#🎜🎜#Dans le code ci-dessus, on déclare un fichier nommé tbl_name est utilisée pour stocker le nom de la table renvoyé par la procédure stockée. Lorsque la procédure stockée get_all_employees est appelée, tbl_name sera mis à jour en "temp_employees". Après cela, nous pouvons accéder aux données de la table temporaire via l'instruction SELECT * FROM TABLE(tbl_name) et les afficher dans le client. #🎜🎜##🎜🎜#Il convient de noter que le cycle de vie de la table temporaire globale est au niveau de la session, c'est-à-dire que les données de la table seront automatiquement supprimées à la fin de la session de base de données. Cela garantit que chaque session dispose de sa propre table temporaire et évite les conflits de données entre les différentes sessions. #🎜🎜##🎜🎜##🎜🎜#Renvoyer plusieurs ensembles de résultats#🎜🎜##🎜🎜##🎜🎜#Dans certains cas, nous devons renvoyer plusieurs ensembles de résultats dans une seule procédure stockée. Par exemple, dans une requête complexe, nous devons renvoyer à la fois les résultats de la requête et des statistiques récapitulatives. Dans les procédures stockées Oracle, nous pouvons utiliser les paramètres et les curseurs OUT pour obtenir plusieurs sorties de jeux de résultats. #🎜🎜##🎜🎜#Ce qui suit est un exemple de code simple qui montre comment renvoyer deux jeux de résultats dans une procédure stockée : #🎜🎜#

CREATE OR REPLACE PROCEDURE get_employees_and_stats(cur OUT SYS_REFCURSOR, total_salary OUT NUMBER)
AS
BEGIN
  OPEN cur FOR SELECT * FROM employees;
  SELECT SUM(salary) INTO total_salary FROM employees;
END;

在上面的代码中，我们定义了一个名为 get_employees_and_stats 的存储过程，它有两个 OUT 参数，分别是一个游标变量 cur 和一个标量变量 total_salary。在存储过程中，我们先通过 OPEN cur FOR 来填充游标变量 cur，并将其返回给调用者。接着，我们通过 SELECT SUM(salary) INTO total_salary FROM employees; 语句计算出员工表中工资的总和，并将结果设置为标量变量 total_salary，同样也将其返回给调用者。

在调用存储过程时，我们需要将两个 OUT 参数作为参数传递给存储过程，并用游标变量来访问查询结果：

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
  total_salary NUMBER;
BEGIN
  get_employees_and_stats(emp_cur, total_salary);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理员工数据行
  END LOOP;
  -- 处理工资汇总数据（total_salary）
  CLOSE emp_cur;
END;

在上面的代码中，我们声明了一个游标变量 emp_cur 和一个标量变量 total_salary，用来接收存储过程的返回值。在调用存储过程时，我们将这两个变量作为参数传递给存储过程，并通过 FETCH emp_cur INTO emp_rec 逐行读取结果集中的数据。在读取所有数据之后，我们通过标量变量 total_salary 处理工资汇总数据。最后，我们需要手动关闭游标 emp_cur 以释放资源。

需要注意的是，在返回多个结果集时，我们需要保证每个结果集在调用存储过程之前都执行完毕，否则可能会导致输出数据不完整或者部分数据丢失。因此，我们需要仔细设计存储过程的逻辑，保证数据的完整性和准确性。

总结

在 Oracle 数据库中，存储过程是一个强大的编程工具，可以帮助我们完成一些复杂的数据处理任务。在存储过程中，返回结果集是一个常见的需求，我们可以使用游标或者临时表来实现结果集的输出，也可以通过 OUT 参数来返回多个结果集。在编写存储过程时，我们需要深入理解数据库的工作方式和 PL/SQL 语法，以保证代码的正确性和性能。

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!