paging prosedur tersimpan oracle

Untuk mengoptimumkan prestasi pertanyaan pangkalan data dengan lebih baik, menggunakan prosedur tersimpan untuk pertanyaan halaman adalah pilihan yang baik. Mewujudkan prosedur tersimpan membolehkan kami menggunakan sumber pada halaman apabila menanyakan sejumlah besar data. Pangkalan data Oracle menyediakan beberapa algoritma lanjutan yang boleh dilaksanakan untuk operasi halaman, menggunakan peratusan minimum untuk mendapatkan hasil yang diperlukan dan meningkatkan kecekapan pertanyaan.

Dalam Oracle, prosedur tersimpan ialah objek pangkalan data yang boleh digunakan untuk menyusun dan menyimpan penyata SQL. Prosedur tersimpan boleh menerima parameter dan mengembalikan hasil. Apabila memproses sejumlah besar data, menggunakan prosedur tersimpan boleh meningkatkan kecekapan pertanyaan pangkalan data.

Di bawah, kami akan memperkenalkan cara melaksanakan pertanyaan paging melalui prosedur tersimpan dalam Oracle.

Pertama, kita perlu menetapkan parameter prosedur tersimpan supaya ia boleh dinomborkan.

 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

Prosedur tersimpan ini menerima lima parameter input: nama jadual, syarat pertanyaan, syarat isihan, bilangan rekod yang dipaparkan pada setiap halaman dan nombor halaman. Antaranya, parameter in_table, in_pageSize dan in_pageNumber mesti diisi, manakala parameter in_where dan in_orderby adalah pilihan. out_cursor digunakan sebagai parameter output untuk mendapatkan hasil pertanyaan setiap halaman.

Seterusnya, kita perlu mempertimbangkan cara membuat penomboran dalam prosedur tersimpan, yang merupakan kunci kepada fungsi kita.

Kita boleh menggunakan prosedur tersimpan lain untuk melaksanakan paging. Seperti yang ditunjukkan di bawah, melalui gelung FOR LOOP, rekod yang terkandung dalam halaman yang ditentukan dalam set data diperoleh semula dan bilangan rekod yang akan dikembalikan dikira.

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
  )
);

Prosedur tersimpan ini akan mengembalikan hasil penomboran nombor halaman yang ditentukan Dua parameter in_where dan in_orderby digunakan untuk mengawal syarat dan kaedah pengisihan. Jika parameter in_where dan in_orderby tidak diluluskan, ini bermakna tiada syarat atau pesanan diperlukan.

Dalam gelung untuk, kami mula-mula mendapatkan semula bilangan rekod yang memenuhi syarat dalam keseluruhan set data, dan kemudian mengira jumlah bilangan halaman berdasarkan bilangan rekod yang dipaparkan pada setiap halaman Seterusnya, kami dapatkan semula data dalam halaman yang ditentukan Di sini kami menggunakan Row_num dan row_counter disingkirkan, row_num mewakili nombor baris, dan row_counter ialah jumlah bilangan baris yang dikira secara berurutan. Akhirnya, kami membuka kursor dan mengembalikan set hasil penomboran.

Dalam prosedur tersimpan, untuk memudahkan pemprosesan, kami menggunakan pernyataan EXECUTE IMMEDIATE untuk melaksanakan pernyataan SQL secara dinamik, jadi parameter v_sql perlu ditetapkan untuk menerima pernyataan SQL secara dinamik. Di samping itu, empat pembolehubah biasa perlu ditakrifkan untuk menyimpan nombor halaman semasa, jumlah bilangan halaman, baris permulaan dan baris penamat.

Selepas melakukan langkah di atas, Oracle akan dapat memproses logik paging seperti biasa. Sekarang kita boleh memanggil prosedur tersimpan dalam PL/SQL untuk mendapatkan semula data.

 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;

Dengan memanggil contoh di atas, kami boleh mendapatkan semula data halaman yang ditentukan dengan mudah dan mengembalikan set hasil sebagai parameter output. Antaranya, in_where dan in_orderBy boleh menyediakan syarat untuk julat data dan kaedah pengisihan anda.

Secara amnya, menggunakan prosedur tersimpan untuk halaman boleh meningkatkan prestasi pangkalan data apabila menanyakan sejumlah besar data. Apa yang perlu kita ambil perhatian ialah kecekapan pengendalian prosedur tersimpan adalah berkaitan dengan kualiti penulisan SQL, dan mesti diselaraskan dan dioptimumkan mengikut keadaan tertentu.

Melalui contoh di atas, saya percaya semua orang telah memahami bahawa prinsip asas menggunakan prosedur tersimpan untuk melaksanakan pertanyaan paging adalah untuk menjana pernyataan SQL secara dinamik untuk melaksanakan paging dengan menetapkan parameter paging. Menggunakan prosedur tersimpan secara berkesan boleh meringankan beban pertanyaan pangkalan data, meningkatkan kecekapan pertanyaan pangkalan data, dan mengurangkan bilangan permintaan sebelah pelayan.

Atas ialah kandungan terperinci paging prosedur tersimpan oracle. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!