Écrire des fonctions et des procédures stockées dans SQL Server

Une collection d'instructions SQL contenues dans des procédures et des fonctions stockées, des objets de base de données utilisés pour effectuer certaines tâches (ou peuvent également être utilisés en science des données). Les deux diffèrent à bien des égards.

Dans cet article, nous discuterons en détail des fonctions et des procédures ainsi que de leurs différences.

Commençons par les procédures stockées -

Procédures stockées en SQL

Le code SQL simplement écrit est enregistré pour être réutilisé plusieurs fois, formant une procédure stockée. Si vous pensez à une requête que vous écrivez fréquemment, vous pouvez l'enregistrer en tant que procédure stockée, puis appeler cette procédure stockée pour exécuter le code SQL que vous avez enregistré dans le cadre de la procédure stockée. Cela vous évitera de devoir rédiger encore et encore les mêmes questions.

Vous pouvez exécuter à plusieurs reprises le même code SQL et fournir des paramètres aux procédures stockées. Selon les besoins, la procédure stockée répondra de manière appropriée en fonction des valeurs des paramètres fournies.

Les performances peuvent également être améliorées grâce à des procédures stockées. Un ensemble d'instructions SQL est utilisé pour effectuer plusieurs tâches. Les instructions SQL à exécuter ensuite dépendent des résultats de l'instruction SQL initiale et de la logique conditionnelle. Ces instructions SQL et la logique conditionnelle qu'elles contiennent peuvent être combinées en un seul plan d'exécution sur le serveur en les écrivant dans une procédure stockée. Puisque tout le travail est effectué sur le serveur, la logique conditionnelle peut être exécutée sans transmettre les résultats au client.

Avantages des procédures stockées

Compiler et exécuter

Chaque procédure stockée est compilée une fois par SQL Server puis le plan d'exécution est réutilisé. Lorsque vous appelez fréquemment des procédures stockées, les améliorations de performances sont énormes.

Trafic client/serveur réduit

Si la bande passante réseau pose un problème dans votre environnement, vous apprécierez que les procédures stockées peuvent compresser de longues recherches SQL sur une seule ligne pouvant être transmise par câble.

Réutilisation efficace du code et abstraction de la programmation

Les procédures stockées sont disponibles pour de nombreuses applications utilisateur et client. Si vous les utilisez de manière planifiée, le cycle de développement prendra moins de temps.

Mesures de sécurité renforcées

Indépendamment des autorisations sur les tables sous-jacentes, vous pouvez fournir aux utilisateurs l'accès pour exécuter des procédures stockées.

Fonctions en SQL

SQL Server prend en charge deux types de fonctions

Fonctions intégrées

Les fonctions intégrées fonctionnent selon les définitions de référence Transact-SQL et ne peuvent pas être modifiées. Seules les instructions Transact-SQL qui suivent la syntaxe établie par la référence Transact-SQL peuvent utiliser ces fonctions comme référence.

Le système a déjà défini ces fonctions. Il est divisé en deux catégories -

Dans ce tutoriel, nous nous référerons au tableau ci-dessous -

ID	Nom	étiquette	Âge
1	Stern	90	19
2	Suresh	50	20
3	Pratik	80	21
4	Dhanraj	95	19
5	Ram	85	18

Fonction scalaire

Ces opérations prennent une valeur en entrée et la sortent. Certaines opérations scalaires du système incluent -

• round() - Arrondit un nombre aux trois chiffres les plus proches. Par exemple, round(28.64851) produira 28,649

SELECT ROUND(MARKS,0) FROM students;

• upper() - upper("english") renvoie l'anglais, lower("ENGLISH") renvoie l'anglais.

SELECT upper(NAME) FROM Students;

Sortie

HARSH
SURESH
PRATIK
DHANRAJ
RAM

• rand() - En utilisant la fonction rand(), un nombre aléatoire dans une plage sera renvoyé. Par exemple, Rand(8) renvoie 0,71372242401 ou tout autre nombre généré aléatoirement.

Fonction d'agrégation du système

Ces fonctions renvoient une valeur unique, ces fonctions prennent une collection d'arguments d'entrée. Les exemples incluent -

Avg() fournira la valeur moyenne de toutes les entrées fournies.

Exemple

SELECT AVG(MARKS) FROM Students;

Sortie

80

Count() Cette fonction renverra le nombre de lignes qui remplissent la condition donnée.

Exemple

SELECT COUNT(*) FROM Students;

Sortie

5

Max() et min() Les fonctions max() et min() renverront la valeur la plus élevée et la plus basse parmi les arguments fournis.

Exemple

SELECT MAX(AGE) FROM Students

Sortie

21

Exemple

SELECT MIN(AGE) FROM Students;

Sortie

18

Fonctions définies par l'utilisateur

Créez des fonctions Transact-SQL personnalisées à l'aide de la commande CREATE FUNCTION. Les fonctions définies par l'utilisateur fournissent une valeur unique et nécessitent de zéro à plusieurs paramètres d'entrée. Certaines fonctions définies par l'utilisateur (UDF) renvoient une valeur de données unique, telle qu'un nombre décimal, un caractère ou un entier.

Opérations scalaires

Les fonctions scalaires définies par l'utilisateur génèrent une valeur pour chaque étape de l'opération de la fonction. Renvoie n’importe quelle valeur de type de données dans la fonction.

Fonctions table

Fonctions en ligne

Les fonctions de tableau en ligne avec des valeurs définies par l'utilisateur effectuent des opérations et renvoient les résultats sous forme de tableau. Il n’y a pas de corps BEGIN/END. Utilisez simplement une instruction SELECT pour obtenir les résultats.

Fonctions multi-instructions

Si une fonction définie par l'utilisateur contient une instruction SELECT non modifiable ou contient plusieurs instructions SELECT, les résultats qu'elle donne ne changeront pas. Nous devons spécifier explicitement les variables de table et décrire les valeurs qui peuvent être récupérées à partir de diverses requêtes SQL.

Avantages des fonctions définies par l'utilisateur

• Supporte la programmation modulaire

• La fonction peut être créée une fois, enregistrée dans la base de données, puis utilisée autant de fois dans le logiciel que nécessaire. Les fonctions définies par l'utilisateur peuvent être modifiées sans modifier le code source de l'application.

• Ils accélèrent l'exécution

• Les fonctions définies par l'utilisateur Transact-SQL (telles que les procédures stockées) réduisent les coûts de compilation en mettant en cache les plans et en les réutilisant sur plusieurs exécutions. Étant donné que les fonctions définies par l'utilisateur n'ont pas besoin d'être analysées et optimisées à chaque fois qu'elles sont utilisées, le temps d'exécution est considérablement réduit.

• Pour les charges de travail de calcul, la logique métier et les opérations sur les chaînes, les fonctions CLR fonctionnent bien mieux que les fonctions Transact-SQL. La logique gourmande en accès aux données est mieux adaptée aux opérations Transact-SQL.

• Ils peuvent réduire l'activité du réseau.

• Les fonctions peuvent être utilisées pour représenter des opérations qui filtrent les informations en fonction de contraintes complexes qui ne peuvent pas être représentées par une seule expression numérique. Pour réduire le nombre de lignes servies au client, cette fonction peut être utilisée dans la clause WHERE.

La différence entre les fonctions définies par l'utilisateur et les procédures stockées

Le tableau suivant met en évidence les principales différences entre les fonctions définies par l'utilisateur et les procédures stockées dans SQL -

Standard	Fonctions définies par l'utilisateur	Procédure stockée
valeur de retour	Valeur unique	Unique, multiple ou même zéro
Paramètres	Entrez la valeur	Valeurs d'entrée et de sortie
Base de données	Ne peut pas être modifié	peut être modifié
Déclaration	Instruction SELECT uniquement	Instructions SELECT et DML
Appel	Appel depuis la procédure	ne peut pas être appelé depuis la fonction
Compiler et exécuter	Besoin de compiler à chaque fois	Compiler une seule fois
Gestion des transactions	Impossible	Impossible

Conclusion

Dans cet article, nous avons discuté en profondeur des procédures stockées et de leurs avantages, fonctions, types de fonctions et avantages des fonctions et avons finalement conclu par la différence entre les fonctions et les procédures stockées.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!