Prosedur tersimpan paging Oracle - menjadikan carian paging lebih cekap

Dengan perkembangan Internet, aplikasi pangkalan data telah menjadi semakin popular, dan sejumlah besar data perlu disoal dan diproses. Dalam proses ini, carian halaman adalah salah satu keperluan yang paling biasa. Walaupun pangkalan data Oracle menyediakan ROWNUM sebagai cara untuk melaksanakan pertanyaan paging, ia menghadapi banyak batasan dan kekurangan dalam proses permohonan sebenar. Oleh itu, membangunkan prosedur tersimpan paging Oracle telah menjadi penyelesaian yang lebih cekap.

Artikel ini akan menggabungkan kes sebenar untuk memperkenalkan cara mereka bentuk prosedur tersimpan paging yang cekap berdasarkan pangkalan data Oracle.

1. Reka bentuk keseluruhan

Seni bina reka bentuk prosedur tersimpan paging Oracle boleh dibahagikan kepada empat bahagian teras: penghantaran parameter, pertanyaan data, pengiraan paging data dan hasil pemulangan.

1. Melalui parameter: Lulus pernyataan pertanyaan, nombor halaman semasa, bilangan rekod setiap halaman, dsb. sebagai parameter input ke dalam prosedur yang disimpan.
2. Pertanyaan data: Pertanyaan berdasarkan pernyataan pertanyaan masuk untuk mendapatkan data yang memenuhi syarat.
3. Pengiraan paging data: lakukan pemprosesan paging pada hasil pertanyaan dan hitung data nombor halaman yang ditentukan.
4. Kembalikan hasil: Kembalikan data nombor halaman yang ditentukan dan jumlah bilangan rekod.

2. Pelaksanaan khusus

Dalam proses melaksanakan prosedur tersimpan paging Oracle, beberapa sintaks dan fungsi khusus Oracle perlu digunakan. Jadi, ada beberapa asas yang perlu difahami terlebih dahulu.

1. Fungsi ROW_NUMBER

Fungsi ROW_NUMBER ialah kata kunci dalam Oracle yang digunakan untuk mengira bilangan baris dalam set hasil. Hasil pulangannya ialah nilai integer, disusun dalam susunan baris menaik. Berikut ialah contoh:

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

Fungsi ROW_NUMBER dalam contoh di atas akan mengisih mengikut nombor pekerja dan memberikan nombor siri yang semakin meningkat. Ini sangat penting untuk pertanyaan bernombor muka surat.

2. Pertanyaan rekod N pertama

Bagaimana untuk bertanya rekod N pertama? Oracle menyediakan dua kaedah: fungsi ROWNUM dan ROW_NUMBER(). Seperti yang ditunjukkan di bawah:

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

atau

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

Cuma gantikan N dengan bilangan rekod yang perlu anda buat pertanyaan.

3. Pertanyaan halaman

Pertanyaan halaman ialah senario biasa, yang biasanya memerlukan penetapan nombor halaman untuk dipaparkan dan bilangan rekod setiap halaman. Antaranya, OFFSET menentukan indeks permulaan data yang dipaparkan pada setiap halaman, dan LIMIT menentukan bilangan maksimum rekod yang dipaparkan pada setiap halaman. Seperti yang ditunjukkan di bawah:

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;

Antaranya, OFFSET dan LIMIT boleh dilaraskan mengikut keperluan khusus untuk mencapai pertanyaan paging yang fleksibel.

3. Pelaksanaan kod

Berikut ialah contoh prosedur tersimpan halaman Oracle yang lengkap:

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;

Kod ini akan menanyakan penyata, nombor halaman semasa, dan rekod setiap nombor halaman, set hasil pertanyaan, dan jumlah bilangan rekod sebagai parameter input dan output. Antaranya, set keputusan pertanyaan dan jumlah rekod akan dikembalikan sebagai parameter output.

4. Ringkasan

Dalam aplikasi sebenar, prosedur tersimpan paging Oracle sangat meningkatkan kecekapan dan kestabilan pertanyaan paging. Dengan menguasai pengetahuan dan kemahiran di atas, kami boleh menggunakan prosedur tersimpan paging Oracle secara fleksibel dalam amalan untuk meningkatkan prestasi sistem dan pengalaman pengguna dengan ketara.

Atas ialah kandungan terperinci Prosedur tersimpan paging Oracle - menjadikan carian paging lebih cekap. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!