Procedura Memorizzata SQL: Automatizzare e Ottimizzare le Query

Tutorial
SQL

Le stored procedure SQL sono insiemi di istruzioni SQL salvate e memorizzate in un database. Possono essere eseguite su richiesta per compiere operazioni di manipolazione e convalida dei dati, riducendo la necessità di scrivere codice SQL ripetitivo per operazioni comuni. Le stored procedure sono utili nella gestione del database perché promuovono l’efficienza e la riutilizzabilità. Inoltre, supportano una maggiore sicurezza e manutenibilità del database. In questo articolo, discuteremo come creare ed eseguire stored procedure SQL, casi d’uso comuni e le migliori pratiche.

Per iniziare, consiglio vivamente di seguire i corsi Introduzione a SQL e Impara SQL di DataCamp per apprendere le conoscenze di base sull’estrazione e l’analisi dei dati usando SQL. Inoltre, il SQL Basics Cheat Sheet, che puoi scaricare, è un utile riferimento poiché contiene tutte le funzioni SQL più comuni.

Cosa sono le Stored Procedure in SQL?

Una stored procedure in SQL è una raccolta di istruzioni SQL salvate e memorizzate all’interno del database. Lo scopo della stored procedure SQL è quello di eseguire una sequenza di operazioni su un database, come interrogare, inserire, aggiornare o eliminare dati.

A differenza delle normali query SQL, eseguite come comandi separati, le procedure memorizzate racchiudono un insieme di istruzioni SQL, rendendo facile riutilizzare il codice senza dover scrivere ripetutamente comandi SQL.

I vantaggi delle procedure memorizzate SQL includono i seguenti:

  • Riutilizzabilità del codice: Una volta creata, una procedura memorizzata può essere chiamata tutte le volte necessarie, eliminando la ridondanza nel codice SQL.
  • Prestazioni migliorate: Le procedure memorizzate spesso vengono eseguite più rapidamente perché sono precompilate e memorizzate sul server di database, riducendo la latenza di rete e il tempo di compilazione.
  • Sicurezza: Le stored procedure possono migliorare la sicurezza dei dati e il controllo sull’accesso ai dati sensibili concedendo agli utenti il permesso di eseguire una stored procedure senza avere accesso diretto alle tabelle.

Sintassi e struttura di base

La sintassi per la creazione di una stored procedure può variare leggermente a seconda del sistema di database (ad esempio, MySQL, SQL Server, Oracle). Di seguito è riportato un esempio generale utilizzando la sintassi di SQL Server:

-- Creare una stored procedure chiamata NomeProcedura
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- Dichiarazioni SQL qui
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

Nella sintassi sopra;

  • CREA PROCEDURA: Questo comando è utilizzato per definire una nuova procedura memorizzata.

  • NomeProcedura: Il nome dato alla procedura memorizzata. Deve essere unico all’interno del database.

  • @Parametro1, @Parametro2: I parametri sono facoltativi; consentono alla procedura di ricevere dati in input. Ogni parametro è definito con un simbolo @ e un tipo di dato (ad esempio, INT, VARCHAR(50)).

  • COME INIZIO…FINE: Le istruzioni SQL all’interno di INIZIO e FINE formano il corpo della procedura, dove viene eseguita la logica principale. La procedura recupera record da una tabella in base a condizioni specifiche in questo esempio.

Parametri di input e output

I parametri di input e output ti permettono di passare valori da e verso una stored procedure.

Per esempio, se @Parameter1 è definito come parametro di input, può essere assegnato qualsiasi valore quando la procedura viene chiamata, influenzando la logica SQL o l’output. Nell’esempio qui sotto, il parametro @UserID recupera dati specifici relativi al UserID fornito.

-- Creare una procedura per recuperare i dati di un utente specifico per UserID
CREATE PROCEDURE GetUserData 
 -- Parametro di input: ID dell'utente da recuperare
    @UserID INT 
AS
BEGIN
    -- Selezionare tutte le colonne da Utenti dove UserID corrisponde al parametro di input
    SELECT * FROM Users WHERE UserID = @UserID;
END;

I parametri di output, definiti dalla parola chiave OUTPUT, consentono a una stored procedure di inviare un valore all’ambiente chiamante. Per esempio, se una procedura calcola uno sconto, potrebbe utilizzare un parametro di output per passarlo al programma chiamante.

-- Creare una procedura per calcolare il prezzo scontato
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: prezzo finale dopo lo sconto
AS
BEGIN
    -- Calcolare il prezzo finale applicando il tasso di sconto al prezzo originale
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Per chiamare questa procedura, useresti;

-- Dichiarare una variabile per memorizzare il prezzo finale dopo lo sconto
DECLARE @FinalPrice DECIMAL(10, 2);
-- Eseguire la procedura CalculateDiscount con un prezzo di 100 e uno sconto del 10%
-- Memorizzare l'output nella variabile @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Selezionare e visualizzare il prezzo finale scontato
SELECT @FinalPrice AS FinalPrice;

Procedure memorizzate in MySQL

Come avevo accennato, l’esecuzione di una procedura memorizzata in SQL può essere fatta in modi diversi a seconda del sistema di database e degli strumenti utilizzati.

Creazione di procedure memorizzate in MySQL

Creare una procedura memorizzata in MySQL implica definire il nome della procedura, i parametri e le istruzioni SQL che costituiscono il suo corpo. L’esempio seguente crea una procedura chiamata GetEmployeeDetails che accetta EmployeeID come parametro di input e recupera i dettagli per quel dipendente specifico.

DELIMITER $
-- Crea una procedura per recuperare i dettagli di un dipendente specifico tramite EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Seleziona tutte le colonne da Employees dove EmployeeID corrisponde al parametro di input
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Esecuzione di procedure memorizzate in MySQL

Il modo più comune per eseguire una procedura memorizzata è utilizzare i comandi SQL. In MySQL, utilizziamo il comando CALL per eseguire la procedura memorizzata.

CALL ProcedureName();

Utilizzando la procedura definita GetEmployeeDetails, la query di esecuzione sarebbe simile a questa:

-- Esegui la procedura memorizzata per recuperare i dettagli per EmployeeID 101
CALL GetEmployeeDetails(101);

Procedure memorizzate in SQL Server

SQL Server fornisce una sintassi e comandi specifici per creare, eseguire e gestire le procedure memorizzate. Questo approccio facilita la costruzione di routine SQL efficienti e riutilizzabili che possono gestire compiti complessi con una minima ripetizione.

Creazione di procedure memorizzate in SQL Server

Creare una procedura memorizzata in SQL Server comporta la definizione del nome della procedura, dei parametri e delle istruzioni SQL che compongono il suo corpo. Il seguente esempio crea una procedura chiamata GetEmployeeDetails che prende @EmployeeID come parametro di input e recupera i dettagli per quell’impiegato specifico.

-- Crea una procedura per recuperare i dettagli di un impiegato specifico tramite EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Parametro di input: ID dell'impiegato da recuperare
AS
BEGIN
    -- Seleziona tutte le colonne da Employees dove EmployeeID corrisponde al parametro di input
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Eseguire procedure memorizzate in SQL Server

In SQL Server, il comando EXEC o EXECUTE chiama una procedura memorizzata. Il seguente esempio mostra come eseguire la procedura memorizzata GetEmployeeDetails utilizzando parametri di input specifici. 

-- Esegui la procedura GetEmployeeDetails con EmployeeID impostato a 102
EXEC GetEmployeeDetails @EmployeeID = 102;

Puoi anche eseguire i parametri di output dichiarando la variabile nel comando. Nell’esempio seguente, @TotalSales è dichiarata come variabile per ricevere l’output da CalculateTotalSales.

-- Dichiarare una variabile per memorizzare l'importo totale delle vendite
DECLARE @TotalSales DECIMAL(10, 2);
-- Eseguire CalculateTotalSales per SalespersonID 5, memorizzare il risultato in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Visualizzare l'importo totale delle vendite
SELECT @TotalSales AS TotalSales;

Consiglio di seguire il nostro corso Introduzione a SQL Server per comprendere le diverse funzionalità di SQL Server per interrogare i dati. Inoltre, considera il nostro percorso professionale completo SQL Server Developer, che non solo ti doterà delle competenze per creare, aggiornare ed eseguire stored procedure, ma ti aiuterà anche con le funzioni di aggregazione, join, inserimento ed eliminazione di tabelle, e molto altro.

Usi Comuni delle Stored Procedure

Le stored procedure SQL sono utili nei casi in cui sono necessarie attività complesse ripetitive. Di seguito sono riportate applicazioni reali delle stored procedure nella gestione dei dati e nelle operazioni aziendali.

Validazione dei dati e applicazione dell’integrità

Le stored procedure possono essere utilizzate per convalidare i dati prima dell’aggiornamento o dell’inserimento. Nell’esempio seguente, una stored procedure controlla che l’email di un cliente sia univoca prima di inserire un nuovo record nella tabella Clienti, garantendo la coerenza dei dati. Questo centralizza la logica di convalida all’interno del database, riducendo la ridondanza e garantendo un’applicazione uniforme attraverso diverse applicazioni.

-- Creare una procedura per aggiungere un nuovo cliente, controllando la presenza di email duplicate
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Controllare se l'email esiste già nella tabella Clienti
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Generare un errore se l'email è già in uso
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Inserire i dettagli del nuovo cliente se l'email è univoca
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Elaborazione automatica dei dati e reporting

Puoi anche utilizzare procedure memorizzate per generare report regolari o elaborare grandi set di dati. Ad esempio, una procedura memorizzata potrebbe aggregare i dati di vendita giornalieri da una piattaforma di e-commerce e memorizzarli in una tabella di reporting, rendendo più facile per i team accedere alle informazioni sulle vendite senza eseguire query complesse.

-- Crea una procedura per generare un report di vendita giornaliero
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Inserisci la data odierna e il totale delle vendite nella tabella SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Seleziona la data corrente e la somma delle vendite per oggi dalla tabella Vendite
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Gestione delle transazioni

Utilizzando procedure memorizzate, puoi garantire che più operazioni vengano eseguite come un’unica transazione. Ad esempio, in un sistema bancario, una procedura memorizzata può gestire sia azioni di addebito che di accredito in un trasferimento di fondi, assicurando che entrambe le azioni abbiano successo o falliscano insieme.

-- Crea una procedura per trasferire fondi tra conti
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Inizia una transazione per garantire l'atomicità
    -- Deduci l'importo specificato dal saldo del conto del mittente
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Aggiungi l'importo specificato al saldo del conto del destinatario
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Controlla eventuali errori e annulla se si sono verificati; altrimenti, conferma la transazione
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Annulla tutte le modifiche se si è verificato un errore
    ELSE
        COMMIT TRANSACTION;    -- Conferma le modifiche se non ci sono errori
END;

Controllo degli accessi e sicurezza dei dati

Puoi anche utilizzare le stored procedure SQL per controllare l’accesso ai dati sensibili. Ad esempio, una stored procedure può limitare l’accesso diretto alle tabelle consentendo agli utenti di chiamare una procedura che recupera solo i campi rilevanti, come i saldi dei conti, senza dettagli sulle transazioni.

-- Crea una procedura per recuperare il saldo del conto, con controllo di autorizzazione
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Controlla se il conto esiste ed è di proprietà dell'utente specificato
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Se autorizzato, seleziona e restituisci il saldo del conto
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Se non autorizzato, genera un errore
        THROW 50000, 'Unauthorized access.', 1;
END;

Migrazione dei dati e processi ETL

Le procedure memorizzate vengono utilizzate anche per caricare, trasformare e migrare i dati tra i sistemi. Una procedura memorizzata può automatizzare l’estrazione dei dati da un database sorgente, trasformarli secondo necessità e inserirli in una tabella di destinazione, semplificando l’integrazione dei dati per reportistica o analisi.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Estrai
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Trasforma
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Carica
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Best Practices per le Procedure Memorizzate

Scrivere procedure memorizzate efficienti e manutenibili assicura che il tuo database funzioni in modo ottimale. Di seguito sono riportati alcuni suggerimenti per scrivere procedure memorizzate per i tuoi database SQL.

  • Utilizzare convenzioni di denominazione coerenti: Per rendere le stored procedure facili da identificare e capire, utilizzare un formato di denominazione coerente e descrittivo. Inoltre, evitare il prefisso sp_ in SQL Server riservato alle procedure di sistema per evitare potenziali conflitti e problemi di prestazioni.

  • Implementazione della gestione degli errori: Avvolgere le istruzioni SQL in blocchi TRY...CATCH per intercettare e gestire gli errori e mantenere l’integrità dei dati.

  • OTTIMIZZAZIONE DELLE PRESTAZIONI: Ridurre al minimo l’uso dei cursori in quanto possono essere lenti e intensivi in termini di risorse. Invece, provare a utilizzare operazioni basate su set, che sono generalmente più efficienti. Inoltre, indicizzare le colonne utilizzate frequentemente e evitare join complessi in tabelle di grandi dimensioni per ridurre il sovraccarico di memoria e migliorare l’efficienza.

  • Parametrizza le Procedure Memorizzate: Utilizza parametri invece di valori codificati per consentire di passare valori dinamici nella tua procedura, rendendola più flessibile e riutilizzabile.

Scopri il nostro corso SQL Intermedio per saperne di più sull’uso delle funzioni aggregate e delle join per filtrare i dati. Inoltre, prova i nostri percorsi di abilità Fondamenti di SQL Server e Fondamenti di SQL per migliorare le tue competenze nella join di tabelle e nell’analisi dei dati.

Conclusione

Le procedure memorizzate in SQL migliorano la riutilizzabilità del codice e l’ottimizzazione delle prestazioni nella gestione dei database. Le procedure memorizzate migliorano anche la sicurezza del database attraverso l’accesso controllato e garantendo l’integrità dei dati. Come professionista dei dati, ti incoraggio a praticare la creazione e l’esecuzione di procedure memorizzate per padroneggiare le migliori pratiche di gestione dei database.

Se sei interessato a diventare un analista di dati esperto, dai un’occhiata al nostro Percorso di carriera per Associate Data Analyst in SQL per apprendere le competenze necessarie. Il corso Reporting in SQL è anche adatto se vuoi imparare a costruire dashboard professionali utilizzando SQL. Infine, ti consiglio di ottenere la Certificazione SQL Associate per dimostrare la tua padronanza nell’utilizzo di SQL per l’analisi dei dati e distinguerti tra gli altri professionisti dei dati.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure