aiuto stored procedure (stored function?)

[lilux]

Ciao ragazzi, ho delle tabelle che hanno i seguenti campi:
id, id1, id2, matching, posizione

- se id1=id2 il campo matching assume valore SI, altrimento NO
- se il campo matching assume valore SI, il campo posizione= campo id

Fin qui è tutto chiaro e le query le ho realizzate.
Il problema è che ho 356 tabelle siffatte quindi vorrei realizzare una stored procedure (o stored function ?) che faccia quanto detto per tutte le tabelle automaticamente, invece che farlo manualmente per ogni tabella.
Inoltre devo fissare un campo k (che può assumere un valore variabile-->verrà fissato di volta in volta)
Tutte le tabelle che hanno nel campo posizione un valore < o = a k hanno un valore SI mentre per valori >k hanno un valore NO.
Spero che possiate aiutarmi.

[nicola75ss]

Originariamente inviato da lilux
Ciao ragazzi, ho delle tabelle che hanno i seguenti campi:
id, id1, id2, matching, posizione

- se id1=id2 il campo matching assume valore SI, altrimento NO
- se il campo matching assume valore SI, il campo posizione= campo id

Ne abbiamo già discusso diverse volte sul fatto di salvare in tabella dei campi calcolati.
Comunque, se è quello che vuoi, sfrutta l'information_schema:

select concat("update ", table_name," set matching = if(id1=id2,'si','no'),posizione=if(id1=id2,id,null);") 
from information_schema.tables
where table_schema = 'nome_database'

copia-incolla l'output ed esegui tutte le query in successione. Ovviamente accertati prima del funzionamento della query su una singola tabella.

[lilux]

Ok, supponiamo che io disponga già di tabelle caratterizzate dai campi:

id1, id2, matching, posizione

come posso scrivere una funzione che prenda in input tutte le tabelle e che, in base al valore che assume una variabile k (valore che fisserò ogni volta che mando in esecuzione la query), assegni un SI alle tabelle che hanno nel campo 'posizione' un valore < o = a k mentre assegni un NO per valori >k.

[nicola75ss]

Non ho capito.

Vuoi passare un parametro k e fin lì ci siamo.
All'inizio pareva che il campo posizione venisse valorizzato col valore id solo nel caso id1 e id2 coincidessero. In tutti gli altri casi posizione è null o cosa?

A scanso di equivoci posta qualche record di esempio con un ipotetico parametro che passeresti e il risultato che la procedura dovrebbe generare.

[lilux]

id id1 id2 matching posizione
1 9 4 no /
2 4 3 no /
3 6 6 si 3
4 3 8 no /
5 8 9 no /


Fisso k=4.
Dato che ho un SI nella posizione 3 e 3< 4=k allora a questa mia tabella posso assegnare un SI che significherebbe: c'è un matching entro le prime k=4 righe.

Se in un secondo momento fisso k=2 allora alla mia tabella assegnerò un NO.

[nicola75ss]

Crea una nuova tabella che chiameremo risultati che conterrà l'elenco delle tabelle e il valore che memorizzerai (si/no).

create table risultati (
id int not null auto_increment primary key,
tabella varchar(50),
valore char(2)
);

insert into risultati (tabella)
select table_name from information_schema.tables
where table_schema = 'nome_database' and table_name <> 'risultati'

La stored procedure sarà così

delimiter //
create procedure cicla(in k int)
begin
declare finite int default 0;
declare tab varchar(50);
declare str varchar(200);
declare cur_tabella cursor for select table_name from information_schema.tables where table_schema = 'nome_database' and table_name <> 'risultati'; 
declare  continue handler for not found set finite = 1;
open cur_tabella;
mio_loop:loop
fetch cur_tabella into tab;
if finite = 1 then
leave mio_loop;
end if;
set @str = concat("update risultati 
		   set valore = 
	           case when (select sum(if(matching='si',1,0)) from (select matching from ",tab," order by id limit ",k,") as t) > 0 
		   then 'si' else 'no' end where tabella = '",tab,"'");
prepare stmt from @str;
execute stmt;
end loop;
close cur_tabella;
end //
delimiter ;

Tramite la quale ciclerai tutte le tabelle ad esclusione di quella risultati che verrà popolata all'interno del ciclo.

La richiamerai semplicemente così:

call cicla(4);

Ovviamente fatti un dump preventivo e poi testala.

[lilux]

Ti ringrazio per l'estrema disponibilità.
Scusa l'ignoranza ma per fare ciò devo necessariamente avere le mie tabelle in information_schema, giusto?

[nicola75ss]

Originariamente inviato da lilux
Scusa l'ignoranza ma per fare ciò devo necessariamente avere le mie tabelle in information_schema, giusto?

No.
L'information_schema è un database a sè stante disponibile a partire dalla versione 5 di mysql che contiene tutta una serie di metadati ed è accessibile in sola lettura.

Ti basta eseguire questa semplice query:

select table_name from information_schema.tables
where table_schema = 'nome_database'

per vedere il risultato.
Sul manuale trovi tutte le tabelle che lo compongono e le informazioni a cui puoi accedere.

http://dev.mysql.com/doc/refman/5.0/...on-schema.html

[lilux]

Scusami ancora ma quando faccio

call cicla(4)
ho il seguente errore:

[Err] 1054 - Unknown column 'matching' in 'field list'

eppure ho controllato, e nelle mie tabelle la colonna matching c'è sempre

[nicola75ss]

Originariamente inviato da lilux
Scusami ancora ma quando faccio

call cicla(4)
ho il seguente errore:

[Err] 1054 - Unknown column 'matching' in 'field list'

eppure ho controllato, e nelle mie tabelle la colonna matching c'è sempre

Verifica se ciò che dici è vero con questa query:

select t1.table_name from information_schema.tables as t1
left join (
select table_name from information_schema.columns
where column_name = 'matching' and table_schema = 'tuo_database') as t2
on t1.table_name = t2.table_name
where t2.table_name is null and table_schema = 'tuo_database'