Stored procedure (Motore di database)

Si applica a:SQL Serverdatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsSistema di Piattaforma Analitica (PDW)Database SQL in Microsoft Fabric

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

• Accettare parametri di input e restituire più valori sotto forma di parametri di output al programma che esegue la chiamata.

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

• Restituire un valore di stato a un programma che esegue la chiamata per indicare l'esito positivo o negativo (e il motivo dell'esito negativo).

Vantaggi dell'utilizzo delle stored procedure

L'elenco seguente descrive alcuni vantaggi derivanti dall'uso delle procedure.

Riduzione del traffico di rete server/client

I comandi in una procedura vengono eseguiti come un unico blocco di codice. Questo approccio può ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la procedura viene inviata attraverso la rete. Senza l'incapsulamento del codice fornito da una procedura memorizzata, ogni singola riga di codice dovrebbe 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 procedura controlla quali processi e attività vengono eseguiti e protegge gli oggetti del database sottostanti. Questo approccio elimina il requisito di concedere le autorizzazioni a livello di singolo oggetto e semplifica i livelli di sicurezza.

La EXECUTE AS clausola può essere specificata nell'istruzione CREATE PROCEDURE per consentire la rappresentazione di un altro utente o consentire a utenti o applicazioni di eseguire determinate attività di database senza richiedere autorizzazioni dirette per gli oggetti e i comandi sottostanti. Per alcune azioni ad esempio, come TRUNCATE TABLE, non è possibile concedere le autorizzazioni. Per eseguire TRUNCATE TABLE è necessario che l'utente disponga delle autorizzazioni ALTER per la tabella specificata. Non è consigliabile concedere a un utente le autorizzazioni ALTER su una tabella, perché l'utente disporrebbe di autorizzazioni ben superiori alla semplice possibilità di troncare la tabella. Incorporando l'istruzione TRUNCATE TABLE in un modulo e specificando che tale modulo venga eseguito come un utente che dispone di autorizzazioni per la modifica della tabella, è possibile estendere le autorizzazioni per il troncamento della tabella all'utente al quale si concedono le autorizzazioni EXECUTE sul modulo.

Quando un'applicazione chiama una stored procedure attraverso la rete, solo la chiamata per eseguire la stored procedure è visibile. Pertanto, gli utenti malintenzionati non possono visualizzare i nomi di oggetti database e tabelle, né incorporare le istruzioni di Transact-SQL personalizzate o cercare dati critici.

L'uso dei parametri di procedura aiuta a proteggersi dagli attacchi di SQL injection. Dal momento che l'input del parametro viene trattato come un valore letterale e non come codice eseguibile, è più difficile che un utente malintenzionato riesca a inserire un comando nelle istruzioni Transact-SQL all'interno della procedura e compromettere la sicurezza.

È possibile crittografare le procedure per offuscare il codice sorgente. Per altre informazioni, vedere Crittografia di SQL Server.

Riutilizzo del codice

Il codice per qualsiasi operazione di database ripetitiva è una candidata perfetta per l'incapsulamento nella procedura. Questo approccio elimina le riscrizioni inutili dello stesso codice, riduce l'incoerenza del codice e consente a qualsiasi utente o applicazione con le autorizzazioni necessarie di accedere ed eseguire il codice.

Semplificazione della manutenzione

Quando le applicazioni client chiamano le procedure e mantengono le operazioni del database nel livello dati, è sufficiente aggiornare le procedure per eventuali modifiche nel database sottostante. Il livello applicazione rimane separato e non deve conoscere le modifiche apportate a layout, relazioni o processi del database.

Prestazioni migliorate

Per impostazione predefinita, una routine compila la prima volta che viene eseguita e crea un piano di esecuzione che riutilizza per le esecuzioni successive. Poiché il processore di query non deve creare un nuovo piano, in genere la procedura richiede meno tempo per essere elaborata.

Se sono presenti modifiche significative alle tabelle o ai dati a cui fa riferimento la stored procedure, il piano precompilato potrebbe determinare in realtà un'esecuzione più lenta della stored procedure. In questo caso, ricompilare la stored procedure e forzare un nuovo piano di esecuzione può migliorare le prestazioni.

Tipi di procedure memorizzate

User-defined

È 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 .NET Framework.

Temporary

Le procedure temporanee sono un tipo di procedure definite dall'utente. Le procedure archiviate temporanee sono simili a una procedura archiviata permanente, tranne per il fatto che vengono archiviate in tempdb. Esistono due tipi di procedure memorizzate temporanee: locali e globali. 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 di numero (##) 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.

System

Le procedure di sistema sono incluse nel Motore di database. Vengono archiviati fisicamente nel database interno, nascosto Resource e vengono visualizzati logicamente nello sys schema di ogni database definito dal sistema e definito dall'utente. Inoltre, il database msdb contiene anche le procedure archiviate di sistema nello schema dbo utilizzate per la pianificazione di avvisi e processi. Poiché le procedure di sistema iniziano con il prefisso sp_, non usare questo prefisso quando si assegnano nomi a procedure definite dall'utente. Per un elenco completo delle procedure memorizzate di sistema, vedere Procedure di sistema memorizzate.

SQL Server supporta le procedure di sistema che forniscono un'interfaccia da SQL Server ai programmi esterni per diverse attività di manutenzione. Queste procedure estese utilizzano il prefisso xp_. Per un elenco completo delle procedure estese, vedere Procedure di memorizzazione generale estese.

Estensione definita dall'utente

Le stored procedure estese permettono di creare routine esterne in un linguaggio di programmazione come C e sono DLL che possono essere caricate ed eseguite in modo dinamico da un'istanza di SQL Server.

Attività correlate

Contenuti correlati

• Stored procedure CLR
• Risoluzione dei nomi posticipata