oracle 預存程序 傳回結果集

在 Oracle 資料庫中，預存程序是一段已編譯的可重複使用程式碼區塊，它接受輸入參數並執行一系列操作，最終傳回一個結果。這個結果可能是一個標量值、一個儲存在暫存表或遊標中的結果集，或是透過 OUT 參數傳遞給呼叫者的值。

在日常工作中，我們經常需要編寫預存程序來完成一些批次操作、長時間運行的任務或複雜的資料處理邏輯。然而，在預存過程中，我們需要傳回一個結果集時，往往會遇到一些問題：如何輸出結果集？輸出結果集的格式是怎樣的？如何處理多個結果集？

針對這些問題，本文將介紹在 Oracle 預存程序中如何傳回結果集，並提供一些範例程式碼來幫助讀者更好地理解。

1. 使用遊標傳回結果集

在 Oracle 預存程序中，我們可以使用遊標來傳回一個結果集。具體來說，我們需要定義一個 REF CURSOR 類型的變量，然後透過 OPEN-FETCH-CLOSE 操作將資料填入遊標中，最後將遊標作為 OUT 參數傳回給呼叫者。

以下是一個簡單的範例程式碼，示範如何使用遊標傳回員工表中的所有記錄：

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

在上面的程式碼中，我們定義了一個名為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) 語句來存取臨時表中的數據，並將其顯示在客戶端中。

要注意的是，全域臨時表的生命週期是會話層級的，也就是在資料庫會話結束時，表格中的資料會自動被刪除。這樣可以確保每個會話都有自己的臨時表，避免了不同會話之間的資料衝突。

3. 傳回多個結果集

在某些情況下，我們需要在一個預存程序中傳回多個結果集。例如，在一個複雜的查詢中，我們既需要傳回查詢結果，又需要傳回一些總計統計資料。在 Oracle 預存程序中，我們可以使用 OUT 參數和遊標來實現多結果集輸出。

以下是一個簡單的範例程式碼，示範如何在一個預存程序中傳回兩個結果集：

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

以上是oracle 預存程序 傳回結果集的詳細內容。更多資訊請關注PHP中文網其他相關文章！