Enregistrements et Fonctions

Afin d'impliquer une fonction avec votre entrée de données, vous devez avoir une identité unique. Vous pouvez utiliser l'une des fonctions intégrées de Transact-SQL. Vous pouvez vérifier l'une des fonctions que nous avons examinées dans la Leçon 8. Normalement, le meilleur moyen est de vérifier la documentation en ligne pour savoir si l'affectation à effectuer est déjà créée. L'utilisation d'une fonction intégrée peut vous épargner la difficulté d'obtenir une fonction. Par exemple, imaginez que vous disposez d'une base de données nommée AutoRepairShop et imaginez qu'elle a une table utilisée pour créer des commandes de réparation des clients :

CREATE TABLE RepairOrders 
(
    RepairID int Identity(1,1) NOT NULL, 
    CustomerName varchar(50), 
    CustomerPhone varchar(20), 
    RepairDate datetime2 
); 
GO

Lors de l'exécution de l'entrée de données pour cette table, vous pouvez laisser l'utilisateur saisir le nom et le numéro de téléphone du client. D'autre part, vous pouvez aider l'utilisateur en entrant par programmation la date actuelle. Pour le faire, vous devez appeler la fonction GETDATE(). Voici des exemples :

INSERT INTO RepairOrders (CustomerName, CustomerPhone, RepairDate) 
            VALUES(N'Annette Berceau', N'301-988-4615', GETDATE()) ; 
GO 
INSERT INTO RepairOrders (CustomerPhone, CustomerName, RepairDate) 
            VALUES(N'(240) 601-3795 ', N'Paulino Santiago', GETDATE()); 
GO 
INSERT INTO RepairOrders (CustomerNomae, RepairDate, CustomerPhone) 
            VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095') ; 
GO 
INSERT INTO RepairOrders (RepairDate, CustomerPhone, CustomerName) 
            VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen') ; 
GO

Vous pouvez également impliquer la fonction dans une opération, puis utiliser le résultat comme la valeur à attribuer à un champ. Vous pouvez également appeler une fonction qui prend un ou plusieurs arguments. Assurez-vous que vous respectez les règles de transmission d'un argument à une fonction lors de son appel.

Toutes les fonctions que nous avons créées jusqu'ici sont renvoyées 0 ou une valeur unique qui a été l'un des types de données prises en charge par Transact-SQL. Une fonction table est une fonction qui retourne à une table. Une fonction table multi-instructions est une fonction qui crée une table, éventuellement la remplit avec les enregistrements souhaités et renvoie ensuite le tableau.

Pour créer une fonction qui retourne à une table, vous disposez de différentes options. Dans l'Explorateur d'objets, développez la base de données qui sera propre à la fonction, développez le nœud de programmabilité et étendez les fonctions. Cliquez avec le bouton droit de la souris sur les fonctions de Table-Valued, puis cliquez sur New Multi-Statement Table-Valued fonctions...

--================================================
--Template generated from Template Explorer using : 
--Create Multi-Statement Function (New Menu).SQL
--
--Use the Specify Values for Template Parameters
--command (Ctrl-Shift-M) to fill in the parameter
--values below.
--
--This block of comments will not be included in
--the definition of the function.
--================================================ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO
--=============================================
--Author : <Author,,Name>
--Create date: < Create Date,, 
>--Description : <Description,,>
--============================================= 
CREATE FUNCTION < Table_Function_Name, sysname, FunctionName > 
(
        --Add the paremeters for the function here
        <@param1, sysname, @p1> <data_type_for_param1, int >, 
        <@ param2, sysname, @p2 > <data_type_for_param2, char>
) 
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
        (--Add the column definitions for the TABLE variable here 
        <Column_1, sysname, c1> <Data_Type_For_Column1, int>, 
        <Column_2, sysname, c2> <Data_Type_For_Column2, int>
) 
AS 
BEGIN
        --Fill the table variable with the rows for your result set
        RETURN 
END 
GO

Comme alternative, ouvrez une nouvelle fenêtre de requête. Dans l'Explorateur de modèles, développez la fonction, faites glisser Create Multi-Statement Function (nouveau menu) :

--================================================
--Template generated from Template Explorer using : 
--Create Multi-Statement Function (New Menu).SQL
--
--Use the Specify Values for Template Parameters
--command  (Ctrl-Shift-M) to fill in the parameter
--values below.
--
--This block of comments will not be included in
--the definition of the function.
--================================================ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO
--=============================================
--Author : <Author,,Name>
--Create date : < Create Date,, >
--Description : <Description,,>
--============================================= 
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
        --Add the parameters for the function here 
        <@param1, sysname, @p1> <data_type_for_param1, int>, 
        <@param2, sysname, @p2> <data_type_for_param2, char>
) 
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
        --Add the column definitions for the TABLE variable here 
        <Column_1, sysname, c1> <Data_Type_For_Column1, int>, 
        <Column_2, sysname, c2> <Data_Type_For_Column2, int>
) 
AS 
BEGIN
        --Fill the table variable with the rows for your result set 
        RETURN 
END 
GO

La formule primaire pour créer une fonction de table multi-instruction est la suivante :

CREATE FUNCTION FunctionName () 
RETURNS @ TableName TABLE (Table's Columns) 
AS 
BEGIN 
    RETURN ; 
END ;

Vous commencez avec l'expression CREATE FUNCTION suivie d'un nom pour la fonction et ses parenthèses. Comme dans la leçon 7, après les parenthèses, tapez le mot-clé RETURNS. Étant donné que vous créez en fait une table, suivez le mot-clé RETURNS avec l'opérateur @, suivi du nom souhaité de la table, suivi par le mot clé TABLE et ses parenthèses.

Créez les colonnes de la table dans les parenthèses. Chaque colonne est créée à l'aide de la formule :

ColumnName DataType Options

Chaque colonne suit les options nécessaires : nom, type de données, clé primaire facultative, nullité facultative, cocher unique, facultatif ou expression facultative.

Après les parenthèses qui contiennent les colonnes, tapez AS, BEGIN, RETURN et END, de préférence sur différentes lignes pour une meilleure lisibilité. Voici un exemple qui crée une fonction interne qui crée une table :

USE exercice ; 
GO 
 
 
CREATE FUNCTION GetStates() 
RETURNS @States TABLE 
(
    ShortName nchar nchar(2), 
    LongName nvarchar(50)
) 
AS 
BEGIN 
    RETURN ; 
END ; 
GO

Lors de la création de la fonction, si vous souhaitez ajouter des enregistrements à la table, avant le mot clé RETURN, utilisez les instructions INSERT ou INSERT INTO comme nécessaire. Voici un exemple :

USE Exercise ; 
GO 
 
CREATE FUNCTION GetStates() 
RETURNS @States TABLE 
(
     ShortName nchar(2), 
     LongName nvarchar(50)
) 
AS 
BEGIN 
    INSERT INTO @States VALUES(N'DC', N'District of Columbia'), 
                              (N'MD ', N'Maryland'), 
                              (N'PA ', N'Pennsylvania'), 
                              (N'VA ', N'Virginia'), 
                              (N'WV ', N'West Virginia') 
RETURN ; 
END ; 
GO

Après avoir créé une fonction table multi-instruction, elle est représentée dans le nœud de fonctions Table-Valued de l'Explorateur d'objets. Pour appeler la fonction, utilisez une instruction SELECT. Pour indiquer que vous souhaitez tous les enregistrements de la table, vous pouvez utiliser l'opérateur *. Voici un exemple :

USE Exercise ; 
GO 
 
SELECT * FROM GetStates() ; 
GO 

Cela donnerait :

Une fonction table multi-instruction est un type de table. Elle rend simplement facile d'accéder aux enregistrements d'une table avec un seul appel. Étant donné qu'il s'agit d'un type de table, vous pouvez accéder à un, à certains ou à tous ses champs. Pour spécifier les champs que vous souhaitez obtenir, créez une instruction SELECT qui contient la liste des colonnes. Voici un exemple :

USE exercise ; 
GO 
SELECT LongName FROM GetStates() ; 
GO

Cela donnerait :

Bien entendu vous pouvez également définir des conditions que l'instruction SELECT doit suivre. De la même manière, vous pouvez inclure une des instructions que nous avons déjà vues, ou que nous verrons dans d'autres leçons, dans la création ou l'appel d'une fonction de table en ligne.

Dans sa formule, nous avons vu qu'une fonction table avait des parenthèses. Entre les parenthèses, vous pouvez spécifier un ou plusieurs arguments. Chaque argument est sous la forme de @, suivie d'un nom et un type de données. Voici un exemple :

USE Exercise ; 
GO 
 
 
CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40)) 
RETURNS @States TABLE 
(
    RegionName nvarchar(40), 
    ShortName nchar(2), 
    LongName nvarchar(50)
) 
AS 
BEGIN 
   INSERT INTO @States VALUES(N'New England', N'CT', N'Connecticut'), 
                             (N'New England ', N'ME', N'Maine '), 
                             (N'South Atlantic', N'DC', N'District of Columbia'), 
                             (N'South Atlantic', N'MD', N'Maryland '), 
                             (N'South Atlantic', N'PA', N'Pennsylvania'), 
                             (N'South Atlantic', N'VA', N'Virginia'), 
                             (N'South Atlantic', N'WV',  N'West Virginia') 
   RETURN ; 
END ; 
GO

Lors de l'appel de la fonction, vous devez fournir une valeur pour l'argument. Voici un exemple :

USE Exercise 
GO 
 
 
DECLARE @Region nvarchar(40) ; 
SET @Region = N'South Atlantic '; 
SELECT ShortName, LongName, RegionName FROM GetStates(@Region) ; 
GO

L'argument de la fonction est créée à l'aide de n'importe quelle règle dont vous connaissez les arguments. Par exemple, il peut être créé avec une valeur par défaut. Voici un exemple :

USE Exercise;  
GO    
CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40) = N'All')  
RETURNS @States TABLE  
(      
    RegionName nvarchar(40),      
    ShortName nchar(2),      
    LongName nvarchar(50)  
)  
AS  
BEGIN      
    INSERT INTO @States VALUES  
        (N'New England', N'CT', N'Connecticut'), (N'New England', N'ME', N'Maine'),  
        (N'New England', N'MA', N'Massachusetts'), (N'New England', N'NH', N'New Hampshire'), 
        (N'New England', N'RI', N'Rhode Island'), (N'New England', N'VT', N'Vermont'),  
        (N'Mid-Atlantic', N'NJ', N'New Jersey'), (N'Mid-Atlantic', N'NY', N'New York'),  
        (N'Mid-Atlantic', N'PA', N'Pennsylvania'), (N'South Atlantic', N'DE', N'Delaware'),  
        (N'South Atlantic', N'DC', N'District of Columbia'),  
        (N'South Atlantic', N'FL', N'Florida'),	(N'South Atlantic', N'GA', N'Georgia'),  	
        (N'South Atlantic', N'MD', N'Maryland'),  
        (N'South Atlantic', N'PA', N'Pennsylvania'), (N'South Atlantic', N'VA', N'Virginia'),  	
        (N'South Atlantic', N'WV', N'West Virginia'),  
        (N'All', N'AL', N'Alabama'), (N'All', N'AK', N'Alaska'), (N'All', N'AZ', N'Arizona'), 
        (N'All', N'AR', N'Arkansas'), (N'All', N'CA', N'California'),  
        ('All', N'CO', N'Colorado'), (N'All', N'CT', N'Connecticut'),   
        (N'All', N'DE', N'Delaware'), (N'All', N'DC', N'District of Columbia'), 
        (N'All', N'FL', N'Florida'), (N'All', N'GA', N'Georgia'), (N'All', N'HI', N'Hawaii'),  
        (N'All', N'ID', N'Idaho'), (N'All', N'IL', N'Illinois'), (N'All', N'IN', N'Indiana'),  
        (N'All', N'IA', N'Iowa'), (N'All', N'KS', N'Kansas'), (N'All', N'KY', N'Kentucky'),  
        (N'All', N'LA', N'Louisiana'), (N'All', N'ME', N'Maine'), (N'All', N'MD', N'Maryland'), 
        (N'All', N'MA', N'Massachusetts'), (N'All', N'MI', N'Michigan'),  
        (N'All', N'MN', N'Minnesota'), (N'All', N'MS', N'Mississippi'),  
        (N'All', N'MO', N'Missouri'), (N'All', N'MT', N'Montana'), (N'All', N'NE', N'Nebraska'),  
        (N'All', N'NV', N'Nevada'), (N'All', N'NH', N'New Hampshire'),  
        (N'All', N'NJ', N'New Jersey'),(N'All', N'NM', N'New Mexico'),  
        (N'All', N'NY', N'New York'), (N'All', N'NC', N'North Carolina'), 
        (N'All', N'ND', N'North Dakota'), (N'All', N'OH', N'Ohio'), 
        (N'All', N'OK', N'Oklahoma'), (N'All', N'OR', N'Oregon'),  
        (N'All', N'PA', N'Pennsylvania'), (N'All', N'RI', N'Rhode Island'), 
        (N'All', N'SC', N'South Carolina'), (N'All', N'SD', N'South Dakota'),  	
        (N'All', N'TN', N'Tennessee'), (N'All', N'TX', N'Texas'), (N'All', N'UT', N'Utah'),  
        (N'All', N'VT', N'Vermont'), (N'All', N'VA', N'Virginia'),  
        (N'All', N'WA', N'Washington'),	(N'All', N'WV', N'West Virginia'),  	
        (N'All', N'WI', N'Wisconsin'), (N'All', N'WY', N'Wyoming');     
    RETURN;  
END;  
GO

Lors de l'appel de la fonction, vous pouvez omettre la valeur de l'argument et tapez DEFAULT dans son emplacement réservé :

USE Exercise ; 
GO 
 
SELECT RegionName, ShortName, LongName FROM GetStates (DEFAULT); 
GO

Dans le cas contraire, vous pouvez appeler la fonction et transmettre une valeur de votre choix.

Lorsque vous appelez une fonction table multi-instruction, vous pouvez utiliser une condition pour restreindre la liste de documents qu'elle produit. Par exemple, vous pouvez utiliser une condition WHERE afin de contrôler le résultat. Voici un exemple :

USE Exercise 
GO 
 
 
DECLARE @Region nvarchar(40) ; 
SET @Region = N'South Atlantic'; 
SELECT LongName, ShortName FROM GetStates(@Region) 
WHERE RegionName = @Region ; 
GO

Cela donnerait :

De la même façon, vous pouvez utiliser CASE, LIKE, BETWEEN, etc. afin d'affiner le résultat.

Examinez la table Employees suivante et ses enregistrements :

USE Exercise ; 
Go 
 
 
CREATE TABLE Employees 
    EmployeeNumber nchar(10), 
    EmployeeName nvarchar(50), 
    DateHired date, 
    HourlySalary money
); 
GO 
 
 
BEGIN TRANSACTION AddEmployees 
 
 
INSERT INTO Employees 
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15), 
      (N '720947 ', N'Paul Handsome', '20000802', 36.05) 
GO 
INSERT INTO Employees(EmployeeNumber, EmployeeName, HourlySalary) 
VALUES(N'247903', N'Gina Palau', 18.85) 
GO 
INSERT INTO Employees 
VALUES(N'595002', N'John Meah', N'20000212', 32.25) 
GO 
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired) 
VALUES(N'Clarice Simms', N'971403', N'20011112') ; 
GO 
INSERT INTO Employees 
VALUES(N'928375', N'Chuck Stansil', N'20080628', 20.18), 
      (N'792764 ', Perez N'Orlando ', N ' 20000616 ', 12,95); 
GO 
 
 
COMMIT TRANSACTION AddEmployees ; 
GO

Lorsque vous souhaitez récupérer les documents détenus par ce tableau, vous pouvez écrire une instruction SELECT de très nombreuses sections. Imaginez que vous souhaitez obtenir les enregistrements dans de nombreux cas, encore et encore. Au lieu de créer une nouvelle instruction SELECT chaque fois, vous pouvez créer une fonction qui vous permet d'accéder aux enregistrements.

Une fonction de table en ligne est une fonction qui génère les enregistrements d'une fonction à l'aide d'une instruction SELECT. Par opposition à une fonction de table en ligne, vous ne pouvez pas inclure (directement) d'une instruction SELECT dans une fonction table multi-instruction. La solution consiste à créer la fonction Iinline.

Pour créer une fonction de table en ligne, vous disposez de différentes options. Pour avoir le code généré pour vous :

• Dans l'Explorateur d'objets, développez la base de données qui sera propriétaire de la fonction. Développez Programmability. Développez les fonctions. Cliquez avec le bouton droit de la souris sur les fonctions de Table-Valued, puis cliquez sur New Inline Table-Valued Functions...
  

  --================================================
  --Template generated from Template Explorer using : 
  --Create inline Function (New Menu).SQL)
  --
  --Use the Specify Values for Template Parameters
  --command (Ctrl-Shift-M) to fill in the parameter
  --values below.
  --
  --This block of comments will not be included in
  --the definition of the function.
  --================================================ 
  SET ANSI_NULLS ON 
  GO 
  SET QUOTED_IDENTIFIER ON 
  GO
  --=============================================
  --Author : <Author,,Name>
  --Create Date: < Create Date,,>
  --Description : <Description,,>
  --============================================= 
  CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
  (
         --Add the parameters for the function here
         <@param1, sysname, @p1> <Data_Type_For_Param1, int >, 
         <@ param2, sysname, @p2> <Data_Type_For_Param2, char>
  ) 
  RETURNS TABLE 
  AS 
  RETURN 
  (
        --Add the SELECT statement with parameter references here 
        SELECT 0 
  )
  GO

• Dans l'Explorateur de modèle, développez le nœud de fonction. Cliquez avec le bouton droit de la souris sur Create Inline Function (nouveau menu) et cliquez sur Ouvrir ou Modifier
• Ouvrez une fenêtre de requête vide. Dans l'Explorateur de modèle, développez la fonction de nœud. Faites glisser Create Inline Function (nouveau menu) et laissez-le tomber dans la fenêtre de requête

Une fois que le code squelette a été généré pour vous, vous pouvez modifier et personnaliser.

La formule de base pour créer une fonction en ligne est la suivante :

CREATE FUNCTION FunctionName (Argument(s)) 
RETURNS TABLE 
AS 
RETURN 
    SELECT 0

Vous commencez avec l'expression CREATE FUNCTION suivie d'un nom pour la fonction et ses arguments. Si la fonction prend un ou plusieurs arguments, entrez-la (les) dans les parenthèses. S'il n'y a aucun argument, laissez les parenthèses vides,

La parenthèse fermante est suivie de l'expression RETURNS TABLE. Le mot clé TABLE indique que la fonction retourne une liste de colonnes et de leurs dossiers. Ceci est suivi de AS et le mot-clé RETURN.

Après le mot clé RETURN, si nécessaire, déclarez des variables et faites ce que vous voulez dans le corps de la fonction. Avant la fin de la fonction, vous devez créer une instruction SELECT qui renvoie un type de table.

Voici un exemple :

USE Exercise ; 
GO 
 
CREATE FUNCTION GetEmployees() 
RETURNS TABLE 
AS 
RETURN
SELECT * FROM Employees ; 
GO

Après la création d'une fonction de table en ligne, vous pouvez accéder à ses dossiers. Pour appeler une telle fonction, utilisez une instruction SELECT. Par exemple, pour indiquer que vous souhaitez tous les enregistrements de la table, vous pouvez utiliser l'opérateur *. Voici un exemple :

USE Exercise; 
GO 
 
SELECT * FROM GetEmployees() ; 
GO 

Cela donnerait :

Parce qu'une fonction de table en ligne est un type de tableau, vous pouvez accéder à un, certains ou tous ses champs à l'aide d'une instruction SELECT. Voici un exemple :

SELECT EmployeeName, HourlySalary FROM GetEmployees() ;

Bien entendu vous pouvez également définir les conditions que doit suivre l'instruction SELECT. Voici un exemple :

SELECT EmployeeNumber, EmployeeName FROM GetEmployees()
WHERE HourlySalary > = 20,00 ;

De la même manière, vous pouvez inclure une des instructions que nous avons déjà vues, ou que nous verrons dans d'autres leçons, dans la création ou l'appel d'une fonction de table en ligne.