Oracle ストアド プロシージャは結果セットを返します

Oracle Databaseでは、ストアド・プロシージャは、入力パラメータを受け入れて一連の操作を実行し、最終的に結果を返すコンパイル済みの再利用可能なコード・ブロックです。結果は、スカラー値、一時テーブルまたはカーソルに格納された結果セット、または OUT パラメータを介して呼び出し元に渡される値である場合があります。

私たちの日常業務では、バッチ操作、長時間実行されるタスク、または複雑なデータ処理ロジックを完了するためにストアド プロシージャを作成する必要があることがよくあります。ただし、ストアド プロシージャで結果セットを返す必要がある場合、次のような問題に遭遇することがよくあります。結果セットをどのように出力するか?出力結果セットの形式は何ですか?複数の結果セットを処理するにはどうすればよいですか?

これらの問題に対処するために、この記事では、Oracle ストアド プロシージャで結果セットを返す方法を紹介し、読者の理解を深めるためにいくつかのサンプル コードを提供します。

1. カーソルを使用して結果セットを返す

Oracle ストアド プロシージャでは、カーソルを使用して結果セットを返すことができます。具体的には、REF CURSOR 型の変数を定義し、OPEN-FETCH-CLOSE 操作を通じてカーソルにデータを入力し、最後にカーソルを OUT パラメータとして呼び出し元に返す必要があります。

以下は、カーソルを使用してemployeesテーブル内のすべてのレコードを返す方法を示す簡単なサンプルコードです:

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

上記のコードでは、get_all_employeesというファイルを定義します。 のストアド プロシージャには、返された結果セットを表す SYS_REFCURSOR 型の OUT パラメータ cur があります。ストアド プロシージャでは、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 テーブル内のすべてのレコードをテーブルに移入します。次に、OUT パラメータ tbl_name を介して、テーブル名 "temp_employees" を呼び出し元に返します。

ストアド プロシージャを呼び出す場合、テーブル名を使用して一時テーブル内のデータにアクセスできます。

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) ステートメントを通じて一時テーブル内のデータにアクセスし、クライアントに表示できます。

グローバル一時テーブルのライフサイクルはセッション レベルであることに注意してください。つまり、テーブル内のデータはデータベース セッションが終了すると自動的に削除されます。これにより、各セッションに独自の一時テーブルが確保され、異なるセッション間のデータの競合が回避されます。

複数の結果セットを返す

場合によっては、1 つのストアド プロシージャで複数の結果セットを返す必要があります。たとえば、複雑なクエリでは、クエリ結果といくつかの概要統計の両方を返す必要があります。 Oracle ストアド プロシージャでは、OUT パラメータとカーソルを使用して、複数の結果セットの出力を実現できます。

以下は、ストアド プロシージャで 2 つの結果セットを返す方法を示す簡単なサンプル コードです。

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 中国語 Web サイトの他の関連記事を参照してください。