Oracle ストアド プロシージャ ページング

データベース クエリのパフォーマンスをより最適化するには、ページング クエリにストアド プロシージャを使用するのが良い選択です。ストアド プロシージャを確立すると、大量のデータをクエリするときにページングでリソースを消費できるようになります。 Oracleデータベースには、ページング操作用の実行可能な高度なアルゴリズムがいくつか用意されており、最小限のパーセンテージを使用して必要な結果を取得し、問合せ効率を向上させます。

Oracle では、ストアド プロシージャは、SQL ステートメントをプリコンパイルして保存するために使用できるデータベース オブジェクトです。ストアド プロシージャはパラメータを受け取り、結果を返すことができます。大量のデータを処理する場合、ストアド プロシージャを使用すると、データベース クエリの効率が大幅に向上します。

以下では、Oracle のストアド プロシージャを使用してページング クエリを実装する方法を紹介します。

まず、ページ分割できるようにストアド プロシージャのパラメーターを設定する必要があります。

 CREATE OR REPLACE PROCEDURE paged_results(
  in_table IN VARCHAR2,
  in_where IN VARCHAR2 DEFAULT NULL,
  in_orderby IN VARCHAR2 DEFAULT NULL,
  in_pageSize IN NUMBER DEFAULT 10,
  in_pageNumber IN NUMBER DEFAULT 1,
  out_cursor OUT SYS_REFCURSOR) AS

このストアド プロシージャは、テーブル名、クエリ条件、並べ替え条件、各ページに表示されるレコード数、およびページ番号の 5 つの入力パラメータを受け入れます。このうち、in_table、in_pageSize、in_pageNumber パラメータは入力する必要がありますが、in_where パラメータと in_orderby パラメータはオプションです。 out_cursor は、各ページのクエリ結果を取得するための出力パラメータとして使用されます。

次に、ストアド プロシージャ内でページネーションを行う方法を検討する必要があります。これが機能の鍵となります。

別のストアド プロシージャを使用してページングを実装できます。以下に示すように、FOR LOOP ループを通じて、データ セット内の指定されたページに含まれるレコードが取得され、返されるレコードの数が計算されます。

cnt := in_pageSize * ( in_pageNumber - 1 );
v_sql := 'select count(*) from ' || in_table ||
         '' || NVL( in_where, '' ) || '';
EXECUTE IMMEDIATE v_sql INTO v_totalRows;
v_totalPages := CEIL( v_totalRows / in_pageSize );
v_currentPage := in_pageNumber;
v_rowCount := 0;
v_startRow := 1;
v_endRow := in_pageSize;
OPEN out_cursor FOR
( SELECT *
  FROM
  (SELECT
    ROW_NUMBER() OVER(ORDER BY tmp_table.row_counter) row_num,
    tmp_table.*
  FROM
    (SELECT
      ROWNUM - 1 + cnt row_counter,
      t.*
    FROM
      ( SELECT
          *
        FROM ' || in_table || 
          NVL( in_where, '' ) ||
          NVL( in_orderby, '' ) || '
      )t
    WHERE
      ROWNUM <= cnt + in_pageSize
    ) tmp_table
  WHERE
    tmp_table.row_counter >= cnt
  )
);

このストアド プロシージャは、指定されたページ番号のページング結果を返します。2 つのパラメータ in_where と in_orderby は、条件と並べ替え方法を制御するために使用されます。 in_where パラメーターと in_orderby パラメーターが渡されない場合は、条件や順序付けが必要ないことを意味します。

for ループでは、まずデータ セット全体から条件を満たすレコードの数を取得し、次に各ページに表示されるレコードの数に基づいて総ページ数を計算します。指定されたページのデータを取得します。ここでは Row_num を使用し、row_counter を削除します。row_num は行番号を表し、row_counter は連続してカウントされる行の合計数です。最後に、カーソルを開いて、ページ分割された結果セットを返します。

ストアド プロシージャでは、処理を容易にするために、EXECUTE IMMEDIATE ステートメントを使用して SQL ステートメントを動的に実行するため、SQL ステートメントを動的に受け取るように v_sql パラメーターを設定する必要があります。さらに、現在のページ番号、総ページ数、開始行と終了行を保存するには、4 つの共通変数を定義する必要があります。

上記の手順を実行すると、Oracle はページング ロジックを正常に処理できるようになります。これで、PL/SQL でストアド プロシージャを呼び出してデータを取得できるようになりました。

 DECLARE
  CURSOR c_results IS
    SELECT *
      FROM table_name;
  v_result sds.table_name%ROWTYPE;
  in_pageNum NUMBER := 50;
  in_pageSize NUMBER := 10;
  in_orderBy VARCHAR2(100) := ” ORDER BY column_2 ASC”;
  v_string VARCHAR2(100);
  new_cursor SYS_REFCURSOR;
BEGIN
  sds.paged_results(
    in_table_name => 'table_name',
    in_where => 'WHERE column_1 < 50',
    in_orderBy => in_orderBy,
    in_pageSize => in_pageSize,
    in_pageNum => in_pageNum,
    out_cursor => new_cursor);
END;

上記の例のメソッド呼び出しを通じて、指定されたページのデータを簡単に取得し、結果セットを出力パラメータとして返すことができます。このうち、in_where と in_orderBy は、データ範囲と並べ替え方法の条件を提供できます。

一般に、ページングにストアド プロシージャを使用すると、大量のデータをクエリするときのデータベースのパフォーマンスが向上します。注意する必要があるのは、ストアド プロシージャの動作効率は SQL 記述の品質に関係しており、特定の条件に応じて調整および最適化する必要があるということです。

上記の例を通じて、ストアド プロシージャを使用してページング クエリを実装する基本原則は、ページング パラメーターを設定することでページングを実装する SQL ステートメントを動的に生成することであることが理解できたと思います。ストアド プロシージャを使用すると、データベース クエリの負担が効果的に軽減され、データベース クエリの効率が向上し、サーバー側のリクエストの数が削減されます。

以上がOracle ストアド プロシージャ ページングの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。