SQL – User Defined Functions

SQL User Defind Functions הם פונקציות אשר נוצרו על ידי המשתמש במטרה לבצע פעולה ספציפית שחוזרת על עצמה שוב ושוב. מדובר בכלי חשוב בניהול מסדי נתונים וטבלאות ובמאמר זה נראה כיצד נוכל ליצור לשנות ולמחוק פונקציה שיצרנו ממסד הנתונים שלנו.

ב-SQL ניתן ליצור מגוון של אובייקטים, כאשר User Defined Function הוא אחד מהם.
בנוסף לכך שנוכל להשתמש בפונקציות על מנת למנוע קוד שחוזר על עצמו,
נוכל להשתמש בפונקציות לשנות את מבנה הפלט, כמו כן נוכל גם להגדיר למי בדיוק מותר להשתמש בפונקציה שלנו.
כאשר אנו עובדים מול אובייקט ב-SQL נוכל להשתמש ב-CREATE,ALTER ו-DROP על מנת לבצע את האופרציות שאנחנו צריכים.

לשם תחילת העבודה צרו מסד נתונים ולו שתי טבלאות:

CREATE User Defined Function

ב-CREATE נוכל להשתמש על מנת ליצור את הפונקציה.
שימו לב ל-Code Snippet הבא שיתאר לנו באופן כללי את התחביר הנכון ליצירת User Defined Function:

CREATE FUNCTION [database_name.]function_name (parameters)
RETURNS data_type AS
BEGIN
    SQL statements
    RETURN value
END

כפי שניתן לראות:

• הפונקציה מקבלת פרמטרים (שורה 1)
• מבצעת פעולה כלשהיא בפרמטרים (שורה 4)
• מחזירה ערך כלשהו (שורה 5)

ALTER User Defined Function

ALTER עובד בצורה דומה לזו של CREATE, ומטרתו לשנות פונקציה קיימת:

ALTER FUNCTION [database_name.]function_name (parameters)
RETURNS data_type AS
BEGIN
    SQL statements
    RETURN value
END

שימו לב שהתחביר זהה לחלוטין לזה של יצירת פונקציה,
ההבדל היחידי ביניהם הוא המילה ALTER בחתימת הפונקציה.

DROP User Defined Function

על מנת למחוק פונקציה שיצרנו נשתמש ב-DROP:

DROP FUNCTION [database_name.]function_name

כמובן כשם שמחיקת רשומה ממסד נתונים היא פעולה שאין לה הופכין (בדרכים סטנדרטיות), כך גם אם נמחק פונקציה שיצרנו זה יקרה לצמיתות.

יצירה ועבודה עם הפונקציה שלנו במסד הנתונים

כעת לאחר שהבנו את התחביר, ניצור בתוכנית שלנו פונקציה פשוטה שתחזיר לנו טבלה של תלמידים שלומדים בקורס מסוים, לפי ה-CourseID שנשלח לה כפרמטר.
ראשית ניצור את הפונקציה שלנו כך:

CREATE FUNCTION returnCourseStudents ( @int INT)

RETURNS TABLE
AS
RETURN
    SELECT 
        CourseId,
        CourseName,
        StudentId
    FROM
        StudentCourse
    WHERE
        @int = CourseId

שימו לב לכך ששם הפרמטר int שמקבלת הפונקציה מתחיל ב-@.

לאחר מכן תוכלו ליצור שאילתה חדשה ולהשתמש בפונקציה כך:

SELECT * FROM returnCourseStudents(1)

למרות שה-SSMS יסמן לכם שהקוד לא תקין הוא יריץ אותו ללא שום בעיה:

אם נהיה מעוניינים לשנות משהו בפונקציה לאחר שנוצרה,
למשל שיחזיר לנו את אותה הטבלה ללא עמודת ה-CourseID, נוכל לכתוב את ה-ALTER שלנו כך:

ALTER FUNCTION returnCourseStudents ( @int INT)

RETURNS TABLE
AS
RETURN
    SELECT 
        CourseName,
        StudentId
    FROM
        StudentCourse
    WHERE
        @int = CourseId

גם כאן, תתעלמו מהסימון הקוד של ה-SSMS, שמראה שהקוד אינו תקין,
ואם פעלתם נכון תקבלו את הפלט הבא לאחר שתשתמשו שוב בפונקציה:

ולבסוף על מנת למחוק את הפונקציה פשוט השתמשו ב-DROP כך:

DROP FUNCTION returnCourseStudents

לקריאה מורחבת על פונקציות משתמש באתר של מייקרוסופט יש ללחוץ כאן