CURSOR and BULK COLLECT

Version imprimable

25/10/2012, 15h45
lenoil

CURSOR and BULK COLLECT

Bonjour à tous,

j'essaye d'écrire une procédure Oracle qui doit réaliser un traitement sur une table contenant 400 000 enregsitrements.

Je réalise un curseur et je parcours ce curseur tous les 200 en les stockant dans une table temporaire grâce au BULK COLLECT.

Quand je lance la procédure , elle tourne et rien ne se passe.

A votre avis quelle est la meilleure façon de traiter cette problématique ?

Merci à tous
25/10/2012, 16h22
mnitu

Faire du debug, non ?!
Pourquoi voulez-vous mettre les enregistrements dans une table temporaire ?

ben je vois pas comment faire autrement pour aller vite ..... en PL/SQL

voici le code

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
 
 
PROCEDURE UPDATE_TABLE1_CHAMP2
IS
 
   TYPE    TYP_TAB_TABLE1 IS TABLE OF TABLE1%ROWTYPE INDEX BY PLS_INTEGER;
   TEMP_TABLE1 TYP_TAB_TABLE1 ;
   Cursor  C_TABLE1 is Select * From TABLE1  order by CHAMP1,rowid ASC; 
   cpt Pls_integer :=1;
   prec TABLE1.CHAMP1%TYPE :='';
 
Begin
 
 
     OPEN C_TABLE1;
     LOOP
        FETCH C_TABLE1 BULK COLLECT into TEMP_TABLE1 LIMIT 200;
 
        FOR i In 1..TEMP_TABLE1.Count loop
          dbms_output.put_line('Traitement : CHAMP1=' || TO_CHAR(TEMP_TABLE1(i).CHAMP1));
          update TABLE1 set CHAMP2=TO_CHAR(cpt) where ID_TABLE1=TEMP_TABLE1(i).ID_TABLE1;
          cpt:=cpt+1;
          if (prec <> '') and (prec <>TEMP_TABLE1(i).CHAMP1 ) then
            cpt:=1;
          end if;
          prec := TEMP_TABLE1(i).CHAMP1 ;
        end loop;
        exit when C_TABLE1%notfound;
 
     END LOOP ;
     CLOSE C_TABLE1;
 
 
 
EXCEPTION
    WHEN OTHERS then  dbms_output.put_line(TO_CHAR(sqlerrm));
 
END UPDATE_TABLE1_CHAMP2;

Vous ne maitrisez pas le traitement via Bulk (votre condition de sortie de boucle est incorrecte). Voilà un exemple (faite set server output sous sqlplus on avant de l'exécuter)

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
 
Declare
  cBatchsize  constant Pls_Integer := 3;
  Type rEmployee Is Record (
    employee_id     employees.employee_id%Type,
    first_name      employees.first_name%Type,
    last_name       employees.last_name%Type,
    email           employees.email%Type,
    phone_number    employees.phone_number%Type,
    hire_date       employees.hire_date%Type,
    job_id          employees.job_id%Type,
    salary          employees.salary%Type,
    commission_pct  employees.commission_pct%Type,
    manager_id      employees.manager_id%Type,
    department_id   employees.department_id%Type
  );
  Type Employee_va  Is Varray(1000) Of rEmployee;  
  l_Employees          Employee_va;
  --  
  Cursor CrsEmployee(department_id  employees.department_id%Type) Return employees%Rowtype Is
    Select t.employee_id, 
           t.first_name, 
           t.last_name, 
           t.email, 
           t.phone_number, 
           t.hire_date, 
           t.job_id, 
           t.salary, 
           t.commission_pct, 
           t.manager_id, 
           t.department_id
      From hr.employees t
     Where t.department_id = CrsEmployee.department_id;      
Begin
  Open CrsEmployee(60);
  --    
  Loop
    Fetch CrsEmployee Bulk Collect Into l_Employees Limit cBatchsize;
    --
    For i In 1..l_Employees.Count 
    Loop
      Dbms_Output.Put_Line(l_Employees(i).employee_id);
    End Loop;
    --
    Exit When l_Employees.Count() < cBatchsize;
  End Loop;
  --
  Close CrsEmployee;
End;

PS. Supprimez les gestionnaire d'exception, il ne sert qu'à vous poser des problèmes.

25/10/2012, 17h24
lenoil

OK mais dans mon cas mon cursor CrsEmployee contiendra 400000 enregistrements ! car c'est ma table au complet ...

Je vais refaire des tests en set server output ...

merci
25/10/2012, 17h27
mnitu

Un curseur ne "contiendra" jamais les enregistrements de la table.
25/10/2012, 17h39
lenoil

ok ce n'est pas une copie mais un pointeur alors ....
25/10/2012, 17h41
lenoil

donc pour mon cas le bulk collect ne sert à rien ....

Ca ne serait pas simplement ça, l'objectif ?
Code:

1 2 3 4 5 6 merge into TABLE1 t1 using (select rowid as rid, row_number() over(partition by CHAMP1 order by rowid) as c2 from TABLE1) t2 on (t1.rowid = t2.rid) when matched then update set t1.CHAMP2 = t2.c2
PS : pourquoi vouloir faire un to_char sur le compteur pour le champ2, autant avoir l'information en nombre, non ?

[edit] Si c'est bien ça, il est fortement déconseillé de stocker le row_number, il est nettement préférable de l'utiliser directement dans les requêtes ou d'utiliser une vue.
Un peu de lecture sur des fonctions indispensables à connaître :
Les fonctions analytiques

25/10/2012, 19h41
mnitu

Citation:

Envoyé par lenoil

donc pour mon cas le bulk collect ne sert à rien ....

Ce n'est pas ça ce que j'ai dit.
26/10/2012, 09h10
lenoil

bon ben je vais regarder la doc car là je suis un peu léger

Merci à tout les deux en tout cas je reviendrai vers vous pour vous dire ce que j'ai fait si j'y arrive .....

je dois travailler sur autre chose aujourd'hui ....
30/10/2012, 10h56
lenoil

finalement je m'en suis sorti avec deux requetes sql packagées (utilisation de merge ...) dans une procédure....