Die gespeicherte Oracle-Prozedur gibt eine Ergebnismenge zurück

In der Oracle-Datenbank ist eine gespeicherte Prozedur ein kompilierter, wiederverwendbarer Codeblock, der Eingabeparameter akzeptiert und eine Reihe von Vorgängen ausführt und letztendlich ein Ergebnis zurückgibt. Das Ergebnis kann ein Skalarwert, ein in einer temporären Tabelle oder einem Cursor gespeicherter Ergebnissatz oder ein über einen OUT-Parameter an den Aufrufer übergebener Wert sein.

Bei der täglichen Arbeit müssen wir häufig gespeicherte Prozeduren schreiben, um einige Batch-Vorgänge, Aufgaben mit langer Laufzeit oder komplexe Datenverarbeitungslogik abzuschließen. Wenn wir jedoch eine Ergebnismenge in einer gespeicherten Prozedur zurückgeben müssen, stoßen wir häufig auf einige Probleme: Wie wird die Ergebnismenge ausgegeben? Welches Format hat die Ausgabeergebnismenge? Wie gehe ich mit mehreren Ergebnismengen um?

Als Reaktion auf diese Probleme stellt dieser Artikel vor, wie Ergebnismengen in gespeicherten Oracle-Prozeduren zurückgegeben werden, und stellt einige Beispielcodes bereit, um den Lesern das Verständnis zu erleichtern.

1. Verwenden Sie einen Cursor, um eine Ergebnismenge zurückzugeben

In gespeicherten Oracle-Prozeduren können wir einen Cursor verwenden, um eine Ergebnismenge zurückzugeben. Insbesondere müssen wir eine Variable vom Typ REF CURSOR definieren, dann die Daten über die Operation OPEN-FETCH-CLOSE in den Cursor füllen und schließlich den Cursor als OUT-Parameter an den Aufrufer zurückgeben.

Das Folgende ist ein einfacher Beispielcode, der zeigt, wie man mit einem Cursor alle Datensätze in der Mitarbeitertabelle zurückgibt:

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

Im obigen Code haben wir eine gespeicherte Prozedur namens get_all_employees definiert, die Es gibt einen OUT-Parameter cur vom Typ SYS_REFCURSOR, der die zurückgegebene Ergebnismenge darstellt. In der gespeicherten Prozedur verwenden wir OPEN cur FOR, um den Cursor mit den Ergebnissen der Ausführung der SELECT-Anweisung zu füllen. Am Ende der gespeicherten Prozedur wird der Cursor schließlich automatisch geschlossen. 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)

Beim Aufrufen der gespeicherten Prozedur müssen wir zunächst eine Variable vom gleichen Typ wie der Cursor deklarieren und als Parameter an die gespeicherte Prozedur übergeben und dann die FETCH-Anweisung verwenden, um die Datenzeilen vom Cursor zu lesen:

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;

Im obigen Code deklarieren wir zunächst eine Cursorvariable mit dem Namen emp_cur, rufen dann die gespeicherte Prozedur get_all_employees auf und übergeben emp_cur als Parameter. Als nächstes verwenden wir LOOP- und FETCH-Anweisungen, um Daten zeilenweise aus dem Cursor zu lesen, und verwenden die Variable emp_rec, um die Daten der aktuellen Zeile in jeder Schleifeniteration zu speichern. Nachdem wir alle Daten gelesen haben, müssen wir den Cursor manuell schließen, um Ressourcen freizugeben.

3. Es ist zu beachten, dass wir im obigen Code %ROWTYPE verwenden, um eine Zeilentypvariable emp_rec für den Tabellencode <code>employees zu definieren. Auf diese Weise kann in der FETCH-Anweisung anstelle der manuellen Angabe von Variablen für jedes Feld die gesamte Datenzeile in die Variable emp_rec eingelesen werden. Dieser Ansatz macht den Code prägnanter und lesbarer.

Es ist erwähnenswert, dass wir in Oracle 12c auch die Anweisung FETCH BULK COLLECT INTO verwenden können, um mehrere Datenzeilen gleichzeitig in eine PL/SQL-Tabelle oder Array-Variable einzulesen, um die Codeeffizienz zu verbessern. Da die Verwendung von BULK COLLECT relativ komplex ist, wird in diesem Artikel nicht auf Details eingegangen. Leser können nach relevanten Informationen für ein tieferes Studium suchen.

Verwenden Sie eine temporäre Tabelle, um eine Ergebnismenge zurückzugeben
🎜Zusätzlich zu Cursorn können wir auch eine temporäre Tabelle verwenden, um eine Ergebnismenge zurückzugeben. Konkret können wir in der gespeicherten Prozedur eine temporäre Tabelle erstellen, die Tabelle mit Daten füllen und schließlich den Tabellennamen als OUT-Parameter an den Aufrufer zurückgeben. 🎜🎜Das Folgende ist ein einfacher Beispielcode, der zeigt, wie eine temporäre Tabelle verwendet wird, um alle Datensätze in der Mitarbeitertabelle zurückzugeben: 🎜

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;

🎜Im obigen Code erstellen wir zunächst eine globale temporäre Tabelle mit dem Namen temp_employees , während Sie die Tabelle erstellen, füllen Sie sie mit allen Datensätzen in der Tabelle employees. Als nächstes geben wir den Tabellennamen "temp_employees" über den OUT-Parameter tbl_name an den Aufrufer zurück. 🎜🎜Beim Aufrufen einer gespeicherten Prozedur können wir über den Tabellennamen auf die Daten in der temporären Tabelle zugreifen: 🎜rrreee🎜Im obigen Code haben wir eine Variable mit dem Namen tbl_name zum Speichern des von zurückgegebenen Tabellennamens deklariert die gespeicherte Prozedur. Wenn die gespeicherte Prozedur get_all_employees aufgerufen wird, wird tbl_name auf "temp_employees" aktualisiert. Danach können wir über die Anweisung SELECT * FROM TABLE(tbl_name) auf die Daten in der temporären Tabelle zugreifen und sie im Client anzeigen. 🎜🎜Es ist zu beachten, dass der Lebenszyklus der globalen temporären Tabelle auf Sitzungsebene liegt, dh die Daten in der Tabelle werden automatisch gelöscht, wenn die Datenbanksitzung endet. Dadurch wird sichergestellt, dass jede Sitzung über eine eigene temporäre Tabelle verfügt und Datenkonflikte zwischen verschiedenen Sitzungen vermieden werden. 🎜🎜🎜Mehrere Ergebnismengen zurückgeben🎜🎜🎜In manchen Fällen müssen wir mehrere Ergebnismengen in einer gespeicherten Prozedur zurückgeben. Bei einer komplexen Abfrage müssen wir beispielsweise sowohl die Abfrageergebnisse als auch einige zusammenfassende Statistiken zurückgeben. In gespeicherten Oracle-Prozeduren können wir OUT-Parameter und Cursor verwenden, um eine Ausgabe mehrerer Ergebnismengen zu erreichen. 🎜🎜Das Folgende ist ein einfacher Beispielcode, der zeigt, wie zwei Ergebnismengen in einer gespeicherten Prozedur zurückgegeben werden: 🎜

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 语法，以保证代码的正确性和性能。

Das obige ist der detaillierte Inhalt vonDie gespeicherte Oracle-Prozedur gibt eine Ergebnismenge zurück. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!