Comment implémenter une requête de pagination dans une procédure stockée Oracle

Dans la base de données Oracle, une procédure stockée est un bloc réutilisable de code SQL qui peut être utilisé pour implémenter de nombreuses opérations de données complexes. Parmi elles, la requête de pagination est une exigence courante, comme l'affichage d'une liste de données de pagination dans une application Web ou l'affichage des résultats sous forme de pagination dans un rapport.

Dans cet article, nous présenterons comment implémenter des requêtes de pagination dans les procédures stockées Oracle et fournirons un exemple de code simple pour aider les lecteurs à mieux comprendre et appliquer cette technologie.

1. Principes de base de la requête de pagination

Dans les requêtes SQL générales, nous pouvons utiliser la syntaxe "SELECT * FROM table_name WHERE condition" pour récupérer toutes les lignes qui remplissent les conditions. Afin d'implémenter une requête de pagination, nous devons couper les résultats de la requête en fonction du nombre spécifié de pages et de lignes par page, puis renvoyer uniquement les données du nombre de pages spécifié. Par exemple, à la page 1, nous pouvons récupérer les 10 premières lignes de données, à la page 2, nous pouvons récupérer les lignes 11 à 20, et ainsi de suite.

Selon ce principe, nous pouvons utiliser des procédures stockées Oracle pour implémenter des requêtes de pagination. Tout d'abord, nous devons calculer la ligne de début de récupération et la ligne de fin de récupération, puis utiliser la fonction "ROWNUM" pour limiter le nombre de lignes dans les résultats de récupération, et enfin renvoyer les résultats de la requête. Voici des étapes de mise en œuvre simples :

1. Calculez la ligne de départ et la ligne d'arrivée.

Il est à noter que la fonction ROWNUM d'Oracle trie les résultats de la requête avant qu'ils ne soient renvoyés, et non avant la requête. Par conséquent, si nous utilisons la fonction ROWNUM dans l'instruction de requête principale, les résultats peuvent être inexacts ou imprévisibles. Pour résoudre ce problème, nous pouvons utiliser une instruction de sous-requête pour activer la fonction ROWNUM. Par exemple, dans l'instruction suivante, nous pouvons calculer les lignes de début et de fin :

SELECT start_row, end_row
FROM (
SELECT ROWNUM AS rnum, ((page_no - 1) page_size + 1) AS start_row, (page_no page_size ) AS end_row
FROM (

SELECT 1 AS page_no, 10 AS page_size FROM DUAL

)
)
WHERE rnum = 1;

Dans cet exemple, nous définissons d'abord le nombre de lignes et de pages par page, puis calculons la ligne de départ et la somme via la ligne de fin de l'instruction de sous-requête . Cette instruction renverra une ligne de données, y compris les valeurs de ligne de début et de fin.

2. Récupérer des données.

Après avoir calculé la ligne de départ et la ligne de fin, nous devons interroger les données qui remplissent les conditions. À l'aide d'une instruction de sous-requête, nous pouvons sélectionner toutes les lignes qui répondent aux critères et limiter le nombre de lignes à l'aide de la fonction ROWNUM. Par exemple, dans l'instruction suivante, nous pouvons interroger les données du nombre de pages spécifié :

SELECT *
FROM (
SELECT ROWNUM as rnum, t.*
FROM (

SELECT *
FROM table_name
WHERE condition
ORDER BY order_by

) t
)
WHERE rnum > ;= start_row AND rnum

Dans cet exemple, nous trions d'abord les données qui remplissent les conditions, puis utilisons la fonction ROWNUM pour limiter les résultats. Enfin, nous sélectionnons uniquement les données entre la ligne de début spécifiée et la ligne de fin parmi toutes les lignes qui remplissent les conditions, et renvoyons enfin les résultats de la requête.

2. Exemple d'implémentation d'une requête de pagination

Ce qui suit est un exemple complet de procédure stockée Oracle pour implémenter une requête de pagination :

CREATE OR REPLACE PROCEDURE PAGING_PROC(
i_page_no IN INTEGER,
i_page_size IN INTEGER,
o_records OUT SYS_REFC URSOR,
o_page_count OUT INTEGER,
i_table_name IN VARCHAR2,
i_condition IN VARCHAR2,
i_order_by IN VARCHAR2
)
IS
v_start_row INTEGER;
v_end_row INTEGER;
B EGIN
-- Étape 1 : Calculer la ligne de début et de fin
SELECT (i_page_no - 1 ) i_page_size + 1, i_page_no i_page_size
INTO v_start_row, v_end_row
FROM DUAL;

-- Étape 2 : Récupérer les données
OPEN o_records FOR
SELECT *
FROM (

SELECT ROWNUM AS rnum, t.*
FROM (
  SELECT *
  FROM i_table_name
  WHERE i_condition
  ORDER BY i_order_by
) t

)
WHERE rnum > = v_start_row ET rnum

-- Étape 3 : Calculer le nombre de pages
SELECT CEIL(COUNT(*)/i_page_size)
INTO o_page_count
FROM i_table_name
WHERE i_condition;
END PAGING_PROC;

Dans cet exemple, nous transmettons les paramètres tels que le numéro de page, la taille de la page, les enregistrements de sortie, le numéro de page et le nom de la table, les conditions et le tri sont inclus. Sur la base des paramètres d'entrée, nous calculons d'abord la ligne de début et la ligne de fin, puis utilisons l'instruction OPEN pour ouvrir une donnée de sortie REFCURSOR.

Enfin, nous comptons le nombre de pages et publions les résultats. Veuillez noter que la façon dont nous calculons le nombre de pages consiste à utiliser la fonction d'agrégation COUNT pour diviser le nombre de toutes les lignes qui remplissent la condition par le nombre de lignes par page et arrondir.

3. Conclusion

Dans la base de données Oracle, la procédure stockée est une technologie importante d'exploitation des données. En utilisant des procédures stockées, nous pouvons mettre en œuvre des opérations de données complexes, telles que des requêtes de pagination, des mises à jour par lots, l'importation et l'exportation de données, etc. En particulier en termes de requêtes de pagination, les procédures stockées Oracle peuvent offrir des performances supérieures et une meilleure sécurité des données, et peuvent également interagir facilement avec d'autres interfaces de programme.

Dans cet article, nous expliquons comment implémenter une requête paginée dans une procédure stockée Oracle et fournissons un exemple de code simple. Nous encourageons les lecteurs à essayer cette technique dans des applications réelles, à l'optimiser et à l'étendre en fonction de leurs propres besoins.

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!