Les funcions a SQL Server són els objectes de base de dades que conté a conjunt de sentències SQL per realitzar una tasca específica . Una funció accepta paràmetres d'entrada, realitza accions i després retorna el resultat. Hem de tenir en compte que les funcions sempre retornen un únic valor o una taula. L'objectiu principal de les funcions és replicar fàcilment la tasca comuna. Podem crear funcions una vegada i les podem utilitzar en diverses ubicacions segons les nostres necessitats. SQL Server no permet l'ús de les funcions per inserir, suprimir o actualitzar registres a les taules de la base de dades.
A continuació es mostren les regles per crear funcions d'SQL Server:
- Una funció ha de tenir un nom i el nom no pot començar amb un caràcter especial com ara @, $, # o altres caràcters semblants.
- Les instruccions SELECT són les úniques que operen amb funcions.
- Podem utilitzar una funció a qualsevol lloc, com ara AVG, COUNT, SUM, MIN, DATE i altres funcions amb la consulta SELECT a SQL.
- Sempre que es crida una funció, es compila.
- Les funcions han de retornar un valor o un resultat.
- Les funcions només utilitzen paràmetres d'entrada.
- No podem utilitzar sentències TRY i CATCH a les funcions.
Tipus de Funcions
SQL Server classifica les funcions en dos tipus:
- Funcions del sistema
- Funcions definides per l'usuari
Descrivim ambdós tipus amb detall.
Funcions del sistema
Les funcions que es defineixen pel sistema es coneixen com a funcions del sistema. En altres paraules, tots els funcions integrades suportats pel servidor s'anomenen funcions del sistema. Les funcions integrades ens estalvien temps mentre realitzem la tasca específica. Aquest tipus de funcions normalment funcionen amb la instrucció SQL SELECT per calcular valors i manipular dades.
Aquesta és la llista d'algunes de les funcions del sistema utilitzades a SQL Server:
- Funcions de cadena (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funcions de data i hora (datetime, datetime2, smalldatetime)
- Funcions agregades (COUNT, MAX, MIN, SUM, AVG)
- Funcions matemàtiques (ABS, POWER, PI, EXP, LOG)
- Funcions de classificació (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
La imatge següent mostra totes les funcions de base de dades integrades utilitzades al servidor SQL:
Funcions definides per l'usuari
Funcions que són creat per l'usuari a la base de dades del sistema o una base de dades definida per l'usuari es coneixen com a funcions definides per l'usuari. Les funcions UDF accepten paràmetres, realitzen accions i retornen el resultat. Aquestes funcions ens ajuden a simplificar el nostre desenvolupament encapsulant una lògica de negoci complexa i fent-la disponible per a la seva reutilització a qualsevol lloc segons les necessitats. Les funcions definides per l'usuari fan que el codi necessari per consultar dades sigui molt més fàcil d'escriure. També milloren la llegibilitat i la funcionalitat de les consultes, així com permeten a altres usuaris replicar els mateixos procediments.
SQL Server classifica les funcions definides per l'usuari principalment en dos tipus:
- Funcions escalars
- Funcions amb valor de taula
Aquí teniu les descripcions d'aquestes funcions UDF.
Funcions escalars
Funció escalar a SQL Server sempre accepta paràmetres, únics o múltiples i retorna un únic valor . Les funcions escalars són útils en la simplificació del nostre codi. Suposem que podem tenir un càlcul complex que apareix en una sèrie de consultes. En aquest cas, podem construir una funció escalar que encapsuli la fórmula i l'utilitzi en cada consulta en lloc de fer-la servir a cada consulta.
La sintaxi següent il·lustra la creació de la funció escalar a SQL Server:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Els paràmetres de sintaxi anteriors es descriuen a continuació:
Primer definirem el nom de la funció després de FUNCIÓ DE CREAR paraules clau. El nom de l'esquema és opcional. Si no definirem el nom de l'esquema, SQL Server utilitza l'esquema predeterminat dbo . A continuació, definirem la llista de paràmetres tancat entre parèntesis. En tercer lloc, escriurem les declaracions per a la funció i després, a la DEVOLUCIONS declaració, definiu el tipus de dades del valor de retorn. Finalment, hem afegit la instrucció RETURN per retornar un valor dins del cos de la funció.
Exemple
Aquest exemple crearà una funció per calcular les vendes netes en funció de la quantitat, el preu i el valor de descompte:
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;
Ara, podem utilitzar aquesta funció per calcular les vendes netes de qualsevol comanda de venda a la taula definida.
La següent imatge mostra on podem trobar les funcions escalars:
Podem anomenar les funcions escalars el mateix que la funció integrada a SQL Server. Per exemple, podem anomenar la funció anterior udfNet_Sales de la següent manera:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
L'execució d'aquesta funció retornarà les vendes netes:
SQL Server també ens ho permet per modificar la funció escalar utilitzant el ALTER paraula clau. Aquí teniu la sintaxi per fer-ho:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Podem utilitzar la instrucció següent per eliminar la funció escalar de la base de dades SQL Server:
DROP FUNCTION [schema_name.]function_name;
Funcions amb valor de taula
Les funcions amb valors de taula a SQL Server són les funció definida per l'usuari que retorna dades d'un tipus de taula. Com que aquesta funció és el tipus de retorn és una taula , el podem utilitzar de la mateixa manera que fem servir una taula.
Podem classificar la funció amb valors de taula en dos tipus:
1. Funcions de taula-valors en línia
Aquesta funció UDF retorna una variable de taula basada en l'acció realitzada per la funció. S'ha d'utilitzar una sola instrucció SELECT per determinar el valor de la variable de taula.
Exemple
L'exemple següent crearà una funció de valors de taula i recuperarà les dades de la taula dels empleats:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
En aquesta sintaxi, el TAULA DE DEVOLUCIONS especifica que la funció retornarà una taula. Com que no n'hi ha COMENÇAR... FINALITZAR declaració, simplement consulta dades de la taula dels empleats. A més, si no té cap paràmetre, s'anomenarà directament.
Podem cridar les funcions amb valors de taula utilitzant el DE clàusula de la SELECCIONA consulta. Per exemple , podem anomenar l'anterior udf_GetEmployee funcionen de la següent manera:
SELECT * FROM udf_GetEmployee();
L'execució d'aquesta funció retornarà el resultat següent:
SQL Server també ens permet modificar les funcions amb valors de taula utilitzant la paraula clau ALTER en comptes de la paraula clau CREATE. La resta del guió és el mateix.
Funcions amb valors de taula de diverses declaracions (MSTVF)
Aquesta funció UDF retorna una variable de taula en funció de l'acció realitzada per la funció. Pot contenir declaracions simples o múltiples per produir el resultat, i també és una funció que retorna el resultat de diverses sentències en forma tabular. És útil perquè podem executar diverses declaracions en aquesta funció i obtenir resultats agregats a la taula retornada. Podem definir aquesta funció utilitzant una variable de taula com a valor de retorn. Dins de la funció, executem diverses consultes i inserim dades en aquesta variable de taula.
L'exemple següent crea un nom de funció 'MULTIVALUIDA' que retorna el ' @Empleat' taula. Conté tres camps anomenats id, emp_name i salari del 'Empleat' taula utilitzant la instrucció INSERT i després utilitza la instrucció UPDATE per actualitzar el nom de l'empleat.
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
Podem cridar a les funcions amb valors de taula multi-instruccions utilitzant la clàusula FROM de la consulta SELECT. Per exemple , podem anomenar la funció anterior de la següent manera:
cadena java a enter
SELECT * FROM MULTIVALUED();
L'execució d'aquesta funció retornarà el resultat següent:
Quan s'han d'utilitzar les funcions amb valors de taula?
Les funcions amb valors de taula s'utilitzen habitualment com a vistes parametritzades. Les funcions amb valors de taula són més flexibles que els procediments emmagatzemats perquè es poden utilitzar a qualsevol lloc on s'utilitzin les taules.
Conclusió
En aquest article s'explicarà una visió general completa de les funcions utilitzades a SQL Server. Aquí hem après principalment dos tipus de funcions que s'utilitzen a SQL Server: funcions del sistema i definides per l'usuari.