IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Boucle de mise à jour de séquences


Sujet :

PL/SQL Oracle

  1. #1
    Membre du Club
    Homme Profil pro
    Consultant communication & réseaux
    Inscrit en
    Novembre 2014
    Messages
    57
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Consultant communication & réseaux

    Informations forums :
    Inscription : Novembre 2014
    Messages : 57
    Points : 50
    Points
    50
    Par défaut Boucle de mise à jour de séquences
    Bonjour à tous,

    Voilà j'aimerais faire une procedure me permettant de sortir les sequences oracle à mettre à jour. Je dois ajouter le DROP et le CREATE SEQUENCE
    dans le cas ou le MAXID de ma table soit incohérent avec sa séquence mais je ne sais pas de quelle facon de facon a ce que ce soit le plus optimal possible.
    J'aimerais que les requêtes soient en output de facon à les passer à la mano pour eviter les erreurs.
    Je vous mets le bout de code pl sql que j'ai commencé à faire

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
     
    SET SERVEROUTPUT ON
    DECLARE
      difference         INTEGER;
      sqlstmt            VARCHAR2(255) ;
      sqlstmt2           VARCHAR2(255) ;
      sqlstmt3           VARCHAR2(255) ;
      nextsequencevalue      NUMBER;
      sequencename       VARCHAR2(30) ;
      sequencelastnumber INTEGER;
      CURSOR allseq
      IS
         SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
    BEGIN
      DBMS_OUTPUT.enable(64000) ;
      OPEN allseq;
      LOOP
        FETCH allseq INTO sequencename, sequencelastnumber;
        EXIT
      WHEN allseq%NOTFOUND;
     
      -- 1.recupère le maxid de la table
      sqlstmt2 := 'select max(' || regexp_replace(sequencename,'ID_SEQ|_SEQ','ID')||') from ' || regexp_replace(sequencename,'ID_SEQ|_SEQ','') ;
      DBMS_OUTPUT.PUT_LINE(sqlstmt2);
     
      -- 2.on stocke le resultat dans la variable sequencelastnumber
      execute immediate sqlstmt2 into sequencelastnumber;
      DBMS_OUTPUT.PUT_LINE('maxid:' ||sequencelastnumber) ;
     
      -- 3.calcul de la prochaine valeur de la séquence
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
     
      -- 4. stockage de la prochaine 
      execute immediate sqlstmt3 into nextsequencevalue;
      DBMS_OUTPUT.PUT_LINE('next value is:' ||nextsequencevalue) ;
     
      END LOOP;
      CLOSE allseq;
    END;
    merci pour votre aide

  2. #2
    McM
    McM est déconnecté
    Expert éminent

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Points : 7 740
    Points
    7 740
    Billets dans le blog
    4
    Par défaut
    Bonjour,

    Privilégie le FOR LOOP que la déclaration de Curseur quand c'est possible, le code sera plus lisible et plus performant

    Pour le point 3, je pense qu'il est inutile voire déconseillé de faire un NEXTVAL sur toutes tes séquences, d'autant plus que tu as récupéré le last_number dans le curseur.
    Attention à cause du cache, le LAST_NUMBER correspond au début du prochain groupe de numéro
    exemple Séquence pas dans le cache,
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT cache_size, last_number FROM user_sequences WHERE sequence_name = 'MASEQ';
    CACHE_SIZE 20
    LAST_NUMBER 58
     
    select Maseq.nextval from dual => 58
     
    SELECT last_number FROM user_sequences WHERE sequence_name = 'MASEQ';
    LAST_NUMBER 78
     
    select Maseq.nextval from dual => 59
    Si ton but est de recréer les séquences avec le max des tables si l'ID des tables est supérieur
    Attention, A vérifier, exemple il n'y a aucun droit de mis dans la création, et pour le flag ORDER je ne suis pas sûr que ce soit le bon ordre.


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    SET SERVEROUTPUT ON
    DECLARE
      sqlstmt           VARCHAR2(500) ;
      IdMaxTable INTEGER;
     
    BEGIN
      DBMS_OUTPUT.enable(64000) ;
     
      FOR rc IN (SELECT sequence_name, last_number, min_value, max_value, cache_value, cycle_flag, order_flag FROM user_sequences ORDER BY sequence_name)
      LOOP
     
    		-- 1.recupère le maxid de la table
    		sqlstmt2 := 'select max(' || REGEXP_REPLACE(rc.sequencename,'ID_SEQ|_SEQ','ID')||') from ' || REGEXP_REPLACE(rc.sequencename,'ID_SEQ|_SEQ','') ;
    		DBMS_OUTPUT.PUT_LINE(sqlstmt);
     
    		-- 2.on stocke le resultat dans la variable IdMaxTable
    		execute IMMEDIATE sqlstmt INTO IdMaxTable;
    		DBMS_OUTPUT.PUT_LINE('maxid:'|| IdMaxTable) ;
     
    	-- 3 Si l'Id de la table est supérieur à la séquence on recrée la séquence
    		IF IdMaxTable > rc.last_number
    		THEN
    				-- On droppe la séquence
    				sqlstmt2 := 'DROP SEQUENCE '|| rc.sequencename;
    				DBMS_OUTPUT.PUT_LINE(sqlstmt);
    				EXECUTE IMMEDIATE sqlstmt;
     
    				-- On recrée la séquence
    				sqlstmt := 'CREATE SEQUENCE '|| rc.sequencename ||' START WITH '|| (IdMaxTable + 1) ||' MAXVALUE '|| rc.max_value 
    													||' MINVALUE '|| rc.min_value || CASE WHEN rc.cycle_flag = 'N' THEN ' NOCYCLE' ELSE ' CYCLE' END
    													||' CACHE '|| rc.cache_value
    													|| CASE WHEN rc.order_flag = 'N' THEN ' NOORDER' ELSE ' ORDER' END;
    				DBMS_OUTPUT.PUT_LINE(sqlstmt);
    				EXECUTE IMMEDIATE sqlstmt;
     
    		END IF;
    	END LOOP;
    END;

    Mais je pense que le plus simple sans recréer serait de récupérer X fois le NEXTVAL pour arriver au numéro souhaité, ça éviterait de devoir dropper et recréer
    More Code : More Bugs. Less Code : Less Bugs
    Mon Blog PL/Sql : Fichier Zip / Image BMP / Lire sqliteDB / QRCode et Images PNG ou BMP

  3. #3
    Membre chevronné
    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Décembre 2019
    Messages
    1 135
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Décembre 2019
    Messages : 1 135
    Points : 1 913
    Points
    1 913
    Par défaut
    Bonjour,

    Mais en faisant ça tu seras forcément en décalage avec ta séquence puisqu'il faudra faire un NEXTVAL pour connaitre le prochain numéro, sauf si coup de chance tu tombes sur le même. D'autant plus que des numéros de la séquence sont certainement en cache (dans la SGA), donc tu ne pourras pas vraiment te fier à LAST_NUMBER de user_sequences.

    Pour rappel il est impossible de garantir des séquences sans trous pour en attribuer les numéros de manière strictement incrémentale. Mais vu qu'il s'agit d'une id quel est le but de la manœuvre ici?

  4. #4
    McM
    McM est déconnecté
    Expert éminent

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Points : 7 740
    Points
    7 740
    Billets dans le blog
    4
    Par défaut
    Je pense que l'ID des tables est supérieur à la séquence qui est mise dans cet ID, et que donc tu vas arriver à un moment à un PK_VIOLATED.
    More Code : More Bugs. Less Code : Less Bugs
    Mon Blog PL/Sql : Fichier Zip / Image BMP / Lire sqliteDB / QRCode et Images PNG ou BMP

  5. #5
    Membre du Club
    Homme Profil pro
    Consultant communication & réseaux
    Inscrit en
    Novembre 2014
    Messages
    57
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Consultant communication & réseaux

    Informations forums :
    Inscription : Novembre 2014
    Messages : 57
    Points : 50
    Points
    50
    Par défaut
    Hello,

    désolé pour ce retour tardif. Un grand merci à vous pour vos retours et votre aide.
    L'histoire du cache est intéressante merci d'avoir préciser ce point.

    j'aurais une derniere question: ma requete pour déduire le nom de la table (sqlstmt2)
    n'est finalement pas très fiable. Dans certains cas je retrouve bien le nom de la table à partir du nom de la séquence
    dans d'autres non.

    Est ce qu'il existe un moyen de retrouver le nom d'une table associée à la séquence?
    Je cherche depuis quelques jours sur le net mais visiblement de ce que je peux lire ce n'est pas si simple
    voire impossible, vous confirmez ?

    Encore merci

  6. #6
    McM
    McM est déconnecté
    Expert éminent

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Points : 7 740
    Points
    7 740
    Billets dans le blog
    4
    Par défaut
    En effet, impossible d'être sûr à 100% de qui utilise une séquence pour mettre dans quelle table.

    tu peux avoir un trigger, une procédure, un écran Forms, un code dans un report, la même séquence utilisée sur plusieurs tables, etc...
    More Code : More Bugs. Less Code : Less Bugs
    Mon Blog PL/Sql : Fichier Zip / Image BMP / Lire sqliteDB / QRCode et Images PNG ou BMP

  7. #7
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    En effet, une table et une séquence sont deux objets complètement dissociés.
    Une séquence peut être utilisée par 0 à n tables, mais aussi par du code SQL simple.

    Dans la pratique on retrouve "souvent" (je ne pense pas qu'on puisse faire une état des lieux global) une association tacite une séquence une table, mais ça n'a rien d'obligatoire.

    Edit: dépassé par McM !

  8. #8
    Membre du Club
    Homme Profil pro
    Consultant communication & réseaux
    Inscrit en
    Novembre 2014
    Messages
    57
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Consultant communication & réseaux

    Informations forums :
    Inscription : Novembre 2014
    Messages : 57
    Points : 50
    Points
    50
    Par défaut
    Merci @McM et @Waldar ! je vais essayer de trouver une autre facon de faire pour que ca réponde a mon cas.

    Sujet clos

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2010] Boucle de mise à jour incomplète
    Par adriendb dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 14/07/2017, 18h18
  2. [XL-2010] Boucle de mise à jour
    Par Fred_rt dans le forum Macros et VBA Excel
    Réponses: 7
    Dernier message: 29/04/2015, 13h17
  3. Réponses: 16
    Dernier message: 07/05/2009, 14h23
  4. Boucle de mise à jour SQL
    Par ChrisMan dans le forum Langage SQL
    Réponses: 3
    Dernier message: 29/05/2007, 16h30
  5. Mise à jour des séquences
    Par delphyjp dans le forum Oracle
    Réponses: 10
    Dernier message: 20/01/2006, 23h44

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo