Question Fonction ou procédure stockée dans SQL Server


J'ai appris des fonctions et des procédures stockées pendant un certain temps mais je ne sais pas pourquoi et quand je devrais employer une fonction ou une procédure stockée. Ils me paraissent identiques, peut-être parce que je suis un peu novice à ce sujet.

Quelqu'un peut-il me dire pourquoi?


655
2017-07-24 19:40


origine


Réponses:


Les fonctions sont des valeurs calculées et ne peuvent pas effectuer de modifications environnementales permanentes sur SQL Server (c'est-à-dire, aucune instruction INSERT ou UPDATE n'est autorisée).

Une fonction peut être utilisée en ligne dans des instructions SQL si elle renvoie une valeur scalaire, ou peut être jointe si elle renvoie un ensemble de résultats.

Un point à noter des commentaires, qui résument la réponse. Merci à @Sean K Anderson:

Les fonctions suivent la définition de l'informatique et de la science en ce qu'elles DOIVENT renvoyer une valeur et ne peuvent pas modifier les données qu'elles reçoivent en tant que paramètres   (les arguments). Les fonctions ne sont autorisées à rien changer, doivent   avoir au moins un paramètre, et ils doivent retourner une valeur. Stocké   les procs ne doivent pas avoir de paramètre, peuvent changer les objets de base de données,   et ne pas avoir à retourner une valeur.


552
2017-07-24 19:42



La différence entre SP et UDF est la suivante:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

464
2017-10-15 17:00



Les fonctions et les procédures stockées servent des objectifs distincts. Bien que ce ne soit pas la meilleure analogie, les fonctions peuvent être vues littéralement comme n'importe quelle autre fonction que vous utiliseriez dans n'importe quel langage de programmation, mais les procs stockés ressemblent plutôt à des programmes individuels ou à un script batch.

Les fonctions ont normalement une sortie et éventuellement des entrées. La sortie peut ensuite être utilisée comme entrée pour une autre fonction (un serveur SQL intégré tel que DATEDIFF, LEN, etc.) ou comme prédicat pour une requête SQL - par exemple, SELECT a, b, dbo.MyFunction(c) FROM table ou SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Les processus stockés sont utilisés pour lier des requêtes SQL dans une transaction et interagir avec le monde extérieur. Les cadres tels que ADO.NET, etc. ne peuvent pas appeler une fonction directement, mais ils peuvent appeler un proc stocké directement.

Cependant, les fonctions ont un danger caché: elles peuvent être mal utilisées et provoquer des problèmes de performances plutôt désagréables: considérez cette requête:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Où MyFunction est déclaré comme:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Ce qui se passe ici, c'est que la fonction MyFunction est appelée pour chaque ligne de la table MyTable. Si MyTable a 1000 lignes, cela représente 1000 autres requêtes ad-hoc sur la base de données. De même, si la fonction est appelée lorsqu'elle est spécifiée dans la spécification de la colonne, la fonction sera appelée pour chaque ligne renvoyée par le SELECT.

Donc, vous devez être prudent en écrivant des fonctions. Si vous faites SELECT à partir d'une table dans une fonction, vous devez vous demander si cela peut être mieux effectué avec un JOIN dans le parent stocké proc ou une autre construction SQL (comme CASE ... WHEN ... ELSE ... FIN).


180
2017-07-24 20:06



Ecrivez une fonction définie par l'utilisateur lorsque vous souhaitez calculer et renvoyer une valeur à utiliser dans d'autres instructions SQL; écrire une procédure stockée quand vous voulez à la place est de grouper un ensemble potentiellement complexe d'instructions SQL. Ce sont deux cas d'utilisation assez différents, après tout!


52
2017-07-24 19:42



Différences entre les procédures stockées et les fonctions définies par l'utilisateur:

  • Les procédures stockées ne peuvent pas être utilisées dans les instructions Select.
  • Les procédures stockées prennent en charge la résolution de noms différée.
  • Les procédures stockées sont généralement utilisées pour exécuter une logique métier.
  • Les procédures stockées peuvent renvoyer n'importe quel type de données.
  • Les procédures stockées peuvent accepter un plus grand nombre de paramètres d'entrée que les fonctions définies par l'utilisateur. Les procédures stockées peuvent avoir jusqu'à 21 000 paramètres d'entrée.
  • Les procédures stockées peuvent exécuter Dynamic SQL.
  • Les procédures stockées prennent en charge la gestion des erreurs.
  • Des fonctions non déterministes peuvent être utilisées dans des procédures stockées.

  • Les fonctions définies par l'utilisateur peuvent être utilisées dans les instructions Select.
  • Les fonctions définies par l'utilisateur ne prennent pas en charge la résolution de nom différée.
  • Les fonctions définies par l'utilisateur sont généralement utilisées pour les calculs.
  • Les fonctions définies par l'utilisateur doivent renvoyer une valeur.
  • Les fonctions définies par l'utilisateur ne peuvent pas renvoyer d'images.
  • Les fonctions définies par l'utilisateur acceptent un plus petit nombre de paramètres d'entrée que les procédures stockées. Les fonctions UDF peuvent avoir jusqu'à 1 023 paramètres d'entrée.
  • Les tables temporaires ne peuvent pas être utilisées dans des fonctions définies par l'utilisateur.
  • Les fonctions définies par l'utilisateur ne peuvent pas exécuter Dynamic SQL.
  • Les fonctions définies par l'utilisateur ne prennent pas en charge la gestion des erreurs. RAISEERROR OU @@ERROR ne sont pas autorisés dans les FDU.
  • Les fonctions non déterministes ne peuvent pas être utilisées dans les fonctions définies par l'utilisateur. Par exemple, GETDATE() ne peut pas être utilisé dans les fonctions UDF.

51
2018-05-02 09:52



Différence de base

La fonction doit renvoyer une valeur, mais dans la procédure stockée, elle est facultative (la procédure peut renvoyer des valeurs nulles ou n).

Les fonctions ne peuvent avoir que des paramètres d'entrée alors que les procédures peuvent avoir des paramètres d'entrée / sortie.

La fonction prend un paramètre d'entrée, elle est obligatoire mais la procédure stockée peut prendre jusqu'à n paramètres d'entrée.

Les fonctions peuvent être appelées depuis la procédure alors que les procédures ne peuvent pas être appelées depuis la fonction.

Différence anticipée

La procédure autorise l'instruction SELECT ainsi que l'instruction DML (INSERT / UPDATE / DELETE) alors que Function n'autorise que l'instruction SELECT.

Les procédures ne peuvent pas être utilisées dans une instruction SELECT alors que Function peut être intégrée dans une instruction SELECT.

Les procédures stockées ne peuvent pas être utilisées dans les instructions SQL n'importe où dans la section WHERE / HAVING / SELECT alors que Function peut l'être.

Les fonctions qui renvoient des tables peuvent être traitées comme un autre ensemble de lignes. Cela peut être utilisé dans JOINs avec d'autres tables.

La fonction Inline peut être composée de vues qui prennent des paramètres et peuvent être utilisées dans JOINs et d'autres opérations de Rowset.

L'exception peut être gérée par un bloc try-catch dans une procédure alors que le bloc try-catch ne peut pas être utilisé dans une fonction.

Nous pouvons opter pour la gestion des transactions en procédure alors que nous ne pouvons pas aller dans la fonction.

la source


19
2018-02-28 10:55



Une fonction définie par l'utilisateur est un outil important disponible pour un programmeur de serveur SQL. Vous pouvez l'utiliser en ligne dans une instruction SQL comme si

SELECT a, lookupValue(b), c FROM customers 

lookupValue sera un UDF. Ce type de fonctionnalité n'est pas possible lors de l'utilisation d'une procédure stockée. En même temps, vous ne pouvez pas faire certaines choses à l'intérieur d'un UDF. La chose de base à retenir ici est que UDF:

  • ne peut pas créer des changements permanents
  • ne peut pas changer les données

une procédure stockée peut faire ces choses.

Pour moi, l'utilisation en ligne d'un fichier UDF est l'utilisation la plus importante d'un fichier UDF.


18
2017-07-24 20:04



Procédures stockées  sont utilisés comme scripts. Ils exécutent une série de commandes pour vous et vous pouvez les programmer pour s'exécuter à certains moments.

Les fonctions  sont utilisés comme méthodes. Vous lui passez quelque chose et il renvoie un résultat. Devrait être petit et rapide - le fait à la volée.


12
2018-03-13 19:55



Procédure stockée:

  • Est comme un programme miniature dans SQL Server.
  • Peut être aussi simple qu'une instruction select, ou aussi complexe qu'une longue script qui ajoute, supprime, met à jour et / ou lit des données à partir de plusieurs tables dans une base de données.
  • (Peut implémenter des boucles et des curseurs, ce qui vous permet de travailler avec résultats plus petits ou opérations rangée par rangée sur les données.)
  • Devrait être appelé en utilisant EXEC ou EXECUTE déclaration.
  • Retourne les variables de table, mais nous ne pouvons pas utiliser OUT paramètre.
  • Prend en charge les transactions.

Fonction:

  • Ne peut pas être utilisé pour mettre à jour, supprimer ou ajouter des enregistrements à la base de données.
  • Renvoie simplement une seule valeur ou une valeur de table.
  • Peut seulement être utilisé pour sélectionner des enregistrements. Cependant, il peut être appelé très facilement à partir de SQL standard, tels que:

    SELECT dbo.functionname('Parameter1')
    

    ou

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
    
  • Pour les opérations de sélection réutilisables simples, les fonctions peuvent simplifier le code. Juste se méfier de l'utilisation JOIN clauses dans vos fonctions. Si ton fonction a un JOIN clause et vous l'appelez d'un autre sélectionnez déclaration qui renvoie plusieurs résultats, cet appel de fonction sera JOIN ces tables ensemble pour chaque ligne renvoyée dans le jeu de résultats. Alors bien qu'ils puissent être utiles pour simplifier une certaine logique, ils peuvent aussi être un Goulot d'étranglement des performances s'ils ne sont pas utilisés correctement.

  • Renvoie les valeurs en utilisant OUT paramètre.
  • Ne supporte pas les transactions.

6
2018-04-29 11:56



Pour décider quand utiliser ce que les points suivants pourraient aider

  1. Les procédures stockées ne peuvent pas retourner une variable de table où la fonction peut le faire.

  2. Vous pouvez utiliser les procédures stockées pour modifier les paramètres de l'environnement du serveur où vous utilisez des fonctions que vous ne pouvez pas.

à votre santé


5
2017-07-24 19:54



Les fonctions SQL Server, comme les curseurs, sont destinées à être utilisées comme votre dernière arme! Ils ont des problèmes de performance et, par conséquent, l'utilisation d'une fonction table devrait être évitée autant que possible. Parler de performance, c'est parler d'une table avec plus de 1 000 000 d'enregistrements hébergés sur un serveur sur un matériel de classe moyenne; Dans le cas contraire, vous n'avez pas besoin de vous inquiéter de la baisse des performances causée par les fonctions.

  1. Ne jamais utiliser une fonction pour retourner un ensemble de résultats à un code externe (comme ADO.Net)
  2. Utilisez autant que possible la combinaison vues / procédures stockées. vous pouvez récupérer de futurs problèmes de croissance-performance en utilisant les suggestions que DTA (Database Tuning Adviser) vous donnerait (comme les vues indexées et les statistiques) - parfois!

pour plus de référence, voir: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html


5
2017-12-09 20:27