Oracle ストアドプロシージャページング-Oracle-php.cn

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

WBOY

リリース： 2023-05-18 10:19:38

オリジナル

642 人が閲覧しました

データベースクエリのパフォーマンスをより最適化するには、ページングクエリにストアドプロシージャを使用するのが良い選択です。ストアドプロシージャを確立すると、大量のデータをクエリするときにページングでリソースを消費できるようになります。 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 サイトの他の関連記事を参照してください。