procédure stockée de pagination Oracle

Oracle est un puissant système de gestion de base de données qui prend en charge des fonctionnalités avancées telles que les procédures stockées, permettant aux programmeurs d'écrire facilement une logique métier complexe. Dans certains scénarios spécifiques, il est nécessaire d'effectuer des requêtes de pagination sur une grande quantité de données. Pour y parvenir, nous pouvons écrire une procédure stockée paginée. Cet article explique comment écrire des procédures stockées de pagination Oracle.

1. Analyse des exigences

Dans le développement de sites Web, nous rencontrons souvent des situations où les données soumises par les utilisateurs doivent être affichées dans des pages. Par exemple, interroger tous les enregistrements d'une table exercera une pression importante sur les performances de la base de données et affectera l'expérience utilisateur si tous les enregistrements sont interrogés en même temps. Par conséquent, afficher les données dans des pages est une meilleure solution. Analysons les exigences :

1. Obtenir le nombre total d'enregistrements
2. Calculez la position de l'enregistrement de départ et de l'enregistrement de fin en fonction de la taille de chaque page et de la numéro de page actuel# 🎜🎜#
Interroger les données requises en fonction de l'enregistrement de début et de l'enregistrement de fin

2. Conception de procédure stockée

Basé sur l'analyse de la demande ci-dessus, nous pouvons concevoir la procédure stockée de pagination Oracle suivante :

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

Le code ci-dessus créera une procédure stockée nommée pagination, qui peut recevoir 4 paramètres : nom de la table, numéro de page, taille de chacune page et nombre total d’enregistrements. Parmi eux, p_cursor est le paramètre de sortie, qui est utilisé pour renvoyer le curseur des résultats de la requête.

3. Description de la procédure stockée

SELECT COUNT(*) INTO p_total_num FROM p_table_name;
1. Cette instruction est utilisée pour interroger le nombre total d'enregistrements dans le tableau et stockez le résultat dans p_total_num. Grâce à cette variable, nous pouvons calculer le nombre total de pages et la plage d'enregistrement de la page actuelle.
   
v_start_pos := (p_page_num - 1) * p_page_size + 1;
2. Cette instruction est utilisée pour calculer la position de l'enregistrement de départ, qui est calculée à partir du numéro de page et de la taille de chaque page.
   
v_end_pos := v_start_pos + p_page_size - 1;
3. Cette instruction est utilisée pour calculer la position de l'enregistrement de fin, qui est également calculée à partir du numéro de page et de la taille de chaque page.
   
v_sql_query := 'SELECT
4. FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM ' || p_table_name || ') A WHERE ROWNUM <= ' | | v_end_pos || ') WHERE RN >= ' || v_start_pos;Cette instruction est une instruction de requête, utilisée pour interroger des données dans la plage spécifiée dans la table. ROWNUM est une pseudo-colonne spécifique à Oracle qui représente le numéro de ligne de chaque enregistrement. Nous avons utilisé ROWNUM pour limiter la portée de la requête et ajouté la colonne RN via des requêtes imbriquées pour représenter le numéro de ligne de l'enregistrement actuel. Enfin, la plage des résultats de requête est limitée en fonction de la position de départ et de la position de fin.
   
OPEN p_cursor FOR v_sql_query
5. Cette instruction est utilisée pour exécuter l'instruction de requête et stocker les résultats dans le curseur p_cursor. Ce curseur peut être utilisé pour les opérations et transferts de données ultérieurs.
   

4. Cas de test

Afin de vérifier si la procédure stockée est correcte, nous pouvons créer une table de test et insérer des données dans la table : # 🎜🎜 #

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

Ensuite, nous pouvons exécuter le code suivant pour tester notre procédure stockée :

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

Le code ci-dessus affichera les valeurs d'identifiant et de nom de 1 à 10 enregistrements.

Il ressort des résultats des tests que la procédure stockée de pagination que nous avons écrite peut calculer correctement la plage d'enregistrements et que les résultats de la requête sont également corrects. Cette procédure stockée peut réduire efficacement la pression sur la base de données lors de l'interrogation des données, tout en évitant les problèmes de performances causés par l'interrogation d'un trop grand nombre de données à la fois.

De plus, nous pouvons également ajuster les paramètres et les instructions de requête dans la procédure stockée en fonction des besoins réels pour nous adapter à des scénarios de requêtes plus complexes.

5. Résumé

Dans la base de données Oracle, les procédures stockées sont une fonctionnalité très importante, qui peut nous aider à écrire des processus complexes de logique métier et d'exploitation des données et à améliorer l'efficacité opérationnelle et maintenabilité. Cet article explique comment écrire des procédures stockées de pagination Oracle. En analysant les exigences, en concevant des algorithmes et en écrivant du code, sur la base de la compréhension des procédures stockées Oracle, une procédure stockée de pagination simple est implémentée. En étudiant les cas présentés dans cet article, il aidera les lecteurs à mieux maîtriser les méthodes et techniques d'écriture de procédures stockées Oracle.

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!