problème procédure oracle

[buzzzy]

Bonjour à tous,

voilà je viens de commencer sur le PL/SQL et j'ai un souci avec la proc ci-dessous.
Pour simplifier j'ai une table GARANTIE qui a 3 champs : No_ADH numéro d'adhérent, DT_NAISS date de naissance et NORNGA (rang de naissance mais peu importe l'ordre)
Je voudrais que pour chaque numéro d'adhérent, la procédure update le champ NORNGA vide de ma table GARANTIE et lui mette 2, puis 3 et 4... pour chaque ligne avec date de naissance. Puis pour un nouveau numéro d'adhérent, le NORNGA se remet à 2, puis 3 etc....
On a donc un num adhérent, une date de naissance et un num de rang null

NO_ADH - DT_NAISS - NORNGA
1000 - 30/05/1950 - 1
1000 - 14/6/1974 - null
1000 - 7/6/ 1942 - null
1000 - 1/10/1980 -null
2000 -4/4/1973 -1
2000 -7/6/1974 - null
2000 -7/9/1977 - null
devienne après la proc
NO_ADH - DT_NAISS - NORNGA
1000 - 30/05/1950 - 1
1000 - 14/6/1974 - 2
1000 - 7/6/ 1942 - 3
1000 - 1/10/1980 -4
2000 -4/4/1973 -1
2000 -7/6/1974 - 2
2000 -7/9/1977 - 3

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
CREATE OR REPLACE procedure UPDATE_NORNGA IS
P_NUM_ADH Varchar2(15);
i integer (2);
c integer (2);
DECLARE
cursor C_NO_ADH IS
SELECT NO_ADh FROM garantie
 
BEGIN
 
c:= SELECT count(dt_naiss) FROM tfp_ech_garantie WHERE no_adh=P_NUM_ADH 
i:= 2
j:= i+1
 
FOR i TO c
 
LOOP
 
UPDATE GARANTIE
SET NORNGA = j
WHERE no_adh=P_NUM_ADH
AND DT_NAISS IS NULL;
 
END UPDATE_NORNGA;

Merci à vous

[skuatamad]

Salut,

A priori pas besoin de PL/SQL sutout si c'est du one shot, sinon tu peux créer une procédure autour de la requête.

Par contre je ne comprends pas bien :

NORNGA (rang de naissance mais peu importe l'ordre)

Justement l'ordre est très important, en tout cas ma proposition trie en fonction de la dt_naiss.
Mais, comme tu sembles avoir déjà des lignes où NORNGA est à 1 pour chaque NO_ADH, je te propose une requête qui n'updatera que les NULLs en laissant à 1 ceux qui sont déjà renseignés même s'ils ont une dt_naiss inférieure.

Tu peux utiliser MERGE - je pars du principe que le couple (no_adh,dt_naiss) est unique mais si tu as une PK utilise la dans le SELECT du USING et dans le ON - donc :

Code :

1
2
3
4
5
6
7
8
9
10
merge INTO t1
USING (
  SELECT no_adh,dt_naiss, 
	 row_number() over (partition BY no_adh ORDER BY dt_naiss)+1 nornga 
  FROM t1 
  WHERE nornga IS NULL
) t2
ON (t1.no_adh=t2.no_adh AND t1.dt_naiss=t2.dt_naiss)
when matched then
UPDATE SET t1.nornga = t2.nornga

qui donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT * FROM t1 ORDER BY no_adh,nornga;
 
    NO_ADH DT_NAISS      NORNGA
---------- --------- ----------
      1000 30-MAY-50          1
      1000 07-JUN-42          2
      1000 14-JUN-74          3
      1000 01-OCT-80          4
      2000 04-APR-73          1
      2000 07-JUN-74          2
      2000 07-SEP-77          3
 
7 rows selected.

Mais je crois que la possibilité de ne pas préciser WHEN NOT MATCHED dans le MERGE dépend de la version (peut être à partir de 10G).
Je suis presque sûr qu'il est possible d'écrire juste un UPDATE mais je ne me souviens plus de la syntaxe...
Si une âme charitable peut me rafraichir la mémoire, ce sera avec plaisir.
Je laisse le CTAS pour plus de facilité :

Code :

1
2
3
4
5
6
7
8
9
CREATE TABLE t1 AS (
SELECT 1000 AS NO_ADH , to_date('30/05/1950','dd/mm/yyyy') AS DT_NAISS , 1 AS NORNGA FROM dual union ALL
SELECT 1000 , to_date('14/06/1974','dd/mm/yyyy'), NULL FROM dual union ALL
SELECT 1000 , to_date('07/06/ 1942','dd/mm/yyyy'), NULL FROM dual union ALL
SELECT 1000 , to_date('01/10/1980','dd/mm/yyyy') ,NULL FROM dual union ALL
SELECT 2000 , to_date('04/04/1973','dd/mm/yyyy') ,1 FROM dual union ALL
SELECT 2000 , to_date('07/06/1974','dd/mm/yyyy') , NULL FROM dual union ALL
SELECT 2000 , to_date('07/09/1977','dd/mm/yyyy') , NULL FROM dual
)

En attendant peut être que ta version est suffisante pour que le MERGE fonctionne.

[buzzzy]

Salut skuatamad

je te remercie pour ta réponse je vais tester ça demain et je te tiens au courant. Thanks

[buzzzy]

Hélas ça ne parche pas.
J'ai un message "missing keyword" à la fin du code, même en ayant rajouté un ";" à la fin ?

Ce serait parce que je suis en oracle v9 ?
Merci d'avance

[skuatamad]

Oui je pense que le MISSING Key Words viens de l'absence du NOT MATCHED en 9i.

Bon j'ai trouvé une syntaxe qui devrait passer sur une 9i, mais que je l'ai testé sur une 11G alors ça n'est peut être toujours pas bon :

Code :

1
2
3
4
5
6
7
8
UPDATE t1 SET nornga = (
	WITH t AS (
		SELECT no_adh, dt_naiss,row_number() over (partition BY no_adh ORDER BY dt_naiss)+1 AS new_n 
		FROM t1 
		WHERE nornga IS NULL)
	SELECT new_n FROM t WHERE t.no_adh = t1.no_adh AND t.dt_naiss = t1.dt_naiss
	)
WHERE nornga IS NULL

Par contre, le couple (no_adh, dt_naiss) DOIT être unique pour que la requête fonctionne...
Si tu as une PK autre utilise la.