store procedure con ciclo

[Vale1976]

salve,
espongo il mio problema con la speranza di avere una risposta quanto prima

Ho 2 tabelle in un DB Mysql:
- la prima si chiama contratti ed è composta dai seguenti campi: idContratto (bigint 11), NomeContratto (varchar), DataContratto (date), NomeAmministrazione (varchar), ValoreEconomico (decimal 11,2)
- la seconda si chiama percentuali e prevede i seguenti campi: idPercentuale (bigint 11), [I]Anno[I] (bigint 4), Percentuale (bigint 2)

Vorrei creare una terza tabella che si chiami contrattiattualizzati che preveda gli stessi campi della tabella contratti, più un ulteriore campo che mi indichi l'anno di attualizzazione (e che quindi dovrebbe essere un bigint 4) e una store procedure che faccia quanto segue: per ciascun contratto (idContratto) presente nella tabella contratti dovrebbero essere fatti tanti INSERT nella nuova tabella quanti sono gli anni di differenza tra la DataContratto e la Data corrent e, per ciascun INSERT, dovrebbe essere applicata la percentuale definita nella tabella percentuali al ValoreEconomico presente nella tabella contratti, a partire da un dato anno.

Faccio un esempio
nella tabella contratti ci sono 2 contratti:
1) idContratto = 1; NomeContratto = Contratto1; DataContratto = 2009-05-12; NomeAmministrazione = Amministrazione1; ValoreEconomico = 100,00
2) idContratto = 2; NomeContratto = Contratto2; DataContratto = 2010-01-15; NomeAmministrazione = Amministrazione2; ValoreEconomico = 100,00
3) idContratto = 3; NomeContratto = Contratto3; DataContratto = 2008-11-01; NomeAmministrazione = Amministrazione3; ValoreEconomico = 100,00

nella tabella percentuali ci sono le seguenti righe:
1) idPercentuale = 1; Anno = 2009; Percentuale = 5
1) idPercentuale = 2; Anno = 2010; Percentuale = 7

nella nuova tabella dovrebbero comparire le seguenti righe:
1) idContratto = 1; NomeContratto = Contratto1; DataContratto = 2009-05-12; NomeAmministrazione = Amministrazione1; ValoreEconomico = 100,00; AnnoAttualizzazione = 2009;
2) idContratto = 1; NomeContratto = Contratto1; DataContratto = 2009-05-12; NomeAmministrazione = Amministrazione1; ValoreEconomico = 100,00-(100,00/100*7); AnnoAttualizzazione = 2010
3) idContratto = 2; NomeContratto = Contratto2; DataContratto = 2010-01-15; NomeAmministrazione = Amministrazione2; ValoreEconomico = 100,00, AnnoAttualizzazione = 2010
4) idContratto = 3; NomeContratto = Contratto3; DataContratto = 2008-11-01; NomeAmministrazione = Amministrazione3; ValoreEconomico = 100,00; AnnoAttualizzazione = 2008
5) idContratto = 3; NomeContratto = Contratto3; DataContratto = 2008-11-01; NomeAmministrazione = Amministrazione3; ValoreEconomico = 100,00-(100,00/100*5); AnnoAttualizzazione = 2009
6) idContratto = 3; NomeContratto = Contratto3; DataContratto = 2008-11-01; NomeAmministrazione = Amministrazione3; ValoreEconomico = 100,00-(100,00/100*5)-(100,00-(100,00/100*5))/100*7; AnnoAttualizzazione = 2008

La store procedure dovrebbe lavorare in background senza lanci da codice.

Spero di essere riuscito a spiegarmi e che riuscite a darmi una mano nel più breve tempo possibile.

GRAZIE

[nicola75ss]

Prova questo dump e dimmi se come logica ci siamo. Spero di non aver scritto eresie.

drop table if exists `contratti`;

create table `contratti` (
  `idcontratto` int(11) not null auto_increment,
  `nomecontratto` varchar(50) default null,
  `datacontratto` date default null,
  `nomeamministrazione` varchar(50) default null,
  `valoreeconomico` decimal(10,2) default null,
  primary key (`idcontratto`)
) engine=myisam auto_increment=5 default charset=latin1;



/*Data for the table `contratti` */
insert  into `contratti`(`idcontratto`,`nomecontratto`,`datacontratto`,`nomeamministrazione`,`valoreeconomico`) values (1,'contratto1','2009-05-12','Amministrazione1','100.00');
insert  into `contratti`(`idcontratto`,`nomecontratto`,`datacontratto`,`nomeamministrazione`,`valoreeconomico`) values (2,'contratto2','2010-01-15','Amministrazione2','100.00');
insert  into `contratti`(`idcontratto`,`nomecontratto`,`datacontratto`,`nomeamministrazione`,`valoreeconomico`) values (3,'contratto3','2008-11-01','Amministrazione3','100.00');
insert  into `contratti`(`idcontratto`,`nomecontratto`,`datacontratto`,`nomeamministrazione`,`valoreeconomico`) values (4,'contratto4','2001-01-01','Amministrazione4','100.00');


/*Table structure for table `interi` */
drop table if exists `interi`;

create table `interi` (
  `i` tinyint(4) default null
) engine=myisam default charset=latin1;



/*Data for the table `interi` */
insert  into `interi`(`i`) values (0);
insert  into `interi`(`i`) values (1);
insert  into `interi`(`i`) values (2);
insert  into `interi`(`i`) values (3);
insert  into `interi`(`i`) values (4);
insert  into `interi`(`i`) values (5);
insert  into `interi`(`i`) values (6);
insert  into `interi`(`i`) values (7);
insert  into `interi`(`i`) values (8);
insert  into `interi`(`i`) values (9);



/*Table structure for table `percentuali` */


drop table if exists `percentuali`;

create table `percentuali` (
  `id` int(11) not null auto_increment,
  `anno` int(11) default null,
  `percentuale` tinyint(4) default null,
  primary key (`id`)
) engine=myisam auto_increment=3 default charset=latin1;



/*Data for the table `percentuali` */

insert  into `percentuali`(`id`,`anno`,`percentuale`) values (1,2009,5);
insert  into `percentuali`(`id`,`anno`,`percentuale`) values (2,2010,7);


select a.annoAttualizzazione, c.*
from (
  select 2000 + c.i*100 + d.i*10 + u.i as annoAttualizzazione
  from interi as c
  join interi as d
  join interi as u
  where ( c.i*100 + d.i*10 + u.i ) < 100
) a 
inner join contratti as c on ((a.annoAttualizzazione >= year(c.datacontratto)) and (a.annoAttualizzazione <= year(curdate()))) order by c.idcontratto,a.annoAttualizzazione;

Con una funzione dovrebbe essere possibile aggiungere il campo calcolato che ti manca.

[Vale1976]

Temo di essermi espresso male nel primo post.

Io devo prendere i valori dalla tabella Contratti e, tramite una store procedure che mi applichi le percentuali di attualizzazione in base all'anno, mi inserisca i dati in una nuova tabella, identica per struttura a quella dei contratti, ma con un campo in più che mi indichi l'anno di attualizzazione.

In sostanza per ciascun contratto della tabella Contratti, nella nuova tabella (che potremmo chiamare ContrattiAttualizzati), ci dovrebbero essere tante righe quanti sono gli anni di differenza tra l'anno della data corrente e l'anno della DataContratto

[nicola75ss]

Originariamente inviato da Vale1976
Temo di essermi espresso male nel primo post.

Io devo prendere i valori dalla tabella Contratti e, tramite una store procedure che mi applichi le percentuali di attualizzazione in base all'anno, mi inserisca i dati in una nuova tabella, identica per struttura a quella dei contratti, ma con un campo in più che mi indichi l'anno di attualizzazione.

In sostanza per ciascun contratto della tabella Contratti, nella nuova tabella (che potremmo chiamare ContrattiAttualizzati), ci dovrebbero essere tante righe quanti sono gli anni di differenza tra l'anno della data corrente e l'anno della DataContratto

Non ti sei espresso male. Ho capito. Prova la query. Se il risultato è quello atteso fai una insert into select per creare la nuova tabella. Ho solo usato un approccio diverso rispetto a una stored procedure.

[Vale1976]

ma a me serve una store procedure da poter poi schedulare su MySql Server ogni ora...

[nicola75ss]

Originariamente inviato da Vale1976
ma a me serve una store procedure da poter poi schedulare su MySql Server ogni ora...

Ma allora all'interno della sp bisogna di volta in volta tenere conto anche di quali record sono stati già caricati e andare a elaborare solo i nuovi inserimenti.

Mah, lascio la parola a gente più esperta.