Discussion :

[mennou]

Bonjour,
je travaille avec mysql server 5.0.
Je ne sais pas d'où vient le problème exactement !!! car ma procédure se crée sans erreur mais ne fait pas le travail demandé!
voilà ma procédure :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
delimiter //
CREATE  PROCEDURE calcul1()
begin
declare fini int default 0;
declare maxrisk,info,node,vriskm,infoimp,vriskh int;
declare descrisk,descimp varchar(45) ;
declare cur_info cursor for select num_info_asset from matrix_info_node;
declare cur_node cursor for select num_node from matrix_info_node;
declare continue handler for not found set fini=1;
open cur_node;
while fini <> 1 do
fetch cur_node into node;
if fini=0 then open cur_info;
while fini <> 1 do
fetch cur_info into info;
if fini <> 1 then
select max_risk_node into maxrisk from network_node where num_node=node;
select description_risk into descrisk from risk_node_loss where value_risk=maxrisk;
select value_risk into vriskm from risk_node_loss where description_risk='med';
select value_risk into vriskh from risk_node_loss where description_risk='hi';
select info_impact into infoimp from matrix_info_node where num_node=node and num_info_asset=info;
select desc_asset_impact into descimp from scale_asset_impact where value_asset_impact=infoimp;
 
if descrisk='hi' then
 begin
  if descimp ='asset available using work around' then update matrix_info_node set risk_node_info=vriskm where num_node=node and num_info_asset=info;
  elseif descimp ='availability degraded even with work around' then update matrix_info_node set risk_node_info=vriskh where num_node=node and num_info_asset=info;
  elseif descimp ='asset not available at all' then update matrix_info_node set risk_node_info=vriskh where num_node=node and num_info_asset=info;
  end if;
 end;
end if;
 
end if;
end while;
close cur_info;
set fini=0;
end if;
end while;
close cur_node;
end//

Ce qui est sure est que la syntaxe est juste car sinon la procédure ne se crée pas en plus quand je l'appelle aucune erreur ne se produit, ou est alors le problème?! je me suis vraiment coincée

merci d'avance.

[Alain Defrance]

Bonjour,

C'est bien mais euh ... elle est sensé faire quoi ?

[mennou]

Bonjour,
oui vous avez raison, la procédure est longue, alors j'explique :

Code :

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

1
2
3
4
5
6
7
8
9
 
IF descrisk='hi' then
 begin
  IF descimp ='asset available using work around' then UPDATE matrix_info_node SET risk_node_info=vriskm WHERE num_node=node AND num_info_asset=info;
  elseif descimp ='availability degraded even with work around' then UPDATE matrix_info_node SET risk_node_info=vriskh WHERE num_node=node AND num_info_asset=info;
  elseif descimp ='asset not available at all' then UPDATE matrix_info_node SET risk_node_info=vriskh WHERE num_node=node AND num_info_asset=info;
  end IF;
 end;
end IF;

Je dois tester le contenu de la variable deskrisk si elle contient 'hi' alors je passe à un autre test sur le contenu de la variable descimp, selon ce contenu je vais faire un update sur la table matrix_info_node.
Quand j'appelle la procédure aucune erreur s'affiche mais ma table ne se met pas à jour quand il le faut.
Merci pour votre patience.

[mennou]

bonjour,
j'ai tout changé et ma procédure fonctionne et fait ce qui est demandé correctement :

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
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
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `calcul1`()
begin
declare fini int default 0;
declare maxrisk,info,node,vriskm,infoimp,vriskh int;
declare descrisk,descimp varchar(45) ;
declare cur_info cursor for select num_info_asset from matrix_info_node;
declare cur_node cursor for select num_node from matrix_info_node;
declare continue handler for not found set fini=1;
open cur_node;
while fini <> 1 do
fetch cur_node into node;
if fini=0 then open cur_info;
while fini <> 1 do
fetch cur_info into info;
if fini <> 1 then
select max_risk_node into maxrisk from network_node where num_node=node;
select description_risk into descrisk from risk_node_loss where value_risk=maxrisk;
select value_risk into vriskm from risk_node_loss where description_risk='med';
select value_risk into vriskh from risk_node_loss where description_risk='hi';
 
 
if descrisk='hi' then
 begin
   if (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info)) ='availability degraded even with work around' then
    update matrix_info_node set risk_node_info=vriskh where num_node=node and num_info_asset=info;
 
   elseif (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info)) ='asset not available at all' then
    update matrix_info_node set risk_node_info=vriskh where num_node=node and num_info_asset=info;
 
   elseif (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info)) ='asset available using work around' then
    update matrix_info_node set risk_node_info=vriskm where num_node=node and num_info_asset=info;
 
   end if;
 end;
 
elseif descrisk='med' then
 begin
   if (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info))='asset available using work around' then
    update matrix_info_node set risk_node_info=(select value_risk from risk_node_loss where description_risk='low') where num_node=node and num_info_asset=info;
 
   elseif (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info))='availability degraded even with work around'  then
    update matrix_info_node set risk_node_info=(select value_risk from risk_node_loss where description_risk='med') where num_node=node and num_info_asset=info;
 
   elseif (select desc_asset_impact from scale_asset_impact where value_asset_impact=(select info_impact from matrix_info_node where num_node=node and num_info_asset=info))='asset not available at all' then
    update matrix_info_node set risk_node_info=(select value_risk from risk_node_loss where description_risk='med') where num_node=node and num_info_asset=info;
 
   end if;
 
 end;
 
  elseif descrisk='low' then
  update matrix_info_node set risk_node_info = (select value_risk from risk_node_loss where description_risk='low') where num_node=node and num_info_asset=info;
 
end if;
 
end if;
end while;
close cur_info;
set fini=0;
end if;
end while;
close cur_node;
end

mon problème est résolu .
Merci.