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

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

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

ما هو الإجراء المخزن؟

الإجراء المخزن هو روتين مُجمَّع مسبقًا يُخزَّن في قاعدة بيانات علائقية ويُستدعى بالاسم مع معلمات اختيارية. يقوم محرك قاعدة البيانات بتجميعه مرة واحدة، وتخزين خطة التنفيذ مؤقتًا، وإعادة استخدام تلك الخطة في كل استدعاء لاحق، متجنبًا بذلك دورة التحليل والتجميع والتحسين التي تتطلبها استعلامات SQL المخصصة في كل مرة يتم تشغيلها.

SQL

-- SQL Server: basic stored procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
END;
GO

-- Calling the procedure
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

لا يقوم التطبيق بكتابة استعلامات SQL مباشرة. بل يستدعيها. GetCustomerOrders مع تحديد المعاملات. أما الباقي فيتم التعامل معه بواسطة قاعدة البيانات.

الإجراءات المخزنة مقابل العرض مقابل الوظائف

ثلاثة كائنات من قواعد البيانات غالباً ما يتم الخلط بينها. يوضح الجدول أدناه الفرق بينها:

هدف	سياسات الإرجاع والاستبدال	يقبل المعاملات	يمكن تعديل البيانات	خطة التنفيذ مخزنة مؤقتًا	أفضل ل
إجراء مخزن	مجموعات النتائج، معلمات الإخراج، رموز الإرجاع	نعم	نعم	نعم	منطق معقد، عمليات لغة معالجة البيانات، تطبيق الأمن
عرض الطلب	مجموعة نتائج واحدة (مثل جدول)	لا	لا (عادةً)	جزئي	تبسيط استعلامات SELECT، وأمان على مستوى الأعمدة
الدالة العددية	قيمة واحدة	نعم	لا	لا	إعادة استخدام العمليات الحسابية في قوائم SELECT
دالة ذات قيم جدولية	مجموعة النتائج	نعم	لا	جزئي	طرق عرض مُعَلمة، عمليات حسابية تُعيد مجموعات

التمييز الرئيسي: استخدم عرضًا عندما تريد تجريدًا قابلًا لإعادة الاستخدام لعبارة SELECT. استخدم إجراءً مخزنًا عندما تحتاج إلى معلمات، أو منطق شرطي، أو تعديل بيانات، أو تطبيق إجراءات أمنية. استخدم دالة عندما تحتاج إلى عملية حسابية تُرجع قيمة أو جدولًا، ويجب دمجها مع استعلامات SQL أخرى.

الفوائد الأساسية الأربع

الأداء: التجميع المسبق وتخزين الخطة مؤقتًا

عندما يتلقى خادم SQL أو PostgreSQL أو Oracle استدعاءً لإجراء مخزن، فإنه يتحقق مما إذا كانت هناك خطة تنفيذ مخزنة مؤقتًا لهذا الإجراء. إذا كانت موجودة، فإنه ينفذها فورًا. وإذا لم تكن موجودة، فإنه يُجمّع الإجراء، ويُنشئ خطة تنفيذ، ويخزنها مؤقتًا، ثم ينفذها. وفي جميع الاستدعاءات اللاحقة، يُعاد استخدام الخطة المخزنة مؤقتًا.

قد تُعاد ترجمة استعلامات SQL المخصصة، وهي استعلامات مُجمّعة من سلاسل نصية تُرسل من كود التطبيق، في كل استدعاء، وذلك بحسب قاعدة البيانات وبنية الاستعلام. بالنسبة للاستعلامات عالية التردد التي تُستدعى آلاف المرات في الثانية، تُصبح تكلفة الترجمة هذه كبيرة.

SQL

-- Demonstrating plan reuse in SQL Server
-- Check if a plan exists for a procedure
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, 1, 100) AS procedure_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.execution_count DESC;

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

الأمان: تقييد الوصول المباشر إلى الجدول

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

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

SQL

-- Create a role for application users
CREATE ROLE AppReadRole;

-- Grant execute on the procedure
GRANT EXECUTE ON GetCustomerOrders TO AppReadRole;

-- Deny direct table access
DENY SELECT ON Orders TO AppReadRole;
DENY SELECT ON Customers TO AppReadRole;

-- The user can now:
--   EXEC GetCustomerOrders @CustomerId=123, ... -> works
--   SELECT * FROM Orders WHERE ... -> ACCESS DENIED

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

SQL

-- VULNERABLE: dynamic SQL built from user input
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Customers WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- An attacker can inject: '; DROP TABLE Customers; --

-- SAFE: parameterized stored procedure
CREATE PROCEDURE GetCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SELECT CustomerId, Name, Email
    FROM Customers
    WHERE Name = @CustomerName;  -- @CustomerName is a literal, not SQL
END;

احترس: إجراء مخزن يقوم بإنشاء استعلامات SQL ديناميكية داخليًا باستخدام EXEC() or sp_executesql إن دمج مدخلات المستخدم في سلاسل نصية لا يقل عرضةً للاختراق عن لغة SQL الديناميكية على مستوى التطبيق. يجب أن يشمل استخدام المعلمات أي لغة SQL ديناميكية يتم إنشاؤها داخل الإجراء.

سهولة الصيانة: تغيير واحد، تحديث جميع التطبيقات

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

SQL

-- Before: discount logic duplicated in three application services
-- After: centralized in one stored procedure
CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @DiscountedTotal DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Subtotal DECIMAL(10,2);
    DECLARE @CustomerTier NCHAR(1);

    SELECT @Subtotal     = SUM(li.Quantity * li.UnitPrice),
           @CustomerTier = c.Tier
    FROM OrderLineItems li
    JOIN Orders o ON o.OrderId = li.OrderId
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE li.OrderId = @OrderId
    GROUP BY c.Tier;

    -- Business rule: Gold customers get 15%, Silver get 8%, Standard get 0%
    SET @DiscountedTotal = @Subtotal * CASE @CustomerTier
        WHEN 'G' THEN 0.85
        WHEN 'S' THEN 0.92
        ELSE 1.00
    END;
END;

يمكنك تغيير نسب الخصم في مكان واحد. جميع خدمات التطبيق الثلاث التي تتصل CalculateOrderTotal قم بتطبيق الأسعار الجديدة فوراً.

التغليف مع معلمات الإخراج ومعالجة الأخطاء

تقوم الإجراءات المخزنة بإرجاع قيم متعددة من خلال معلمات الإخراج وتُبلغ عن حالة المعالجة من خلال رموز الإرجاع، مما يتيح أنماط تفاعل أغنى من عملية SELECT البسيطة.

SQL

CREATE PROCEDURE InsertOrder
    @CustomerId  INT,
    @OrderDate   DATE,
    @NewOrderId  INT OUTPUT,
    @StatusCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, @OrderDate, 'PENDING');

        SET @NewOrderId = SCOPE_IDENTITY();
        SET @StatusCode = 0;  -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @StatusCode = ERROR_NUMBER();
        SET @NewOrderId = -1;
    END CATCH;
END;

-- Usage
DECLARE @OrderId INT, @Status INT;
EXEC InsertOrder
    @CustomerId = 5001,
    @OrderDate  = '2026-07-15',
    @NewOrderId = @OrderId OUTPUT,
    @StatusCode = @Status OUTPUT;

IF @Status <> 0
    PRINT 'Insert failed with error: ' + CAST(@Status AS VARCHAR);
ELSE
    PRINT 'Created order: ' + CAST(@OrderId AS VARCHAR);

تحسين الأداء: ما تخبرك به خطة التنفيذ

خطة التنفيذ هي سجل محرك قاعدة البيانات لكيفية اختياره لتنفيذ استعلام، والفهارس التي استخدمها، وخوارزمية الربط التي اختارها، وعدد الصفوف المُقدَّر في كل خطوة. بالنسبة لإجراء مخزن يُستدعى آلاف المرات يوميًا، تُعد خطة التنفيذ الأداة التشخيصية الأساسية لمشاكل الأداء.

SQL

-- Enable actual execution plan in SQL Server
-- Then run:
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

-- Check for parameter sniffing issues
-- (cached plan was optimized for a different parameter distribution)
EXEC GetCustomerOrders
    @CustomerId = 99999,  -- rare customer with very few orders
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30'
WITH RECOMPILE;  -- forces fresh plan for this call

استنشاق المعلمات يُعدّ هذا من أكثر مشاكل أداء الإجراءات المخزنة شيوعًا. يقوم خادم SQL بتخزين خطة التنفيذ المُولّدة لأول مجموعة من المعلمات التي يتم استدعاء الإجراء بها. إذا استخدمت الاستدعاءات اللاحقة قيمًا مختلفة تمامًا للمعلمات، مثل عميل لديه 50,000 طلب مقابل عميل لديه طلبان فقط، فقد تكون الخطة المخزنة غير مثالية لتلك القيم.

استراتيجيات التخفيف: OPTIMIZE FOR تلميح لتحسين قيمة المعلمة التمثيلية؛ WITH RECOMPILE على مستوى الإجراء لإنشاء خطة جديدة في كل استدعاء (مكلف، ولكنه فعال عندما تختلف توزيعات المعلمات على نطاق واسع)؛ تعيين المتغيرات المحلية في بداية الإجراء لمنع التجسس:

SQL

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    -- Local variable trick: prevents parameter sniffing
    DECLARE @LocalCustomerId INT = @CustomerId;
    DECLARE @LocalStart      DATE = @StartDate;
    DECLARE @LocalEnd        DATE = @EndDate;

    SELECT o.OrderId, o.OrderDate, o.TotalAmount
    FROM Orders o
    WHERE o.CustomerId = @LocalCustomerId
      AND o.OrderDate BETWEEN @LocalStart AND @LocalEnd;
END;

أفضل الممارسات: قائمة مرجعية عملية

بدلاً من المبادئ العامة، هذه هي الممارسات التي تجعل الإجراءات المخزنة قابلة للصيانة على نطاق واسع:

التسمية والتنظيم

• استخدم اصطلاح تسمية متسقًا: usp_ بادئة لإجراءات المستخدم المخزنة، sp_ مخصص لإجراءات النظام
• تسمية الإجراءات باستخدام الفعل + الاسم: GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
• تجميع الإجراءات ذات الصلة في مخطط: Sales.GetCustomerOrders, Inventory.UpdateStock

هيكل الكود

• ابدأ كل إجراء بـ SET NOCOUNT ON لحجب رسائل عدد الصفوف التي قد يسيء العملاء تفسيرها
• استعمل BEGIN TRY / BEGIN CATCH كتل مع صريح BEGIN TRANSACTION / COMMIT / ROLLBACK
• تجنب استخدام المؤشرات في عمليات المجموعات، وأعد كتابة الاستعلامات باستخدام لغة SQL القائمة على المجموعات كلما أمكن ذلك.
• لا تستخدم SELECT *قم بتسمية كل عمود تُرجعه العملية

الأمن والحماية

• منح صلاحيات التنفيذ على الإجراءات؛ ومنع الوصول المباشر إلى الجداول لأدوار التطبيق
• تجنب إنشاء استعلامات SQL ديناميكية من مدخلات المستخدم داخل الإجراءات
• استعمل sp_executesql باستخدام الاستعلامات المُعَلمة إذا كان استخدام لغة SQL الديناميكية أمرًا لا مفر منه

هاملت

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

توثيق

• أضف تعليقًا في بداية كل إجراء: الغرض، والمعلمات، والقيم المُعادة، والمؤلف، وتاريخ آخر تعديل.
• توثيق قواعد العمل المضمنة في منطق الإجراء، ليس فقط ما يفعله SQL، ولكن لماذا

إدارة تبعيات الإجراءات المخزنة

لا توجد الإجراءات المخزنة بمعزل عن غيرها. فالإجراء الذي يقرأ من خمسة جداول، ويستدعي إجراءين آخرين، ويتم استدعاؤه بواسطة عشرات خدمات التطبيق، هو مكون ذو تبعيات معقدة في جميع الاتجاهات الثلاثة: ما يعتمد عليه، وما يعتمد عليه، وما يشترك فيه مع الإجراءات الأخرى.

عند تغيير نوع عمود في جدول، يجب اختبار جميع الإجراءات التي تشير إلى هذا العمود. وعند تغيير تنسيق مخرجات إجراء ما، يجب التحقق من صحة جميع الجهات المستدعِية له. وعند النظر في تعديل إجراء ما، تحدد مجموعة الجهات المستدعِية له نطاق التغيير واختبارات التراجع المطلوبة.

أنواع التبعيات المهمة:

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

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

كيفية SMART TS XL يدير تبعيات الإجراءات المخزنة على نطاق المؤسسة

SMART TS XLالصورة تحليل الكود الثابت يقوم هذا البرنامج بتحليل الإجراءات المخزنة في لغة SQL بالتزامن مع برامج COBOL وخدمات Java وخطوط أنابيب Python والمكونات الأخرى التي تتفاعل مع قاعدة البيانات نفسها. ينتج عن هذا التحليل الموحد نموذج هيكلي متعدد اللغات: لا يقتصر على تبعيات SQL داخل قاعدة البيانات فحسب، بل يشمل السلسلة الكاملة من كود التطبيق مرورًا بالإجراءات المخزنة وصولًا إلى الجداول الأساسية وبالعكس.

استخدم رسم خرائط تبعية التطبيق تُنشئ هذه الخاصية مخططًا كاملاً للمستدعي: أي برامج COBOL تستخدم لغة SQL المضمنة التي تقرأ من الجداول المملوكة للإجراءات المخزنة، وأي خدمات Java تستدعي الإجراءات المخزنة عبر JDBC، وأي مهام JCL الدفعية تستدعي أدوات قاعدة البيانات التي تُشغّل الإجراءات المخزنة. عند تغيير توقيع أو سلوك إجراء مخزن، تُظهر خريطة التبعية كل مستدعي عبر جميع اللغات، والنطاق الكامل لما يجب اختباره قبل تطبيق التغيير في بيئة الإنتاج.

استخدم تحليل الأثر تتيح هذه الإمكانية إمكانية تنفيذ خريطة التبعية هذه لتخطيط التغيير: اقترح تغييرًا على CalculateOrderTotal ويتلقى النظام قائمة مفصلة بكل مكون يستدعيه، وكل جدول يقرأه ويكتب فيه، وكل إجراء لاحق يستدعيه. هذا يحوّل سؤال "ما الذي سيتعطل؟" من مجرد معرفة غير مؤكدة إلى تقرير نطاق منظم قائم على الأدلة.

استخدم البحث عن الشركات تتيح هذه الخاصية إمكانية الاستعلام عن نموذج التبعية بالكامل: العثور على كل إجراء مخزن يقرأ من Ordersكل متصل بـ GetCustomerOrders، كل إجراء يقوم بتعديل عمود معين، بالثواني، عبر مجموعة قواعد بيانات من أي حجم.

لسلوك الفرق تحديث التراث البرامج التي تحتوي على إجراءات مخزنة تشفر عقودًا من منطق الأعمال الذي يجب الحفاظ عليه أثناء عملية الترحيل، SMART TS XLيوفر تحليل 's الوثائق الهيكلية التي تجعل المنطق قابلاً للاستخراج وتسلسل الهجرة قابلاً للتخطيط.

طبقة قاعدة البيانات التي تستحق بقاءها

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

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