Stored procedure SQL: automatizza e ottimizza le query

Le stored procedure SQL sono insiemi di istruzioni SQL salvati e memorizzati in un database. Possono essere eseguite su richiesta per svolgere attività di manipolazione e validazione dei dati, riducendo la necessità di scrivere codice SQL ripetitivo per operazioni comuni. Le stored procedure sono utili nella gestione dei database perché promuovono efficienza e riusabilità. Inoltre, supportano una maggiore sicurezza e manutenibilità del database. In questo articolo vedremo come creare ed eseguire stored procedure SQL, i casi d’uso più comuni e le best practice.

Per iniziare, ti consiglio vivamente i corsi di DataCamp Introduction to SQL e Learn SQL per apprendere le basi dell’estrazione e dell’analisi dei dati con SQL. Inoltre, il SQL Basics Cheat Sheet, che puoi scaricare, è un utile riferimento perché raccoglie tutte le funzioni SQL più comuni.

Cos’è una stored procedure in SQL?

Una stored procedure in SQL è un insieme di istruzioni SQL salvato e memorizzato all’interno del database. Lo scopo della stored procedure è 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 stored procedure incapsulano un set di istruzioni SQL, rendendo semplice riutilizzare il codice senza dover riscrivere continuamente i comandi SQL.

I vantaggi delle stored procedure SQL includono:

• Riusabilità del codice: una volta creata, una stored procedure può essere richiamata tutte le volte necessarie, eliminando la ridondanza nel codice SQL.
• Prestazioni migliorate: le stored procedure spesso si eseguono più velocemente perché sono precompilate e memorizzate sul server del database, riducendo la latenza di rete e i tempi di compilazione.
• Sicurezza: le stored procedure possono migliorare la sicurezza dei dati e il controllo sull’accesso alle informazioni sensibili concedendo agli utenti il permesso di eseguire una procedura senza accesso diretto alle tabelle.

Sintassi e struttura di base

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

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;Offerto da

Nella sintassi sopra:

• CREATE PROCEDURE: comando usato per definire una nuova stored procedure.

• ProcedureName: il nome assegnato alla stored procedure. Deve essere univoco nel database.

• @Parameter1, @Parameter2: i parametri sono opzionali; consentono alla procedura di ricevere input. Ogni parametro è definito con il simbolo @ e un tipo di dato (ad es., INT, VARCHAR(50)).

• AS BEGIN...END: le istruzioni SQL tra BEGIN e END costituiscono il corpo della procedura, dove viene eseguita la logica principale. In questo esempio, la procedura recupera record da una tabella in base a condizioni specifiche.

Parametri di input e output

I parametri di input e output ti permettono di passare valori a una stored procedure e di riceverne indietro.

Per esempio, se @Parameter1 è definito come parametro di input, può essere assegnato qualsiasi valore al momento della chiamata della procedura, influenzando la logica SQL o l’output. Nell’esempio seguente, il parametro @UserID recupera dati specifici per lo UserID fornito.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;Offerto da

I parametri di output, definiti tramite la parola chiave OUTPUT, consentono a una stored procedure di inviare un valore all’ambiente chiamante. Ad esempio, se una procedura calcola uno sconto, può usare un parametro di output per restituirlo al programma chiamante.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;Offerto da

Per chiamare questa procedura, useresti:

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;Offerto da

Stored procedure in MySQL

Come accennato, l’esecuzione di una stored procedure in SQL può avvenire in modi diversi a seconda del sistema di database e degli strumenti utilizzati.

Creare stored procedure in MySQL

Creare una stored procedure in MySQL significa definirne il nome, i parametri e le istruzioni SQL che ne compongono il corpo. Nell’esempio seguente viene creata una procedura chiamata GetEmployeeDetails che accetta EmployeeID come parametro di input e recupera i dettagli per quello specifico dipendente.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;Offerto da

Eseguire stored procedure in MySQL

Il modo più comune per eseguire una stored procedure è usare i comandi SQL. In MySQL, usiamo il comando CALL per eseguire la stored procedure.

CALL ProcedureName();Offerto da

Usando la procedura GetEmployeeDetails definita, la query di esecuzione sarà simile a questa:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);Offerto da

Stored procedure in SQL Server

SQL Server fornisce una sintassi e comandi specifici per creare, eseguire e gestire le stored procedure. Questo approccio semplifica la costruzione di routine SQL efficienti e riutilizzabili in grado di gestire attività complesse con il minimo di ripetizione.

Creare stored procedure in SQL Server

Creare una stored procedure in SQL Server significa definirne il nome, i parametri e le istruzioni SQL che ne compongono il corpo. Nell’esempio seguente viene creata una procedura chiamata GetEmployeeDetails che accetta @EmployeeID come parametro di input e recupera i dettagli per quello specifico dipendente.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;Offerto da

Eseguire stored procedure in SQL Server

In SQL Server, il comando EXEC o EXECUTE richiama una stored procedure. L’esempio seguente mostra come eseguire la stored procedure GetEmployeeDetails usando parametri di input specifici. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;Offerto da

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

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;Offerto da

Ti consiglio il nostro corso Introduction to SQL Server per capire le diverse funzionalità di SQL Server per interrogare i dati. Valuta anche l’intero career track SQL Server Developer, che non solo ti fornirà le competenze per creare, aggiornare ed eseguire stored procedure, ma ti aiuterà anche con funzioni di aggregazione, join, inserimenti ed eliminazioni di tabelle e molto altro.

Utilizzi comuni delle stored procedure

Le stored procedure SQL sono utili in scenari che richiedono attività complesse e ripetitive. Ecco applicazioni reali delle stored procedure nella gestione dei dati e nelle operazioni aziendali. 

Validazione dei dati e rispetto dell’integrità

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

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;Offerto da

Elaborazione dati e reportistica automatizzate

Puoi anche usare le stored procedure per generare report periodici o elaborare grandi dataset. Ad esempio, una stored procedure potrebbe aggregare i dati di vendita giornalieri da una piattaforma e-commerce e salvarli in una tabella di reporting, facilitando l’accesso alle metriche di vendita senza eseguire query complesse.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;Offerto da

Gestione delle transazioni

Usando le stored procedure, puoi garantire che più operazioni vengano eseguite come un’unica transazione. Ad esempio, in un sistema bancario, una stored procedure può gestire sia l’addebito sia l’accredito in un trasferimento di fondi, assicurando che entrambe le azioni riescano o falliscano insieme.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;Offerto da

Controllo degli accessi e sicurezza dei dati

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

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;Offerto da

Migrazione dei dati e processi ETL

Le stored procedure vengono utilizzate anche per caricare, trasformare e migrare dati tra sistemi. Una stored procedure 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 report o analisi.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;Offerto da

Best practice per le stored procedure

Scrivere stored procedure efficienti e manutenibili garantisce prestazioni ottimali del database. Ecco alcuni consigli per scrivere stored procedure per i tuoi database SQL.

• Usa convenzioni di denominazione coerenti: per rendere le stored procedure facili da identificare e comprendere, usa uno schema di nomi coerente e descrittivo. Inoltre, evita il prefisso sp_ in SQL Server, riservato alle procedure di sistema, per prevenire potenziali conflitti e problemi di prestazioni.

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

• Ottimizza le prestazioni: riduci al minimo l’uso dei cursori, che possono essere lenti e dispendiosi in termini di risorse. Preferisci le operazioni set-based, generalmente più efficienti. Inoltre, indicizza le colonne usate più spesso ed evita join complessi su tabelle grandi per ridurre l’overhead di memoria e migliorare l’efficienza.

• Parametrizza le stored procedure: usa parametri invece di valori hardcoded per passare valori dinamici alla procedura, rendendola più flessibile e riutilizzabile.

Dai un’occhiata al nostro corso Intermediate SQL per saperne di più sull’uso di funzioni di aggregazione e join per filtrare i dati. Prova anche gli skill track SQL Server Fundamentals e SQL Fundamentals per migliorare le tue competenze nei join tra tabelle e nell’analisi dei dati.

Conclusione

Le stored procedure SQL migliorano la riusabilità del codice e l’ottimizzazione delle prestazioni nella gestione dei database. Le stored procedure aumentano anche la sicurezza del database grazie al controllo degli accessi e al mantenimento dell’integrità dei dati. Come professionista dei dati, ti invito a esercitarti nella creazione e nell’esecuzione di stored procedure per padroneggiare le migliori pratiche di gestione dei database.

Se vuoi diventare un data analyst competente, scopri il career track Associate Data Analyst in SQL per acquisire le competenze necessarie. Il corso Reporting in SQL è adatto anche se vuoi imparare a creare dashboard professionali con SQL. Infine, ti consiglio di ottenere la SQL Associate Certification per dimostrare la tua padronanza di SQL per l’analisi dei dati e distinguerti tra gli altri professionisti del settore.