Stored Procedures (motore del database)

Una stored procedure in SQL Server è un gruppo di una o più istruzioni Transact-SQL o un riferimento a un metodo CLR (Common Runtime Language) di Microsoft .NET Framework. Le procedure sono simili a costrutti in altri linguaggi di programmazione perché possono:

• Accettare i parametri di input e restituire più valori sotto forma di parametri di output al programma chiamante.

• Contiene istruzioni di programmazione che eseguono operazioni nel database. Queste includono la chiamata ad altre procedure.

• Restituisce un valore di stato a un programma chiamante per indicare l'esito positivo o negativo e il motivo dell'errore.

Vantaggi dell'uso di procedure memorizzate

Nell'elenco seguente vengono descritti alcuni vantaggi dell'utilizzo delle procedure.

Traffico di rete server/client ridotto
I comandi in una stored procedure vengono eseguiti come un solo batch di codice. In questo modo è possibile ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la stored procedure viene inviata attraverso la rete. Senza l'incapsulamento del codice fornito da una routine, ogni singola riga di codice dovrà attraversare la rete.

Maggiore sicurezza
Tramite una stored procedure, più utenti e programmi client sono in grado di eseguire operazioni su oggetti di database sottostanti, anche se gli utenti e i programmi non dispongono di autorizzazioni dirette su tali oggetti sottostanti. La stored procedure consente di controllare quali processi e attività vengono eseguiti e di proteggere gli oggetti di database sottostanti. In questo modo si elimina la necessità di concedere autorizzazioni a livello di singolo oggetto, semplificando i livelli di sicurezza.

La clausola EXECUTE AS può essere specificata nell'istruzione CREATE PROCEDURE per consentire la rappresentazione di un altro utente o consentire agli utenti o alle applicazioni di eseguire determinate attività di database senza richiedere autorizzazioni dirette per gli oggetti e i comandi sottostanti. Ad esempio, alcune azioni come TRUNCATE TABLE, non dispongono di autorizzazioni concesse. Per eseguire TRUNCATE TABLE, l'utente deve disporre delle autorizzazioni ALTER per la tabella specificata. La concessione di autorizzazioni ALTER utente per una tabella potrebbe non essere ideale perché l'utente avrà effettivamente autorizzazioni ben oltre la possibilità di troncare una tabella. Incorporando l'istruzione TRUNCATE TABLE in un modulo e specificando che il modulo viene eseguito come utente che dispone delle autorizzazioni per modificare la tabella, è possibile estendere le autorizzazioni per troncare la tabella all'utente a cui si concedono le autorizzazioni EXECUTE per il modulo.

Quando si chiama una routine in rete, è visibile solo la chiamata per eseguire la procedura. Pertanto, gli utenti malintenzionati non possono visualizzare nomi di oggetti di tabella e di database, incorporare Transact-SQL istruzioni personalizzate o cercare dati critici.

L'uso dei parametri di procedura consente di proteggersi dagli attacchi SQL injection. Poiché l'input del parametro viene considerato come valore letterale e non come codice eseguibile, è più difficile per un utente malintenzionato inserire un comando nelle istruzioni Transact-SQL all'interno della procedura e compromettere la sicurezza.

Le procedure possono essere crittografate, consentendo di offuscare il codice sorgente. Per altre informazioni, vedere Crittografia di SQL Server.

Riutilizzo del codice
Il codice per qualsiasi operazione di database ripetitiva è il candidato perfetto per l'incapsulamento procedurale. Ciò elimina le riscrizioni inutili dello stesso codice, riduce l'incoerenza del codice e consente l'accesso e l'esecuzione del codice da parte di qualsiasi utente o applicazione che dispone delle autorizzazioni necessarie.

Semplificazione della manutenzione
Quando si effettua la chiamata delle stored procedure tramite le applicazioni client e si mantengono le operazioni nel database solo nel livello dati, in caso di modifiche nel database sottostante è necessario aggiornare unicamente le stored procedure. Il livello applicazione rimane separato e non deve sapere come apportare modifiche ai layout, alle relazioni o ai processi del database.

Prestazioni migliorate
Per impostazione predefinita, una routine compila la prima volta che viene eseguita e crea un piano di esecuzione riutilizzato per le esecuzioni successive. Poiché Query Processor non deve creare un nuovo piano, l'elaborazione della procedura richiede in genere meno tempo.

Se sono state apportate modifiche significative alle tabelle o ai dati a cui fa riferimento la procedura, il piano precompilato può effettivamente causare un rallentamento della procedura. In questo caso, ricompilare la procedura e forzare un nuovo piano di esecuzione può migliorare le prestazioni.

Tipi di stored procedure

personalizzato
È possibile creare una procedura definita dall'utente in un database definito dall'utente o in tutti i database di sistema, ad eccezione del database resource . La procedura può essere sviluppata in Transact-SQL o come riferimento a un metodo CLR (Common Runtime Language) di Microsoft .NET Framework.

Provvisorio
Le procedure temporanee sono una forma di procedure definite dall'utente. Le procedure temporanee sono come una procedura permanente, ad eccezione delle procedure temporanee vengono archiviate in tempdb. Esistono due tipi di procedure temporanee: locale e globale. I due tipi differiscono per i nomi, la visibilità e la disponibilità. Le procedure temporanee locali hanno un segno di numero singolo (#) come primo carattere dei nomi; sono visibili solo alla connessione utente corrente e vengono eliminati quando la connessione viene chiusa. Le procedure temporanee globali hanno due segni numerici (##) come primi due caratteri dei nomi; sono visibili a qualsiasi utente dopo la creazione e vengono eliminati alla fine dell'ultima sessione usando la procedura.

Sistema
Le procedure di sistema sono incluse in SQL Server. Vengono archiviati fisicamente nel database risorse interno, nascosto e vengono visualizzati logicamente nello schema sys di ogni database definito dal sistema e dall'utente. Inoltre, il database msdb contiene anche stored procedure di sistema nello dbo schema usate per la programmazione di avvisi e attività. Poiché le procedure di sistema iniziano con il prefisso sp_, è consigliabile non usare questo prefisso quando si assegnano nomi a procedure definite dall'utente. Per un elenco completo delle procedure di sistema, vedere System Stored Procedures (Transact-SQL)

SQL Server supporta le procedure di sistema che forniscono un'interfaccia da SQL Server a programmi esterni per varie attività di manutenzione. Queste procedure estese usano il prefisso xp_. Per un elenco completo delle stored procedure estese generali, vedere General Extended Stored Procedures (Transact-SQL).

User-Defined estesa
Le procedure estese consentono di creare routine esterne in un linguaggio di programmazione, ad esempio C. Queste procedure sono DLL che un'istanza di SQL Server può caricare ed eseguire dinamicamente.

Annotazioni

Le procedure memorizzate estese verranno rimosse in una prossima versione di SQL Server. Non usare questa funzionalità in un nuovo progetto di sviluppo e modificare non appena possibile le applicazioni in cui è attualmente implementata. Creare invece procedure CLR. Questo metodo offre un'alternativa più affidabile e sicura alla scrittura di procedure estese.

Attività correlate

Descrizione attività	argomento
Viene descritto come creare una stored procedure.	Creare una procedura memorizzata
Viene descritto come modificare una stored procedure.	Modificare una procedura memorizzata
Viene descritto come eliminare una stored procedure.	Eliminare una procedura memorizzata
Il testo descrive come eseguire una stored procedure.	Eseguire una procedura memorizzata
Viene descritto come concedere le autorizzazioni per una stored procedure.	Concedi autorizzazioni per una stored procedure
Descrive come restituire i dati da una procedura memorizzata a un'applicazione.	Restituire dati da una procedura memorizzata
Viene descritto come ricompilare una stored procedure.	Ricompilare una procedura memorizzata
Viene descritto come rinominare una stored procedure.	Rinominare una procedura memorizzata
Viene descritto come visualizzare la definizione di una stored procedure.	Visualizzare la definizione di una stored procedure
Viene descritto come visualizzare le dipendenze di una stored procedure.	Visualizzare le Dipendenze di una Stored Procedure

Contenuto correlato

Procedure memorizzate CLR