Temporäre Tabelle der gespeicherten Oracle-Prozedur

In einer Oracle-Datenbank ist eine gespeicherte Prozedur eine Reihe vordefinierter SQL-Anweisungen, die Eingabeparameter empfangen und Ausgabewerte zurückgeben können. Sie werden verwendet, um bestimmte Aufgaben auszuführen, normalerweise um die Leistung und Wartbarkeit in Oracle-Datenbanken zu verbessern und eine enge Integration mit der Geschäftslogik zu erreichen.

Eine temporäre Tabelle ist ein temporärer Speicherplatz, der während des Abfragevorgangs verwendet wird. In Oracle-Datenbanken können temporäre Tabellen mit der Anweisung CREATE GLOBAL TEMPORARY TABLE erstellt werden. Solche Tabellen werden am Ende einer Sitzung automatisch gelöscht und können als Zwischenergebnisse für mehrere Abfragen innerhalb einer Sitzung verwendet werden.

In den letzten Jahren hat sich die Kombination aus gespeicherten Prozeduren und temporären Tabellen zu einer der Best Practices für die Ausführung effizienter und komplexer Abfragen entwickelt. Beispielsweise können in großen Data Warehouses komplexe ETL-Vorgänge (Extraktion, Transformation und Laden) mithilfe gespeicherter Prozeduren und temporärer Tabellen implementiert werden und weisen eine gute Leistung in Bezug auf Leistung und Wartbarkeit auf.

In diesem Artikel stellen wir vor, wie Sie eine temporäre Tabelle in einer Oracle-Datenbank erstellen und diese mit gespeicherten Prozeduren verwenden.

Temporäre Tabelle erstellen

Um eine temporäre Tabelle in der Oracle-Datenbank zu erstellen, können Sie die folgende CREATE GLOBAL TEMPORARY TABLE-Anweisung verwenden:

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

In dieser Anweisung:

• temp_table_name ist das, was Sie möchten create Der Name der temporären Tabelle.
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 ist der Spaltenname in der Tabelle und es können mehrere Spalten definiert werden.

datatype ist der Datentyp jeder Spalte.

Beachten Sie, dass sich ON COMMIT DELETE ROWS in dieser Anweisung auf das Löschen aller Zeilen in der temporären Tabelle bezieht, wenn die Transaktion festgeschrieben oder die Sitzung geschlossen wird. Dadurch wird sichergestellt, dass jede Sitzung eine leere Tabelle verwendet und am Ende der Sitzung Systemressourcen freigegeben werden.

Temporäre Tabellen verwenden

🎜Sobald Sie eine temporäre Tabelle erstellt haben, können Sie sie in einer gespeicherten Prozedur verwenden. Wenn Sie beispielsweise einen Zwischenergebnissatz in einer gespeicherten Prozedur definieren müssen, können Sie die folgenden Schritte ausführen: 🎜

🎜Definieren Sie eine temporäre Tabelle in einer gespeicherten Prozedur:

rrreee🎜Hier Beispiel: I In der gespeicherten Prozedur ist eine temporäre Tabelle mit dem Namen temp_table definiert, die zwei Spalten col1 und col2 enthält und ON COMMIT verwendet DELETE ROWS-Option, um es zu definieren. 🎜

🎜Daten in die temporäre Tabelle in der gespeicherten Prozedur einfügen:

rrreee🎜In diesem Beispiel habe ich zwei Datenzeilen in die temporäre Tabelle eingefügt. Beachten Sie, dass die Anweisung COMMIT hier erforderlich ist, da temporäre Tabellen in der Oracle-Datenbank nur verfügbar sind, wenn die Transaktion festgeschrieben wird. 🎜

🎜Fragen Sie eine temporäre Tabelle in einer gespeicherten Prozedur ab:

rrreee🎜In diesem Beispiel habe ich den Datentyp SYS_REFCURSOR verwendet, um ein Ergebnis zu deklarieren Zeiger setzen. Anschließend habe ich einen Cursor in der gespeicherten Prozedur geöffnet, mit einer SELECT-Anweisung die Daten aus der temporären Tabelle abgerufen und die Ergebnismenge an das Programm zurückgegeben, das die gespeicherte Prozedur aufgerufen hat. Danach können Sie die Ergebnismenge wie jeden anderen Cursor verwenden. 🎜🎜Fazit🎜🎜In Oracle-Datenbanken sind gespeicherte Prozeduren und temporäre Tabellen zu bewährten Methoden für die Ausführung komplexer Abfragen geworden. Die Verwendung temporärer Tabellen in Verbindung mit gespeicherten Prozeduren kann die Abfrageleistung und Wartbarkeit erheblich verbessern und eine enge Integration in die Geschäftslogik erreichen. In praktischen Anwendungen wird empfohlen, Ihre temporären Tabellen und gespeicherten Prozeduren sorgfältig zu entwerfen und sicherzustellen, dass sie Ihren Anforderungen gerecht werden. 🎜

Das obige ist der detaillierte Inhalt vonTemporäre Tabelle der gespeicherten Oracle-Prozedur. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!