Procédures stockées de pagination Oracle : rendre les recherches de pagination plus efficaces

Avec le développement d'Internet, l'application des bases de données est devenue de plus en plus populaire et une grande quantité de données doit être interrogée et traitée. Dans ce processus, la recherche par pagination est l'une des exigences les plus courantes. Bien que la base de données Oracle fournisse ROWNUM comme moyen d'implémenter des requêtes de pagination, elle est confrontée à de nombreuses limitations et lacunes dans le processus de candidature réel. Par conséquent, le développement de procédures stockées de pagination Oracle est devenu une solution plus efficace.

Cet article combinera des cas réels pour présenter comment concevoir une procédure stockée de pagination efficace basée sur la base de données Oracle.

1. Conception globale

L'architecture de conception de la procédure stockée de pagination Oracle peut être divisée en quatre parties principales : la transmission des paramètres, la requête de données, le calcul de la pagination des données et le retour des résultats.

1. Passage des paramètres : transmettez l'instruction de requête, le numéro de la page actuelle, le nombre d'enregistrements par page, etc. comme paramètres d'entrée dans la procédure stockée.
2. Requête de données : requête basée sur l'instruction de requête entrante pour obtenir des données qui remplissent les conditions.
3. Calcul de pagination de données : effectuez un traitement de pagination sur les résultats de la requête et calculez les données du numéro de page spécifié.
4. Renvoyer les résultats : renvoie les données du numéro de page spécifié et le nombre total d'enregistrements.

2. Implémentation spécifique

Dans le processus d'implémentation des procédures stockées de pagination Oracle, vous devez utiliser certaines syntaxes et fonctions spécifiques à Oracle. Il y a donc quelques bases qu’il faut d’abord comprendre.

1. Fonction ROW_NUMBER

La fonction ROW_NUMBER est un mot-clé dans Oracle qui est utilisé pour compter le nombre de lignes dans un ensemble de résultats. Son résultat de retour est une valeur entière, classée par ordre croissant de lignes. Voici un exemple :

SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
FROM emp;

La fonction ROW_NUMBER dans l'exemple ci-dessus triera par numéro d'employé et attribuera un numéro de série croissant. Ceci est très important pour les requêtes paginées.

2. Requête d'enregistrement Top N

Comment implémenter la requête d'enregistrement Top N ? Oracle propose deux méthodes : les fonctions ROWNUM et ROW_NUMBER(). Comme indiqué ci-dessous :

SELECT *
FROM (
    SELECT emp.*, ROWNUM rn
    FROM emp
) t
WHERE t.rn <= N;

ou

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno <= N;

Remplacez simplement N par le nombre d'enregistrements que vous devez interroger.

3. Requête de page

La requête de page est un scénario typique, vous devez généralement spécifier le numéro de page à afficher et le nombre d'enregistrements par page. Parmi eux, OFFSET spécifie l'index de départ des données affichées sur chaque page et LIMIT spécifie le nombre maximum d'enregistrements affichés sur chaque page. Comme indiqué ci-dessous :

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno > OFFSET AND t.rowno <= OFFSET+LIMIT;

Parmi eux, OFFSET et LIMIT peuvent être ajustés en fonction de besoins spécifiques pour obtenir des requêtes de pagination flexibles.

3. Implémentation du code

Ce qui suit est un exemple de procédure stockée de pagination Oracle complète :

CREATE OR REPLACE PROCEDURE paginating_demo (
    p_sql       IN    VARCHAR2,    --带有占位符(:P1,:P2...)的查询语句
    p_curPage   IN    NUMBER,      --当前页码
    p_pageSize  IN    NUMBER,      --每页的记录数量
    p_recordset OUT   SYS_REFCURSOR,--查询结果集
    p_total     OUT   NUMBER       --记录的总数
)
AS
  v_sql VARCHAR2(4000); 
  v_fromIndex NUMBER; 
  v_toIndex NUMBER;

BEGIN
  SELECT COUNT(*) INTO p_total FROM ( p_sql );

  IF (p_total > 0) THEN
    -- 计算 limit 和offset 边界值
    v_fromIndex := ((p_curPage - 1) * p_pageSize);
    v_toIndex   := (p_curPage * p_pageSize);

    v_sql := 'SELECT * FROM ( SELECT t.*, ROWNUM RN FROM ( ' || p_sql || ' ) t WHERE ROWNUM <= &#39; || v_toIndex || &#39; ) WHERE RN > ' || v_fromIndex;

    OPEN p_recordset FOR v_sql;
  END IF;

END paginating_demo;

Ce code prend l'instruction de requête, le numéro de page actuel, le nombre d'enregistrements par page, l'ensemble de résultats de la requête et le nombre total d'enregistrements. paramètres d'entrée et de sortie. Parmi eux, l'ensemble des résultats de la requête et le nombre total d'enregistrements seront renvoyés comme paramètres de sortie.

4. Résumé

Dans les applications réelles, les procédures stockées de pagination Oracle améliorent considérablement l'efficacité et la stabilité des requêtes de pagination. En maîtrisant les connaissances et compétences ci-dessus, nous pouvons utiliser de manière flexible les procédures stockées de pagination Oracle dans la pratique pour améliorer considérablement les performances du système et l'expérience utilisateur.

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!