إجراء مخزن SQL: أتمتة وتحسين الاستعلامات

الدروس التعليمية
SQL

إجراءات SQL المخزنة هي مجموعات من عبارات SQL محفوظة ومخزنة في قاعدة بيانات. يمكن تنفيذها عند الطلب لأداء مهام معالجة البيانات والتحقق منها، مما يقلل الحاجة إلى كتابة شيفرة SQL مكررة للعمليات الشائعة. تعتبر الإجراءات المخزنة مفيدة في إدارة قواعد البيانات من خلال تعزيز الكفاءة وإعادة الاستخدام. بالإضافة إلى ذلك، تدعم أمان قاعدة البيانات المحسن وقابلية الصيانة. في هذه المقالة، سنتناول كيفية إنشاء وتنفيذ إجراءات SQL المخزنة، والحالات الشائعة لاستخدامها، وأفضل الممارسات.

بينما نبدأ، أوصي بشدة بأخذ دورات مقدمة في SQL وتعلم SQL في DataCamp لتعلم المعرفة الأساسية لاستخراج وتحليل البيانات باستخدام 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)).

  • كما BEGIN…END: تشكل جمل SQL الموجودة بين BEGIN وEND جسم الإجراء، حيث يتم تنفيذ المنطق الرئيسي. يسترجع الإجراء السجلات من جدول بناءً على شروط محددة في هذا المثال.

معلمات الإدخال والإخراج

تسمح لك معلمات الإدخال والإخراج بتمرير القيم إلى ومن إجراء مخزن.

على سبيل المثال، إذا تم تعريف @Parameter1 كمعامل إدخال، يمكن تعيين أي قيمة له عند استدعاء الإجراء، مما يؤثر على منطق SQL أو المخرجات. في المثال أدناه، يسترجع معامل @UserID البيانات المتعلقة بـ UserID المقدم.

-- إنشاء إجراء لاسترجاع البيانات لمستخدم معين بواسطة UserID
CREATE PROCEDURE GetUserData 
 -- معامل إدخال: معرف المستخدم الذي سيتم استرجاعه
    @UserID INT 
AS
BEGIN
    -- تحديد جميع الأعمدة من المستخدمين حيث يتطابق 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);
-- نفذ إجراء حساب الخصم بسعر 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 $
-- أنشئ إجراء لاسترجاع تفاصيل لموظف محدد بواسطة معرف الموظف
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- حدد جميع الأعمدة من الجدول Employees حيث يتطابق معرف الموظف مع المعلمة المدخلة 
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

تنفيذ الإجراءات المخزنة في MySQL

أكثر الطرق شيوعًا لتنفيذ إجراء مخزن هي باستخدام أوامر SQL. في MySQL ، نستخدم الأمر CALL لتنفيذ الإجراء المخزن.

CALL ProcedureName();

باستخدام الإجراء المعرف GetEmployeeDetails ، ستبدو عبارة التنفيذ كما يلي:

-- تنفيذ الإجراء المخزن لاسترجاع تفاصيل الموظف ذو معرف 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 لمعرف المبيعات 5 ، وتخزين النتيجة في @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- عرض إجمالي مبلغ المبيعات
SELECT @TotalSales AS TotalSales;

أوصي بأخذ دورتنا مقدمة إلى خادم SQL لفهم وظائف SQL Server المختلفة للاستعلام عن البيانات. كما ننصح بالنظر في مسارنا الوظيفي مطور خادم SQL الكامل ، الذي لن يجهزك فقط بالمهارات اللازمة لإنشاء وتحديث وتنفيذ الإجراءات المخزنة ، ولكنه سيساعدك أيضًا في وظائف الجمع والانضمام وإدخال وحذف الجداول ، وأكثر من ذلك بكثير.

الاستخدامات الشائعة لإجراءات التخزين

إجراءات SQL المخزنة مفيدة في السيناريوهات التي تتطلب مهام معقدة مكررة. وفيما يلي تطبيقات العالم الحقيقي لإجراءات التخزين في إدارة البيانات وعمليات الأعمال.

التحقق من البيانات وفرض النزاهة

يمكن استخدام الإجراءات المخزنة للتحقق من البيانات قبل التحديث أو الإدراج. في المثال أدناه، تقوم إجراءات التخزين بالتحقق من أن بريد الكتروني للعميل فريد قبل إدراج سجل جديد في جدول العملاء، مما يضمن تناسق البيانات. هذا يمركز منطق التحقق داخل قاعدة البيانات، مما يقلل من التكرار ويضمن فرض موحد عبر تطبيقات مختلفة.

-- إنشاء إجراء لإضافة عميل جديد، مع فحص البريد الإلكتروني المكرر
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 المحجوزة لإجراءات النظام لتجنب الصراعات المحتملة ومشاكل الأداء.

  • تنفيذ معالجة الأخطاء: قم بلف البيانات الخاصة ب SQL في كتل TRY...CATCH لالتقاط الأخطاء ومعالجتها والحفاظ على سلامة البيانات.

  • التحسين من أجل الأداء: قلل من استخدام المؤشرات لأنها يمكن أن تكون بطيئة وتستهلك الكثير من الموارد. جرب بدلاً من ذلك استخدام عمليات قائمة على المجموعات، والتي تكون عمومًا أكثر كفاءة. كما يُنصح بعمل فهارس للأعمدة المستخدمة بشكل متكرر وتجنب الانضمامات المعقدة في الجداول الكبيرة لتقليل الذاكرة المستخدمة وتحسين الكفاءة.

  • تعميم الإجراءات المخزنة: استخدم المعلمات بدلاً من القيم الثابتة المُدمجة لتمكينك من تمرير القيم الديناميكية إلى إجراءك، مما يجعله أكثر مرونة وإعادة استخدامًا.

تحقق من دورتنا SQL المتقدمة لتعرف المزيد حول استخدام وظائف العد والانضمامات لتصفية البيانات. كما يمكنك تجربة مسارات المهارات SQL Server Fundamentals و SQL Fundamentals لتحسين مهارات انضمام الجداول وتحليل البيانات.

الاستنتاج

تعزز إجراءات التخزين في SQL إعادة استخدام الكود وتحسين أداء إدارة قواعد البيانات. كما تعزز إجراءات التخزين أيضًا أمان قواعد البيانات من خلال الوصول المراقب وضمان سلامة البيانات. كممارس للبيانات، أشجعك على ممارسة إنشاء وتنفيذ إجراءات التخزين لتحكم في أفضل ممارسات إدارة قواعد البيانات.

إذا كنت مهتمًا بأن تصبح محلل بيانات ماهر، تحقق من مسارنا الوظيفي محلل بيانات مساعد في SQL لتتعلم المهارات اللازمة. الدورة التقارير في SQL مناسبة أيضًا إذا كنت ترغب في تعلم كيفية إنشاء لوحات معلومات احترافية باستخدام SQL. أخيرًا، أوصي بالحصول على شهادة مساعد SQL لتظهر إتقانك لاستخدام SQL في تحليل البيانات وتبرز بين محترفي البيانات الآخرين.

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