CURSOR FOR et INSERT INTO SELECT

Version imprimable

Bonjour,

Je fais face à un problème sur mon script, et j'ai vraiment besoin de vos lumières.

Pour vous expliquer ce que je veux faire en deux mots :
Il faut que j'insère dans une table des éléments mois par mois, en partant de deux tables de relation 1,n ayant comme info une date de début et une date de fin.
Le souci, c'est qu'il faut que je fasse une somme sur un indicateur présent dans la table côté n.

Ex :
Table1
ID | Nom
1 | TOTO

Table 2
ID | ID2 | DateDebut | DateFin | Montant
1 | 1 | 01/01/2010 | 01/02/2010 | 10
1 | 2 | 01/02/2010 | 01/03/2010 | 20

Et ce que j'aimerais obtenir :
Table Finale
ID | Nom | Echeance | Montant
1 | TOTO | 01/01/2010 | 10
1 | TOTO | 01/02/2010 | 30
1 | TOTO | 01/03/2010 | 20

J'étais donc parti dans la création d'un curseur sur lequel je voulais placer un FOR pour générer mes différents mois :

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
CREATE OR REPLACE PROCEDURE BO.ALIM_PLANAP_ECH_TEST IS
    v_duree NUMBER;
 
    BEGIN
        EXECUTE IMMEDIATE('TRUNCATE TABLE BO.PLANAP_ECH_TEST');    
        FOR v_rec IN (
                    SELECT d.CORG, 
                           d.CCOMPTE, 
                           d.CORG||d.CCOMPTE AS IDCOMPTE, 
                           d.NOBAIL, 
                           decode(length(d.NOBAIL),1,d.CORG||d.CCOMPTE||'0'||d.NOBAIL,d.CORG||d.CCOMPTE||d.NOBAIL) AS IDBAIL,
                           d.NOPLAN,
                           d.DSIGN,
                           d.MTENGAGE,
                           d.DEFFET,
                           d.DCLOTURE,
                           d.CMODPLAN,
                           p.DDEB,
                           p.DFIN,
                           p.NB
                    FROM DOSPLAN@DB_EELOGI d, PLANRAT@DB_EELOGI p
                    WHERE d.CORG = p.CORG AND d.CCOMPTE = p.CCOMPTE AND d.NOBAIL = p.NOBAIL AND d.NOPLAN = p.NOPLAN
                    ORDER BY CORG,CCOMPTE,NOBAIL,NOPLAN
                   ) 
                   LOOP
                        v_duree := months_between(last_day(v_rec.DFIN),last_day(v_rec.DDEB))+1;
                        FOR i IN 1..v_duree LOOP
 
                            INSERT INTO BO.PLANAP_ECH_TEST
                            VALUES (
                            v_rec.CORG,
                            v_rec.CCOMPTE,
                            v_rec.IDCOMPTE, 
                            v_rec.NOBAIL, 
                            v_rec.IDBAIL,
                            v_rec.NOPLAN,
                            v_rec.DSIGN,
                            v_rec.MTENGAGE,
                            v_rec.DEFFET,
                            v_rec.DCLOTURE,
                            last_day(add_months(v_rec.DDEB,i)-1),
                            to_number(to_char(last_day(add_months(v_rec.DDEB,i)-1),'yyyy')),
                            to_number(to_char(last_day(add_months(v_rec.DDEB,i)-1),'mm')),
                            v_rec.NB * (months_between(last_day(add_months(v_rec.DDEB,i)-1),last_day(v_rec.DDEB))+1),
                            v_rec.MTENGAGE-(v_rec.NB * (months_between(last_day(add_months(v_rec.DDEB,i)-1),last_day(v_rec.DDEB))+1)),
                            v_rec.NB,
                            v_rec.CMODPLAN );        
                        END LOOP;                
                   END LOOP;
         COMMIT;
    END;
/

Le problème c'est que pour avoir mes montants sommés, il faudrait que je fasse un sum() sur mes lignes avec "NB".
Or, l'instruction INSERT INTO ... VALUES n'accepte pas les agrégations (jusque là OK)
Mais mon autre problème, c'est que je ne peux pas utiliser INSERT INTO ... SELECT car v_rec n'étant ni une table ni une vue, je ne peux pas l'utiliser dans un FROM...

Si quelqu'un a une idée...

Merci d'avance

Les curseurs c'est le mal, une requête SQL ensembliste fera l'affaire.

Sur les données fournies, le problème n'est pas si complexe, après à voir une fois transposé sur les tables réelles :

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
With Table1 as
(
select 1 as id1, 'TOTO' as nom from dual
)
  ,  Table2 as
(
select 1 as id1, 1 as id2, date '2010-01-01' as dtd, date '2010-02-01' as dtf, 10 as mnt from dual union all
select 1       , 2       , date '2010-02-01'       , date '2010-03-01'       , 20        from dual
)
  ,  NewTable2 as
(
select id1, dtd as echeance, mnt
  from table2
 union all
select id1, dtf, mnt
  from table2
)
  select t1.id1, t1.nom, t2.echeance,
         sum(t2.mnt) as montant
    from Table1 t1
         inner join NewTable2 t2
           on t2.id1 = t1.id1
group by t1.id1, t1.nom, t2.echeance
order by t1.id1 asc, t2.echeance asc;
 
       ID1 NOM  ECHEANCE    MONTANT
---------- ---- -------- ----------
         1 TOTO 01/01/10         10
         1 TOTO 01/02/10         30
         1 TOTO 01/03/10         20

22/10/2010, 10h31
TomDuBouchon

Bonjour et merci pour cette réponse,

Malheureusement, je pense que j'ai été un peu simpliste dans l'exemple que j'ai donné car toutes les échéances étaient présentes en date de début et date de fin...

Pour être plus précis, je modifie mon exemple :
Table1
ID | Nom
1 | TOTO

Table 2
ID | ID2 | DateDebut | DateFin | Montant
1 | 1 | 01/01/2010 | 01/04/2010 | 10
1 | 2 | 01/04/2010 | 01/07/2010 | 20

Et ce que j'aimerais obtenir :
Table Finale
ID | Nom | Echeance | Montant
1 | TOTO | 01/01/2010 | 10
1 | TOTO | 01/02/2010 | 10
1 | TOTO | 01/03/2010 | 10
1 | TOTO | 01/04/2010 | 30
1 | TOTO | 01/05/2010 | 20
1 | TOTO | 01/06/2010 | 20
1 | TOTO | 01/07/2010 | 20

J'ai compris que le curseur était "le mal"... Je cherche donc un autre moyen pour parcourir une table ligne à ligne dans une boucle... L'équivalent d'un WHILE NOT EOF dans d'autres langages en fait...

Si vous avez une table de référence des mois, il faut rajouter une jointure externe sur votre table.

Si vous n'en avez pas, vous pouvez la construire dynamiquement à l'exécution :

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
with table1 as
(
select 1 as id1, 'TOTO' as nom from dual
)
  ,  table2 as
(
select 1 as id1, 1 as id2, date '2010-01-01' as dtd, date '2010-04-01' as dtf, 10 as mnt from dual union all
select 1       , 2       , date '2010-04-01'       , date '2010-07-01'       , 20        from dual
)
  ,  bornes as
(
select min(least(dtd, dtf))    as mth_min,
       max(greatest(dtd, dtf)) as mth_max
  from table2
)
  ,  calmonth as
(
    select add_months(mth_min, level-1) as mth
      from bornes
CONNECT by level <= months_between(mth_max, mth_min)+1
)
  select t1.id1, t1.nom, cm.mth as echeance,
         sum(t2.mnt) as montant
    from calmonth cm
         left outer join table2 t2
         inner join table1 t1
           on t1.id1 = t2.id1
           on cm.mth between t2.dtd and t2.dtf
group by t1.id1, t1.nom, cm.mth
order by t1.id1 asc, cm.mth asc;
 
       ID1 NOM  ECHEANCE      MONTANT
---------- ---- ---------- ----------
         1 TOTO 01/01/2010         10
         1 TOTO 01/02/2010         10
         1 TOTO 01/03/2010         10
         1 TOTO 01/04/2010         30
         1 TOTO 01/05/2010         20
         1 TOTO 01/06/2010         20
         1 TOTO 01/07/2010         20

22/10/2010, 12h07
TomDuBouchon

Un grand merci, ça fonctionne parfaitement :ccool:
22/10/2010, 17h18
TomDuBouchon

Pardon, question subsidiaire :

Je souhaite intégrer ce script dans une procédure et insérer les résultats dans une table. Seulement, à la suite des instructions WITH, il ne semble pas tolérer ni le INSERT INTO SELECT, ni le SELECT INTO.

Il me renvoie respectivement les messages d'erreur suivants :
"ERROR line 33, col 13, ending_line 33, ending_col 18, Found 'INSERT', Expecting: , -or- ( SELECT"
"PLS-00403: expression 'BO.PLANAP_ECH_TEST' cannot be used as an INTO-target of a SELECT/FETCH statement"

Il faut directement insérer dans la table, avec le WITH après l'insert :
Code:

1 2 3 4 5 6 7 create table t1 (id1 number(1)); -- Table created. insert into t1 (id1) with sr as (select 1 from dual) select * from sr; -- 1 row created.

25/10/2010, 14h53
TomDuBouchon

J'ai pu tout tester et ça fonctionne parfaitement bien. Un grand merci Waldar :ccool:
25/10/2010, 15h36
Waldar

De rien !
Transmettez mes amitiés à "mister that's it" si vous le croisez !