الإجراء المخزن- هذا نوع خاصحزمة عبارة Transact-SQL تم إنشاؤها باستخدام لغة SQL والامتدادات الإجرائية. يتمثل الاختلاف الرئيسي بين الحزمة والإجراء المخزن في أن الأخير يتم تخزينه ككائن قاعدة بيانات. بمعنى آخر ، يتم تخزين الإجراءات المخزنة على جانب الخادم لتحسين أداء المهام المتكررة واتساقها.
يدعم محرك قاعدة البيانات الإجراءات المخزنة وإجراءات النظام. يتم إنشاء الإجراءات المخزنة بنفس طريقة إنشاء جميع كائنات قاعدة البيانات الأخرى ، أي باستخدام لغة DDL. إجراءات النظاميتم توفيرها بواسطة محرك قاعدة البيانات ويمكن استخدامها للوصول إلى المعلومات وتعديلها في كتالوج النظام.
عند إنشاء إجراء مخزن ، يمكنك تحديد قائمة اختيارية من المعلمات. وبالتالي ، فإن الإجراء سوف يأخذ الحجج المناسبة في كل مرة يتم استدعاؤه. يمكن أن ترجع الإجراءات المخزنة قيمة تحتوي على تعريف المستخدمالمعلومات أو ، في حالة حدوث خطأ ، رسالة الخطأ المقابلة.
يتم إجراء ترجمة مسبقة لإجراء مخزن قبل تخزينه ككائن في قاعدة البيانات. يتم تخزين النموذج المترجم مسبقًا للإجراء في قاعدة البيانات ويستخدم في كل مرة يتم استدعاؤه. توفر خاصية الإجراءات المخزنة هذه فائدة مهمة للتخلص من عمليات إعادة تجميع الإجراءات (في جميع الحالات تقريبًا) والحصول على تحسين الأداء المقابل. هذه الخاصية من الإجراءات المخزنة لها أيضًا تأثير إيجابي على كمية البيانات المتبادلة بين نظام قاعدة البيانات والتطبيقات. على وجه الخصوص ، قد يتطلب استدعاء إجراء مخزن من عدة آلاف بايت أقل من 50 بايت. عندما يقوم العديد من المستخدمين بتنفيذ مهام متكررة باستخدام الإجراءات المخزنة ، يمكن أن يكون التأثير التراكمي لهذه المدخرات كبيرًا.
يمكن أيضًا استخدام الإجراءات المخزنة للأغراض التالية:
لإنشاء سجل من السجلات حول الإجراءات مع جداول قاعدة البيانات.
يوفر استخدام الإجراءات المخزنة مستوى من التحكم الأمني يتجاوز بكثير الأمان الذي يوفره استخدام عبارات GRANT و REVOKE ، والتي تمنح امتيازات وصول مختلفة للمستخدمين. هذا ممكن لأن التخويل لتنفيذ إجراء مخزن مستقل عن التخويل لتعديل الكائنات الموجودة في الإجراء المخزن ، كما هو موضح في القسم التالي.
توفر الإجراءات المخزنة التي تنشئ سجلات لكتابة الجدول و / أو عمليات القراءة فرصة إضافيةأمن قاعدة البيانات. باستخدام مثل هذه الإجراءات ، يمكن لمسؤول قاعدة البيانات تتبع التعديلات التي أدخلها المستخدمون أو التطبيقات على قاعدة البيانات.
إنشاء وتنفيذ الإجراءات المخزنة
يتم إنشاء الإجراءات المخزنة باستخدام العبارة إنشاء إجراء، والتي لها بناء الجملة التالي:
إنشاء PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (،…) AS باتش | اسم خارجي أسلوب_اسم اصطلاحات بناء الجملة
تحدد المعلمة schema_name اسم المخطط الذي تم تعيينه بواسطة مالك الإجراء المخزن الذي تم إنشاؤه. تحدد معلمة proc_name اسم الإجراء المخزن. المعلمة @ param1 هي معلمة إجراء (وسيطة رسمية) يتم تحديد نوع بياناتها بواسطة معلمة type1. تكون معلمات الإجراء محلية داخل الإجراء ، تمامًا كما تكون المتغيرات المحلية محلية داخل الحزمة. معلمات الإجراء هي القيم التي يتم تمريرها من قبل المستدعي إلى الإجراء لاستخدامها فيه. تحدد المعلمة default1 القيمة الافتراضية لمعامل الإجراء المقابل. (يمكن أن تكون القيمة الافتراضية أيضًا NULL.)
خيار الإخراجيحدد أن معلمة الإجراء قابلة للإرجاع ويمكن استخدامها لإرجاع قيمة من إجراء مخزن إلى إجراء الاستدعاء أو النظام.
كما ذكرنا سابقًا ، يتم تخزين النموذج المترجم مسبقًا للإجراء في قاعدة البيانات ويتم استخدامه في كل مرة يتم استدعاؤه. إذا احتاج إجراء مخزن ، لسبب ما ، إلى التحويل البرمجي في كل مرة يتم استدعاؤه ، يستخدم إعلان الإجراء الخيار مع RECOMPILE. يؤدي استخدام خيار WITH RECOMPILE إلى إبطال إحدى أهم فوائد الإجراءات المخزنة: تحسين الأداء بسبب التجميع الفردي. لذلك ، يجب استخدام الخيار WITH RECOMPILE فقط في حالة وجود تغييرات متكررة على كائنات قاعدة البيانات المستخدمة بواسطة الإجراء المخزن.
التنفيذ كعرضيعرّف سياق الأمان الذي يجب تنفيذ الإجراء المخزن فيه بعد استدعائه. من خلال تعيين هذا السياق ، يمكن لمحرك قاعدة البيانات التحكم في تحديد حسابات المستخدمين للتحقق من أذونات الوصول للكائنات المشار إليها بواسطة هذا الإجراء المخزن.
بشكل افتراضي ، يمكن فقط لأعضاء دور خادم مسؤول النظام الثابت ودور قاعدة البيانات الثابتة db_owner أو db_ddladmin استخدام عبارة CREATE PROCEDURE. ومع ذلك ، يمكن لأعضاء هذه الأدوار منح هذا الحق للمستخدمين الآخرين باستخدام التعليمات إجراء منح المنح.
يوضح المثال أدناه كيفية إنشاء إجراء مخزن بسيط للعمل مع جدول المشروع:
USE SampleDb ؛ الانتقال إلى إجراء إنشاء زيادة الميزانية (percent INT = 5) AS UPDATE Project SET Budget = Budget + Budget * @ percent / 100؛
كما ذكرنا سابقًا ، يتم استخدام لفصل حزمتين تعليمات GO. لا يمكن دمج عبارة CREATE PROCEDURE مع عبارات SQL للعمليات الأخرى في نفس الدفعة. يؤدي إجراء زيادة الميزانية المخزنة إلى زيادة الميزانيات لجميع المشاريع بنسبة معينة ، يتم تحديدها بواسطة المعلمةpercent. يحدد الإجراء أيضًا قيمة النسبة المئوية الافتراضية (5) التي يتم تطبيقها إذا لم تكن هذه الوسيطة موجودة أثناء تنفيذ الإجراء.
يمكن للإجراءات المخزنة الوصول إلى جداول غير موجودة. تتيح لك هذه الخاصية تصحيح أخطاء رمز الإجراء دون إنشاء الجداول المناسبة أولاً ودون الاتصال بالخادم الوجهة.
على عكس الإجراءات المخزنة الأساسية ، والتي يتم تخزينها دائمًا في قاعدة البيانات الحالية ، من الممكن إنشاء إجراءات مخزنة مؤقتة ، والتي يتم وضعها دائمًا في قاعدة بيانات النظام المؤقتة. قد يكون أحد أسباب إنشاء إجراءات مخزنة مؤقتة هو تجنب التنفيذ المتكرر لمجموعة معينة من العبارات عند الاتصال بقاعدة بيانات. يمكنك إنشاء إجراءات مؤقتة محلية أو عالمية. للقيام بذلك ، يتم تحديد اسم الإجراء المحلي بحرف # واحد (#proc_name) ، ويتم تحديد اسم الإجراء العام بحرف مزدوج (## proc_name).
لا يمكن تنفيذ إجراء محلي مؤقت مخزن إلا بواسطة المستخدم الذي قام بإنشائه ، وخلال الاتصال بقاعدة البيانات التي تم إنشاؤه فيها فقط. يمكن لجميع المستخدمين تنفيذ إجراء مؤقت عام ، ولكن فقط حتى يتم إنهاء الاتصال الأخير الذي يتم تشغيله عليه (عادةً ما يكون اتصال منشئ الإجراء).
تتكون دورة حياة الإجراء المخزن من مرحلتين: إنشائه وتنفيذه. يتم إنشاء كل إجراء مرة واحدة ويتم تنفيذه عدة مرات. يتم تنفيذ الإجراء المخزن بواسطة عبارات التنفيذمستخدم يمتلك الإجراء أو لديه حق التنفيذ للوصول إلى الإجراء. تحتوي جملة EXECUTE على بناء الجملة التالي:
[] [return_status =] (proc_name |proc_name_var) ([[@ parameter1 =] value | [@ parameter1 =]variable] | DEFAULT) .. اصطلاحات التركيب
باستثناء معلمة return_status ، فإن جميع معلمات عبارة EXECUTE لها نفس القيمة المنطقية كمعلمات بيان CREATE PROCEDURE الذي يحمل نفس الاسم. تحدد معلمة return_status متغير عدد صحيح يخزن حالة إرجاع الإجراء. يمكن تعيين قيمة إلى معلمة باستخدام إما ثابت (قيمة) أو متغير محلي (@ متغير). ترتيب قيم المعلمات المسماة ليس مهمًا ، ولكن يجب توفير قيم المعلمات غير المسماة بالترتيب الذي تم تحديدها به في عبارة CREATE PROCEDURE.
شرط افتراضييوفر القيم الافتراضية لمعلمة الإجراء التي تم تحديدها في تعريف الإجراء. عندما يتوقع إجراء قيمة لمعلمة لم يتم تحديد قيمة افتراضية لها والمعلمة مفقودة ، أو كلمة رئيسيةافتراضي ، ثم يحدث خطأ.
عندما تكون عبارة EXECUTE هي العبارة الأولى في الدُفعة ، يمكن حذف الكلمة الأساسية EXECUTE. ومع ذلك ، فمن الأكثر أمانًا تضمين هذه الكلمة في كل حزمة. يظهر استخدام عبارة EXECUTE في المثال أدناه:
USE SampleDb ؛ تنفيذ الزيادة في الميزانية 10 ؛
تقوم جملة EXECUTE في هذا المثال بتنفيذ إجراء زيادة الميزانية المخزنة ، مما يؤدي إلى زيادة ميزانية كافة المشروعات بنسبة 10٪.
يوضح المثال التالي كيفية إنشاء إجراء مخزن لمعالجة البيانات في جدولي الموظف و Works_on:
يوضح الإجراء ModifyEmpId في المثال استخدام الإجراءات المخزنة كجزء من عملية التكامل المرجعي (في هذه الحالة بين جدولي الموظف و Works_on). يمكن استخدام مثل هذا الإجراء المخزن داخل تعريف المشغل ، والذي يفرض بالفعل التكامل المرجعي.
يوضح المثال التالي استخدام جملة OUTPUT في إجراء مخزن:
يمكن تنفيذ هذا الإجراء المخزن باستخدام العبارات التالية:
إعلانquantityDeleteEmployee INT ؛ تنفيذ DeleteEmployee @ empId = 18316،OUTPUT ؛ PRINT N "تم حذف الموظفين:" + تحويل (nvarchar (30) ،quantityDeleteEmployee) ؛
يحسب هذا الإجراء عدد المشاريع التي يعمل عليها موظف لديه رقم موظفينempId ويعين القيمة الناتجة لمعامل عداد ©. بعد حذف جميع الصفوف لرقم موظف معين من جدولي الموظف و Works_on ، يتم تعيين القيمة المحسوبة إلى متغيرquantityDeleteEmployee.
يتم إرجاع قيمة المعلمة إلى إجراء الاستدعاء فقط إذا تم تحديد خيار الإخراج. في المثال أعلاه ، يمرر الإجراء DeleteEmployee المعلمةcounter إلى إجراء الاستدعاء ، لذلك يقوم الإجراء المخزن بإرجاع القيمة إلى النظام. لذلك ، يجب تحديد المعلمةcounter في كل من خيار OUTPUT عند إعلان الإجراء ، وفي عبارة EXECUTE عند استدعائها.
جملة WITH RESULTS SETS من عبارة EXECUTE
في خادم قاعدة البياناتتم تقديم 2012 لبيان التنفيذ WITH RESULTS SETS بنديمكن ، في ظل ظروف معينة ، تغيير شكل مجموعة نتائج الإجراء المخزن.
سيساعد المثالان التاليان في شرح هذه الجملة. المثال الأول هو مثال تمهيدي يوضح الشكل الذي قد تبدو عليه النتيجة عند حذف جملة WITH RESULTS SETS:
يُعد إجراء EmployeesInDept إجراءً بسيطًا يعرض أرقام الموظفين وأسماء العائلة لجميع الموظفين العاملين في قسم معين. رقم القسم هو معلمة إجراء ويجب تحديده عند استدعاء الإجراء. يؤدي تنفيذ هذا الإجراء إلى إخراج جدول به عمودين تتطابق عناوينهما مع أسماء الأعمدة المقابلة في جدول قاعدة البيانات ، أي المعرف والاسم الأخير. لتغيير رؤوس أعمدة النتائج (بالإضافة إلى نوع البيانات الخاصة بها) ، يستخدم SQL Server 2012 جملة WITH RESULTS SETS الجديدة. يظهر تطبيق هذا البند في المثال أدناه:
USE SampleDb ؛ EXEC EmployeesInDept "d1" WITH RESULT SETS ((INT NOT NULL، [Last Name] CHAR (20) NOT NULL)) ؛
ستكون نتيجة تنفيذ إجراء مخزن يسمى بهذه الطريقة كما يلي:
كما ترى ، فإن تشغيل إجراء مخزن باستخدام جملة WITH RESULT SETS في عبارة EXECUTE يسمح لك بتغيير الأسماء ونوع البيانات لأعمدة مجموعة النتائج التي ينتجها الإجراء. وبالتالي ، توفر هذه الوظيفة الجديدة مزيدًا من المرونة في تنفيذ الإجراءات المخزنة ووضع نتائجها في جدول جديد.
تغيير هيكل الإجراءات المخزنة
كما يدعم مشغل قاعدة البيانات العبارة تغيير الإجراءلتعديل هيكل الإجراءات المخزنة. عادةً ما يتم استخدام عبارة ALTER PROCEDURE لتعديل عبارات SQL للعمليات داخل إجراء. جميع معلمات عبارة ALTER PROCEDURE لها نفس المعنى مثل معلمات عبارة CREATE PROCEDURE التي تحمل الاسم نفسه. الغرض الأساسي من استخدام هذه العبارة هو تجنب تجاوز أذونات الإجراءات المخزنة الموجودة.
يدعم محرك قاعدة البيانات نوع بيانات CURSOR. يتم استخدام نوع البيانات هذا للإعلان عن المؤشرات في الإجراءات المخزنة. المؤشرهو بناء برمجة يستخدم لتخزين نتائج الاستعلام (عادة مجموعة من الصفوف) وللسماح للمستخدمين بعرض تلك النتيجة صفًا بصف.
لإزالة واحد أو مجموعة من الإجراءات المخزنة ، استخدم بيان إجراء السقوط. يمكن فقط لمالك الإجراء المخزن أو أعضاء الأدوار الثابتة db_owner و sysadmin حذف إجراء مخزن.
الإجراءات المخزنة ووقت تشغيل اللغة العامة
يدعم SQL Server وقت تشغيل اللغة العامة (CLR) الذي يسمح لك بتطوير كائنات قاعدة البيانات المختلفة (الإجراءات المخزنة ، والوظائف المعرفة من قبل المستخدم ، والمشغلات المعرفة من قبل المستخدم). وظائف إحصائيةوأنواع البيانات المعرفة من قبل المستخدم) ، باستخدام C # و البصرية الأساسية. يسمح وقت تشغيل اللغة العامة أيضًا بتنفيذ هذه الكائنات باستخدام نظام وقت التشغيل المشترك.
يتم تمكين وقت تشغيل اللغة العامة وتعطيله عبر الخيار clr_enabledإجراء النظام sp_configure، والتي يتم إطلاقها للتنفيذ من خلال التعليمات إعادة التشكيل. يوضح المثال التالي كيف يمكنك تمكين وقت تشغيل اللغة العامة باستخدام إجراء نظام sp_configure:
USE SampleDb ؛ EXEC sp_configure "clr_enabled" ، 1 RECONFIGURE
يتطلب إنشاء إجراء وترجمته وحفظه باستخدام CLR التسلسل التالي من الخطوات ، بالترتيب المدرج:
قم بإنشاء إجراء مخزن في C # أو Visual Basic ، ثم قم بترجمته باستخدام المحول البرمجي المناسب.
باستخدام التعليمات إنشاء التجميع، قم بإنشاء الملف القابل للتنفيذ المناسب.
تنفيذ إجراء باستخدام عبارة EXECUTE.
يوضح الشكل أدناه مخططًا رسوميًا للخطوات الموضحة مسبقًا. التالي هو أكثر وصف مفصلهذه العملية.
قم أولاً بإنشاء البرنامج المطلوب في بعض بيئة التطوير ، على سبيل المثال استوديو مرئي. تجميع البرنامج النهائيلكائن التعليمات البرمجية باستخدام مترجم C # أو Visual Basic. يتم تخزين هذه التعليمات البرمجية في ملف مكتبة الارتباط الديناميكي (.dll) الذي يعمل كمصدر لعبارة إنشاء التجميع ، والتي تقوم بإنشاء تعليمات برمجية وسيطة قابلة للتنفيذ. بعد ذلك ، قم بإصدار عبارة CREATE PROCEDURE لحفظ التعليمات البرمجية التي يتم تنفيذها ككائن قاعدة بيانات. أخيرًا ، قم بتشغيل الإجراء باستخدام عبارة EXECUTE المألوفة.
يوضح المثال أدناه التعليمات البرمجية المصدر للإجراء المخزن في C #:
باستخدام System.Data.SqlClient ؛ باستخدام Microsoft.SqlServer.Server ؛ فئة جزئية عامة StoredProcedures (public static int CountEmployees () (الصفوف int ؛ SqlConnection connect = new SqlConnection ("Context Connection = true")؛ connection.Open ()؛ SqlCommand cmd = connection.CreateCommand ()؛ cmd.CommandText = "حدد عد (*) كـ "عدد الموظفين" "+" من الموظف "؛ الصفوف = (int) cmd.ExecuteScalar () ؛ connection.Close () ؛ إرجاع الصفوف ؛))
يقوم هذا الإجراء بتنفيذ استعلام لحساب عدد الصفوف في جدول الموظف. باستخدام التوجيهات في بداية البرنامج ، حدد مساحات الأسماء المطلوبة لتنفيذه. يسمح لك استخدام هذه التوجيهات بالتحديد في مصدر الرمزأسماء الفئات دون تحديد مساحات الأسماء المقابلة صراحة. بعد ذلك ، يتم تحديد فئة StoredProcedures ، والتي من أجلها سمة SqlProcedure، والتي تُعلم المترجم أن هذه الفئة عبارة عن إجراء مخزن. داخل رمز الفصل الدراسي ، يتم تحديد طريقة CountEmployees (). يتم إنشاء الاتصال بنظام قاعدة البيانات من خلال مثيل للفئة اتصال SQL. لفتح اتصال ، يتم استخدام طريقة Open () لهذا المثيل. أ طريقة CreateCommand ()يسمح لك بالوصول إلى مثيل لفئة SqlCommnd، والتي يتم تمرير أمر SQL المطلوب إليها.
في مقتطف الشفرة التالي:
Cmd.CommandText = "حدد العدد (*) كـ" عدد الموظفين "" + "من الموظف"؛
يستخدم عبارة SELECT لحساب عدد الصفوف في جدول الموظف وعرض النتيجة. يتم تحديد نص الأمر عن طريق تعيين الخاصية CommandText لمتغير cmd إلى المثيل الذي تم إرجاعه بواسطة أسلوب CreateCommand (). التالي يسمى طريقة ExecuteScalar ()مثيل SqlCommand. تقوم هذه الطريقة بإرجاع قيمة عددية تم تحويلها إلى نوع عدد صحيح int البيانات وتخصيصها لمتغير الصفوف.
يمكنك الآن تجميع هذا الرمز باستخدام Visual Studio. لقد أضفت هذه الفئة إلى المشروع باسم CLRStoredProcedures ، لذلك سيقوم Visual Studio بتجميع التجميع الذي يحمل نفس الاسم بامتداد * .dll. يوضح المثال أدناه الخطوة التالية في إنشاء إجراء مخزن: إنشاء التعليمات البرمجية للتشغيل. قبل تنفيذ التعليمات البرمجية في هذا المثال ، تحتاج إلى معرفة موقع ملف dll المترجم (الموجود عادةً في مجلد تصحيح المشروع).
USE SampleDb ؛ انتقل إلى إنشاء تجميع إجراءات CLRStoredProcedures من "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" باستخدام PERMISSION_SET = SAFE
تأخذ جملة CREATE ASSEMBLY التعليمات البرمجية المدارة كمدخلات وتنشئ كائنًا مناسبًا يمكنك من أجله إنشاء إجراءات مخزنة لوقت تشغيل اللغة العامة (CLR) ووظائف محددة من قبل المستخدم ومشغلات. تحتوي هذه التعليمات على النحو التالي:
إنشاء اسم التجميع [اسم_المالك التفويض] من (ملف_ dll) اصطلاحات بناء الجملة
تحدد معلمة اسم التجميع اسم التجميع. يحدد بند التفويض الاختياري اسم الدور كمالك لهذا التجمع. تحدد عبارة FROM المسار حيث يوجد التجميع المراد تحميله.
عبارة WITH PERMISSION_SETهي عبارة مهمة جدًا في عبارة إنشاء التجميع ويجب تحديدها دائمًا. وهي تحدد مجموعة حقوق الوصول الممنوحة لكود التجميع. مجموعة الحقوق الآمنة هي الأكثر تقييدًا. لا يمكن لرمز التجميع الذي لديه هذه الحقوق الوصول إلى موارد النظام الخارجية مثل الملفات. تسمح مجموعة حقوق EXTERNAL_ACCESS لكود التجميع بالوصول إلى بعض موارد النظام الخارجية ، بينما توفر مجموعة حقوق UNSAFE وصولاً غير مقيد إلى الموارد ، داخل وخارج نظام قاعدة البيانات.
لتخزين معلومات رمز التجميع ، يجب أن يكون المستخدم قادراً على إصدار عبارة "إنشاء تجميع". التجميع مملوك للمستخدم (أو الدور) الذي ينفذ التعليمات. يمكنك تغيير مالك التجميع باستخدام بند التفويض الخاص ببيان CREATE SCHEMA.
يدعم مشغل قاعدة البيانات أيضًا عبارات ALTER ASSEMBLY و DROP ASSEMBLY. تعديل بيان التجميعتستخدم لتحديث البناء إلى احدث اصدار. تضيف هذه التعليمات أيضًا أو تزيل الملفات المرتبطة بالتجميع المقابل. بيان التجميعيزيل التجميع المحدد وجميع الملفات المرتبطة من قاعدة البيانات الحالية.
يوضح المثال التالي كيفية إنشاء إجراء مخزن بناءً على التعليمات البرمجية المُدارة التي تم تنفيذها مسبقًا:
USE SampleDb ؛ انتقل إلى إنشاء عدد الإجراءات الموظفون باسم خارجي CLR إجراءات مخزنة.
تختلف عبارة CREATE PROCEDURE في المثال عن نفس العبارة في الأمثلة السابقة من حيث أنها تحتوي على معلمة الاسم الخارجي. يحدد هذا الخيار أن الكود تم إنشاؤه بواسطة CLR. يتكون الاسم في هذه الجملة من ثلاثة أجزاء:
اسم التجميع
اسم التجميع - يحدد اسم التجميع ؛
class_name - تحدد اسم الفئة العامة ؛
اسم_الطريقة - جزء اختياري ، يحدد اسم الطريقة التي تم تعيينها داخل الفئة.
يظهر تنفيذ إجراء CountEmployees في المثال أدناه:
USE SampleDb ؛ إعلانcount INT تنفيذcount = CountEmployees طباعةcount - إرجاع 7
تُرجع عبارة PRINT العدد الحالي للصفوف في جدول الموظف.
الإجراءات المخزنة
يعد موضوع هذا الفصل أحد أقوى الأدوات المقدمة لمطوري تطبيقات قاعدة بيانات InterBase لتنفيذ منطق الأعمال. تسمح لك الإجراءات المخزنة (اللغة الإنجليزية ، الإجراءات الرتيبة) بتنفيذ جزء كبير من منطق التطبيق على مستوى قاعدة البيانات وبالتالي زيادة أداء التطبيق بأكمله ، وجعل معالجة البيانات مركزية وتقليل مقدار الكود المطلوب لإنجاز المهام المطروحة تقريبًا يتطلب أي تطبيق قاعدة بيانات معقد بدرجة كافية استخدام الإجراءات المخزنة.
بالإضافة إلى هذه الفوائد المعروفة لاستخدام الإجراءات المخزنة ، والتي تعتبر شائعة في معظم أنظمة قواعد البيانات العلائقية ، يمكن أن تلعب إجراءات InterBase المخزنة دور مجموعات البيانات شبه الكاملة ، مما يسمح لك باستخدام النتائج التي يتم إرجاعها في استعلامات SQL العادية.
غالبًا ما يفكر المطورون المبتدئون في الإجراءات المخزنة على أنها مجرد مجموعة من استعلامات SQL المحددة التي تقوم بشيء ما داخل قاعدة البيانات ، وهناك رأي مفاده أن العمل مع الإجراءات المخزنة أصعب بكثير من تنفيذ نفس الوظيفة في تطبيق العميل ، في اللغة مستوى عال
إذن ما هي بالضبط الإجراءات المخزنة في InterBase؟
الإجراء المخزن (SP) هو جزء من البيانات الوصفية لقاعدة البيانات ، وهو عبارة عن روتين فرعي تم تجميعه في التمثيل الداخلي لـ InterBase ، مكتوبًا بلغة خاصة ، تم تضمين مترجمها في قلب خادم InteiBase
يمكن استدعاء إجراء مخزن من تطبيقات العميل ومن المشغلات ومن الإجراءات المخزنة الأخرى. يتم تنفيذ الإجراء المخزن داخل عملية الخادم ويمكنه معالجة البيانات الموجودة في قاعدة البيانات ، وكذلك إعادة نتائج تنفيذه إلى العميل الذي أطلق عليه (أي المشغل ، HP ، التطبيق)
أساس الإمكانات القوية الكامنة في HP هي لغة البرمجة الإجرائية ، والتي تتضمن كلاً من عبارات SQL العادية المعدلة ، مثل INSERT و UPDATE و SELECT ، بالإضافة إلى أدوات التفرع والتكرار (IF ، WHILE) ، بالإضافة إلى أدوات معالجة الأخطاء . وحالات استثنائية تسمح لك لغة الإجراءات المخزنة بتنفيذ خوارزميات معقدة للعمل مع البيانات ، وبسبب التركيز على العمل مع البيانات العلائقية ، فإن SPs أكثر إحكاما من الإجراءات المماثلة في اللغات التقليدية.
وتجدر الإشارة إلى أن لغة البرمجة نفسها تُستخدم في المشغلات ، باستثناء عدد من الميزات والقيود. تمت مناقشة الاختلافات بين مجموعة فرعية من اللغة المستخدمة في المشغلات ولغة HP بالتفصيل في فصل المشغلات (الجزء 1).
مثال على إجراء مخزن بسيط
حان الوقت لإنشاء أول إجراء مخزن واستخدامه كمثال لدراسة عملية إنشاء الإجراءات المخزنة. لكن أولاً ، يجب أن تُقال بضع كلمات حول كيفية العمل مع الإجراءات المخزنة ، والحقيقة هي أن HP تدين بشهرتها كأداة غامضة وغير مريحة لأدوات قياسية سيئة للغاية لتطوير الإجراءات المخزنة وتصحيحها. في وثائق InterBase ، يوصى بإنشاء إجراءات باستخدام ملفات البرنامج النصي SQL التي تحتوي على نص CP ، والتي يتم تغذيتها لمترجم isql ، وبالتالي إنشاء وتعديل CP. في حالة حدوث خطأ ، سيعرض isql رسالة عليها سطر من ملف البرنامج النصي SQL حدث الخطأ. صحح الخطأ وكرر الأمر من جديد. حول التصحيح الفهم الحديثهذه الكلمة ، أي تتبع التنفيذ ، مع القدرة على رؤية القيم الوسيطة للمتغيرات ، ليست سؤالًا على الإطلاق. من الواضح أن هذا النهج لا يساهم في نمو جاذبية الإجراءات المخزنة في نظر المطور.
ومع ذلك ، بالإضافة إلى نهج الحد الأدنى القياسي لتطوير HP<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
يتم وصف بناء جملة الإجراءات المخزنة على النحو التالي:
إنشاء اسم الإجراء
[(param datatype [، param datatype ...])]
)]
مثل
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
إعلان متغير نوع بيانات var ؛
يبدأ
< compound_statement>
[< compound_statement> ...]
نهاية
< compound_statement> =
(
إنها تبدو ضخمة جدًا ويمكن أن تكون مرهقة ، ولكن في الواقع كل شيء بسيط للغاية.من أجل إتقان بناء الجملة بشكل تدريجي ، دعونا ننظر في أن تصبح تدريجياً أمثلة أكثر تعقيدًا.
إذن ، هذا مثال على إجراء مخزن بسيط للغاية يأخذ رقمين كمدخلات ويجمعهما ويعيد النتيجة:
إنشاء إجراء SP_Add (أول_ دقة مضاعفة ،
second_arg دقة مزدوجة)
العوائد (نتيجة الدقة المزدوجة)
مثل
يبدأ
النتيجة = first_arg + second_arg ؛
تعليق؛
نهاية
كما ترى ، كل شيء بسيط: بعد الأمر CREATE PROCEDURE ، يشار إلى اسم الإجراء الذي تم إنشاؤه حديثًا (والذي يجب أن يكون فريدًا داخل قاعدة البيانات) - في هذه الحالة ، SP_Add ، ثم معلمات إدخال XP - first_arg و second_arg - يتم سردها بين أقواس مفصولة بفواصل - للإشارة إلى أنواعها.
تعد قائمة معلمات الإدخال جزءًا اختياريًا من عبارة CREATE PROCEDURE - هناك حالات عندما يتلقى الإجراء جميع البيانات الخاصة بعمله من خلال الاستعلامات إلى الجداول داخل نص الإجراء.
تستخدم الإجراءات المخزنة أي أنواع من أنواع البيانات العددية InteiBase لا يوجد استخدام للصفائف وأنواع محددة من قبل المستخدم - المجالات
بعد ذلك تأتي الكلمة الرئيسية RETURNS ، وبعد ذلك يتم سرد معلمات الإرجاع بين قوسين ، للإشارة إلى أنواعها - في هذه الحالة ، واحدة فقط - النتيجة.
إذا لم يكن من المفترض أن يؤدي الإجراء إلى إرجاع المعلمات ، فإن كلمة RETURNS وقائمة المعلمات التي تم إرجاعها مفقودة.
يتبع RETURNSQ الكلمة الأساسية AS. قبل أن تذهب الكلمة الأساسية AS عنوان،وبعدها - تيكوإجراءات.
نص الإجراء المخزن عبارة عن قائمة بإعلانات المتغيرات الداخلية (المحلية) الخاصة به (إن وجدت ، تمت مناقشتها بمزيد من التفصيل أدناه) ، مفصولة بفاصلة منقوطة (؛) ، وكتلة من العبارات محاطة بأقواس عبارة BEGIN END. في هذه الحالة ، يكون جسم CP بسيطًا جدًا - نطلب إضافة وسيطتي إدخال وتعيين نتيجتهما إلى المخرجات ، ثم استدعاء الأمر SUSPEND. بعد ذلك بقليل ، سنشرح جوهر إجراء هذا الأمر ، ولكن في الوقت الحالي سنلاحظ فقط أنه يلزم تمرير معلمات الإرجاع إلى المكان الذي تم استدعاء الإجراء المخزن منه.
فواصل في الإجراءات المخزنة
لاحظ أن العبارة الموجودة داخل الإجراء تنتهي بفاصلة منقوطة (؛). كما تعلم ، فإن الفاصلة المنقوطة هي فاصل الأوامر القياسي في SQL - إنها إشارة لمترجم SQL بأن نص الأمر قد تم إدخاله بالكامل ويجب معالجته. ألن يتضح أنه بعد العثور على فاصلة منقوطة في منتصف SP ، سوف يعتبر مترجم SQL أن الأمر قد تم إدخاله بالكامل وسيحاول تنفيذ جزء من الإجراء المخزن؟ هذا الافتراض لا يخلو من معنى. في الواقع ، إذا أنشأت ملفًا تكتب فيه المثال أعلاه ، وأضف أمر اتصال قاعدة البيانات وحاول تنفيذ نص SQL هذا باستخدام مترجم isql ، فسيتم إرجاع خطأ متعلق بما هو غير متوقع ، وفقًا للمترجم ، نهاية الأمر لإنشاء إجراء مخزن. إذا قمت بإنشاء إجراءات مخزنة باستخدام ملفات نصية SQL ، دون استخدام أدوات مطور InterBase المتخصصة ، فيجب عليك تغيير فاصل أوامر البرنامج النصي إلى حرف آخر غير فاصلة منقوطة بعد نص HP استعادته مرة أخرى. يبدو الأمر isql الذي يغير فاصل جملة SQL كما يلي:
اضبط المدة
بالنسبة للحالة النموذجية لإنشاء إجراء مخزن ، يبدو الأمر كما يلي:
تعيين المدة ^ ؛
إنشاء إجراء some_procedure
... . .
نهاية
^
اضبط المدة ؛ ^
استدعاء إجراء مخزن
لكن العودة إلى إجراءاتنا المخزنة. الآن وقد تم إنشاؤه ، نحتاج إلى تسميته بطريقة ما ، وتمرير المعلمات إليه ، والحصول على النتائج التي تم إرجاعها. من السهل جدًا القيام بذلك - ما عليك سوى كتابة استعلام SQL بالشكل التالي:
يختار *
من Sp_add (181.35 ، 23.09)
سيعيد لنا هذا الاستعلام صفًا واحدًا يحتوي على حقل نتيجة واحد فقط ، والذي سيحتوي على مجموع الأرقام 181.35 و 23.09 ، أي 204.44.
وبالتالي ، يمكن استخدام الإجراء الخاص بنا في استعلامات SQL العادية التي يتم تنفيذها في كل من برامج العميل وفي برامج الخدمة SP أو المشغلات الأخرى. أصبح هذا الاستخدام للإجراء الخاص بنا ممكنًا عن طريق استخدام الأمر SUSPEND في نهاية الإجراء المخزن.
الحقيقة هي أنه في InterBase (وفي جميع نسخه) يوجد نوعان من الإجراءات المخزنة: الإجراءات القابلة للتحديد والإجراءات القابلة للتنفيذ. يتمثل الاختلاف في تشغيل هذين النوعين من CPs في أن إجراءات الاختيار عادةً ما تُرجع العديد من مجموعات معلمات الإخراج ، مجمعة صفًا بصف ، والتي تبدو كمجموعة بيانات ، والإجراءات القابلة للتنفيذ إما لا يمكنها إرجاع معلمات على الإطلاق ، أو إرجاع واحدة فقط مجموعة من معلمات الإخراج المدرجة في المرتجعات ، حيث سطر واحد من المعلمات. يتم استدعاء إجراءات التحديد في استعلامات SELECT ، ويتم استدعاء الإجراءات القابلة للتنفيذ باستخدام الأمر EXECUTE PROCEDURE.
كلا النوعين من الإجراءات المخزنة لهما نفس بناء جملة الإنشاء وهما رسميًا متماثلان ، لذلك يمكن استدعاء أي إجراء قابل للتنفيذ في استعلام SELECT ويمكن استدعاء أي إجراء تحديد باستخدام EXECUTE PROCEDURE. السؤال هو كيف ستتصرف HP ومتى أنواع مختلفةيتصل. بمعنى آخر ، يكمن الاختلاف في تصميم الإجراء لنوع معين من المكالمات. أي ، يتم إنشاء إجراء تحديد خصيصًا ليتم استدعاؤه من استعلام SELECT ، ويتم إنشاء إجراء قابل للتنفيذ على وجه التحديد ليتم استدعاؤه باستخدام EXECUTE PROCEDURE. لنلقِ نظرة على الاختلافات في تصميم هذين النوعين من HP.
من أجل فهم كيفية عمل إجراء الاختيار ، عليك التعمق أكثر في النظرية. دعنا نتخيل استعلام SQL عاديًا مثل معرف التحديد ، NAME FROM Table_example. نتيجة لتنفيذه ، حصلنا على جدول يتكون من عمودين (ID و NAME) وعدد معين من الصفوف (يساوي عدد الصفوف في جدول Table_example). يُطلق على الجدول الذي تم إرجاعه نتيجة لهذا الاستعلام أيضًا مجموعة بيانات SQL ، دعنا نفكر في كيفية تكوين مجموعة البيانات أثناء تنفيذ هذا الاستعلام ، بعد أن تلقى الخادم الاستعلام ، يحدد الجداول التي ينتمي إليها ، ثم يكتشف أي مجموعة فرعية من يجب تضمين السجلات من هذه الجداول في نتيجة الاستعلام. بعد ذلك ، يقرأ الخادم كل سجل يلبي نتائج الاستعلام ، ويحدد الحقول المطلوبة منه (في حالتنا ، هذه هي ID و NAME) ويرسلها إلى العميل. ثم تتكرر العملية مرة أخرى - وهكذا دواليك لكل سجل محدد.
كل هذا الاستطراد ضروري حتى يفهم القارئ العزيز أن جميع مجموعات بيانات SQL تتكون سطراً بسطر ، بما في ذلك الإجراءات المخزنة! والفرق الرئيسي بين إجراءات التحديد والإجراءات القابلة للتنفيذ هو أن الأول مصمم لإرجاع صفوف متعددة ، بينما تم تصميم الأخير لإرجاع صفوف واحدة فقط. لذلك ، يتم تطبيقها بشكل مختلف: يتم استدعاء إجراء التحديد بواسطة الأمر SELECT ، والذي "يتطلب" الإجراء لإرجاع جميع السجلات التي يمكنه إرجاعها. يسمى الإجراء القابل للتنفيذ باستخدام إجراء التنفيذ ، والذي "يأخذ" سطرًا واحدًا فقط من CP ، ويتجاهل الباقي (حتى لو كان موجودًا!)
دعنا نلقي نظرة على مثال لإجراء التحديد لجعله أكثر وضوحًا. للتسامح ، دعنا ننشئ إجراء مخزنًا يعمل تمامًا مثل معرف التحديد ، الاسم من استعلام Table_Example ، أي أنه ببساطة يختار حقلي المعرف والاسم من الجدول بأكمله. هذا المثال:
إنشاء إجراء Simple_Select_SP
عائدات (
procID INTEGER ،
procNAME VARCHAR (80))
مثل
يبدأ
ل
حدد المعرف ، الاسم من table_example
INTO: procID ،: procNAME
يفعل
يبدأ
تعليق؛
نهاية
نهاية
دعنا نلقي نظرة على إجراءات هذا الإجراء ، المسمى Simple_Select_SP. كما ترى ، لا تحتوي على معلمات إدخال ولديها معلمتان إخراج - ID و NAME. الأكثر إثارة للاهتمام ، بالطبع ، يكمن في جسم الإجراء. يتم استخدام بناء FOR SELECT هنا:
ل
حدد المعرف ، الاسم من table_example
INTO: procID ،: procNAME
يفعل
يبدأ
/ * افعل شيئًا باستخدام متغيرات procID و procName * /
نهاية
يعني هذا الجزء من التعليمات البرمجية ما يلي: لكل صف محدد من جدول Table_example ، ضع القيم المحددة في متغيرات procID و procName ، ثم قم بتنفيذ بعض الإجراءات على هذه المتغيرات.
يمكنك وضع وجه متفاجئ وتسأل ، "المتغيرات؟ ما هي المتغيرات الأخرى 9؟" إنه نوع من المفاجأة في هذا الفصل أنه يمكننا استخدام المتغيرات في الإجراءات المخزنة. في XP ، يمكنك التصريح عن كل من المتغيرات المحلية الخاصة بك ضمن إجراء ، واستخدام معلمات الإدخال والإخراج كمتغيرات.
للإعلان عن متغير محلي في إجراء مخزن ، يجب أن تضع إعلانه بعد الكلمة الأساسية AS وقبل الكلمة الأولى BEGIN. يبدو إعلان المتغير المحلي على النحو التالي:
إعلان متغير
على سبيل المثال ، للإعلان عن عدد صحيح متغير محلي Mylnt ، يمكنك إدراج التعريف التالي بين AS و BEGIN
إعلان متغير MyInt INTEGER ؛
تبدأ المتغيرات في مثالنا بنقطتين. يتم ذلك لأنه يتم الوصول إليها داخل الأمر FOR SELECT SQL ، لذلك للتمييز بين الحقول في الجداول المستخدمة في SELECT والمتغيرات ، يجب أن تسبق الأخير بنقطتين. بعد كل شيء ، يمكن أن يكون للمتغيرات نفس الاسم تمامًا مثل الحقول في الجداول!
ولكن يجب استخدام النقطتين قبل اسم المتغير داخل استعلامات SQL فقط. خارج النصوص ، يتم الوصول إلى المتغير بدون نقطتين ، على سبيل المثال:
procName = "اسم ما" ؛
لكن العودة إلى جسد عمليتنا. ترجع جملة FOR SELECT البيانات ليس في شكل جدول - مجموعة بيانات ، ولكن في شكل صف واحد في كل مرة. يجب وضع كل حقل تم إرجاعه في المتغير الخاص به: ID => procID ، NAME => procName. في جزء DO ، يتم إرسال هذه المتغيرات إلى العميل الذي يسمى الإجراء p> باستخدام الأمر SUSPEND.
وهكذا ، فإن الأمر FOR SELECT ... DO حلقات من خلال السجلات المحددة في جزء SELECT من الأمر. في جسم الحلقة التي شكلها جزء DO ، يتم نقل السجل التالي الذي تم إنشاؤه إلى العميل باستخدام الأمر SUSPEND.
لذلك ، تم تصميم إجراء الاختيار لإرجاع صف واحد أو أكثر ، حيث يتم تنظيم حلقة داخل جسم CP ، وملء المعلمات المتغيرة الناتجة. وفي نهاية نص هذه الحلقة ، يوجد أمر SUSPEND الذي سيعيد الصف التالي من البيانات إلى العميل.
الحلقات ومشغلي الفروع
بالإضافة إلى الأمر FOR SELECT ... DO ، الذي ينظم دورة من خلال سجلات تحديد معين ، هناك نوع آخر من الدورات - بينما ... DO ، والذي يسمح لك بتنظيم دورة بناءً على التحقق من أي شروط. فيما يلي مثال على HP باستخدام حلقة WHILE..DO. يقوم هذا الإجراء بإرجاع مربعات الأعداد الصحيحة من 0 إلى 99:
إنشاء PROCEDJRE QUAD
عوائد (رباعي صحيح)
مثل
أعلن متغيرًا صحيحًا ؛
يبدأ
أنا = 1 ؛
عندما أنا<100) DO
يبدأ
QUADRAT = أنا * أنا ؛
أنا = أنا + 1 ؛
تعليق؛
نهاية
نهاية
نتيجة لتنفيذ استعلام SELECT FROM QUAD ، سنحصل على جدول يحتوي على عمود QUADRAT واحد ، حيث سيكون هناك مربعات من الأعداد الصحيحة من 1 إلى 99
بالإضافة إلى التكرار على نتائج استعلام SQL والحلقة الكلاسيكية ، تستخدم لغة الإجراء المخزن العبارة IF ... ثم ... ELSE ، والتي تسمح لك بتنظيم التفريع اعتمادًا على تنفيذ أي \ شرط. على غرار معظم عبارات الفروع في لغات البرمجة عالية المستوى ، مثل Pascal و C.
دعنا نلقي نظرة على مثال أكثر تعقيدًا لإجراء مخزن يقوم بما يلي.
- لحساب متوسط السعر في جدول Table_example (راجع فصل "جداول المفاتيح الأساسية والمولدات")
- بعد ذلك ، لكل إدخال في الجدول ، يقوم بالتحقق التالي ، إذا كان السعر الحالي (PRICE) أكبر من متوسط السعر ، ثم يحدد السعر مساويًا لمتوسط السعر بالإضافة إلى النسبة المئوية الثابتة المحددة
- إذا كان السعر الحالي أقل من متوسط السعر أو مساويًا له ، فعندئذٍ يحدد السعر مساويًا للسعر السابق مضافًا إليه نصف الفرق بين السعر القديم ومتوسط السعر.
- تُرجع جميع الصفوف التي تم تغييرها في جدول.
أولاً ، دعنا نحدد اسم HP ، بالإضافة إلى معلمات الإدخال والإخراج. كل هذا مكتوب في رأس الإجراء المخزن
إنشاء إجراء زيادة الأسعار (
Percent2 زيادة الدقة المزدوجة)
RETURNS (ID INTEGER، NAME VARCHAR (SO)، new_price DOUBLE
الدقة AS
سيطلق على الإجراء اسم "زيادة الأسعار" ، ويحتوي على معلمة إدخال واحدة Peiceni21nciease ، وهي من النوع DOUBLE PRECISION ، و 3 معلمات إخراج - ID و NAME و new_pnce. لاحظ أن أول اثنين من معلمات الإخراج لهما نفس أسماء الحقول الموجودة في الجدول Table_example الذي سنعمل معه ، وهذا مسموح به بواسطة قواعد لغة الإجراء المخزن.
الآن علينا أن نعلن عن متغير محلي سيتم استخدامه لتخزين متوسط القيمة. سيبدو إعلان الأنا على النحو التالي:
إعلان متغير avg_price ضعف الدقة ؛
الآن دعنا ننتقل إلى جسم الإجراء المخزن لنفتح جسم CP
الكلمة الرئيسية تبدأ.
أولاً ، نحتاج إلى تنفيذ الخطوة الأولى من خوارزمية لدينا - لحساب متوسط السعر. للقيام بذلك ، سوف نستخدم الاستعلام التالي:
حدد AVG (السعر_ل)
من Table_Example
INTO: متوسط السعر ، -
يستخدم هذا الاستعلام دالة AVG التجميعية التي تعرض متوسط قيمة الحقل PRICE_1 بين صفوف الاستعلام المحددة - في حالتنا ، متوسط قيمة PRICE_1 عبر الجدول Table_example بالكامل. يتم وضع القيمة التي تم إرجاعها بواسطة الاستعلام في متغير avg_price. لاحظ أن المتغير avg_pnce مسبوق بنقطتين - لتمييزه عن الحقول المستخدمة في الطلب.
ميزة طلب معينهو أنه يقوم دائمًا بإرجاع إدخال واحد بالضبط. تسمى هذه الاستعلامات استعلامات فردية ، ويمكن استخدام هذه التحديدات فقط في الإجراءات المخزنة. إذا أرجع الاستعلام أكثر من صف واحد ، فيجب تنسيقه على أنه FOR SELECT ... DO ، الذي ينظم حلقة لمعالجة كل صف تم إرجاعه
إذن ، حصلنا على متوسط قيمة السعر. أنت الآن بحاجة إلى استعراض الجدول بأكمله ، ومقارنة قيمة السعر في كل سجل بمتوسط السعر واتخاذ الإجراء المناسب.
من البداية ، ننظم التكرار لكل سجل من الجدول Table_example
ل
SELECT ID ، NAME ، PRICE_1
من Table_Example
INTO: المعرف ،: NAME ،: new_price
يفعل
يبدأ
/ * _ هنا اطلب كل سجل * /
نهاية
عند تنفيذ هذا البناء ، سيتم استرداد البيانات صفًا بصف من الجدول Table_example وسيتم تعيين قيم الحقل في كل صف إلى معرّف المتغيرات ، NAME و new_pnce. بالطبع ، تتذكر أنه تم الإعلان عن هذه المتغيرات كمعلمات إخراج ، ولكن لا داعي للقلق من إعادة البيانات المحددة كنتائج: حقيقة أن معلمات الإخراج قد تم تعيينها شيئًا ما لا تعني أن العميل الذي يتصل بـ HP سيتلقى على الفور هذه القيم.! يتم تمرير المعلمات فقط عند تنفيذ الأمر SUSPEND ، وقبل ذلك يمكننا استخدام معلمات الإخراج كمتغيرات عادية - في مثالنا ، نقوم بذلك باستخدام المعامل new_price.
لذلك ، داخل جسم حلقة BEGIN .. .END ، يمكننا معالجة قيم كل صف. كما تتذكر ، نحتاج إلى معرفة كيفية مقارنة السعر الحالي بالمتوسط ، واتخاذ الإجراء المناسب. نقوم بتنفيذ إجراء المقارنة هذا باستخدام عبارة IF:
إذا (new_price> avg_price) ثم / * إذا كان السعر الحالي أكبر من متوسط السعر * /
يبدأ
/ * ثم حدد سعرًا جديدًا يساوي متوسط السعر مضافًا إليه نسبة مئوية ثابتة * /
new_price = (avg_price + avg_price * (Percent2Increase / 100)) ؛
تحديث Table_example
اضبط PRICE_1 =: new_price
أين المعرف =: المعرف ؛
نهاية
آخر
يبدأ
/ * إذا كان السعر الحالي أقل من متوسط السعر أو مساويًا له ، فقم بتعيين السعر مساويًا للسعر السابق مضافًا إليه نصف الفرق بين السعر القديم ومتوسط السعر * /
new_price = (new_pnce + ((avg_pnce new_price) / 2)) ؛
تحديث Table_example
اضبط PRICE_1 =: new_price
حيث المعرف = .ID ؛
نهاية
كما ترى ، حصلنا على بنية IF كبيرة إلى حد ما ، والتي سيكون من الصعب فهمها إذا لم تكن للتعليقات المضمنة في / ** / الأحرف.
من أجل تغيير السعر وفقًا للفرق المحسوب ، سنستخدم بيان UPDATE الذي يسمح لنا بالتعديل السجلات الموجودة- واحد أو أكثر. للإشارة بشكل لا لبس فيه إلى السجل الذي يجب تغيير السعر فيه ، نستخدم حقل المفتاح الأساسي في جملة WHERE ، لمقارنته بقيمة المتغير الذي يخزن قيمة المعرف للسجل الحالي: ID =: ID. لاحظ أن متغير ID مسبوق بنقطتين.
بعد تنفيذ IF ... ثم ... ELSE ، تحتوي متغيرات ID و NAME و new_price على البيانات التي يجب أن نعيدها إلى العميل \ الذي استدعى الإجراء. للقيام بذلك ، بعد IF ، تحتاج إلى إدخال الأمر SUSPEND ، والذي سيرسل البيانات إلى المكان الذي تم استدعاء CP منه. طوال مدة النقل ، سيتم تعليق عملية الإجراء ، وعندما يتم تسجيل رقم جديد مطلوب من CP ، سيستمر مرة أخرى - وسيستمر هذا حتى FOR SELECT ... لن تتكرر خلال جميع سجلات استعلامها.
وتجدر الإشارة إلى أنه بالإضافة إلى الأمر SUSPEND ، الذي يوقف فقط الإجراء المخزن ، هناك أمر EXIT ، والذي ينهي الإجراء المخزن بعد تمرير السلسلة. ومع ذلك ، نادرًا ما يتم استخدام الأمر EXIT ، نظرًا لأنه ضروري بشكل أساسي لكسر الحلقة عند الوصول إلى حالة معينة.
في هذه الحالة ، عندما يتم استدعاء الإجراء بواسطة عبارة SELECT وإنهائه بواسطة EXIT ، لن يتم إرجاع آخر صف تم استرداده. بمعنى ، إذا كنت بحاجة إلى مقاطعة الإجراء وما زلت> تحصل على هذا الخط ، فأنت بحاجة إلى استخدام التسلسل
تعليق؛
مخرج؛
الغرض الرئيسي من EXIT هو الحصول على مجموعات بيانات فردية ، وإرجاع المعلمات عن طريق الاتصال من خلال EXECUTE PROCEDURE. في هذه الحالة ، يتم تعيين قيم معلمات الإخراج ، ولكن لا يتم تشكيل مجموعة بيانات SQL منها ، وينتهي الإجراء.
دعنا نكتب نص الإجراء المخزن بالكامل حتى نتمكن من التقاط منطقه في لمحة:
إنشاء إجراء زيادة الأسعار (
Percent2 زيادة الدقة المزدوجة)
العوائد (رقم التعريف الصحيح ، الاسم فاركار (80) ،
new_price ضعف الدقة) AS
إعلان متغير avg_price ضعف الدقة ؛
يبدأ
حدد AVG (السعر_ل)
من Table_Example
INTO: متوسط السعر ؛
ل
SELECT ID ، NAME ، PRICE_1
من Table_Example
INTO: المعرف ،: NAME ،: new_price
يفعل
يبدأ
/ * نقوم بمعالجة كل سجل هنا * /
إذا (new_pnce> avg_price) ثم / * إذا كان السعر الحالي أكبر من متوسط السعر * /
يبدأ
/ * حدد سعرًا جديدًا يساوي متوسط السعر مضافًا إليه نسبة مئوية ثابتة * /
new_price = (avg_price + avg_price * (Percent2Increase / 100)) ؛
تحديث Table_example
اضبط PRICE_1 =: new_price
أين المعرف =: المعرف ؛
نهاية
آخر
يبدأ
/ * إذا كان السعر الحالي أقل من متوسط السعر أو مساويًا له ، فعندئذٍ يحدد السعر المساوي للسعر السابق مضافًا إليه نصف الفرق بين السعر القديم ومتوسط السعر * /
new_price = (new_price + ((avg_price - new_price) / 2)) ؛
تحديث Table_example
اضبط PRICE_1 =: new_price
أين المعرف =: المعرف ؛
نهاية
تعليق؛
نهاية
نهاية
يوضح مثال الإجراء المخزن هذا استخدام الإجراء المخزن الأساسي وبنيات لغة التشغيل. بعد ذلك ، سننظر في طرق استخدام الإجراءات المخزنة لحل بعض المشكلات الشائعة.
الإجراءات المخزنة العودية
يمكن أن تكون الإجراءات المخزنة InterBase متكررة. هذا يعني أن الإجراء المخزن يمكنه استدعاء نفسه. يُسمح بما يصل إلى 1000 مستوى متداخل من الإجراءات المخزنة ، ومع ذلك ، يجب على المرء أن يتذكر أن الموارد المجانية على الخادم قد تنفد قبل الوصول إلى الحد الأقصى لتداخل HP.
أحد الاستخدامات الشائعة للإجراءات المخزنة هو معالجة هياكل الشجرة المخزنة في قاعدة بيانات. غالبًا ما تُستخدم الأشجار في قائمة المواد والمستودعات والموارد البشرية والتطبيقات الشائعة الأخرى.
لنلقِ نظرة على مثال لإجراء مخزن يحدد جميع المنتجات من نوع معين ، بدءًا من مستوى تداخل معين.
دعنا نحصل على بيان المشكلة التالي: لدينا دليل للبضائع بهيكل هرمي من هذا النوع:
بضائع
- الأجهزة
- ثلاجات
- ثلاث غرف
- غرفتين
- غرفة واحدة
-
غسالة ملابس
- رَأسِيّ
- أمامي
- كلاسيك
- ضيق
- تكنولوجيا الكمبيوتر
....
يمكن أن يكون لهذا الهيكل الخاص بدليل فئات المنتجات فروع بأعماق مختلفة. وتزداد أيضًا بمرور الوقت. مهمتنا هي توفير مجموعة مختارة من جميع العناصر المحدودة من الدليل مع "كشف الاسم الكامل" ، بدءًا من أي عقدة. على سبيل المثال ، إذا حددنا عقدة "الغسالات" ، فسنحتاج إلى الحصول على الفئات التالية:
الغسالات - عمودية
الغسالات - فرونتال كلاسيك
الغسالات - أمامي ضيق
دعنا نحدد بنية الجداول لتخزين المعلومات في دليل المنتج. نستخدم مخططًا مبسطًا لتنظيم شجرة في جدول واحد:
إنشاء شجرة السلع الجدول
(ID_GOOD INTEGER ليس فارغًا ،
ID_PARENT_GOOD INTEGER ،
GOOD_NAME VARCHAR (80) ،
قيد المفتاح الأساسي pkGooci (ID_GOOD)) ؛
نقوم بإنشاء جدول GoodsTree واحد ، يحتوي على 3 حقول فقط: ID_GOOD - معرف الفئة الذكية ، ID_PARENT_GOOD - معرف الشجرة الأصل للفئة لهذه الفئة و GOOD_NAME - اسم الفئة. لضمان سلامة البيانات الواردة في هذا الجدول ، سنفرض قيد مفتاح خارجي على هذا الجدول:
تبديل البضائع الجدول شجرة
أضف القيد FK_goodstree
المفتاح الخارجي (ID_PARENT_GOOD)
المراجع GOODSTPEE (ID_GOOD)
يشير الجدول إلى نفسه والمفتاح الخارجي المحدد يتتبع ذلك. بحيث لا يحتوي الجدول على مراجع لأبوين غير موجودين ، ويمنع أيضًا محاولات حذف فئات المنتجات التي لها أطفال.
لنضع البيانات التالية في جدولنا:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
اسم جيد بضائع |
الآن بعد أن أصبح لدينا مكان لتخزين البيانات ، يمكننا البدء في إنشاء إجراء مخزن يعرض جميع فئات المنتجات "النهائية" في نموذج "موسع" - على سبيل المثال ، للفئة "ثلاثية الغرف" الاسم الكامل الفئة ستبدو مثل "ثلاجات الأجهزة المنزلية ثلاث غرف".
طورت الإجراءات المخزنة التي تعالج هياكل الشجرة المصطلحات الخاصة بها. كل عنصر من عناصر الشجرة يسمى عقدة ؛ وتسمى العلاقة بين العقد التي تشير إلى بعضها البعض علاقة الوالدين والطفل. العقد التي تقع في نهاية الشجرة وليس لها أطفال تسمى "أوراق".
سيكون لإجرائنا المخزن معرف فئة كمدخل ، سنحتاج منه لبدء التنقل لأسفل. سيبدو الإجراء المخزن على النحو التالي:
إنشاء إجراء GETFULLNAME (ID_GOOD2SHOW INTEGER)
العوائد (FULL_GOODS_NAME VARCHAR (1000) ،
ID_CHILD_GOOD INTEGER)
مثل
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR (80) ،
يبدأ
/ * 0 تنظيم خارجي لحلقةحدد على الأحفاد المباشرة لمنتج مع ID_GOOD = ID_GOOD2SHOW * /
لـ SELECT gtl.id_good، gtl.good_name
من GoodsTree gtl
أين gtl.id_parent_good =: ID_good2show
INTO: ID_CHILD_GOOD،: full_goods_name
يفعل
يبدأ
/ "تحقق من الدالة EXISTS ، التي تُرجع TRUE إذا كان الاستعلام الموجود بين قوسين يُرجع صفًا واحدًا على الأقل. إذا كانت العقدة التي تم العثور عليها مع ID_PARENT_GOOD = ID_CHILD_GOOD لا تحتوي على توابع ، فهي" جزء "من الشجرة ويتم إدخالها في النتائج * /
إذا (ليس خارجًا (
حدد * من GoodsTree
أين GoodsTree.id_parent_good =: id_child_good))
ثم
يبدأ
/ * مرر "ورقة" الشجرة إلى النتائج * /
تعليق؛
نهاية
آخر
/ * للعقد التي لديها أطفال * /
يبدأ
/ * تخزين اسم العقدة الرئيسية في متغير مؤقت * /
CURR_CHILD_NAME = full_goods_name ؛
/ * قم بتشغيل هذا الإجراء بشكل متكرر * /
ل
حدد ID_CHILD_GOOD ، full_goods_name
من GETFULLNAME (: ID_CHILD_GOOD)
INTO: ID_CHILD_GOOD،: full_goods_name
ابدأ
/ * أضف اسم العقدة الأصل إلى. ، اسم الطفل باستخدام عملية سلسلة السلسلة || * /
full_goods_name = CURR_CHILD_NAME | "" | و ull_goods_name ، -
تعليق؛ / * إرجاع الاسم الكامل للمنتج * /
نهاية
نهاية
نهاية
نهاية
إذا قمنا بتنفيذ هذا الإجراء باستخدام معلمة الإدخال ID_GOOD2SHOW = 1 ، فسنحصل على ما يلي:
كما ترون ، بمساعدة إجراء مخزن متكرر ، تجولنا في شجرة الفئات بأكملها واستنتجنا الاسم الكامل لفئات "الورقة" الموجودة في أطراف الفروع.
خاتمة
بهذا نختتم مراجعتنا للسمات الرئيسية للغة الإجراء المخزن. من الواضح أنه من المستحيل إتقان تطوير الإجراءات المخزنة بشكل كامل في فصل واحد ، لكننا هنا حاولنا تقديم وشرح المفاهيم الرئيسية المرتبطة بالإجراءات المخزنة. يمكن تطبيق تصاميم HP الموصوفة وتقنيات التصميم في معظم تطبيقات قواعد البيانات.
سيتم تناول بعض القضايا المهمة المتعلقة بتطوير الإجراءات المخزنة في الفصل التالي - "الميزات الموسعة للغة الإجراء المخزن InterBase" ، والمخصص لمعالجة الاستثناءات ومعالجة الأخطاء في الإجراءات المخزنة والعمل مع المصفوفات.
في Microsoft SQL Server لتنفيذ وأتمتة الخوارزميات الخاصة بهم ( العمليات الحسابية) يمكنك استخدام الإجراءات المخزنة ، لذلك سنتحدث اليوم عن كيفية إنشائها وتعديلها وحذفها.
لكن أولاً ، نظرية صغيرة حتى تفهم ماهية الإجراءات المخزنة وما الغرض منها في T-SQL.
ملحوظة! للمبرمجين المبتدئين ، أوصي بالمواد المفيدة التالية حول موضوع T-SQL:
- لدراسة أكثر تفصيلا T-SQLأوصي أيضًا بقراءة كتاب - طريقة مبرمج T-SQL. البرنامج التعليمي Transact-SQL ؛
- دورات T-SQL المهنية عبر الإنترنت
ما هي الإجراءات المخزنة في T-SQL؟
الإجراءات المخزنة- هذه هي كائنات قاعدة البيانات التي يتم فيها تضمين الخوارزمية في شكل مجموعة من تعليمات SQL. بمعنى آخر ، يمكننا القول أن الإجراءات المخزنة هي برامج داخل قاعدة بيانات. تُستخدم الإجراءات المخزنة لتخزين التعليمات البرمجية القابلة لإعادة الاستخدام على الخادم ، على سبيل المثال ، قمت بكتابة خوارزمية أو عملية حسابية متسلسلة أو عبارة SQL متعددة الخطوات ، وليس لتنفيذ جميع التعليمات المضمنة في كل مرة. هذه الخوارزمية، يمكنك ترتيب ذلك كإجراء مخزن. في هذه الحالة ، عند إنشاء إجراء SQL ، يقوم الخادم بترجمة التعليمات البرمجية ، وبعد ذلك ، في كل مرة تقوم فيها بتشغيل هذا الإجراء ، لن يقوم خادم SQL بترجمته مرة أخرى.
لتشغيل إجراء مخزن في SQL Server ، من الضروري كتابة الأمر EXECUTE قبل اسمه ، ومن الممكن أيضًا كتابة أمر EXEC هذا باختصار. قم باستدعاء إجراء مخزن في جملة SELECT ، على سبيل المثال ، لأن الوظيفة لن تعمل بعد الآن ، أي يتم تشغيل الإجراءات بشكل منفصل.
في الإجراءات المخزنة ، على عكس الوظائف ، من الممكن بالفعل إجراء عمليات تعديل البيانات مثل: UNSERT ، UPDATE ، DELETE. أيضًا ، في الإجراءات ، يمكنك استخدام أي نوع من عبارات SQL تقريبًا ، على سبيل المثال ، CREATE TABLE لإنشاء جداول أو EXECUTE ، أي استدعاء إجراءات أخرى. الاستثناء هو عدة أنواع من التعليمات ، مثل: إنشاء أو تغيير الوظائف ، وطرق العرض ، والمشغلات ، وإنشاء المخططات ، وبعض الإرشادات الأخرى المماثلة ، على سبيل المثال ، من المستحيل أيضًا تبديل سياق اتصال قاعدة البيانات (USE) في إجراء مخزن .
قد يحتوي الإجراء المخزن على معلمات إدخال ومعلمات إخراج ، وقد يؤدي إلى إرجاع بيانات مجدولة ، وقد لا يقوم بإرجاع أي شيء ، ولكنه يقوم فقط بتنفيذ التعليمات الواردة فيه.
تعتبر الإجراءات المخزنة مفيدة للغاية ، فهي تساعدنا على أتمتة أو تبسيط العديد من العمليات ، على سبيل المثال ، تحتاج باستمرار إلى إنشاء العديد من التقارير التحليلية المعقدة باستخدام الجداول المحورية، أي. مشغل PIVOT. لتبسيط تكوين الاستعلامات باستخدام هذا العامل ( كما تعلم ، فإن بنية PIVOT معقدة نوعًا ما) ، يمكنك كتابة إجراء من شأنه إنشاء تقارير موجزة ديناميكيًا ، على سبيل المثال ، في المادة "Dynamic PIVOT in T-SQL" ، يتم تقديم مثال على تنفيذ هذه الميزة في شكل إجراء مخزن.
أمثلة على العمل مع الإجراءات المخزنة في Microsoft SQL Server
البيانات الأولية للحصول على أمثلة
سيتم تنفيذ جميع الأمثلة أدناه في Microsoft SQL Server 2016 Express. من أجل توضيح كيفية عمل الإجراءات المخزنة مع البيانات الحقيقية ، نحتاج إلى هذه البيانات ، فلنقم بإنشائها. على سبيل المثال ، دعنا ننشئ جدول اختبار ونضيف بعض السجلات إليه ، لنفترض أنه سيكون جدولًا يحتوي على قائمة بالمنتجات مع أسعارها.
بيان إنشاء الجدول CREATE TABLE TestTable (INT IDENTITY (1،1) NOT NULL، INT NOT NULL، VARCHAR (100) NOT NULL، MONEY NULL) GO - إضافة بيان البيانات INSERT INTO TestTable (CategoryId، ProductName، Price) VALUES (1 ، "Mouse"، 100)، (1، "Keyboard"، 200)، (2، "Phone"، 400) انتقال - تحديد * من استعلام TestTable
هناك بيانات ، فلننتقل الآن إلى إنشاء الإجراءات المخزنة.
إنشاء إجراء مخزن في T-SQL - عبارة CREATE PROCEDURE
يتم إنشاء الإجراءات المخزنة باستخدام العبارة إنشاء إجراء، بعد هذه التعليمات ، يجب عليك كتابة اسم الإجراء الخاص بك ، ثم ، إذا لزم الأمر ، حدد معلمات الإدخال والإخراج بين قوسين. بعد ذلك ، تكتب الكلمة الأساسية AS وتفتح مجموعة من التعليمات بالكلمة الرئيسية BEGIN ، وأغلق هذه الكتلة بالكلمة END. داخل هذه الكتلة ، تكتب جميع التعليمات التي تنفذ الخوارزمية الخاصة بك أو نوعًا من الحساب المتسلسل ، وبعبارة أخرى ، تقوم بالبرمجة في T-SQL.
على سبيل المثال ، دعنا نكتب إجراء مخزن سيضيف رقم قياسي جديد، أي. منتج جديدإلى طاولة الاختبار الخاصة بنا. للقيام بذلك ، سنحدد ثلاث معلمات إدخال:CategoryId - معرف فئة المنتج ،ProductName - اسم المنتج و @ السعر - سعر المنتج ، معلمة معينةسيكون لدينا اختياري ، أي لا يمكن تمريره إلى الإجراء ( على سبيل المثال ، لا نعرف السعر بعد) ، لهذا سنقوم بتعيين القيمة الافتراضية في تعريفها. هذه المعلمات موجودة في نص الإجراء ، أي في BEGIN ... يمكن استخدام كتلة END بنفس طريقة استخدام المتغيرات العادية ( كما تعلم ، يتم الإشارة إلى المتغيرات بعلامة @). إذا كنت بحاجة إلى تحديد معلمات الإخراج ، فبعد اسم المعلمة ، حدد كلمة OUTPUT الأساسية ( أو يختصر OUT).
في كتلة BEGIN… END ، سنكتب تعليمات لإضافة البيانات ، وأيضًا في نهاية الإجراء ، عبارة SELECT بحيث يقوم الإجراء المخزن بإرجاع بيانات مجدولة حول المنتجات في الفئة المحددة ، مع مراعاة الجديد ، مجرد منتج مضاف. في هذا الإجراء المخزن أيضًا ، أضفت معالجة المعلمة الواردة ، وهي الإزالة مساحات اضافيةفي البداية والنهاية سلسلة نصيةمن أجل تجنب المواقف التي يتم فيها إدخال عدة مسافات بطريق الخطأ.
هنا هو رمز لهذا الإجراء أنا أيضا علقت على ذلك).
إنشاء إجراء CREATE PROCEDURE TestProcedure (- المعلمات الواردةCategoryId INT،Price MONEY = 0) AS BEGIN - التعليمات التي تنفذ الخوارزمية الخاصة بك - معالجة المعلمات الواردة - إزالة المسافات الزائدة في البداية و في نهاية السلسلة النصية SETProductName = LTRIM (RTRIM (ProductName)) ؛ --إضافة إدخال جديد INSERT INTO TestTable (CategoryId، ProductName، Price) VALUES (CategoryId،ProductName،Price) - إرجاع البيانات حدد * من TestTable WHERE CategoryId =CategoryId END GO
تشغيل إجراء مخزن في T-SQL - أمر التنفيذ
يمكنك تشغيل إجراء مخزن ، كما أشرت بالفعل ، باستخدام الأمر EXECUTE أو EXEC. يتم تمرير المعلمات الواردة إلى الإجراءات بمجرد تعدادها وتوفير القيم المناسبة بعد اسم الإجراء ( لمعلمات الإخراج ، يجب عليك أيضًا تحديد أمر الإخراج). ومع ذلك ، قد لا يتم تحديد اسم المعلمات ، ولكن في هذه الحالة من الضروري اتباع تسلسل تحديد القيم ، أي حدد القيم بالترتيب الذي يتم به تعريف معلمات الإدخال ( ينطبق هذا أيضًا على معلمات الإخراج).
قد لا يتم تحديد المعلمات التي تحتوي على قيم افتراضية ، فهذه هي ما يسمى بالمعلمات الاختيارية.
فيما يلي بعض الطرق المختلفة ولكن المتكافئة لتشغيل الإجراءات المخزنة ، وتحديدًا إجراء الاختبار الخاص بنا.
1. قم باستدعاء الإجراء بدون تحديد السعر EXECUTE TestProcedureCategoryId = 1،ProductName = "Test product 1" --2. نقوم باستدعاء الإجراء بالسعر المحدد EXEC TestProcedureCategoryId = 1،ProductName = "Test product 2"، @ Price = 300 --3. نقوم باستدعاء الإجراء دون تحديد اسم معلمات EXEC TestProcedure 1 ، "عنصر الاختبار 3" ، 400
تغيير إجراء مخزّن إلى T-SQL - بيان إجراء التغيير
يمكنك إجراء تغييرات على خوارزمية الإجراء باستخدام التعليمات تغيير الإجراء. بمعنى آخر ، لتغيير إجراء موجود بالفعل ، تحتاج فقط إلى كتابة إجراء بديل بدلاً من إنشاء إجراء ، وتغيير كل شيء آخر حسب الضرورة.
لنفترض أننا بحاجة إلى إجراء تغييرات على إجراء الاختبار الخاص بنا ، لنقل المعلمة @ السعر ، أي السعر ، سنجعله إلزاميًا ، لذلك سنزيل القيمة الافتراضية ، ونتخيل أيضًا أننا لم نعد بحاجة إلى الحصول على مجموعة البيانات الناتجة ، لذلك سنقوم ببساطة بإزالة عبارة SELECT من الإجراء المخزن.
قم بتغيير الإجراء ALTER PROCEDURE TestProcedure (- المعلمات الواردةCategoryId INT،ProductName VARCHAR (100)،Price MONEY) كما تبدأ - التعليمات التي تنفذ الخوارزمية الخاصة بك - معالجة المعلمات الواردة - إزالة المسافات الزائدة في البداية والنهاية من سطور النص SETProductName = LTRIM (RTRIM (ProductName)) ؛ --إضافة سجل جديد INSERT INTO TestTable (CategoryId، ProductName، Price) VALUES (CategoryId،ProductName،Price) END GO
حذف إجراء مخزن في جملة T-SQL - DROP PROCEDURE
إذا لزم الأمر ، يمكنك حذف الإجراء المخزن ، ويتم ذلك باستخدام العبارة إجراء السقوط.
على سبيل المثال ، دعنا نحذف إجراء الاختبار الذي أنشأناه.
إجراء اختبار السقوط
عند حذف الإجراءات المخزنة ، يجدر بنا أن نتذكر أنه إذا تمت الإشارة إلى الإجراء بواسطة إجراءات أخرى أو عبارات SQL ، بعد حذفه ، فسوف يفشل مع حدوث خطأ ، لأن الإجراء الذي يشيرون إليه لم يعد موجودًا.
لدي كل شيء ، آمل أن تكون المادة ممتعة ومفيدة لك ، وداعا!
في هذا دليل الدراسةسوف تتعلم كيف إنشاء وحذف الإجراءات في SQL Server(Transact-SQL) مع بناء الجملة والأمثلة.
وصف
في SQL Server ، الإجراء عبارة عن برنامج مخزن يمكنك تمرير المعلمات إليه. لا يُرجع قيمة مثل دالة. ومع ذلك ، يمكنه إرجاع حالة النجاح / الفشل إلى الإجراء الذي يطلق عليه.
إنشاء إجراء
يمكنك إنشاء الإجراءات المخزنة الخاصة بك في SQL Server (Transact-SQL). دعونا نلقي نظرة فاحصة.
بناء الجملة
إجراءات بناء الجملة في SQL Server (Transact-SQL):
إنشاء (إجراء | إجراء) اسم_إجراء
[parameterdatatype
[VARYING] [= افتراضي] [خروج | الإخراج | يقرأ فقط]
، @ نوع بيانات المعلمة
[VARYING] [= افتراضي] [خروج | الإخراج | يقرأ فقط ] ]
[مع (التشفير | المعيد | تنفيذ بند)]
[للنسخ]
مثل
يبدأ
قسم قابل للتنفيذ
نهاية؛
خيارات أو وسيطات
اسم_المخطط هو اسم مخطط قاعدة البيانات الذي ينتمي إليه الإجراء المخزن.
اسم الإجراء هو الاسم الذي سيتم تعيين هذا الإجراء له في SQL Server.
parameter - يتم تمرير معلمة واحدة أو أكثر إلى الإجراء.
type_schema_name هو المخطط الذي يمتلك نوع البيانات ، إن أمكن.
نوع البيانات - نوع البيانات لـparameter.
يتم تعيين VARYING لمعلمات المؤشر عندما تكون مجموعة النتائج هي معلمة إخراج.
الافتراضي هو القيمة الافتراضية لتعيينها إلى المعلمةparameter.
OUT - وهذا يعني أنparameter هي معلمة إخراج.
الإخراج - وهذا يعني أنparameter هي معلمة إخراج.
READONLY - وهذا يعني أنه لا يمكن الكتابة فوقparameter بواسطة إجراء مخزن.
التشفير - هذا يعني أنه لن يتم تخزين مصدر الإجراء المخزن كنص عادي في طرق عرض نظام SQL Server.
RECOMPILE - هذا يعني أنه لن يتم تخزين خطة الاستعلام مؤقتًا لهذا الإجراء المخزن.
EXECUTE AS - يضبط سياق الأمان لتنفيذ إجراء مخزن.
للنسخ المتماثل - هذا يعني أن الإجراء المخزن يتم تنفيذه فقط أثناء النسخ المتماثل.
مثال
لنلقِ نظرة على مثال لإنشاء إجراء مخزن في SQL Server (Transact-SQL).
فيما يلي مثال بسيط للإجراء:
معاملات SQL
إنشاء إجراء FindSitesite_name VARCHAR (50) خارج AS BEGIN DECLAREsite_id INT ؛ SETsite_id = 8 ؛ إذاsite_id< 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; END;
إنشاء إجراء FindSite site_name VARCHAR (50) خارج يبدأ إعلان @ site_id INT ؛ SETsite_id = 8 ؛ إذاsite_id< 10 SETsite_name = "yandex.com" ؛ آخر SETsite_name = "google.com" ؛ نهاية ؛ |
هذا الإجراء يسمى FindSite. يحتوي على معلمة واحدة تسمىsite_name ، وهي معلمة إخراج يتم تحديثها بناءً على المتغيرsite_id.
يمكنك بعد ذلك الرجوع إلى إجراء مخزن جديد يسمى FindSite على النحو التالي.
يحتوي MySQL 5 على العديد من الميزات الجديدة ، من أهمها إنشاء الإجراءات المخزنة. في هذا البرنامج التعليمي ، سأتحدث عن ماهيتهم وكيف يمكنهم تسهيل حياتك.
مقدمة
الإجراء المخزن هو طريقة لتغليف الإجراءات المتكررة. يمكن استخدام الإجراءات المخزنة للإعلان عن المتغيرات وإدارة تدفق البيانات واستخدام تقنيات البرمجة الأخرى.
سبب إنشائها واضح ويؤكده الاستخدام المتكرر. من ناحية أخرى ، إذا تحدثت إلى أولئك الذين يعملون معهم بشكل غير منتظم ، فسيتم تقسيم الآراء إلى جانبين متعارضين تمامًا. لا تنسى ذلك.
خلف
- منطق تقاسم مع التطبيقات الأخرى. الإجراءات المخزنة تغلف الوظيفة ؛ هذا يضمن الوصول إلى البيانات والاتصال الإداري بين التطبيقات المختلفة.
- عزل المستخدمين من جداول قاعدة البيانات. يسمح لك هذا بمنح حق الوصول إلى الإجراءات المخزنة ، ولكن ليس إلى بيانات الجدول نفسها.
- يوفر آلية حماية. وفقًا للنقطة السابقة ، إذا كان بإمكانك الوصول إلى البيانات فقط من خلال الإجراءات المخزنة ، فلا يمكن لأي شخص آخر مسح بياناتك من خلال أمر SQL DELETE.
- تحسين الأداء نتيجة التخفيض ازدحام انترنت. باستخدام الإجراءات المخزنة ، يمكن دمج استعلامات متعددة.
ضد
- زيادة الحمل على خادم قاعدة البيانات بسبب حقيقة أن معظم العمل يتم على جانب الخادم ، وأقل من جانب العميل.
- عليك أن تتعلم الكثير. ستحتاج إلى تعلم بناء جملة تعبير MySQL لكتابة الإجراءات المخزنة.
- أنت تقوم بتكرار منطق التطبيق الخاص بك في مكانين: رمز الخادم ورمز الإجراءات المخزنة ، مما يعقد عملية معالجة البيانات.
- يمكن أن يؤدي الترحيل من DBMS إلى آخر (DB2 ، SQL Server ، إلخ) إلى حدوث مشكلات.
الأداة التي أستخدمها تسمى MySQL Query Browser ، وهي قياسية إلى حد ما للتفاعل مع قواعد البيانات. أداة سطر الأوامر MySQL هو خيار ممتاز آخر. أنا أخبرك بهذا لأن phpMyAdmin المفضل لدى الجميع لا يدعم تشغيل الإجراءات المخزنة.
بالمناسبة ، أستخدم بنية الجدول الأولي لتسهيل فهم هذا الموضوع. أنا أتحدث عن الإجراءات المخزنة ، وهي معقدة بما يكفي للتعمق في بنية الجدول المرهقة أيضًا.
الخطوة 1: قم بإعداد المحدد
المحدد هو حرف أو سلسلة من الأحرف تُستخدم للإشارة إلى عميل MySQL أنك انتهيت من كتابة جملة SQL. للأعمار ، كان المحدد هو الحرف الفاصلة المنقوطة. ومع ذلك ، يمكن أن تنشأ مشاكل لأن إجراء مخزن يمكن أن يحتوي على تعبيرات متعددة ، كل منها يجب أن ينتهي بفاصلة منقوطة. في هذا البرنامج التعليمي ، أستخدم السلسلة "//" كمحدد.
الخطوة 2: كيفية التعامل مع الإجراءات المخزنة
قم بإنشاء إجراء مخزن
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT" إجراء "BEGIN SELECT" Hello World! "؛ نهاية//
يقوم الجزء الأول من الكود بإنشاء إجراء مخزن. التالي - يحتوي على معلمات اختيارية. ثم يأتي الاسم وأخيراً نص الإجراء نفسه.
أسماء الإجراءات المخزنة حساسة لحالة الأحرف. لا يمكنك أيضًا إنشاء إجراءات متعددة بنفس الاسم. ضمن إجراء مخزن ، لا يمكن أن يكون هناك تعبيرات تقوم بتعديل قاعدة البيانات نفسها.
4 خصائص إجراء مخزن:
- اللغة: لأغراض قابلية النقل ، الافتراضي هو SQL.
- حتمية: إذا كان الإجراء يُرجع نفس النتيجة طوال الوقت ويأخذ نفس معلمات الإدخال. هذا من أجل عملية النسخ والتسجيل. القيمة الافتراضية هي NOT DETERMINISTIC.
- أمان SQL: أثناء المكالمة ، يتم التحقق من حقوق المستخدم. INVOKER هو المستخدم الذي يستدعي الإجراء المخزن. DEFINER هو "مبتكر" الإجراء. القيمة الافتراضية هي DEFINER.
- التعليق: لأغراض التوثيق ، القيمة الافتراضية هي ""
استدعاء إجراء مخزن
لاستدعاء إجراء مخزن ، اكتب الكلمة الأساسية CALL متبوعة باسم الإجراء ، متبوعًا بالمعلمات (المتغيرات أو القيم) بين قوسين. الأقواس مطلوبة.
CALL store_procedure_name (param1، param2، ....) CALL Procedure1 (10، "string parameter"،parameter_var)؛
تغيير إجراء مخزن
تحتوي MySQL على عبارة ALTER PROCEDURE لتعديل الإجراءات ، ولكنها مناسبة فقط لتعديل خصائص معينة. إذا كنت بحاجة إلى تغيير المعلمات أو نص الإجراء ، فيجب حذفه وإعادة إنشائه.
حذف إجراء مخزن
إجراء السقوط إذا كان EXISTS p2 ؛
هذا أمر بسيط. العبارة IF EXISTS اكتشاف خطأ في حالة عدم وجود مثل هذا الإجراء.
الخطوة 3: الخيارات
دعونا نرى كيف يمكنك تمرير المعلمات إلى إجراء مخزن.
- إنشاء إجراء proc1 (): قائمة فارغةحدود
- إنشاء إجراء proc1 (في varname DATA-TYPE): معلمة إدخال واحدة. كلمة IN اختيارية لأن المعلمات الافتراضية هي IN (واردة).
- إنشاء إجراء proc1 (OUT varname DATA-TYPE): معلمة إرجاع واحدة.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): معلمة واحدة ، كل من الإدخال والإخراج.
وبطبيعة الحال ، يمكنك تعيين عدة معلمات من أنواع مختلفة.
في مثال المعلمة
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result ؛ نهاية//
مثال معلمة OUT
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR (100)) BEGIN SET var1 =" This is a test "؛ نهاية //
مثال معلمة INOUT
DELIMITER // إنشاء إجراء `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2 ؛ نهاية //
الخطوة 4: المتغيرات
الآن سوف أعلمك كيفية إنشاء المتغيرات وتخزينها داخل الإجراءات. يجب أن تعلن عنهم صراحة في بداية مجموعة BEGIN / END ، إلى جانب أنواع البيانات الخاصة بهم. بمجرد أن تعلن عن متغير ، يمكنك استخدامه في نفس المكان مثل متغيرات الجلسة أو القيم الحرفية أو أسماء الأعمدة.
تبدو صيغة التصريح عن متغير كما يلي:
DECLARE varname DATA-TYPE DEFAULT defaultvalue ؛
دعنا نعلن عن بعض المتغيرات:
نعلن أ ، ب افتراض افتراضي 5 ؛ DECLARE str VARCHAR (50) ؛ أعلن اليوم TIMESTAMP الافتراضي CURRENT_DATE ؛ إعلان v1، v2، v3 TINYINT؛
العمل مع المتغيرات
بمجرد أن تعلن عن متغير ، يمكنك تعيين قيمته بأوامر SET أو SELECT:
DELIMITER // إنشاء إجراء `var_proc` (IN paramstr VARCHAR (20)) BEGIN DECLARE a، b INT DEFAULT 5 ؛ DECLARE str VARCHAR (50) ؛ أعلن اليوم TIMESTAMP الافتراضي CURRENT_DATE ؛ إعلان v1، v2، v3 TINYINT؛ أدخل في الجدول 1 القيم (أ) ؛ SET str = "أنا سلسلة" ؛ حدد CONCAT (str، paramstr) ، اليوم من الجدول 2 حيث b> = 5 ؛ نهاية //
الخطوة 5: هياكل التحكم في التدفق
تدعم MySQL بنيات IF و CASE و ITERATE و LEAVE LOOP و WHILE و REPEAT للتحكم في التدفق داخل إجراء مخزن. سننظر في كيفية استخدام IF و CASE و WHILE لأنها الأكثر استخدامًا.
إذا البناء
بمساعدة بناء IF ، يمكننا تنفيذ المهام التي تحتوي على شروط:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT ؛ مجموعة متغير 1 = بارام 1 + 1 ؛ إذا المتغير 1 = 0 ثم حدد متغير 1 ؛ إنهاء إذا؛ IF param1 = 0 ثم حدد "قيمة المعلمة = 0" ؛ ELSE SELECT "قيمة المعلمة<>0 "؛ END IF ؛ END //
بناء CASE
CASE هي طريقة أخرى للتحقق من الشروط واختيار الحل الصحيح. هذه طريقة رائعة لاستبدال الكثير من بنيات IF. يمكن وصف البنية بطريقتين ، مما يمنحك المرونة لإدارة التعبيرات الشرطية المتعددة.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT ؛ مجموعة متغير 1 = بارام 1 + 1 ؛ متغير الحالة 1 عندما يكون 0 ثم أدخل قيم الجدول 1 (param1) ؛ عندما 1 ثم أدخل في الجدول 1 القيم (متغير 1) ؛ ELSE INSERT INTO table 1 VALUES (99) ؛ نهاية القضية ؛ نهاية //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT ؛ مجموعة متغير 1 = بارام 1 + 1 ؛ CASE WHEN variable1 = 0 ثم أدخل INTO table1 VALUES (param1) ؛ عندما المتغير 1 = 1 ثم أدخل في الجدول 1 القيم (المتغير 1) ؛ ELSE INSERT INTO table 1 VALUES (99) ؛ نهاية القضية ؛ نهاية //
أثناء البناء
من الناحية الفنية ، هناك ثلاثة أنواع من الحلقات: حلقة WHILE ، حلقة LOOP ، وحلقة REPEAT. يمكنك أيضًا إجراء حلقة من خلال تقنية برمجة Darth Vader: عبارات GOTO. فيما يلي مثال للحلقة:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE متغير 1 ، متغير 2 INT ؛ مجموعة متغير 1 = 0 ؛ بينما متغير 1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
الخطوة 6: المؤشرات
تُستخدم المؤشرات لاجتياز مجموعة الصفوف التي تم إرجاعها بواسطة استعلام ومعالجة كل صف.
تدعم MySQL المؤشرات في الإجراءات المخزنة. فيما يلي بناء جملة قصير لإنشاء واستخدام المؤشر.
أعلن عن اسم المؤشر CURSOR FOR SELECT ... ؛ / * قم بتعريف المؤشر وقم بتعبئته * / DECLARE CONTINUE HANDLER FOR NOT FOUND / * ماذا تفعل عندما لا يكون هناك المزيد من السجلات * / OPEN cursor-name ؛ / * فتح المؤشر * / FETCH اسم المؤشر INTO متغير [، متغير] ؛ / * تعيين قيمة لمتغير يساوي القيمة الحالية للعمود * / CLOSE اسم المؤشر ؛ / * إغلاق المؤشر * /
في هذا المثال ، سنقوم ببعض العمليات البسيطة باستخدام المؤشر:
DELIMITER // إنشاء إجراء `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a، b، c INT؛ إعلان cur1 CURSOR لتحديد col1 من table1 ؛ إعلان استمرار المعالج لمجموعة غير موجودة ب = 1 ؛ فتح cur1 ؛ ضبط ب = 0 ؛ ضبط ج = 0 ؛ بينما b = 0 DO FETCH cur1 INTO a ؛ إذا كانت b = 0 ، فقم بتعيين c = c + a ؛ إنهاء إذا؛ تنتهي بينما ؛ إغلاق cur1 ؛ ضبط param1 = c ؛ نهاية //
تحتوي المؤشرات على ثلاث خصائص تحتاج إلى فهمها لتجنب النتائج غير المتوقعة:
- غير حساس: بمجرد فتحه ، لن يعكس المؤشر التغييرات التي حدثت لاحقًا في الجدول. في الواقع ، لا تضمن MySQL تحديث المؤشر ، لذلك لا تعتمد عليه.
- للقراءة فقط: لا يمكن تعديل المؤشرات.
- لا يوجد ترجيع: يمكن للمؤشر التحرك في اتجاه واحد فقط - للأمام ، ولن تتمكن من تخطي الأسطر دون تحديدها.
خاتمة
في هذا البرنامج التعليمي ، قدمت لك أساسيات الإجراءات المخزنة وبعض الخصائص المحددة المرتبطة بها. بالطبع ، ستحتاج إلى تعميق معرفتك في مجالات مثل الأمان وتعبيرات SQL والتحسين قبل أن تصبح خبيرًا إجرائيًا حقيقيًا في MySQL.
يجب أن تحسب فوائد استخدام الإجراءات المخزنة في تطبيقك الخاص ، وعندها فقط تنشئ الإجراءات الضرورية فقط. بشكل عام ، أستخدم الإجراءات ؛ في رأيي ، يجب تنفيذها في المشاريع نظرًا لأمنها وصيانة الكود والتصميم العام. أيضًا ، ضع في اعتبارك أن إجراءات MySQL لا تزال قيد التقدم. توقع تحسينات فيما يتعلق بالوظائف والتحسينات. لا تتردد في مشاركة آرائك.
تحميل...