Le funzioni in SQL Server sono gli oggetti di database che contengono a insieme di istruzioni SQL per eseguire un'attività specifica . Una funzione accetta parametri di input, esegue azioni e quindi restituisce il risultato. Dovremmo notare che le funzioni restituiscono sempre un singolo valore o una tabella. Lo scopo principale delle funzioni è replicare facilmente l'attività comune. Possiamo creare funzioni una volta e utilizzarle in più luoghi in base alle nostre esigenze. SQL Server non consente l'utilizzo delle funzioni per l'inserimento, l'eliminazione o l'aggiornamento dei record nelle tabelle del database.
Di seguito sono riportate le regole per la creazione di funzioni SQL Server:
- Una funzione deve avere un nome e il nome non può iniziare con un carattere speciale come @, $, # o altri caratteri simili.
- Le istruzioni SELECT sono le uniche che operano con le funzioni.
- Possiamo utilizzare una funzione ovunque come AVG, COUNT, SUM, MIN, DATE e altre funzioni con la query SELECT in SQL.
- Ogni volta che viene chiamata una funzione, viene compilata.
- Le funzioni devono restituire un valore o un risultato.
- Le funzioni utilizzano solo parametri di input.
- Non possiamo usare le istruzioni TRY e CATCH nelle funzioni.
Tipi di funzioni
SQL Server classifica le funzioni in due tipi:
- Funzioni di sistema
- Funzioni definite dall'utente
Descriviamo entrambi i tipi in dettaglio.
Funzioni di sistema
Le funzioni definite dal sistema sono note come funzioni di sistema. In altre parole, tutto il funzioni integrate supportate dal server sono denominate funzioni di sistema. Le funzioni integrate ci fanno risparmiare tempo durante l'esecuzione dell'attività specifica. Questi tipi di funzioni in genere funzionano con l'istruzione SQL SELECT per calcolare valori e manipolare dati.
Ecco l'elenco di alcune funzioni di sistema utilizzate in SQL Server:
- Funzioni stringa (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funzioni di data e ora (datetime, datetime2, smalldatetime)
- Funzioni aggregate (COUNT, MAX, MIN, SUM, AVG)
- Funzioni matematiche (ABS, POTENZA, PI, EXP, LOG)
- Funzioni di classificazione (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
L'immagine seguente mostra tutte le funzioni di database integrate utilizzate in SQL Server:
Funzioni definite dall'utente
Funzioni che sono creato dall'utente nel database di sistema o in un database definito dall'utente sono note come funzioni definite dall'utente. Le funzioni UDF accettano parametri, eseguono azioni e restituiscono il risultato. Queste funzioni ci aiutano a semplificare il nostro sviluppo incapsulando logiche aziendali complesse e rendendole disponibili per il riutilizzo ovunque in base alle esigenze. Le funzioni definite dall'utente rendono molto più semplice scrivere il codice necessario per interrogare i dati. Migliorano inoltre la leggibilità e la funzionalità delle query, oltre a consentire ad altri utenti di replicare le stesse procedure.
SQL Server classifica le funzioni definite dall'utente principalmente in due tipi:
- Funzioni scalari
- Funzioni con valori di tabella
Ecco le descrizioni di queste funzioni UDF.
pronunciare un comando
Funzioni scalari
Funzione scalare in SQL Server sempre accetta parametri, singoli o multipli e restituisce un singolo valore . Le funzioni scalari sono utili nella semplificazione del nostro codice. Supponiamo di avere un calcolo complesso che appare in una serie di query. In tal caso, possiamo creare una funzione scalare che incapsula la formula e la utilizza in ogni query anziché in ogni query.
Di seguito è riportata la sintassi che illustra la creazione della funzione scalare in SQL Server:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
I parametri di sintassi di cui sopra sono descritti di seguito:
Per prima cosa definiremo il nome della funzione dopo il CREA FUNZIONE parole chiave. Il nome dello schema è facoltativo. Se non definiremo il nome dello schema, SQL Server utilizza lo schema predefinito dbo . Successivamente, definiremo l'elenco di parametri racchiuso tra parentesi. Terzo, scriveremo le istruzioni per la funzione e poi, nel file RITORNA istruzione, definire il tipo di dati del valore restituito. Infine, abbiamo aggiunto l'istruzione RETURN per restituire un valore all'interno del corpo della funzione.
Esempio
Questo esempio creerà una funzione per calcolare le vendite nette in base alla quantità, al prezzo e al valore dello sconto:
CREATE FUNCTION udfNet_Sales( @quantity INT, @price DEC(10,2), @discount DEC(3,2) ) RETURNS DEC(10,2) AS BEGIN RETURN @quantity * @price * (1 - @discount); END;
Ora possiamo utilizzare questa funzione per calcolare le vendite nette di qualsiasi ordine di vendita nella tabella definita.
Viene visualizzata l'immagine seguente in cui possiamo trovare le funzioni scalari:
Possiamo chiamare le funzioni scalari come la funzione incorporata in SQL Server. Ad esempio, possiamo chiamare la funzione udfNet_Sales di cui sopra come di seguito:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
L'esecuzione di questa funzione restituirà le vendite nette:
Anche SQL Server ci consente per modificare la funzione scalare utilizzando ALTER parola chiave. Ecco la sintassi per farlo:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Possiamo utilizzare l'istruzione seguente per rimuovere la funzione scalare dal database SQL Server:
DROP FUNCTION [schema_name.]function_name;
Funzioni con valori di tabella
Le funzioni con valori di tabella in SQL Server sono le funzione definita dall'utente che restituisce dati di tipo tabella. Poiché questa funzione è il tipo restituito è una tabella , possiamo usarlo nello stesso modo in cui usiamo una tabella.
ottenere la connessione
Possiamo classificare la funzione con valori di tabella in due tipi:
1. Funzioni di valori di tabella in linea
Questa funzione UDF restituisce una variabile di tabella in base all'azione eseguita dalla funzione. È necessario utilizzare una singola istruzione SELECT per determinare il valore della variabile di tabella.
Esempio
ordinamento dell'heap
L'esempio seguente creerà una funzione di valori di tabella e recupererà i dati della tabella dei dipendenti:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
In questa sintassi, il TABELLA RESI specifica che la funzione restituirà una tabella. Poiché non c'è INIZIO...FINE istruzione, interroga semplicemente i dati dalla tabella dei dipendenti. Inoltre, se non ha parametri, verrà chiamato direttamente.
Possiamo chiamare le funzioni con valori di tabella utilizzando il comando DA clausola del SELEZIONARE domanda. Per esempio , possiamo chiamare quanto sopra udf_GetEmployee funzionare come di seguito:
SELECT * FROM udf_GetEmployee();
L'esecuzione di questa funzione restituirà il risultato seguente:
SQL Server ci consente anche di modificare le funzioni con valori di tabella utilizzando la parola chiave ALTER invece della parola chiave CREATE. Il resto della sceneggiatura è la stessa.
Funzioni con valori di tabella a istruzioni multiple (MSTVF)
Questa funzione UDF restituisce una variabile di tabella in base all'azione eseguita dalla funzione. Può contenere istruzioni singole o multiple per produrre il risultato ed è anche una funzione che restituisce il risultato di più istruzioni in forma tabellare. È utile perché possiamo eseguire più istruzioni in questa funzione e ottenere risultati aggregati nella tabella restituita. Possiamo definire questa funzione utilizzando una variabile di tabella come valore restituito. All'interno della funzione, eseguiamo più query e inseriamo i dati in questa variabile di tabella.
L'esempio seguente crea un nome di funzione 'MULTIVALORE' che restituisce il ' @Dipendente' tavolo. Contiene tre campi denominati id, emp_name e stipendio dal file 'Dipendente' tabella utilizzando l'istruzione INSERT, quindi utilizza l'istruzione UPDATE per aggiornare il nome del dipendente.
CREATE FUNCTION MULTIVALUED() RETURNS @Employee TABLE (id INT, emp_name VARCHAR(50), salary INT) AS BEGIN INSERT INTO @Employee SELECT E.id, E.emp_name, E.salary FROM Employee E; UPDATE @Employee SET emp_name = 'Graeme Smith' WHERE id = 3; RETURN END
Possiamo chiamare le funzioni con valori di tabella a più istruzioni utilizzando la clausola FROM della query SELECT. Per esempio , possiamo chiamare la funzione di cui sopra come di seguito:
SELECT * FROM MULTIVALUED();
L'esecuzione di questa funzione restituirà il risultato seguente:
Quando devono essere utilizzate le funzioni con valori di tabella?
Le funzioni con valori di tabella vengono comunemente utilizzate come visualizzazioni con parametri. Le funzioni con valori di tabella sono più flessibili delle procedure memorizzate perché possono essere utilizzate ovunque vengano utilizzate le tabelle.
Conclusione
Questo articolo spiegherà una panoramica completa delle funzioni utilizzate in SQL Server. Qui abbiamo imparato principalmente due tipi di funzioni utilizzate in SQL Server: funzioni di sistema e funzioni definite dall'utente.