Come creare e utilizzare correttamente procedure (Procedure/Function) in SQL per implementare logiche aziendali riutilizzabili, e quali sono le complessità nella gestione dei parametri e dei valori di ritorno?

Risposta.

Le sottoprogrammi in SQL sono di due tipi:

• Procedure (Stored Procedures): eseguono azioni (inserimento, aggiornamento, eliminazione), possono restituire parametri OUT o set di righe (tramite SELECT). Vengono chiamate usando CALL/EXEC e non sempre possono essere utilizzate nella parte select.
• Funzioni (User Defined Functions, UDF): restituiscono un valore (scalare o tabellare), possono essere utilizzate direttamente in SELECT, WHERE, ORDER BY e altre strutture.

Importante:

• Le procedure NON possono essere utilizzate in un normale SELECT, solo tramite chiamata con EXEC/CALL.
• Le funzioni possono restituire solo un valore (scalari) o una tabella (funzioni tabellari), e possono essere utilizzate in qualsiasi espressione.
• Documentare sempre i parametri: IN (per ingresso), OUT (per restituire il risultato), INOUT (bidirezionali).

Esempio di funzione scalare (PostgreSQL):

CREATE FUNCTION get_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.13) RETURNS NUMERIC AS $$
BEGIN
  RETURN amount * rate;
END;
$$ LANGUAGE plpgsql;

-- utilizzo:
SELECT *, get_tax(price) AS tax FROM product;

Esempio di procedura memorizzata (SQL Server):

CREATE PROCEDURE add_employee(@name NVARCHAR(100), @salary INT, @emp_id INT OUTPUT)
AS
BEGIN
  INSERT INTO employees (name, salary) VALUES (@name, @salary);
  SET @emp_id = SCOPE_IDENTITY();
END;

DECLARE @id INT;
EXEC add_employee 'John', 100000, @id OUTPUT;

Domanda ingannevole.

È possibile utilizzare direttamente una procedura memorizzata in SELECT?

Spesso rispondono "sì", ma non è corretto.

Risposta:

• No: le procedure standard vengono chiamate solo separatamente (EXEC/CALL), mentre le funzioni possono essere utilizzate in SELECT.

---

Esempi di errori reali a causa della mancanza di conoscenza delle complessità dell'argomento.

---

Storia

Progetto: Sistema di contabilizzazione primario, implementazione di report. Errore: Invece di una funzione, è stata scritta una procedura per calcolare la somma — SELECT non funzionava, è stata necessaria una riscrittura dell'intera logica dei report degli utenti.

---

Storia

Progetto: Sistema ERP con parametri esterni. Errore: Non è stata assegnata la chiave OUT per la procedura, con il risultato che il cliente non poteva conoscere l'ID della registrazione aggiunta, l'integrazione è "fallita".

---

Storia

Progetto: Servizio finanziario con calcoli fiscali secondo diverse regole. Errore: Utilizzate una funzione scalare in una query massiva senza test sulla prestazione — la query ha rallentato l'elaborazione della tabella a causa della chiamata riga per riga (piano non ottimizzato).