SQL Server lezione #15 Come codificare stored procedures, funzioni, trigger

Programmazione procedurale in Transact-SQL

La tabella qui sotto presenta i quattro tipi di programmi procedurali che è possibile codificare utilizzando Transact-SQL. Ogni tipo di programma contiene istruzioni SQL. Tuttavia, differiscono per il modo in cui vengono archiviati ed eseguiti.

Stored procedures, funzioni definite dall'utente, e trigger

Le store procedures, le funzioni definite dall'utente e i trigger sono oggetti di database eseguibili. Questo significa che ciascuno è archiviato nel database. Per creare questi oggetti, usi le istruzioni DDL che verranno illustrate in questo capitolo. Quindi, questi oggetti rimangono come parte del database finché non vengono eliminati esplicitamente. Le procedure memorizzate, le funzioni definite dall'utente e i trigger differiscono in base al modo in cui sono eseguite. Le procedure memorizzate e le funzioni definite dall'utente possono essere eseguite da qualsiasi connessione al database che può eseguire un'istruzione SQL. Al contrario, i trigger vengono eseguiti automaticamente in risposta all'esecuzione di una query di azione su una tabella specifica. Le procedure memorizzate vengono spesso scritte dai programmatori SQL per essere utilizzate da utenti finali o programmatori di applicazioni. Se si codificano le procedure memorizzate in questo modo, puoi semplificare il modo in cui questi utenti interagiscono con un database. In più può fornire l'accesso a un database esclusivamente tramite procedure memorizzate. Questo ti dà uno stretto controllo sulla sicurezza dei dati. Sia le funzioni definite dall'utente che i trigger vengono utilizzati più spesso da programmatori SQL piuttosto che dai programmatori di applicazioni o dagli utenti finali. I programmatori SQL spesso utilizzano le proprie funzioni all'interno di script, procedure memorizzate e trigger. Poiché i trigger vengono eseguiti in risposta a una query di azione, i programmatori li utilizzano per aiutare a prevenire errori causati da dati incoerenti o non validi. Anche le procedure memorizzate, le funzioni e i trigger differiscono a seconda che siano o meno utilizzati parametri. I parametri sono valori che possono essere passati o restituiti da una procedura. Possono essere utilizzati parametri sia con le procedure memorizzate che con le funzioni definite dall'utente, ma non con i trigger.

Un confronto tra i diversi tipi di programmi SQL procedurali

Programmi SQL procedurali

Tipo	Batches	Come viene archiviato	Come viene eseguito	Accetta parametri
Script	Molteplici	In un file su un disco	Dall'interno di uno strumento client come Management Studio o SQLCMD	No
Stored procedure	Solo uno	In un oggetto nel database	Da un'applicazione o dall'interno di uno script SQL	Sì
Funzione definita dall'utente	Solo uno	In un oggetto nel database	Da un'applicazione o dall'interno di uno script SQL	Sì
Trigger	Solo uno	In un oggetto nel database	Automaticamente dal server di database quando una specifica query di azione viene eseguita	No

Descrizione

• È possibile scrivere programmi procedurali con Transact-SQL utilizzando script, stored procedures, funzioni definite dall'utente e trigger.
• Gli script sono utili per gli utenti con accesso agli strumenti client di SQL Server, ad esempio lo Management Studio. In genere, questi strumenti vengono utilizzati dai programmatori SQL e DBA, non da programmatori di applicazioni o utenti finali.
• Le procedure memorizzate, le funzioni definite dall'utente e i trigger sono tutti oggetti di database che contengono comandi SQL. Sebbene differiscano nel modo in cui vengono eseguiti e in base al tipo di valori che possono restituire, tutti forniscono un maggiore controllo e prestazioni migliori di uno script.
• Le procedure memorizzate danno al programmatore SQL il controllo su chi accede al database e come. Poiché alcuni programmatori di applicazioni non hanno le competenze per scrivere determinati tipi di query SQL complesse, le procedure memorizzate possono semplificarle l'utilizzo del database.
• Le funzioni definite dall'utente vengono spesso utilizzate dai programmatori SQL all'interno delle stored procedures e trigger che scrivono, sebbene possano essere utilizzati anche da programmatori di applicazioni e utenti finali.
• I trigger sono procedure speciali che vengono eseguite quando viene eseguita una query di azione, ad esempio quando viene eseguito il comando INSERT, UPDATE o DELETE. Come i vincoli, puoi utilizza i trigger per prevenire errori del database, ma i trigger ti danno un maggiore controllo e flessibilità.
• Poiché procedure, funzioni e trigger sono oggetti di database, le istruzioni SQL utilizzate per crearli, eliminarli modificarli sono considerati parte del DDL.

Come codificare le stored procedures

Una stored procedure è un oggetto di database che contiene una o più istruzioni SQL. Negli argomenti che seguono imparerai come creare e utilizzare le stored procedures. Inoltre, imparerai come utilizzare alcune delle procedure memorizzate fornite da SQL Server.

Un'introduzione alle stored procedures

L'esempio qui sotto presenta uno script che crea una stored procedure, chiamata anche an sproc o semplicemente una procedura. Per fare ciò, usi l'istruzione CREATE PROC. Imparerai i dettagli su come codificare questa affermazione tra un attimo. La prima volta che viene eseguita una procedura, ogni istruzione SQL in essa contenuta è compilata ed eseguito per creare un piano di esecuzione. Quindi, la procedura viene memorizzata in forma compilata all'interno del database. Per ogni esecuzione successiva, le istruzioni SQL vengono eseguite senza compilazione, perché sono precompilate. Questo rende l'esecuzione di una procedura memorizzata più veloce dell'esecuzione di uno script SQL equivalente. Per eseguire o chiamare una procedura memorizzata si utilizza l'istruzione EXEC. Se l'istruzione EXEC è la prima riga di un batch, puoi omettere la parola chiave EXEC e basta codificare il nome della procedura. Poiché ciò può portare a un codice che crea confusione, tuttavia, ti consiglio di includere la parola chiave EXEC. Lo script in questa figura crea una procedura memorizzata denominata spInvoiceReport. Questa procedura è costituita da una singola istruzione: un'istruzione SELECT che recupera dati dalle tabelle Fornitori e Fatture. Come vedrai negli argomenti che seguono, tuttavia, una procedura memorizzata può contenere più di un'istruzione, insieme a codice procedurale utilizzato negli script. Quando esegui lo script in questo esempio, crei la procedura memorizzata. La risposta dal sistema mostra che la procedura è stata creata con successo. Quindi, quando si esegue la procedura memorizzata, il set di risultati recuperato dall'istruzione SELECT viene restituito. Come puoi vedere, un utente o un programma che chiama questa procedura non deve conoscere la struttura del database per utilizzare la procedura memorizzata. Questo semplifica l'uso del database eliminando la necessità di conoscere SQL e la necessità di comprendere la struttura del database. Come imparerai nella lezione 17, puoi consentire a un utente o un programma di chiamare le stored procedures specifiche ma non ad eseguire altre istruzioni SQL. Facendo in questo modo puoi proteggere il tuo database limitando l'accesso solo a quelle righe, colonne e tabelle a cui si fornisce l'accesso tramite le procedure memorizzate. Per quei sistemi in cui la sicurezza è fondamentale, questo può essere il modo migliore per proteggere i dati.

Uno script che crea una stored procedure

    USE AP;
    GO
    CREATE PROC spInvoiceReport
    AS
        SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
        FROM Invoices JOIN Vendors
            ON Invoices.vendorID = vendors.vendorID
        WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
        ORDER BY VendorName;
                

La risposta del sistema

    I comandi sono stati completati.
                

Il comando che chiama la store procedure

    USE AP
    GO
    EXEC spInvoiceReport;
                

Il set di risultati creato dalla procedura

Descrizione

• Una procedura memorizzata è un oggetto di database eseguibile che contiene comandi SQL. Una procedura memorizzata è anche chiamata sproc (pronunciata come una parola o come "ess-proc") o una procedura.
• Le procedure memorizzate sono precompilate. Ciò significa che il piano di esecuzione per il codice SQL viene compilato la prima volta che viene eseguita la procedura e viene quindi salvato nella sua forma compilata. Per questo motivo, le procedure memorizzate vengono eseguite più velocemente di un equivalente script SQL.
• Si utilizza il comando EXEC per eseguire o chiamare una procedura. Se questo comando è nella prima riga in un batch, puoi omettere la parola chiave EXEC e codificare solo il nome della procedura. Per rendere il codice più facile da leggere, tuttavia, dovresti sempre includere la parola chiave EXEC.
• È possibile chiamare una procedura memorizzata dall'interno di un'altra procedura memorizzata. Puoi anche chiamare una procedura memorizzata dall'interno di se stessa. Questa tecnica, chiamata ricorsiva o ricorsione, viene utilizzata raramente nella programmazione SQL.
• Uno dei vantaggi dell'utilizzo delle procedure è che i programmatori dell'applicazione gli utenti non hanno bisogno di conoscere la struttura del database o come codificare SQL.
• Un altro vantaggio dell'utilizzo delle procedure è che possono limitare e controllare l'accesso ad un database. Se si utilizzano le procedure in questo modo è possibile evitare sia errori accidentali che danni dolosi.

Come creare una procedura memorizzata

L'esempio qui sotto presenta la sintassi dell'istruzione CREATE PROC da utilizzare per creare una procedura memorizzata. Codifichi il nome della procedura nell'istruzione CREATE PROC. Tieni presente che i nomi delle procedure memorizzate non possono essere uguali al nome di qualsiasi altro oggetto nel database. Per distinguere una procedura memorizzata da altri oggetti del database, è buona norma anteporre al suo nome le lettere sp. Quando viene eseguita l'istruzione CREATE PROC, la sintassi SQL delle dichiarazioni all'interno della procedura viene controllata. Se hai commesso un errore di codifica, il sistema risponde con un messaggio appropriato e la procedura non viene creata. Poiché la procedura memorizzata viene creata nel database corrente, è necessario per modificare il contesto del database codificare un'istruzione USE prima di CREATE PROC. Inoltre, CREATE PROC deve essere la prima e unica dichiarazione nel batch. Poiché lo script in questo esempio crea la procedura dopo l'istruzione USE e DROP PROC, ad esempio, ha un comando GO subito prima dell'istruzione CREATE PROC. Oltre alle procedure memorizzate archiviate nel database corrente, si possono creare stored procedure temporanee archiviate nel database tempdb. Queste procedure esistono solo mentre la sessione corrente del database è aperta, quindi non vengono utilizzati spesso. Per identificare una procedura memorizzata temporanea, anteporre al nome nome un cancelletto (#) per una procedura locale e due cancelletti (##) per una procedura globale. Dopo il nome della procedura, si codificano le dichiarazioni per i relativi parametri. Imparerai di più a riguardo negli esempi che seguono. È inoltre possibile codificare la clausola facoltativa WITH con l'optione RECOMPILE, l'opzione ENCRYPTION, l'opzione EXECUTE_AS_clause o qualsiasi altra opzione. L'opzione RICOMPILE impedisce al sistema la precompilazione della procedura. Ciò significa che il piano di esecuzione per la procedura deve essere compilata ogni volta che viene eseguita, il che rallenterà notevolmente la procedure. Per questo motivo in genere dovresti omettere questa opzione. Alcune procedure, tuttavia, potrebbero utilizzare valori insoliti o atipici. In tal caso, la prima compilazione potrebbe risultare in un piano di esecuzione non efficiente per le successive esecuzioni. In tal caso, il tempo speso per la ricompilazione della procedura potrebbe essere compensato dalla riduzione del tempo di esecuzione della query. Se scopri che una procedura memorizzata che hai scritto funziona in modo irregolare, potresti provare questa opzione. LA CRITTOGRAFIA è un'opzione di sicurezza che impedisce all'utente di visualizzare la dichiarazione di una procedura memorizzata. Poiché il sistema memorizza la procedura come oggetto nel database, memorizza anche il codice della procedura. Se questo codice contiene informazioni che non desideri che l'utente esamini, dovresti utilizzare questa opzione. L'opzione EXECUTE_AS consente di specificare un'istruzione EXECUTE AS per consentire agli utenti di eseguire la procedura memorizzata con una sicurezza specificata. Ad esempio, puoi utilizzare questa clausola per consentire agli utenti di eseguire l'operazione memorizzata nella procedura con le tue stesse autorizzazioni di sicurezza. In questo modo puoi essere sicuro che la procedura memorizzata funzionerà per il chiamante anche se il chiamante non ha le autorizzazioni per accedere a tutti gli oggetti utilizzati nella procedura memorizzata.

La sintassi dell'istruzione CREATE PROC

    CREATE {PROC | PROCEDURE} procedure_name
    [parameter_declarations]
    [WITH [RECOMPILE] [,ENCRYPTION] [, EXECUTE_AS_clause]]
    AS sql_statements
                

Uno script che crea una procedura memorizzata che copia una tabella

    USE AP
    GO
    IF OBJECT_ID (N'spCopyInvoices', N'P') IS NOT NULL
        DROP PROC spCopyInvoices;
    GO
    CREATE PROC spCopyInvoices
    AS
        IF OBJECT_ID(N'InvoiceCopy', N'U') IS NOT NULL
            DROP TABLE InvoiceCopy;
        SELECT *
        INTO InvoiceCopy
        FROM Invoices
                

Il comando che chiama la store procedure

    USE AP
    GO
    EXEC spCopyInvoices
                

Descrizione

• Si utilizza l'istruzione CREATE PROC per creare una procedura memorizzata nel database. Il nome di una procedura memorizzata può contenere fino a 128 caratteri ed è tipicamente preceduto dalle lettere sp.
• L'istruzione CREATE PROC deve essere la prima e unica istruzione in un batch. Se stai creando la procedura all'interno di uno script, quindi devi codificare un comando GO che deve seguire qualsiasi istruzione che preceda l'istruzione CREATE PROC.
• Per creare una procedura memorizzata temporanea, anteporre al nome della procedura un il segno (#) per una procedura locale o due segni (##) per una procedura globale. La procedura memorizzata temporanea esiste solo mentre la sessione del database corrente è aperta
• È possibile utilizzare i parametri per passare uno o più valori dal programma chiamante alla procedura memorizzata o dalla procedura al programma chiamante.
• La clausola AS contiene le istruzioni SQL che devono essere eseguite dalla procedura memorizzata. Poiché una procedura memorizzata deve essere costituita da un singolo batch, un comando GO viene interpretato come la fine dell'istruzione CREATE PROC.
• L'opzione RICOMPILE impedisce al sistema di precompilare la procedura, il che significa che deve essere compilato ogni volta che viene eseguito. Poiché ciò riduce le prestazioni, in genere non si utilizza questa opzione.
L'opzione ENCRYPTION impedisce agli utenti di visualizzare il codice archiviato nella procedura. Vedere l'esempio qui sotto per ulteriori informazioni sulla visualizzazione delle procedure memorizzate.
• L'opzione EXECUTE_AS_clause è stata introdotta con SQL Server 2005. È possibile utilizzare questa opzione per consentire agli utenti di eseguire la procedura memorizzata con le autorizzazioni specificate dalla clausola EXECUTE AS. Per ulteriori informazioni, vedere il link

Come dichiarare e lavorare con i parametri

L'esempio qui sotto presenta la sintassi per dichiarare i parametri in un'istruzione CREATE PROC. Come una variabile locale, il nome di un parametro deve iniziare con una chiocciola(@). Il tipo di dati per un parametro può essere qualsiasi tipo di dato valido di SQL Server ad eccezione del tipo di dati tabella. Le procedure memorizzate prevedono due diversi tipi di parametri: parametri input e parametri di output. Un parametro di input viene passato alla Stored procedure dal programma chiamante. Un parametro di output viene restituito al programma chiamante dalla procedura memorizzata. Si identifica un parametro di output con la parola chiave OUTPUT. Se questa parola chiave viene omessa, il parametro è considerato un parametro di input. È possibile dichiarare un parametro di input in modo che richieda un valore o il suo valore sia facoltativo. Il valore di un parametro richiesto deve essere passato alla Stored procedure dal programma chiamante altrimenti si verifica un errore. Non è necessario passare il valore di un parametro facoltativo dal programma chiamante. Identifichi un parametro opzionale assegnandogli un valore predefinito. Quindi, se un valore non viene passato dal programma chiamante, viene utilizzato il valore predefinito. Anche se puoi codificare un valore predefinito per un parametro di output, di solito non c'è motivo di farlo. È inoltre possibile utilizzare i parametri di output come parametri di input. Cioè, puoi passare un valore dal programma chiamante alla procedura memorizzata tramite un parametro di output. Tuttavia, questo è un modo insolito di utilizzare i parametri di output. Per evitare confusione, dovresti usare i parametri di output esclusivamente per l'output. All'interno della procedura si utilizzano parametri come variabili. Anche se tu puoi modificare il valore di un parametro di input all'interno della procedura, non viene restituito al programma chiamante e non ha alcun effetto su di esso. Invece, quando termina la procedura, i valori di eventuali parametri di output vengono restituiti al programma chiamante.

La sintassi per dichiarare i parametri

    @parameter_name_l data_type [= default] [OUTPUT]
    [ , @parameter_name_2 data_type [=def ault] [OUTPUT]] ...
                

Dichiarazioni tipiche dei parametri

@DateVar smalldatetime	Parametro di input che accetta un valore di data/ora
@VendorVar varchar(40) = NULL	Parametro di input facoltativo che accetta un valore di carattere
@InvTotal money OUTPUT	Parametro di output che restituisce un valore di tipo money

Un'istruzione CREATE PROC che utilizza un parametro di input e un parametro di output

    USE AP
    GO
    IF OBJECT_ID(N'spInvTotal1', N'P') IS NOT NULL
        DROP PROC spInvTotal1
    GO
    CREATE PROC spInvTotal1
        @DateVar smalldatetime,
        @InvTotal money OUTPUT
    AS
        SELECT @InvTotal = SUM ( InvoiceTotal )
        FROM Invoices
        WHERE InvoiceDate >= @DateVar
                

Il comando che chiama la store procedure

    USE AP
    GO
    DECLARE @InvoiceTotal money;
    EXEC spInvTotal1 '2022-02-01', @InvoiceTotal OUTPUT;
    SELECT @InvoiceTotal
                

Un'istruzione CREATE PROC che utilizza un parametro facoltativo

    USE AP
    GO
    IF OBJECT_ID(N'spInvTotal2', N'P') IS NOT NULL
        DROP PROC spInvTotal2
    GO    
    CREATE PROC spInvTotal2
        @DateVar smalldatetime = NULL
    AS
        IF @DateVar IS NULL
            SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
        SELECT SUM(InvoiceTotal )
        FROM Invoices
        WHERE InvoiceDate >= @DateVar
                

Il comando che chiama la store procedure senza parametro

    USE AP
    GO
    EXEC spInvTotal2
                

Verifico MIN(InvoiceDate)

    USE AP
    GO
    SELECT MIN(InvoiceDate) FROM Invoices
                

Il comando che chiama la store procedure con parametro

    USE AP
    GO
    EXEC spInvTotal2 '2022-10-08'
                

Descrizione

• Per dichiarare un parametro all'interno di una procedura memorizzata, si codifica il nome del parametro seguito dal tipo di dati. Il nome del parametro deve iniziare con il segno chiocciola (@) e il tipo di dati può essere qualsiasi tipo tranne tabella. I parametri sono sempre locali la procedura.
• I parametri di input accettano i valori passati dal programma chiamante.
• I parametri di output memorizzano i valori che vengono restituiti al programma chiamante. Si può identificare un parametro di output codificando la parola chiave OUTPUT dopo il nome del parametro e tipo di dati
• I parametri facoltativi sono parametri che non richiedono il passaggio di un valore dal programma chiamante. Per dichiarare un para1neter opzionale, gli assegni un valore predefinito. Quindi, quel valore viene utilizzato se non ne viene passato uno dal programma chiamante.
• Una procedura memorizzata può dichiarare fino a 2100 parametri. Se dichiari due o più parametri, le dichiarazioni devono essere separate da virgole.
• È una buona pratica di programmazione codificare i tuoi comandi CREATE PROC in modo da elencare prima i parametri obbligatori, seguiti dai parametri facoltativi.

Come chiamare procedure con parametri

L'esempio qui sotto mostra come chiamare le procedure che utilizzano parametri. La procedura in questo esempio accetta due parametri di input e restituisce un parametro in output. Come puoi vedere, entrambi i parametri di input sono facoltativi perché ognuno ha un valore predefinito. Per passare i valori dei parametri a una procedura memorizzata, codificare i valori nella dichiarazione EXEC dopo il nome della procedura. È possibile passare i parametri ad una store procedure per posizione o per nome. La prima istruzione EXEC in questo esempio passa i parametri per posizione. Quando usi questa tecnica, non includi i nomi dei parametri. Invece, i parametri sono elencati nello stesso ordine in cui appaiono nell'istruzione CREATE PROC. Questo è il modo comune per chiamare procedure memorizzate che hanno un breve elenco di parametri. La seconda istruzione EXEC mostra come passare i parametri con il nome. Per fare ciò, includi i nomi dei parametri come definiti nella istruzione CREATE PROC. Quando usi questa tecnica, puoi elencare i parametri in qualsiasi ordine. Se la procedura ha molti parametri, soprattutto se alcuni di essi sono facoltativi, passare i parametri per nome è solitamente più semplice che passare i parametri per posizione. La terza istruzione EXEC in questo esempio mostra come omettere un parametro facoltativo quando si passano i parametri per nome. Per fare ciò, semplicemente ometti il parametro facoltativo. Al contrario, quando passi i parametri per posizione, puoi ometterli solo se compaiono dopo i parametri richiesti. Questo è illustrato dall'ultima istruzione EXEC in questo esempio. Si noti che in tutti e quattro questi esempi l'istruzione EXEC è preceduta da un'istruzione DECLARE che crea una variabile denominata @MyinvTotal. Questo la variabile viene utilizzata per memorizzare il valore del parametro di output che viene restituito dalla procedura memorizzata. Come puoi vedere, il nome di questa variabile è incluso in ciascuna delle istruzioni EXEC in questo esempio. Inoltre, il nome della variabile è seguito dalla parola chiave OUTPUT, che lo identifica come parametro di output.

Un'istruzione CREATE PROC che include tre parametri

    USE AP
    GO
    IF OBJECT_ID(N'spInvTotal3', N'P') IS NOT NULL
        DROP PROC spInvTotal3
    GO
    CREATE PROC spInvTotal3
        @InvTotal money OUTPUT,
        @DateVar date = NULL,
        @VendorVar varchar(40) = '%'
    AS
        IF @DateVar IS NULL
            SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;

        SELECT @InvTotal = SUM(InvoiceTotal)
        FROM Invoices JOIN Vendors
            ON Invoices.VendorID = Vendors.VendorID
        WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar)
                

Codice che passa i parametri per posizione

    USE AP
    GO
    DECLARE @InvTotal money;
    EXEC spInvTotal3 @InvTotal OUTPUT, '2022-02-01', 'Federal Express%';
    SELECT @InvTotal
                

Codice che passa i parametri per nome

    USE AP
    GO
    DECLARE @MyInvTotal money;
    EXEC spInvTotal3 @DateVar = '2022-02-01', @VendorVar = 'Federal Express%', @InvTotal = @MyInvTotal OUTPUT
    SELECT @MyInvTotal
                

Codice che omette un parametro opzionale

    USE AP
    GO
    DECLARE @MyInvTotal money;
    EXEC spInvTotal3 @VendorVar = 'Federal Express%', @InvTotal = @MyInvTotal OUTPUT
    SELECT @MyInvTotal
                

Codice che omette entrambi i parametri opzionali

    USE AP
    GO
    DECLARE @MyInvTotal money;
    EXEC spInvTotal3 @InvTotal = @MyInvTotal OUTPUT
    SELECT @MyInvTotal
                

Descrizione

• Per chiamare una procedura che accetta parametri, si passano i valori alla procedura codificandoli seguendo il nome della procedura. Puoi passare i parametri posizione o per nome.
• Per passare i parametri per posizione, devi elencarli nello stesso ordine in cui appaiono nel file CREATE PROC e separarli con virgole. Quando usi questa tecnica, puoi omettere parametri facoltativi solo se sono dichiarati dopo i parametri richiesti.
• Per passare i parametri per nome, codificare il nome del parametro seguito da un uguale tipo e valore. Puoi separare più parametri con virgole. Quando tu usi questa tecnica, puoi elencare i parametri in qualsiasi ordine e puoi facilmente omettere i parametri facoltativi.
• Per utilizzare un parametro di output nel programma chiamante, è necessario dichiarare una variabile e memorizzarne il valore. Quindi, usi il nome di quella variabile nell'istruzione EXEC e si codifica la parola chiave OUTPUT dopo per identificarla come parametro di output

Come lavorare con i valori di ritorno

Oltre a passare i parametri di output al programma chiamante, le stored procedure memorizzate restituiscono anche un valore di ritorno. Per impostazione predefinita, questo valore è zero. Tuttavia, è possibile utilizzare un'istruzione RETURN per restituire un altro numero. Ad esempio, se una procedura memorizzata aggiorna le righe, è possibile che si voglia restituire il numero di righe che sono state aggiornate. A tale scopo, è possibile utilizzare la funzione @@ROWCOUNT descritta nella lezione 14. Nella figura che segue, la stored procedure denominata spinvCount restituisce un conteggio del numero di fatture che soddisfano le condizioni specificate dai parametri di input. Questi parametri sono identici a quelli utilizzati dalla stored procedure della figura precedente. Tuttavia, dal momento che questa procedura utilizza un'istruzione RETURN per restituire un valore intero, non è necessario utilizzare un parametro di output. Lo script che richiama la procedura utilizza una variabile per memorizzare il valore di ritorno. A tale scopo, il nome della variabile viene codificato dopo la parola chiave EXEC, seguita da un segno di uguale e dal nome della procedura memorizzata. Dopo la restituzione del controllo allo script, quest'ultimo utilizza un'istruzione PRINT per stampare il valore di ritorno. In questa figura, lo script ottiene il conteggio delle fatture in cui la data della fattura è dopo il 1° febbraio 2016 e il nome del fornitore inizia con P. In questo caso, il valore di ritorno indica che 6 fatture corrispondono a queste specifiche. Quando utilizzare l'istruzione RETURN per restituire i valori e quando utilizzare i parametri di output? Se una stored procedure deve restituire un singolo valore intero, molti programmatori preferiscono usare l'istruzione RETURN, poiché la sintassi per restituire un valore è più concisa e intuitiva rispetto all'uso dei parametri di uscita. Tuttavia, se una stored procedure deve restituire altri tipi di dati, o se deve restituire più valori, l'istruzione RETURN non funziona. In questo caso, si possono usare i parametri di uscita o una funzione per restituire altri tipi di dati (compresi gli insiemi di risultati), come descritto più avanti in questa lezione. Naturalmente è sempre possibile utilizzare un'istruzione RETURN insieme a parametri di uscita quando ciò ha senso.

La sintassi dell'istruzione RETURN per una stored procedure

            
    RETURN [integer_expression]
                

Una stored procedure che restituisce un valore

    USE AP
    GO                    
    DROP PROC IF EXISTS spInvCount;
    GO
    
    CREATE PROC spInvCount
            @DateVar date = NULL,
            @VendorVar varchar(40) = '%'
    AS
        IF @DateVar IS NULL
            SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
        
        DECLARE @InvCount int;
        
        SELECT @InvCount = COUNT(InvoiceID)
        FROM Invoices i
            JOIN Vendors v
            ON i.VendorID = v.VendorID
        WHERE (InvoiceDate >= @DateVar) AND
                (VendorName LIKE @VendorVar);
        RETURN @InvCount;
                

Uno script che richiama la stored procedure

    USE AP
    GO                     
    DECLARE @InvoiceCount int;
    EXEC @InvoiceCount = spInvCount '2023-01-01', 'P%';
    --SELECT  @InvoiceCount AS InvoiceCount
    PRINT 'Invoice count: ' + CONVERT(varchar, @InvoiceCount);
                

Descrizione

• L'istruzione RETURN esce immediatamente dalla procedura e restituisce al programma chiamante un valore intero opzionale. Se non si specifica un valore in questa istruzione, il valore di ritorno è zero.
• Per utilizzare il valore di ritorno nel programma chiamante, è necessario dichiarare una variabile per memorizzarne il valore. Quindi, si codifica il nome della variabile seguito da un segno di uguale e dal nome della procedura nello stato EXEC.

Come convalidare i dati e segnalare gli errori

Oltre a usare l'istruzione TRY ... CATCH per gestire gli errori dopo che si sono verificati, si possono anche prevenire gli errori prima che si verifichino, controllando i dati prima che vengano utilizzati per assicurarsi che siano validi. Il controllo dei dati prima del loro utilizzo è spesso indicato all'indirizzo come convalida dei dati e spesso ha senso eseguire la convalida dei dati all'interno di una stored procedure. Quindi, se i dati non sono validi, si può eseguire il codice che li rende validi, oppure si può restituire un errore al programma chiamante. Per restituire un errore, è spesso utile usare l'istruzione THROW. Quindi, se il programma chiamante contiene un'istruzione TRY ... CATCH, può catturare e gestire l'errore. Altrimenti, la connessione client viene terminata immediatamente. La Figura che segue presenta la sintassi dell'istruzione THROW. Il primo parametro è il numero di errore che si vuole assegnare all'errore. Il valore di questo parametro deve essere pari o superiore a 50000, che lo identifica come un errore personalizzato. Si può utilizzare questo valore per indicare il tipo di errore che si è verificato. Il secondo parametro è semplicemente il messaggio di errore che si desidera visualizzare se l'errore viene generato. Il terzo parametro è lo stato che si vuole associare all'errore. Il codice di stato è strettamente informativo e non ha alcun significato di sistema. È possibile utilizzare qualsiasi valore compreso tra 0 e 255 per rappresentare lo stato in cui si trovava il sistema quando è stato generato l'errore. Nella maggior parte dei casi, per questo argomento si usa il codice 1. La procedura memorizzata in questa figura illustra come funziona l'istruzione THROW. Questa procedura controlla il VendorID passato dal programma prima di eseguire l'operazione di inserimento specificata dall'istruzione INSERT. In questo modo, l'errore di sistema che viene segnalato dal sistema non è più visibile. In questo modo, non si verificherà mai l'errore di sistema che viene generato quando si tenta di inserire una riga con una chiave esterna non valida. Invece, se il valore VendorID è non valido, l'istruzione THROW genera un errore personalizzato che fornisce un messaggio di facile comprensione. In questo caso, l'errore personalizzato contiene un breve messaggio che indica che il VendorID non è valido. Lo script di chiamata in questa figura tenta di inserire una riga nella tabella Fatture con l'ID venditore 799. Poiché questo ID fornitore non esiste nella tabella Venditori, l'inserimento provoca la generazione di un errore personalizzato. Di conseguenza, l'esecuzione del programma salta nel blocco CATCH dell'istruzione TRY ... CATCH. Questo blocco CATCH stampa un messaggio che indica che si è verificato un errore e stampa il messaggio memorizzato nell'errore personalizzato. Quindi, il blocco catch utilizza un'istruzione IF per verificare se il numero di errori è maggiore o uguale a 50000. In caso affermativo, stampa un messaggio sulla terza riga che indica che l'errore è un errore personalizzato. Quando si codifica un'istruzione THROW all'interno di un blocco di istruzioni, deve essere consapevole che deve sempre essere preceduta da un punto e virgola (;). Questo vale anche se l'istruzione THROW è la prima o l'unica istruzione del blocco. Questo è illustrato nell'ultimo esempio di questa figura. Qui si può notare che ho codificato un punto e virgola nella riga precedente l'istruzione THROW. Naturalmente, se l'istruzione THROW è preceduta da un'altra istruzione del blocco, si può codificare il punto e virgola alla fine dell'istruzione. L'istruzione THROW è stata introdotta con SQL Server 2012. Nelle precedenti versioni di SQL Server, si utilizzava l'istruzione RAISERROR per eseguire una funzione simile. Oltre a sollevare errori personalizzati, questa istruzione consente di sollevare errori di sistema. Consente inoltre di specificare un livello di gravità che indica se l'errore è informativo, se l'esecuzione del programma deve saltare in un blocco CATCH o se la connessione del client deve essere terminata. Dato che non si dovrebbero lanciare errori di sistema e che quasi sempre si vuole che un errore venga catturato e gestito, si può semplificare la gestione degli errori usando i comandi THROW invece dei comandi RAISERROR.

La sintassi dell'istruzione THROW

    THROW [error_number, message, state]
                

Una stored procedure che verifica la presenza di una chiave esterna valida

    USE AP
    GO
    DROP PROC IF EXISTS spInsertInvoice;
    GO

    CREATE PROC spInsertInvoice
            @VendorID    int,  @InvoiceNumber  varchar(50),
            @InvoiceDate date, @InvoiceTotal   money,
            @TermsID     int,  @InvoiceDueDate date
    AS
    
    IF EXISTS(SELECT * FROM Vendors WHERE VendorID = @VendorID)
        INSERT Invoices
        VALUES (@VendorID, @InvoiceNumber,
                @InvoiceDate, @InvoiceTotal, 0, 0,
                @TermsID, @InvoiceDueDate, NULL);
    ELSE 
        THROW 50001, 'Not a valid VendorID!', 1;
                

Uno script che richiama la procedura

    USE AP
    GO
    BEGIN TRY
        EXEC spInsertInvoice 799,'ZXK-799','2023-03-01',299.95,1,'2023-04-01';
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred.';
        PRINT 'Message: ' + CONVERT(varchar, ERROR_MESSAGE());
        IF ERROR_NUMBER() >= 50000
            PRINT 'This is a custom error message.';
    END CATCH;
                

L'istruzione THROW codificata all'interno di un blocco

    BEGIN
        ;
    THROW 50001, 'Not a valid VendorID! ', 1;
    END;
                

Descrizione

• Il processo di verifica dei valori in una o più colonne è noto come convalida dei dati. È buona norma convalidare i dati all'interno di una stored procedure ogni volta che è possibile.
• L'istruzione THROW solleva sempre un errore. A meno che questo errore non venga catturato da un'istruzione TRY ... CATCH all'interno della stored procedure, l'errore sarà restituito al chiamante proprio come un errore sollevato dal motore del database.
• L'argomento state serve a identificare la gravità di un errore. La gravità di un errore generato con l'istruzione THROW è sempre 16.
• Un'istruzione THROW codificata all'interno di un blocco deve essere preceduta da un punto e virgola.
• Un'istruzione THROW che non include alcun parametro deve essere codificata in un blocco CATCH.

Una stored procedure che gestisce le operazioni di inserimento

La Figura che segue presenta una stored procedure che potrebbe essere utilizzata da un programma che inserisce nuove fatture nella tabella Fatture. Questo dovrebbe dare un'idea più precisa di come si possono usare le stored procedure. Questa procedura inizia con un commento che documenta la stored procedure. Questa documentazione include il nome dell'autore, la data di creazione della procedura, le date di modifica, l'autore della modifica e una descrizione generale dello scopo della procedura. Poiché questa procedura restituisce un valore, anche il valore di ritorno è brevemente descritto nei commenti. Naturalmente, è possibile includere qualsiasi altra informazione ritenuta utile. Questa procedura utilizza nove parametri che corrispondono a nove delle colonne della tabella Fatture. Tutti i parametri sono parametri di input e a ogni parametro viene assegnato lo stesso tipo di dati della colonna corrispondente nella tabella Fatture. Ciò significa che se il programma chiamante passa un valore che non può essere trasformato in tipo di dati appropriato, verrà sollevato un errore durante la chiamata della procedura. In altre parole, questo tipo di errore non sarà colto dalla procedura. Se il programma chiamante passasse un valore di 13-15-89 al parametro @lnvoiceDate, ad esempio, si verificherebbe un errore perché questo valore non può essere lanciato come una data. Per gestire questo tipo di errore all'interno della procedura, si potrebbe definire ogni parametro con il tipo di dati varchar. In questo modo, la procedura potrebbe verificare la presenza di tipi di dati non validi e, se necessario, generare gli errori appropriati. A tutti i parametri di input viene inoltre assegnato il valore predefinito di NULL. Poiché la maggior parte delle colonne della tabella Fatture non può accettare valori nulli, questo potrebbe sembrare un problema. Tuttavia, come si vedrà tra poco, la procedura verifica il valore di ogni parametro prima di tentare l'inserimento. Quindi, se il parametro contiene un valore non valido, viene restituito un errore appropriato al programma chiamante e l'operazione di inserimento non viene mai eseguita. Codificando la procedura in questo modo, è possibile risolvere alcuni errori fornendo valori predefiniti e restituire messaggi di errore personalizzati per altri errori.

Creo la tabella Terms

    USE AP
    GO                
    -- create the Terms table
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE Terms (
        TermsID int IDENTITY(1,1) NOT NULL,
        TermsDescription varchar(50) NOT NULL,
        TermsDueDays smallint NOT NULL,
        CONSTRAINT PK_Terms PRIMARY KEY CLUSTERED 
        (
            TermsID ASC
        ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
    ) ON [PRIMARY]
                

Inserisco i dati nella tabella Terms

    USE AP
    GO                
    SET IDENTITY_INSERT Terms ON 
    INSERT Terms (TermsID, TermsDescription, TermsDueDays) VALUES (1, N'Net due 10 days', 10)
    INSERT Terms (TermsID, TermsDescription, TermsDueDays) VALUES (2, N'Net due 20 days', 20)
    INSERT Terms (TermsID, TermsDescription, TermsDueDays) VALUES (3, N'Net due 30 days', 30)
    INSERT Terms (TermsID, TermsDescription, TermsDueDays) VALUES (4, N'Net due 60 days', 60)
    INSERT Terms (TermsID, TermsDescription, TermsDueDays) VALUES (5, N'Net due 90 days', 90)
    SET IDENTITY_INSERT Terms OFF
                

Una procedura memorizzata che convalida i dati di una nuova fattura

    USE AP
    GO

    DROP PROC IF EXISTS spInsertInvoice;
    GO
    
    CREATE PROC spInsertInvoice
            @VendorID       int = NULL,
            @InvoiceNumber  varchar(50) = NULL,
            @InvoiceDate    date = NULL,
            @InvoiceTotal   money = NULL,
            @PaymentTotal   money = NULL,
            @CreditTotal    money = NULL,
            @TermsID        int = NULL,
            @InvoiceDueDate date = NULL,
            @PaymentDate    date = NULL
    AS
                    
        IF NOT EXISTS (SELECT * FROM Vendors WHERE VendorID = @VendorID)
            THROW 50001, 'Invalid VendorID.', 1;
        IF @InvoiceNumber IS NULL
            THROW 50001, 'Invalid InvoiceNumber.', 1;
        IF @InvoiceDate IS NULL 
        OR @InvoiceDate > GETDATE() 
        OR DATEDIFF(dd, @InvoiceDate, GETDATE()) > 30
            -- DATEDIFF ( datepart , startdate , enddate )
            -- errore quando data fattura anteriore di più di 30 gg rispetto ad oggi
            THROW 50001, 'Invalid InvoiceDate.', 1;
        IF @InvoiceTotal IS NULL 
        OR @InvoiceTotal <= 0
            THROW 50001, 'Invalid InvoiceTotal.', 1;
        IF @PaymentTotal IS NULL
            SET @PaymentTotal = 0;
        IF @CreditTotal IS NULL
            SET @CreditTotal = 0;
        IF @CreditTotal > @InvoiceTotal
            THROW 50001, 'Invalid CreditTotal.', 1;
        IF @PaymentTotal > @InvoiceTotal - @CreditTotal
            THROW 50001, 'Invalid PaymentTotal.', 1;
        IF NOT EXISTS (SELECT * FROM Terms WHERE TermsID = @TermsID)
            IF @TermsID IS NULL
                SELECT @TermsID = DefaultTermsID
                FROM Vendors
                WHERE VendorID = @VendorID;
            ELSE  -- @TermsID IS NOT NULL
                THROW 50001, 'Invalid TermsID.', 1;
        IF @InvoiceDueDate IS NULL
            BEGIN
                DECLARE @TermsDueDays int = (SELECT TermsDueDays 
                                            FROM Terms 
                                            WHERE TermsID = @TermsID);
        
                SET @InvoiceDueDate = DATEADD(day, @TermsDueDays, @InvoiceDate);
            END
        ELSE  -- @InvoiceDueDate IS NOT NULL
            IF @InvoiceDueDate < @InvoiceDate OR
                    DATEDIFF(dd, @InvoiceDueDate, @InvoiceDate) > 180
                THROW 50001, 'Invalid InvoiceDueDate.', 1;
        IF @PaymentDate < @InvoiceDate OR
                DATEDIFF(dd, @PaymentDate, GETDATE()) > 14
            THROW 50001, 'Invalid PaymentDate.', 1;
                    
        INSERT Invoices
        VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, @InvoiceTotal,
                @PaymentTotal, @CreditTotal, @TermsID, @InvoiceDueDate, 
                @PaymentDate);
        RETURN @@IDENTITY;
                

Descrizione

• Una serie di istruzioni IF viene utilizzata per convalidare i dati di ogni colonna della nuova riga di fattura. Se il valore di una colonna non è valido, viene utilizzata un'istruzione THROW per restituire un errore personalizzato al programma chiamante. In questo modo, la procedura memorizzata viene terminata.
• Alcune delle condizioni testate da questo codice potrebbero essere realizzate utilizzando i vincoli. Tuttavia, verificando queste condizioni prima dell'esecuzione dell'istruzione INSERT si evitano errori di sistema e si può restituire un messaggio di errore personalizzato di facile comprensione. Altre condizioni testate da questo codice non possono essere applicate utilizzando i vincoli. Se i parametri di input superano tutti i test di convalida, viene eseguita l'istruzione INSERT. Poiché i dati sono già stati convalidati, l'istruzione INSERT dovrebbe essere eseguita quasi sempre con successo e inserire la riga. Quindi, l'istruzione seguente utilizza la funzione @@IDENTITY per ottenere il nuovo valore dell'ID della fattura generato quando la riga viene inserita e lo restituisce al programma chiamante. Tuttavia, anche se i dati per l'inserimento sono stati convalidati, è possibile che si verifichi un errore di sistema imprevisto. In questo caso, SQL Server solleverà un errore di sistema e terminerà la stored procedure. Nella maggior parte dei casi, una stored procedure di questo tipo verrebbe richiamata da un programma applicativo. Poiché i dettagli di questa operazione esulano dallo scopo di questa lezione, questa figura presenta uno script SQL che richiama la procedura. Questo script include un'elaborazione simile a quella che potrebbe essere utilizzata in un programma applicativo. In breve, questo script utilizza un'istruzione TRY ... CATCH per catturare eventuali errori che sono stati sollevati e per gestirli in modo appropriato. In molti casi, la gestione di un errore è semplice come la visualizzazione di un messaggio che descrive l'errore e indica cosa si può fare per risolvere il problema. In questa figura, ad esempio, il blocco CATCH utilizza tre istruzioni PRINT per indicare che si è verificato un errore, per visualizzare il numero di errore e per visualizzare il messaggio di errore. In altri casi, tuttavia, la gestione degli errori può includere un'elaborazione aggiuntiva, come il salvataggio dei dati o l'uscita dal programma nel modo più corretto possibile.

Uno script che richiama la procedura quando si verifica un errore di convalida

    USE AP
    GO                    
    BEGIN TRY
        DECLARE @InvoiceID int;
        EXEC @InvoiceID = spInsertInvoice
                @VendorID = 799,-- errore
                @InvoiceNumber = 'RZ99381',
                @InvoiceDate = '2023-02-12',-- errore
                @InvoiceTotal = 1292.45;
        PRINT 'Row was inserted.';
        PRINT 'New InvoiceID: ' + CONVERT(varchar, @InvoiceID);
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred. Row was not inserted.';
        PRINT 'Error number: ' + CONVERT(varchar, ERROR_NUMBER());
        PRINT 'Error message: ' + CONVERT(varchar, ERROR_MESSAGE());
    END CATCH;
                

Uno script che richiama la procedura per un inserimento riuscito

    USE AP
    GO                    
    BEGIN TRY
        DECLARE @InvoiceID int;
        DECLARE @myInvoiceDate DATETIME =  DATEADD(day, -15, GETDATE());
        EXEC @InvoiceID = spInsertInvoice
                @VendorID = 34,-- IBM
                @InvoiceNumber = 'RZ99381',
                @InvoiceDate = @myInvoiceDate,
                @InvoiceTotal = 1292.45;
        PRINT 'Row was inserted.';
        PRINT 'New InvoiceID: ' + CONVERT(varchar, @InvoiceID);
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred. Row was not inserted.';
        PRINT 'Error number: ' + CONVERT(varchar, ERROR_NUMBER());
        PRINT 'Error message: ' + CONVERT(varchar, ERROR_MESSAGE());
    END CATCH;
                

Descrizione

• Se i dati in tutte le colonne della nuova riga sono validi, la procedura esegue un'istruzione INSERT per inserire la riga. Se l'inserimento ha successo, la procedura ottiene il nuovo valore InvoiceID e lo restituisce al programma chiamante, che termina la procedura. In caso contrario, il motore del database solleva un errore di sistema, restituisce un valore di zero e termina la procedura.
• Se questa procedura fosse chiamata da un programma applicativo, il programma avrebbe bisogno di gestire gli errori che si verificano. Ciò include gli errori personalizzati generati da questa procedura memorizzata e gli errori di sistema imprevisti generati da SQL Server se non riesce a eseguire l'istruzione INSERT.

Come passare una tabella come parametro

Finora tutte le stored procedure mostrate in questa lezione hanno accettato come parametri i valori scalari. Tuttavia, in alcuni casi è possibile passare un'intera tabella a una stored procedure. Ad esempio, si potrebbe voler passare più fatture o voci di riga a una stored procedure per l'elaborazione. Prima di SQL Server 2008, i programmatori dovevano sviluppare le proprie soluzioni per passare una tabella come parametro. Sfortunatamente, questo comportava spesso un codice personalizzato difficile da sviluppare e mantenere. Con SQL Server 2008 e versioni successive, è possibile passare una tabella come parametro, come mostrato nella figura seguente. Prima di poter passare una tabella come parametro, è necessario definire un tipo di dati per la tabella. In altre parole, è necessario creare un tipo di tabella definito dall'utente. A tale scopo, è possibile creare un'istruzione CREATE TYPE come quella mostrata in questa figura. Questa istruzione definisce un tipo di dati chiamato Lineitems per una tabella che contiene colonne simili alle colonne della tabella InvoiceLineitems, compresa la stessa definizione per la chiave primaria. Tuttavia, le chiavi esterne non sono consentite per i tipi di tabella definiti dall'utente. Di conseguenza, le definizioni delle colonne in questa figura non includono le chiavi esterne. Una volta definito un tipo di tabella, è possibile creare una stored procedure che accetti questo tipo di dati come parametro. In questa figura, ad esempio, l'istruzione CREATE PROC crea una procedura che accetta un singolo parametro chiamato @Lineitems del tipo Lineitems. Di conseguenza, il corpo della stored procedure può trattare questo parametro come se fosse una variabile di tabella. In questa figura, ad esempio, un'istruzione INSERT inserisce tutte le righe memorizzate nel parametro @Lineitems nella tabella InvoiceLineitems. Questo funziona perché il tipo Lineitems contiene lo stesso numero e tipo di colonne della tabella InvoiceLineitems. Quando si codifica una stored procedure che accetta una tabella come parametro, deve utilizzare la parola chiave READONLY per identificare il parametro come di sola lettura. Il risultato è che non è possibile modificare i dati memorizzati nel parametro. Tuttavia, al di fuori della stored procedure, è possibile modificare i dati memorizzati in una variabile di un tipo di tabella definita dall'utente. In questa figura, ad esempio, il codice che passa la tabella alla stored procedure inizia dichiarando una variabile di tipo Lineitems. Quindi, utilizza tre istruzioni INSERT per inserire tre righe nella tabella. Infine, utilizza un'istruzione EXEC per passare la tabella alla procedura. Se necessario, si può anche unire una tabella passata come parametro con un'altra tabella. Ad esempio, si supponga di voler aggiornare la tabella InvoiceLineitems con i dati della tabella @Lineitems. Per farlo, si potrebbe scrivere una dichiarazione del tipo:

    UPDATE InvoiceLineitems
    SET InvoiceLineitemAmount = li.ItemAmount
    FROM InvoiceLineitems i JOIN @Lineitems li
        ON i.InvoiceID = li.InvoiceID
        AND i.InvoiceSequence = li.InvoiceSequence;
                

In questo caso, la tabella InvoiceLineitems ha una chiave primaria definita da due colonne. Di conseguenza, le tabelle vengono unite in base a entrambe le colonne.

La sintassi per la creazione di un tipo di tabella definito dall'utente

    CREATE TYPE TableTypeName AS
    TABLE
    table_definition
                

Un'istruzione che crea un tipo di tabella definito dall'utente

    USE AP
    GO
    
    -- drop table type if it exists already
    DROP TYPE IF EXISTS LineItems;
    GO
    
    -- create the user-defined table type named LineItems
    CREATE TYPE LineItems AS
    TABLE
    (InvoiceID       int           NOT NULL,
    InvoiceSequence  smallint      NOT NULL,
    AccountNo        int           NOT NULL,
    ItemAmount       money         NOT NULL,
    ItemDescription  varchar(100)  NOT NULL,
    PRIMARY KEY (InvoiceID, InvoiceSequence));
                

Un'istruzione che crea una stored procedure che accetta una tabella come parametro.

    USE AP
    GO                    

    -- drop stored procedure if it exists already
    DROP PROC IF EXISTS spInsertLineItems;
    GO

    -- create a stored procedure that accepts the LineItems type
    CREATE PROC spInsertLineItems
        @NewLineItems LineItems READONLY
    AS
        INSERT INTO InvoiceLineItems
        SELECT *
        FROM @NewLineItems;
                

Dichiarazioni che passano una tabella a una stored procedure

    USE AP
    GO 

    -- delete old line item data
    DELETE FROM InvoiceLineItems 
    WHERE InvoiceID = 114
        AND InvoiceSequence > 1;
    
    -- declare a variable for the LineItems type
    DECLARE @NewLineItems LineItems;
    
    -- insert rows into the LineItems variable
    INSERT INTO @NewLineItems VALUES (114, 2, 553, 152.25, 'Freight');
    INSERT INTO @NewLineItems VALUES (114, 3, 553, 29.25, 'Freight');
    INSERT INTO @NewLineItems VALUES (114, 4, 553, 48.50, 'Freight');
    
    -- execute the stored procedure
    EXEC spInsertLineItems @NewLineItems;
                

Descrizione

• Se si desidera passare una tabella come parametro di una stored procedure o di una funzione definita dall'utente, è necessario creare un tipo di tabella definita dall'utente per la tabella.
• Un tipo di tabella definito dall'utente può essere usato solo come parametro di ingresso, non come parametro di uscita.
• La creazione di un tipo di tabella definita dall'utente è simile alla creazione di una tabella normale. Tuttavia, non è possibile definire chiavi esterne per la tabella.

Come eliminare o modificare una stored procedure

La Figura che segue presenta la sintassi dell'istruzione DROP PROC. Questa istruzione viene utilizzata per eliminare una o più stored procedure dal database. Come per le altre istruzioni apprese che eliminano oggetti dal database, l'eliminazione è permanente. Questa figura presenta anche la sintassi dell'istruzione ALTER PROC. Questa istruzione viene utilizzata per ridefinire una stored procedure esistente. Come si può notare, la sintassi è la stessa dell'istruzione CREATE PROC. Come l'istruzione ALTER VIEW, ALTER PROC sostituisce completamente la definizione precedente della stored procedure. Per questo motivo, di solito modifica la definizione di una stored procedure cancellando la procedura e ricreandola. Tuttavia, se sono state assegnate autorizzazioni di sicurezza per limitare gli utenti che possono chiamare la procedura, tali autorizzazioni vengono perse quando si elimina la procedura. Se si desidera mantenere le autorizzazioni, è necessario utilizzare l'istruzione ALTER PROC. Gli esempi di questa figura mostrano come si possono usare le istruzioni ALTER PROC e DROP PROC. Il primo esempio crea una stored procedure denominata spVendorState che seleziona i venditori dallo stato specificato dal parametro @State. Poiché l'istruzione SELECT fallirà se non viene specificato uno stato, questo parametro è necessario. Nel secondo esempio, invece, si utilizza un'istruzione ALTER PROC per modificare la procedura in modo che lo stato sia facoltativo. L'ultimo esempio elimina questa procedura. Se si elimina una tabella o una vista utilizzata da una stored procedure, è necessario assicurarsi che elimini anche la stored procedure. In caso contrario, la stored procedure può ancora essere richiamata da qualsiasi utente o programma che vi abbia accesso. Si verificherà quindi un errore perché la tabella o la vista è stata eliminata.

La sintassi dell'istruzione DROP PROC

    DROP {PROCIPROCEDURE} procedure_name [, ... ]
                

La sintassi dell'istruzione ALTER PROC

        ALTER (PROC | PROCEDURE) procedure_name
        [parameter declarations]
        [WITH [RECOMPILE] [,ENCRYPTION] [, EXECUTE_AS_clause]]
        AS sql_statements
                

Un'istruzione che crea una procedura

    USE AP
    GO 
               
    DROP PROC IF EXISTS spVendorState;
    GO
    
    CREATE PROC spVendorState
            @State varchar(20)
    AS
    SELECT VendorName
    FROM Vendors
    WHERE VendorState = @State;
                

Un'istruzione che modifica il parametro definito dalla procedura

    USE AP
    GO 
               
    ALTER PROC spVendorState
        @State varchar(20) = NULL
    AS
    IF @State IS NULL
        SELECT VendorName
        FROM Vendors;
    ELSE
        SELECT VendorName
        FROM Vendors
        WHERE VendorState = @State;
                

Un'istruzione che cancella la procedura

    USE AP
    GO 

    DROP PROC spVendorState;
                

Descrizione

• Per eliminare una procedura memorizzata dal database, utilizzare l'istruzione DROP PROC
• Per modificare la definizione di una procedura, si può cancellare la procedura e poi crearla di nuovo, oppure si può usare l'istruzione ALTER PROC per specificare la nuova definizione.
• Quando si elimina una procedura, vengono eliminate anche le autorizzazioni di sicurezza assegnate alla procedura. Se non si vuole questo, si può usare l'istruzione ALTER PROC per modificare la procedura e mantenere i permessi.

Come lavorare con le stored procedure di sistema

SQL Server è dotato di centinaia di procedure memorizzate di sistema che possono essere utilizzate per gestire e mantenere i database. Queste procedure sono memorizzate nel database Master, ma possono essere richiamate da qualsiasi database. La Figura che segue presenta una tabella di procedure memorizzate di sistema comunemente utilizzate. Questa figura presenta anche uno script che richiama la procedura memorizzata di sistema sp_HelpText. Questa procedura restituisce il codice SQL specificato dall'istruzione CREATE per una vista, una procedura memorizzata, una funzione definita dall'utente o un trigger. Se l'oggetto è stato creato con l'opzione WITH ENCRYPTION, il codice SQL non può essere restituito. Poiché il codice delle procedure memorizzate di sistema non è mai crittografato, è possibile esaminare anche il codice di queste procedure. Le stored procedure di sistema possono essere utilizzate per semplificare le attività amministrative Tuttavia, si consiglia di evitare di usare queste procedure nei programmi di produzione. Infatti, ogni volta che viene rilasciata una nuova versione di SQL Server, alcune di queste procedure memorizzate possono cambiare. In tal caso, potrebbe essere necessario riscrivere i programmi che le utilizzano. Oltre alle stored procedure di sistema fornite da SQL Server, è possibile creare le proprie stored procedure di sistema. A tale scopo, si crea la procedura nel database Master e si assegna alla procedura un nome che inizia con sp_.

Procedure memorizzate di sistema comunemente utilizzate

Procedure memorizzate di sistema

Procedure	Descrizione
sp_Help [name]	Restituisce informazioni sull'oggetto o sul tipo di dati del database specificato. Senza un parametro, restituisce un riepilogo di tutti gli oggetti del database corrente.
sp_HelpText name	Restituisce il testo di una stored procedure non codificata, di una funzione definita dall'utente, di un trigger o di una vista.
sp_HelpDb [database_name]	Restituisce informazioni sul database specificato o, se non viene specificato alcun parametro, su tutti i database.
sp_Who [login_ID]	Restituisce informazioni su chi è attualmente collegato e su quali processi sono in esecuzione. Se non viene specificato alcun parametro , vengono restituite informazioni su tutti gli utenti attivi.
sp_Columns name	Restituisce informazioni sulle colonne definite nella tabella o nella vista specificata.

Come utilizzare la stored procedure di sistema sp_HelpText

    USE AP
    GO                     
    EXEC sp_HelpText spInvoiceReport;
                

Descrizione

• Microsoft SQL Server include numerose procedure memorizzate di sistema che possono essere utilizzate per eseguire operazioni utili su un database. Queste procedure sono identificate dal prefisso sp_. Queste procedure possono essere utilizzate negli script e nelle procedure scritte dall'utente.
• Le procedure memorizzate di sistema sono memorizzate nel database Master, ma possono essere eseguite su qualsiasi database. Queste procedure operano nel contesto del database corrente impostato con lo stato USE.
• SQL Server dispone di centinaia di stored procedure di sistema. Per visualizzare l'elenco completo, cercate "syste1n stored procedures" nella documentazione di SQL Server.
• È anche possibile creare le proprie stored procedure di sistema. A tale scopo, occorre assegnare alla procedura un nome che inizi con sp_ e crearla nel database Master.

Come codificare le funzioni definite dall'utente

Oltre alle funzioni di SQL Server conosciute in questo libro, è possibile creare funzioni personalizzate, chiamate funzioni definite dall'utente. Per farlo, si utilizza un codice simile a quello utilizzato per creare una stored procedure. Esistono tuttavia alcune differenze tra le stored procedure e le funzioni definite dall'utente. Queste differenze sono illustrate negli argomenti che seguono.

Introduzione alle funzioni definite dall'utente

La Figura che segue riassume i tre tipi di funzioni definite dall'utente, chiamate anche UDF o semplicemente funzioni, che si possono creare con Transact-SQL. Le funzioni con valore scalare sono come le funzioni conosciute nella lezione 9 che restituiscono un singolo valore. Oltre alle funzioni con valore scalare, tuttavia, è possibile creare anche funzioni con valore di tabella. Come dice il nome, una funzione con valore di tabella restituisce un'intera tabella. Una funzione con valore di tabella basata su una singola istruzione SELECT è chiamata funzione con valore di tabella semplice. Al contrario, una funzione con valore di tabella basata su più istruzioni SQL è chiamata funzione con valore di tabella a più istruzioni. Come una stored procedure, una UDF può accettare uno o più parametri di input. La funzione mostrata in questa figura, ad esempio, accetta un parametro chiamato @VendorName. Tuttavia, una UDF non può essere definita con parametri di uscita. Al contrario, l'istruzione RETURN deve essere usata per passare un valore al programma chiamante. Il valore restituito deve essere compatibile con il tipo di dati specificato nella clausola RETURNS. In questo esempio, viene restituito un intero contenente il valore VendorID selezionato dalla tabella Vendors. Per chiamare o invocare una funzione con valore scalare, la si include in un'espressione. Quindi, il valore restituito dalla funzione viene sostituito dalla funzione stessa. La prima istruzione SELECT in questa figura, ad esempio, utilizza il valore restituito dalla funzione fnVendorID nella sua clausola WHERE. Si noti che quando si fa riferimento a una funzione definita dall'utente, è necessario includere il nome dello schema. In questo caso, lo schema è dbo. Per invocare una funzione con valore di tabella, si fa riferimento ad essa ovunque si codifichi normalmente il nome di una tabella o di una vista. La seconda istruzione SELECT di questa figura, ad esempio, utilizza una funzione denominata fnTopVendorsDue nella clausola FROM. La definizione di questa funzione è riportata più avanti in questo capitolo. A differenza di una stored procedure, una UDF non può apportare modifiche permanenti agli oggetti di un database. Ad esempio, non può inviare istruzioni INSERT, UPDATE e DELETE alle tabelle o alle viste del database. Tuttavia, nel codice di una funzione, è possibile creare una tabella, una tabella temporanea o una variabile di tabella. Quindi, la funzione può eseguire operazioni di inserimento, aggiornamento e cancellazione su tale tabella.

I tre tipi di funzioni definite dall'utente

funzioni definite dall'utente

Tipo di funzione	Descrizione
Funzione a valore scalare	Restituisce un singolo valore di qualsiasi tipo di dati T-SQL
Funzione semplice con valore di tabella	Restituisce una tabella basata su una singola istruzione SELECT.
Funzione con valore di tabella a più dichiarazioni	Restituisce una tabella basata su più dichiarazioni

Un'istruzione che crea una funzione a valore scalare

    USE AP
    GO

    DROP FUNCTION IF EXISTS fnVendorID;
    GO
    
    CREATE FUNCTION fnVendorID
        (@VendorName varchar(50))
        RETURNS int
    BEGIN
        RETURN (SELECT VendorID FROM Vendors WHERE VendorName = @VendorName);
    END;
                

Un'istruzione che invoca la funzione a valore scalare

    USE AP
    GO
    SELECT InvoiceDate, InvoiceTotal
    FROM Invoices
    WHERE VendorID = dbo.fnVendorID('IBM');
                

Come creare una funzione a valore scalare

La Figura che segue presenta la sintassi dell'istruzione CREATE FUNCTION per creare una funzione a valore scalare. La clausola CREATE FUNCTION nomina la funzione e dichiara i parametri di ingresso. Se non si specifica il nome dello schema come parte del nome, la funzione viene memorizzata nello schema associato all'utente corrente. La sintassi utilizzata per dichiarare i parametri di una funzione è simile alla sintassi utilizzata per dichiarare i parametri delle stored procedure. Tuttavia, nel caso di una funzione, le dichiarazioni devono essere racchiuse tra parentesi. Inoltre, poiché una funzione non può avere parametri di uscita, la parola chiave OUTPUT non è consentita. Per invocare una funzione che ha dei parametri, è necessario passare i parametri per posizione. Non è possibile passarli per nome, come avviene quando si chiama una procedura memorizzata. Per questo motivo, si dovrebbero codificare prima i parametri obbligatori, seguiti da quelli opzionali. Inoltre, non è possibile omettere i parametri opzionali quando si richiama una funzione, come si può fare con una stored procedure. Al contrario, è necessario che utilizzi la parola chiave DEFAULT come segnaposto per il parametro opzionale. La clausola RETURNS specifica il tipo di dati del valore restituito dalla funzione. Poiché il valore deve essere scalare, non è possibile specificare il tipo di dati della tabella. Inoltre, non è possibile specificare testo, ntext, image o timestamp. Le istruzioni per la funzione vengono codificate all'interno di un blocco BEGIN ... END. All'interno di questo blocco, l'istruzione RETURN specifica il valore che viene passato indietro al programma che la invoca. Poiché questa istruzione fa terminare la funzione, è solitamente codificata alla fine della funzione. Si noti che, a differenza di un'istruzione RETURN codificata all'interno di una stored procedure, un'istruzione RETURN codificata all'interno di una funzione può restituire un valore di qualsiasi tipo di dati. All'interno di una specifica funzione, tuttavia, deve restituire un valore con un tipo di dati compatibile con il tipo di dati specificato dalla clausola RETURNS. La funzione scalare mostrata in questa figura non accetta alcun parametro di ingresso e restituisce un valore con il tipo di dati money. In questo caso, il codice per la funzione consiste in una singola istruzione SELECT codificata all'interno dell'istruzione RETURN. Tuttavia, una funzione può includere tutte le istruzioni necessarie per calcolare il valore di ritorno. Se ci si ritrova a codificare ripetutamente la stessa espressione, è possibile creare una funzione con valore scalare per l'espressione. In seguito, si può utilizzare la funzione al posto dell'espressione, risparmiando così tempo di codifica e facilitando la manutenzione del codice. La maggior parte dei programmatori SQL crea una serie di utili UDF ogni volta che lavora su un nuovo database.

La sintassi per la creazione di una funzione con valore scalare

    CREATE FUNCTION [schema_name.]function_name
        ( [@parameter_name data_ type [= default]] [, ... ] )
        RETURNS data_type
        [WITH [ENCRYPTION] [, SCHEMABINDING] [, EXECUTE_AS_clause]]
        [AS]
    BEGIN
        [sql_statements]
        RETURN scalar_expression
    END
                

Un'istruzione che crea una funzione a valore scalare che restituisce l'importo totale dovuto delle fatture

    USE AP
    GO

    DROP FUNCTION IF EXISTS fnBalanceDue;
    GO
    
    CREATE FUNCTION fnBalanceDue()
        RETURNS money
    BEGIN
        RETURN (SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal)
                FROM Invoices
                WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0);
    END;
                

Uno script che invoca la funzione

    USE AP
    GO                    
    PRINT 'Balance due: $' + FORMAT(dbo.fnBalanceDue(), '#,###.00', 'it-IT');
                

Descrizione

• Le funzioni possono essere definite con un numero di parametri di ingresso compreso tra zero e 1024. I parametri vengono specificati tra parentesi dopo il nome della funzione nell'istruzione CREATE FUNCTION. A ciascun parametro può essere assegnato un valore predefinito opzionale.
• Una funzione non può contenere parametri di uscita. Si deve invece specificare il tipo di dati che la funzione deve restituire nella clausola RETURNS.
• Si codificano le istruzioni che definiscono la funzione all'interno di un blocco BEGIN ... END. Questo blocco include un'istruzione RETURN che specifica il valore da restituire.
• Quando si richiama una funzione, i parametri vengono elencati tra parentesi dopo il nome della funzione. Per utilizzare il valore predefinito di un parametro, si usa la parola chiave DEFAULT al posto del valore del parametro. Non è possibile passare i parametri delle funzioni per nome.
• Quando si crea una funzione, questa viene memorizzata nello schema associato all'utente corrente, se non si specifica il nome dello schema. Quando si richiama la funzione, invece, è necessario specificare il nome dello schema.
• L'opzione SCHEMABINDING vincola la funzione allo schema del database. Ciò impedisce di eliminare o modificare le tabelle o le viste utilizzate dalla funzione. Questa opzione è più comunemente usata per le funzioni con valore di tabella.
• L'opzione ENCRYPTION impedisce agli utenti di visualizzare il codice della funzione.
• L'opzione EXECUTE_AS_clause specifica il contesto di sicurezza in cui viene eseguita la funzione.

Come creare una semplice funzione con valore di tabella

La Figura che segue presenta la sintassi per la creazione di una semplice funzione con valore di tabella, chiamata anche funzione con valore di tabella in linea. Si utilizza questa sintassi se l'insieme di risultati può essere restituito da una singola istruzione SELECT. In caso contrario, è necessario utilizzare la sintassi presentata nella figura successiva. Per dichiarare la funzione con valore di tabella, si codifica il tipo di dati della tabella nella clausola RETURNS. Quindi, si codifica l'istruzione SELECT che definisce la tabella tra parentesi nell'istruzione RETURN. Poiché una tabella non può avere alcuna colonna senza nome, è necessario assegnare un nome a ogni colonna calcolata nel set di risultati. La funzione mostrata in questa figura restituisce una tabella contenente il nome del fornitore e il saldo totale dovuto per ogni fornitore con un saldo dovuto. L'unico parametro di input, @CutOff, è un parametro opzionale perché gli viene assegnato un valore predefinito di 0. Questo parametro viene utilizzato nella clausola HAVING per restituire solo i fornitori con fatture totali maggiori o uguali all'importo specificato. La prima istruzione SELECT mostrata in questa figura, ad esempio, restituisce i fornitori con fatture totali maggiori o uguali a 5.000 dollari. La seconda istruzione SELECT mostra come sia possibile unire il risultato di una funzione con valore di tabella con un'altra tabella. Si noti che per evitare di dover codificare la funzione in entrambe le clausole FROM e ON, alla funzione è stato assegnato un nome di correlazione. In questo esempio si nota anche che non viene specificato un valore per il parametro opzionale. Al contrario, viene specificata la parola chiave DEFAULT, in modo che venga utilizzato il valore predefinito del parametro. Una funzione con valore di tabella come quella mostrata qui si comporta come una vista dinamica. Poiché una funzione può accettare parametri, l'insieme di risultati che crea può essere modificato. Si tratta di una potente estensione delle funzionalità standard di SQL.

La sintassi per creare una semplice funzione con valore di tabella

    CREATE FUNCTION [schema_name.]function_name
        ( [@parameter_name data_ type [= default]] [, ... ] )
        RETURNS TABLE
        [WITH [ENCRYPTION] [, SCHEMABINDING]]
        [AS]
    RETURN [(] select_statement [)]
                

Un'istruzione che crea una semplice funzione con valore di tabella

    USE AP
    GO
                  
    DROP FUNCTION IF EXISTS fnTopVendorsDue;
    GO
    
    CREATE FUNCTION fnTopVendorsDue
        (@CutOff money = 0)
        RETURNS table
    RETURN
        SELECT VendorName, SUM(InvoiceTotal) AS TotalDue
        FROM Vendors v
            JOIN Invoices i
            ON v.VendorID = i.VendorID
        WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
        GROUP BY VendorName
        HAVING SUM(InvoiceTotal) >= @CutOff;
                

Un'istruzione SELECT che richiama la funzione

    USE AP
    GO                    
    SELECT * FROM dbo.fnTopVendorsDue(5000);
                

Un'istruzione SELECT che utilizza la funzione in un'operazione di join

    USE AP
    GO
    SELECT v.VendorName, VendorCity, TotalDue
    FROM Vendors v
        -- JOIN tra Vendors e la tabella restituita
        -- da fnTopVendorsDue per esporre TotalDue    
        JOIN dbo.fnTopVendorsDue(DEFAULT) t
        ON v.VendorName = t.VendorName;
                

Descrizione

• Si crea una semplice funzione con valore di tabella, detta anche funzione con valore di tabella inline, codificando il tipo di dati della tabella nella clausola RETURNS dell'istruzione CREATE FUNCTION. Quindi, si codifica un'istruzione SELECT che definisce la tabella nell'istruzione RETURN.
• Per utilizzare una semplice funzione con valore di tabella, codificare il nome della funzione al posto del nome della tabella o della vista. Se si utilizza una funzione con valore di tabella in un'operazione di join, è necessario assegnarle un nome di correlazione, come mostrato sopra.

Come creare una funzione con valore di tabella a più dichiarazioni

La Figura che segue presenta la sintassi per la creazione di una funzione con valore di tabella a più dichiarazioni. Anche se questa sintassi dovrebbe essere nota, probabilmente non sarà mai necessario utilizzarla. Infatti, una singola istruzione SELECT con join e subquery può soddisfare quasi tutte le esigenze di query. Poiché una funzione con valore di tabella a più istruzioni crea una nuova tabella, è necessario definire la struttura di tale tabella. A tale scopo, si dichiara una variabile di tabella nella clausola RETURNS e si definiscono le colonne della nuova tabella. La sintassi utilizzata per definire le colonne è simile a quella utilizzata per definire le colonne di una variabile tabella. Le istruzioni SQL che creano la tabella vengono codificate all'interno di un blocco BEGIN ... END. Questo blocco termina con la parola chiave RETURN senza alcun argomento. Questo termina la funzione e restituisce la variabile di tabella al programma che l'ha invocata. La funzione mostrata in questa figura restituisce una tabella con una riga per ogni fattura con un saldo dovuto. Questa funzione calcola l'aggiustamento del credito che sarebbe necessario per ridurre il saldo totale dovuto all'importo di soglia che viene passato alla funzione. Questa funzione inizia utilizzando un'istruzione INSERT per copiare tutte le righe della tabella Fatture con un saldo dovuto nella variabile di tabella @OutTable. Quindi, viene utilizzata un'istruzione WHILE per incrementare di un centesimo la colonna CreditTotal di ogni riga di questa tabella finché l'importo totale dovuto per tutte le fatture non scende al di sotto della soglia. L'istruzione SELECT che utilizza questa funzione riassume la colonna CreditTotal per fornitore.

La sintassi per la creazione di una funzione con valore di tabella a più dichiarazioni

    USE AP
    GO                    
    CREATE FUNCTION fnCreditAdj (@HowMuch money)
        RETURNS @OutTable table
            (InvoiceID int, VendorID int, InvoiceNumber varchar(5O),
            InvoiceDate smalldatetime, InvoiceTotal money,
            PaymentTotal money, CreditTotal money)
    BEGIN
  
        INSERT @OutTable
            SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate,
                InvoiceTotal, PaymentTotal, CreditTotal
            FROM Invoices
            WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0;
        WHILE (SELECT SUM(InvoiceTotal - CreditTotal - PaymentTotal)
                FROM @OutTable) >= @HowMuch
            UPDATE @OutTable
            SET CreditTotal = CreditTotal + .01
            WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0;
        RETURN
    END;
                

Un'istruzione SELECT che utilizza la funzione

    USE AP
    GO
    SELECT VendorName, SUM(CreditTotal) AS CreditRequest
    FROM Vendors JOIN dbo.fnCreditAdj(25000) AS CreditTable
        ON vendors.vendorID = CreditTable.vendorID
    GROUP BY VendorName;
                

Attenzione

• Poiché il codice deve scorrere migliaia di volte la tabella Fatture, l'esecuzione di questa funzione può richiedere molto tempo.

Come codificare i trigger

Un trigger è un tipo speciale di procedura che viene invocata, o lanciata, automaticamente quando una query d'azione viene eseguita su una tabella o una vista. I trigger rappresentano un modo efficace per controllare il modo in cui le query d'azione modificano i dati nel database. Se necessario, è possibile utilizzare i trigger per applicare le regole di progettazione, implementare la logica aziendale e prevenire l'incoerenza dei dati. Tuttavia, i trigger possono causare problemi di blocco e di prestazioni. Inoltre, spesso esiste un modo migliore per svolgere un'attività rispetto all'uso di un trigger. Di conseguenza, si consiglia di usarli con parsimonia.

Come creare un trigger

La Figura che segue presenta la sintassi dell'istruzione CREATE TRIGGER per creare un'attivazione. Notate in questa sintassi che un trigger non può usare parametri. Inoltre, un trigger non può restituire un valore. L'istruzione CREATE TRIGGER prevede due tipi di trigger: trigger AFTER e trigger INSTEAD OF. Entrambi i tipi di trigger possono essere definiti per essere attivati per un'operazione di inserimento, aggiornamento o cancellazione o per qualsiasi combinazione di queste operazioni. Se una query azione ha un trigger AFTER, il trigger scatta dopo la query azione. Se una query azione ha un trigger INSTEAD OF, il trigger viene attivato al posto della query azione. In altre parole, la query d'azione non viene mai eseguita. Oltre a AFTER e INSTEAD OF, è possibile codificare la parola chiave FOR nell'istruzione CREATE TRIGGER. Un trigger FOR è identico a un trigger AFTER. FOR è una parola chiave ANSI-standard ed era l'unica parola chiave consentita prima di SQL Server 2000. Per questi motivi, FOR è più comunemente usato di AFTER. Tuttavia, in questo libro si utilizza AFTER poiché descrive più chiaramente quando il trigger viene attivato. Ogni trigger è associato alla tabella o alla vista indicata nella clausola ON. Sebbene ogni trigger sia associato a una singola tabella o vista, una singola tabella può avere un numero qualsiasi di trigger AFTER. Tuttavia, poiché due o più trigger per la stessa tabella possono creare confusione nella gestione e nel debug, si consiglia di avere non più di un trigger per ogni azione. Ogni tabella o vista può anche avere un trigger INSTEAD OF per ogni azione. Una vista non può avere trigger AFTER. L'istruzione CREATE TRIGGER in questa figura definisce un trigger AFTER per la tabella Vendors. Si noti che il nome di questo trigger riflette la tabella a cui è associato e le operazioni che ne causano l'attivazione. Questa è una convenzione di denominazione comune. In questo caso, il trigger si attiva dopo che è stata eseguita un'operazione di inserimento o di aggiornamento sulla tabella. Come si può vedere, il trigger aggiorna la colonna VendorState in modo che i codici di stato siano in maiuscolo. Si noti che la clausola WHERE del trigger utilizza una sottoquery basata su su una tabella denominata Inserted. Si tratta di una tabella speciale creata da SQL Server durante un'operazione di inserimento. Contiene le righe che vengono inserite nella tabella. Poiché questa tabella esiste solo durante l'esecuzione del trigger, nel codice del trigger è possibile fare riferimento ad essa. Allo stesso modo, durante un'operazione di cancellazione, SQL Server crea una tabella denominata Deleted che contiene le righe che vengono eliminate. Per un'operazione di aggiornamento, SQL Server crea entrambe le tabelle. In questo caso, la tabella Inserted contiene le righe con i dati aggiornati, mentre la tabella Deleted contiene i dati originali delle righe che vengono aggiornate.

Sintassi dell'istruzione CREATE TRIGGER

    CREATE TRIGGER trigger_name
        ON {table_name | view_name}
        [WITH [ENCRYPTION] [,] [EXECUTE_AS_clause]]
        (FOR I AFTER I INSTEAD OF) [INSERT] [I] [UPDATE] [I] [DELETE]
    AS sql_statements
                

Un'istruzione CREATE TRIGGER che corregge i nomi degli stati in lettere miste

    USE AP
    GO

    DROP TRIGGER IF EXISTS Vendors_INSERT_UPDATE;
    GO

    CREATE TRIGGER Vendors_INSERT_UPDATE
        ON Vendors
        AFTER INSERT,UPDATE
    AS
        UPDATE Vendors
        SET VendorState = UPPER(VendorState)
        WHERE VendorID IN (SELECT VendorID FROM Inserted);
                

Un'istruzione INSERT che attiva il trigger

    USE AP
    GO

    INSERT Vendors
    VALUES ('Peerless Uniforms, Inc.', '785 S Pixley Rd', NULL,
            'Piqua', 'Oh', '45356', '(937) 555-8845', NULL, NULL, 4,550);
                

La nuova riga inserita nella tabella Fornitori

    USE AP
    GO
    
    SELECT VendorName, VendorCity, VendorState
    FROM VENDORS
    WHERE VendorName = 'Peerless Uniforms, Inc.';

    -- oppure
    SELECT * FROM Vendors WHERE VendorID = (SELECT MAX(VendorID) FROM Vendors);
                

Descrizione

• Un trigger è un tipo speciale di procedura che si esegue, o si attiva, in risposta a una query di azione. A differenza di una stored procedure, non si può invocare direttamente un trigger, non si possono passare parametri a un trigger e non si può restituire un valore di ritorno.
• Un trigger è associato a una singola tabella o vista, che viene identificata nella clausola ON. L'attivazione può essere impostata per essere attivata su istruzioni INSERT, UPDATE, DELETE o su una combinazione di queste istruzioni.
• Un trigger può essere impostato per essere attivato dopo la query di azione (AFTER) o al posto della query di azione (INSTEAD OF). Un trigger FOR è uguale a un trigger AFTER.
• Una tabella può avere più trigger AFTER, anche per la stessa azione. Una vista non può avere un trigger AFTER. Una tabella o una vista possono avere solo un trigger INSTEAD OF per ogni azione.
• Per nascondere all'utente il codice del trigger, includere l'opzione ENCRYPTION.
• Per eseguire un trigger in un contesto di sicurezza specifico, includere l'opzione EXECUTE_AS_clause.
• È una pratica di programmazione comune nominare i trigger in base alla tabella o alla vista e alle azioni che causeranno l'attivazione del trigger.
• All'interno di un trigger, è possibile fare riferimento a due tabelle create dal sistema: Inserted e Deleted. La tabella Inserted contiene le nuove righe per le operazioni di inserimento e aggiornamento. La tabella Deleted contiene le righe originali per le operazioni di aggiornamento e cancellazione.

Come utilizzare i trigger AFTER

La Figura che segue mostra un altro esempio di trigger AFTER. Questo trigger archivia tutte le righe eliminate dalla tabella Fatture inserendo le righe eliminate in un'altra tabella denominata lnvoiceArchive. L'istruzione CREATE TRIGGER mostrata in questa figura inizia specificando il nome lnvoices_DELETE. Quindi, specifica che questo trigger deve essere eseguito dopo l'esecuzione di un'istruzione DELETE nella tabella Invoices. Il corpo di questo trigger utilizza un'istruzione INSERT per inserire nella tabella InvoiceArchive le righe che sono state eliminate dalla tabella Invoices. Per realizzare ciò, questo trigger utilizza un'istruzione SELECT per recuperare le colonne dalla tabella Deleted. L'istruzione DELETE mostrata in questa figura elimina tre righe dalla tabella Invoices. Questo provoca l'attivazione del trigger lnvoices_DELETE. Di conseguenza, il trigger inserisce queste tre righe nella tabella lnvoiceArchive dopo l'esecuzione dell'istruzione DELETE. Poiché un trigger AFTER si attiva dopo l'esecuzione della query di azione, il trigger non si attiva se la query di azione causa un errore. Di solito, questo è ciò che si vuole. In questa figura, ad esempio, non si vogliono archiviare le righe cancellate se l'istruzione DELETE ha causato un errore e non è stata eseguita correttamente.

Un trigger AFTER che archivia i dati cancellati

    USE AP
    GO
    
    DROP TRIGGER IF EXISTS Invoices_DELETE;
    GO
    
    CREATE TRIGGER Invoices_DELETE
        ON Invoices
        AFTER DELETE
    AS
        IF object_id('InvoiceArchive', 'U') is null
            BEGIN
                SELECT *
                INTO InvoiceArchive
                FROM Deleted;
            END
        ELSE
            BEGIN
                SET IDENTITY_INSERT InvoiceArchive ON 
                INSERT INTO InvoiceArchive
                    (InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal,
                    PaymentTotal, CreditTotal, TermsID,  InvoiceDueDate, PaymentDate)            
                SELECT
                    InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal,
                    PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate
                FROM Deleted;
                SET IDENTITY_INSERT InvoiceArchive OFF
            END
                

Un'istruzione DELETE che provoca l'attivazione del trigger AFTER

    USE AP
    GO

    DELETE Invoices
    WHERE VendorID = 121;
    
    SELECT * FROM InvoiceArchive
    WHERE VendorID = 121;
                

Descrizione

• Un trigger AFTER si attiva dopo l'esecuzione della query d'azione. Se la query d'azione causa un errore, il trigger AFTER non viene mai attivato.
• I trigger AFTER possono essere utilizzati per archiviare i dati eliminati.

Come utilizzare i trigger INSTEAD OF

Un trigger INSTEAD OF può essere associato a una tabella o a una vista. Tuttavia, i trigger INSTEAD OF sono usati più spesso per fornire un migliore controllo delle viste aggiornabili. La Figura che segue presenta un trigger INSTEAD OF utilizzato per controllare un'operazione di inserimento attraverso una vista denominata IBM_Invoices. Questa vista seleziona i dati InvoiceNumber, InvoiceDate e InvoiceTotal dalla tabella Invoices per il fornitore denominato "IBM". Se si esamina il progetto della tabella Invoices, si vedrà che ci sono sei colonne aggiuntive che non ammettono valori nulli. Il problema è: come si fa a inserire valori in queste sei colonne aggiuntive quando la vista non è a conoscenza della loro esistenza? Tre delle sei colonne (InvoiceID, PaymentTotal e CreditTotal) non devono essere inserite nella vista. Questo perché la struttura della tabella Invoices gestisce automaticamente i valori nulli per queste colonne. InvoiceID è una colonna identity, quindi il database inserisce automaticamente il valore di questa colonna. Le altre due colonne hanno un valore predefinito di 0, quindi il database utilizza questo valore predefinito. Rimangono tre colonne (VendorID, TermsID e InvoiceDueDate) che si devono aggiornare quando si inseriscono dati attraverso la vista. Se si tenta di inserire una riga attraverso la vista, non è possibile specificare queste colonne richieste e l'operazione di inserimento fallisce. Questo trigger tiene conto di queste colonne mancanti calcolando i loro valori sulla base di tre ipotesi logiche. In primo luogo, il VendorID può essere assunto perché questa vista è esplicitamente dedicata alle fatture del fornitore "IBM". In secondo luogo, si può supporre che i termini per la fattura siano i termini predefiniti per il fornitore. In terzo luogo, può calcolare la data di scadenza della fattura in base alla data della fattura e ai termini. Dopo aver dichiarato le variabili utilizzate, il trigger interroga la tabella Inserted per ottenere un conteggio del numero di righe che vengono inserite. Poiché questo trigger funzionerà solo se viene inserita una singola riga, viene sollevato un errore se il conteggio delle righe è maggiore di uno. Altrimenti, il trigger interroga la tabella Inserted per ottenere i valori delle tre colonne specificate in un'istruzione INSERT come quella mostrata in questa figura. L'istruzione SELECT assegna questi valori a tre variabili. Quindi, se tutte e tre le variabili contengono valori diversi da null, il trigger calcola i valori delle colonne mancanti. Poiché un trigger INSTEAD OF viene eseguito al posto della query d'azione che fa scattare, l'azione non si verificherà mai, a meno che non venga codificata come parte del trigger. Per questo motivo, l'ultima istruzione di questo trigger è un'istruzione INSERT che inserisce la nuova riga nella tabella Invoices. Senza questa istruzione, la riga non verrebbe mai inserita.

Un trigger INSTEAD OF INSERT per una vista

    USE AP
    GO

    DROP VIEW IF EXISTS IBM_Invoices
    GO

    CREATE VIEW IBM_Invoices
    AS
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
    FROM Invoices
    WHERE VendorID = (SELECT VendorID FROM Vendors WHERE VendorName = 'IBM');
    GO
    
    DROP TRIGGER IF EXISTS IBM_Invoices_INSERT;
    GO
    
    CREATE TRIGGER IBM_Invoices_INSERT
        ON IBM_Invoices
        INSTEAD OF INSERT
    AS
    DECLARE @InvoiceDate date,      @InvoiceNumber varchar(50),
            @InvoiceTotal money,    @VendorID int,
            @InvoiceDueDate date,   @TermsID int,
            @DefaultDays smallint,  @RowCount int;

    SELECT @RowCount = COUNT(*) FROM Inserted;
    
    IF @RowCount = 1
        BEGIN
            SELECT @InvoiceNumber = InvoiceNumber, 
                    @InvoiceDate = InvoiceDate,
                    @InvoiceTotal = InvoiceTotal
            FROM Inserted;
    
            IF (@InvoiceDate IS NOT NULL AND 
                @InvoiceNumber IS NOT NULL AND
                @InvoiceTotal IS NOT NULL)
                BEGIN
                    SELECT @VendorID = VendorID, 
                            @TermsID = DefaultTermsID
                    FROM Vendors 
                    WHERE VendorName = 'IBM';
    
                    SELECT @DefaultDays = TermsDueDays
                    FROM Terms
                    WHERE TermsID = @TermsID;
    
                    SET @InvoiceDueDate =
                        DATEADD(day, @DefaultDays, @InvoiceDate);
    
                    INSERT Invoices (VendorID, InvoiceNumber, InvoiceDate,
                        InvoiceTotal, TermsID, InvoiceDueDate, PaymentDate)
                    VALUES (@VendorID, @InvoiceNumber, @InvoiceDate,
                        @InvoiceTotal, @TermsID, @InvoiceDueDate, NULL);
                END;
        END;
    ELSE
        THROW 50027, 'Limit INSERT to a single row.', 1;
                

Un'istruzione INSERT che riesce perché entra in azione il trigger

    USE AP
    GO

    INSERT IBM_Invoices
    VALUES ('RA23988', '2023-03-09', 417.34);
                

Verifica dell'inserimento

    USE AP
    GO

    SELECT * FROM Invoices
    WHERE InvoiceID = (SELECT MAX(InvoiceID) FROM Invoices);

                

Descrizione

• Un trigger INSTEAD OF viene eseguito al posto della query di azione che ne determina l'attivazione. Poiché la query di azione non viene mai eseguita, il trigger in genere contiene codice che esegue l'operazione.
• I trigger INSTEAD OF vengono in genere utilizzati per fornire viste aggiornabili. Possono anche essere utilizzati per prevenire errori, come violazioni di vincoli, prima che si verifichino.
• Ogni tabella o vista può avere un solo trigger INSTEAD OF per ogni tipo di azione. Tuttavia, se una tabella è definita con un vincolo di chiave esterna che specifica l'opzione CASCADE UPDATE o CASCADE DELETE, i trigger INSTEAD OF UPDATE e INSTEAD OF DELETE non possono essere definiti per la tabella.

Prospettiva

In questa lezione hai imparato a creare i tre tipi di oggetti di database eseguibili supportati da SQL Server utilizzando istruzioni SQL. Le stored procedure sono le più flessibili delle tre perché possono essere utilizzate in moltissimi modi diversi. Puoi codificare le procedure per semplificare e limitare contemporaneamente l'accesso di un utente al database, per verificare l'integrità dei dati e per semplificare le tue attività amministrative. Sebbene siano generalmente meno flessibili delle stored procedure, le funzioni e i trigger sono oggetti potenti. Puoi usarli per risolvere problemi che altrimenti sarebbero difficili o impossibili da risolvere. In particolare, le funzioni con valori di tabella sono una delle estensioni più utili fornite da Transact-SQL perché si comportano come viste ma possono accettare parametri che possono modificare il set di risultati. Oltre a utilizzare istruzioni SQL per lavorare con stored procedure, funzioni e trigger, puoi utilizzare Management Studio. Troverai tutti e tre questi tipi di oggetti nelle cartelle all'interno della cartella Programmabilità di un database. È quindi possibile aggiungere, modificare ed eliminare oggetti utilizzando i menu che compaiono facendo clic con il pulsante destro del mouse su una cartella o un oggetto. È possibile utilizzare questa stessa tecnica per lavorare con i tipi di tabella definiti dall'utente utilizzati con le stored procedure.

---

Esercizi

Se non diversamente indicato, utilizzare la sintassi di unione esplicita

Invia

---