MySQL - Funzioni memorizzate

Ciao, appassionati di database in erba! Oggi esploreremo il mondo affascinante delle funzioni memorizzate di MySQL. Come il tuo amico insegnante di informatica di quartiere, sono entusiasta di guidarti in questo viaggio, anche se non hai mai scritto una riga di codice prima. Allora, prenditi una tazza della tua bevanda preferita e mettiamoci all'opera!

Cos'è una funzione memorizzata di MySQL?

Immagina di avere un libro di ricette magico che può preparare istantaneamente il tuo piatto preferito ogni volta che ne hai bisogno. Questo è più o meno cosa sono le funzioni memorizzate per i database! Sono come ricette pre-scritte (o funzioni) che puoi richiamare ogni volta che hai bisogno di eseguire un'attività specifica nel tuo database.

Perché usare funzioni memorizzate?

1. Riutilizzabilità: Scrivi una volta, usa molte volte!
2. Coerenza: Assicurati che la stessa logica venga applicata ovunque.
3. Sicurezza: Controlla chi può accedere e modificare le tue funzioni.
4. Performance: Possono essere più rapide rispetto a scrivere lo stesso codice ripetutamente.

Ora, mettiamo le mani al lavoro e creiamo la nostra prima funzione memorizzata!

Creare la tua prima funzione memorizzata

Ecco un esempio semplice per iniziare:

DELIMITER //

CREATE FUNCTION HelloWorld()
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN 'Ciao, Mondo!';
END //

DELIMITER ;

Spieghiamo questo:

1. DELIMITER //: Questo dice a MySQL di usare // come delimitatore di istruzioni invece del punto e virgola (;) predefinito. Lo facciamo perché la nostra funzione contiene punto e virgola, e non vogliamo che MySQL si confonda.

2. CREATE FUNCTION HelloWorld(): Questa riga dichiara che stiamo creando una funzione chiamata "HelloWorld".

3. RETURNS VARCHAR(50): Questo specifica che la nostra funzione restituirà una stringa (VARCHAR) di massimo 50 caratteri.

4. DETERMINISTIC: Questa parola chiave dice a MySQL che per lo stesso input, la funzione sempre restituirà lo stesso output.

5. BEGIN e END: Queste parole chiave racchiudono il corpo della nostra funzione.

6. RETURN 'Ciao, Mondo!';: Questo è il codice effettivo della nostra funzione. Semplicemente restituisce la stringa "Ciao, Mondo!".

7. DELIMITER ;: Questo ripristina il delimitatore predefinito a punto e virgola.

Ora, chiamiamo la nostra funzione:

SELECT HelloWorld();

E voilà! Dovresti vedere "Ciao, Mondo!" come risultato. Congratulazioni, hai appena creato e chiamato la tua prima funzione memorizzata!

Parametri e variabili nelle funzioni memorizzate

Rendiamo le cose un po' più interessanti aggiungendo parametri e variabili alle nostre funzioni.

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE greeting VARCHAR(100);
SET greeting = CONCAT('Ciao, ', name, '! Benvenuto nelle funzioni MySQL!');
RETURN greeting;
END //

DELIMITER ;

In questo esempio:

1. Abbiamo aggiunto un parametro name VARCHAR(50) alla nostra funzione.
2. Abbiamo dichiarato una variabile greeting utilizzando la parola chiave DECLARE.
3. Abbiamo usato la parola chiave SET per assegnare un valore alla nostra variabile.
4. Abbiamo utilizzato la funzione CONCAT per combinare stringhe.

Chiamiamo la nostra nuova funzione:

SELECT Greet('Alice');

Questo dovrebbe restituire: "Ciao, Alice! Benvenuto nelle funzioni MySQL!"

Utilizzare le funzioni memorizzate con i dati del database

Ora, creiamo una funzione che interagisce effettivamente con il nostro database. Immagina di avere una tabella chiamata employees con le colonne id, first_name, last_name, e salary.

DELIMITER //

CREATE FUNCTION CalculateBonus(employee_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus DECIMAL(10,2);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

SET bonus = employee_salary * 0.1;

RETURN bonus;
END //

DELIMITER ;

Questa funzione:

1. Prende un employee_id come input.
2. Dichiarare variabili per employee_salary e bonus.
3. Recupera lo stipendio dell'impiegato dalla tabella employees.
4. Calcola un bonus del 10% basato sullo stipendio.
5. Restituisce il bonus calcolato.

Per utilizzare questa funzione:

SELECT first_name, last_name, CalculateBonus(id) AS bonus
FROM employees
WHERE id = 1;

Questo restituirà il nome, il cognome e il bonus calcolato per l'impiegato con id 1.

Flusso di controllo nelle funzioni memorizzate

Rafforziamo il nostro calcolo del bonus con un po' di flusso di controllo:

DELIMITER //

CREATE FUNCTION CalculateBonusTier(employee_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus_tier VARCHAR(20);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

IF employee_salary < 30000 THEN
SET bonus_tier = 'Bronze';
ELSEIF employee_salary < 50000 THEN
SET bonus_tier = 'Silver';
ELSE
SET bonus_tier = 'Gold';
END IF;

RETURN bonus_tier;
END //

DELIMITER ;

Questa funzione utilizza IF, ELSEIF, e ELSE per determinare un livello di bonus in base allo stipendio dell'impiegato.

Chiamare una funzione memorizzata da una procedura memorizzata

Infine, vediamo come possiamo utilizzare le nostre funzioni memorizzate all'interno di una procedura memorizzata:

DELIMITER //

CREATE PROCEDURE EmployeeBonusReport(IN emp_id INT)
BEGIN
DECLARE emp_name VARCHAR(100);
DECLARE emp_bonus DECIMAL(10,2);
DECLARE emp_bonus_tier VARCHAR(20);

SELECT CONCAT(first_name, ' ', last_name) INTO emp_name
FROM employees
WHERE id = emp_id;

SET emp_bonus = CalculateBonus(emp_id);
SET emp_bonus_tier = CalculateBonusTier(emp_id);

SELECT emp_name AS 'Nome Dipendente',
emp_bonus AS 'Importo Bonus',
emp_bonus_tier AS 'Livello Bonus';
END //

DELIMITER ;

Questa procedura:

1. Prende un ID dipendente come input.
2. Recupera il nome completo del dipendente.
3. Chiama la nostra funzione CalculateBonus per ottenere l'importo del bonus.
4. Chiama la nostra funzione CalculateBonusTier per ottenere il livello del bonus.
5. Restituisce un rapporto con il nome del dipendente, l'importo del bonus e il livello del bonus.

Per chiamare questa procedura:

CALL EmployeeBonusReport(1);

Ecco! Abbiamo coperto le basi delle funzioni memorizzate di MySQL, dalla creazione all'uso all'interno delle procedure memorizzate. Ricorda, la pratica fa la perfezione, quindi non aver paura di sperimentare con le tue funzioni. Buon coding!

Credits: Image by storyset