Stored procedure definite dall'utente in SQL

Userai Microsoft SQL Server come database. Il concetto trattato in questo tutorial è lo stesso per la maggior parte dei sistemi di gestione di database relazionali (RDBMS), ma le sintassi variano; tuttavia, l’idea illustrata qui si applica bene a tutti i database.

Una stored procedure è un blocco di istruzioni SQL in un RDBMS; in genere viene scritta da programmatori, amministratori di database o data analyst, e viene salvata e riutilizzata in più programmi. Può assumere forme diverse a seconda dell’RDBMS. Tuttavia, le due stored procedure più importanti presenti in qualsiasi RDBMS sono le seguenti.

• Stored procedure definita dall’utente
• Stored procedure di sistema

Una stored procedure è codice precompilato che viene memorizzato o messo in cache e poi riutilizzato. Avere codice in cache da riutilizzare rende la manutenzione molto più semplice: non serve modificarlo più volte e si può preservare la sicurezza.

Creazione di una tabella

Ora creerai una tabella chiamata table_Employees come segue.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)
Offerto da

La tabella contiene informazioni sui dipendenti di una determinata azienda e include le seguenti colonne:

1. EmployeeId: è la chiave primaria, univoca, di tipo intero; il valore viene auto-incrementato.
2. EmployeeFirstName: è il nome del dipendente, di tipo carattere, e non deve essere vuoto.
3. EmployeeLast Name: è il cognome del dipendente, di tipo carattere, e non deve essere vuoto.
4. EmployeeGender: indica il genere del dipendente ed è anch’esso di tipo carattere.
5. EmployeeDepartmentId: è l’id del dipartimento del dipendente, di tipo intero.

Puoi scrivere una query per inserire valori nella tabella. Le query per aggiungere valori alla tabella sono:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)
Offerto da

Dopo aver inserito i valori nella tabella, viene creata table_Employees.

Creare una stored procedure definita dall’utente

Le stored procedure ti permettono di scrivere una query una volta sola, assegnarle un nome, salvarla ed eseguirla più volte, se necessario. Eseguendo le query, vedrai creare una cartella in Programmability->Stored Procedure, con il file denominato dbo.uprocGetEmployees.

La sintassi per creare una stored procedure è:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END
Offerto da

Puoi creare una stored procedure usando l’istruzione SQL come segue.

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Inoltre, è consigliabile creare una stored procedure con una convenzione di denominazione diversa da "sp_.". Nell’esempio, la stored procedure è creata con il nome: uprocGetEmployees.

Eseguire la stored procedure

Per eseguire una stored procedure, puoi usare uno di tre approcci ed eseguirla come segue.

• EXEC <>
• EXECUTE <>
• <>

Nell’esempio sopra, puoi usare il nome della procedura uprocGetEmployees e selezionarlo per eseguire la query.

Stored procedure con parametri

Una stored procedure può accettare uno o più parametri. Capire i parametri singoli è semplice se prima comprendi quelli multipli.

La sintassi per creare una stored procedure con più parametri è:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END
Offerto da

L’esempio mostra che c’è una modifica nella cartella seguente.
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Il concetto è molto simile a quello delle funzioni nei linguaggi di programmazione come Python, Java, ecc. Il programma sopra ha una funzione o procedura chiamata "uprocGetEmployeesGenderAndDepartment", con i parametri @EmployeeGender e @EmployeeDepartmentId, che vengono "chiamati" passando i valori richiesti alla procedura; questo può essere fatto in due modi, spiegati qui sotto.

I parametri in questo programma sono @EmployeeGender e @EmployeeDepartmentId e vengono "chiamati" passando un valore al parametro.

Il valore può essere specificato in due modi. Puoi usare uno qualunque dei due per eseguire la stored procedure con più parametri.

• Specificando i nomi dei parametri nella query e passando i valori richiesti:

• In base alla posizione che fa corrispondere i parametri della stored procedure con la tua query e passando i valori richiesti al parametro:

Modificare la stored procedure

Puoi anche modificare la stored procedure usando il comando ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END
Offerto da

Eliminare una stored procedure

Le stored procedure possono essere eliminate rapidamente usando i seguenti comandi:

DROP PROC <> DROP PROCEDURE <>

Aggiungere sicurezza tramite crittografia

Il simbolo del lucchetto indicato nell’immagine a sinistra mostra che la stored procedure è crittografata, il che garantisce l’accesso solo alle persone autorizzate.

Conclusione

Hai appena imparato le basi delle stored procedure definite dall’utente e gli argomenti trattati sono concettualmente simili in qualsiasi RDBMS. Puoi approfondire su DataCamp seguendo il corso Intermediate SQL Server. Inoltre, questo tutorial sulle Stored Procedures sarà molto utile per il tuo apprendimento.