הכל על Stored Procedure
CREATE PROCEDURE שם AS ואחריו השאילתה. הפרוצדורה נשמרת בדאטאבייס ומתקמפלת, ואין צורך לכתוב שוב את השאילתות בכל הפעלה.EXEC שם מריץ את הפרוצדורה, והיא מחזירה את התוצאה של השאילתה.ALTER PROCEDURE משנה את הפרוצדורה, ו-DROP PROCEDURE מוחק אותה.@ID int = 9. בקריאה מעבירים ערך, או מסתמכים על ברירת המחדל.RETURN מחזיר ערך סקלרי אחד. בדוגמה מחזירים את מספר העובדים.sp_recompile מסמן פרוצדורה לקומפילציה מחדש בהפעלה הבאה. WITH RECOMPILE מורה לקמפל מחדש בכל הפעלה, ואפשר לציין אותו גם בעת ההפעלה עצמה.INSERT INTO Employees (FirstName, LastName) EXEC ....פרקי ההרצאה: הכל על Stored Procedure
תמלול ההרצאה: הכל על Stored Procedure
הטקסט המלא של הדקות הראשונות בהרצאה «הכל על Stored Procedure» בקורס הווידאו של PracticU – כתוביות בעברית עם זמן התחלה לכל פסקה. כך תוכלו לקרוא ולחפש את הנושאים לפני שמתחילים ללמוד.
חברים וחברות, שלום לכם וברוכים הבאים לשיעור נוסף בנושא של SQL. השיעור הוא היום נעסוק בנושא של Store Procedure. Store Procedure, המשמעות היא שאני לוקח אוסף של שאילתות SQL, אני עוטף אותן ונותן להן שם, ומכאן ואילך, במקום להפעיל את כל השאילתות ולהריץ אותן מחדש,
אני פשוט קורא לשם שנתתי. אני קורא ל-Stored Procedure. פעולה זו נותנת לי אפשרות של חיסכון בהכרת מבנה הדאטאבייס, מבנה הנתונים שנמצא אצלי.
יתרה מכך, פעולה זו מבחינתי גם משפרת ביצועים, כיוון שאחר שלקחתי את כל אותן השאילתות ועטפתי אותן ב-Stored Procedure, הן מקומפלות פעם אחת בתוך הדאטאבייס, נשמרות ככה, ומכאן ואילך אני קורא רק ל-Stored Procedure בלבד.
בואו ננסה להבין מתוך דוגמה כיצד הדבר הזה עובד. יש לי שאילתת פשוטה, שאילתת Select All From Employees, שאילתה שמחזירה לי את מגוון הנתונים על כל העמודות בתוך טבלת Employees.
בכל פעם שאני מריץ את השאילתה הזאת מתבצע פרסינג לאותה שאילתה. הדאטאבס בוחר לעצמו Execution Plan, כלומר תוכנית עבודה כיצד לשלוף את כל הנתונים בצורה האופטימלית, היעילה ביותר ואם אני אקח אבל את השאילתה הזאת ואני אעטוף אותה create procedure ואני אתן לזה שם שוב פעם as כאן ואילך יצרתי בעצם
פרוצדורה בתוך הדאטה בס שהפרוצדורה נקראת get employees והיא מריצה עבורי את השאילתה הזו 5 הדבר הזה מאוד מאוד מזכיר לנו יצירה של view אולם כפי שנראה בהמשך בניגוד ל view לסטורד פרוסייג'ר אני יכול לתת פרמטרים ואני יכול לקבל ממנו Output פרמטר, אני יכול לקבל ממנו ערך מוחזר.
אחרי שקראתי, ייצרתי את ה-Stored Procedure, אני יכול להפעיל אותו, Exec, קיצור של Execute או בקיצור פשוט לרשום את שם הפרוצדורה וגם ככה אני אקבל ריצה של כל הנתונים.
כאובייקט בתוך ה-Database הפרוצדורה, לא רק שאני יכול ליצור אותה אלא כמו כל אובייקט אחר כמו טבלה או view אנחנו גם יכולים לשנות או למחוק אותה alter procedure שוב פעם שם הפרוצדורה as select first name last name ובו נבחר גם את הטייטל from employees בשלב הזה אני אני שיניתי את הפרוצדורה get employees ולכן בעוד שביצירה כתבנו את פקודת create כאן
אנחנו כותבים את פקודת alter ולכן מכאן ואילך בעת שאני אפעיל את הפרוצדורה אני אקבל את השמות של העובדים שלי שם משפחתי ושם משפחה משורשרים יחדיו לצד הטייטל שלהם תפקידם בחברה.
אז כמו שיצרתי את אותה פרוצדורה אני יכול גם לעשות לה שינוי ואני גם יכול לעשות לה drop procedure שם הפרוצדורה ומכאן ואילך הפרוצדורה כבר לא קיימת יותר ואם אנחנו נבוא וננסה להפעיל אותה called not find stored procedure אם לא יתרה מכך בואו נשוב ונייצר את הפרוצדורה זה כבר לא alter אלא create procedure יצרנו שוב
מחדש את הפרוצדורה וכעת הפרוצדורה הזאת, אני רוצה להשתמש בה גם בפקודת insert. מהי אותה פקודת insert? בואו נראה כאן.
אני אצור לצורך העניין פרוצדורה חדשה. create procedure get first and last, אני יוצר כרגע פרוצדורה חדשה וזאת על מנת להדגים בפניכם כיצד אני משתמש באותה פרוצדורה גם בפקודות dml.
אני יכול לבצע insert into employees, של מה? של כל הערך המוחזר לי מתוך הפרוצדורה. של כל מה שאני אקבל כאן מתוך get first last.
כלומר, ברגע שהוא יבצע לי הריצה של אותה פרוצדורה, כל הערך שיוחזר יקבל, רק אני צריך להגיד לו שכאן אני רוצה כבר להריץ אותה, exit get first ו-last.
וכאן כמובן אנחנו צריכים לציין שאנחנו מוצאים להכניס את הערכים לתוך first name, ולתוך last name ובאופן הזה ב-F5 אני מקבל שכל הפלט שקיבלתי אותו כאן בשליפת הנתונים נכנס מחדש לתוך טבלת employees כחלק מפקודת insert שהריצה לי גם את ה-stored procedure.
כעת חברים אבא ונבחן כיצד אני מעניק בקריאה לפרוצדורה ביצירה שלה כיצד אני יוצר פרוצדורה המקבלת פרמטרים. לצורך כך בואו נבחן, פותח לנו קווירי חדש.
שוב, אני רוצה ליצור כאן פרוצדורה, פרוסידג'ר, ואנחנו ניתן לה amp with ID. אנחנו בעצם נותנים לה כאן את השם של הפרוצדורה, ולצד זה שאני נותן לה את השם שלה, אני גם מיד מציין אילו פרמטרים היא מקבלת.
אז כבר הפרמטר הראשון שאני מציין זה פרמטר ID. אני מציין שהוא מסוג אינטג'ר, הדאטה טייפ שלו, אז אני יכול כבר להיכנס לתוך הפרוצדורה ולהגיד Select כוכבית From Employees Where Employee ID שווה ל-Shrudel ID, דהיינו לפרמטר שאותו קיבלתי.
יצרנו כאן כרגע אובייקט, טרם יצרנו, אבל הגדרנו אובייקט שנקרא AMP with ID. האובייקט הזה הוא מסוג Procedure, פרוצדורה שתישהה מכאן ואילך בדאטאבייס, אולם זו פרוצדורה שמקבלת פרמטר אחד.
מסוג אינטג'ר והשם שלו זה שטרודל איי-די. F5 אני מקבל אינדיקציה שהאובייקט נוצר בתוך הדאטאבס. איך אני יכול לקרוא לו מכאן ואילך? כפי שראינו עד כה שטרודל אקזק אמפ וויב איי-די.
אני רושם את השם. כעת אם אני אנסה לקרוא לו אז כבר אני אקבל הודעת שגיאה. Procedure of function אמפ וויב איי-די. Expected parameter שטרודל איי-די.
כלומר אני חייב לתת כאן את הפרמטר לספק אותו בשעה שאני קורא לתוך הפרוצדורה ואת זאת אני עושה באופן הבא שטרודל איידי שם הפרמטר ואני יכול לתת לו כאן מספר מסוים 5. כשה-F5 אני מריץ אותה אז אני אכן אקבל את הקוד, את ה-employee ID שהקוד שלו הוא 4. אני יכול להריץ את אותה פרוצדורה ולשלוח לה פעם 5 ושוב פעם היא תרוץ ושוב פעם אני אקבל קוד עובד
הפעם לפי הפרמטר שאני שלחתי ID שווה ל-5. שימו לב שציון של שם הפרמטר כ-ID שווה הוא במקרה הזה בא לשפר לנו את הקריאות שאני אראה איזה פרמטר אני מאכלס, אבל זה כמובן לא חובה כיוון שאני יכול רק לרשום את שם הפרוצדורה, את הפרמטרים לפי סדר במידה והיו יותר מאחד אחד אחרי השני והפרמטר, הערך הראשון ייכנס לפרמטר הראשון.
הבה ונחזור לפרוצדורה שאותה יצרנו. יצרנו כאן פרוצדורה ונתנו לה שם. נתנו לה ערך שאותו הוא מקבל, אבל בשעה שאנחנו לא מספקים את הערך אנחנו נקבל הודעת שגיאה.
אני רוצה לתת כאן ערך דיפולטי, שבהינתן וקראו לפרוצדורה ללא ערך, היא תקרא לאותו ערך דיפולטי. אז קודם כל אנחנו כבר לא ב-create אלא ב-alter procedure.
ואת הערך הדיפולטי אני מציין אותו באופן הזה על ידי זה שאני מציין שווה ואת הערך עצמו. אלטר פרוסידג'ר אנחנו מקמפלים את זה שוב מחדש, מריצים ושוב פעם אובייקט נוצר, הפעם עם ערך דיפולט value של 9. בואו ננסה, אם אני קורא לאותה פרוצדורה עם 5 אני אקבל 5, אולם מנגד, אם אני קורא לפרוצדורה
והפעם אני לא מספק פרמטרים, אני לא מקבל הודעת שגיאה כמו שקיבלתי מקודם, אני מקבל שליפה של הנתון עם ה-Default Value של קוד עם פלואי שווה 9. יצרנו אם כן בדוגמה הקודמת שלפנינו את amp-with-id פרוצדורה שמקבלת פרמטר 1 ויש לו default value של 9 בהינתן מקרה שבו קראתי לפרוצדורה ולא שלחתי לפרמטר.
בואו ננסה ליצור פרוצדורה נוספת, הפעם עם מספר פרמטרים. שוב, Create, Procedure, בואו נלך הפעם על טבלת Products, ננסה לנתח אותה, מתוך טבלת פרודקס אני רוצה לשלוף לפי קטגוריה את כל המוצרים ששייכים לקטגוריה מסוימת, ומחירם גבוה מהמחיר שאותו
אני נותן כקלט לשאילתא. אז השאילתא, הסטור פרוסיג'ר שלי תקרא SP prod. כאן בשאילתא, בסטור פרוסיג'ר הזה, מכיוון שאני רוצה לשלוח מספר פרמטרים, אני יכול לעטוף אותם על מנת לשפר את הקריאות בסוגריים עגולים.
זה חובה זה לא must אבל זה בהחלט משפר את הקריאות מה שכן חובה זה את הפרמטרים עצמם לציין עם שטרודל כרוכית לפני שם של הפרמטר שם של הפרמטר הראשון זה categoryid code קטגוריה שאותו אנחנו רוצים לבקש data type שאותו אני חייב לציין מיד לאחר שם הפרמטר אז אני אציין int ומכיוון שאני רוצה לתת לו ערך default value אז אני ארשום שווה null
כך ציינתי את הפרמטר הראשון שאותו אני רוצה לשלוח לפרוצדורה פרמטר שני גם הוא אני חייב להתחיל אותו בשטרודל unit price גם אותו נציין כ-int והערך שלו יש שווה ל-null.
אבא נבחן את הטבלה מבחינת העמודות שלה אם אנחנו מדייקים אנחנו רואים שהcategory ID int ה-unit price הוא מסוג של money אז גם כאן אנחנו נחזור ל-Store Procedure וגם כאן אנחנו נרצה לשלוח לה Money על מנת לשמור על תעימות. עד כאן רשמתי את הכותרת של ה-Store Procedure.
זה השם שלה. אלו הפרמטרים שהיא מקבלת. עטפתי אותם שוב בסוגריים רק לשפר את הקריאות ולא יותר מזה. אז אני נכנס כבר לגוף הפרוצדורה, מה בפועל היא עושה.
הפרוצדורה שולפת לי את כל המוצרים מתוך טבלת Products אשר מקיימים את התנאים הבאים. מה הם אותם תנאים? שהCategory ID שלהם שווה לאותו פרמטר שאנחנו קיבלנו וה-Unit Price שלהם גבוה גדול שווה או מספיק גדול מהערך שאנחנו קיבלנו כאן ב-Unit Price.
שימו לב שפקודת גו מציינת סיום של Store Procedure, לא ניתן בתוך Store Procedure אחד לציין את המילה השמורה הזאת "go" מכיוון ש"go" משמעותה סיום של ה-Store Procedure, לכן זה יבוא תמיד תמיד בסוף ואך ורק פעם אחת.
אנחנו מריצים את ה-Store Procedure כמו שאנחנו רואים אבל אז אנחנו מגלים את הבעיה הבאה: ה-Store Procedure שלנו יודע לשלוף אולם בהינתן מצב שלא נתתי לו ערכים אז הוא ישלוף לי קטגורי ID Null כלומר הוא לא ישלוף לי שום ערך.
בואו ננסה להפעיל את ה-Store Procedure SPprod אנחנו נשלח לו כפרמטרים אני רוצה את הפרמטרים הבאים אז בואו נלך לטבלה ונבחן את הערכים שאותם אנחנו רוצים לשלוף עשיתי איזשהו סלקט אני רוצה לקחת את כל אלו שהקטגורי האידי שלהם זה אחד ומחירם גבוה מ-12 שקלים 12 דולרים אז אנחנו נריץ
את זה SP אז כפרמטר ראשון אנחנו רוצים לשלוח את כל אלו שהקטגוריה שלהם שווה לאחת, ולכן אני כותב מציין את הערך 1, ולאחר מכן על ידי שאני מציין בעצם רווח על מנת לציין שאני כאן מתחיל את הפרמטר הבא, אני מציין שה-unit price שאותו אני רוצה לשלוח הוא 12. נשלוף ואנחנו מגלים שאני אכן מקבל כאן מה שביקשתי כל המוצרים שקטגוריה ID שלהם
שווה 1 ומחירם גבוה מ-12 שקלים. אם אני אעלה כאן את המחיר ואני אציין כאן 18 אז שוב פעם תיקרא הפרוצדורה עדיין עם הערך 1 קטגורי ID אבל רק אלו שהיוניט פרייז שלהם גדול מהערך שאותו אני סיפקתי הערך 18. כמו שקראתי לפרוצדורה כך אני יכול גם לקרוא לה באופן כפי שראינו קודם אני יכול לציין את השמות של הפרמטרים קטגורי ID שווה ל-1 ואפילו שאני לא מציין
את השמות של הפרמטרים לא לפי סדר, כאן אני צריך לשוב ולציין גם את המחיר של ה-Unit Price. Unit Price שווה ל-18. השורה הזאת והשורה הקודמת שלפניה, מלבד זה שאני צריך להוסיף כאן פסיק על מנת להפריד בין הפרמטרים, הן זהות לחלוטין, אלא שהשורה השנייה היא יותר קריאה, מכיוון
שהיא מציינת לי את שם הפרמטר שאליו אני מכניס ערך ומהו ערכו. יתרה מכך, את השורה הזאת, מכיוון שאני מציין את הפרמטרים במדויק, אז אין לי צורך, אני כבר לא מחויב לסדר הפרמטרים כפי שהם מופיעים בכותרת של הפרוצדורה, אלא, שימו לב,
אני יכול לשלוח אותם בסדר הפוך. אף על פי שהפרוצדורה מקבלת תחילה קטגורי, ולאחר מכן Unit Price, כאן הפכתי את הסדר, תחילה שלחתי Unit Price, ולאחר מכן קראתי ל"Category ID", אבל גם עם זה ה-DataBase יודע לחיות היטב, והנה אני לוחץ על F5, Execute, ואני מקבל את המידע.
כל פרמטר נשלח לערכו המתאים, כפי שאני ציינתי במפורש. שם הפרמטר שווה והערך שאני שולח אליו. אממה יש לנו כאן בעיה אחת, שברגע שאני אקרא לאותה פרוצדורה svprod ואני אספק לה את הקטגורי ID 1. אולם אני לא מספק את ה-unit price אז מה שנעשה כאן זה unit price גדול מה-default value.
ה-default value הוא נעל, הוא לא נותן לי כאן בעצם שום דבר ולכן כאן בשליפה אני לא מקבל שום ערך. על מנת לשנות את זה ועל מנת להפוך שבמידה ואחד התנאים הוא נעל אז בעצם לא צריך להתייחס אליו, אנחנו נבצע את השינוי הבא, בואו ראשית נגדיל את המסך,
אנחנו משנים את הפרוצדורה, אז שוב פעם אנחנו כבר עוברים לפקודת Altered Procedure שם הפרוצדורה Unit Price Category ו-Unit Price, אולם כאן מה שאני אכתוב זה Is Null בהינתן שהערך הזה נתתי לו Null, אז כל מה שאני רוצה כאן לבקש זה שה-Unit Price יהיה גדול מאפס, כיוון שהתנאי הזה כבר מבחינתי הוא לא רלוונטי, אני רוצה את כל ה-Unit Price.
אותו דבר, is null, בהינתן שcategoryid שלחתי אותו ללא פרמטר ולא אכלסתי אותו בערך, כלומר סמכתי על ה-default value שהוא null, אז כל מה שאני רוצה שהcategoryid יהיה שווה למה?
לcategoryid כפי שהוא מופיע בטבלה, שמבחינתי בעצם התנאי הזה הוא כבר לא רלוונטי. בואו נריץ על מנת לקבל את הפרוצדורה החדשה שלנו.
כעת כשאני שופך את זה אז אני אמנם שולח categoryid שווה ל-1, אולם מאחר והערך unit price כאן טפתי אותו בפונקציית is null, בהינתן שהוא null הוא מקבל בעצם אפס, אז אני כן, בניגוד לפעם הקודמת, כן אני אקבל כאן ערכים מהפרוצדורה.
אני מקבל את כל השורות שבהן קטגוריה הגישה ב-1, ללא שום בדיקה מהו ה-unit price שלהן. יתרה מכך, גם כשאני קורא ל-sp-code ואני בכלל לא שולח לו פרמטרים, סליחה, ואני בכלל לא שולח לו פרמטרים, אני שולח אותו כמו שהוא.
אז ממילא בצעד בדיקה, לא על עמודת קטגורי ID וגם לא על עמודה של Unit Price. בואו נריץ את זה: SP Broad F5 והנה אני מקבל כאן גם קטגורי ID 1 וגם את 2 ואת 7 וכמובן שאת Unit Price אני מקבל את הכל.
אני מקבל 77 רוז את כל המוצרים שקיימים לי כרגע בטבלת פרודקטס. בשלב הזה, מה שאני עשיתי, יצרתי לנו פרוצדורה שאומנם מסוגלת, יודעת לקבל פרמטרים, שני פרמטרים, אולם היא מסוגלת גם להתמודד עם העדר אחד הפרמטרים או עם שניהם, על ידי זה שהיא נותנת ערך כדיפולט value null,
בהתאם לכך גם השאילתה עודכנה שבהינתן והערך הוא נ"ל ישנה התנהגות מתאימה אילו שורות אני מעוניין לשלוף. בדוגמה הבאה שלפנינו אנחנו מעוניינים לבחון את הנושא של Output Parameters.
Output Parameters אלו הם פרמטרים שנשלחים לתוך הפרוצדורה, אולם לעומת Input Parameters שהם מביאים איתם ערך, Output Parameters אני מצפה לקבל ערך שאני אוכל להשתמש בו מחוץ לפרוצדורה.
Output Parameters הינו פרמטר לכל דבר ועניין שיכול להכיל בתוכו ערכים שהפרוצדורה תשתמש בהם, אבל היתרון של Output Parameters שאני יכול בתוך הפרוצדורה לבצע בו