pagination de procédures stockées Oracle

Afin de mieux optimiser les performances des requêtes de base de données, l'utilisation de procédures stockées pour les requêtes de pagination est un bon choix. L'établissement d'une procédure stockée nous permet de consommer des ressources lors de la pagination lors de l'interrogation de grandes quantités de données. La base de données Oracle fournit plusieurs algorithmes avancés réalisables pour les opérations de pagination, utilisant le pourcentage minimum pour récupérer les résultats requis et améliorer l'efficacité des requêtes.

Dans Oracle, les procédures stockées sont des objets de base de données qui peuvent être utilisés pour précompiler et enregistrer des instructions SQL. Les procédures stockées peuvent accepter des paramètres et renvoyer des résultats. Lors du traitement de grandes quantités de données, l'utilisation de procédures stockées peut améliorer considérablement l'efficacité des requêtes dans la base de données.

Ci-dessous, nous présenterons comment implémenter une requête de pagination via des procédures stockées dans Oracle.

Tout d'abord, nous devons définir les paramètres de la procédure stockée afin qu'elle puisse être paginée.

 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

Cette procédure stockée accepte cinq paramètres d'entrée : nom de la table, conditions de requête, conditions de tri, nombre d'enregistrements affichés sur chaque page et numéro de page. Parmi eux, les paramètres in_table, in_pageSize et in_pageNumber doivent être renseignés, tandis que les paramètres in_where et in_orderby sont facultatifs. out_cursor est utilisé comme paramètre de sortie pour obtenir les résultats de la requête de chaque page.

Ensuite, nous devons réfléchir à la manière de paginer dans la procédure stockée, qui est la clé de notre fonctionnalité.

Nous pouvons utiliser une autre procédure stockée pour implémenter la pagination. Comme indiqué ci-dessous, via la boucle FOR LOOP, les enregistrements contenus dans la page spécifiée de l'ensemble de données sont récupérés et le nombre d'enregistrements à renvoyer est calculé.

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

Cette procédure stockée renverra les résultats paginés du numéro de page spécifié. Les deux paramètres in_where et in_orderby sont utilisés pour contrôler les conditions et la méthode de tri. Si les paramètres in_where et in_orderby ne sont pas transmis, cela signifie qu'aucune condition ni aucun ordre n'est requis.

Dans la boucle for, nous récupérons d'abord le nombre d'enregistrements qui remplissent les conditions dans l'ensemble de données, puis calculons le nombre total de pages en fonction du nombre d'enregistrements affichés sur chaque page. Ensuite, nous récupérons les données dans. la page spécifiée. Ici, nous utilisons row_num et row_counter , row_num représente le numéro de ligne, row_counter est le nombre total de lignes comptées séquentiellement. Enfin, nous ouvrons le curseur et renvoyons le jeu de résultats paginé.

Dans la procédure stockée, afin de faciliter le traitement, nous utilisons l'instruction EXECUTE IMMEDIATE pour exécuter dynamiquement l'instruction SQL, nous devons donc définir le paramètre v_sql pour recevoir dynamiquement l'instruction SQL. De plus, quatre variables communes doivent être définies pour enregistrer le numéro de page actuel, le nombre total de pages, la ligne de début et la ligne de fin.

Après avoir effectué les étapes ci-dessus, Oracle sera en mesure de traiter la logique de pagination normalement. Nous pouvons maintenant appeler la procédure stockée en PL/SQL pour récupérer les données.

 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;

Grâce à la méthode d'appel dans l'exemple ci-dessus, nous pouvons facilement récupérer les données de la page spécifiée et renvoyer l'ensemble de résultats en tant que paramètre de sortie. Parmi eux, in_where et in_orderBy peuvent fournir des conditions pour votre plage de données et votre méthode de tri.

En général, l'utilisation de procédures stockées pour la pagination peut améliorer les performances de la base de données lors de l'interrogation de grandes quantités de données. Ce qu'il faut noter, c'est que l'efficacité opérationnelle des procédures stockées est liée à la qualité de l'écriture SQL et doit être ajustée et optimisée en fonction de conditions spécifiques.

Grâce aux exemples ci-dessus, je pense que tout le monde a compris que le principe de base de l'utilisation de procédures stockées pour implémenter des requêtes de pagination est de générer dynamiquement des instructions SQL pour implémenter la pagination en définissant des paramètres de pagination. L'utilisation de procédures stockées peut efficacement alléger le fardeau des requêtes de base de données, améliorer l'efficacité des requêtes de base de données et réduire le nombre de requêtes côté serveur.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!