Concetti di procedura memorizzata

La stored procedure è una raccolta di una o più istruzioni T-SQL raccolte in un gruppo di unità di elaborazione logica e archiviate nel server di database. Quando viene eseguita la stored procedure, alla prima chiamata, SQL Server la eseguirà e la memorizzerà in una cache, denominata cache dei piani, la prossima volta SQL Server riutilizzerà la cache dei piani, in modo da velocizzare l'elaborazione.gestione ottimale. Questo è molto efficace nell'ottimizzazione di grandi basi di dati con applicazioni ad alte prestazioni

Inoltre, la stored procedure è estremamente comoda per gli amministratori di database (DBA), aiuta DBA a creare blocchi di comandi con nome e inviarli agli sviluppatori senza preoccuparsi di ciò che contiene la stored procedure, occupandosi solo dei parametri di input e output. Ad esempio, scrivi una stored procedure che recupera l'elenco degli ordini per il mese, e poi lo dà allo sviluppatore per integrarlo nell'applicazione, lo sviluppatore non ha bisogno di interferire con il database per scrivere nuovo codice, basta chiamare lo stored procedura che hai dichiarato e usala.

Ad esempio, si scrive una procedura memorizzata per avere un elenco dei prodotti più venduti di giorno, quindi si invia questa archiviata al reparto sviluppo con il manuale utente, quindi il reparto DEV non è interessato al contenuto all'interno dello stored ma solo hanno bisogno di informazioni sui parametri passati e sul risultato restituito di archiviato.

Vantaggi dell'utilizzo delle stored procedure:

• Migliora la sicurezza: gli amministratori di database possono aumentare la sicurezza associando i privilegi alle procedure del negozio. Gli utenti possono ottenere l'autorizzazione per eseguire stored procedure anche se non hanno accesso a tabelle o viste.
• Esecuzione precompilata: la procedura memorizzata viene compilata alla prima esecuzione. Per ogni successiva esecuzione, SQL Server riutilizza la versione precompilata, riducendo i tempi di esecuzione e aumentando le prestazioni.
• Traffico client/server ridotto: la stored procedure aiuta a ridurre il traffico di sistema, quando viene eseguita l'istruzione T-SQL, la rete utilizza risorse separate per ogni esecuzione. Quando viene eseguita la stored procedure, le istruzioni SQL vengono raggruppate ed eseguite come un'unità, riducendo il traffico di rete.
• Riutilizzo del codice: le stored procedure possono essere riutilizzate più volte, eliminando la necessità di immettere ripetutamente centinaia di istruzioni Transact-SQL ogni volta che viene eseguita un'attività simile.

Tipi di stored procedure

Procedure memorizzate definite dall'utente :

possono essere chiamate procedure memorizzate personalizzate, queste procedure vengono utilizzate per riutilizzare le istruzioni T-SQL per l'elaborazione delle attività lapwj. Esistono due tipi di stored procedure definite dall'utente: stored procedure T-SQL e stored procedure Common Language Runtime (CLR). Le stored procedure CLR si basano su diversi metodi di .NET Framework, entrambi in grado di accettare e restituire parametri.

Estendere le procedure archiviate:

Estendi stored procedure aiuta SQL Server a interagire con il sistema operativo. Le stored procedure estese non si trovano negli oggetti di SQL Server. Si tratta di stored procedure sviluppate come Dinamic Link Libraries (DDL) che vengono eseguite all'esterno dell'ambiente SQL Server. L'applicazione interagisce con SQL Server e chiama la DLL durante l'esecuzione, SQL Server alloca spazio per eseguire le stored procedure estese . Le stored procedure estese utilizzano il prefisso 'xp'

Procedure memorizzate di sistema:

Le procedure memorizzate di sistema vengono comunemente utilizzate per interagire con le tabelle di sistema e gestire attività amministrative come l'aggiornamento delle tabelle di sistema, le procedure memorizzate di sistema con il prefisso 'sp_'. Queste procedure si trovano nel database Risorsa . Le procedure possono essere trovate nello schema sys di ogni sistema o database definito dall'utente. Le stored procedure di sistema consentono autorizzazioni GRANT, DENY, REVOKE .

Una stored procedure di sistema è un insieme precompilato di istruzioni T-SQL che verranno eseguite come unità. Le procedure di sistema vengono utilizzate nell'amministrazione del database e nella gestione delle attività e delle informazioni del sistema. Queste procedure forniscono una soluzione per accedere facilmente alle informazioni sui metadati sugli oggetti del database come tabelle di sistema, tabelle definite dall'utente, viste e indici.

Le procedure memorizzate di sistema vengono visualizzate logicamente nello schema sys del sistema e nel database definito dall'utente. Quando si fa riferimento a una stored procedure di sistema, viene utilizzato l'identificatore dello schema sys . Le stored procedure di sistema nel sistema vengono archiviate fisicamente in un database nascosto all'interno di una risorsa del database con il prefisso sp_ . Le stored procedure di sistema sono di proprietà dell'amministratore del database (il più alto livello di amministrazione nel sistema del database).

Nota: le tabelle di sistema vengono create per impostazione predefinita al momento della creazione di un nuovo database, queste tabelle memorizzano informazioni sui metadati sugli oggetti definiti dall'utente come tabelle e viste. Gli utenti non possono accedere o aggiornare le tabelle di sistema utilizzando le procedure memorizzate di sistema a meno che non siano concessi i privilegi dall'amministratore del database.

Classificazione delle stored procedure di sistema

• Procedure memorizzate del catalogo: tutte le informazioni sulle tabelle nel database sono archiviate in un insieme di tabelle chiamato catalogo di sistema. Le informazioni dal catalogo di sistema possono essere recuperate utilizzando le procedure di catalogo. Ad esempio, L sp_tables è un catalogo stored procedure che visualizza un elenco delle tabelle del database corrente
• Procedure archiviate di sicurezza: le procedure archiviate di sicurezza vengono utilizzate per gestire la sicurezza nel database. Ad esempio, sp_changedbowner è una stored procedure di sicurezza utilizzata per modificare il proprietario del database corrente.
• Procedure memorizzate del cursore: le procedure del cursore vengono utilizzate per implementare le funzioni del puntatore. Ad esempio sp_cursor_list è una stored procedure che recupera tutti i puntatori aperti dalla connessione e ne descrive le proprietà.
• Procedure memorizzate di query distribuite: le procedure memorizzate distribuite vengono utilizzate per amministrare le query distribuite. Ad esempio, sp_indexes è una stored procedure di query distribuita che restituisce informazioni sull'indice per una determinata tabella.
• Database Mail e SQL Mail Stored Procedures: utilizzate per gestire le attività che lavorano con la posta elettronica nel server SQL. Ad esempio, sp_send_dbmail è una stored procedure di posta del database che invia un messaggio di posta elettronica a un destinatario specificato. Il contenuto dell'e-mail può essere un set di risultati, un file allegato o entrambi.

Procedure archiviate temporanee

Le stored procedure create per l'utilizzo temporaneo all'interno di una sessione sono denominate procedure memorizzate temporanee . Queste procedure sono archiviate nella tabella tempdb . La tabella di sistema tempdb è una risorsa globale disponibile per tutti gli utenti che si connettono tramite l'istanza di SQL Server. Contiene tutte le tabelle temporanee e le procedure memorizzate temporanee.

SQL Server supporta due tipi di stored procedure temporanee, locali e globali, con le differenze seguenti:

Nota: una sessione viene stabilita quando l'utente si connette al database e termina quando l'utente si disconnette. Il nome completo della stored procedure temporanea globale, incluso il prefisso ##, non può superare i 128 caratteri. Il nome finale della stored procedure temporanea locale, incluso il prefisso #, non può superare i 116 caratteri.

Procedure di archiviazione remota

Le stored procedure che possono essere eseguite su SQL Server remoto sono chiamate stored procedure remote. La procedura remota può essere utilizzata solo se il server consente l'accesso remoto (accesso remoto, non accesso locale). Quando la stored procedure remota viene eseguita da un'istanza locale di SQL Server al client, l'istruzione potrebbe riscontrare un errore di interruzione. Quando si verifica un errore, il comando che ha causato l'errore verrà terminato, ma la stored procedure remota continuerà a essere eseguita.

Stored procedure estese

Le stored procedure estese vengono utilizzate per gestire attività che non possono essere gestite dalle normali sintassi T-SQL. Le stored procedure estese utilizzano il prefisso 'xp_' . Queste stored procedure verranno archiviate nello schema dbo del database master.

Sintassi:

 EXECUTE <procedure_name>

Esempio utilizzando la stored procedure estesa xp_fileexist per verificare se il file Mytext.txt esiste:

 EXECUTE xp_fileexist 'CMyTest.txt'

Procedura memorizzata definita dall'utente

In SQL Server, gli utenti possono creare stored procedure personalizzate per eseguire attività diverse, queste stored procedure sono denominate stored procedure personalizzate o definite dall'utente.

Ad esempio, se la tabella Customer_Details archivia tutti i dati del cliente, dovremmo scrivere istruzioni T-SQL ogni volta che desideriamo visualizzare i dati dettagliati sul cliente. Possiamo invece creare una stored procedure costom che visualizza i dettagli del cliente ogni volta che la procedura viene eseguita per il riutilizzo.

La creazione di una stored procedure personalizzata richiede l'autorizzazione CREATE PROCEDURE nel database e l'autorizzazione dello schema ALTER per la procedura creata.

Sintassi:

 CREATE {PROC|PROCEDURE} proc_ame [{@parameter data_type}] AS <sql_statement>

Esempio di inizializzazione di una stored procedure personalizzata denominata uspGetCustTerritory utilizzata per visualizzare i dettagli del cliente:

 use AdventureWorks2019 CREATE PROCEDURE uspGetCustTerritory AS SELECT TOP 10 CustomerID,Customer.TerritoryID,Sales.SalesTerritory.Name FROM Sales.Customer JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID go exec uspGetCustTerritory

Utilizzo dei parametri (parametri)

La potenza della stored procedure deriva dal poter inserire parametri al suo interno, i dati verranno passati nella stored procedure tramite il programma chiamante, ci sono 2 tipi di parametri come segue:

• I parametri di input consentono al programma chiamante di passare i valori nella procedura memorizzata. Questi valori vengono acquisiti nella variabile definita nelle stored procedure.
• I parametri di output consentono alla stored procedure di restituire i valori al programma chiamante. Questi valori vengono raccolti nella variabile del programma chiamante.

Parametri di input

I valori vengono passati dal programma chiamante alla stored procedure, questi valori vengono acquisiti nella variabile definita nelle stored procedure. Il parametro di input viene definito al momento della creazione della stored procedure. I valori passati al parametro di input possono essere variabili o costanti, il valore verrà passato alla stored procedure al momento della chiamata della procedura. Le procedure del negozio gestiscono attività specifiche utilizzando questi valori.

Sintassi:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Ad esempio, creare una stored procedure uspGetSales con il parametro territorial per ottenere il nome del territorio e visualizzare i dettagli di vendita e l'ID venditore per questo territorio. Quindi, il codice che esegue la stored procedure con il valore 'Northwest' verrà passato come parametro di input:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetSales @territory varchar(40) AS SELECT BusinessEntityID, B.SalesYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name=@territory; GO exec uspGetSales 'Northwest'

Parametri di uscita

Le stored procedure a volte devono restituire l'output al programma chiamante. Passerà i dati dalla procedura memorizzata al programma chiamante e sarà elaborato attraverso i parametri di output . I parametri di output sono definiti al momento della procedura memorizzata. Per specificare il parametro di output, la parola chiave OUTPUT viene utilizzata quando si dichiara il parametro. E chiamando l'istruzione è anche necessario definire la variabile con la parola chiave OUTPUT.

Sintassi:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> OUTPUT AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Esempio di procedura memorizzata upsGetTotalSales con parametro di input @territory per ottenere il nome del territorio e parametro di output @sum per ottenere il totale delle vendite dall'inizio dell'anno:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetTotalSales @territory varchar(40), @sum int OUTPUT AS SELECT @sum= SUM(B.SalesYTD) FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Nel passaggio successivo, eseguiamo la procedura memorizzata sopra dichiarando la variabile @sumsale per ricevere il valore di output.

 DECLARE @sumsale int; exec uspGetTotalSales 'NorthWest', @sumsale OUTPUT; SELECT @sumsale AS 'Total sales northWest';

I parametri OUTPUT hanno le seguenti proprietà:

• Impossibile trasportare il tipo di dati di testo o immagine
• L'istruzione call deve contenere una variabile per ricevere il valore di output.
• Variabile che può essere utilizzata nella successiva richiamata dell'istruzione T-SQL da restituire all'utente

La clausola OUTPUT restituisce informazioni per ogni riga in cui sono state eseguite le istruzioni INSERT, UPDATE e DELETE. Questa clausola è utile per ottenere l'identità o i valori di colonna calcolati dopo aver eseguito un'operazione INSERT o UPDATE.

Naturalmente, possiamo anche utilizzare SSMS per creare stored procedure .

Procedura memorizzata ALTER (Modifica).

Le autorizzazioni associate alla stored procedure vengono perse quando la stored procedure viene reinizializzata. Tuttavia, quando vengono modificati tramite l'istruzione ALTER, i permessi definiti per la procedura rimangono nello stato.

Sintassi:

 ALTER PROCEDURE <procedure_name> @parameter <data_type> [OUTPUT] [WITH {ENCRYPTION|RECOMPILE}] AS <sql_statement>

Modificare la definizione di una stored procedure denominata uspGetTotals per aggiungere una colonna CostYTD nella tabella Sales.SalesTerritory

 ALTER PROCEDURE [dbo].[uspGetTotal] @territory varchar = 40 AS SELECT BusinessEntityID, B.SalesYTD, B.CostYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Nota: quando si modifica la definizione nella procedura memorizzata, gli oggetti dipendenti possono presentare un errore di esecuzione, questo problema si verifica se gli oggetti dipendenti non vengono aggiornati per riflettere la modifica della procedura memorizzata.

Procedura memorizzata DROP

Le stored procedure possono essere eliminate quando non sono più necessarie.Se il programma richiama una procedura eliminata, verrà restituito un errore.

Se viene creata una nuova procedura con lo stesso nome e gli stessi parametri della procedura eliminata, tutte le chiamate alla procedura precedente verranno eseguite correttamente, poiché farà riferimento alla nuova procedura con lo stesso nome e gli stessi parametri della procedura eliminata.

Prima di eliminare la stored procedure, è possibile eseguire una stored procedure di sistema denominata sp_depends per identificare quali oggetti dipendono dalla procedura.

Sintassi:

 DROP PROCEDURE <procedure_name>

 DROP PROCEDURE uspGetTotals

Procedure memorizzate nidificate

SQL Server 2019 consente di richiamare le stored procedure all'interno di altre stored procedure. Questa architettura della procedura memorizzata chiama un'altra procedura memorizzata denominata procedura memorizzata nidificata.

Quando una stored procedure richiama un'altra stored procedure, si dice che il livello di nidificazione viene aumentato di uno. Allo stesso modo, quando la procedura memorizzata completa l'esecuzione e passa il flusso di controllo alla procedura memorizzata chiamante, il livello di annidamento viene ridotto di uno. Il livello di nidificazione massimo supportato da SQL Server 2019 è 32.

Per esempio:

 CREATE PROCEDURE NestedProcedure AS BEGIN EXEC uspGetCustTerritory EXEC uspGetSales 'France' END