table temporaire de procédure stockée Oracle

Dans la base de données Oracle, une procédure stockée est un ensemble d'instructions SQL prédéfinies qui peuvent recevoir des paramètres d'entrée et renvoyer des valeurs de sortie. Ils sont utilisés pour effectuer des tâches spécifiques, généralement pour améliorer les performances et la maintenabilité des bases de données Oracle, et pour réaliser une intégration étroite avec la logique métier.

Une table temporaire est un espace de stockage temporaire utilisé pendant le processus de requête. Dans la base de données Oracle, des tables temporaires peuvent être créées à l'aide de l'instruction CREATE GLOBAL TEMPORARY TABLE. Ces tables sont automatiquement supprimées à la fin d'une session et peuvent être utilisées comme résultats intermédiaires pour plusieurs requêtes au sein d'une même session.

Au cours des dernières années, la combinaison de procédures stockées et de tables temporaires est devenue l'une des meilleures pratiques pour exécuter des requêtes efficaces et complexes. Par exemple, dans les grands entrepôts de données, des opérations ETL (extraction, transformation et chargement) complexes peuvent être mises en œuvre à l'aide de procédures stockées et de tables temporaires, et avoir de bonnes performances en termes de performances et de maintenabilité.

Dans cet article, nous expliquerons comment créer une table temporaire dans la base de données Oracle et l'utiliser avec des procédures stockées.

Créer une table temporaire

Pour créer une table temporaire dans la base de données Oracle, vous pouvez utiliser l'instruction CREATE GLOBAL TEMPORARY TABLE suivante :

CREATE GLOBAL TEMPORARY TABLE temp_table_name
(
  column_name1  datatype1,
  column_name2  datatype2,
  column_name3  datatype3,
  ...
)
ON COMMIT DELETE ROWS;

Dans cette instruction :

• temp_table_name est ce que vous souhaitez créer Le nom de la table temporaire.
temp_table_name是你要创建的临时表的名称。
• column_name是表中的列名，可以定义多个列。
• datatype是每个列的数据类型。

注意，在这个语句中，ON COMMIT DELETE ROWS是指在提交事务或者会话关闭时删除临时表中的所有行。这可以确保每个会话使用的是空表，并且可以在会话结束时释放系统资源。

使用临时表

一旦你创建了临时表，你就可以在存储过程中使用它。例如，如果你需要在存储过程中定义一个中间结果集，你可以使用以下步骤：

1. 在存储过程中定义临时表：

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
END my_procedure;

在这个例子中，我在存储过程中定义了一个名为temp_table的临时表，包括两个列col1和col2，并使用ON COMMIT DELETE ROWS选项来定义它。

2. 在存储过程中向临时表中插入数据：

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
END my_procedure;

在这个例子中，我向临时表中插入了两行数据。注意，这里的COMMIT语句是必需的，因为Oracle数据库中的临时表只在事务提交时可用。

3. 在存储过程中查询临时表：

CREATE OR REPLACE PROCEDURE my_procedure IS
  cur1 SYS_REFCURSOR;
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
  
  OPEN cur1 FOR
  SELECT *
  FROM temp_table;
  
  -- Use the result set
  -- ...
END my_procedure;

在这个例子中，我使用了SYS_REFCURSOR数据类型来声明一个结果集指针。然后，我在存储过程中打开了一个游标，使用SELECTcolumn_name est le nom de la colonne dans le tableau, et plusieurs colonnes peuvent être définies.

datatype est le type de données de chaque colonne.

Notez que dans cette instruction, ON COMMIT DELETE ROWS fait référence à la suppression de toutes les lignes de la table temporaire lorsque la transaction est validée ou que la session est fermée. Cela garantit que chaque session utilise une table vide et libère des ressources système à la fin de la session.

Utilisation de tables temporaires

🎜Une fois que vous avez créé une table temporaire, vous pouvez l'utiliser dans une procédure stockée. Par exemple, si vous devez définir un jeu de résultats intermédiaire dans une procédure stockée, vous pouvez utiliser les étapes suivantes : 🎜

🎜Définissez une table temporaire dans une procédure stockée :

rrreee🎜Dans ce exemple, I Une table temporaire nommée temp_table est définie dans la procédure stockée, comprenant deux colonnes col1 et col2, et utilise ON COMMIT DELETE ROWS pour le définir. 🎜

🎜Insérer des données dans la table temporaire dans la procédure stockée :

rrreee🎜Dans cet exemple, j'ai inséré deux lignes de données dans la table temporaire. Notez que l'instruction COMMIT est requise ici car les tables temporaires de la base de données Oracle ne sont disponibles que lorsque la transaction est validée. 🎜

🎜Interroger une table temporaire dans une procédure stockée :

rrreee🎜Dans cet exemple, j'ai utilisé le type de données SYS_REFCURSOR pour déclarer un résultat Définir le pointeur. J'ai ensuite ouvert un curseur dans la procédure stockée, utilisé une instruction SELECT pour récupérer les données de la table temporaire et renvoyé le jeu de résultats au programme qui a appelé la procédure stockée. Après cela, vous pouvez utiliser le jeu de résultats comme n’importe quel autre curseur. 🎜🎜Conclusion🎜🎜Dans la base de données Oracle, les procédures stockées et les tables temporaires sont devenues une bonne pratique pour exécuter des requêtes complexes. L'utilisation de tables temporaires conjointement avec des procédures stockées peut améliorer considérablement les performances et la maintenabilité des requêtes, et permettre une intégration étroite avec la logique métier. Dans les applications pratiques, il est recommandé de concevoir soigneusement vos tables temporaires et procédures stockées et de vous assurer qu'elles peuvent répondre à vos besoins. 🎜

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!