Oracle Stored Procedure Paging

Um die Leistung von Datenbankabfragen besser zu optimieren, ist die Verwendung gespeicherter Prozeduren für Paging-Abfragen eine gute Wahl. Durch die Einrichtung einer gespeicherten Prozedur können wir beim Abfragen großer Datenmengen Ressourcen beim Paging verbrauchen. Die Oracle-Datenbank bietet mehrere praktikable erweiterte Algorithmen für Paging-Vorgänge, die den Mindestprozentsatz verwenden, um die erforderlichen Ergebnisse abzurufen und die Abfrageeffizienz zu verbessern.

In Oracle sind gespeicherte Prozeduren Datenbankobjekte, die zum Vorkompilieren und Speichern von SQL-Anweisungen verwendet werden können. Gespeicherte Prozeduren können Parameter akzeptieren und Ergebnisse zurückgeben. Bei der Verarbeitung großer Datenmengen kann die Verwendung gespeicherter Prozeduren die Effizienz von Datenbankabfragen erheblich verbessern.

Im Folgenden stellen wir vor, wie Paging-Abfragen über gespeicherte Prozeduren in Oracle implementiert werden.

Zuerst müssen wir die Parameter der gespeicherten Prozedur festlegen, damit sie paginiert werden kann.

 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

Diese gespeicherte Prozedur akzeptiert fünf Eingabeparameter: Tabellenname, Abfragebedingungen, Sortierbedingungen, Anzahl der auf jeder Seite angezeigten Datensätze und Seitennummer. Darunter müssen die Parameter in_table, in_pageSize und in_pageNumber ausgefüllt werden, während die Parameter in_where und in_orderby optional sind. out_cursor wird als Ausgabeparameter verwendet, um die Abfrageergebnisse jeder Seite zu erhalten.

Als nächstes müssen wir überlegen, wie wir in der gespeicherten Prozedur paginieren, was der Schlüssel zu unserer Funktionalität ist.

Wir können eine andere gespeicherte Prozedur verwenden, um Paging zu implementieren. Wie unten gezeigt, werden durch die FOR LOOP-Schleife die auf der angegebenen Seite im Datensatz enthaltenen Datensätze abgerufen und die Anzahl der zurückzugebenden Datensätze berechnet.

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

Diese gespeicherte Prozedur gibt die paginierten Ergebnisse der angegebenen Seitenzahl zurück, wobei die beiden Parameter in_where und in_orderby zur Steuerung der Bedingungen und der Sortiermethode verwendet werden. Wenn die Parameter in_where und in_orderby nicht übergeben werden, bedeutet dies, dass keine Bedingung oder Reihenfolge erforderlich ist.

In der for-Schleife rufen wir zunächst die Anzahl der Datensätze ab, die die Bedingungen im gesamten Datensatz erfüllen, und berechnen dann die Gesamtzahl der Seiten basierend auf der Anzahl der auf jeder Seite angezeigten Datensätze , rufen wir die Daten auf der angegebenen Seite ab. Hier haben wir row_num und row_counter verwendet, row_num stellt die Zeilennummer dar und row_counter ist die Gesamtzahl der nacheinander gezählten Zeilen. Schließlich öffnen wir den Cursor und geben die paginierte Ergebnismenge zurück.

Um die Verarbeitung zu erleichtern, verwenden wir in der gespeicherten Prozedur die EXECUTE IMMEDIATE-Anweisung, um die SQL-Anweisung dynamisch auszuführen. Daher müssen wir den Parameter v_sql festlegen, um die SQL-Anweisung dynamisch zu empfangen. Darüber hinaus müssen vier gemeinsame Variablen definiert werden, um die aktuelle Seitenzahl, die Gesamtseitenzahl, die Startzeile und die Endzeile zu speichern.

Nachdem die oben genannten Schritte ausgeführt wurden, kann Oracle die Paging-Logik normal verarbeiten. Jetzt können wir die gespeicherte Prozedur in PL/SQL aufrufen, um die Daten abzurufen.

 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;

Durch die Aufrufmethode im obigen Beispiel können wir die Daten der angegebenen Seite einfach abrufen und die Ergebnismenge als Ausgabeparameter zurückgeben. Unter anderem können in_where und in_orderBy Bedingungen für Ihren Datenbereich und Ihre Sortiermethode bereitstellen.

Im Allgemeinen kann die Verwendung gespeicherter Prozeduren für Paging die Datenbankleistung beim Abfragen großer Datenmengen verbessern. Wir müssen beachten, dass die Betriebseffizienz gespeicherter Prozeduren mit der Qualität des SQL-Schreibens zusammenhängt und entsprechend den spezifischen Bedingungen angepasst und optimiert werden muss.

Anhand der obigen Beispiele glaube ich, dass jeder verstanden hat, dass das Grundprinzip der Verwendung gespeicherter Prozeduren zur Implementierung von Paging-Abfragen darin besteht, dynamisch SQL-Anweisungen zu generieren, um Paging durch Festlegen von Paging-Parametern zu implementieren. Durch die Verwendung gespeicherter Prozeduren kann die Belastung durch Datenbankabfragen effektiv verringert, die Effizienz von Datenbankabfragen verbessert und die Anzahl serverseitiger Anforderungen verringert werden.

Das obige ist der detaillierte Inhalt vonOracle Stored Procedure Paging. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!