SQL Server / المتوسط / الدرس 11
الإجراءات المخزنة والمعلمات المحددة النوع
أنشئ واجهة قاعدة بيانات ذات نتائج وسلوك أخطاء متوقعين، وميّز SQL الديناميكي ذي المعلمات من بناء النصوص غير الآمن.
في هذه الصفحة
أهداف الدرس
- وصف عقود مدخلات الإجراء ومخرجاته ومعاملاته.
- استخدام sp_executesql بقيم مرتبطة.
- شرح متطلبات الدفعات وحساسية المعلمات.
عامل الإجراء كواجهة برمجية
يجمع الإجراء المخزن T-SQL خلف واجهة مسماة. حدد أنواع المدخلات والقيم الافتراضية الاختيارية وأعمدة النتيجة وترتيبها ومعلمات الإخراج وسلوك الأخطاء قبل التنفيذ. ليست قيمة حالة الإرجاع الصحيحة مجموعة نتائج أو معلمة إخراج. قد يكسر تغيير اسم عمود أو إضافة SELECT غير متوقعة مستدعياً يقرأ النتائج بالتتابع.
قد توحّد الإجراءات الصلاحيات والمنطق، لكنها لا تجعل الشيفرة سريعة أو ذرية أو آمنة من الحقن تلقائياً. يظل الإجراء الذي يدمج نص المستدعي في أمر تنفيذي معرضاً للحقن. اجعل المسؤوليات قابلة للمراجعة ووثّق ملكية المعاملة وتجنب نتائج التشخيص الإضافية في مسار نجاح التطبيق.
راع حدود إنشاء الوحدات
يدعم أساس SQL Server 2019 عبارة CREATE OR ALTER PROCEDURE التي ظهرت في SQL Server 2016 SP1. لإنشاء الإجراء متطلبات دفعة؛ تفصل أداة داعمة لـ GO التعريف عن EXEC اللاحقة، بينما يرسل مشغل التطبيق دفعات منفصلة دون GO. يستخدم المثال إجراء مؤقتاً محلياً لإظهار واجهة كاملة دون كائن أعمال دائم.
تمنع SET NOCOUNT ON رسائل أعداد الصفوف المتأثرة، لكنها لا تخفي SELECT ولا تغير معنى @@ROWCOUNT. تمنع أنواع المعلمات الصريحة تأويل تواريخ نصية عرضياً وتوضح حدود المدخلات. تحقق من قواعد المجال مثل الحد الأقصى لحجم الصفحة إضافة إلى النوع؛ فقد يكون int صالحاً لكنه طلب غير معقول.
CREATE PROCEDURE #TrainingDouble @value int
AS
BEGIN
SET NOCOUNT ON;
SELECT @value*2 AS doubled;
END;
GO
EXEC #TrainingDouble @value=6;
DROP PROCEDURE #TrainingDouble;doubled
12تستهلك الأداة GO؛ ويعيد الإجراء عموداً عددياً مسمى واحداً ثم يحذف المستدعي الوحدة المؤقتة.
اربط القيم الديناميكية بصورة صحيحة
تستقبل sp_executesql نص أمر Unicode وسلسلة تعريف معلمات وقيماً مرتبطة منفصلة. لا يجعل استدعاؤها وحده المدخلات المدمجة آمنة. يجب أن يظهر موضع المعلمة في نص ثابت وتُمرر القيمة منفصلة. يعيد المثال اسماً يتضمن اقتباساً مفرداً دون معالجة يدوية للاقتباسات.
لا تقوم معلمات القيم العادية مقام اسم جدول أو عمود أو الكلمتين ASC وDESC. إذا وجب تغيير البنية فاختر من خريطة ثابتة يملكها التطبيق واقتبس المعرفات المعتمدة بصورة صحيحة. تساعد QUOTENAME على تحديد المعرف لكنها ليست سياسة تفويض تجاري. راجع اختيار المعرف وربط القيمة كسؤالين مستقلين.
DECLARE @name nvarchar(40)=N'O''Neil';
EXEC sys.sp_executesql
N'SELECT @n AS display_name, LEN(@n) AS characters;',
N'@n nvarchar(40)', @n=@name;display_name characters
O'Neil 6الاقتباس جزء من النص المرتبط ولا ينهي الأمر الديناميكي ولا يوسعه.
اجعل الأخطاء والملكية متوقعة
يجب أن يوضح إجراء الكتابة هل يبدأ معاملته أو يتطلب معاملة يملكها المستدعي. حافظ على الأخطاء الأصلية باستخدام THROW بعد التنظيف اللازم. ترجم فشل العمل المعروف عند الحد المناسب وأبعد تفاصيل تشخيص القاعدة عن رسائل المستخدم المعتادة. لا تعتمد PRINT قناة أخطاء منظمة موثوقة.
ينبغي ثبات مجموعات النتائج بين مسارات النجاح وأن يكون الفشل واضحاً للمشغل. تحقق من القيم المطلوبة والأطوال والمديات قبل التعديل. قد يكون تحديث الصفر عدم تغيير مشروعاً أو تعارضاً متفائلاً أو كياناً غائباً؛ اختر المعنى ووثّقه. لا يخبر اسم الإجراء وحده المستدعي أي تفسير ينطبق.
راجع إعادة الاستخدام دون وعد بخطة مثالية
تشجع العبارات الثابتة ذات المعلمات إعادة استخدام الخطط وتقلل الترجمة المتكررة. قد لا تناسب خطة عُمّلت لعميل قليل الصفوف عميلاً له ملايينها. حساسية المعلمة مشكلة توزيع وليست سبباً لدمج القيم نصياً. قارن فئات معلمات ممثلة وأدلة تنفيذ قبل إضافة RECOMPILE أو غيره من التلميحات.
افصل نصوص النشر عن استدعاءات التشغيل وتتبع إصدارات الواجهة واختبر المستدعين على شكل النتيجة الدقيق. اختبر NULL والحدود والاقتباس والنص متعدد اللغات. تميز دلالات الإجراءات وحدود الدفعات وربط الأنواع في SQL Server هذه الواجهة عن مقاطع SQL عامة منسوخة من منتج آخر.
الأخطاء الشائعة وإصلاحها
Incorrect syntax near GO in application code.
- لماذا يحدث؟
- أُرسل فاصل نشر كصياغة خادم.
- كيف تصلحه؟
- أرسل تعريف الإجراء وتنفيذه كأمرين منفصلين.
- كيف تتجنب تكراره؟
- افصل أدوات النصوص عن واجهات التشغيل.
Parameterized procedure remains injectable.
- لماذا يحدث؟
- يدمج الإجراء معلمة داخل نص تنفيذي.
- كيف تصلحه؟
- استخدم مواضع معلمات وقيم sp_executesql مرتبطة مستقلة.
- كيف تتجنب تكراره؟
- راجع حد التنفيذ النهائي.
Caller reads the wrong result set.
- لماذا يحدث؟
- غيرت SELECT تشخيصية تسلسل النتائج.
- كيف تصلحه؟
- أزل النتائج التشخيصية وتتبع عقد الواجهة.
- كيف تتجنب تكراره؟
- اختبر عدد النتائج وأعمدتها وأنواعها.
اختبر فهمك
1. هل يستبدل @sortColumn اسم عمود في ORDER BY @sortColumn؟
اعرض الحل المشروح
يرتب ذلك بقيمة معلمة لا بمعرف عمود مختار. اختر مقاطع SQL معتمدة أو تصميم CASE مراجعاً، وأبق قيم الشروط مرتبطة مستقلة.
2. يستقبل إجراء اسماً يحتوي اقتباساً. هل يحذفه العميل؟
اعرض الحل المشروح
لا؛ اربط النص كاملاً بنوع معلمة Unicode المقصود. تحقق من طول المجال ولا تجعل منع الترقيم بديلاً عن المعلمات. ينبغي أن يعود الاسم بلا تغيير.
أهم ما تعلمته
- الإجراء واجهة بعقد نتائج وأخطاء.
- تؤمن sp_executesql القيم عندما تُربط فعلياً.
- قس حساسية الخطط ولا تتحايل عليها بدمج النصوص.