استخدام المؤشرات والحلقات في Transact-SQL. المؤشرات في MySQL الإجراءات المخزنة المؤشرات في خادم SQL
يسمح لك الأمر DECLARE CURSOR باسترداد السجلات صفًا تلو الآخر من جدول للمعالجة. يسمح هذا بمعالجة صف تلو صف بدلاً من معالجة مجموعة البيانات التقليدية التي يقوم بها SQL.
في التقريب الأول ، عند العمل بمؤشر ، يتم استخدام الخطوات التالية.
يتم إنشاء المؤشر باستخدام الأمر DECLARE. يتم فتح المؤشر باستخدام الأمر OPEN.
يتم تنفيذ عمليات المؤشر باستخدام الأمر FETCH. يتم إغلاق المؤشر باستخدام الأمر CLOSE.
يحدد الأمر DECLARE CURSOR عبارة SELECT. يمكن استرداد كل صف يتم إرجاعه بواسطة عبارة SELECT ومعالجته بشكل فردي. في مثال أوراكل التالي ، تم الإعلان عن المؤشر في كتلة إعلان إلى جانب العديد من المتغيرات الأخرى. بعد ذلك ، في كتلة BEGIN… END اللاحقة ، يتم فتح المؤشر ، ويتم التحديد عليه ، ويتم إغلاق المؤشر.
CURSOR title_price_cursor هي تحديد العنوان والسعر من العناوين
حيث السعر ليس فارغًا ؛ title_price_val title_price_cursor ROWTYPE ؛ سعر_الجديد NUMBER (10.2) ،
افتح العنوان_السعر_المؤشر ؛
FETCH title_price_cur-sor INTO title_price_val؛
new_price: = "title_price_val.price" * 1.25 أدخل قيمة new_title_price
(title_price_val.title، new_price) CLOSE title_price_cursor؛ نهاية؛
نظرًا لأن هذا المثال يستخدم PL / SQL ، فلن يتم شرح معظم الكود في هذا الكتاب. ومع ذلك ، يكون إعلان المؤشر مرئيًا بوضوح في كتلة DECLARE. في كتلة PL / SQL القابلة للتنفيذ ، تتم تهيئة المؤشر بعبارة OPEN ، ويتم استرداد القيم بعبارة FETCH ، وفي النهاية يتم إغلاق المؤشر بعبارة CLOSE.
عبارة SELECT هي جوهر المؤشر ، لذا من الجيد اختبارها جيدًا قبل تضمينها في بيان DECLARE CURSOR. يمكن أن تعمل عبارة SELECT على جدول أو طريقة عرض أساسية. لذلك ، يمكن أن تعمل مؤشرات القراءة فقط مع طرق العرض غير القابلة للتحديث. يمكن أن تحتوي عبارة SELECT على جمل مثل ORDER BY و GROUP BY و HAVING طالما أن هذه الجمل لا تُحدِّث الجدول المصدر. إذا تم تعريف المؤشر على أنه FOR UPDATE ، فمن المستحسن إزالة هذه الجمل من عبارة SELECT.
غالبًا ما تُستخدم المؤشرات المحلية كمعلمات إخراج للإجراءات المخزنة. لذلك ، في إجراء مخزن ، يمكنك تحديد وملء مؤشر وتمريره إلى الوظيفة الدفعية أو الإجراء المخزن الذي يطلق عليه.
في مثال DB2 البسيط التالي ، سنعلن عن مؤشر يبحث عن أرقام الأقسام وأسماء الأقسام وأرقام المديرين في admin_group "XO1".
أعلن عن قسم المؤشر
لـ SELECT dept_nbr، dept_name، mgr_nbr
أين admin_group = "X01"
ORDER BY d "ept_name ASC، dept_nbr DESC، mgr_nbr DESC؛
يوضح المثال التالي لـ Microsoft SQL Server مؤشرًا على جدول الناشرين ويفتحه. يحدد المؤشر السجل الأول من جدول الناشرين الذي يتطابق مع عبارة SELECT ويدرجه في جدول آخر. ثم ينتقل إلى السجل التالي ، ثم إلى السجل التالي ، حتى تتم معالجة جميع السجلات. أخيرًا ، يتم إغلاق المؤشر ويتم تحرير الذاكرة (يتم استخدام الأمر DEALLOCATE فقط في Microsoft SQL Server).
DECLAREpublisher_name VARCHAR (20)
إعلان pub_cursor CURSOR لتحديد اسم pub_name من الناشرين حيث البلد "USA"
إحضار التالي من pub_cursor إلى publisher_name
بينماs> FETCH_STATUS = 0
أدخل قيم الناشرين الأجانب ("j> اسم_الناشر)
CLOSE pub_cursor DEALLOCATE pub_cursor
في هذا المثال ، يمكنك مشاهدة كيفية تحرك المؤشر عبر مجموعة السجلات. (هذا المثال يهدف فقط إلى توضيح هذه الفكرة ، حيث يوجد في الواقع ملف أفضل طريقةحل هذه المشكلة ، وهي عبارة INSERT ، SELECT.)
ينطبق على: SQL Server (منذ 2008) Base بيانات SQL AzureSQL AzureParallel Data Warehouse
تحدد سمات مؤشر جانب الخادم Transact-SQL ، مثل خصائص البحث والاستعلام المستخدم لبناء مجموعة النتائج التي يعمل عليها المؤشر. تدعم عبارة DECLARE CURSOR كلاً من بناء جملة ISO القياسي وبناء الجملة باستخدام مجموعة ملحقات لغة Transact-SQL.
صيغة ISO قم بتعريف cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR (READ ONLY | UPDATE [OF column_name [، ... n]])] [؛] SQL للعمليات الموسعة النحوية DECLARE cursor_name CURSOR [محلي | عالمي] [FORWARD_ONLY | SCROLL] [ثابت | مجموعة المفاتيح | ديناميكي | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] لـ select_statement [FOR UPDATE [OF column_name [، ... n]]] [؛]
cursor_name
cursor_name
حساس
tempdb؛ لذلك ، لا تنعكس التغييرات التي تم إجراؤها على الجداول الأساسية في البيانات التي يتم إرجاعها بواسطة التحديدات الخاصة بهذا المؤشر ، ولا يتم تعديل هذا المؤشر. عند استخدام صيغة ISO ، إذا لم يتم تحديد الخيار INSENSITIVE ، فستنعكس عمليات التحديد والتحديثات الملتزمة التي تم إجراؤها على الجداول الأساسية في التحديدات اللاحقة.
التمرير السريع
يحدد أن جميع خيارات أخذ العينات (الأول ، الأخير ، السابق ، التالي ، النسبي ، المطلق) متاحة. إذا لم تحدد عبارة ISO DECLARE CURSOR خيار SCROLL ، فسيتم دعم خيار الجلب التالي فقط. لا يمكن تحديد خيار SCROLL مع خيار FAST_FORWARD.
select_statement
عبارة SELECT القياسية التي تحدد مجموعة نتائج المؤشر. الكلمات الرئيسية FOR BROWSE و INTO غير صالحة في select_statementإعلان المؤشر.
select_statementتتعارض مع مؤشر من النوع المطلوب.
يقرأ فقط
تحديث ]
اسم العمود [,
.. .ن] ، فقط الأعمدة المدرجة هي التي تسمح بالتغييرات. إذا تم استخدام عبارة UPDATE بدون قائمة الأعمدة ، فسيكون التحديث ممكنًا لجميع الأعمدة.
cursor_name
اسم Transact-SQL لمؤشر الخادم المحدد. cursor_nameيجب أن يتوافق مع قواعد المعرفات.
محلي
يشير إلى أن المؤشر محلي للحزمة أو الإجراء المخزن أو المشغل الذي تم إنشاؤه من خلاله. اسم المؤشر صالح فقط داخل هذه المنطقة. يمكن الإشارة إلى المؤشر بواسطة متغيرات الحزمة المحلية أو الإجراءات المخزنة أو المشغلات أو معلمة إخراج إجراء مخزن. يتم استخدام معلمة OUTPUT لتمرير مؤشر محلي إلى حزمة الاستدعاء أو الإجراء المخزن أو المشغل ، والذي يمكنه بعد ذلك تعيين المعلمة إلى متغير المؤشر للوصول إلى المؤشر بعد اكتمال الإجراء المخزن. يتم تحرير المؤشر بشكل ضمني عند اكتمال تنفيذ دفعة أو إجراء مخزن أو مشغل ، ما لم يتم تمرير المؤشر إلى معلمة OUTPUT. إذا تم تمرير المؤشر إلى معلمة OUTPUT ، فسيتم تحرير المؤشر عند تحرير جميع المتغيرات التي تشير إليه أو عند الخروج من النطاق.
عالمي
يحدد أن المؤشر عام للاتصال. يمكن استخدام اسم المؤشر بواسطة أي إجراء أو حزمة مخزنة يتم تنفيذها على الاتصال. يتم تحرير المؤشر بشكل ضمني فقط في حالة قطع الاتصال.
إلى الأمام فقط
يحدد أنه لا يمكن تمرير المؤشر إلا من السطر الأول إلى الأخير. يتم دعم خيار جلب NEXT فقط. إذا تم تحديد FORWARD_ONLY بدون الكلمات الأساسية الثابتة أو KEYSET أو DYNAMIC ، فسيعمل المؤشر كمؤشر DYNAMIC. إذا لم يتم تحديد وسيطة FORWARD_ONLY ولا وسيطة SCROLL ، يتم استخدام الوسيطة FORWARD_ONLY افتراضيًا ما لم تكن الكلمات الأساسية STATIC أو KEYSET أو DYNAMIC موجودة. يتم تعيين مؤشرات STATIC و KEYSET و DYNAMIC بشكل افتراضي على SCROLL. بخلاف واجهات برمجة تطبيقات قاعدة البيانات مثل ODBC و ADO ، يتم دعم وضع FORWARD_ONLY بواسطة مؤشرات لغة Transact-SQL التالية: STATIC و KEYSET و DYNAMIC.
ثابتة
يحدد المؤشر الذي يقوم بإنشاء نسخة مؤقتة من البيانات ليستخدمها المؤشر. تشير جميع استعلامات المؤشر إلى الجدول المؤقت المحدد بتنسيق tempdb؛ لذلك ، لا تنعكس التغييرات التي تم إجراؤها على الجداول الأساسية في البيانات التي يتم إرجاعها بواسطة التحديدات الخاصة بهذا المؤشر ، ولا يتم تعديل هذا المؤشر.
مجموعة المفاتيح
يحدد أن عضوية أو ترتيب الصفوف في المؤشر لم يتغير عند فتحه. يتم تضمين مجموعة من المفاتيح التي تحدد الصفوف بشكل فريد في جدول في tempdbمُسَمًّى مفاتيح.
يتم عرض التغييرات التي تم إجراؤها على القيم غير الرئيسية في الجداول الأساسية التي أجراها مالك المؤشر أو قام بها مستخدمون آخرون عندما يعرض المالك المؤشر. لا يتم عرض التغييرات التي أجراها المستخدمون الآخرون (لا يمكن إجراء التغييرات باستخدام مؤشر خادم Transact-SQL). إذا تم حذف صف ، فإن محاولة إحضار الصفوف ترجعFETCH_STATUS -2. تشبه تحديثات القيم الأساسية من خارج المؤشر حذف صف قديم ثم إدراج صف جديد. الصف الذي يحتوي على القيم الجديدة غير مرئي ومحاولات جلب الصف بالقيم القديمة تُرجعFETCH_STATUS -2. تظهر التحديثات على الفور إذا تم إجراؤها من خلال المؤشر باستخدام جملة WHERE CURRENT OF.
متحرك
يحدد المؤشر الذي يعرض جميع تغييرات البيانات التي تم إجراؤها على الصفوف في مجموعة النتائج أثناء عرض هذا المؤشر. قد تتغير قيم البيانات وترتيبها وعضوية الصفوف في كل نموذج. لا تدعم المؤشرات الديناميكية خيار التحديد ABSOLUTE.
FAST_FORWARD
تحديد مؤشر FORWARD_ONLY ، READ_ONLY الذي تم تمكين تحسين الأداء له. لا يمكن تحديد خيار FAST_FORWARD باستخدام خياري SCROLL أو FOR_UPDATE.
يقرأ فقط
يمنع التغييرات التي تم إجراؤها من خلال هذا المؤشر. لا يمكن أن تشير جملة WHERE CURRENT OF إلى مؤشر في عبارة UPDATE أو DELETE. هذا الإعداد له الأسبقية على القدرة الافتراضية لتحديث المؤشر.
SCROLL_LOCKS
يحدد أن التحديثات الموضوعة أو عمليات الحذف التي يقوم بها المؤشر مضمونة للنجاح. يقوم SQL Server بتأمين الصفوف أثناء قراءتها في المؤشر للتأكد من توفر هذه الصفوف للتعديلات اللاحقة. لا يمكن تحديد خيار SCROLL_LOCKS باستخدام خيار FAST_FORWARD أو STATIC.
مستبشر
يحدِّد عدم إجراء التحديثات أو عمليات الحذف التي يتم إجراؤها بواسطة المؤشر إذا تم تحديث الصف منذ قراءته في المؤشر. لا يقوم SQL Server بتأمين الصفوف أثناء قراءتها في المؤشر. بدلا من ذلك ، يتم استخدام المقارنات الطابع الزمنيقيم العمود أو اختباريإذا كان الجدول لا يحتوي على الطابع الزمنيلتحديد ما إذا كان قد تم تعديل الصف منذ قراءته في المؤشر. إذا تم تعديل الصف ، فستفشل محاولات التحديث أو الحذف المحددة. لا يمكن تحديد خيار OPTIMISTIC مع خيار FAST_FORWARD.
TYPE_WARNING
يشير إلى أنه سيتم إرسال تحذير إلى العميل إذا تم تحويل المؤشر ضمنيًا من نوع مطلوب إلى آخر.
select_statement
عبارة SELECT القياسية التي تحدد مجموعة نتائج المؤشر. لا يُسمح بالكلمات الرئيسية COMPUTE و COMPUTE BY و FOR BROWSE و INTO في select_statementإعلان المؤشر.
يقوم SQL Server ضمنيًا بتحويل المؤشر إلى نوع مختلف إذا كانت الجمل بتنسيق select_statementتتعارض مع مؤشر من النوع المطلوب. لمزيد من المعلومات ، راجع تحويلات المؤشر الضمنية.
للتحديث]
يحدد الأعمدة القابلة للتحديث في المؤشر. إذا كان OF اسم العمود [,
... ن] ، فقط الأعمدة المدرجة هي التي تسمح بالتغييرات. إذا تم استخدام عبارة UPDATE بدون قائمة الأعمدة ، يكون التحديث ممكنًا لجميع الأعمدة ، ما لم يتم تحديد خيار READ_ONLY التزامن.
تحدد عبارة DECLARE CURSOR سمات مؤشر جانب الخادم Transact-SQL ، مثل خصائص البحث والاستعلام المستخدم لتكوين مجموعة النتائج التي يعمل عليها المؤشر. تملأ العبارة OPEN مجموعة النتائج ، وترجع جملة FETCH صفاً منها. تقوم عبارة CLOSE بمسح مجموعة النتائج الحالية المرتبطة بالمؤشر. تقوم عبارة DEALLOCATE بإصدار الموارد المستخدمة بواسطة المؤشر.
يستخدم النموذج الأول من عبارة DECLARE CURSOR بناء جملة ISO لتعيين خيارات المؤشر. يستخدم النموذج الثاني من عبارة DECLARE CURSOR ملحقات Transact-SQL لتحديد المؤشرات باستخدام نفس الأنواع المستخدمة في وظائف مؤشر API لقاعدة البيانات مثل ODBC و ADO.
لا يمكنك مزج هذين الشكلين. إذا قمت بتحديد SCROLL أو تجاهل الكلمات الأساسية قبل الكلمة الأساسية CURSOR ، فلا يمكنك استخدام الكلمات الأساسية بين CURSOR وأيضًا لـ select_statementالكلمات الدالة. عند تحديد الكلمات الرئيسية بين المؤشر وكذلك من أجل select_statementالكلمات الرئيسية ، لا يمكنك تحديد SCROLL أو INSENSITIVE قبل الكلمة الأساسية CURSOR.
إذا لم يتم تحديد خيارات READ_ONLY أو OPTIMISTIC أو SCROLL_LOCKS لعبارة DECLARE CURSOR عند استخدام بناء جملة Transact-SQL ، فسيتم افتراض القيمة الافتراضية التالية.
إذا كانت عبارة SELECT لا تدعم التحديثات (أو لا توجد أذونات كافية ، أو عند الوصول إلى الجداول البعيدة التي لا تدعم التحديثات ، وما إلى ذلك) ، فسيتم تعيين الخيار READ_ONLY للمؤشر.
يتم تعيين مؤشرات STATIC و FAST_FORWARD افتراضيًا على READ_ONLY.
يتم تعيين مؤشرات DYNAMIC و KEYSET افتراضيًا على OPTIMISTIC.
لا يمكن الإشارة إلى المؤشرات إلا من خلال عبارات SQL للعمليات الأخرى. لا يمكن أن تشير دالات API لقاعدة البيانات إلى المؤشرات. على سبيل المثال ، بمجرد التصريح عن مؤشر ، لا يمكن أن تشير وظائف وأساليب OLE DB أو ODBC أو ADO إلى اسمه. لا يمكن تحديد صفوف المؤشر باستخدام الوظائف المناسبة وطرق API ؛ يجب استخدام عبارات إحضار SQL للعمليات لهذا الغرض.
يمكن استخدام الإجراءات المخزنة التالية لتحديد خصائص المؤشر بعد التصريح عنها.
يمكن استخدام المتغيرات كجزء select_statementالذي يتم فيه التصريح عن المؤشر. لا يتم تغيير قيم متغيرات المؤشر بعد إعلانها.
بشكل افتراضي ، يتم منح أذونات DECLARE CURSOR لجميع المستخدمين الذين لديهم إذن التحديد في طرق العرض والجداول والأعمدة التي يستخدمها المؤشر.
لا يمكنك استخدام المؤشرات أو المشغلات على جدول به فهرس مخزن أعمدة متفاوت. لا ينطبق هذا القيد على فهارس nonclustered؛ يمكنك استخدام المؤشرات والمشغلات على جدول مع فهرس مخزن أعمدة غير متفاوت.
أ. استخدام مؤشر بسيط وبناء الجملة
تتضمن مجموعة النتائج التي تم إنشاؤها عند فتح هذا المؤشر جميع صفوف وأعمدة الجدول. هذا المؤشر قابل للتحديث ، ويتم تمثيل كافة التحديثات وعمليات الحذف في التحديد الخاص بهذا المؤشر. إحضار "التالي هو مجرد جلب لأنه لم يتم تحديد SCROLL.
قم بتعريف vend_cursor CURSOR FOR SELECT * FROM Purchase.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor؛
ب. استخدام المؤشرات المتداخلة لعرض تقرير
يستخدم المثال التالي المؤشرات المتداخلة لعرض تقرير معقد. يتم الإعلان عن مؤشر داخلي لكل مزود.
ضبط NOCOUNT ON ؛
DECLAREvendor_id int ،vendor_name nvarchar (50) ،message varchar (80) ،product nvarchar (50) ؛مطبعة" -------- تقرير منتجات البائع -------- "؛
DECLARE vendor_cursor CURSOR FOR SELECT VendorID، Name from Purchase.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID؛افتح vendor_cursor إحضار التالي من vendor_cursor INTOvendor_id،vendor_name WHILEFETCH_STATUS = 0 ابدأ الطباعة "" SELECTmessage = "----- منتجات من البائع:"+vendor_name PRINT @ الرسالة - إعلان مؤشر داخلي قائم
- في vendor_id من المؤشر الخارجي.قم بتعريف product_cursor CURSOR FOR SELECT v. الاسم من الشراء. - قيمة متغيرة من المؤشر الخارجيافتح product_cursor إحضار التالي من product_cursor إلى @ المنتج IFFETCH_STATUS<>0 طباعة "<
المؤشر - رابط إلى منطقة ذاكرة السياق. في بعض تطبيقات لغة برمجة SQL (Oracle ، Microsoft SQL Server) ، تم الحصول على مجموعة النتائج عند تنفيذ استعلام ومؤشر السجل الحالي المرتبط به. قد يقول أن المؤشر هو جدول افتراضي وهو عبارة عن مخزن بيانات بديل. في نفس الوقت ، يسمح لك المؤشر بالوصول إلى بياناتك كما لو كانت مصفوفة عادية.
يتم استخدام المؤشرات في الإجراءات المخزنة. نظرية كافية ، دعنا نلقي نظرة على مثال:
لدينا قاعدة بيانات (قاعدة البيانات ليست جيدة بعض الشيء ، هذه واحدة من العمل المخبري، لكن مدرس قاعدة البيانات لدينا أصر على مثل هذا الهيكل)
/* التفاصيل المصرفية */
إنشاء جدول "بنك" (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT" "،
المفتاح الأساسي ("BankId")) المحرك = InnoDB
مجموعة الأحرف "utf8" COLLATE "utf8_bin" ؛
/ * بيانات الإيداع * /
إنشاء جدول "bankdistribution" (
"BankId" INTEGER (11) ليس فارغًا ،
"رقم" عدد صحيح (11) افتراضي فارغ ،
"مبلغ المساهمة" عشري (10،0) ليس فارغًا ،
"ClientId" عدد صحيح (11) ليس فارغًا ،
PRIMARY KEY (`BankId`،` ClientId`) ،
مفتاح "BankId" ("BankId") ،
مفتاح "ClientId" ("ClientId") ،
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (` BankId`) المراجع `bank` (` BankId`) ،
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (` ClientId`) المراجع `client` (` ClientId`)
) المحرك = InnoDB
/ * بيانات عن المساهمين * /
إنشاء جدول "العميل" (
"ClientId" عدد صحيح (3) ليس NULL AUTO_INCREMENT ،
"CreditCardId" BIGINT (10) ليست فارغة ،
"اللقب" VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ،
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT" "،
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT" "،
"Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT" "،
"العنوان" VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ،
"SafeId" INTEGER (5) ليس فارغًا ،
PRIMARY KEY ("ClientId" ، "CreditCardId") ،
مفتاح "معرف العميل" ("معرف العميل")) المحرك = InnoDB
AUTO_INCREMENT = 11 مجموعة أحرف "utf8" COLLATE "utf8_bin"
لنفترض أننا بحاجة إلى استلام كل بنك بدوره وتنفيذ بعض الإجراءات معه ، فقد يساعدنا هذا الطلب في هذا الأمر
حدد "البنك". * من "البنك" LIMIT RECORD_NUMBER_WE NEED ، 1. وبالتالي ، باستخدام LIMIT NUMBER_NECESSARY_US_RECORD ، 1 نستخرج كل سجل في حلقة من جدول البنك وننفذ الإجراءات التي نحتاجها معه ، مع زيادة القيمة NUMBER_NECESSARY_US_RECORD بمقدار 1. الآن سنفعل الشيء نفسه ولكن باستخدام المؤشر
يبدأ
/ * المتغيرات التي نحضر فيها البيانات * /
نعلن عدد صحيح BBankId ؛
نعلن اسم فاركار (50) ؛
إعلان vAddress VARCHAR (50) ؛
إعلان vPhone VARCHAR (50) ؛
/ * هادلر متغير - أ * /
التصريح عن القيام بعدد صحيح افتراضي 0 ؛
/ * إعلان المؤشر * /
قم بتعريف مؤشر BankCursor لتحديد "bank". "BankId" ، "bank". "BankName" ، "bank". "العنوان" ، "bank". "الهاتف" ، من "bank" حيث 1 ؛
/ * مهمة HANDLER ، والتي سيتم شرحها أدناه * /
تم إجراء DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET = 1 ؛
/ * فتح المؤشر * /
OpenBankCursor ؛
/* استخراج البيانات */
أثناء القيام به = 0 DO
نحن نفعل ما نحتاج إلى القيام به
تنتهي بينما ؛
/ * إغلاق المؤشر * /
أغلق BankCursor ؛
نهاية ؛* تم تمييز كود المصدر هذا باستخدام Source Code Highlighter.
الخطأ: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
الرسالة: لا توجد بيانات - لم يتم جلب صفوف أو تحديدها أو معالجتها
SQLSTATE: 02000 إطلاق النار عند الوصول إلى نهاية المؤشر ، أو عند تحديد أو تحديث إرجاع سلسلة فارغة.
في السطر التالي ، أعلنا عن المؤشر DECLARE cursor_name CURSOR FOR select_statement ؛
افتح المؤشر Open cursor_name؛
علاوة على ذلك ، حتى نصل إلى نهاية المؤشر (بينما تم = 0 DO) ، نقوم باستخراج البيانات ومعالجتها.
أغلق المؤشر قبل الخروج من الإجراء المخزن. أغلق cursor_name ؛
لا يبدو الأمر معقدًا. ولكن هناك العديد من المزالق المرتبطة بـ SQLSTATE "02000".
أثناء القيام به = 0 DO
إحضار مؤشر البنك في BBankId ، اسم البنك ، vAddress ، vPhone ؛
حدد (ContributeAmount) إلى vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛
قم ببعض الإجراءات
تنتهي بينما ؛* تم تمييز كود المصدر هذا باستخدام Source Code Highlighter.
كل شيء جيد وصحيح من وجهة نظر النحو. لكن منطقيا ، لا. قد يحدث أن المساهمين لم يفتحوا حسابات في أي بنك ، ثم لـ Select (ContributeAmount) INTO vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛ SQLSTATE: سيتم تشغيل 02000 ، سيتم تعيين متغير تم على 1 ، وستنتهي حلقة while في وقت أقرب مما توقعنا. يمكن تجنب ذلك عن طريق القيام بما يلي
أثناء القيام به = 0 DO
إحضار مؤشر البنك في BBankId ، اسم البنك ، vAddress ، vPhone ؛
/ * استخراج مبلغ أي من ودائعه للبنك * /
إذا (vContributeAmountSUM> 0) ثم
/ * استخراج مبلغ أي من ودائعه للبنك * /
إنهاء إذا ؛
قم ببعض الإجراءات
تنتهي بينما ؛* تم تمييز كود المصدر هذا باستخدام Source Code Highlighter.
مع الطلب الأول ، تحققنا مما إذا كانت هناك مساهمات (إذا لم تكن هناك مساهمات ، فعندئذٍ vContributeAmountSUM == 0) وفقط في حالة وجود أي مساهمات ، نقوم باستخراج البيانات.
لنفترض الآن أننا بحاجة إلى استرداد المبلغ الإجمالي للحسابات في بنوك مختلفة لكل عميل
قم بتعريف مؤشر ClientSummCursor لتحديد المبلغ
قم بتعريف ClientSummCursor Cursor للمبلغ المحدد (`bankdistribution`.`ContributeAmount`) ،` bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) حيث توجد مجموعة واحدة عن طريق` bankdistribution`. "معرف العميل" ؛افتح ClientSummCursor ؛
أثناء القيام به = 0 DO
إحضار مؤشر البنك في BBankId ، اسم البنك ، vAddress ، vPhone ؛
/ * استخراج مبلغ أي من ودائعه للبنك * /
حدد Count (ContributeAmount) INTO vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛
/ * تحقق مما إذا كانت هناك ودائع بالفعل في هذا البنك * /
إذا (vContributeAmountSUM> 0) ثم
/ * استخراج مبلغ أي من ودائعه للبنك * /
حدد ContributeAmount INTO vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛
إنهاء إذا ؛
نقوم ببعض الإجراءات.
تنتهي بينما ؛* تم تمييز كود المصدر هذا باستخدام Source Code Highlighter.
يمكن أن يحدث نفس الموقف عندما تنتهي البيانات الموجودة في ClientSummCursor قبل البيانات الموجودة في BankCursor ، يتم تشغيل SQLSTATE: 02000 ، ويتم تعيين المتغير المنجز على 1 ، وتنتهي حلقة while في وقت أبكر مما توقعنا. يمكن تجنب ذلك عن طريق القيام بما يلي
افتح ClientSummCursor ؛
أثناء القيام به = 0 DO
إحضار مؤشر البنك في BBankId ، اسم البنك ، vAddress ، vPhone ؛
/ * استخراج مبلغ أي من ودائعه للبنك * /
حدد Count (ContributeAmount) INTO vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛
/ * تحقق مما إذا كانت هناك ودائع بالفعل في هذا البنك * /
إذا (vContributeAmountSUM> 0) ثم
/ * استخراج مبلغ أي من ودائعه للبنك * /
حدد ContributeAmount INTO vContributeAmountSUM من توزيع البنك حيث BankId = BBankId limit 1 ؛
إنهاء إذا ؛
/ * قبل جلب البيانات من المؤشر الثاني ، تذكر حالة sqlstate * /
SET old_status = تم ؛
/ * استخراج البيانات التي نحتاجها * /
إحضار ClientSummCursor INTO vSum ، vClientId ؛
/ * تحقق مما إذا تم جلب البيانات ، في حالة فشل sqlstate 0200 * /
إذا (تم = 0) ثم
نقوم ببعض الإجراءات.
إنهاء إذا ؛
/ * قبل نهاية الفترة ، قم باستعادة قيمة المتغير المنجز * /
تم ضبطه = old_status ؛
تنتهي بينما ؛* تم تمييز كود المصدر هذا باستخدام Source Code Highlighter.
شكرًا لكل من قرأ هذا حتى الآن ، أتمنى أن يكون هذا مفيدًا لشخص ما.
يشبه تنفيذ المؤشر في قاعدة البيانات فئة Java التي تحتوي على مجموعة من البيانات والطرق لمعالجتها. حيث مؤشر SQLيستخدم البيانات كمصفوفة عادية. يمكن استخدام المؤشرات في المشغلات والإجراءات والوظائف المخزنة.
وفقًا لمعيار SQL ، عند التعامل مع المؤشرات ، يتم تنفيذ الإجراءات الرئيسية التالية:
- إعلان المؤشر
- فتح المؤشر بقراءة البيانات ؛
- اختيار البيانات سطرًا بسطر من المؤشر ؛
- تغيير مع مؤشر بيانات الصف ؛
- إغلاق المؤشر ، وبعد ذلك يتعذر الوصول إليه ؛
- تحرير المؤشر ، أي إزالة المؤشر من الذاكرة ، لأن إغلاقها لا يؤدي بالضرورة إلى تحرير الذاكرة المرتبطة به.
في تطبيقات مختلفة ، التعريف المؤشرقد يكون هناك بعض الاختلافات. لذلك ، على سبيل المثال ، من الضروري في بعض الأحيان تحرير الذاكرة المخصصة للمؤشر بشكل صريح. عندما يتم تحرير المؤشر ، يتم أيضًا تحرير الذاكرة المرتبطة به. هذا يجعل من الممكن إعادة استخدام اسم المؤشر. في تطبيقات أخرى ، عند إغلاق المؤشر ، يتم إلغاء تخصيص الذاكرة ضمنيًا.
في بعض الحالات ، لا غنى عن استخدام المؤشر. ومع ذلك ، إذا أمكن ، يجب تجنب استخدام المؤشر والعمل مع أوامر معالجة البيانات القياسية: SELECT ، UPDATE ، INSERT ، DELETE. هذا يرجع إلى حقيقة أن المؤشرات لا تسمح بتغيير العمليات على كمية البيانات بالكامل وأن سرعة عمليات معالجة البيانات باستخدام المؤشر أقل بشكل ملحوظ من الوسائل القياسية SQL.
إذا كان البرنامج يمكنه تغيير البيانات التي تم تحميلها في المؤشر ، فإنه يسمى قابل للتعديل. بالحديث عن المؤشرات ، يجب ألا ننسى عزل المعاملات. يقوم أحد المستخدمين بتعديل السجل بمؤشر بينما يقرأ مستخدم آخر السجل بمؤشره الخاص. علاوة على ذلك ، يمكنه تغيير نفس السجل ، مما يستلزم تكامل البيانات.
إعلان المؤشر ، أعلن المؤشر
يجب الإعلان عن المؤشرات قبل استخدامها. يستخدم معيار SQL بناء الجملة التالي لإنشاء مؤشر:
قم بتعريف مؤشر cursor_name لـ select_statement])]
يعلن هذا التعبير عن مؤشر تعلن المؤشرباسم "cursor_name".
حساسيتم إنشاء مؤشر ثابت لا يسمح بالتغييرات. أيضًا ، لا يتم عرض التغييرات التي أجراها المستخدمون الآخرون. في حالة عدم وجود كلمة رئيسية حساسة ، يتم إنشاء مؤشر ديناميكي.
عند استخدام الكلمة التمرير السريعيمكن تمرير المؤشر الذي تم إنشاؤه في أي اتجاه ، مما يسمح بتطبيق أي أوامر تحديد. إذا تم حذف هذه الوسيطة ، فسيكون المؤشر متسلسلًا ، أي لن يكون عرضه ممكنًا إلا في اتجاه واحد - من البداية إلى النهاية.
تعبير select_statementيشير إلى بناء لقراءة المعلومات من نوع حدد ... من .... يجب ألا تحتوي على عامل التشغيل داخل، لأن المؤشر له عامل التشغيل الخاص به أحضرلتعبئة المتغيرات ببيانات المؤشر.
عند إعطاء حجة للقراءة فقطسيتم إنشاء مؤشر للقراءة فقط ولا يُسمح بإجراء أي تعديلات على البيانات. يمكن الإعلان عن مؤشر للقراءة فقط كمؤشر ديناميكي ليعكس التغييرات التي أجراها مستخدم آخر.
إنشاء مؤشر مع حجة للتحديثيسمح لك بتغيير البيانات في المؤشر إما في الأعمدة المحددة ، أو في حالة عدم وجود وسيطة من اسم العمود، في جميع الأعمدة.
يمكنك إعلان عدة مؤشرات في روتين فرعي. لكن يجب أن يكون لكل مؤشر اسم فريد. لفتح المؤشر ، استخدم عامل التشغيل يفتح، والذي يفتح المؤشر المعلن مسبقًا:
فتح المؤشر
يعرّف SQL بناء الجملة التالي لفتح مؤشر "فتح المؤشر":
افتح cursor_name ؛
جلب البيانات من المؤشر ، وجلب المؤشر
فيما يلي بناء الجملة لقراءة البيانات من المؤشر إلى بعض المتغيرات:
إحضار cursor_name إلى var_name [، var_name] ... ؛
المشغل أو العامل أحضريجلب بيانات المؤشر المفتوح إلى المتغيرات الموجودة بعد داخلويحرك المؤشر إلى الموضع التالي.
إغلاق المؤشر ، إغلاق المؤشر
المشغل أو العامل يغلقيغلق المؤشر. إذا لم يتم تحديد العبارة بشكل صريح ، فسيتم إغلاق المؤشر تلقائيًا عند إغلاق كتلة البرنامج المقابلة.
أغلق cursor_name ؛
بعد الإغلاق ، يصبح المؤشر غير قابل للوصول. عند الإغلاق ، يتم تحرير جميع الأقفال التي تم ضبطها أثناء عملية المؤشر. يمكن إغلاق المؤشرات المفتوحة فقط. يمكن إعادة فتح مؤشر مغلق ولكن لم يتم تحريره. لا يجوز إغلاق مؤشر غير مفتوح.
كل DBMS له خصائصه الخاصة في استخدام المؤشر.
ميزات استخدام المؤشرات في Oracle
توجد أربع سمات للمؤشر في PL / SQL ٪وجد, ٪غير معثور عليه, ٪مفتوحو ٪ ROWCOUNT. يتم التصريح عن سمات المؤشر مثل عبارات٪ TYPE و٪ ROWTYPE ، إلى يمين اسم المؤشر.
السمة التي تم العثور عليها٪
السمة٪ NOTFOUND
السمة٪ NOTFOUND هي عكس٪ FOUND تمامًا.
سمة ISOPEN٪
تشير السمة٪ ISOPEN فقط إلى ما إذا كان المؤشر مفتوحًا أم لا.
سمة٪ ROWCOUNT
يصف ٪ ROWCOUNTهي سمة رقمية تُرجع عدد الصفوف التي يقرأها المؤشر في نقطة زمنية معينة.
مثال مؤشر SQL في Oracle DBMS
قم بتعريف v_id manager.id٪ TYPE ؛ v_name manager.name٪ TYPE ؛ v_comm manager.comm٪ TYPE ؛ مؤشر crs لاختيار معرف ، اسم ، مجموع (بالاتصالات) من المديرين حيث البيانات بين "2014-11-01" و "2014-11-30" مجموعة حسب المعرف ، الاسم ؛ بدء فتح crs ؛ حلقة الخروج عندما crs٪ NOTFOUND ؛ إحضار crs إلى v_id ، v_name ، v_comm ؛ إدراج قيم المكافأة (المعرف ، الاسم ، comm) (crs.id ، crs.name ، crs.comm) ؛ نهاية الحلقة يقترف؛ وثيقة crs نهاية؛
ميزات استخدام المؤشرات في SQL Server
يمكن أن تكون المؤشرات المستخدمة في MSSQL متسلسلة وقابلة للتمرير. يتيح لك التسلسل تحديد البيانات في اتجاه واحد فقط - من البداية إلى النهاية. تسمح المؤشرات القابلة للتمرير بالحركة في كلا الاتجاهين وتسمح لك بالانتقال إلى صف عشوائي في مجموعة نتائج المؤشر.
يدعم SQL Server المؤشرات الثابتة والديناميكية والمتسلسلة والقائمة على مجموعة المفاتيح.
في مخطط بمؤشر ثابت ، يتم تخزين المعلومات كلقطة كنقطة زمنية. لذلك ، التغييرات التي تم إجراؤها على قاعدة البيانات من قبل مستخدم آخر غير مرئية. طوال مدة فتح المؤشر ، يكتسب الخادم قفلًا على جميع الصفوف المضمنة في مجموعة النتائج الكاملة الخاصة به. لا يتغير المؤشر الثابت بعد الإنشاء ويعرض دائمًا مجموعة البيانات التي كانت موجودة وقت فتحه. إذا قام المستخدمون الآخرون بتغيير البيانات المضمنة في المؤشر في الجدول الأصلي ، فلن يؤثر ذلك على المؤشر الثابت بأي شكل من الأشكال. لا يمكن تعديل مؤشر ثابت ، لذلك يفتح دائمًا في وضع القراءة فقط.
يتطلب المؤشر الديناميكي موارد إضافية للشبكة وبرامج. عند استخدام المؤشرات الديناميكية ، لا يتم إنشاء نسخة كاملة من البيانات ، ولكن يتم التحديد من الجداول المصدر فقط عندما يصل المستخدم إلى بيانات معينة. أثناء الجلب ، يقفل الخادم الصفوف ، وستظهر أي تغييرات يقوم بها المستخدم على مجموعة النتائج الكاملة للمؤشر في المؤشر. ومع ذلك ، بمجرد جلب البيانات بواسطة المؤشر ، لن تنعكس التغييرات التي أجراها مستخدم آخر في المؤشر.
يقع المؤشر الذي يعتمد على مجموعة المفاتيح بين الخصائص الثابتة والديناميكية. يتم تحديد السجلات في وقت أخذ العينات ، وبالتالي يتم تتبع التغييرات. هذا النوع من المؤشرات مفيد عند التمرير للخلف. في هذه الحالة ، لا تظهر عمليات إضافة البيانات وحذفها حتى يتم تحديث المعلومات ويختار المؤشر نسخة جديدةيسجل إذا تم إجراء تغييرات عليه.
من الأفضل استخدام المؤشرات الثابتة لأنظمة معالجة المعلومات ، على سبيل المثال. لأنظمة التقارير أو للأغراض الإحصائية والتحليلية. يعتبر المؤشر الثابت أفضل في جلب كميات كبيرة من البيانات. في أنظمة الشراء الإلكتروني أو حجز الأشياء (المقاعد والتذاكر) ، من الضروري إدراك المعلومات المحدثة ديناميكيًا عند إجراء التغييرات. في مثل هذه الحالات ، يتم استخدام مؤشر ديناميكي. في هذه التطبيقات ، عادةً ما يكون مقدار البيانات المنقولة صغيرًا ويمكن الوصول إليه على مستوى السجل الفردي.
لا تسمح المؤشرات المتسلسلة باسترداد البيانات بشكل عكسي ، فقط من بداية المؤشر إلى نهايته. لا يخزن المؤشر المتسلسل مجموعة من كافة صفوف البيانات. تتم قراءتها من قاعدة البيانات بمجرد تنفيذ جلب المؤشر ، مما يسمح لك بعكس جميع التغييرات التي أجراها المستخدمون على قاعدة البيانات ديناميكيًا باستخدام أوامر INSERT و UPDATE و DELETE. يقرأ المؤشر أحدث حالة للبيانات.
إعلان المؤشر
قم بتعريف مؤشر cursor_name لـ SELECT_statement]]
عند استخدام الكلمة محليسيتم إنشاء مؤشر محلي يكون مرئيًا فقط داخل كتلة أو مشغل أو إجراء مخزن أو وظيفة محددة من قبل المستخدم. الكلمة الرئيسية عالمي، يحدد مؤشرًا عامًا موجودًا حتى يتم إغلاق الاتصال الحالي.
المشغل أو العامل إلى الأمام فقطيحدد مؤشرًا متسلسلًا يسمح باسترداد البيانات فقط في الاتجاه من الصف الأول إلى الأخير. عند استخدام المشغل التمرير السريعيتم إنشاء مؤشر قابل للتمرير يسمح بالوصول إلى البيانات بأي ترتيب وفي أي اتجاه.
يتم تحديد نوع المؤشر بواسطة عوامل التشغيل:
- ثابت - إنشاء مؤشر ثابت ؛
- DYNAMIC - إنشاء مؤشر ديناميكي ؛
- KEYSET - إنشاء مؤشر مفتاح.
إذا كان للمؤشر يقرأ فقطحدد حجة FAST_FORWARD، ثم سيتم تحسين المؤشر الذي تم إنشاؤه من أجل وصول سريعللبيانات. لا يمكن استخدام هذه الحجة بالاقتران مع الوسائط إلى الأمام فقطو مستبشر.
إذا تم إنشاء المؤشر مع البيان مستبشر، يمنع تغيير وحذف الأسطر التي تم تغييرها منذ فتح المؤشر.
عند إعطاء حجة TYPE_WARNINGسيقوم الخادم بالإبلاغ عن تغيير نوع المؤشر الضمني إذا كان غير متوافق مع استعلام SELECT.
إحضار البيانات من المؤشر ، الجلب
مباشرة بعد فتح المؤشر ، يمكنك الحصول على محتوياته بالأمر التالي:
عند استخدام المشغل أولاًسيتم إرجاع الصف الأول من مجموعة نتائج المؤشر ، والذي يصبح الصف الحالي. عندما تحدد آخرسيتم إرجاع السطر الأخير من المؤشر. يصبح أيضًا الخط الحالي.
عند تحديد المشغل التاليسيتم إرجاع الصف مباشرة بعد الصف الحالي في مجموعة النتائج. يصبح هذا الخط هو الخط الحالي. الأمر الافتراضي أحضريستخدم بالضبط هذه الطريقة في جلب الصفوف.
عند تحديد المشغل قبلسيتم إرجاع السطر قبل السطر الحالي. يصبح هذا الخط هو الخط الحالي.
المشغل أو العامل مطلق (line_number |line_number_variable)تُرجع صفًا بترتيبها المطلق في مجموعة النتائج الكاملة للمؤشر. يمكن تحديد رقم السطر باستخدام ثابت أو كاسم متغير يخزن رقم السطر. يجب أن يحتوي المتغير على نوع بيانات عدد صحيح. يشار إلى كل من القيم الإيجابية والسلبية. إذا قمت بتحديد قيمة موجبة ، يتم حساب السلسلة من بداية المجموعة ، قيمة سالبة - من النهاية. يصبح الخط المحدد هو السطر الحالي. إذا تم تحديد قيمة خالية ، فلن يتم إرجاع أية سلسلة.
دعوى نسبي (row_number |row_number_variable)تُرجع السلسلة التي تمت إزاحتها بعدد محدد من الأسطر بعد السطر الحالي. إذا قمت بتحديد قيمة سالبة لعدد الأسطر ، فسيتم إرجاع السطر الذي يمثل عدد الأسطر المحدد قبل السطر الحالي. تحديد قيمة خالية سيعيد الصف الحالي. يصبح الصف الذي تم إرجاعه هو الصف الحالي.
لفتح مؤشر عام ، اسبق اسمه بكلمة أساسية عالمي. يمكن أيضًا تحديد اسم المؤشر باستخدام متغير.
في التعبير INTOvariable_name [، ... n]يتم تحديد قائمة المتغيرات التي سيتم فيها تخزين قيم العمود المقابلة للصف الذي تم إرجاعه. يجب أن يتطابق ترتيب المتغيرات مع ترتيب الأعمدة في المؤشر ، ويجب أن يتطابق نوع بيانات المتغير مع نوع البيانات في عمود المؤشر.
تغيير وحذف البيانات باستخدام المؤشر
لتغيير البيانات باستخدام مؤشر ، يجب إصدار أمر UPDATE بالتنسيق التالي:
في عملية واحدة ، يمكن تغيير قيم عدة أعمدة للصف الحالي للمؤشر ، ولكن يجب أن تنتمي جميعها إلى نفس الجدول.
لحذف البيانات باستخدام مؤشر ، استخدم الأمر DELETE بالتنسيق التالي:
نتيجة لذلك ، سيتم حذف السطر الحالي في المؤشر.
إلغاء تخصيص الذاكرة ، إلغاء التخصيص
لإزالة مؤشر من الذاكرة ، استخدم الأمر
إلغاء تخصيص cursor_name ؛
سمةFETCH_STATUS
لتحديد وجود الصفوف في المؤشر ، استخدم المتغير العام FETCH_STATUS، والتي تأخذ قيمة غير صفرية إذا لم يكن هناك المزيد من الصفوف في المؤشر. إذا لم يتم استنفاد مجموعة الصفوف بعد ، فسيكونFETCH_STATUS صفرًا.
مثال المؤشر في SQL Server
Declarecompany varchar (50) ، @ manager varchar (50) ، @ message varchar (256) ؛ إعلان مؤشر crs_clients محليًا لشركة محددة ، مديرًا من العملاء حيث city = "موسكو" أمر حسب الشركة ، المدير ؛ طباعة "قائمة العملاء" ؛ عملاء crs_clients مفتوحون ؛ جلب التالي من crs_clients إلىcompany ، @ manager ؛ whileFETCH_STATUS = 0 ابدأ حددmessage = "Company" +company + "manager" + @ manager؛ طباعة @ رسالة ؛ - انتقل إلى الإدخال التالي لجلب الإدخال التالي من crs_clients إلىcompany ، @ manager ؛ نهاية؛ عملاء crs_clients وثيقون ؛ إلغاء تخصيص عملاء crs_clients ؛
تم تحديد المؤشر. يتم تقديم وصف لأنواعها وسلوكها: مؤشرات ثابتة وديناميكية ومتسلسلة ومفاتيح. تم وصف مبادئ التحكم في المؤشر: إنشاء المؤشر وفتحه ، وقراءة البيانات ، وإغلاق المؤشر. يتم إعطاء أمثلة برمجة المؤشر.مفهوم المؤشر
عادةً ما يُرجع استعلام قاعدة البيانات العلائقية صفوفًا (سجلات) متعددة من البيانات ، لكن التطبيق يعالج سجلًا واحدًا فقط في كل مرة. حتى إذا كان يتعامل مع عدة صفوف في نفس الوقت (على سبيل المثال ، إخراج البيانات في شكل جداول بيانات) ، فإن عددها لا يزال محدودًا. بالإضافة إلى ذلك ، عند تعديل البيانات أو حذفها أو إضافتها ، تكون وحدة العمل هي الصف. في هذه الحالة ، يظهر مفهوم المؤشر في المقدمة ، وفي هذا السياق ، يكون المؤشر مؤشرًا لصف.
المؤشر في SQL هو منطقة في ذاكرة قاعدة البيانات مخصصة لتخزين آخر جملة SQL. إذا كانت العبارة الحالية عبارة عن استعلام قاعدة بيانات ، فإن سلسلة بيانات الاستعلام ، التي تسمى القيمة الحالية ، أو صف المؤشر الحالي ، يتم تخزينها أيضًا في الذاكرة. يتم تسمية المنطقة المحددة في الذاكرة وإتاحتها لبرامج التطبيق.
عادةً ما يتم استخدام المؤشرات لتحديد مجموعة فرعية من المعلومات المخزنة في قاعدة البيانات. يمكن التحقق من سطر واحد من المؤشر بواسطة التطبيق في كل مرة. غالبًا ما تستخدم المؤشرات في جمل SQLمدمج في برامج التطبيق المكتوبة باللغات الإجرائية. يتم إنشاء بعضها ضمنيًا بواسطة خادم قاعدة البيانات ، بينما يتم تحديد البعض الآخر بواسطة المبرمجين.
وفقًا لمعيار SQL ، عند العمل مع المؤشرات ، يمكن تمييز الإجراءات الرئيسية التالية:
- إنشاء أو إعلان المؤشر;
- فتح المؤشر، أي. ملئها بالبيانات المخزنة في ذاكرة متعددة المستويات ؛
- جلب من المؤشروتغيير صفوف البيانات معها ؛
- إغلاق المؤشر، وبعد ذلك يصبح غير متاح لبرامج المستخدم ؛
- تحرير المؤشر، أي. حذف المؤشر ككائن ، لأن إغلاقه لا يؤدي بالضرورة إلى تحرير الذاكرة المرتبطة به.
في تطبيقات مختلفة ، قد يكون لتعريف المؤشر بعض الاختلافات. لذلك ، على سبيل المثال ، يجب على المطور أحيانًا تحرير الذاكرة المخصصة للمؤشر بشكل صريح. بعد تحرير المؤشريتم تحرير الذاكرة المرتبطة بها أيضًا. هذا يجعل من الممكن إعادة استخدام اسمه. في تطبيقات أخرى ، إغلاق المؤشريتم إلغاء تخصيص الذاكرة ضمنيًا. بعد الشفاء مباشرة ، يصبح متاحًا لعمليات أخرى: فتح مؤشر آخرإلخ.
في بعض الحالات ، لا مفر من استخدام المؤشر. ومع ذلك ، إذا أمكن ، يجب تجنب ذلك والعمل مع أوامر معالجة البيانات القياسية: SELECT ، UPDATE ، INSERT ، DELETE. بالإضافة إلى حقيقة أن المؤشرات لا تسمح لك بإجراء عمليات تعديل على كمية البيانات بأكملها ، فإن سرعة تنفيذ عمليات معالجة البيانات باستخدام مؤشر أقل بشكل ملحوظ من أدوات SQL القياسية.
تنفيذ المؤشرات في بيئة MS SQL Server
يدعم SQL Server ثلاثة أنواع من المؤشرات:
- تُستخدم مؤشرات SQL بشكل أساسي داخل المشغلات والإجراءات المخزنة والبرامج النصية ؛
- تعمل مؤشرات الخادم على الخادم وتنفذ واجهة برمجة التطبيقات لـ ODBC و OLE DB و DB_Library ؛
- يتم تطبيق مؤشرات العميل على العميل نفسه. يقومون بجلب مجموعة الصفوف الناتجة بالكامل من الخادم وتخزينها محليًا ، مما يؤدي إلى تسريع عمليات معالجة البيانات عن طريق تقليل إهدار عمليات الشبكة.
تتطلب الأنواع المختلفة من التطبيقات متعددة المستخدمين أنواعًا مختلفة من الوصول المتوازي إلى البيانات. تحتاج بعض التطبيقات إلى وصول فوري إلى معلومات حول التغييرات في قاعدة البيانات. هذا نموذجي لأنظمة حجز التذاكر. في حالات أخرى ، على سبيل المثال ، في أنظمة التقارير الإحصائية ، يكون استقرار البيانات مهمًا ، لأنه إذا تم تعديلها باستمرار ، فلن تتمكن البرامج من عرض المعلومات بشكل فعال. تحتاج التطبيقات المختلفة إلى تطبيقات مختلفة للمؤشرات.
في بيئة SQL Server ، تختلف أنواع المؤشرات من حيث القدرات التي توفرها. يتم تحديد نوع المؤشر في مرحلة إنشائه ولا يمكن تغييره. يمكن لبعض أنواع المؤشرات اكتشاف التغييرات التي أجراها المستخدمون الآخرون على الصفوف المضمنة في مجموعة النتائج. ومع ذلك ، يتعقب SQL Server التغييرات على هذه الصفوف فقط في مرحلة الوصول إلى الصف ولا يسمح بتعديل التغييرات بعد قراءة الصف بالفعل.
تنقسم المؤشرات إلى فئتين: متتابعوقابل للتمرير. تسلسليتسمح لك بتحديد البيانات في اتجاه واحد فقط - من البداية إلى النهاية. مؤشرات قابلة للتمريرتوفير المزيد من حرية العمل - يمكنك التحرك في كلا الاتجاهين والقفز إلى سطر عشوائي لمجموعة نتائج المؤشر.إذا كان البرنامج قادرًا على تعديل البيانات التي يشير إليها المؤشر ، فيُطلق عليه اسم قابل للتمرير وقابل للتعديل. عند الحديث عن المؤشرات ، لا ينبغي لأحد أن ينسى عزل المعاملات. عندما يقوم أحد المستخدمين بتعديل أحد السجلات ، يقوم آخر بقراءته بمؤشره الخاص ، علاوة على ذلك ، يمكنه تعديل نفس السجل ، مما يجعل من الضروري الحفاظ على تكامل البيانات.
يدعم SQL Server الثابت والديناميكي متتابعوتتحكم فيه مجموعة مفاتيح.
في المخطط مع مؤشر ثابتتتم قراءة المعلومات من قاعدة البيانات مرة واحدة وتخزينها كلقطة (اعتبارًا من وقت ما) ، لذلك لا تظهر التغييرات التي أجراها مستخدم آخر على قاعدة البيانات. لفترة وجيزة فتح المؤشريكتسب الخادم قفلًا على جميع الصفوف المضمنة في مجموعة النتائج الكاملة الخاصة به. مؤشر ثابتلا يتغير بعد الإنشاء ويعرض دائمًا مجموعة البيانات التي كانت موجودة وقت فتحها.
إذا قام المستخدمون الآخرون بتغيير البيانات المضمنة في المؤشر في الجدول المصدر ، فلن يؤثر ذلك على ملف مؤشر ثابت.
في مؤشر ثابتلا يمكنك إجراء تغييرات ، لذلك يتم فتحه دائمًا في وضع القراءة فقط.
مؤشر ديناميكيتحافظ على البيانات حية ، ولكنها تتطلب موارد الشبكة والبرامج. استخدام مؤشرات ديناميكيةلا يتم إنشاء نسخة كاملة من البيانات المصدر ، ولكن يتم إجراء تحديد ديناميكي من جداول المصدر فقط عندما يصل المستخدم إلى بيانات معينة. أثناء الجلب ، يقفل الخادم الصفوف ، وستظهر أي تغييرات يقوم بها المستخدم على مجموعة النتائج الكاملة للمؤشر في المؤشر. ومع ذلك ، إذا قام مستخدم آخر بإجراء تغييرات بعد أن قام المؤشر بجلب البيانات ، فلن تنعكس في المؤشر.
يتم التحكم بالمؤشر بواسطة مجموعة من المفاتيحتقع في المنتصف بين هذين الطرفين. يتم تحديد السجلات في وقت أخذ العينات ، وبالتالي يتم تتبع التغييرات. هذا النوع من المؤشر مفيد لتنفيذ التمرير للخلف بحيث لا تظهر عمليات إضافة الصفوف وحذفها حتى يتم تحديث المعلومات ، ويختار السائق إصدارًا جديدًا من الإدخال إذا تم إجراء تغييرات عليه.
مؤشرات متسلسلةلا تسمح بجلب البيانات في الاتجاه العكسي. يمكن للمستخدم تحديد الصفوف فقط من بداية المؤشر إلى نهايته. مؤشر متسلسللا تخزن مجموعة من كل الصفوف. تتم قراءتها من قاعدة البيانات بمجرد تحديدها في المؤشر ، مما يسمح لك بعكس جميع التغييرات التي أجراها المستخدمون على قاعدة البيانات ديناميكيًا باستخدام أوامر INSERT و UPDATE و DELETE. يظهر المؤشر أحدث حالة للبيانات.
مؤشرات ثابتةتوفر عرضًا ثابتًا للبيانات. إنها جيدة لأنظمة "مستودع" المعلومات: تطبيقات لأنظمة التقارير أو للأغراض الإحصائية والتحليلية. بجانب، مؤشر ثابتأفضل من الآخرين يتعامل مع أخذ عينات كمية كبيرة من البيانات. على العكس من ذلك ، في أنظمة الشراء الإلكتروني أو حجوزات التذاكر ، من الضروري إدراك المعلومات المحدثة ديناميكيًا عند إجراء التغييرات. في مثل هذه الحالات ، استخدم مؤشر ديناميكي. في هذه التطبيقات ، عادةً ما يكون مقدار البيانات المنقولة صغيرًا ، ويتم الوصول إليها على مستوى الصفوف (السجلات الفردية). الوصول إلى المجموعة نادر جدًا.
التحكم بالمؤشر في بيئة MS SQL Server
التحكم في المؤشرنفذت بتنفيذ الأوامر التالية:
- أعلن - إنشاء أو إعلان المؤشر;
- يفتح- فتح المؤشر، أي. ملئه بالبيانات ؛
- أحضر- جلب من المؤشروتغيير صفوف البيانات بمؤشر ؛
- يغلق- إغلاق المؤشر;
- مناقشة- تحرير المؤشر، أي. حذف المؤشر ككائن.
إعلان المؤشر
يوفر معيار SQL الأمر التالي لإنشاء مؤشر:
سيؤدي استخدام الكلمة المفتاحية الحسّاسة إلى إنشاء مؤشر ثابت. تغييرات البياناتغير مسموح بها ، بالإضافة إلى ذلك ، لا يتم عرض التغييرات التي أجراها المستخدمون الآخرون. إذا لم تكن الكلمة الرئيسية حساسة ، فسيتم إضافة ملف مؤشر ديناميكي.
من خلال تحديد الكلمة الأساسية SCROLL ، يمكن تمرير المؤشر الناتج في أي اتجاه ، مما يسمح بتطبيق أي أوامر تحديد. إذا تم حذف هذه الوسيطة ، فسيكون المؤشر ثابت، أي. لن يكون عرضه ممكنًا إلا في اتجاه واحد - من البداية إلى النهاية.
تحدد عبارة SELECT نص استعلام SELECT ، والذي يستخدم لتحديد مجموعة النتائج لصفوف المؤشر.
يؤدي تحديد الوسيطة FOR READ_ONLY إلى إنشاء مؤشر للقراءة فقط ولا يسمح بأي تعديلات على البيانات. إنه يختلف عن الثابت ، على الرغم من أن الأخير لا يسمح أيضًا بتغيير البيانات. كمؤشر للقراءة فقط يمكن الإعلان عنها مؤشر ديناميكي، والتي ستعرض التغييرات التي أجراها مستخدم آخر.
يتيح لك إنشاء مؤشر باستخدام وسيطة FOR UPDATE التنفيذ في المؤشر تغيير البياناتإما في الأعمدة المحددة ، أو في جميع الأعمدة في حالة عدم وجود وسيطة اسم العمود.
في بيئة MS SQL Server ، يُقبل بناء الجملة التالي لأمر إنشاء المؤشر:
<создание_курсора>:: = DECLARE cursor_name CURSOR FOR SELECT_statement]]
سيؤدي استخدام الكلمة الأساسية المحلية إلى إنشاء مؤشر محلي يكون مرئيًا فقط داخل الحزمة أو المشغل أو الإجراء المخزن أو الوظيفة المعرفة من قبل المستخدم التي أنشأتها. عند إنهاء حزمة أو مشغل أو إجراء أو وظيفة ، يتم إتلاف المؤشر ضمنيًا. لتمرير محتويات المؤشر خارج البناء الذي أنشأه ، يجب عليك تعيين وسيطة OUTPUT إلى المعلمة الخاصة به.
إذا تم تحديد الكلمة الأساسية GLOBAL ، يتم إنشاء مؤشر عام ؛ موجود حتى يتم إغلاق الاتصال الحالي.
يؤدي تحديد FORWARD_ONLY إلى إنشاء مؤشر متسلسل؛ يمكن أخذ عينات من البيانات فقط في الاتجاه من الصف الأول إلى الأخير.
يؤدي تحديد SCROLL إلى إنشاء مؤشر قابل للتمرير؛ يمكن الوصول إلى البيانات بأي ترتيب وفي أي اتجاه.
يؤدي تحديد STATIC إلى إنشاء مؤشر ثابت.
يؤدي تحديد KEYSET إلى إنشاء مؤشر مفتاح.
يؤدي تحديد DYNAMIC إلى إنشاء مؤشر ديناميكي.
إذا قمت بتحديد وسيطة FAST_FORWARD لمؤشر READ_ONLY ، فسيتم تحسين المؤشر الذي تم إنشاؤه للوصول السريع إلى البيانات. لا يمكن استخدام هذه الوسيطة مع الوسيطتين FORWARD_ONLY و OPTIMISTIC.
يمنع المؤشر الذي تم إنشاؤه باستخدام الوسيطة OPTIMISTIC تعديل وحذف الصفوف التي تم تعديلها منذ ذلك الحين فتح المؤشر.
باستخدام الوسيطة TYPE_WARNING ، سيقوم الخادم بإعلام المستخدم بتغيير نوع المؤشر الضمني إذا كان غير متوافق مع استعلام SELECT.
فتح المؤشر
ل فتح المؤشروتعبئته بالبيانات من المحدد عند إنشاء مؤشر استعلام SELECT ، يتم استخدام الأمر التالي:
بعد فتح المؤشريتم تنفيذ عبارة SELECT المرتبطة ، ويتم تخزين مخرجاتها في ذاكرة ذات طبقات.
إحضار البيانات من المؤشر
تماما بعد فتح المؤشريمكنك تحديد محتواه (نتيجة الاستعلام المقابل) بالأمر التالي:
سيؤدي تحديد FIRST إلى إرجاع الصف الأول من مجموعة نتائج المؤشر الكاملة ، والتي تصبح الصف الحالي.
يؤدي تحديد LAST إلى إرجاع أحدث صف من المؤشر. يصبح أيضًا الخط الحالي.
يؤدي تحديد NEXT إلى إرجاع الصف مباشرةً بعد الصف الحالي في مجموعة النتائج الكاملة. الآن يصبح الحالي. بشكل افتراضي ، يستخدم الأمر FETCH هذه الطريقة لإحضار الصفوف.
الكلمة الأساسية PRIOR ترجع السطر الذي يسبق السطر الحالي. تصبح الحالية.
دعوى مطلق (line_number |line_number_variable)تُرجع صفًا بترتيبها المطلق في مجموعة النتائج الكاملة للمؤشر. يمكن تحديد رقم السطر باستخدام ثابت أو كاسم متغير يخزن رقم السطر. يجب أن يحتوي المتغير على نوع بيانات عدد صحيح. يشار إلى كل من القيم الإيجابية والسلبية. إذا قمت بتحديد قيمة موجبة ، يتم حساب السلسلة من بداية المجموعة ، قيمة سالبة - من النهاية. يصبح الخط المحدد هو السطر الحالي. إذا تم تحديد قيمة خالية ، فلن يتم إرجاع أية سلسلة.
دعوى نسبي (row_number |row_number_variable)تُرجع السلسلة التي هي عدد الأسطر المحدد بعد السطر الحالي. إذا قمت بتحديد قيمة سالبة لعدد الأسطر ، فسيتم إرجاع السطر الذي يمثل عدد الأسطر المحدد قبل السطر الحالي. تحديد قيمة خالية سيعيد الصف الحالي. يصبح الصف الذي تم إرجاعه هو الصف الحالي.
ل فتح المؤشر العالمي، يتطلب الكلمة الأساسية العالمية قبل اسمها. يمكن أيضًا تحديد اسم المؤشر باستخدام متغير.
في البناء INTOvariable_name [، ... n]يتم تحديد قائمة المتغيرات التي سيتم فيها تخزين قيم العمود المقابلة للصف الذي تم إرجاعه. يجب أن يتطابق ترتيب المتغيرات مع ترتيب الأعمدة في المؤشر ، ويجب أن يتطابق نوع بيانات المتغير مع نوع البيانات في عمود المؤشر. إذا لم يتم تحديد بناء INTO ، فإن سلوك أمر FETCH سيشابه سلوك أمر التحديد - يتم عرض البيانات على الشاشة.
تغيير وحذف البيانات
لإجراء تغييرات باستخدام المؤشر ، يجب إصدار أمر UPDATE بالتنسيق التالي:
يمكن تعديل الأعمدة المتعددة للصف الحالي للمؤشر في عملية واحدة ، ولكن يجب أن تنتمي جميعها إلى نفس الجدول.
لحذف البيانات باستخدام مؤشر ، استخدم الأمر DELETE بالتنسيق التالي:
نتيجة لذلك ، سيتم حذف مجموعة السطر الحالية في المؤشر.
إغلاق المؤشر
بعد الإغلاق ، يصبح المؤشر غير متاح لمستخدمي البرنامج. عند الإغلاق ، تتم إزالة جميع الأقفال التي تم ضبطها أثناء التشغيل. لا يمكن تطبيق الإغلاق إلا على المؤشرات المفتوحة. مغلق ولكن لا تحرير المؤشريمكن إعادة فتحه. لا يجوز إغلاق مؤشر غير مفتوح.
تحرير المؤشر
إغلاق المؤشريطلق اختياريًا الذاكرة المرتبطة به. في بعض عمليات التنفيذ ، يجب عليك إلغاء تخصيصها بشكل صريح مع بيان DEALLOCATE. بعد تحرير المؤشريتم تحرير الذاكرة أيضًا ، ويمكن إعادة استخدام اسم المؤشر.
للتحكم في وقت الوصول إلى نهاية المؤشر ، يوصى باستخدام الوظيفة:FETCH_STATUS
ترجع الدالةFETCH_STATUS:
0 إذا كان الجلب ناجحًا ؛
1 إذا فشل الجلب بسبب محاولة جلب صف خارج المؤشر ؛
2 إذا فشل الجلب بسبب محاولة الوصول إلى صف تم حذفه أو تعديله.
DECLAREid_kl INT،firm VARCHAR (50)،fam VARCHAR (50)،message VARCHAR (80)،nam VARCHAR (50)، @ d DATETIME،p INT،s INT SET @ s = 0 PRINT "قائمة التسوق" DECLARE klient_cursor CURSOR LOCAL FOR SELECT Client ID، Company، Last Name FROM Client WHERE City = "Moscow" ORDER BY Company، Last Name OPEN klient_cursor FETCH التالي من klient_cursor INTOid_kl،firm،fam WHILEFETCH_STAT = 0 BEGIN SELECT @ message = "العميل" [بريد إلكتروني محمي]+ "Company" +firm PRINTmessage SELECT @ message = "اسم المنتج تاريخ الشراء السعر" PRINTmessage DECLARE tovar_cursor CURSOR FOR SELECT Item.Name، Trade.Date، Item.Price * Trade.Quantity AS Cost from Item INNER JOIN المعاملات على البضائع. ItemCode = Deal.ItemCode حيث يتم عرض الصفقة.رمز العميل [بريد إلكتروني محمي] _kl افتح tovar_cursor إحضار التالي من tovar_cursor INTOnam،d،p IFFETCH_STATUS<>0 اطبع "لا مشتريات" WHILEFETCH_STATUS = 0 BEGIN SELECT @ message = "" [بريد إلكتروني محمي]+ "" + CAST (d AS CHAR (12)) + "" + CAST (p AS CHAR (6)) PRINTmessage SET @ [بريد إلكتروني محمي][بريد إلكتروني محمي]إحضار التالي من tovar_cursor INTOnam،d،p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @ message = "إجمالي التكلفة" + CAST (s AS CHAR (6)) PRINTmessage - الانتقال إلى العميل التالي - إحضار التالي من klient_cursor إلىid_kl،firm،fam END CLOSE klient_cursor DEALLOCATE klient_cursor مثال 13.6. مؤشر لعرض قائمة البضائع المشتراة من قبل العملاء من موسكو والتكلفة الإجمالية.
مثال 13.7.قم بتطوير مؤشر قابل للتمرير للعملاء من موسكو. إذا كان رقم الهاتف يبدأ بالرقم 1 ، فقم بإزالة العميل بهذا الرقم وفي إدخال المؤشر الأول ، استبدل الرقم الأول في رقم الهاتف بالرقم 4.
DECLAREfirm VARCHAR (50)،fam VARCHAR (50)،tel VARCHAR (8)،message VARCHAR (80) PRINT "List of Clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Company، Last Name، Phone FROM Client حيث المدينة = "موسكو" ترتيب حسب الشركة ، اسم العائلة للتحديث افتح klient_cursor إحضار التالي من klient_cursor INTOfirm،fam،tel WHILEFETCH_STATUS = 0 BEGIN SELECT @ message = "Client" [بريد إلكتروني محمي]+ "شركة" [بريد إلكتروني محمي]"Phone" + @ tel PRINTmessage - إذا كان رقم الهاتف يبدأ بالرقم 1 - احذف العميل بهذا الرقم IFtel LIKE '1٪' احذف العميل حيث يوجد حاليًا klient_cursor آخر - انتقل إلى العميل التالي وابدأ بعد ذلك من klient_cursor INTO firm،fam،tel END FETCH ABSOLUTE 1 من klient_cursor INTOfirm،fam،tel - في الإدخال الأول ، استبدل الرقم الأول في - رقم الهاتف بـ 4 UPDATE Client SET Phone = '4' + RIGHT ( @ tel، LEN (tel) -1)) حيث يوجد klient_cursor SELECT @ message = "Client" [بريد إلكتروني محمي]+ "Firm" +firm "Phone" + @ tel PRINTmessage CLOSE klient_cursor DEALLOCATE klient_cursor مثال 13.7. مؤشر قابل للتمرير للعملاء من موسكو.
مثال 13.8.إستعمال المؤشر كمعامل إخراج لإجراء ما. يقوم الإجراء بإرجاع مجموعة بيانات - قائمة بالبضائع.
يتم استدعاء الإجراء وطباعة البيانات من مؤشر الإخراج على النحو التالي:
إعلانmy_cur CURSOR DECLAREn VARCHAR (20) EXEC my_proc @ [بريد إلكتروني محمي] _cur الإخراج التالي منmy_cur INTOn SELECTn بينما (FETCH_STATUS = 0) ابدأ الإحضار التالي منmy_cur INTO @ n حددn النهاية إغلاقmy_cur DEALLOCATEmy_cur
