Bagaimana untuk melaksanakan pertanyaan paging dalam prosedur tersimpan Oracle

Dalam pangkalan data Oracle, prosedur tersimpan ialah blok kod SQL yang boleh digunakan semula yang boleh digunakan untuk melaksanakan banyak operasi data yang kompleks. Antaranya, pertanyaan paging ialah keperluan biasa, seperti memaparkan senarai data paging dalam aplikasi web, atau memaparkan hasil dalam paging dalam laporan.

Dalam artikel ini, kami akan memperkenalkan cara melaksanakan pertanyaan halaman dalam prosedur tersimpan Oracle, dan menyediakan kod sampel mudah untuk membantu pembaca memahami dan menggunakan teknologi ini dengan lebih baik.

1. Prinsip asas pertanyaan paging

Dalam pertanyaan SQL umum, kita boleh menggunakan sintaks "SELECT * FROM table_name WHERE condition" untuk mendapatkan semula semua baris yang memenuhi syarat. Untuk melaksanakan pertanyaan paging, kami perlu memotong hasil pertanyaan mengikut bilangan halaman dan baris setiap halaman yang ditentukan, dan kemudian hanya mengembalikan data bilangan halaman yang ditentukan. Sebagai contoh, dalam halaman 1, kita boleh mendapatkan semula 10 baris pertama data, dalam halaman 2, kita boleh mendapatkan baris 11 hingga 20, dan seterusnya.

Mengikut prinsip ini, kami boleh menggunakan prosedur tersimpan Oracle untuk melaksanakan pertanyaan paging. Mula-mula, kita perlu mengira baris permulaan perolehan dan baris penamat dapatkan semula, kemudian gunakan fungsi "ROWNUM" untuk mengehadkan bilangan baris dalam hasil carian dan akhirnya mengembalikan hasil pertanyaan. Berikut ialah langkah pelaksanaan mudah:

1. Kira garisan permulaan dan garisan penamat.

Perlu diambil perhatian bahawa fungsi ROWNUM Oracle mengisih hasil pertanyaan sebelum ia dikembalikan, bukan sebelum pertanyaan. Oleh itu, jika kita menggunakan fungsi ROWNUM dalam pernyataan pertanyaan utama, hasilnya mungkin tidak tepat atau tidak dapat diramalkan. Untuk menyelesaikan masalah ini, kita boleh menggunakan pernyataan subquery untuk mendayakan fungsi ROWNUM. Sebagai contoh, dalam pernyataan berikut, kita boleh mengira baris mula dan akhir:

PILIH baris_mula, baris_akhir
DARI (
PILIH ROWNUM AS rnum, ((halaman_no - 1) page_size + 1) SEBAGAI baris_mula, (tidak_halaman saiz_halaman) SEBAGAI baris_akhir
DARI (

SELECT 1 AS page_no, 10 AS page_size FROM DUAL

)
)
WHERE rnum = 1;

at In contoh ini, kita mula-mula mentakrifkan bilangan baris dan halaman setiap halaman, dan kemudian mengira baris permulaan dan baris berakhir melalui pernyataan subquery. Pernyataan ini akan mengembalikan satu baris data, termasuk nilai baris permulaan dan akhir.

2. Dapatkan semula data.

Selepas mengira baris permulaan dan baris akhir, kita perlu menanyakan data yang memenuhi syarat. Menggunakan pernyataan subquery, kita boleh memilih semua baris yang memenuhi kriteria dan mengehadkan bilangan baris menggunakan fungsi ROWNUM. Sebagai contoh, dalam pernyataan berikut, kita boleh menanyakan bilangan halaman data yang ditentukan:

PILIH *
DARI (
PILIH ROWNUM sebagai rnum, t.*
DARI (

SELECT *
FROM table_name
WHERE condition
ORDER BY order_by

) t
)
WHERE rnum >= start_row DAN rnum <= end_row;

Dalam contoh ini, kami mula-mula mengisih data yang memenuhi kriteria, dan kemudian gunakan Fungsi ROWNUM mengehadkan keputusan. Akhir sekali, kami memilih hanya data antara baris mula yang ditentukan dan baris akhir daripada semua baris yang memenuhi syarat, dan akhirnya mengembalikan hasil pertanyaan.

2. Contoh pelaksanaan pertanyaan paging

Berikut ialah contoh prosedur tersimpan Oracle yang lengkap untuk melaksanakan pertanyaan paging:

BUAT ATAU GANTIKAN PROSEDUR PAGING_PROC(
i_page_no IN Integer,
i_page_size dalam integer,
o_records keluar sys_refcursor,
o_page_count out integer,
i_table_name dalam varchar2,
i_condition dalam varchar2,
IS
v_start_row INTEGER;
v_end_row INTEGER;
BEGIN
​​​​-- Langkah 1: Kira baris mula dan tamat
PILIH (i_page_no - 1)
i_page_size + 1, i_page . >
) WHERE rnum >= v_start_row AND rnum <= v_end_row;
-- Langkah 3: Kira kiraan halaman
PILIH CEIL(COUNT(*)/i_page_size)

KE DALAM o_page_count

FROM i_table_name
WHERE i_condition;
END PAGING_PROC;

Dalam contoh ini, kami memasukkan nombor halaman, saiz halaman, rekod output, nombor halaman dan nama jadual, Parameter seperti syarat dan pengasingan. Berdasarkan parameter input, kami mula-mula mengira baris permulaan dan baris penamat, kemudian gunakan pernyataan OPEN untuk membuka data output REFCUSOR.

SELECT ROWNUM AS rnum, t.*
FROM (
  SELECT *
  FROM i_table_name
  WHERE i_condition
  ORDER BY i_order_by
) t

Akhir sekali, kami mengira bilangan halaman dan mengeluarkan hasilnya. Sila ambil perhatian bahawa cara kami mengira bilangan halaman adalah dengan menggunakan fungsi COUNT agregat untuk membahagikan bilangan semua baris yang memenuhi syarat dengan bilangan baris setiap halaman dan bundarkan.

3. Kesimpulan

Dalam pangkalan data Oracle, prosedur tersimpan ialah teknologi operasi data yang penting. Dengan menggunakan prosedur tersimpan, kami boleh melaksanakan operasi data yang kompleks, seperti pertanyaan paging, kemas kini kelompok, import dan eksport data, dsb. Terutamanya dari segi pertanyaan paging, prosedur tersimpan Oracle boleh memberikan prestasi yang lebih tinggi dan keselamatan data yang lebih baik, dan juga boleh berinteraksi dengan mudah dengan antara muka program lain.

Dalam artikel ini, kami memperkenalkan cara melaksanakan pertanyaan halaman dalam prosedur tersimpan Oracle dan menyediakan kod sampel mudah. Kami menggalakkan pembaca mencuba teknik ini dalam aplikasi sebenar dan mengoptimumkan serta memanjangkannya mengikut keperluan mereka sendiri.

Atas ialah kandungan terperinci Bagaimana untuk melaksanakan pertanyaan paging dalam prosedur tersimpan Oracle. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!