Procedure memorizzate in SQL | Una panoramica

In questo blog, discuteremo delle stored procedure in SQL che puoi utilizzare per salvare un set di codice e utilizzarlo ripetutamente ogni volta che ne hai bisogno. Le stored procedure sono simili alle viste. Tuttavia, puoi eseguire operazioni di tabella comuni come DROP, TRUNCATE, DELETE, ecc. Con procedure memorizzate che non puoi eseguire con le viste.

Anche le stored procedure sono precompilate in modo che vengano eseguite più velocemente delle visualizzazioni. Inoltre riduce al minimo la quantità di dati inviati al server del database.

Sommario

• Sintassi per le stored procedure in SQL: senza parametri
• Sintassi per le stored procedure in SQL: con parametri
• Esempio di scenario di stored procedure in SSMS
• Secondo scenario di esempio
• Terzo scenario di esempio
• Posizione delle stored procedure salvate in SQL
• Creazione di una stored procedure in SQL con parametri
• Creazione di una stored procedure con un valore predefinito
• Conclusione

Sintassi per le stored procedure in SQL: senza parametri

Per creare una stored procedure senza parametri, è necessario iniziare con la funzione CREATE . Quindi aggiungi la funzione PROC , digita il nome della procedura subito dopo e aggiungi la funzione AS .

La creazione di una stored procedure in è simile alla creazione di tabelle e viste. Tuttavia, la differenza sta nel processo di acquisizione dei dati.

Ad esempio, se vogliamo ottenere dati da una vista, usiamo " SELECT * FROM view_name ". D'altra parte, per le procedure memorizzate, usiamo EXEC che significa "esegui", quindi digitiamo il nome della procedura memorizzata dopo.

Una volta eseguita una procedura memorizzata , verranno visualizzate anche le informazioni in base alle istruzioni o ai comandi che hai aggiunto.

Sintassi per le stored procedure in SQL: con parametri

Passiamo alla . Il processo è simile all'esempio precedente che ho dimostrato. L'unica differenza è che devi includere i parametri prima della funzione AS e anche il tipo di dati subito dopo.

Puoi anche utilizzare il parametro che hai dichiarato nella creazione della stored procedure nei tuoi comandi. Questo è per te per risparmiare tempo inserendo più valori. Ad esempio, invece di digitare manualmente customerID1 , customerID2 e così via, abbiamo utilizzato @custid o il parametro per evitare di avere un lungo set di codice.

Si noti che quando si utilizza o si include un parametro, è importante utilizzare il simbolo @ . Puoi anche includere tutti i parametri che desideri, non solo uno. Questo è il modo in cui eseguiamo le stored procedure con i parametri.

Come puoi notare, abbiamo utilizzato anche il parametro con valore pari a 1 nell'esecuzione della stored procedure. Questo porterà solo le informazioni di custid1 .

Esempio di scenario di stored procedure in SSMS

Facciamo altri esempi di stored procedure in SQL. In prima istanza, creerò una stored procedure di esempio utilizzando il comando evidenziato di seguito.

Per convenzione di denominazione nella creazione di una stored procedure, di solito usiamo "usp" o "sp" per indicare che si tratta di una stored procedure. Se ti stai chiedendo cosa significhi "usp", significa semplicemente procedura memorizzata definita dall'utente. 

Successivamente, scriveremo query per la stored procedure da elaborare quando viene eseguita.

Le query nell'esempio precedente rimuoveranno semplicemente la tabella denominata dbo.stageOrders . Quindi ricreerà dbo.stageOrders con i dati della tabella Sales.SalesOrderHeader . 

Creiamo questa stored procedure evidenziando il seguente codice e cliccando sul pulsante Execute .

Successivamente, dovresti vedere un messaggio come questo.

Quindi ora eseguiremo la stored procedure eseguendo EXEC usp_TEST .

Dopo l'esecuzione della stored procedure usp_TEST , dbo.stageOrders ora dovrebbe avere i dati da Sales.SalesOrderHeader . Controlliamo il contenuto di dbo.stageOrders eseguendo il comando seguente.

Di conseguenza, questi sono i dati che abbiamo creato in dbo.stageOrders in base ai comandi che abbiamo aggiunto alla nostra stored procedure.

Secondo scenario di esempio

Passiamo a un altro esempio. Questa volta non voglio eliminare la tabella dbo.stageOrders e ricreare la stessa tabella con i dati provenienti dalla tabella Sales.SalesOrderHeader .

Invece, voglio solo che i dati dalla tabella Sales.SalesOrderHeader vengano visualizzati ogni volta che eseguo la stored procedure " usp_TEST " .

Per eseguire ciò, è necessario apportare modifiche alla stored procedure creata di recente utilizzando l' istruzione ALTER .

In questo esempio, abbiamo utilizzato l' istruzione ALTER che viene utilizzata per modificare la nostra procedura memorizzata. Puoi anche applicarlo a viste e tabelle. Una volta eseguito il codice nello screenshot sopra, dovrebbe cambiare le query che devono essere elaborate dalla nostra procedura memorizzata.

Questa volta non ricreerà nessuna tabella come abbiamo fatto in precedenza. Se eseguiamo " usp_TEST ", porterà solo i dati dalla tabella Sales.SalesOrderHeader .

Terzo scenario di esempio

Facciamo un altro esempio. Questa volta riutilizzeremo la tabella dbo.stageOrders che avevamo nel nostro primo esempio di creazione di una stored procedure in SSMS . Inizieremo con questo esempio selezionando la tabella dbo.stageOrders. 

I risultati dopo aver selezionato tutti i dati da dbo.stageOrders dovrebbero essere così.

Ora svuotiamo la tabella dbo.stageOrders eseguendo usp_TEST . Possiamo farlo aggiungendo il seguente comando.

Dopo aver aggiunto il comando TRUNCATE TABLE , dobbiamo aggiornare usp_TEST .

Successivamente, dovrebbe essere visualizzato un messaggio che dice che i comandi sono stati completati correttamente . Quindi eseguiremo nuovamente usp_TEST .

Dopo l'esecuzione usp_TEST , mostrerà tutti i record da Sales.SalesOrderHeader e svuoterà anche la tabella dbo.stageOrders . 

Per verificare se la tabella dbo.stageOrders è vuota, dobbiamo selezionarla utilizzando il seguente comando ed eseguire.

Dopo aver eseguito il codice sopra, possiamo vedere che la tabella dbo.stageOrders è ora vuota. Ciò è dovuto al comando TRUNCATE TABLE che abbiamo utilizzato per aggiornare usp_TEST .

Posizione delle stored procedure salvate in SQL

Se vuoi vedere dove viene salvata la procedura memorizzata in SQL , vai al pannello Esplora oggetti sul lato sinistro e fai clic sull'icona " + " prima del database su cui stai lavorando.

Quindi fare clic con il pulsante destro del mouse su Programmabilità e selezionare Aggiorna .

Espandere la cartella o il gruppo Programmabilità facendo clic sull'icona " + ". Quindi espandere il gruppo Stored procedure eseguendo lo stesso passaggio. All'interno del gruppo Stored Procedure, dovresti vedere dbo.usp_TEST .

Se si desidera verificare quali comandi o query sta eseguendo una procedura memorizzata specifica in SQL, è possibile fare clic con il pulsante destro del mouse su una procedura memorizzata e seguire i passaggi nello screenshot seguente.

Successivamente, aprirà la procedura memorizzata in una scheda diversa in cui è possibile visualizzare i comandi forniti in essa. Ecco come appare dbo.usp_TEST all'apertura.

Come puoi vedere, ci sono alcuni comandi predefiniti prima dell'istruzione CREATE . Puoi semplicemente eliminarlo se lo desideri. 

Ora sai come controllare quali comandi sta eseguendo una stored procedure in SQL .

Creazione di una stored procedure in SQL con parametri

Successivamente, creeremo una stored procedure con parametri. Ad esempio, utilizzeremo il seguente codice per creare una nuova stored procedure.

Nel codice di esempio, ho utilizzato lo stesso processo di creazione di una stored procedure denominata usp_GetCustomer . Quindi ho aggiunto un parametro che è @CustomerID con un tipo di input di INT .

Tieni presente che una volta aggiunto un parametro nella creazione di una procedura memorizzata, dovresti sempre fornire un parametro ogni volta che eseguirai un comando.

Vediamo cosa accadrà se eseguiamo usp_GetCustomer senza fornire un parametro.

Dopo aver eseguito usp_GetCustomer senza un parametro, è apparso un messaggio di errore. Ecco come apparirebbe se eseguissimo usp_GetCustomer con un parametro.

Con un parametro fornito, siamo in grado di ottenere un risultato corretto nell'esecuzione della nostra procedura memorizzata .

Creazione di una stored procedure con un valore predefinito

Se si desidera evitare di ricevere un messaggio di errore durante l'esecuzione di una stored procedure con un parametro, è possibile impostare un valore predefinito che fungerà da parametro predefinito.

Ad esempio, creeremo una stored procedure denominata usp_GetOrdersByYear .

Quindi aggiungerò un parametro @OrderYear con il tipo di input " INT " e un valore predefinito uguale a 2011 .

Se eseguiamo usp_GetOrdersByYear senza un parametro fornito, verranno visualizzati i record con l'anno 2011 .

D'altra parte, se eseguiamo usp_GetOrdersByYear con 2014 come dato parametro, dovrebbe visualizzare i record con l'anno 2014 .

Questo è il modo in cui utilizzi le stored procedure nelle tue attività quotidiane di gestione dei dati.

HAVING Clausola nelle funzioni di aggregazione SQL
ISNULL Funzione SQL nelle istruzioni Case
Estrazione dei dati SQL mediante OFFSET e FETCH

Conclusione

Tenendo conto di tutto, hai imparato cos'è una stored procedure in SQL e il suo scopo. Abbiamo anche discusso l' istruzione ALTER che viene utilizzata per apportare modifiche o aggiornamenti in una procedura memorizzata presente.

Inoltre, hai appreso che esistono diversi metodi per creare stored procedure in SQL e come evitare di ricevere errori durante l'esecuzione di una stored procedure fornendo un valore predefinito.

Ancora più importante, hai imparato a utilizzare le stored procedure per archiviare set di comandi per evitare di eseguire ripetutamente lunghi set di codice. Come promemoria finale, non dimenticare di utilizzare il simbolo " @ " quando fornisci un parametro.

Ti auguro il meglio,

Hafiz