asp - asp.net - aspcode.it

COMMUNITY - Login
 Username:
 
 Password:
 
Voglio registrarmi!
Password dimenticata?
 Utenti on-line: 0
 Ospiti on-line: 60409
ASPCode.it - Store

  > > Articoli

Le stored procedure ricorsive in SQL Server

Data di pubblicazione: 26/09/2002        Voto della community: 3,78 (Votanti: 3)


Mi occupo spesso di realizzare applicazioni Web, ed altrettanto spesso mi è capitato di dover affrontare la gestione di dati strutturati gerarchicamente. Dopo i primi tentativi per raggiungere comunque il risultato desiderato, mi sono fermato a riflettere se non ci fosse una maniera standard, una sorta di pattern, da poter applicare in situazioni come queste. In questo modo avrei ottenuto un punto di partenza sempre a disposizione da adattare alla particolare applicazione, piuttosto che scrivere ogni volta una soluzione da zero.

Lo scenario

Riflettendo sul problema, un punto mi è saltato subito agli occhi: volevo un meccanismo standard legato alla strutturazione dei dati, piuttosto che ai dati stessi. Per manipolare una struttura spesso non c'è niente di meglio che un algoritmo ricorsivo, infatti esso si preoccupa di applicarsi alla struttura, indipendentemente dal valore oggettivo dei dati. Bene, a questo punto sapevo di cosa avrei avuto bisogno. Ora dovevo decidere come implementarlo.

Nelle applicazioni Web-based ci sono diversi punti in cui si può decidere di effettuare il lavoro di elaborazione pura dei dati. Io ho scelto di demandare il lavoro al DBMS che utilizzo più di frequente, cioè SQL Server. Quello che dovevo fare era allora scrivere una stored procedure ricorsiva.

Il problema

L'esempio che prendiamo in considerazione per esporre l'algoritmo utilizzato si preoccupa di disegnare un menu in modo dinamico. Gli item del menu sono memorizzati in una tabella e possono essere gestiti da un amministratore. Questi può aggiungere a suo piacimento items, o cancellarli, se crede. Indipendentemente dal lavoro dell'amministratore, comunque, quello che abbiamo sono una serie di dati organizzati gerarchicamente. Infatti ogni menu avrà una serie di suoi Item, che potrebbero avere i loro SubItems, e cosi via. Questo rende la struttura gerarchica studiata sufficientemente generica.

La tabella che contiene il menu avrà quindi una struttra del tipo:

I campi che danno luogo alla gerarchia sono idTem ed idParent. Mentre il primo rappresenta un identificatore del campo, il secondo lega il figlio al proprio genitore.

Il nostro esempio di applicazione consiste in una pagina ASP che richiama la stored procedure che si occupa di individuare gli elementi del menu e che li restituisce all'interno di un recordset, che verrà visualizzato all'interno della pagina.

L'algoritmo

L'algoritmo si basa su due Stored Procedure, MainRicor e Ricor. La prima si occupa di preparare l'ambiente, mentre la seconda è quella che esegue il lavoro di ricerca ricorsiva degli elementi. Vediamo il loro funzionamento nel dettaglio.

MainRicor ha bisogno di ricevere in input l'identificatore della radice del menu che si vuole esplorare. Il suo compito è quello di preparare una tabella temporanea che conterrà gli elementi del menu ritrovati e di richiamare la procedura ricorsiva, che riempirà questa tabella con gli items del menu. Perchè una tabella temporanea? Diciamo che questo semplifica molto le cose nel caso di accessi contemporanei. In un ambiente Web reale, non è raro che due utenti facciano richiesta della stessa pagina contemporaneamente. Allora ogni chiamata alla MainRicor fà si che venga creata una tabella temporanea unica, questa viene utilizzata solo per calcolare il menu relativo alla richiesta del singolo utente, e subito dopo distrutta. In questo modo si ha la certezza che due chiamate contemporanee, concorrenti, non possano portare a risultati sporchi, con accavallamenti o perdita di dati. Ma come è possibile creare una tabella unica? Per far questo dobbiamo ricorrere ad una feature di SQL Server. Tramite la variabile di sistema @@SPID è possibile conoscere l'identificatore interno che SQL Server associa al processo designato a soddisfare la richiesta di un determinato utente.

Conoscendo questo identificatore interno del processo, determinare un nome univoco per la tabella è semplicissimo:
SET @gtemp_table_name = '##TempTable' + convert( varchar(4), @@spid )

-- Stringa di costruzione della tabella....
SET @sql_statement_string = 'CREATE TABLE ' + @gtemp_table_name
                             +' ('
                             +' Description VARCHAR(50) NOT NULL,'
                             +' MLevel int NOT NULL'
                             +' )'

-- Creiamo la tabella.....
EXECUTE sp_executesql @sql_statement_string
Con le tre istruzioni precedenti si costruisce il nome della tabella, si prepara una stringa con il comando SQL per la sua creazione e quindi lo si esegue.

Per eseguire il comando contenuto nella stringa @sql_statement_string è stata utilizzata la funzione sp_executesql, introdotta con la versione 7.0 di SQL Server. Tale funzione permette di eseguire il contenuto di una stringa, in questo caso un comando SQL, in maniera più efficiente rispetto alla semplice execute.

Da notare come nel nome della tabella compaiano due simboli #. Questo indica a SQL Server che la tabella da creare è una tabella temporanea globale. Si è scelto di utilizzare una tabella temporanea globale poichè deve essere visibile da due diverse stored procedure. Se l'avessimo dichiarata locale, sarebbe stata visibile solo da MainRicor ed il tentativo di utilizzarla in Ricor avrebbe portato ad un errore. Infatti mentre una tabella locale ha come scope solo la procedura in cui è stata creata, una tabella globale può essere vista anche da tutte le procedure richiamate durante l'esecuzione della procedura che l'ha creata.

A questo punto inseriamo nella tabella la radice del nostro albero e quindi chiamiamo la stored Ricor per calcolarne il resto.
-- Inseriamo la radice dell'albero.....
SELECT @Description=Description, @Level=MLevel FROM Menu 
  WHERE idItem = @TopID AND idParent=0

IF @Level  = 0
  BEGIN
    SET @sql_statement_string = 'INSERT INTO '
                                + @gtemp_table_name
                                + ' VALUES(' + CAST(@@SPID as VARCHAR(10))
                                + ',' + ''''+ @Description + '''' + ','
                                + CAST(@Level as VARCHAR(10)) + ',' + '0' + ')'
 
        -- Eseguiamo il commando SQL...
        EXEC sp_executesql @sql_statement_string
  END

-- Costruiamo l'albero richiamando la procedura ricorsiva.....
EXEC Ricor @TopID,  @gtemp_table_name

La Stored Ricor

Il compito della Ricor è quello di andare alla ricerca di tutti quegli elementi che abbiamo come idParent il valore dell'identificatore idItem della Radice, passato come parametro alla MainRicor. Il meccanismo è molto semplice. Innanzitutto si costruisce un cursore locale, a partire dalla tabella Menu, in cui vengono caricati gli elementi della tabella per i quali l'idParent corrisponde a quello della radice a cui siamo interessati.
DECLARE curRecurse CURSOR LOCAL FOR 
  SELECT idItem, Description, MLevel FROM Menu WHERE idParent = @TopID
Costruito il cursore, lo scorriamo inserendo l'elemento corrente nella tabella di appoggio e successivamente richiamando la Ricor dandole come identificatore di radice l'idItem dell'elemento corrente. In questo modo troveremo tutti i suoi subitem, se ce ne sono.

Ogni iterazione ricorsiva produce un nuovo cursore. Per questo si è scelto di dichiaralo come locale. In questo modo quando un'iterazione termina, il cursore che essa ha creato viene distrutto.
-- facciamo pulizia delle risorse utilizzate...
CLOSE curRecurse
DEALLOCATE curRecurse
Le due righe qui sopra non fanno mai male... Il cursore all'uscita della ricorsione viene distrutto automaticamente, ma con queste istruzioni ci assicuriamo la deallocazione delle risorse, dunque è sempre una buona pratica!
Al ritorno da ogni ricorsione si prosegue con l'elemento successivo nel cursore sino ad attraversarlo completamente. Il ciclo seguente si occupa proprio di questo:
WHILE (@@FETCH_STATUS = 0)
   BEGIN
            :
            :
            FETCH NEXT FROM curRecurse INTO @idItem, @Description, @Level
   END
Se la variabile @@FETCH_STATUS ha valore 0, allora ci sono ancora elementi nel cursore. Quando assume il valore –1 vuol dire che si è superato l'ultimo elemento presente. Quando la prima chiamata a Ricor ritorna, nella MainRicor viene eseguita una Select sulla tabella temporanea che permette così di ottenere il recordset con gli elementi del menu desiderati.

Il Client ASP

Veniamo ora alla parte client, che comunque non contiene nulla di più che il codice per la chiamata della Stored MainRicor e quello per la visualizzazione del recordset. Il client è costituito dalla semplice pagina Ricor.asp, che trovate nel progetto da scaricare.

Un punto da evidenziare è la dichiarazione del path OLEDB per il corretto puntamento al database. Qui dovrete effettuare le modifiche del caso per far sì che l'esempio funzioni anche con il vostro database SQL Server ed, in particolare, si deve modificare il nome del catalogo e quello della macchina.

Quindi cambiate opportunamente i valori dei campi:
  • Initial Catalog = "nome catalogo"
  • Data Source = "nome macchina"
  • Workstation ID = "nome macchina"
... il resto è ordinaria amministrazione.

Eseguendo il codice, dovreste ottenere in output una pagina simile alla seguente:

Come avrete notato, in testa alla pagina è visualizzato anche il valore del SPID assegnato al vostro processo.

Conclusioni

Eccoci giunti alla fine di questa dissertazione sulle stored ricorsive. Forse alla prima impressione può sembrare un argomento ostico ma, come abbiamo visto, ce la siamo cavata con relativamente poche righe di codice. Ciò che più conta, invece, è che sono stati introdotti diversi concetti importanti sul metodo di analisi e sull'implementazione di questo utile strumento.

Naturalmente la soluzione adottata si presta facilmente ad essere estesa o applicata a casi più complessi. Ora tocca a voi...

[ Scarica il codice sorgente ]




Utenti connessi: 60409