הליך מאוחסן של SQL: אוטומציה ואופטימיזציה של שאילתות

מדריכים
SQL

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

כשמתחילים, אני ממליץ בחום לקחת את הקורסים של DataCamp מבוא ל-SQL ו-ללמוד SQL כדי ללמוד את הידע הבסיסי של חילוץ וניתוח נתונים באמצעות SQL. בנוסף, ה-גיליון ההונאה של יסודות SQL, שאותו ניתן להוריד, הוא מקור עזר מועיל שכן יש בו את כל הפונקציות הנפוצות ביותר ב-SQL.

מהי פרוצדורה מאוחסנת ב-SQL?

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

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

היתרונות של פרוצדורות SQL מאוחסנות כוללים את הבאים:

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

תחביר ומבנה בסיסיים

התחביר ליצירת הליך אחסון עשוי להשתנות במעט בהתאם למערכת המסד נתונים (למשל, MySQL, SQL Server, Oracle). להלן דוגמה כללית המשתמשת בתחביר של SQL Server:

-- צור הליך אחסון בשם ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- משפטי SQL נמצאים כאן
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

בתחביר לעיל;

  • צור פרוצדורה: פקודה זו משמשת להגדיר פרוצדורה מאוחסנת חדשה.

  • שם פרוצדורה: השם שניתן לפרוצדורה המאוחסנת. הוא צריך להיות ייחודי בתוך בסיס הנתונים.

  • @פרמטר1, @פרמטר2: פרמטרים הם אופציונליים; הם מאפשרים להליך לקבל קלטי נתונים. כל פרמטר מוגדר עם סימן @ וסוג נתון (למשל, INT, VARCHAR(50)).

  • כפי שמתחיל…נגמר: הצהרות SQL שבין BEGIN לEND מהוות את גוף ההליך, שבו מתבצע הלוגיקה העיקרית. ההליך שולף רשומות מטבלה על סמך תנאים ספציפיים בדוגמה זו.

פרמטרים של קלט ופלט

פרמטרי הקלט והפלט מאפשרים לך להעביר ערכים אל ומן הליך מאוחסן.

למשל, אם @Parameter1 מוגדר כפרמטר קלט, ניתן להקצות לו כל ערך כאשר הפונקציה נקראת, מה שישפיע על הלוגיקה של SQL או על הפלט. בדוגמה למטה, הפרמטר @UserID משיב נתונים ספציפיים לUserID שניתן.

-- צור פונקציה כדי לשחזר נתונים עבור משתמש ספציפי לפי UserID
CREATE PROCEDURE GetUserData 
 -- פרמטר קלט: מזהה המשתמש שיש לשחזר
    @UserID INT 
AS
BEGIN
    -- בחר את כל העמודות מ-Users היכן ש-UserID תואם לפרמטר הקלט
    SELECT * FROM Users WHERE UserID = @UserID;
END;

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

-- צור פונקציה כדי לחשב את המחיר המוזל
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- פלט: המחיר הסופי לאחר הנחה
AS
BEGIN
    -- חשב את המחיר הסופי על ידי יישום שיעור ההנחה על המחיר המקורי
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

כדי לקרוא לפעולה זו, תשתמש;

-- הכרז על משתנה כדי לאחסן את המחיר הסופי לאחר ההנחה
DECLARE @FinalPrice DECIMAL(10, 2);
-- בצע את הפעולה CalculateDiscount עם מחיר של 100 והנחה של 10%
-- אחסן את הפלט במשתנה @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- בחר והצג את המחיר הסופי לאחר ההנחה
SELECT @FinalPrice AS FinalPrice;

נהלי אחסון ב-MySQL

כפי שהזכרתי, ביצוע של נהלי אחסון ב-SQL יכול להתבצע בדרכים שונות בהתאם למערכת המסד נתונים ולכלים בשימוש.

יצירת נהלי אחסון ב-MySQL

יצירת נהלי אחסון ב-MySQL כוללת הגדרת שם הפעולה, פרמטרים והמשפטים SQL שמרכיבים את גוף הפעולה. הדוגמה הבאה יוצרת פעולה בשם GetEmployeeDetails שלוקחת את EmployeeID כפרמטר קלט ומחזירה פרטים עבור העובד הספציפי הזה.

DELIMITER $
-- צור הליך לשליפת פרטים עבור עובד ספציפי לפי EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- בחר את כל העמודות מעובדים כאשר EmployeeID תואם את פרמטר הקלט 
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

ביצוע הליכים מאוחסנים ב-MySQL

הדרך הנפוצה ביותר לבצע הליך מאוחסן היא באמצעות פקודות SQL. ב-MySQL, אנו משתמשים בפקודת CALL כדי לבצע את ההליך המאוחסן.

CALL ProcedureName();

באמצעות ההליך המוגדר GetEmployeeDetails, השאילתא לביצוע תיראה כך:

-- בצע את ההליך המאוחסן כדי לשלוף פרטים עבור EmployeeID 101
CALL GetEmployeeDetails(101);

הליכים מאוחסנים ב-SQL Server

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

יצירת הליכים מאוחסנים ב-SQL Server

יצירת פרוצדורה מאוחסנת ב-SQL Server כרוכה בהגדרת שם הפרוצדורה, פרמטרים, והפקודות SQL שמרכיבות את גוף הפרוצדורה. הדוגמה הבאה יוצרת פרוצדורה בשם GetEmployeeDetails שלוקחת @EmployeeID כפרמטר קלט ומחזירה פרטים על העובד הספציפי הזה.

-- צור פרוצדורה להחזיר פרטים על עובד ספציפי לפי EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- פרמטר קלט: מזהה העובד להחזיר
AS
BEGIN
    -- בחר את כל העמודות מעובדים שבהן EmployeeID תואם את פרמטר הקלט
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

הפעלת פרוצדורות מאוחסנות ב-SQL Server

ב-SQL Server, הפקודה EXEC או EXECUTE קוראת לפרוצדורה מאוחסנת. הדוגמה הבאה מראה כיצד להפעיל את הפרוצדורה המאוחסנת GetEmployeeDetails באמצעות פרמטרי קלט ספציפיים.

-- הפעל את פרוצדורת GetEmployeeDetails עם EmployeeID שהוגדר ל-102
EXEC GetEmployeeDetails @EmployeeID = 102;

אתה יכול גם לבצע את פרמטרי הפלט על ידי הכרזת המשתנה בפקודה. בדוגמה הבאה, @TotalSales מוכרז כמשתנה לקבל את הפלט מCalculateTotalSales.

-- הכרז על משתנה לאחסון סכום המכירות הכולל
DECLARE @TotalSales DECIMAL(10, 2);
-- בצע את CalculateTotalSales עבור SalespersonID 5, אחסן את התוצאה ב@TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- הצג את סכום המכירות הכולל
SELECT @TotalSales AS TotalSales;

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

שימושים נפוצים לפרוצדורות מאוחסנות

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

אימות נתונים ואכיפת שלמות

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

-- צור הליך להוספת לקוח חדש, תוך בדיקת דוא"ל כפול
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- בדוק אם האימייל כבר קיים בטבלת לקוחות
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- זרוק שגיאה אם האימייל כבר בשימוש
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- הכנס פרטי לקוח חדשים אם האימייל ייחודי
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

עיבוד נתונים ודיווח אוטומטיים

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

-- צור פרוצדורה ליצירת דוח מכירות יומי
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- הכנס את תאריך היום ואת סך המכירות לטבלת SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- בחר את תאריך הנוכחי ואת סכום המכירות להיום מטבלת המכירות
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

ניהול עסקאות

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

-- צור הליך להעברת כספים בין חשבונות
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- התחל עסקה כדי להבטיח אטומיות
    -- הפחת את הסכום המצויין מאיזון החשבון של השולח
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- הוסף את הסכום המצויין לאיזון החשבון של המקבל
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- בדוק שגיאות וחזור אם התרחשה שגיאה; אחרת, אישר את העסקה
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- בטל את כל השינויים אם התרחשה שגיאה
    ELSE
        COMMIT TRANSACTION;    -- אשר שינויים אם אין שגיאות
END;

בקרת גישה ואבטחת מידע

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

-- צור הליך להחזרת יתרת חשבון, עם בדיקת הרשאה
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- בדוק אם החשבון קיים ונמצא בבעלות המשתמש המצויין
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- אם מורשה, בחר והחזר את יתרת החשבון
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- אם לא מורשה, זרוק שגיאה
        THROW 50000, 'Unauthorized access.', 1;
END;

הגירת נתונים ותהליכי ETL

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

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- חילוץ
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- שינוי
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- טעינה
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

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

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

  • שימוש בקונבנציות שמות עקביות: כדי להקל על זיהוי והבנה של הליכי אחסון, השתמש בפורמט שמות עקבי ותיאורי. כמו כן, הימנע מהקידומת sp_ ב-SQL Server המיועדת להליכי מערכת כדי למנוע קונפליקטים פוטנציאליים ובעיות ביצועים.

  • יישום טיפול בשגיאות: עטוף אתStatements SQL בבלוקים של TRY...CATCH כדי ללכוד ולנהל שגיאות ולשמור על שלמות הנתונים.

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

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

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

סיכום

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

אם אתה מעוניין להפוך לנתח נתונים מיומן, בדוק את מסלול הקריירה שלנו אנליסט נתונים אסוציאטיבי ב-SQL כדי ללמוד את הכישורים הנדרשים. הקורס דיווח ב-SQL גם מתאים אם אתה רוצה ללמוד כיצד לבנות לוחות מחוונים מקצועיים באמצעות SQL. לבסוף, אני ממליץ על קבלת תעודת אסוציאטיב ב-SQL כדי להראות את שליטתך בשימוש ב-SQL לניתוח נתונים ולבלוט בין אנשי מקצוע אחרים בתחום הנתונים.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure