prosedur tersimpan paging oracle

Oracle ialah sistem pengurusan pangkalan data yang berkuasa yang menyokong ciri lanjutan seperti prosedur tersimpan, menjadikannya mudah untuk pengaturcara menulis logik perniagaan yang kompleks. Dalam beberapa senario tertentu, adalah perlu untuk melakukan pertanyaan paging pada sejumlah besar data. Untuk mencapai ini, kita boleh menulis prosedur tersimpan bernombor. Artikel ini akan memperkenalkan cara menulis prosedur tersimpan paging Oracle.

1. Analisis Keperluan

Dalam pembangunan laman web, kami sering menghadapi situasi di mana data yang dihantar oleh pengguna perlu dipaparkan dalam halaman. Contohnya, menanyakan semua rekod dalam jadual akan memberikan tekanan prestasi yang serius pada pangkalan data dan menjejaskan pengalaman pengguna jika semua rekod disoal serentak. Oleh itu, memaparkan data dalam halaman adalah penyelesaian yang lebih baik. Mari analisa keperluan:

1. Dapatkan jumlah rekod
2. Mengikut saiz setiap halaman dan nombor halaman semasa, kira kedudukan rekod permulaan dan rekod penamat
3. Tanya data yang diperlukan berdasarkan rekod permulaan dan rekod tamat

2. Reka bentuk prosedur tersimpan

Berdasarkan analisis permintaan di atas, kami boleh mereka bentuk berikut Prosedur tersimpan paging Oracle:

CREATE OR REPLACE PROCEDURE pagination(p_table_name IN VARCHAR2,
                                        p_page_num IN NUMBER,
                                        p_page_size IN NUMBER,
                                        p_total_num OUT NUMBER,
                                        p_cursor OUT SYS_REFCURSOR) IS
  v_start_pos NUMBER;
  v_end_pos NUMBER;
  v_sql_query VARCHAR2(1000);
BEGIN
  SELECT COUNT(*) INTO p_total_num FROM p_table_name;
  v_start_pos := (p_page_num - 1) * p_page_size + 1;
  v_end_pos := v_start_pos + p_page_size - 1;
  v_sql_query := 'SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM ' || p_table_name || ') A WHERE ROWNUM <= ' || v_end_pos || ') WHERE RN >= ' || v_start_pos;
  OPEN p_cursor FOR v_sql_query;
END;
/

Kod di atas akan mencipta prosedur tersimpan bernama penomboran, yang boleh menerima 4 parameter: nama jadual, nombor halaman, saiz setiap halaman dan jumlah bilangan rekod. Antaranya, p_cursor ialah parameter output, yang digunakan untuk mengembalikan kursor hasil pertanyaan.

3. Perihalan prosedur tersimpan

1. PILIH COUNT(*) INTO p_total_num FROM p_table_name;
   Pernyataan ini digunakan untuk menanyakan jumlah rekod dalam jadual dan menyimpan keputusan p_jumlah_bilangan. Melalui pembolehubah ini, kita boleh mengira jumlah halaman dan julat rekod halaman semasa.
2. v_start_pos := (p_page_num - 1) * p_page_size + 1;
   Pernyataan ini digunakan untuk mengira kedudukan rekod permulaan, yang dikira daripada nombor halaman dan saiz setiap halaman.
3. v_end_pos := v_start_pos + p_page_size - 1;
   Pernyataan ini digunakan untuk mengira kedudukan rekod akhir, yang juga dikira daripada nombor halaman dan saiz setiap halaman.
4. v_sql_query := 'PILIH DARI (PILIH A., ROWNUM RN DARI (PILIH * DARI ' || p_table_name || ') A WHERE ROWNUM <= ' || v_end_pos || ') WHERE RN >= ' ||. v_start_pos;
   Pernyataan ini ialah pernyataan pertanyaan, digunakan untuk menanyakan data dalam julat yang ditentukan dalam jadual. ROWNUM ialah lajur pseudo khusus Oracle yang mewakili nombor baris setiap rekod. Kami menggunakan ROWNUM untuk mengehadkan skop pertanyaan dan menambah lajur RN melalui pertanyaan bersarang untuk mewakili nombor baris rekod semasa. Akhir sekali, julat hasil pertanyaan adalah terhad berdasarkan kedudukan permulaan dan kedudukan penamat.
5. BUKA p_kursor UNTUK v_sql_query
   Pernyataan ini digunakan untuk melaksanakan pernyataan pertanyaan dan menyimpan keputusan dalam kursor p_kursor ini boleh digunakan untuk operasi dan pemindahan data seterusnya.

4. Kes Ujian

Untuk mengesahkan sama ada prosedur yang disimpan adalah betul, kami boleh membuat jadual ujian dan memasukkan beberapa data ke dalam jadual:

CREATE TABLE test(
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50)
);

DECLARE 
  v_id NUMBER;
BEGIN
  FOR i IN 1..100 LOOP
    v_id := i;
    INSERT INTO test(id,name) VALUES(v_id,'name'||v_id);
  END LOOP;
  COMMIT;
END;
/

Seterusnya, kami boleh melaksanakan kod berikut untuk menguji prosedur tersimpan kami:

DECLARE
  v_page_num NUMBER := 1;
  v_page_size NUMBER := 10;
  v_total_num NUMBER;
  v_cursor SYS_REFCURSOR;
  v_id NUMBER;
  v_name VARCHAR2(50);
BEGIN
  pagination('test',v_page_num,v_page_size,v_total_num,v_cursor);
  
  DBMS_OUTPUT.PUT_LINE('Total number of records: ' || v_total_num);
  LOOP
    FETCH v_cursor INTO v_id,v_name;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Id: '||v_id ||', Name: '|| v_name);
  END LOOP;
  CLOSE v_cursor;
END;
/

Kod di atas akan mengeluarkan nilai id dan nama 1~10 rekod.

Daripada keputusan ujian, prosedur tersimpan paging yang kami tulis boleh mengira julat rekod dengan betul, dan keputusan pertanyaan juga betul. Prosedur tersimpan ini boleh mengurangkan tekanan pada pangkalan data dengan berkesan apabila menanyakan data, dan pada masa yang sama mengelakkan masalah prestasi yang disebabkan oleh menanya terlalu banyak data pada satu masa.

Selain itu, kami juga boleh melaraskan parameter dan pernyataan pertanyaan dalam prosedur tersimpan mengikut keperluan sebenar untuk menyesuaikan diri dengan senario pertanyaan yang lebih kompleks.

5. Ringkasan

Dalam pangkalan data Oracle, prosedur tersimpan adalah ciri yang sangat penting. Ia boleh membantu kami menulis logik perniagaan dan proses operasi data, meningkatkan kecekapan dan kecekapan operasi pangkalan data . Artikel ini memperkenalkan cara menulis prosedur tersimpan paging Oracle Dengan menganalisis keperluan, mereka bentuk algoritma dan menulis kod, berdasarkan pemahaman prosedur tersimpan Oracle, prosedur tersimpan paging yang mudah dilaksanakan. Dengan mengkaji kes dalam artikel ini, pembaca boleh menguasai kaedah dan teknik menulis prosedur tersimpan Oracle dengan lebih baik.

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