Ricorsività per lettura albero - problema di cursori?

mercoledì 20 settembre 2006 - 15.02

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

nullatore Profilo | Junior Member

191 messaggi | Data Invio: mer 20 set 2006 - 15:02

Ho un albero N-ario (che rappresenta una gerarchia di persone) mappato sul mio DB (sql2005) con una tabella siffatta:
una colonna la matricola del soggetto e una colonna la matricola del suo capo.
Devo creare una procedura che dalla matricola mi dia tutti i suoi dipendenti (diretti o indiretti...insomma una lettura di un albero partendo da un suo nodo).
Ho provato in questo modo:

PROCEDURE [dbo].[Dipendenti] @nodo varchar[20]
AS
BEGIN

DECLARE @valore varchar[20];
DECLARE cur CURSOR FOR SELECT login FROM Gerarchia WHERE capo=@nodo;
OPEN cur;
FETCH NEXT FROM cur INTO @valore;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC Dipendenti @valore;
FETCH NEXT FROM cur INTO @valore;
END
CLOSE cur;
DEALLOCATE cur;
END

2 domande: è questa la strada? Se sì, questo codice mi da problema con i cursori...ogni volta che chiamo ricorsivamente la store_procedure dovrei creare un cursore nuovo...ma come?

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 20 set 2006 - 15:25

>Ho un albero N-ario (che rappresenta una gerarchia di persone)
>mappato sul mio DB (sql2005) con una tabella siffatta:
>una colonna la matricola del soggetto e una colonna la matricola
>del suo capo.

Non mi stancherò mai di ripeterlo, 4 righe di codice SQL valgono più di 1000 parole!
Posta i comandi DDL delle tabelle, alcune righe di codice ed il risultato che vuoi ottenere.

>Devo creare una procedura che dalla matricola mi dia tutti i
>suoi dipendenti (diretti o indiretti...insomma una lettura di
>un albero partendo da un suo nodo).
>Ho provato in questo modo:

I cursori sono l'ultima cosa da considerare quando si interroga un RDBMS.
Per quanto riguarda le gerarchie, SQL Server 2005 offre le Common Table Expression ricorsive (recursive CTE):
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1107414,00.html?FromTaxonomy/pr/301329

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

TheGramix Profilo | Newbie

13 messaggi | Data Invio: gio 21 set 2006 - 14:43

Se non vuoi modificare nulla prova così:

PROCEDURE [dbo].[Dipendenti] @nodo varchar[20]
AS
BEGIN

' >>
DECLARE @cur CURSOR
' <<

DECLARE @valore varchar[20];
SET @cur = CURSOR FOR SELECT login FROM Gerarchia WHERE capo=@nodo;
OPEN @cur;
FETCH NEXT FROM @cur INTO @valore;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC Dipendenti @valore;
FETCH NEXT FROM @cur INTO @valore;
END
CLOSE @cur;
DEALLOCATE @cur;
END
-----------------------------------------------------------------------------------
E' difficile acchiappare un gatto nero in una stanza buia
soprattutto quando non c'è.

nullatore Profilo | Junior Member

191 messaggi | Data Invio: dom 24 set 2006 - 10:50

>Se non vuoi modificare nulla prova così:
..........

In questo mondo sembra proprio funzionare. Ovviamente credo che la soluzione con le CTE sia piu' professionale...ma rimaniamo su questa.
Per concludere questo thread vorrei chiedere un ultima cosa.
Come posso fare l'union di una select con il risultato della procedure?
Ad esempio (riporto solo il ciclo WHILE modificato)

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT login FROM Gerarchia WHERE capo=@nodo UNION (EXEC Dipendenti @valore);
FETCH NEXT FROM @cur INTO @valore;
END

C'e' un modo per farlo funzionare? Oppure devo creare una tabella d'appoggio, popolarla, selectarla e cancellarla?
Grazie 1000

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: dom 24 set 2006 - 10:56

>In questo mondo sembra proprio funzionare. Ovviamente credo che
>la soluzione con le CTE sia piu' professionale...ma rimaniamo
>su questa.

Non è questione di "professionalità" ma di efficienza. I CTE sono stati introdotti in SQL Server 2005 appunto per la generazione di query ricorsive...

>Per concludere questo thread vorrei chiedere un ultima cosa.
>Come posso fare l'union di una select con il risultato della
>procedure?
...
>SELECT login FROM Gerarchia WHERE capo=@nodo UNION (EXEC Dipendenti
>@valore);

Non puoi, devi necessariamente passare da una tabella temporanea o da una variabile TABLE, valorizzandole con il comando INSERT...EXEC; a questo punto puoi fare una SELECT ed utilizzare l'operatore UNION per "fondere" i dati con l'altra SELECT.

>C'e' un modo per farlo funzionare? Oppure devo creare una tabella
>d'appoggio, popolarla, selectarla e cancellarla?
Se definisci una tabella temporanea locale nel corpo della stored procedure verrà automaticamente distrutta quando andrai fuori scope, ovvero quando terminerà l'esecuzione della stored procedure.

>Grazie 1000
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !