Aujourd'hui, j'ai examiné la clause de sortie dans SQL Server 2005 afin que vous puissiez renvoyer des données aux variables de table à partir d'instructions de modification (INSERT, UPDATE, DELETE). Les scénarios utiles pour DML avec résultats incluent la purge et l'archivage, les applications de messagerie et d'autres scénarios. La syntaxe de cette nouvelle clause OUTPUT est :
OUTPUT <dml_select_list> INTO @table_variable
peut accéder à l'ancienne/nouvelle image de la ligne modifiée en référençant la table insérée et la table supprimée, de la même manière que pour accéder aux déclencheurs. Dans une instruction INSERT, seule la table insérée est accessible. Dans une instruction DELETE, seules les tables supprimées sont accessibles. Dans l'instruction UPDATE, vous pouvez accéder aux tables insérées et aux tables supprimées. Voici un exemple tiré de MSDN :
À titre d'exemple de schéma de purge et d'archivage pouvant être utile pour le DML avec des résultats, supposons que vous disposez d'une grande table Commandes et que vous souhaitez purger régulièrement les données historiques. Vous souhaitez également copier les données purgées dans une table d'archive nommée OrdersArchive. Vous déclarez une variable de table nommée @DeletedOrders et entrez une boucle dans laquelle vous supprimez les données historiques par morceaux (par exemple, les commandes antérieures à 2003). Le code ajouté ici est la clause OUTPUT, qui copie tous les attributs de toutes les lignes supprimées dans la variable de table @DeletedOrders, puis utilise l'instruction INSERT INTO pour copier toutes les lignes de la variable de table dans la table OrdersArchive :
DECLARE @DeletedOrders TABLE
(
id de commande INT,
date de commande DATETIME,
empidINT,
gardien VARCHAR(5),
qtéINT
)
PENDANT 1=1
COMMENCER
COMMENCER LA TRANSMISSION
SUPPRIMER TOP (5000) DES commandes
SORTIE supprimée.* INTO @DeletedOrders
OÙ date de commande < '20030101'
INSÉRER DANS OrdersArchive
SELECT * FROM @DeletedOrders
COMMIT TRAN
SUPPRIMER DE @DeletedOrders
SI @@rowcount < 5000
CASSER
FIN
Un autre exemple est le suivant S'il existe une table comme suit : Créer une table Employé (EmpID int Identity (1, 1) contrainte Employee_pk clé primaire, FirstName varchar (100), LastName Varchar (100), Sex char (1), Salutation comme cas lorsque Sexe = 'M' puis 'M.' lorsque Sexe = 'F' puis 'Mme' sinon '' fin, Salaire Argent, Déductions comme cas Quand Salaire <3000 puis 0,20 * Salaire quand Salaire entre 30000 et 50000 puis 0,26* Salaire lorsque Salaire entre 50001 et 75000 puis 0,30*Salaire sinon 0,35*Fin de salaire, contrainte datetime DOJ DOJ_DEF par défaut getdate())
} Un tableau très simple, dans lequel les clauses de cas sont utilisées pour définir respectivement la salutation, les déductions, le doj, etc. Et nous pouvons créer une autre procédure stockée, comme suit : Créer une procédure employe_insert@Fname varchar(100),@Lname varchar(100),@sex char(1),@Salary Moneyasdeclare @empoutput table (EmpID int, FirstName varchar(100) , LastName Varchar (100), Sex char (1), Salutation char (3), Salary Money, Deductions Money, DOJ datetime) insérer dans Employee (FirstName, LastName, Salary, sex) OUTPUT inséré.* dans @empoutputvalues (@Fname ,@Lname ,@Salary,@sex )Sélectionnez * dans @empoutput
Ensuite, lors de l'appel de la procédure stockée, vous pouvez utiliser USE [MyDatabase] comme ceci
GOexec employe_insert 'Miho','Yoshikawa','F',146000
De cette façon, les données seront insérées dans la table des employés lors de l'exécution, et une variable temporaire empoutput sera définie. Notez que cela peut être sous la forme d'une table. Défini dans le formulaire, il est facile à voir ici, et les données insérées dans la table des employés seront sorties vers la variable empoutput en même temps. Après avoir exécuté cette procédure stockée, nous trouverons les résultats suivants.
EmpID, Prénom, Nom, Sexe, Salutation, Salaire, Déductions, DOJ
7,Miho,Yoshikawa,F,Mme,146000.00,51100.00,2006-04-09 23:16:44.920
, et les enregistrements sont bien insérés dans la table des employés
Vous pouvez voir
la source : jackyrong BLOG