[PL/SQL] Ordonnancement, déplacement d'un bloque

Version imprimable

[Oracle 9i]

Bonjour,

Voici les ordres et les libellés de la table QUESTIONS:

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I

Je désir mettre en place une fonctionnalité permettant de déplacer un bloque de question. C'est à dire que l'utilisateur indique la borne de début, la borne de fin et la question précédente.
Par exemple, pour les paramètres suivants:
Borne de début: E
Borne de fin : G
Question précédente : B
On doit obtenir le résultat suivant:

1 A
2 B
3 E
4 F
5 G
6 C
7 D
8 H
9 I

Je suis parti en me disant que je devrai faire la mise à jour des ordres en 2 temps:
1. Mise à jour des ordres des questions qui se touvent entre la question précédente et la borne de début:
Code:

1 2 3 4 5 UPDATE QUESTIONS SET ordre = ordre + vl_blocksize WHERE (ordre > vl_ordrequestprec) AND (ordre < vl_ordrebornedeb);
2. Mise à jour des ordres des questions qui se trouvent dans le bloque:
Code:

1 2 3 4 5 UPDATE QUESTIONS SET ordre = ordre + vl_intervalsize WHERE (ordre >= vl_ordrebornedeb) AND (ordre <= vl_ordrebornefin);
Remarques:
Code:

1 2 3 4 5 6 /* vl_blocksize: Nb d'éléments qui se trouvent dans le blque de question à déplacer (dans l'ex. 3). vl_ordrequestprec: Ordre de la question précédente (dans l'ex. 2). vl_ordrebornedeb : Ordre de 1ere question du bloque (dans l'ex 5). vl_ordrebornefin : Ordre de dernière question du bloque (dans l'ex 7). */
Cette méthode ne fonctionne pas car une fois mon permier update executé, j'ai plusieurs fois le même ordre:
1 A
2 B
6 C
7 D
5 E
6 F
7 G
8 H
9 I
Ensuite, dans mon exemple, mon deuxième update met également à jour les ordres des questions C et D et ce n'est pas ce qui est voulu :cry: ! bref, pour notre exemple, nous arrivons au resultat final suivant:
1 A
2 B
3 E
4 C
4 F
5 D
5 G
8 H
9 I
Zut!

Est-ce que quelqu'un peut me dire comment éviter que les enregistrement mis à jour lors du 1er update ne soient pas mis à jour dans le 2ème?
Moi je verrai éventuellement l'ajout d'une colonne "jeton" dans ma table QUESTIONS. Lors du 1er update je metterai le jeton à "vrai" et dans le 2ème, je ne metterai pas à jour les enregistrements dont le jeton est à "vrai".
Voyez-vous une autre solution?
Merci du coup de main.

Pourquoi ne pas faire tout en un seul update :

Code:

1
2
3
4
5
6
7
8
9
update test_dvp
set ordre = case 
            when ordre between v_ordre_quest_prec + 1 and v_ordre_borne_deb -1 then
                 ordre + v_ordre_borne_fin - v_ordre_borne_deb + 1
            when ordre between v_ordre_borne_deb  and v_ordre_borne_fin then
                 ordre - (v_ordre_borne_fin - v_ordre_borne_deb)
            else
                 ordre
            end

L'idée c'est de réussir à faire toues les modifs en une seule passe. En fait il faut trouver la formule de calcul pour transformer les numéros d'ordre:

Pour mieux comprendre je pense qu'il faut poser le problème comme ceci:

A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9

se transforme en (j'ai classé par lettre et non par numéro):

A 1
B 2
C 6
D 7
E 3
F 4
G 5
H 8
I 9

Donc il faut trouver une formule qui fait la transformation suivante:
1 -> 1
2 -> 2
3 -> 6
4 -> 7
5 -> 3
6 -> 4
7 -> 5
8 -> 8
9 -> 9

On peut voir que ça vérifie l'algo suivant:
- vl_blocksize : 3 (taille du bloc)
- vl_ordrequestprec : 2 (décalage negatif)
- vl_ordrebornedeb : 5 (ofset du bloc à intervertir)
- (je pense qu'il y a un paramètre de trop dans ton énoncé, on a pas besoin du dernier ofset puisqu'on a la taille du bloc)

Citation:

si num_ordre >= 5 et num_ordre < 5+3
alors num_ordre = num_ordre -2
sinon-si num_ordre >= (5 - 2) et num_ordre < (5 -2) +3
alors num_ordre = num_ordre + 2
sinon
num_ordre = num_ordre

On peu se passer du dernier "sinon" en disant qu'on ne modifie les lignes qu'à la condition:

Citation:

(num_ordre >= 5 et num_ordre < 5+3) ou (num_ordre >= (5 - 2) et num_ordre < (5 -2) +3)

la traduction de cette formule peut se faire avec le CASE du sql, en remplaçant les constantes par les variable ça nous donne donc:
Code:

1 2 3 4 5 6 7 8 9 UPDATE QUESTIONS SET ordre = CASE WHEN (ordre >= vl_ordrebornedeb AND ordre < vl_ordrebornedeb + vl_blocksize) THEN ordre - vl_ordrequestprec WHEN (ordre >= (vl_ordrebornedeb - vl_ordrequestprec) AND ordre < (vl_ordrebornedeb - vl_ordrequestprec + vl_blocksize) THEN ordre + vl_ordrequestprec END WHERE (ordre >= vl_ordrebornedeb AND ordre < vl_ordrebornedeb + vl_blocksize) OR (ordre >= (vl_ordrebornedeb - vl_ordrequestprec) AND ordre < (vl_ordrebornedeb - vl_ordrequestprec + vl_blocksize)

Citation:
Envoyé par plaineR
Pourquoi ne pas faire tout en un seul update :
Code:

1 2 3 4 5 6 7 8 9 update test_dvp set ordre = case when ordre between v_ordre_quest_prec + 1 and v_ordre_borne_deb -1 then ordre + v_ordre_borne_fin - v_ordre_borne_deb + 1 when ordre between v_ordre_borne_deb and v_ordre_borne_fin then ordre - (v_ordre_borne_fin - v_ordre_borne_deb) else ordre end
Tiens y'a eu croisage de réponse ;)

par contre j'ai l'impression qu'on a pas compris l'énoncé tout à fait pareil parceque je vois pas pourquoi tu démarres le 1ier between par "v_ordre_quest_prec + 1" ... :koi:

27/10/2006, 10h37
plaineR

Citation:

Envoyé par remi4444

je vois pas pourquoi tu démarres le 1ier between par "v_ordre_quest_prec + 1" ... :koi:

Si j'ai compris ce que veux faire NPortmann, jusquà "Question précédente" (B dans son exemple) l'ordre est inchangé. Il change donc à partir de l'ordre de la question précédente + 1 (et je décale de la longueur du bloc à insérer). Cela revient à ce que tu as fait (me semble-t-il), si ce n'est que j'ai inversé les deux conditions when ;)
27/10/2006, 10h59
remi4444

Ok...
mais j'ai peur que ça ne fonctionne que pour ce cas particulier ou il y à un décalage de 2 pour des bloc de taille 3, ce qui entraine un chevauchement sur 1 élément...
Il faudrait voir ce que ça donne avec d'autres paramètres. Du coup l'ordre des WHEN est important puisqu'il faut choisir quel bloc "chevauche" l'autre ;)

feedback

Merci pour les réponses.
J'étais à deux doigts de partir sur une solution plus compliquée. Pour en revenir à nos moutons, je dois aussi prendre en considération le cas ou le bloque à déplacer se situe en dessus de la question précédente.
Voici le code que j'obtiens pour mon cas pratique:

Code:

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
-------------------------------------------
    -- Ordonnancement d'un bloque de valeurs --
    -------------------------------------------             
    -- Recherche de vl_ordrequestprec 
    SELECT  QUESTIONNAIRES.ordre INTO vl_ordrequestprec
    FROM    QUESTIONNAIRES, VARIABLES
    WHERE   QUESTIONNAIRES.vari_vpro_inst_numero = VARIABLES.vpro_inst_numero
    AND     QUESTIONNAIRES.vari_vpro_numerodep   = VARIABLES.vpro_numerodep
    AND     QUESTIONNAIRES.vari_numerodep        = VARIABLES.numerodep
    AND     QUESTIONNAIRES.vag_numerodep   = FORM_VAL.vag_numerodep
    AND     QUESTIONNAIRES.inst_numero     = FORM_VAL.inst_numero
    AND     QUESTIONNAIRES.tyqre_numerodep = FORM_VAL.tyqre_numerodep
    AND     VARIABLES.dupl_nom = FORM_VAL.QUEST_PREC;
 
    -- Recherche de vl_ordreblockfrom
    SELECT  QUESTIONNAIRES.ordre INTO vl_ordreblockfrom 
    FROM    QUESTIONNAIRES, VARIABLES
    WHERE   QUESTIONNAIRES.vari_vpro_inst_numero = VARIABLES.vpro_inst_numero
    AND     QUESTIONNAIRES.vari_vpro_numerodep   = VARIABLES.vpro_numerodep
    AND     QUESTIONNAIRES.vari_numerodep        = VARIABLES.numerodep
    AND     QUESTIONNAIRES.vag_numerodep   = FORM_VAL.vag_numerodep
    AND     QUESTIONNAIRES.inst_numero     = FORM_VAL.inst_numero
    AND     QUESTIONNAIRES.tyqre_numerodep = FORM_VAL.tyqre_numerodep
    AND     VARIABLES.dupl_nom = FORM_VAL.BLOCK_FROM;
 
    -- Recherche de vl_ordreblockto
    SELECT  QUESTIONNAIRES.ordre INTO vl_ordreblockto
    FROM    QUESTIONNAIRES, VARIABLES
    WHERE   QUESTIONNAIRES.vari_vpro_inst_numero = VARIABLES.vpro_inst_numero
    AND     QUESTIONNAIRES.vari_vpro_numerodep   = VARIABLES.vpro_numerodep
    AND     QUESTIONNAIRES.vari_numerodep        = VARIABLES.numerodep
    AND     QUESTIONNAIRES.vag_numerodep   = FORM_VAL.vag_numerodep
    AND     QUESTIONNAIRES.inst_numero     = FORM_VAL.inst_numero
    AND     QUESTIONNAIRES.tyqre_numerodep = FORM_VAL.tyqre_numerodep
    AND     VARIABLES.dupl_nom = FORM_VAL.BLOCK_TO;                 
 
    -- Calcul du taille du bloque
    vl_blocksize := (vl_ordreblockto - vl_ordreblockfrom) + 1;
 
    -- Pour un décalage vers le haut (le bloque se situe 
    -- au dessous de la question précédente):
    IF (vl_ordrequestprec < vl_ordreblockfrom) THEN 
      vl_intervalnegsize := (vl_ordreblockfrom - vl_ordrequestprec) - 1;
      UPDATE QUESTIONNAIRES
      SET ordre = CASE 
        WHEN (ordre >= vl_ordreblockfrom AND ordre <= vl_ordreblockto)
          THEN ordre - vl_intervalnegsize
        WHEN (ordre > vl_ordrequestprec AND ordre < vl_ordreblockfrom)
          THEN ordre + vl_blocksize
        END
      WHERE vag_numerodep 	= FORM_VAL.vag_numerodep
      AND inst_numero 	= FORM_VAL.inst_numero 
      AND tyqre_numerodep = FORM_VAL.tyqre_numerodep
      AND ((ordre >= vl_ordreblockfrom AND ordre <= vl_ordreblockto) OR (ordre > vl_ordrequestprec AND ordre < vl_ordreblockfrom)); 
    -- Pour un décalage vers le bas (le bloque se situe
    -- au dessus de la question précédente):
    ELSIF (vl_ordrequestprec > vl_ordreblockto)  then
      vl_intervalpossize := vl_ordrequestprec - vl_ordreblockto;  
      UPDATE QUESTIONNAIRES
      SET ordre = CASE 
        WHEN (ordre >= vl_ordreblockfrom AND ordre <= vl_ordreblockto)
          THEN ordre + vl_intervalpossize
        WHEN (ordre > vl_ordreblockto AND ordre <= vl_ordrequestprec)
          THEN ordre - vl_blocksize
        END
      WHERE ((ordre >= vl_ordreblockfrom AND ordre <= vl_ordreblockto) OR (ordre > vl_ordreblockto AND ordre <= vl_ordrequestprec)); 
    END IF;

Voyez que c'est largement inspiré de vos codes, je vous remercie encore 1000 fois!