الجزء ال17: الأجراء المخزن Stored Procedure

الجزء ال17: الأجراء المخزن

 Stored Procedure 

الأجراء المخزن Stored Procedure عبارة عن مجموعة اوامر جمل الأستعلام T-SQL(Transact SQL), يمكن الأستفادة منه لتجنب كتابة نفس جمل الأستعلام عدة مرات, بل يمكنك كتابة تلك الجمل كمجموعة جمل مقترنة بأسم لها وبهذا الحال تسمى بالأجراء المخزن Stored Procedure, واذا اردت تنفيذ تلك الجمل ماعليك سوى استدعاء اسم ذلك الأجراء المخزن Stored Procedure :

لنأخذ مثال عملي: اريد استرجع اسماء الموظفين مع نوع الجنس؟

طبعاً الحل سهل باستخدام جملة الأستعلام التالية:

USE [test]

GO

SELECT NAME,GenderName

FROM tblEmployees E

JOIN tblGender G

ON E.GenderID = G.ID

السؤال هنا: ماذا لو تم استدعاء اسماء الموظفين ونوع الجنس اكثر من مرة سيصبح الأمر ممل كتابة جملة الأستعلام مرة بعد مرة!! لنفترض انها مكونة من 100 جملة استعلام!!!

الحل: يجب انشاء اجراء مخزن Stored Procedure لها,

لأنشاء الأجراء المخزن Stored Procedure:

CREATE PROCEDURE {Name for Stored Procedure}

AS

BEGIN

{Query Statements}

END

انشاء اجراء مخزن لجمل الأستعلام في المثال اعلاه:

USE [test]

GO

CREATE PROCEDURE spGetEmployees

AS

BEGIN

SELECT NAME,GenderName

FROM tblEmployees E

JOIN tblGender G

ON E.GenderID = G.ID

END

ملاحظة: يمكن اختصار كلمة  PROCEDUREعند انشاء الStored Procedure الى PROC

لأستدعاء (تنفيذ جمل الأستعلام) الأجراء المخزن بأستخدام احد الطرق الأربعة:

·       فقط قم بكتابة اسم الأجراء المخزن:

·       قم بكتابة EXECUTE امام اسم الأجراء المخزن:

·       قم بكتابة EXEC امام اسم الأجراء المخزن:

·       عن طريق الواجهة الرسومية للSSMS:

ماذا لو اردت عمل اجراء مخزن لأستدعاء اسماء الموظفين أعتماداً على تحديد نوع الجنس عند تنفيذ الأجراء, اي بمعنى اخر, اريد تحديد نوع الجنس عند تنفيذ الأجراء؟!

الحل عن طريق عمل متغير Variable لتحديد نوع الجنس عند انشاء الأجراء المخزن Stored Procedure , وعند استدعاء هذا الأجراء يجب تزويده بقيمة المتغير اي Parameter.

لأنشاء الأجراء المخزون Stored Procedure:

USE [test]

GO

CREATE PROCEDURE spGetEmpByGender

@GENDER NVARCHAR(20)

AS

BEGIN

  SELECT E.Name,G.GenderName

  FROM tblEmployees E

  JOIN tblGender G

  ON E.GenderID = G.ID

  WHERE G.GenderName = @GENDER

END

لتنفيذ الأجراء المخزون:Stored Procedure  يجب تزويد الأجراء بقيمة المتغير كالأتي:

او يمكن تحديد اسم المتغير مع قيمته:

ملاحظة: لاحظ استخدام ال@ مع بداية اسم المتغير عند انشاء الأجراء المخزون Stored Procedure وعند تزويد قيمة للمتغير عند تنفيذ الأجراء.

لأظهار النص للأجراء المخزون Stored Procedure يوجد طريقتان:

اما عن طريق الأجراء المخزون الخاص بالنظام System Stored Procedure الذي يسمى ب sp_helptext

او عن طريق الواجهة الرسومية للSSMS:

ملاحظة: مايكروسوفت لا توصي بأستخدام sp_ كبادئة لأسم الأجراء المخزون Stored Procedure , لأن الأجراءات المخزونة للنظام System Stored Procedure تحتوي على تلك البادئة, فالنتيجة سوف يكون هنالك غموض بين الأجراءات التي يخزنها المستخدم User defined Stored Procedureوبين الأجراءات الخاصة بالنظام System Stored Procedure!

لنفرض اننا نريد ترتيب اسماء الموظفين في المثال السابق عند انشاء الأجراء المخزون الذي سيسترجع اسماء الموظفين حسب القيمة المدخلة لنوع الجنس عند تنفيذ الأجراء, اي بمعنى اخر, كيفية تعديل الأجراء المخزون؟

الحل: كأنشاء الأجراء المخزون لأول مرة, فقط غير كلمة CREATE  الى ALTER مع اجراء التعديل كالأتي:

لحذف الأجراء المخزون Stored Procedure اما عن طريق جملة الأستعلام كالأتي:

DROP PROCEDURE spGetEmpByGender

او عن طريق الواجهة الرسومية للSSMS:

 لتشفير نص الأجراء المخزون Stored Procedure اي عدم التمكن من قراءة محتوى الأجراء يمكن ذلك بأستخدام WITH ENCRYPTION  كالأتي:

كما تلاحظ عند تنفيذ الأجراء الخاص sp_helptext سوف لم يتم اظهار نص الأجراء المخزون لأنه مشفر!