Oracle ページング ストアド プロシージャ - ページング検索の効率化

インターネットの発展に伴い、データベースのアプリケーションはますます普及しており、大量のデータをクエリして処理する必要があります。このプロセスでは、ページング検索が最も一般的な要件の 1 つです。 Oracle データベースはページング問合せを実装する手段として ROWNUM を提供しますが、実際のアプリケーションプロセスでは多くの制限や欠陥に直面します。したがって、Oracle ページング ストアド プロシージャの開発は、より効率的なソリューションになりました。

この記事では、実際の事例を組み合わせて、Oracle データベースに基づいた効率的なページング ストアド プロシージャを設計する方法を紹介します。

1. 全体的な設計

Oracle ページング ストアド プロシージャの設計アーキテクチャは、パラメータ転送、データ クエリ、データ ページング計算、および戻り結果の 4 つのコア部分に分割できます。

1. パラメータの受け渡し: クエリ ステートメント、現在のページ番号、ページごとのレコード数などを入力パラメータとしてストアド プロシージャに渡します。
2. データ クエリ: 受信したクエリ ステートメントに基づいてクエリを実行し、条件を満たすデータを取得します。
3. データページング計算：クエリ結果に対してページング処理を行い、指定したページ番号のデータを計算します。
4. 戻り結果: 指定されたページ番号と総レコード数のデータを返します。

2. 特定の実装

Oracle ページング ストアド プロシージャを実装するプロセスでは、Oracle 固有の構文と関数を使用する必要があります。したがって、最初に理解する必要がある基本がいくつかあります。

1. ROW_NUMBER 関数

ROW_NUMBER 関数は、結果セット内の行数をカウントするために使用される Oracle のキーワードです。戻り結果は、行の昇順に並べられた整数値です。以下に例を示します。

SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
FROM emp;

上の例の ROW_NUMBER 関数は、従業員番号で並べ替え、昇順のシリアル番号を割り当てます。これはページ分割されたクエリにとって非常に重要です。

2. 最初の N レコードのクエリ

最初の N レコードをクエリするにはどうすればよいですか? Oracle では、ROWNUM 関数と ROW_NUMBER() 関数という 2 つのメソッドが提供されています。以下に示すように:

SELECT *
FROM (
    SELECT emp.*, ROWNUM rn
    FROM emp
) t
WHERE t.rn <= N;

または

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno <= N;

N をクエリするレコードの数に置き換えるだけです。

3. ページング クエリ

ページング クエリは典型的なシナリオであり、通常は、表示するページ番号とページごとのレコード数を指定する必要があります。このうち、OFFSET は各ページに表示されるデータの開始インデックスを指定し、LIMIT は各ページに表示される最大レコード数を指定します。以下に示すように:

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno > OFFSET AND t.rowno <= OFFSET+LIMIT;

このうち、OFFSET と LIMIT は、柔軟なページング クエリを実現するために、特定のニーズに応じて調整できます。

3. コードの実装

次に、完全な Oracle ページング ストアド プロシージャの例を示します:

CREATE OR REPLACE PROCEDURE paginating_demo (
    p_sql       IN    VARCHAR2,    --带有占位符(:P1,:P2...)的查询语句
    p_curPage   IN    NUMBER,      --当前页码
    p_pageSize  IN    NUMBER,      --每页的记录数量
    p_recordset OUT   SYS_REFCURSOR,--查询结果集
    p_total     OUT   NUMBER       --记录的总数
)
AS
  v_sql VARCHAR2(4000); 
  v_fromIndex NUMBER; 
  v_toIndex NUMBER;

BEGIN
  SELECT COUNT(*) INTO p_total FROM ( p_sql );

  IF (p_total > 0) THEN
    -- 计算 limit 和offset 边界值
    v_fromIndex := ((p_curPage - 1) * p_pageSize);
    v_toIndex   := (p_curPage * p_pageSize);

    v_sql := 'SELECT * FROM ( SELECT t.*, ROWNUM RN FROM ( ' || p_sql || ' ) t WHERE ROWNUM <= &#39; || v_toIndex || &#39; ) WHERE RN > ' || v_fromIndex;

    OPEN p_recordset FOR v_sql;
  END IF;

END paginating_demo;

このコードは、クエリ ステートメント、現在のページ番号、レコード数を結合します。ページごとに、クエリ結果セットとレコードの合計数が入力および出力パラメータとして取得されます。このうち、クエリ結果セットとレコードの総数が出力パラメータとして返されます。

4. 概要

実際のアプリケーションでは、Oracle ページング ストアド プロシージャにより、ページング クエリの効率と安定性が大幅に向上します。上記の知識とスキルを習得することで、Oracle ページング ストアド プロシージャを実際に柔軟に使用して、システム パフォーマンスとユーザー エクスペリエンスを大幅に向上させることができます。

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