Discussion :

[Phénélas]

Bonjour et bravo pour ce site, je sens qu'il me sera tres utile a l'avenir

Mon problême est assez simple en fait .
Dans une procédure stockée j'ai un cruseur qui récupère le nom de différentes tables que je dois mettre a jours . Par exemple je récupère tous les noms de tables ayant la colonne 'PAC' .
Le but c'est de mettre a jour les valeurs de 'PAC' dans toutes ces tables .

exemple :

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
 
CREATE OR REPLACE PROCEDURE MAJ_PAC
(p_new_pac IN varchar2, p_old_pac in varchar2 )
IS
 
cursor c1 is select table_name from user_tab_columns a, user_objects b 
where a.column_name='PAC' and a.table_name=b.object_name 
and b.object_type='TABLE';
 
var1 c1%rowtype;
req varchar2(200);
 
BEGIN
 
open c1;
loop
    fetch c1 into var1;
    exit when c1%NOTFOUND;
    req := 'update '||var1.table_name||' set PAC = '||p_new_pac ||
    'where PAC = '||p_old_pac;
    EXECUTE IMMEDIATE req;
    commit;   
end loop;
close c1;
 
END;

Avec ce systeme ma procedure fait un commit apres chaque table updaté, mon probleme c'est que je ne sais pas a l'avance combien de lignes seront modifiés dans chaque table, ce que je voudrais obtenir c'est par exemple un commit sur ma table dès qu'on a atteint 500 000 lignes modifiés .
Parce que là parfois ma procédure fait un commit apres un update qui impact 16 millions de lignes d'une table, et les rollback segment enflent et le dba devient tout rouge et vient crier dans mon bureau

Merci d'avance !

[LeoAnderson]

Bonjour et bienvenue !

La règle de base ici est de ne poster qu'après avoir cherché (cf "A lire avant de poster").

Sur ce même forum, il y a un topic "Comment faire un commit intermédiaire".... ;-)

[Phénélas]

Sur ce même forum, il y a un topic "Comment faire un commit intermédiaire".... ;-)

oui merci j'ai vu avant de poster, mais le contexte n'est pas du tout le meme car dans le topic dont vous parlez il s'agit de faire un insert de valeurs retournés par un select, donc on peut eventuellement utiliser le rownum (meme si apparement c'est tres déconseillé au niveau perf) . Dans mon cas je ne peux pas utiliser de rownum (et comme de toutes façon c'est pas la bonne solution ...).

[remi4444]

tu peux capter une variable PL/SQL juste après ton EXECUTE, elle s'appelle SQL%ROWCOUNT. Elle te donne le nombre exact de ligne modifiées, donc apres ça tu peux par exemple programmer pour accumuler cette variable dans un compteur général et commiter quand tu atteint un certain seuil.

Cependant tu ne pourra pas insérer un commit intermédiaire au milieu de ta requête d'update si celle si fait 16M de modif en une seule fois...

[plaineR]

Dans mon cas je ne peux pas utiliser de rownum (et comme de toutes façon c'est pas la bonne solution ...).

Moi au contraire dans ton cas je pense que c'est la bonne solution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
open c1;
loop
    fetch c1 into var1;
    exit when c1%NOTFOUND;
    req := 'update '||var1.table_name||' set PAC = '||p_new_pac ||
    'where PAC = '||p_old_pac || ' and rownum < 50000';
    loop
       EXECUTE IMMEDIATE req;
       commit;    
    exit when sql%notfound;
    end loop;
end loop;
close c1;

[remi4444]

Moi au contraire dans ton cas je pense que c'est la bonne solution:

Bien vu, pour cette requête effectivement, y'a aucun problème vu que la clause where élimine forcément les ligne modifiées précédemment....

Juste un cas particulier, verifier que p_old_pac soit bien différent de p_new_pac

[Phénélas]

Merci pour votre aide précieuse, je suis désolé je ne suis pas (encore) tres calé en pl/sql mais dans l'exemple donné par plaineR il me semble que seulement 50 000 enregistrements sont modifiés (rownum<50000) mais si ma table en contient plusieurs millions comment modifier les enregistrements compris entre 50 001 et x millions ?

[plaineR]

Une fois ton update fait, la valeur nouvelle valeur de PAC sera différente de l'ancienne (enfin normalement..., sinon il est inutile de faire l'update).

La clause where ramènera les 50000 lignes égales à p_old_pac. Les lignes ayant été updatées précédemment étant égales à p_new_pac ne sont pas ramenées.

[Phénélas]

ok mais ce que je ne comprends pas c'est qu'une fois mon update effectué et les 50 000 premiers enregistrement mis à jour, ma requete d'update s'arrette là puisque la clause rownum<50000 exclut les enregistrements restants, non ?
A ce moment là ma boucle passe a l'itération suivante et change de table (la boucle ne passe qu'une seule fois sur chaque table) .
Comment faire pour que l'update, arrivé a 50 000 enregistrements mis a jour fasse le commit et continu avec les 50 000 suivants sur la meme table, puis fasse le commit et poursuive encore avec les 50 000 suivants et ainsi de suite jusqu'a avoir parcouru toute la table ?
Sinon j'ai peut etre rien compris, désolé dans ce cas

[plaineR]

Regarde :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
    loop
       EXECUTE IMMEDIATE req;
       commit;    
    exit when sql%notfound;
    end loop;

J'exécute la commande autant de fois qu'il y a des lignes à mettre à jour, c'est à dire tant qu'il y a des lignes avec PAC = p_old_pac. Et je le fais par "paquets" de 50 000 lignes.

Exemple :
Tu as 102000 lignes à mettre à jour avec p_old_pac = 1 et p_new_pac = 2 :

=> au premier passage : je mets à jour les 50000 premières lignes donc pour ces 50000 lignes, PAC prends la valeur 2.

=> au second passage, je n'ai plus que 520000 lignes dont PAC est égale à 1 (pour les 50000 autres PAC est désormais égal à 2). Parmi ces 520000 lignes, je prends les 50000 premières et je leur attribue pour la colonne PAC la valeur 2.

=> au troisième passage, je n'ai plus que 20000 lignes dont PAC est égal à 1 (pour les 100000 autres, PAC est désormais égal à 2). Je mets à jour pour ces 2000 lignes PAC à 2.

=> au quatrième passage : je n'ai plus de ligne à mettre à jour, je sors de ma boucle et je passe à la table suivante.

C'est ce que tu voulais non ?

[Phénélas]

Merci Beaucoup PlaineR pour ta patience avec le débutant que je suis

Je vais tester ça aujourd'hui et je mettrai la balise "Résolu" dès que ce sera validé (mais je n'ai aucun doute la dessus) .
Je connaissais pas sql%notfound, décidément le pl/sql est plein de ressources

Encore Merci