Corso SQL Server

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
TipoBatchesCome viene archiviatoCome viene eseguitoAccetta parametri
ScriptMoltepliciIn un file su un discoDall'interno di uno strumento client come Management Studio o SQLCMDNo
Stored procedureSolo unoIn un oggetto nel databaseDa un'applicazione o dall'interno di uno script SQLSì
Funzione definita dall'utenteSolo unoIn un oggetto nel databaseDa un'applicazione o dall'interno di uno script SQLSì
TriggerSolo unoIn un oggetto nel databaseAutomaticamente dal server di database quando una specifica query di azione viene eseguitaNo

Descrizione

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.

Un comando che chiama la procedure

    USE AP
    GO
    EXEC spInvoiceReport;

Il set di risultati creato dalla procedura

Descrizione

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 ( 'spCopyInvoices' ) IS NOT NULL
        DROP PROC spCopyInvoices;
    GO
    CREATE PROC spCopyInvoices
    AS
        IF OBJECT_ID('InvoiceCopy') IS NOT NULL
            DROP TABLE InvoiceCopy;
        SELECT *
        INTO InvoiceCopy
        FROM Invoices;

Descrizione

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 smalldatetimeParametro di input che accetta un valore di data/ora
@VendorVar varchar ( 40) = NULLParametro di input facoltativo che accetta un valore di carattere
@InvTotal money OUTPUTParametro 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
    CREATE PROC spInvTotal1
        @DateVar smalldatetime,
        @InvTotal money OUTPUT
    AS
        SELECT @InvTotal = SUM ( InvoiceTotal )
        FROM Invoices
        WHERE InvoiceDate >= @DateVar;

Un'istruzione CREATE PROC che utilizza un parametro facoltativo

    USE AP
    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;

Descrizione

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
    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

Come lavorare con i valori di ritorno